Peter at MySQL Performance Blog pointed out this sweet perl script to analyze MySQL’s slow query logs. (This is supposedly a PHP port.)
The script does a good job of aggregating similar queries (those that only differ in their query values) and displaying overall stats for them. The following two queries are showing up a lot in my WPMU installation because I also have it set to log queries that don’t use indexes. They’re not necessarily slow (MySQL < 5.1 logs execution times to the nearest second, with a one second minimum), but the fact that they’re appearing a lot probably means the value isn’t being cached in Memcached.
The first SQL line shows the prototype for the query, the second is a real example from the log.
### 90379 Queries
### Total time: 90556, Average time: 1.00195841954436
### Taking 1 to 6 seconds to complete
### Rows analyzed 744 - 587874
SELECT COUNT(ID) FROM wp_XXX_posts WHERE post_status = 'XXX' and post_type = 'XXX';
SELECT COUNT(ID) FROM wp_7_posts WHERE post_status = 'publish' and post_type = 'post';
### 8768 Queries
### Total time: 9033, Average time: 1.03022354014599
### Taking 1 to 4 seconds to complete
### Rows analyzed 0 - 199
SELECT option_name, option_value FROM wp_XXX_options FORCE INDEX(PRIMARY) ORDER BY option_id ASC;
SELECT option_name, option_value FROM wp_4_options FORCE INDEX(PRIMARY) ORDER BY option_id ASC;
Posted March 17, 2009 by Casey Bisson
Categories: Dispatches. Tags: mysql, optimization, performance, slow query log.
No Comments Yet
No comments yet.
Comments RSS
TrackBack Identifier URI
User contributed tags for this post:
slow query log analyzer (10) - mysql slow query log (8) - mysql slow query (5) - mysql slow (3) - mysql slow queries (3) - log slow queries (3) - mysql slow log analyzer (2) - mysql slow query log analyser (2) - wordpress slow mysql queries (2) - open source mysql query analysis (2) - analyze mysql query log (2) - analyze slow query log (2) - mysql slow query log analyzer (2) - mysql slow query analyse (2) - mysql analyze query log (2) - analysis mysql query log (2) - mysql analysis (2) - analysing slow query log (2) - mysql slow analyzer (2) - slow query log (2) - analyze mysql slow queries (1) - mysql analyse querylog (1) - slow log query not created (1) - mac mysql query log reader (1) - mysql slow order by (1) - mysql slow query log analyse (1) - slow queries analyze php script (1) - nanlysing mysql slow query log file (1) - wordpress SELECT option_value performance index (1) - how to analyze mysql slow log queries manual (1) - script mysql slow querys (1) - problem Slow queries in mysql (1) - log query time (1) - mysql slow log no index (1) - script analyse mysql slow log (1) - slow query log analyser (1) - MySQL Log slow querys (1) - how to read query log mysql (1) - query log analyzer mysql (1) - how to mysql slow query (1) - mysql query slow wordpress (1) - mysql avg slow (1) - mysql slow log analysis (1) - how to log queries mysql (1) - mysql analyze slow log (1) - mysql slow query analyze (1) - slow queries log analyzer mysql (1) - what is query time in mysql slow query log (1) - mysql queries in mysql (1) - analysing slow queries mysql index (1) - web mysql Slow Query Log (1) - mysql log analyzer (1) - wordpress comments slow mysql queries (1) - mysql (1) - mysql order by ASC slow (1) - how to track slow queries MySQL (1) - order by ascending mysql slow perfomance (1) - query execution is to slow with ORDER BY clause mysql (1) - query time in slow query log (1) - mysql slow query logging (1) - slow query logs (1) - mysql query log (1) - MySQL Log (1) - slow MySQL queries wordpress (1) - mysql analyse slow queries (1) - tags wordpress slow mysql (1) - mysql slowquery analyzer (1) - analyze log-slow-queries mysql (1) - mysql-slow log analysis (1) - analyze mysql slow query log (1) - mysql Slow with order by QUICK (1) - mysql log users queries (1) - set mysql query log analysis (1) - mysql slow queries log analysis (1) - mysql index slow queries (1) - mysql analyse query (1) - query php-mysql slowly (1) - mysql log queries location (1) - mysql php log analyzer (1) - analyzing mysql slow query log (1) - mysql slow log (1) -
