…/slow_query.log { nocompress create 660 mysql mysql size 1G dateext missingok notifempty sharedscripts postrotate /usr/local/bin/mysql -e ‘select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query…
Comment: Rotating MySQL slow logs safely
April 19, 2013 By 7 Comments
…astrostl, the slow logs are disabled in the postrotate stanza. The order of …. Slow logging is paused (set global long_query_time=2000;) 2b. There is a small wait for buffered logs to be flushed (select sleep(2);) 2c. File handles are reopened (FLUSH LOGS;) 2d. Slow logging resumed (set global long_query_time=@lqt_save…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… pt-query-digest Using pt-query-digest is pretty straight forward: pt-query-digest /path/to/slow-query.log Note that executing pt-query-digest can… the SELECT wp_options query, this is basically a unique way of identifying the query and simply implies that this is a SELECT query executed…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
… Slow Query Log – Especially after the changes to log all queries slow query log may be growing too rapidly to follow, so we implemented slow query log…. Changes may help to one queries but hurt others, for example adding indexes often help SELECT queries but slow down INSERT/UPDATE ones. Final…
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
… log-slow.log and now change it to SELECT * FROM slow_log ORDER BY start_time DESC LIMIT 100 you would find new queries…. If you want to get new queries in the end you can do SELECT * FROM (SELECT * FROM slow_log ORDER BY start_time DESC LIMIT 100) l ORDER BY start_time which shows last queries in…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… slow query | 0.000002 | | cleaning up | 0.000002 | +———————-+———–+ 17 rows in set (0.00 sec) As you can see that the SELECT query still blocks, and the status counter Opened_tables is also incremented once the query finishes. So much…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… start to analyze the information on INDEX_STATISTICS table. mysql> SELECT * FROM INDEX_STATISTICS; +————–+————+————–+———–+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS… from the slow query log. The concept is easy to understand. pt-index-usage reads the slow query log and execute every query with EXPLAIN… sometimes the real query can use a different execution plan. pt-index-usage tries to convert non-SELECT queries to SELECT queries and is not…
Post: How To Test Your Upgrades - pt-upgrade
December 29, 2011 By 7 Comments
… tool lets you test your SELECT queries against multiple MySQL servers and reports on how each type of query performs from every server…. Capture your production queries, we recommend either slow query log (use long_query_time = 0) or tcpdump (other types supported by pt-query-digest’s… # Fingerprint # select * from categories where parent = ? and site = ? Looking at the sample above, there are microsecond differences between the query times. The…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…scan vs range scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | … is completely disk bound can be very slow. One of the reasons elevating this …requests one by one for query execution, which means if single query execution time is …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… the client is doing queries like “… WHERE client IN (SELECT id FROM clients)” which is a really bad query plan. After this I… slow /path/to/slow.log By default, this outputs the top 10 most expensive queries, in total execution time. By the way, the slow query… time. For each query, I run EXPLAIN if possible (rewriting non-SELECT queries if needed, and using careful judgment for queries with subqueries in…
