5.5 Optimising the MySQL Server

5.5.1 System/Compile Time and Startup Parameter Tuning

We start with the system level things since some of these decisions have to be made very early. In other cases a fast look at this part may suffice because it not that important for the big gains. However, it is always nice to have a feeling about how much one could gain by changing things at this level.

The default OS to use is really important! To get the most use of multiple-CPU machines one should use Solaris (because the threads works really nice) or Linux (because the 2.2 kernel has really good SMP support). Also on 32-bit machines Linux has a 2G file-size limit by default. Hopefully this will be fixed soon when new filesystems are released (XFS/Reiserfs). If you have a desperate need for files bigger than 2G on Linux-intel 32 bit, you should get the LFS patch for the ext2 filesystem.

Because we have not run MySQL in production on that many platforms, we advice you to test your intended platform before choosing it, if possible.

Other tips:

• If you have enough RAM, you could remove all swap devices. Some operating systems will use a swap device in some contexts even if you have free memory.
• Use the --skip-external-locking MySQL option to avoid external locking. Note that this will not impact MySQL's functionality as long as you only run one server. Just remember to take down the server (or lock relevant parts) before you run myisamchk. On some system this switch is mandatory because the external locking does not work in any case.

  The --skip-external-locking option is on by default when compiling with MIT-pthreads, because flock() isn't fully supported by MIT-pthreads on all platforms. It's also on default for Linux as Linux file locking are not yet safe.

  The only case when you can't use --skip-external-locking is if you run multiple MySQL servers (not clients) on the same data, or run myisamchk on the table without first flushing and locking the mysqld server tables first.

  You can still use LOCK TABLES/UNLOCK TABLES even if you are using --skip-external-locking

5.5.2 Tuning Server Parameters

You can get the default buffer sizes used by the mysqld server with this command:

shell> mysqld --help

This command produces a list of all mysqld options and configurable variables. The output includes the default values and looks something like this:

Possible variables for option --set-variable (-O) are:
back_log                 current value: 5
bdb_cache_size           current value: 1048540
binlog_cache_size        current value: 32768
connect_timeout          current value: 5
delayed_insert_timeout   current value: 300
delayed_insert_limit     current value: 100
delayed_queue_size       current value: 1000
flush_time               current value: 0
interactive_timeout      current value: 28800
join_buffer_size         current value: 131072
key_buffer_size          current value: 1048540
lower_case_table_names   current value: 0
long_query_time          current value: 10
max_allowed_packet       current value: 1048576
max_binlog_cache_size    current value: 4294967295
max_connections          current value: 100
max_connect_errors       current value: 10
max_delayed_threads      current value: 20
max_heap_table_size      current value: 16777216
max_join_size            current value: 4294967295
max_sort_length          current value: 1024
max_tmp_tables           current value: 32
max_write_lock_count     current value: 4294967295
myisam_sort_buffer_size  current value: 8388608
net_buffer_length        current value: 16384
net_retry_count          current value: 10
net_read_timeout         current value: 30
net_write_timeout        current value: 60
read_buffer_size         current value: 131072
read_rnd_buffer_size     current value: 262144
slow_launch_time         current value: 2
sort_buffer              current value: 2097116
table_cache              current value: 64
thread_concurrency       current value: 10
tmp_table_size           current value: 1048576
thread_stack             current value: 131072
wait_timeout             current value: 28800

Please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own.

If there is a mysqld server currently running, you can see what values it actually is using for the variables by executing this command:

shell> mysqladmin variables

You can find a full description for all variables in the SHOW VARIABLES section in this manual. See section SHOW VARIABLES.

You can also see some statistics from a running server by issuing the command SHOW STATUS. See section SHOW STATUS.

MySQL uses algorithms that are very scalable, so you can usually run with very little memory. If you, however, give MySQL more memory, you will normally also get better performance.

When tuning a MySQL server, the two most important variables to use are key_buffer_size and table_cache. You should first feel confident that you have these right before trying to change any of the other variables.

If you have much memory (>=256M) and many tables and want maximum performance with a moderate number of clients, you should use something like this:

shell> mysqld_safe -O key_buffer=64M -O table_cache=256 \
           -O sort_buffer=4M -O read_buffer_size=1M &

If you have only 128M and only a few tables, but you still do a lot of sorting, you can use something like:

shell> mysqld_safe -O key_buffer=16M -O sort_buffer=1M

If you have little memory and lots of connections, use something like this:

shell> mysqld_safe -O key_buffer=512k -O sort_buffer=100k \
           -O read_buffer_size=100k &

or even:

shell> mysqld_safe -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O read_buffer_size=8k -O net_buffer_length=1K &

If you are doing a GROUP BY or ORDER BY on files that are much bigger than your available memory you should increase the value of read_rnd_buffer_size to speed up the reading of rows after the sorting is done.

