[mythtv-users] Slow MySQL query after delete

crs23 pvr at groundhog.pair.com
Thu Sep 6 19:04:32 UTC 2007



David Rees wrote:
> 
> I may have missed it, but did you post the version of MySQL and the
> relevant parameters from my.cnf that would affect performance?
> 
> When the query is running, does it appear that the system is CPU bound
> or IO bound (watch vmstat 1 output). If you could grab a snippet of
> vmstat 1 output while the query is running that would be helpful. What
> are the specs of the machine running MySQL?
> 


I did not post any of those things.  Linux 2.6.18.  dpkg shows MySQL
5.0.32-2.  Here are the interesting bits from my.cnf:


key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
table_cache             = 256
sort_buffer_size        = 4M
read_buffer_size        = 1M
read_rnd_buffer_size    = 1M
query_cache_limit       = 1048576
query_cache_size        = 16777216
query_cache_type        = 1
skip-bdb
skip-innodb

I recently added 'skip-innodb' at the suggestion of one post and adjusted
some of the numbers a little.  Those changes don't seem to have made much
difference.

When running the query I'm seeing 98% CPU on the mysqld process, ~65% user
and ~35% system.  The vmstat results are below for the second of two back to
back runs of the query (the query times were essentially the same for both). 
The machine is low end:  600MHz C3 with 256MB RAM.  The disk seems fast
(it's a ST3160021A, seagate barracuda 4).


procs -----------memory---------- ---swap-- -----io---- -system--
----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id
wa
0  0 120828   8488   1728  46348    0    0     0    12 1275  621  0  2 98  0
 3  0 120828   8480   1728  46404    0    0     0     0 1255  625 19 11 70 
0
 2  0 120828   8480   1728  46524    0    0     0     0 1213  614 68 32  0 
0
 1  0 120828   8480   1728  46664    0    0     0     0 1213  604 65 35  0 
0
 1  0 120828   8360   1728  46744    0    0     0     0 1254  626 63 37  0 
0
 1  0 120828   8240   1728  46856    0    0     0     0 1214  607 66 34  0 
0
 1  0 120828   8128   1736  47000    0    0     0   628 1217  632 64 36  0 
0
 3  0 120828   7948   1736  47140    0    0     0     0 1201  613 67 33  0 
0
 1  0 120828   7948   1736  47180    0    0     0     0 1205  609 64 36  0 
0
 1  0 120828   7888   1736  47236    0    0     0     0 1211  608 62 38  0 
0
 3  0 120828   7828   1736  47280    0    0     0     0 1231  620 62 38  0 
0
 1  0 120828   7708   1744  47412    0    0     0   484 1238  613 64 36  0 
0
 1  0 120828   7528   1744  47544    0    0     0   224 1238  620 66 34  0 
0
 1  0 120828   7408   1744  47684    0    0     0     0 1203  617 67 33  0 
0
 1  0 120828   7348   1744  47740    0    0     0     0 1202  623 63 37  0 
0
 3  0 120828   7168   1744  47936    0    0     0     0 1197  607 65 35  0 
0
 2  0 120828   7048   1752  48056    0    0     0   488 1217  641 67 33  0 
0
 3  0 120828   6868   1752  48196    0    0     0     0 1206  606 68 32  0 
0
 1  0 120828   6808   1752  48276    0    0     0   236 1231  635 62 38  0 
0
 2  0 120828   6688   1752  48388    0    0     0     0 1247  626 64 36  0 
0
 1  0 120828   6568   1752  48528    0    0     0     0 1188  653 64 36  0 
0
 1  0 120828   6388   1760  48660    0    0     0   452 1197  673 65 35  0 
0
 1  0 120828   6328   1760  48704    0    0     0     0 1208  686 63 37  0 
0
 2  0 120828   6268   1760  48760    0    0     0     0 1209  683 63 37  0 
0
 1  0 120828   6268   1760  48832    0    0     0     0 1223  679 66 34  0 
0
 1  0 120828   6088   1760  48944    0    0     0   268 1230  665 66 34  0 
0
 2  0 120828   5968   1768  49068    0    0     0   196 1244  644 67 33  0 
0
 1  0 120828   5848   1768  49208    0    0     0     0 1214  602 66 34  0 
0
 3  0 120828   5788   1768  49264    0    0     0     0 1211  625 63 37  0 
0
 2  0 120828   4452   1768  49404    0    0     0     0 1196  811 70 30  0 
0
 5  0 120828   6432   1768  46344    0    0     0     0 2487 4362 76 24  0 
0
 4  0 120828   6432   1780  46360    0    0     0    80 2715 4870 75 25  0 
0
 4  0 120828   6432   1780  46352    0    0     0     0 2754 4975 76 24  0 
0
 0  0 120828   8412   1780  46352    0    0     0     0 2388 3932 57 20 23 
0


-- 
View this message in context: http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12529279
Sent from the mythtv-users mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20070906/1cad4d59/attachment.htm 


More information about the mythtv-users mailing list