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 just updated to a 8gb dedicated server from a 3gb vps that can no longer handle my site. I have built a search engine using sphinx full-text search engine and receive 100k to 200k queries a day "a lot of which are googlebot etc".

Anyway I need help configuring MySQL to a very heavily database driven site. The database is about 10 gigs and data is constantly being queried and inserted. Can anyone recommend some settings to help me get the best performance I can out of MySQL on this new 8gb server? I would really appreciate the help.

I am using MyISAM tables.

Here is my my.cnf file. Can anyone tell me what I should do or what you would do?

The specifications of the server are:

  • Quad-Core Xeon X3220
  • 8 GB DDR2
  • 2x 500 GB SATA2

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock


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

[mysqld]

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking

#bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 256M
max_allowed_packet      = 16M
sort_buffer_size        = 32M
#mysiam_sort_buffer_size = 32M
tmp_table_size          = 128M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 500
table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 1M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

log_error                = /var/log/mysql/error.log
# 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.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * 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!
#
# * 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

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/




share|improve this question
    
Is this server dedicated to MySQL? –  HTTP500 Jan 9 '12 at 23:48
    
You might want to mention the specs of your server, as 8 GB does not say much about the memory, CPU and type of disks you have. This is critical for optimizing your server. –  Lucas Kauffman Jan 9 '12 at 23:51
    
no i run my php search scripts on it also. My main concern is mysql though. –  chris Jan 9 '12 at 23:51
    
Quad-Core Xeon X3220, 8gb ddr2, 2x 500gb sata2 if that helps. I believe the cpu is around 2.1 or 2.3ghz. –  chris Jan 9 '12 at 23:53
    
You also didn't say what kind of tables you have... InnoDB, MyISAM? –  HTTP500 Jan 10 '12 at 0:05
show 2 more comments

2 Answers

http://mysqltuner.pl/mysqltuner.pl is a excellent script that identifies any potential problems.

It is best to run it once MySQL has been running for at least an hour or so. If you have just restarted MySQL then it doesn't have a lot of statistics to work with.

wget http://mysqltuner.pl/mysqltuner.pl && perl mysqltuner.pl

share|improve this answer
add comment

Here you can find a several scripts which can be used to tune up your MySQL server

http://www.serveradminblog.com/2011/03/tuning-mysql-performance-howto-part-1/

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.