I've had issues with database connections problems for the past month or so. My sites go down at least twice a day. They all come pack up within a minute or so.
Then I get the i/o rate warning from linode. They are very low traffic sites so I'm not terribly concerned but I do have some clients on the box who are being affected.

I've set up the mysql-slow. log and have run this command:
mysqldumpslow -s t /var/log/mysql/mysql-slow.log

Count: 57  Time=7.33s (417s)  Lock=0.00s (0s)  Rows=0.0 (0), 2users@localhost
  SET NAMES utf8

Count: 10  Time=26.00s (259s)  Lock=18.09s (180s)  Rows=90.4 (904), 3users@localhost
  SELECT option_name, option_value FROM wp_options WHERE autoload = 'S'

Count: 36  Time=4.16s (149s)  Lock=0.00s (0s)  Rows=0.0 (0), 3users@localhost
  #

Count: 3  Time=14.43s (43s)  Lock=0.00s (0s)  Rows=0.0 (0), kero_master[kero_master]@localhost
  SET NAMES latin1

Count: 3  Time=5.73s (17s)  Lock=0.00s (0s)  Rows=13531.0 (40593), thepat_master[thepat_master]@localhost
  SELECT * FROM wp_comments WHERE comment_post_ID = N AND  comment_approved = 'S' ORDER BY comment_date_gmt ASC

Count: 4  Time=2.83s (11s)  Lock=0.00s (0s)  Rows=0.0 (0), thepat_master[thepat_master]@localhost
  SELECT option_value FROM wp_options WHERE option_name = 'S' LIMIT N

Count: 2  Time=3.06s (6s)  Lock=0.02s (0s)  Rows=269.0 (538), debian-sys-maint[debian-sys-maint]@localhost
  select concat('S',
  TABLE_SCHEMA, 'S', TABLE_NAME, 'S') 
  from information_schema.TABLES where ENGINE='S'

Count: 3  Time=1.95s (5s)  Lock=0.94s (2s)  Rows=5.0 (15), thepat_master[thepat_master]@localhost
  SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE N=N  AND wp_posts.post_type = 'S' AND (wp_posts.post_status = 'S')  ORDER BY wp_posts.post_date DESC LIMIT N, N

Count: 2  Time=2.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), debian-sys-maint[debian-sys-maint]@localhost
  select count(*) into @discard from `information_schema`.`PARTITIONS`

Count: 1  Time=3.29s (3s)  Lock=0.11s (0s)  Rows=1.0 (1), kero_master[kero_master]@localhost
  SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'S' AND post_type IN ('S', 'S', 'S') ORDER BY post_modified_gmt DESC LIMIT N

Count: 1  Time=1.61s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), thepat_master[thepat_master]@localhost
  SELECT * FROM wp_posts WHERE ID = N LIMIT N

I've got no idea who thepat_master or kero_master is..same with 2users@localhost. Has my server been compromised or is it some rouge plugin (wordpress sites) causing the issues. I've searched kero_master and thepat_master but get nothing on the web. And some of these queries look a bit off...

Any help would be appreciated.
Steve

Update: I actually do have a db_user named 'thepat_master'...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.

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

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




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

<IfModule mpm_prefork_module>
StartServers 2
MinSpareServers 2
MaxSpareServers 4
MaxClients 15
ServerLimit 100
MaxRequestsPerChild 1000
</IfModule>


Mysql:

table_cache = 1k

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