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.
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.
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. =)
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?
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.
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. =)