Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?


Here is my my.cnf:

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

# * Basic Settings
user    	= mysql
pid-file    = /var/run/mysqld/
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
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    	=
# * 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
# 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.
# * 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.
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

max_allowed_packet  = 16M

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

key_buffer  	= 16M
share|improve this question
Can you elaborate on what queries are taking long? Inserts? Updates? a 30MB script is going to take a few seconds to execute. Also, can you please post the specs of the server? (OS, CPU, RAM ect) – Nathan Adams Nov 24 '09 at 14:34
up vote 0 down vote accepted

You should avoid thread_stack option in your config. While this option doesn't affect performance, it can cause problems.

This article might be useful.

share|improve this answer

Without understanding exactly what your workload is, no.

I recommend that you reproduce the problem on a non-production server, then you can safely try out things to fix it.

Your server is tuned for a tiny amount of ram. This is likely to produce poor performance if you have a database bigger than a toy.

I'm assuming your server is DEDICATED to being a database; you want to assign quite a lot of your ram to either innodb_buffer_pool or key_buffer, depending on whether your applications use innodb or myisam.

You should find out what the developers are using and get them to give you tuning tips.

Your database performance may have dropped because your data are getting bigger; this happens to most databases, so you'll want to contact your development team and get them to stop that happening, or to do capacity planning so you can handle growth in the future.

share|improve this answer is good for checking your MySQL settings are tuned to your actual usage. If it is a large database and you can't tune it to be fast enough (optimal database types, settings, hardware, etc) then it might be time to consider setting up mirroring.

share|improve this answer
yes, I already used it and I followed theses recommendations. – Kiva Nov 24 '09 at 16:23

try to turn of "log_slow_queries", this fills up logfile very quickly and eats log of ressources.

Also try to find out which of your queries may be slow because they do not use indexes by the parameter "log-queries-not-using-indexes" (see mysql manual).

You can use "EXPLAIN SELECT FROM ..." for your queries to find out which do not use indexes or which indexes can be improved. Caveat: no not use too many indexes, because on INSERT and UPDATE (and DELETE of course) they have to be updated too.

CU, arnep

share|improve this answer

I log slow query but the query "COMMIT;" take sometime 20 secondes. The database size is 30Mo, it's not a big database.

My website is a little website.

share|improve this answer
A 30MB database is not a small database. My MySQL backups are 50MB gziped and I have many users (web hosting company). – Nathan Adams Nov 24 '09 at 14:33
30MB in raw text, once gziped, my DB size is 2.2Mo – Kiva Nov 24 '09 at 16:27

is this VPS ? try to enable, delayed commits in innodb,

innodb_flush_log_at_trx_commit = 2

it's not safe, but you could try it in cause of big io load.

paste here output of vmstat 1

share|improve this answer

It could be server load, or lack of memory, or likely a poorly tuned my.cnf file. My.cnf needs to be set up to match your particular environment - server capacity, query profile, etc.

There are sevaral scripts you can run to help tune your MySQL settings to suit your particular environment and load. One such script is

For these scripts to give accurate results, your server needs to have been running for several days before running them. Even if it has not, some of the recommendations can be helpful.

share|improve this answer
I used too this script but the problem is still here – Kiva Nov 24 '09 at 16:29

I use your article ms and I had added theses value in my my.cnf:

    innodb_buffer_pool_size = 384M
#innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2

384M is 40% of my ram (1Go) I commented this value "innodb_log_file_size" because my server crash with this value. I don't understand why.

share|improve this answer

Your Answer


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.