Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
1  
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
add comment

8 Answers

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
add comment

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
add comment

mysqltuner.pl 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
add comment

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
add comment

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
add comment

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
add comment

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 tuning-primer.sh.

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
add comment

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
add comment

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.