Pligg long running queries

Submitted by tomo on September 7, 2010 - 2:20am

 

 

For awhile I've been plagued with these long running queries on my Pligg site that were running up CPU:
 
SELECT DISTINCT pv_user_id, pv_type, pv_page_id FROM mypligg_pageviews GROUP BY pv_type, pv_page_id, pv
 
And so I was running the mkill Perl script to kill any queries taking longer than a minute to complete.
But recently even that stopped working, leaving queries in the Killed state but still running up CPU.  They were all stuck in "converting HEAP to MyISAM".  Did some diagnostics:
 
mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 196   |
+--------------------+-------+
1 row in set (0.01 sec)
 
mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 159   |
+-------------------------+-------+
1 row in set (0.01 sec)
 
mysql> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+
1 row in set (0.01 sec)
 
 
So it seemed there wasn't enough memory to perform the conversion without going to disk which is not what we want.  So I set tmp_table_size and max_heap_table_size to larger values, max_heap_table_size too because MySQL uses the lesser of the two.
 
mysql> set tmp_table_size=100000000;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set  max_heap_table_size=100000000;
Query OK, 0 rows affected (0.01 sec)
 

And now we're golden, CPU back down to normal levels and queries finishing.

Read the rest of this article...
tomo

To see what's going on first, run:

show processlist;

and look for the "converting HEAP to MyISAM" message.

© 2010-2014 Saigonist.