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:
mysqladmin variables
from the command line, or by running:
SHOW VARIABLES
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:
mysqladmin extended-status
from the command line, or:
SHOW STATUS
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 |
+--------------------------+----------+
my.cnf
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:
my.cnf
which is usually placed in:
DATADIR/my.cnf
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. my-medium.cnf is
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.
IE 7
Firefox 2.0