How to optimize MySQL DataBase

MySQL optimization. Here we will give some quick help on optimizing MySQL database performance.

Requirements: MySQL.

One of the factors with the biggest impact on database performance is not the MySQL settings, but your queries! Make sure you have optimized all your queries first, and have created the right indexes on your tables for MySQL to use.

A useful MySQL command for this is EXPLAIN. If you use EXPLAIN SELECT * FROM table WHERE field="something" , MySQL will tell you how many rows it needs to search, and what index it can use for this, among other things.

After you have optimized your queries and created indexes, you can start tweaking your my.cnf MySQL configuration file. Keep in mind that if you optimize or change something you should be able to benchmark it to make sure it will actually increase the performance.

Here is a sample my.cnf file (MySQL 4.1) for use on a dual processor server with 2GB of RAM memory:

# The following options will be passed to all MySQL clients
[client]
socket=/tmp/mysql.sock


# The MySQL server
[mysqld]
tmpdir=/tmp
socket=/tmp/mysql.sock
skip-locking
skip-networking
skip-name-resolve

server-id=1

max_connections=500
key_buffer_size=384M
max_allowed_packet=16M
table_cache=256
sort_buffer_size=2M
read_buffer_size=2M
join_buffer_size=2M

# if you are performing GROUP BY or ORDER BY queries on tables that
# are much larger than your available memory, you should increase
# the value of read_rnd_buffer_size to speed up the reading of rows
# following sorting operations.
# but: change the session variable only from within those clients
# that need to run large queries
read_rnd_buffer_size=2M

max_heap_table_size=256M
tmp_table_size=256M

myisam_sort_buffer_size=64M

# increase until threads_created doesnt grow anymore
thread_cache=256

query_cache_type=1
query_cache_limit=1M
query_cache_size=32M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4


[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M

[mysqlhotcopy]
interactive-timeout
You can use the SHOW STATUS MySQL command to monitor some of the variables (like Threads_created , Created_tmp_disk_tables , Created_tmp_tables and so on).

Posted in: