<blockquote class="quote light-black dark-border-color"><div class="quote light-border-color">
<div class="quote-author" style="font-weight: bold;">David Rees wrote:</div>
<div class="quote-message">
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?
</div>
</div></blockquote>
<p>
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:</p>
<pre>
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
</pre>
<p>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.</p>
<p>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).</p>
<pre>
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
</pre>
<br><hr align="left" width="300">
View this message in context: <a href="http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12529279">Re: Slow MySQL query after delete</a><br>
Sent from the <a href="http://www.nabble.com/mythtv-users-f15550.html">mythtv-users mailing list archive</a> at Nabble.com.<br>