Very slow mysql

Question

Hi guy,

I have a big problem with my server mysql. All worked fine but since on week, it is very slow. Each query is slow (more 20 secondes some times). I have changed nothing in my configuration.

Someone can help me to know why my server is now slow ?

Thanks.

Here is my my.cnf:

[

mysqld_safe]
socket   = /var/run/mysqld/mysqld.sock
nice     = 0

[mysqld]
#
# * Basic Settings
#
user     = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port     = 3306
basedir  = /usr
datadir  = /var/lib/mysql
tmpdir   = /tmp
language    = /usr/share/mysql/english
#join_buffer_size   = 128.0K
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address     = 127.0.0.1
# * Fine Tuning
#
key_buffer   = 16M
max_allowed_packet  = 16M
max_heap_table_size = 64M
tmp_table_size   = 64M

thread_stack     = 128K
thread_cache_size   = 8
#max_connections        = 100
table_cache            = 400 
join_buffer_size    = 2000K
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log     = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
#server-id   = 1
log_bin   = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db    = include_database_name
#binlog_ignore_db   = include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
innodb_buffer_pool_size = 42M
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer   = 16M

Answer 1

You could try to narrow down the issues by monitoring performance with some tools, for unix systems there is also a tool called mytop, usually available through package management systems ...

Also, try to create some indexes on tables to decrease access latency.

Answer 2

Not really an answer, but how long is mysql's uptime? Maybe restarting it could help?

If restarting doesn't help - run a check on all active databases/tables, some could be corrupted and causing trouble.

Also, what is the load of your mysql server system? Something could have eaten all the RAM and caused heavy swapping. You can use htop and free and some other tools to monitor CPU/RAM use.

Answer 3

Assuming that nothing has changed resource wise (cpu, memory, disk activity) - one area to look into might be indexes on the queries. On the assumption that data is being entered/updated constantly, then queries might slow down over time as more and more data is entered - especially if no indexes have been set. Without further information as to your whole set up this kind of question is very difficult to answer.

Answer 4

What kind of application is this DB being used for?

This may be a long shot, but I once kept watch over a server with an app that imported emails for a support application. After a while, I noticed that the app started to slow down. Turns out that the DB grew to a huge size due to spam emails growing one of the tables out. Cleaned them out and it perked up noticeably.

Make sure something hasn't gone wrong and flooded the DB with junk data. Not a likely cause, but it never hurts to check.

In any case, make sure you're not hitting swap to heavily (as suggested elsewhere).

Answer 5

Thanks for your answers.

I try to restart my mysql server but nothing change. About the cpu and ram, nothing wrong, my cpu is around 2% and I have 100Mo free for 1 Go installed.

I run OPTIMIZE TABLE on all tables but the problem is still here.

But, I analyse I/O hard disk and my hard disk write many things every time. I have an average of 75.33 requests/secondes which write on my disk.

The problem is may be here. How know what program write every time ?