Tuning / Optimizing my.cnf file for MySQL

Had to do some fine tuning of MySQL 4.1.9 and here is what my.cnf file looks like for a 2GHz machine with 1GB of memory.

[mysqld]
socket=/path/to/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
# max_connections=500
# Reduced to 200 as memory will not be enough for 500 connections.
# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
# which is now: 64 + (1 + 1) * 200 = 464 MB
# max_connections = approx. MaxClients setting in httpd.conf file
# Default set to 100.
#max_connections=200
#interactive_timeout=180
interactive_timeout=100
#wait_timeout=180
#wait_timeout=100
# Reduced wait_timeout to prevent idle clients holding connections.
#wait_timeout=30
wait_timeout=15
connect_timeout=10
# max_connect_errors is set to 10 by default
#max_connect_errors=10
#table_cache=256
#table_cache=1024
# Checked opened tables and adjusted accordingly after running for a while.
table_cache=512
#tmp_table_size=32M by default
#thread_cache=128
# Reduced it to 32 to prevent memory hogging. Also, see notes below.
thread_cache=32
# key_buffer=258M
# Reduced it by checking current size of *.MYI files, see notes below.
key_buffer=128M
# Commented out the buffer sizes and keeping the default.
# sort_buffer_size=2M by default.
#sort_buffer_size=1M
# read_buffer_size=128K by default.
#read_buffer_size=1M
# 1Mb of read_rnd_buffer_size for 1GB RAM -- see notes below.
# read_rnd_buffer_size=256K by default.
#read_rnd_buffer_size=1M
# myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands.
# myisam_sort_buffer_size=8M by default.
#myisam_sort_buffer_size=64M
# thread_concurrency = 2 * (no. of CPU)
thread_concurrency=2
# log slow queries is a must. Many queries that take more than 2 seconds. 
# If so, then your tables need enhancement.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout 

[client]
socket=/path/to/mysql.sock

Below are notes on some of the important variables, I took down while tuning the config file.

  1. query_cache_size:
    • MySQL 4 provides one feature that can prove very handy - a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
  2. key_buffer_size:
    • The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
    • A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
    • If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.
  3. table_cache:
    • The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
  4. sort_buffer:
    • The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
  5. read_rnd_buffer_size:
    • The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
  6. thread_cache:
    • If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.
  7. tmp_table_size:
    • "Created_tmp_disk_tables" are the number of implicit temporary tables on disk created while executing statements and "created_tmp_tables" are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.

Additional reference material:

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

MySQL Memory Allocation

Another tool

For monitoring I advice to try mtop.

On CENTOS just run: yum -y install mtop.

Performance tune for my server configuration

I am using mysql Ver 5.0.24a with windows OS, 4gb ram, 79gb disk (drive)space, how to set buffer memory? thanks

MySQL memory calculation

This tool calculates the theoretical peak memory usage. read on...

MySQL memory calculation

Do I need the [mysqldump] [mysql] directives at all?

there is also myisamchk isamchk near the end.

MySQLTuner

MySQLTuner is a high-performance MySQL tuning script written in perl that will provide you with a snapshot of a MySQL server’s health. Based on the statistics gathered, specific recommendations will be provided that will increase a MySQL server’s efficiency and performance. The script gives you automated MySQL tuning that is on the level of what you would receive from a MySQL DBA.

The script has been derived from many of the ideas in Matthew Montgomery’s MySQL tuning primer script.

Find variables and mysql usage

1. To see VARIABLES:

mysql > show variables;

or from command line :

# mysqladmin variables

2. To see PROCESS / STATUS

mysql> show status;

or from command line:

# mysqladmin –i10 processlist extended-status

TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE

tmp_table_size is not enough as MySQL also looks at max_heap_table_size variable and uses lower value as a limit to for in memory temporary table after which it will be converted to MyISAM... read more of the discussion at mysqlperformanceblog.com

Tuning Primer

If you've tweaked your mysql configuration and run the database for over 48hrs, the Tuning Primer script will help further analyze your tweaks and provide some recommendations. The script has been inspired by MySQLARd and compatible with all versions of MySQL 3.23 and higher (including 5.1).

Currently it handles recommendations for the following:

  1. Slow Query Log
  2. Max Connections
  3. Worker Threads
  4. Memory Usage
  5. Key Buffer
  6. Query Cache
  7. Sort Buffer
  8. Joins
  9. Temp Tables
  10. Table (Open & Definition) Cache
  11. Table Scans (read_buffer)
  12. Table Locking
  13. Innodb Status

Nice

Thanks for this nice tool and info.
I see one issue?

TEMP TABLES
Current tmp_table_size = 64 M
30% of tmp tables created were disk based
Perhaps you should increase your tmp_table_size

mytop -- a top clone for mysql

If you want to see whats going on with the mysql database, I would suggest installing mytop, which is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server.

Comment