So now you've compiled MySQL to be fast, and you know it's built to be fast. But, just as the Formula 1 car requires a big effort during the race to get to the finish — fuel, tires and driver all working smoothly — so should you be tweaking your database server for more speed. Many novice MySQL users are unaware that the server variables can be tweaked. What started out as a small database gets busier and busier, and they add more and more memory. But it's all in vain as the server doesn't even use this extra RAM. It's time to introduce the mysqld variables. You can view the variables by running:
from the command line, or by running:
from inside MySQL. You will see something like the following. In an untweaked system, most of the variables will probably be similar to those shown in this file here.
Together with looking at the variables, you'll need to look at the current system values. To do this, run:
from the command line, or:
from inside MySQL. The results will be something like the following:
+--------------------------+----------+ | Aborted_clients | 142 | | Aborted_connects | 5 | | Bytes_received | 9005619 | | Bytes_sent | 15444786 | | Connections | 794 | | Created_tmp_disk_tables | 1 | | Created_tmp_tables | 716 | | Created_tmp_files | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 27 | | Handler_read_first | 1534 | | Handler_read_key | 608840 | | Handler_read_next | 652228 | | Handler_read_prev | 164 | | Handler_read_rnd | 14143 | | Handler_read_rnd_next | 1133372 | | Handler_update | 90 | | Handler_write | 131624 | | Key_blocks_used | 6682 | | Key_read_requests | 2745899 | | Key_reads | 6026 | | Key_write_requests | 63925 | | Key_writes | 63790 | | Max_used_connections | 20 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 64 | | Open_files | 128 | | Open_streams | 0 | | Opened_tables | 517 | | Questions | 118245 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 2300 | | Select_range_check | 0 | | Select_scan | 642 | | Slave_running | OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 8 | | Sort_merge_passes | 0 | | Sort_range | 3582 | | Sort_rows | 16287 | | Sort_scan | 806 | | Table_locks_immediate | 82957 | | Table_locks_waited | 2 | | Threads_cached | 0 | | Threads_created | 793 | | Threads_connected | 1 | | Threads_running | 1 | | Uptime | 1662790 | +--------------------------+----------+
Fortunately, MySQL has made life a lot easier by providing default values that fit most systems. Simply using the correct default set of values can make a significant difference. You can set these variables in a file called:
which is usually placed in:
on Unix systems (DATADIR is the directory specified when the system was configured, not the datadir as listed in the variables above!), or C:\mysql\data\my.cnf on Windows systems. The file can be placed elsewhere if you have more than one MySQL server running, or want to specify different options down to a user level, but having one set of values per server is usually sufficient. The 4 sample files that MySQL supplies are:
my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf.
So, pump in my-huge.cnf, and let the good times roll, I hear you say. Well, not quite!
my-huge.cnf is recommended for systems that have at least
1GB memory, and run mainly MySQL (if your Web server and db server
are on the same machine, don't even think about using this set!)
my-large.cnf is for systems with slightly less memory
(512MB), and also mainly running MySQL.
tweaked for a system where MySQL and a Web server are running
together with around 128MB, or around 64MB with MySQL alone.
Lastly, and probably leastly if you're reading this article, is
my-small.cnf, running on a system with less than 64MB,
where MySQL can't hog too many of the resources.