Not the answer you need?
Register and ask your own question!

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

paulspauls EntrantCurrent User Role Beginner
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

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • paulspauls Entrant Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.