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.