Want to unserstand "administrator command: Reset stmt" in query log

[pauls]

Using 5.5.38-35.2-log Percona Server, Release 35.2, Revision 674

We set the log slow time to 0, and captured a few minutes on our busy production system. Then, running pt-query-digest and sorting by query count, the most frequently executed query is “administrator command: Reset stmt”. It is executed 10 times more often than the highest application query. We do use prepared statements, so I see Prepare and Close, and have read up on when those are issued. I cannot find much documentation on Reset. I believe it is connected to the COM_STMT_RESET status variable. Under what circumstances is Reset used, what does it do, and is there anything that can be done to reduce the number of times it is executed (assuming I should be worried about that). Any links or info is appreciated.

Here is the pt-query digest output

Attribute pct total min max avg 95% stddev median

============ === ======= ======= ======= ======= ======= ======= =======

Count 23 1575177

Exec time 0 16s 3us 1ms 10us 13us 8us 9us

Lock time 0 0 0 0 0 0 0 0

Rows sent 0 0 0 0 0 0 0 0

Rows examine 0 0 0 0 0 0 0 0

Rows affecte 0 0 0 0 0 0 0 0

Rows read 0 0 0 0 0 0 0 0

Bytes sent 0 16.52M 11 11 11 11 0 11

Merge passes 0 0 0 0 0 0 0 0

Tmp tables 0 0 0 0 0 0 0 0

Tmp disk tbl 0 0 0 0 0 0 0 0

Tmp tbl size 0 0 0 0 0 0 0 0

Query size 3 50.96M 33 33 33 33 0 33

String:

Databases xxx_… (1598474/101%)… 1858 more

Hosts

Last errno 0

Users xxx

Query_time distribution

1us ##################################################

10us ##################################################

100us

1ms

10ms

100ms

1s

10s+

administrator command: Reset stmt\G

[scott.nemes]

Hi Pauls:

The reset stmt command is related to your usage of prepared statements:

"The thread is resetting a prepared statement. "
http://dev.mysql.com/doc/refman/5.5/en/thread-commands.html

Basically it is resetting a prepared statement so that it can be used again (i.e. clearing the parameters).

http://dev.mysql.com/doc/internals/en/com-stmt-reset.html#packet-COM_STMT_RESET

The part about when this gets called is a little more vague. Depending on how you are running the prepared statements (i.e. PHP or Java), that will probably vary. You can call it explicitly for sure, but I’d imagine there are also cases where it will get called implicitly that may vary by the source. This is something your developers should hopefully be able to help you track down.

http://php.net/manual/en/mysqli-stmt.reset.php
http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#clearParameters%28%29

Note the above links are just some examples of likely explicit resetting of the prepared statement; I do not know for sure if they actually result the “reset stmt” admin command or not, just a starting point to look at.

As for if you should be worried, I would not think so, as reusing of prepared statements is generally a good thing performance wise. =)

-Scott

[pauls]

Thank you Scott -
In general (there are always exceptions!), if the prepared statement flow would be to Prepare…Execute…Close, would there be a need for Reset?

[scott.nemes]

Hi Pauls;

If you are not executing the same prepared statement more than once with different parameters, then it seems like a reset would not be needed. So it is likely that either it is re-using the same prepared statement, or there is something implicitly doing the reset.

One interesting piece to look at is this:
http://dev.mysql.com/doc/refman/5.5/en/statement-repreparation.html

It may not have any affect here, but it does seem like it could be a potential lead to look at anyway.

My experience with prepared statements is limited, so sadly I cannot offer too much more advice. Hopefully someone with more hands on experience in the area can chime in if needed. =)

-Scot