Documentation of major changes to mysqldumpslow as of 2007-10-22 Nils Goroll slink@mcs.de ----- - IN (...) abstraction In previous versions, mysqldumpslow abstracted the values of queries containing 'IN (value1, value2, ...)' to IN (S, S, ...) or IN (N, N, ...) depending on which types where used within the value list. The primary disadvantage of this behaviour is that queries with different numbers of values for IN were aggregated as distinct queries while, most probably, the same piece of SQL code was responsible for all of them. Here's an example of mysqldump's new behaviour on three slow queries you might see when using a popular bulletin board application: # mysqldumpslow test1 Reading mysql slow query log from test1 Count: 3 Time=2.00s (6s) Lock=0.00s (0s) Rows=14.0 (42) Rows examined=5194.0 (15582), Database: myforum_db user[user]@[webserver] SELECT post.postid FROM post AS post WHERE post.threadid = N AND post.visible = N AND post.userid NOT IN (...) ORDER BY post.dateline LIMIT N, N The old behaviour is available via the -ain switch, which, for this example, will show two aggregated queries for the case that five values are given to IN and one query for IN with six values: # mysqldumpslow -ain test1 Reading mysql slow query log from test1 Count: 2 Time=2.00s (4s) Lock=0.00s (0s) Rows=14.0 (28) Rows examined=5194.0 (10388), Database: myforum_db user[user]@[webserver] SELECT post.postid FROM post AS post WHERE post.threadid = N AND post.visible = N AND post.userid NOT IN (N,N,N,N,N) ORDER BY post.dateline LIMIT N, N Count: 1 Time=2.00s (2s) Lock=0.00s (0s) Rows=14.0 (14) Rows examined=5194.0 (5194), Database: myforum_db user[user]@[webserver] SELECT post.postid FROM post AS post WHERE post.threadid = N AND post.visible = N AND post.userid NOT IN (N,N,N,N,N,N) ORDER BY post.dateline LIMIT N, N - VALUES (...) abstraction Analogous to the IN (...) case, mysqldumpslow now abtracts statements containing VALUES (value1,value2,...)(value3,value4,...) to VALUES (...) Here's an example of the new behaviour: # mysqldumpslow test2 Reading mysql slow query log from test2 Count: 3 Time=3.00s (9s) Lock=0.00s (0s) Rows=0.0 (0) Rows examined=0.0 (0), Database: myforum_db user[user]@[webserver] REPLACE INTO postindex (wordid, postid, score, intitle) VALUES (...) The -avalues gives the old behaviour, producing lengthy output for queries with many value tupels: # mysqldumpslow -avalues test2 Reading mysql slow query log from test2 Count: 1 Time=3.00s (3s) Lock=0.00s (0s) Rows=0.0 (0) Rows examined=0.0 (0), Database: myforum_db user[user]@[webserver] REPLACE INTO postindex (wordid, postid, score, intitle) VALUES (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N) (N, N, N, N), (N, N, N, N) Count: 2 Time=3.00s (6s) Lock=0.00s (0s) Rows=0.0 (0) Rows examined=0.0 (0), Database: myforum_db user[user]@[webserver] REPLACE INTO postindex (wordid, postid, score, intitle) VALUES (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N), (N, N, N, N) Note: The VALUES abstraction implementation will eat anything between "VALUES (.." and the rightmost parenthesis, which could lead to missing SQL code in the mysqldumpslow output for corner cases. A correct regular expression avoiding this issue is included in the mysqldumpslow source code, but as it triggers a bug present in poplular perl versions, it is commented out and replaced by the simplier and incomplete but working version. - Rows examined output and sorting by rows examined mysqldumpslow now also outputs the average and total rows examined from the slow log input for each aggregated query. Sorting by rows examined, which is useful for spotting queries not making efficient use of indexes, is available via the "-s re" (total rows examined) or "-s are" (average rows examined) flags. - Inverse grep Besides "grepping" for queries matching a regular expression within the slow.log (option -g PATTERN), mysqldumpslow now also supports an inverse grep to specify slow queries not to consider. The match is case-insensitive One use case for this flag is to filter out queries resulting from mysqldump, which read "SELECT /*! SQL_NO_CACHE */ ..." mysqldumpslow -G=SQL_NO_CACHE Note: This example is simple, but not absolutely correct as it would also filter out queries containing the string "SQL_NO_CACHE" anywhere in the query. A more accurate, but more complex regular expression for this case is mysqldumpslow -G='SELECT /\*!\d+\s+SQL_NO_CACHE\s+\*/' - Whitespace normalisation Traditionally, mysqldumpslow preserves the original formatting of SQL code and only replaces leading whitespace at the beginning of lines with " ". The default has been changed to replace any whitespace (including newlines) with single blanks, which improves aggregation. Use the option -awhitespace to get the old behaviour. - Output of database name for aggregated queries (contribution by Thierry Randrianiriana, see Bug #21359 ) Traditionally, mysqldumpslow does not give any information as to which database (schema) the slow query originated from. Now mysqldumpslow will dump the database name if slow queries originated from exactly one database. If they originated from several databases, it will print "Database: Ndbs" (N being a number) or ""Database: 0dbs", respectively.