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.

Since last week without a particular reason my website started to drastically slow down. I logged into MySQL and run show processlist which resulted in something like this:

mysql> show full processlist;
+------+------+-----------+---------+---------+------+-------+------------------+
| Id   | User | Host      | db      | Command | Time | State | Info             |
+------+------+-----------+---------+---------+------+-------+------------------+
| 2047 | web  | localhost | myDB    | Sleep   |   81 |       | NULL             |
| 2049 | web  | localhost | myDB    | Sleep   |   81 |       | NULL             |
| 2050 | web  | localhost | myDB    | Sleep   |   81 |       | NULL             |
| 2052 | web  | localhost | myDB    | Sleep   |   81 |       | NULL             |
| 2053 | web  | localhost | myDB    | Sleep   |   81 |       | NULL             |
| 2054 | web  | localhost | myDB    | Sleep   |   81 |       | NULL             |
| 2055 | web  | localhost | myDB    | Sleep   |   81 |       | NULL             |
...
| 2066 | web  | localhost | myDB    | Sleep   |   80 |       | NULL             |
| 2067 | web  | localhost | myDB    | Sleep   |   80 |       | NULL             |
| 2178 | web  | localhost | myDB    | Sleep   |   76 |       | NULL             |
+------+------+-----------+---------+---------+------+-------+------------------+
131 rows in set (0.00 sec)

When I restart MySQL I have no processes which is to be expected. When I refresh my website and quickly check processes I can see some queries which disappear after a moment. Everything is perfect but then after some time out of the blue again I get lots of processes like shown above. This of course freezes my website.

They seem to disappear after some time but before the value of wait_timeout and then appear again after some time with different Id's.

Does anyone have a clue what could be causing this?

My server's specs:
CentOS 5
MySQL version: 5.1.33-log Source distribution
RAM 8GB

part of my.cnf

[mysqld]
default-character-set   = utf8
character_set_server    = utf8
default-collation       = utf8_unicode_ci
collation_server        = utf8_unicode_ci
ft_min_word_len         = 3
port                    = 5689
socket                  = /tmp/mysql.sock
skip-locking
skip-name-resolve
key_buffer_size         = 256M
group_concat_max_len    = 2048
max_allowed_packet      = 10M
table_cache             = 512
table_definition_cache  = 512
sort_buffer_size        = 10M
net_buffer_length       = 8K
read_buffer_size        = 40M
read_rnd_buffer_size    = 10M
myisam_sort_buffer_size = 8M
long_query_time         = 1
net_read_timeout        = 30
net_write_timeout       = 60
log_slow_queries        = /var/log/slowqueries
query_cache_type        = 1
query_cache_limit       = 5MB
query_cache_size        = 134217728
max_heap_table_size     = 24MB
tmp_table_size          = 24MB
expire_logs_days        = 99
max_connections         = 500
init-connect            = 'SET NAMES latin1'
thread_cache_size       = 4
wait_timeout            = 600

I hope that will help one of you to point me in the right direction. I'm out of ideas.

EDIT The whole thing seems to be relevant to those MySQL bugs:
http://bugs.mysql.com/bug.php?id=8945
http://bugs.mysql.com/bug.php?id=6070
Unfortunately they're not solved :(

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Check the values of your mysql server. Are you using default users in mysql? I'd also take a look at two things:

  1. Be sure that only specific servers can connect to the mysql database. If you have a user that is connecting from % be sure that you have a good password (and good reason).

  2. Be sure that someone isn't sending queries to your application outside of the code requirements that you have in place to run the application. Someone may be injecting data into your database without even visiting your site.

Keep track of you active connections for a while by paying close attention to netstat. The wait_timeout is an indication that someone/something has queried your server. Keeping close track will help you discover who they are. Once you do that if you need to quickly block them without making drastic changes, use iptables.

One last thing... enable mysql logging briefly. Your server will take a hit but you'll see exactly what's going on with mysql.

edit the my.cnf file and add (or update) this:

log             = /var/log/mysql/mysql.log

EDIT: I made the preceding suggestions because the NULL value in the info field indicates that no statements are being executed.

share|improve this answer
    
Thanks for reminding me about netstat. I added log to my.cnf Now I have to wait... –  6bytes Jan 24 '10 at 23:53
    
mysql.log file shows all connections coming from localhost. I had users in DB that could connect from any host but I deleted them. Problem still happening :( –  6bytes Jan 25 '10 at 11:01
1  
Do you have anything else to go on? The table you listed above really seems fine since it looks like no statements are being executed at all (from the NULL in the statement field). The problem may lie elsewhere. –  Patrick R Jan 25 '10 at 13:48
    
That's the weird part. In normal state I can see statements get executed and disappear from the list but at some point out of nowhere I get about 131 NULL processes which prevent from running anything else. Any legitimate statement that appears alongside nulls gets Locked State and takes ages to execute. –  6bytes Jan 25 '10 at 15:24

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.