Optimising the MySQL Server
===========================
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'
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:
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. *Note SHOW VARIABLES::.
You can also see some statistics from a running server by issuing the
command `SHOW STATUS'. *Note 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.
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 &
sorting
is done.
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.
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'.
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).
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.
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.
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.
*Note 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. *Note `SET' Syntax: SET
OPTION. 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. *Note 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.
* 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. *Note Table cache::.
* 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.
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.
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'.
`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.
@@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. *Note System Variables::.
`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. *Note 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. *Note Command-line
options::.
You can get a list of most variables with `SHOW VARIABLES'. *Note 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'. *Note System Variables::. *Note Command-line options::.
*Note 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!
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. *Note COMMIT::. If set to `0' you have to use `COMMIT' /
`ROLLBACK' to accept/revoke that transaction. *Note COMMIT::. 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 `SELECT's 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.
table. This option was before named
`SQL_LOW_PRIORITY_UPDATES'.
`MAX_JOIN_SIZE = value | DEFAULT'
Don't allow `SELECT's 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 `SELECT's 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. *Note `SHOW CREATE TABLE': SHOW
CREATE TABLE.
a Unix epoch timestamp, not a MySQL
timestamp.
`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.
[Назад] [Содержание] [Вперед]
| Главная |