When you have installed MySQL, the `support-files' directory will contain some different `my.cnf' example files, `my-huge.cnf', `my-large.cnf', `my-medium.cnf', and `my-small.cnf', you can use as a base to optimise your system.

If there are very many 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, of course.

Note that if you change an option to mysqld, it remains in effect only for that instance of the server.

To see the effects of a parameter change, do something like this:

shell> mysqld -O key_buffer=32m --help

Make sure that the --help option is last; otherwise, the effect of any options listed after it on the command-line will not be reflected in the output.

5.5.3 How Compiling and Linking Affects the Speed of MySQL

Most of the following tests are done on Linux with the MySQL benchmarks, but they should give some indication for other operating systems and workloads.

You get the fastest executable when you link with -static.

On Linux, you will get the fastest code when compiling with pgcc and -O3. To compile `sql_yacc.cc' with these options, you need about 200M memory because gcc/pgcc needs a lot of memory to make all functions inline. You should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library (it is not needed). Note that with some versions of pgcc, the resulting code will only run on true Pentium processors, even if you use the compiler option that you want the resulting code to be working on all x586 type processors (like AMD).

By just using a better compiler and/or better compiler options you can get a 10-30% speed increase in your application. This is particularly important if you compile the SQL server yourself!

We have tested both the Cygnus CodeFusion and Fujitsu compilers, but when we tested them, neither was sufficiently bug free to allow MySQL to be compiled with optimisations on.

When you compile MySQL you should only include support for the character sets that you are going to use. (Option --with-charset=xxx.) The standard MySQL binary distributions are compiled with support for all character sets.

Here is a list of some measurements that we have done:

• If you use pgcc and 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 dynamic linked MySQL library. It is only the server that is critical for performance.
• If you strip your mysqld binary with strip libexec/mysqld, the resulting binary can be up to 4% faster.
• If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower on the same computer. (If you are connection to localhost, MySQL will, by default, use sockets.)
• If you connect using TCP/IP from another computer over a 100M Ethernet, things will be 8-11% slower.
• When running our benchmark tests using secure connections (all data encrypted with internal SSL support) things were 55% slower.
• If you compile with --with-debug=full, you will lose 20% for most queries. Some queries may take substantially longer (for example, the MySQL benchmarks ran 35% slower). If you use --with-debug, then you will only lose 15%. For a mysqld version that has been compiled with --with-debug=full, you can disable memory checking at runtime by starting it with the --skip-safemalloc option. The end result in this case should be close to when configuring with --with-debug.
• On a Sun UltraSPARC-IIe, Forte 5.0 is 4% faster than gcc 3.2
• On a Sun UltraSPARC-IIe, 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 option -mcpu=v8 -Wa,-xarch=v8plusa gives 4% more performance.
• On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads on a single processor. With more load/CPUs the difference should get bigger.
• Running with --log-bin makes mysqld 1% slower.
• Compiling on Linux-x86 using gcc without frame pointers -fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp makes mysqld 1-4% faster.

The MySQL-Linux distribution provided by MySQL AB used to be compiled with pgcc, but we had to go back to regular gcc because of a bug in pgcc that would generate the code that does 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 get a faster binary by compiling with pgcc. The standard MySQL Linux binary is linked statically to get it faster and more portable.

5.5.4 How MySQL Uses Memory

The following list indicates some of the ways that the mysqld server uses memory. Where applicable, the name of the server 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 section Tuning Server Parameters.
• Each connection uses some thread-specific space: A stack (default 64K, variable thread_stack), a connection buffer (variable net_buffer_length), and a result buffer (variable net_buffer_length). The connection buffer and result buffer are dynamically enlarged up to max_allowed_packet when needed. When a query is running, a copy of the current query string is also allocated.
• All threads share the same base memory.
• Only the compressed ISAM / MyISAM tables are memory mapped. This is because the 32-bit memory space of 4 GB 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 doing a sequential scan over a table allocates a read buffer (variable record_buffer).
• When reading rows in 'random' order (for example after a sort) a random-read buffer is allocated to avoid disk seeks. (variable read_rnd_buffer_size).
• All joins are done in one 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 big record length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk.

  One problem in MySQL versions before Version 3.23.2 is that if a HEAP table exceeds the size of tmp_table_size, you get the error The table tbl_name is full. In newer versions this is handled by automatically changing the in-memory (HEAP) table to a disk-based (MyISAM) table as necessary. To work around this problem, you can increase the temporary table size by setting the tmp_table_size option to mysqld, or by setting the SQL option BIG_TABLES in the client program. See section SET Syntax. In MySQL Version 3.20, the maximum size of the temporary table was record_buffer*16, so if you are using this version, you have to increase the value of record_buffer. You can also start mysqld with the --big-tables option to always store temporary tables on disk. However, this will affect the speed of many complicated queries.

• Most requests doing a sort allocates a sort buffer and 0-2 temporary files depending on the result set size. See section Where MySQL Stores Temporary Files.
• Almost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items and the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings (this is done with malloc() and free()).
• Each index file is opened once and the datafile is opened once for each concurrently running thread. For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 * n is allocated (where n is the maximum row length, not counting BLOB columns). A BLOB uses 5 to 8 bytes plus the length of the BLOB data. The ISAM/MyISAM storage engines will use one extra row buffer for internal usage.
• For each table having BLOB columns, a buffer is enlarged dynamically to read in larger BLOB values. If you scan a table, a buffer as large as the largest BLOB value is allocated.
• Storage engines for all in-use tables are saved in a cache and managed as a FIFO. Normally 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 section How MySQL Opens and Closes Tables.
• A mysqladmin flush-tables command closes all tables that are not in use and marks all in-use tables to be closed when the currently executing thread finishes. This will effectively free most in-use memory.

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 have tested mysqld with commercial memory-leakage detectors, so there should be no memory leaks.

5.5.5 How MySQL uses DNS

When a new thread connects to mysqld, mysqld will spawn a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.

If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.

You can disable DNS host lookup by starting mysqld with --skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.

If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.

You can disable the hostname cache with --skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.

If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with --skip-networking.

5.5.6 SET Syntax

SET [GLOBAL | SESSION] sql_variable=expression, [[GLOBAL | SESSION] sql_variable=expression...]

SET sets various options that affect the operation of the server or your client.

The following examples shows the different syntaxes one can use to set variables:

In old MySQL versions we allowed the use of the SET OPTION syntax, but this syntax is now deprecated.

In MySQL 4.0.3 we added the GLOBAL and SESSION options and access to most important startup variables.

LOCAL can be used as a synonym for SESSION.

If you set several variables on the same command line, the last used GLOBAL | SESSION mode is used.

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

The @@variable_name syntax is supported to make MySQL syntax compatible with some other databases.

The different system variables one can set are described in the system variable section of this manual. See section System Variables.

If you are using SESSION (the default) the option you set remains in effect until the current session ends, or until you set the option to a different value. If you use GLOBAL, which require the SUPER privilege, the option is remembered and used for new connections until the server restarts. If you want to make an option permanent, you should set it in one of the MySQL option files. See section `my.cnf' Option Files.

