Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

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:



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,

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:

    24738 mysql     20   0 1480m 310m 4612 S  70  13   7:46.33 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql
share|improve this question
Whats your output of top? I had a similar problem with a cpanel server for the previous few days –  vassilis May 22 '11 at 10:40
Do you mean 'top' command? I updated the original post with the top output. –  Andrew May 22 '11 at 11:36

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
Thank you for your tip. Will do as soon as I'm on the shift and the load jumps up again. –  Andrew May 22 '11 at 12:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.