What vmstat does tell us is that the bottleneck is not the CPU, since it is idle almost the entire time (the last column). Your memory swapd, si, so columns don't change, so the OS is fine with the RAM. So we've ruled out CPU and RAM. At least as far as the OS is concerned. That leaves disk and network. Well, it's not the network, since we are not using it. So that leaves I/O, which is almost always disk.
Your bi (block in) column climbs as does your interrupt (in) and context switching (cs). A process can be kicked off the CPU (interrupt and/or context switching) because it lost priority or because it is blocked (i.e. waiting for data). Since I'm guessing I/O is your problem, you may try running iotstat now. That will show you the disk and CPU activity.
Now, all these numbers are from the OS point of view, not MySQL. So if MySQL has a low priority (nice value), it won't be given enough resources to tax any part of the system. That's an extreme example, you won't likely need to change the nice value of MySQL. But for fun, you could try changing the nice value of MySQL on your system to see how it affects things. But this isn't a technique you should rely on.
On Aug 12, 2004, at 2:50 PM, [EMAIL PROTECTED] wrote:
Thanks for the vmstat tip. I ran vmstat 1 on the query on a slightly quicker query so I wouldn't have a ton of numbers to post from the vmstat.
mysql> SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('Pink');
+----------+
| COUNT(*) |
+----------+
| 12231 |
+----------+
1 row in set (8.05 sec)
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
0 0 0 200888 29572 14820 356696 0 0 0 0 531 40 0 0 100
0 1 0 200888 28356 14828 357772 0 0 1084 0 614 128 1 0 99
0 1 0 200888 25932 14836 360204 0 0 2432 28 835 360 0 0 99
0 1 0 200888 24412 14836 362944 0 0 2740 0 867 398 0 1 99
0 1 0 200888 21792 14840 365560 0 0 2616 12 859 374 0 0 100
0 1 0 200888 18664 14840 368688 0 0 3128 0 903 400 0 1 99
0 1 0 200888 16016 14840 371336 0 0 2648 0 870 390 0 0 100
0 1 0 200888 13500 14844 373848 0 0 2512 24 870 372 0 0 99
0 1 0 200888 11368 14840 375984 0 0 2132 0 822 392 0 0 99
0 0 0 200888 10100 14848 377244 0 0 1260 12 717 289 0 0 100
0 0 0 200888 10100 14828 377264 0 0 0 0 524 20 0 0 100
I really don't know what I'm looking for here, is "bi" and "bo" the page-in and page-out variables you were talking about, or is it "si" and "so". The system shouldn't be taxed at all since I am the only user on the machine and its not running anything else at the moment.
The server is a Dual Xeon 2GHz, 512 MB RAM, 72 GB hard drive running on Linux RedHat 7.3
I did notice that the key_buffer_size and sort_buffer_size were a little high (I guess I though the machine had 1Gb of RAM instead of 512) so I decreased the key_buffer_size to 125Mb and the sort_buffer_size to 45 Mb. Restarted mysql and ran an identical query, the result was about 20% slower after I lowered the buffer sizes.(Guess it didn't help)
- John
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