To avoid wrong usage MySQL will give an error if you use SET GLOBAL with a variable that can only be used with SET SESSION or if you are not using SET GLOBAL with a global variable.

If you want to set a SESSION variable to the GLOBAL value or a GLOBAL value to the MySQL default value, you can set it to DEFAULT.

SET max_join_size=DEFAULT;

This is identical to:

SET @@session.max_join_size=@@global.max_join_size;

If you want to restrict the maximum value a startup option can be set to with the SET command, you can specify this by using the --maximum-variable-name command line option. See section mysqld Command-line Options.

You can get a list of most variables with SHOW VARIABLES. See section SHOW VARIABLES. You can get the value for a specific value with the @@[global.|local.]variable_name syntax:

SHOW VARIABLES like "max_join_size";
SHOW GLOBAL VARIABLES like "max_join_size";
SELECT @@max_join_size, @@global.max_join_size;

Here follows a description of the variables that uses a the variables that uses a non-standard SET syntax and some of the other variables. The other variable definitions can be found in the system variable section, among the startup options or in the description of SHOW VARIABLES. See section System Variables. See section mysqld Command-line Options. See section SHOW VARIABLES.

CHARACTER SET character_set_name | DEFAULT

This maps all strings from and to the client with the given mapping. Currently the only option for character_set_name is cp1251_koi8, but you can easily add new mappings by editing the `sql/convert.cc' file in the MySQL source distribution. The default mapping can be restored by using a character_set_name value of DEFAULT.

Note that the syntax for setting the CHARACTER SET option differs from the syntax for setting the other options.

PASSWORD = PASSWORD('some password')

Set the password for the current user. Any non-anonymous user can change his own password!

PASSWORD FOR user = PASSWORD('some password')

Set the password for a specific user on the current server host. Only a user with access to the mysql database can do this. The user should be given in user@hostname format, where user and hostname are exactly as they are listed in the User and Host columns of the mysql.user table entry. For example, if you had an entry with User and Host fields of 'bob' and '%.loc.gov', you would write:

mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");

Which is equivalent to:

mysql> UPDATE mysql.user SET password=PASSWORD("newpass") -> WHERE user="bob" AND host="%.loc.gov";

SQL_AUTO_IS_NULL = 0 | 1

If set to 1 (default) then one can find the last inserted row for a table with an AUTO_INCREMENT column with the following construct: WHERE auto_increment_column IS NULL. This is used by some ODBC programs like Access.

AUTOCOMMIT= 0 | 1

If set to 1 all changes to a table will be done at once. To start a multi-command transaction, you have to use the BEGIN statement. See section START TRANSACTION, COMMIT, and ROLLBACK Syntax. If set to 0 you have to use COMMIT / ROLLBACK to accept/revoke that transaction. See section START TRANSACTION, COMMIT, and ROLLBACK Syntax. Note that when you change from not AUTOCOMMIT mode to AUTOCOMMIT mode, MySQL will do an automatic COMMIT on any open transactions.

BIG_TABLES = 0 | 1

If set to 1, all temporary tables are stored on disk rather than in memory. This will be a little slower, but you will not get the error The table tbl_name is full for big SELECT operations that require a large temporary table. The default value for a new connection is 0 (that is, use in-memory temporary tables). This option was before named SQL_BIG_TABLES. In MySQL 4.0 you should normally never need this flag as MySQL will automatically convert in memory tables to disk based ones if need.

SQL_BIG_SELECTS = 0 | 1

If set to 0, MySQL will abort if a SELECT is attempted that probably will take a very long time, which is defined as if the number of examined rows is probably going to be bigger than MAX_JOIN_SIZE. This is useful when an inadvisable WHERE statement has been issued. A big query is defined as a SELECT that probably will have to examine more than max_join_size rows. The default value for a new connection is 1 (which will allow all SELECT statements).

If you set MAX_JOIN_SIZE to another value than DEFAULT SQL_BIG_SELECTS will be set to 0.

SQL_BUFFER_RESULT = 0 | 1

SQL_BUFFER_RESULT will force the result from SELECTs to be put into a temporary table. This will help MySQL free the table locks early and will help in cases where it takes a long time to send the result set to the client.

LOW_PRIORITY_UPDATES = 0 | 1

If set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECT or LOCK TABLE READ on the affected table. This option was before named SQL_LOW_PRIORITY_UPDATES.

MAX_JOIN_SIZE = value | DEFAULT

Don't allow SELECTs that will probably need to examine more than value row combinations or is likely to do more than value disk seeks. By setting this value, you can catch SELECTs where keys are not used properly and that would probably take a long time. Setting this to a value other than DEFAULT will reset the SQL_BIG_SELECTS flag. If you set the SQL_BIG_SELECTS flag again, the SQL_MAX_JOIN_SIZE variable will be ignored. You can set a default value for this variable by starting mysqld with -O max_join_size=#. This option was before named SQL_MAX_JOIN_SIZE.

Note that if the result of the query is already in the query cache, the above check will not be made. Instead, MySQL will send the result to the client. Since the query result is already computed and it will not burden the server to send the result to the client.

QUERY_CACHE_TYPE = OFF | ON | DEMAND

QUERY_CACHE_TYPE = 0 | 1 | 2

Set query cache setting for this thread.

Option	Description
0 or OFF	Don't cache or retrieve results.
1 or ON	Cache all results except SELECT SQL_NO_CACHE ... queries.
2 or DEMAND	Cache only SELECT SQL_CACHE ... queries.

SQL_SAFE_UPDATES = 0 | 1

If set to 1, MySQL will abort if an UPDATE or DELETE is attempted that doesn't use a key or LIMIT in the WHERE clause. This makes it possible to catch wrong updates when creating SQL commands by hand.

SQL_SELECT_LIMIT = value | DEFAULT

The maximum number of records to return from SELECT statements. If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of SQL_SELECT_LIMIT. The default value for a new connection is "unlimited." If you have changed the limit, the default value can be restored by using a SQL_SELECT_LIMIT value of DEFAULT.

SQL_LOG_OFF = 0 | 1

If set to 1, no logging is done to the standard log for this client, if the client has the SUPER privilege.

SQL_LOG_BIN = 0 | 1

If set to 0, no logging is done to the binary log for the client, if the client has the SUPER privilege.

SQL_LOG_UPDATE = 0 | 1

If set to 0, no logging is done to the update log for the client, if the client has the SUPER privilege. This variable is deprecated starting from version 5.0.

SQL_QUOTE_SHOW_CREATE = 0 | 1

If set to 1, SHOW CREATE TABLE will quote table and column names. This is on by default, for replication of tables with fancy column names to work. SHOW CREATE TABLE.

TIMESTAMP = timestamp_value | DEFAULT

Set the time for this client. This is used to get the original timestamp if you use the binary log to restore rows. timestamp_value should be a Unix epoch timestamp, not a MySQL timestamp.

LAST_INSERT_ID = #

Set the value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a command that updates a table.

INSERT_ID = #

Set the value to be used by the following INSERT or ALTER TABLE command when inserting an AUTO_INCREMENT value. This is mainly used with the binary log.

Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:45