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
socket=/tmp/mysql.sock # The MySQL server
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
quick max_allowed_packet=16M
no-auto-rehash
key_buffer=128M sort_buffer_size=128M read_buffer=2M write_buffer=2M
key_buffer=128M sort_buffer_size=128M read_buffer=2M write_buffer=2M
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).