Take the 2-minute tour ×
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
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
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

 
discard

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.