Mysql – thesql server hung up because of too many connections

MySQL

Our mysql server causes huge overload because of number of opened connections. In my case most connections are in opened state and I cannot even kill them. That is I cannot understand. The connections look like this:

    | 13927 | slimdiz1_dstream | localhost | slimdiz1_docstream         | Query      | 20   | closing tables       | SELECT autoload FROM wp_options WHERE option_name = '_site_transient_theme_roots'                    |
| 13934 | okanaga1_db1     | localhost | okanaga1_db1               | Query      | 5    | Opening tables       | INSERT INTO sessions VALUES('e442db394d16e7ac5d2154ae3e1ccbe4', '1306059991', 'a:5:{s:11:"LoginCooki |
| 13935 | mkca1_wrdp5      | localhost | mkca1_wrdp5                | Query      | 7    | Opening tables       | SELECT * FROM wp_users WHERE ID = 1 ..

Connections number to the web server (max is 150):

    ps -e | grep httpd | wc -l
109

Available memory:

    free -mto
             total       used       free     shared    buffers     cached
Mem:          7974       5565       2409          0        153       1373
Swap:         2047         31       2016
Total:       10022       5597       4425

MySQL server version and the configuration file:

    [mysqld]

safe-show-database

set-variable = max_connections=450
set-variable = interactive_timeout=150
set-variable = connect_timeout=35
set-variable = wait_timeout=70
set-variable = max_user_connections=30

set-variable = key_buffer=48M
set-variable = read_buffer=5M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M

set-variable = table_cache=1028
set-variable = thread_cache=128

set-variable = query_cache_limit=1M
set-variable = query_cache_size=1024M
set-variable = query_cache_type=1
set-variable = thread_concurrency=4
set-variable = max_allowed_packet=5M

set-variable = delayed_insert_limit=80
set-variable = delayed_insert_timeout=200
set-variable = delayed_queue_size=700

set-variable = flush_time=300


mysql  Ver 14.12 Distrib 5.0.91,
Linux 2.6.38.3-grsec 

I am killing the mysql processes when the load is too high using the following commands:

killall -9 httpd php
mysqladmin pr | awk {'print$2'} | xargs -i mysqladmin kill {};

Could you please help me understand why the processes are hung up in the opening state though there are free resources on the server?

P.S. I googled on this issue, read some articles, searched through this forum with no success.

Thank you in advance for any assistance.

Update. top -c output:

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 
    24738 mysql     20   0 1480m 310m 4612 S  70  13   7:46.33 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql

Best Answer

A common possible cause for SQL requests "piling up" on a server is MySQL's/MyISAM's poor man's "locking mechanism" that even does not deserve its name. If you have a long-running SELECT query, any INSERT, UPDATE or DELETE query writing to the tables that are used/locked by the long-running SELECT would cause a "congestion" and put a stop to any consecutive queries using any of the affected tables.

Use the SHOW FULL PROCESSLIST command in MySQL to check if you have many queries in "Locked" state when you hit this condition again.