We start with system-level factors, because some of these decisions must be made very early to achieve large performance gains. In other cases, a quick look at this section may suffice. However, it is always nice to have a sense of how much can be gained by changing things at this level.
The default operating system to use is very important! To get the best use of multiple-CPU machines, you should use Solaris (because its threads implementation works well) or Linux (because the 2.4 and later kernels have good SMP support). Note that older Linux kernels have a 2GB filesize limit by default. If you have such a kernel and a need for files larger than 2GB, you should get the Large File Support (LFS) patch for the ext2 filesystem. Other filesystems such as ReiserFS and XFS do not have this 2GB limitation.
Before using MySQL in production, we advise you to test it on your intended platform.
Other tips:
-
If you have enough RAM, you could remove all swap devices. Some operating systems use a swap device in some contexts even if you have free memory.
-
Use the
--skip-external-lockingMySQL option to avoid external locking. This option is on by default in MySQL 5.0.Note that the
--skip-external-lockingoption does not affect MySQL's functionality as long as you run only one server. Just remember to take down the server (or lock and flush the relevant tables) before you run myisamchk. On some systems this option is mandatory, because the external locking does not work in any case.The only case where you cannot use
--skip-external-lockingis if you run multiple MySQL servers (not clients) on the same data, or if you run myisamchk to check (not repair) a table without telling the server to flush and lock the tables first. Note that using multiple MySQL servers to access the same data concurrently is generally not recommended, except when using MySQL Cluster.You can still use
LOCK TABLESandUNLOCK TABLESeven if you are using--skip-external-locking.
You can determine the default buffer sizes used by the mysqld server using this command:
shell> mysqld --verbose --help
This command produces a list of all mysqld options and configurable system variables. The output includes the default variable values and looks something like this:
back_log 50 binlog_cache_size 32768 bulk_insert_buffer_size 8388608 connect_timeout 5 date_format (No default value) datetime_format (No default value) default_week_format 0 delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 expire_logs_days 0 flush_time 1800 ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (No default value) group_concat_max_len 1024 innodb_additional_mem_pool_size 1048576 innodb_autoextend_increment 8 innodb_buffer_pool_awe_mem_mb 0 innodb_buffer_pool_size 8388608 innodb_concurrency_tickets 500 innodb_file_io_threads 4 innodb_force_recovery 0 innodb_lock_wait_timeout 50 innodb_log_buffer_size 1048576 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_mirrored_log_groups 1 innodb_open_files 300 innodb_sync_spin_loops 20 innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8388600 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 long_query_time 10 lower_case_table_names 1 max_allowed_packet 1048576 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connect_errors 10 max_connections 100 max_delayed_threads 20 max_error_count 64 max_heap_table_size 16777216 max_join_size 4294967295 max_length_for_sort_data 1024 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 4294967295 multi_range_count 256 myisam_block_size 1024 myisam_data_pointer_size 6 myisam_max_extra_sort_file_size 2147483648 myisam_max_sort_file_size 2147483647 myisam_repair_threads 1 myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 optimizer_prune_level 1 optimizer_search_depth 62 preload_buffer_size 32768 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_type 1 query_cache_wlock_invalidate FALSE query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size 131072 read_only FALSE read_rnd_buffer_size 262144 div_precision_increment 4 record_buffer 131072 relay_log_purge TRUE relay_log_space_limit 0 slave_compressed_protocol FALSE slave_net_timeout 3600 slave_transaction_retries 10 slow_launch_time 2 sort_buffer_size 2097144 sync-binlog 0 sync-frm TRUE sync-replication 0 sync-replication-slave-id 0 sync-replication-timeout 10 table_cache 64 thread_cache_size 0 thread_concurrency 10 thread_stack 196608 time_format (No default value) tmp_table_size 33554432 transaction_alloc_block_size 8192 transaction_prealloc_size 4096 updatable_views_with_limit 1 wait_timeout 28800
If there is a mysqld server currently running, you can see what values it actually is using for the system variables by connecting to it and issuing this statement:
mysql> SHOW VARIABLES;
You can also see some statistical and status indicators for a running server by issuing this statement:
mysql> SHOW STATUS;
System variable and status information also can be obtained using mysqladmin:
shell> mysqladmin variables shell> mysqladmin extended-status
You can find a full description for all system and status variables in Sección 5.3.3, “Variables de sistema del servidor” and Sección 5.3.4, “Variables de estado del servidor”.
MySQL uses algorithms that are very scalable, so you can usually run with very little memory. However, normally you get better performance by giving MySQL more memory.
When tuning a MySQL server, the two most important variables to
configure are key_buffer_size and
table_cache. You should first feel confident
that you have these set appropriately before trying to change
any other variables.
The following examples indicate some typical variable values for different runtime configurations.
-
If you have at least 256MB of memory and many tables and want maximum performance with a moderate number of clients, you should use something like this:
shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \ --sort_buffer_size=4M --read_buffer_size=1M & -
If you have only 128MB of memory and only a few tables, but you still do a lot of sorting, you can use something like this:
shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
If there are very many simultaneous connections, swapping problems may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections.
-
With little memory and lots of connections, use something like this:
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \ --read_buffer_size=100K &Or even this:
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \ --table_cache=32 --read_buffer_size=8K \ --net_buffer_length=1K &
If you are doing GROUP BY or ORDER
BY operations 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 after sorting operations.
When you have installed MySQL, the
support-files directory contains some
different my.cnf sample files:
my-huge.cnf,
my-large.cnf,
my-medium.cnf, and
my-small.cnf. You can use these as a basis
for optimizing your system.
Note that if you specify an option on the command line for mysqld or mysqld_safe, it remains in effect only for that invocation of the server. To use the option every time the server runs, put it in an option file.
To see the effects of a parameter change, do something like this:
shell> mysqld --key_buffer_size=32M --verbose --help
The variable values are listed near the end of the output. Make
sure that the --verbose and
--help options are last. Otherwise, the
effect of any options listed after them on the command line are
not reflected in the output.
For information on tuning the InnoDB storage
engine, see Sección 15.11, “Consejos de afinamiento del rendimiento de InnoDB”.
The task of the query optimizer is to find an optimal plan for executing an SQL query. Because the difference in performance between “good” and “bad” plans can be orders of magnitude (that is, seconds versus hours or even days), most query optimizers, including that of MySQL, perform a more or less exhaustive search for an optimal plan among all possible query evaluation plans. For join queries, the number of possible plans investigated by the MySQL optimizer grows exponentially with the number of tables referenced in a query. For small numbers of tables (typically less than 7-10) this is not a problem. However, when bigger queries are submitted, the time spent in query optimization may easily become the major bottleneck in the server's performance.
MySQL 5.0.1 introduces a more flexible method for query optimization that allows the user to control how exhaustive the optimizer is in its search for an optimal query evaluation plan. The general idea is that the fewer plans that are investigated by the optimizer, the less time it spends in compiling a query. On the other hand, because the optimizer skips some plans, it may miss finding an optimal plan.
The behavior of the optimizer with respect to the number of plans it evaluates can be controlled via two system variables:
-
The
optimizer_prune_levelvariable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table. Our experience shows that this kind of “educated guess” rarely misses optimal plans, and may dramatically reduce query compilation times. That is why this option is on (optimizer_prune_level=1) by default. However, if you believe that the optimizer missed a better query plan, then this option can be switched off (optimizer_prune_level=0) with the risk that query compilation may take much longer. Notice that even with the use of this heuristic, the optimizer still explores a roughly exponential number of plans. -
The
optimizer_search_depthvariable tells how far into the “future” of each incomplete plan the optimizer should look in order to evaluate whether it should be expanded further. Smaller values ofoptimizer_search_depthmay result in orders of magnitude smaller query compilation times. For example, queries with 12, 13, or more tables may easily require hours and even days to compile ifoptimizer_search_depthis close to the number of tables in the query. At the same time, if compiled withoptimizer_search_depthequal to 3 or 4, the compiler may compile in less than a minute for the same query. If you are unsure of what a reasonable value is foroptimizer_search_depth, this variable can be set to 0 to tell the optimizer to determine the value automatically.
Most of the following tests were performed on Linux with the MySQL benchmarks, but they should give some indication for other operating systems and workloads.
You obtain the fastest executables when you link with
-static.
On Linux, it is best to compile the server with
pgcc and -O3. You need
about 200MB memory to compile sql_yacc.cc
with these options, because gcc or
pgcc needs a great deal of memory to make all
functions inline. You should also set CXX=gcc
when configuring MySQL to avoid inclusion of the
libstdc++ library, which is not needed. Note
that with some versions of pgcc, the
resulting binary runs only on true Pentium processors, even if
you use the compiler option indicating that you want the
resulting code to work on all x586-type processors (such as
AMD).
By using a better compiler and compilation options, you can obtain a 10-30% speed increase in applications. This is particularly important if you compile the MySQL server yourself.
When we tested both the Cygnus CodeFusion and Fujitsu compilers, neither was sufficiently bug-free to allow MySQL to be compiled with optimizations enabled.
The standard MySQL binary distributions are compiled with
support for all character sets. When you compile MySQL yourself,
you should include support only for the character sets that you
are going to use. This is controlled by the
--with-charset option to
configure.
Here is a list of some measurements that we have made:
-
If you use
pgccand compile everything with-O6, the mysqld server is 1% faster than with gcc 2.95.2. -
If you link dynamically (without
-static), the result is 13% slower on Linux. Note that you still can use a dynamically linked MySQL library for your client applications. It is the server that is most critical for performance. -
If you strip your mysqld binary with
strip mysqld, the resulting binary can be up to 4% faster. -
For a connection from a client to a server running on the same host, if you connect using TCP/IP rather than a Unix socket file, performance is 7.5% slower. (On Unix, if you connect to the hostname
localhost, MySQL uses a socket file by default.) -
For TCP/IP connections from a client to a server, connecting to a remote server on another host is 8-11% slower than connecting to a server on the same host, even for connections over 100Mb/s Ethernet.
-
When running our benchmark tests using secure connections (all data encrypted with internal SSL support) performance was 55% slower than with unencrypted connections.
-
If you compile with
--with-debug=full, most queries are 20% slower. Some queries may take substantially longer; for example, the MySQL benchmarks run 35% slower. If you use--with-debug(without=full), the speed decrease is only 15%. For a version of mysqld that has been compiled with--with-debug=full, you can disable memory checking at runtime by starting it with the--skip-safemallocoption. The execution speed should then be close to that obtained when configuring with--with-debug. -
On a Sun UltraSPARC-IIe, a server compiled with Forte 5.0 is 4% faster than one compiled with gcc 3.2.
-
On a Sun UltraSPARC-IIe, a server compiled with Forte 5.0 is 4% faster in 32-bit mode than in 64-bit mode.
-
Compiling with gcc 2.95.2 for UltraSPARC with the options
-mcpu=v8 -Wa,-xarch=v8plusagives 4% better performance. -
On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads on a single processor. With more load or CPUs, the difference should be larger.
-
Compiling on Linux-x86 using gcc without frame pointers (
-fomit-frame-pointeror-fomit-frame-pointer -ffixed-ebp) makes mysqld 1-4% faster.
Binary MySQL distributions for Linux that are provided by MySQL
AB used to be compiled with pgcc. We had to
go back to regular gcc due to a bug in
pgcc that would generate binaries that do not
run on AMD. We will continue using gcc until
that bug is resolved. In the meantime, if you have a non-AMD
machine, you can build a faster binary by compiling with
pgcc. The standard MySQL Linux binary is
linked statically to make it faster and more portable.
The following list indicates some of the ways that the mysqld server uses memory. Where applicable, the name of the system variable relevant to the memory use is given:
-
The key buffer (variable
key_buffer_size) is shared by all threads; other buffers used by the server are allocated as needed. See Sección 7.5.2, “Afinar parámetros del servidor”. -
Each connection uses some thread-specific space:
-
A stack (default 64KB, variable
thread_stack) -
A connection buffer (variable
net_buffer_length) -
A result buffer (variable
net_buffer_length)
The connection buffer and result buffer are dynamically enlarged up to
max_allowed_packetwhen needed. While a query is running, a copy of the current query string is also allocated. -
-
All threads share the same base memory.
-
Only compressed
MyISAMtables are memory mapped. This is because the 32-bit memory space of 4GB is not large enough for most big tables. When systems with a 64-bit address space become more common, we may add general support for memory mapping. -
Each request that performs a sequential scan of a table allocates a read buffer (variable
read_buffer_size). -
When reading rows in an arbitrary sequence (for example, following a sort), a random-read buffer (variable
read_rnd_buffer_size) may be allocated in order to avoid disk seeks. -
All joins are executed in a single pass, and most joins can be done without even using a temporary table. Most temporary tables are memory-based (
HEAP) tables. Temporary tables with a large record length (calculated as the sum of all column lengths) or that containBLOBcolumns are stored on disk.If an internal heap table exceeds the size of
tmp_table_size, MySQL 5.0 handles this automatically by changing the in-memory heap table to a disk-basedMyISAMtable as necessary. You can also increase the temporary table size by setting thetmp_table_sizeoption to mysqld, or by setting the SQL optionSQL_BIG_TABLESin the client program. See Sección 13.5.3, “Sintaxis deSET”. -
Most requests that perform a sort allocate a sort buffer and zero to two temporary files depending on the result set size. See Sección A.4.4, “Dónde almacena MySQL los archivos temporales”.
-
Almost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items, so the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings; this is done with
malloc()andfree(). -
For each
MyISAMtable that is opened, the index file is opened once; the data file is opened once for each concurrently running thread. For each concurrent thread, a table structure, column structures for each column, and a buffer of size3 *Nare allocated (whereNis the maximum row length, not countingBLOBcolumns). ABLOBcolumn requires five to eight bytes plus the length of theBLOBdata. TheMyISAMstorage engine maintains one extra row buffer for internal use. -
For each table having
BLOBcolumns, a buffer is enlarged dynamically to read in largerBLOBvalues. If you scan a table, a buffer as large as the largestBLOBvalue is allocated. -
Handler structures for all in-use tables are saved in a cache and managed as a FIFO. By default, the cache has 64 entries. If a table has been used by two running threads at the same time, the cache contains two entries for the table. See Sección 7.4.8, “Cómo abre y cierra tablas MySQL”.
-
A
FLUSH TABLESstatement or mysqladmin flush-tables command closes all tables that are not in use at once and marks all in-use tables to be closed when the currently executing thread finishes. This effectively frees most in-use memory.FLUSH TABLESdoes not return until all tables have been closed.
ps and other system status programs may
report that mysqld uses a lot of memory. This
may be caused by thread stacks on different memory addresses.
For example, the Solaris version of ps counts
the unused memory between stacks as used memory. You can verify
this by checking available swap with swap -s.
We test mysqld with several memory-leakage
detectors (both commercial and open source), so there should be
no memory leaks.
When a new client connects to mysqld, mysqld spawns a new thread to handle the request. This thread first checks whether the hostname is in the hostname cache. If not, the thread attempts to resolve the hostname:
-
If the operating system supports the thread-safe
gethostbyaddr_r()andgethostbyname_r()calls, the thread uses them to perform hostname resolution. -
If the operating system doesn't support the thread-safe calls, the thread locks a mutex and calls
gethostbyaddr()andgethostbyname()instead. In this case, no other thread can resolve hostnames that are not in the hostname cache until the first thread unlocks the mutex.
You can disable DNS hostname lookups by starting
mysqld with the
--skip-name-resolve option. However, in this
case, you can use only IP numbers in the MySQL grant tables.
If you have a very slow DNS and many hosts, you can get more
performance by either disabling DNS lookups with
--skip-name-resolve or by increasing the
HOST_CACHE_SIZE define (default value: 128)
and recompiling mysqld.
You can disable the hostname cache by starting the server with
the --skip-host-cache option. To clear the
hostname cache, issue a FLUSH HOSTS statement
or execute the mysqladmin flush-hosts
command.
If you want to disallow TCP/IP connections entirely, start
mysqld with the
--skip-networking option.