Need help deciphering the mysql-slow.log file

Update: I actually do have a dbuser named 'thepatmaster'…let me look and see if I can solve some of this myself.

A couple of tips:

1. Run EXPLAIN before the sql query to see why it's slow http://dev.mysql.com/doc/refman/5.0/en/explain.html

2. It may not be a mysql problem you maybe swapping which is causing the whole server to lag, it's a common problem, check your Linode graphs to see if you've a lot of swap IO.

Thanks. I do have alot of IO swap. This is what brings the server down.

~~http://isighttech.com/generate_graph.png" />

Here's an EXPLAIN from one of the long running queries.

mysql> EXPLAIN SELECT * FROM wp_comments WHERE comment_post_ID = 87 AND  comment_approved = '1' ORDER BY comment_date_gmt ASC;
+----+-------------+-------------+------+------------------------------------------------------------+-----------------+---------+-------+-------+-----------------------------+
| id | select_type | table       | type | possible_keys                                              | key             | key_len | ref   | rows  | Extra                       |
+----+-------------+-------------+------+------------------------------------------------------------+-----------------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | wp_comments | ref  | comment_approved,comment_post_ID,comment_approved_date_gmt | comment_post_ID | 8       | const | 13094 | Using where; Using filesort |
+----+-------------+-------------+------+------------------------------------------------------------+-----------------+---------+-------+-------+-----------------------------+
1 row in set (0.00 sec)

Doesn't seem like the query takes that much time when running..0.00 sec?~~

vmstat looks like this:

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  0  38924 333748   2296  73716    1    1     0     1    1    1  0  0 99  0
 0  0  38624 329716   2696  77316    1    0    66     6  279  105  2  0 98  0
 0  0  38624 357376   2748  77316    0    0     0     3  137   56  1  0 99  0
 0  0  38624 367820   2812  77288    0    0     1     4   72   33  0  0 100  0
 0  0  38624 355836   2840  77336    0    0     0     1   41   24  0  0 100  0
 0  0  38480 368604   2912  77524    0    0     4     3   79   35  0  0 100  0
 2  0  38480 309748   3296  81420    1    0    72     1  101   55  0  0 99  0
 0  0  38480 330756   3328  81432    0    0     0     2  221   69  2  0 98  0
 1  0  38460 364256   3384  81436    0    0     1     2   68   33  0  0 100  0
 0  0  38452 364512   3416  81504    0    0     1     2   91   36  0  0 99  0
 0  0  38452 351080   3468  81712    0    0     3     3  105   42  0  0 99  0
 0  0  38412 363404   3516  81744    1    0     1     4  411  134  3  0 97  0
 1  0  38412 341832   3556  81744    0    0     0     1  103   45  0  0 99  0
 0  0  38412 363528   3580  81764    0    0     0     2   58   29  0  0 100  0
 0  0  38408 352588   3604  81772    0    0     0     3  214   75  1  0 98  0
 1  0  38116 356680   4100  87852    5    0   114     3   92   55  0  0 99  0

If you're hitting swap, the problem isn't with your queries, but that you don't have enough RAM. You can either reconfigure your processes (Apache, MySQL, etc), or add more RAM.

As a band-aid I've purchased 90MB more RAM. Have hired an Admin to help with the processess and fine tuning. I don't have that much traffic on any of the 7 sites I have running. Total, per day, there are probably 200 uniques on the server.

Thanks for the information.

Just keep in mind you have to reboot after adding more RAM, for the system to take advantage of it, and 90MB isn't going to make much of a difference.

The typical culprit is Apache, in these scenarios. By default, Apache is configured for machines with many gigabytes of RAM or some such nonsense. Basically the default mode of operation spawns a separate process for every concurrent connection handled, and if you use mod_php, each process has all of PHP loaded.

Thanks for your feedback, in the end, I worked with an admin who did this:

Apache:

the goal is to minimize memory used by apache & number of processes

KeepAlive Off

StartServers 2

MinSpareServers 2

MaxSpareServers 4

MaxClients 15

ServerLimit 100

MaxRequestsPerChild 1000

Mysql:

table_cache = 1k

Everything seems to be running smoothly. Thank you for your responses.