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
