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 is 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 well) 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 advise you to test your intended platform before choosing it, if possible.
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-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 systems this switch is mandatory because the external locking does not work in any case.
The --skip-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 is not yet safe.
The only case when you can’t use --skip-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-locking.
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 query_buffer_size current value: 0 record_buffer current value: 131072 record_rnd_buffer current value: 131072 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
If a mysqld server is 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 of all variables in the SHOW VARIABLES section of this manual. See Section 4.5.6.4.
You can also see some statistics from a running server by issuing the command SHOW STATUS. See Section 4.5.6.3.
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> safe_mysqld -O key_buffer=64M -O table_cache=256 \
-O sort_buffer=4M -O record_buffer=1M &If you have only 128M and only a few tables, but you still do a lot of sorting, you can use something like this:
shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M
If you have little memory and lots of connections, use something like this:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
-O record_buffer=100k &or even:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
-O table_cache=32 -O record_buffer=8k -O net_buffer=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 record_rnd_buffer 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 these 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.
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 of 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 indicating 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 connecting 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.
If you compile with --with-debug=full, you will lose 20% for most queries, but some queries may take substantially longer (the MySQL benchmarks ran 35% slower). If you use --with-debug, you will only lose 15%. By starting a mysqld version compiled with --with-debug=full with --skip-safemalloc the end result should be close to when configuring with --with-debug.
On a Sun SPARCstation 20, SunPro C++ 4.2 is 5% faster than gcc 2.95.2.
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 MySQL 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.
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 5.5.2.
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 4G 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 record_rnd_buffer).
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 SQL_BIG_TABLES in the client program. See Section 5.5.6. 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 allocate a sort buffer and 0-2 temporary files depending on the result set size. See Section A.4.4.
Almost all parsing and calculating are done in a local memory store. No memory overhead is needed for small items, and the normal slow memory allocation and freeing are avoided. Memory is allocated only for unexpectedly large strings (this is done with malloc( ) and free( )).
Each index file is opened once and 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 size 3 * n are 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 table handlers 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.
Table handlers 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 5.4.7.
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.
When a new thread connects to mysqld, mysqld will span 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 these 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 are 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 lookup with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompiling 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.
SET [OPTION] SQL_VALUE_OPTION= value, ...
SET OPTION sets various options that affect the operation of the server or your client. Any option you set remains in effect until the current session ends, or until you set the option to a different value.
- 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.ccfile 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"); or 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), 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 6.7.1. If set to 0, you have to use COMMIT / ROLLBACK to accept/revoke that transaction. See Section 6.7.1. Note that when you change from not AUTOCOMMIT mode to AUTOCOMMIT mode, MySQL will do an automatic COMMIT on any open transactions.
- SQL_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).
- 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. 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).
- 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.
- SQL_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.
- SQL_MAX_JOIN_SIZE = value | DEFAULT
Don’t allow SELECTs that will probably need to examine more than value row combinations. 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=#.
- SQL_QUERY_CACHE_TYPE = OFF | ON | DEMAND , SQL_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 will be done to the standard log for this client, if the client has the process privilege. This does not affect the update log!
- SQL_LOG_UPDATE = 0 | 1
If set to 0, no logging will be done to the update log for the client, if the client has the process privilege. This does not affect the standard log!
- 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. See Section 4.5.6.8.
- TIMESTAMP = timestamp_value | DEFAULT
Set the time for this client. This is used to get the original timestamp if you use the update 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 update 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 update log.
See Section 6.7.3.
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
