Source code editor What Is Ajax
Source code editor What Is Ajax
↑
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 factors that apply at this level.
The 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.
Avoid external locking. Since MySQL 4.0, the default has been for external locking to be disabled on all systems. The --external-locking and --skip-external-locking options explicitly enable and disable external locking.
Note that disabling external locking 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 it is mandatory to disable external locking because it does not work, anyway.
The only case in which you cannot disable external locking is when 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.
The LOCK TABLES and UNLOCK TABLES statements use internal locking, so you can use them even if external locking is disabled.
You can determine the default buffer sizes used by the mysqld server using this command:
shell> mysqld --verbose --helpThis 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
For a mysqld server that is currently running, you can see the current values of its 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 variablesshell>mysqladmin extended-status
For a full description of all system and status variables, see Section 5.2.3, “System Variables”, and Section 5.2.5, “Status Variables”.
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=1MIf 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 performing 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 following sorting operations.
You can make use of the example option files included with your MySQL distribution; see Section 4.3.2.1, “Preconfigured Option Files”.
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 Section 14.2.11, “InnoDB Performance Tuning Tips”.
MySQL Enterprise. For expert advice on tuning system parameters subscribe to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.
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 larger 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_level variable 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, this option can be switched off (optimizer_prune_level=0) with the risk that query compilation may take much longer. Note that, even with the use of this heuristic, the optimizer still explores a roughly exponential number of plans.
The optimizer_search_depth variable tells how far into the “future” of each incomplete plan the optimizer should look to evaluate whether it should be expanded further. Smaller values of optimizer_search_depth may 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 if optimizer_search_depth is close to the number of tables in the query. At the same time, if compiled with optimizer_search_depth equal to 3 or 4, the optimizer may compile in less than a minute for the same query. If you are unsure of what a reasonable value is for optimizer_search_depth, this variable can be set to 0 to tell the optimizer to determine the value automatically.
The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.
The query cache is extremely useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.
Note: The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
Note: The query cache does not work in an environment where you have multiple mysqld servers updating the same MyISAM tables.
Note: The query cache is not used for server-side prepared statements. If you're using server-side prepared statements consider that these statement won't be satisfied by the query cache. See Section 22.2.4, “C API Prepared Statements”.
Some performance data for the query cache follows. These results were generated by running the MySQL benchmark suite on a Linux Alpha 2Ч500MHz system with 2GB RAM and a 64MB query cache.
If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower.
Searches for a single row in a single-row table are 238% faster with the query cache than without it. This can be regarded as close to the minimum speedup to be expected for a query that is cached.
To disable the query cache at server startup, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead. If you build MySQL from source, query cache capabilities can be excluded from the server entirely by invoking configure with the --without-query-cache option.
This section describes how the query cache works when it is operational. Section 7.5.4.3, “Query Cache Configuration”, describes how to control whether it is operational.
Incoming queries are compared to those in the query cache before parsing, so the following two queries are regarded as different by the query cache:
SELECT * FROMtbl_nameSelect * fromtbl_name
Queries must be exactly the same (byte for byte) to be seen as identical. In addition, query strings that are identical may be treated as different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.
Because comparison of a query against those in the cache occurs before parsing, the cache is not used for queries of the following types:
Prepared statements
Queries that are a subquery of an outer query
Queries executed within the body of a stored procedure, stored function, or trigger
Before a query result is fetched from the query cache, MySQL checks that the user has SELECT privilege for all databases and tables involved. If this is not the case, the cached result is not used.
If a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select. See Section 7.5.4.4, “Query Cache Status and Maintenance”.
If a table changes, all cached queries that use the table become invalid and are removed from the cache. This includes queries that use MERGE tables that map to the changed table. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE.
The query cache also works within transactions when using InnoDB tables.
In MySQL 5.0, queries generated by views are cached.
Before MySQL 5.0, a query that began with a leading comment could be cached, but could not be fetched from the cache. This problem is fixed in MySQL 5.0.
The query cache works for SELECT SQL_CALC_FOUND_ROWS ... queries and stores a value that is returned by a following SELECT FOUND_ROWS() query. FOUND_ROWS() returns the correct value even if the preceding query was fetched from the cache because the number of found rows is also stored in the cache. The SELECT FOUND_ROWS() query itself cannot be cached.
A query cannot be cached if it contains any of the functions shown in the following table:
BENCHMARK() | CONNECTION_ID() | CURDATE() |
CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |
CURTIME() | DATABASE() | ENCRYPT() with one parameter |
FOUND_ROWS() | GET_LOCK() | LAST_INSERT_ID() |
LOAD_FILE() | MASTER_POS_WAIT() | NOW() |
RAND() | RELEASE_LOCK() | SYSDATE() |
UNIX_TIMESTAMP() with no parameters | USER() |
A query also is not cached under these conditions:
It refers to user-defined functions (UDFs) or stored functions.
It refers to user variables.
It refers to tables in the mysql system database.
It is of any of the following forms:
SELECT ... IN SHARE MODE SELECT ... FOR UPDATE SELECT ... INTO OUTFILE ... SELECT ... INTO DUMPFILE ... SELECT * FROM ... WHERE autoincrement_col IS NULL
The last form is not cached because it is used as the ODBC workaround for obtaining the last insert ID value. See the MyODBC section of Chapter 23, Connectors.
It was issued as a prepared statement, even if no placeholders were employed. For example, the query used here is not cached:
char *my_sql_stmt = "SELECT a, b FROM table_c"; /* ... */ mysql_stmt_prepare(stmt, my_sql_stmt, strlen(my_sql_stmt));
It uses TEMPORARY tables.
It does not use any tables.
The user has a column-level privilege for any of the involved tables.
Two query cache-related options may be specified in SELECT statements:
Examples:
SELECT SQL_CACHE id, name FROM customer; SELECT SQL_NO_CACHE id, name FROM customer;
The have_query_cache server system variable indicates whether the query cache is available:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+ When using a standard MySQL binary, this value is always YES, even if query caching is disabled.
Several other system variables control query cache operation. These can be set in an option file or on the command line when starting mysqld. The query cache system variables all have names that begin with query_cache_. They are described briefly in Section 5.2.3, “System Variables”, with additional configuration information given here.
To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default size is 0, so the query cache is disabled by default.
MySQL Enterprise. For expert advice on configuring the query cache subscribe to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.
When using the Windows Configuration Wizard to install or configure MySQL, the default value for query_cache_size will be configured automatically for you based on the different configuration types available. When using the Windows Configuration Wizard, the query cache may be enabled (i.e. set to a non-zero value) due to the selected configuration. The query cache is also controlled by the setting of the query_cache_type variable. You should check the values of these variables as set in your my.ini file after configuration has taken place.
When you set query_cache_size to a non-zero value, keep in mind that the query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value too small, you'll get a warning, as in this example:
mysql>SET GLOBAL query_cache_size = 40000;Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 1282 Message: Query cache failed to set size 39936; new query cache size is 0 mysql>SET GLOBAL query_cache_size = 41984;Query OK, 0 rows affected (0.00 sec) mysql>SHOW VARIABLES LIKE 'query_cache_size';+------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_size | 41984 | +------------------+-------+
For the query cache to actually be able to hold any query results, its size must be set larger:
mysql>SET GLOBAL query_cache_size = 1000000;Query OK, 0 rows affected (0.04 sec) mysql>SHOW VARIABLES LIKE 'query_cache_size';+------------------+--------+ | Variable_name | Value | +------------------+--------+ | query_cache_size | 999424 | +------------------+--------+ 1 row in set (0.00 sec)
The query_cache_size will be aligned to the nearest 1024 byte block. The value reported may therefore be different from the value that you set.
If the query cache size is greater than 0, the query_cache_type variable influences how it works. This variable can be set to the following values:
A value of 0 or OFF prevents caching or retrieval of cached results.
A value of 1 or ON allows caching except of those statements that begin with SELECT SQL_NO_CACHE.
A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.
Setting the GLOBAL query_cache_type value determines query cache behavior for all clients that connect after the change is made. Individual clients can control cache behavior for their own connection by setting the SESSION query_cache_type value. For example, a client can disable use of the query cache for its own queries like this:
mysql> SET SESSION query_cache_type = OFF; To control the maximum size of individual query results that can be cached, set the query_cache_limit system variable. The default value is 1MB.
When a query is to be cached, its result (the data sent to the client) is stored in the query cache during result retrieval. Therefore the data usually is not handled in one big chunk. The query cache allocates blocks for storing this data on demand, so when one block is filled, a new block is allocated. Because memory allocation operation is costly (timewise), the query cache allocates blocks with a minimum size given by the query_cache_min_res_unit system variable. When a query is executed, the last result block is trimmed to the actual data size so that unused memory is freed. Depending on the types of queries your server executes, you might find it helpful to tune the value of query_cache_min_res_unit:
The default value of query_cache_min_res_unit is 4KB. This should be adequate for most cases.
If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.
If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).
MySQL Enterprise. If the query cache is under-utilized, performance will suffer. Advice on avoiding this problem is provided to subscribers to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.
You can check whether the query cache is present in your MySQL server using the following statement:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+ You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.
The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.
To monitor query cache performance, use SHOW STATUS to view the cache status variables:
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+-------------------------+--------+Descriptions of each of these variables are given in Section 5.2.5, “Status Variables”. Some uses for them are described here.
The total number of SELECT queries is given by this formula:
Com_select + Qcache_hits + queries with errors found by parser
The Com_select value is given by this formula:
Qcache_inserts + Qcache_not_cached + queries with errors found during the column-privileges check
The query cache uses variable-length blocks, so Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE, only a single free block remains.
Every cached query requires a minimum of two blocks (one for the query text and one or more for the query results). Also, every table that is used by a query requires one block. However, if two or more queries use the same table, only one table block needs to be allocated.
The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. Tuning information is given in Section 7.5.4.3, “Query Cache Configuration”.
When you are attempting to ascertain what your MySQL server is doing, it can be helpful to examine the process list, which is the set of threads currently executing within the server. Process list information is available from these sources:
The SHOW [FULL] PROCESSLIST statement (Section 13.5.4.21, “SHOW PROCESSLIST Syntax”)
The SHOW PROFILE statement (Section 13.5.4.22, “SHOW PROFILES and SHOW PROFILE Syntax”)
The myqladmin processlist command (Section 8.10, “mysqladmin — Client for Administering a MySQL Server”)
You can always view information about your own threads. To view information about threads being executed for other accounts, you must have the PROCESS privilege.
Each process list entry contains several pieces of information:
Id is the connection identifier for the client associated with the thread.
User and Host indicate the account associated with the thread.
db is the default database for the thread, or NULL if none is selected.
Command and State indicate what the thread is doing.
Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.
Time indicates how long the thread has been in its current state.
Info contains the text of the statement being executed by the thread, or NULL if it is not executing one. By default, this value contains only the first 100 characters of the statement. To see the complete statements, use SHOW FULL PROCESSLIST.
The following sections list the possible Command values, and State values grouped by category. The meaning for some of these values is self-evident. For others, additional description is provided. See also Section 6.3, “Replication Implementation Details”, for additional information about thread states for replication servers.
A thread can have any of the following Command values:
This is a thread on a master server for sending binary log contents to a slave server.
The thread is executing a change-user operation.
The thread is closing a prepared statement.
A replication slave is connected to its master.
A replication slave is connecting to its master.
The thread is executing a create-database operation.
This thread is internal to the server, not a thread that services a client connection.
The thread is generating debugging information.
The thread is a delayed-insert handler.
The thread is executing a drop-database operation.
The thread is executing a prepared statement.
The thread is fetching the results from executing a prepared statement.
The thread is retrieving information for table columns.
The thread is selecting a default database.
The thread is killing another thread.
The thread is retrieving long data in the result of executing a prepared statement.
The thread is handling a server-ping request.
The thread is preparing a prepared statement.
The thread is producing information about server threads.
The thread is executing a statement.
The thread is terminating.
The thread is flushing table, logs, or caches, or resetting status variable or replication server information.
The thread is registering a slave server.
The thread is resetting a prepared statement.
The thread is setting or resetting a client statement-execution option.
The thread is shutting down the server.
The thread is waiting for the client to send a new statement to it.
The thread is producing server-status information.
The thread is sending table contents to a slave server.
Unused.
The following list describes thread State values that are associated with general query processing and not more specialized activities such as replication. Many of these are useful only for finding bugs in the server.
Occurs when the thread creates a table (including internal temporary tables), at the end of the function that creates the table. This state is used even if the table could not be created due to some error.
The thread is calculating a MyISAM table key distributions (for example, for ANALYZE TABLE).
The thread is performing a table check operation.
The thread has processed one command and is preparing to free memory and reset certain state variables.
Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.
The thread is converting an internal temporary table from a MEMORY table to an on-disk MyISAM table.
The thread is processing an ALTER TABLE statement. This state occurs after the table with the new structure has been created but before rows are copied into it.
If a statement has different ORDER BY and GROUP BY criteria, the rows are sorted by group and copied to a temporary table.
The server is copying to a temporary table in memory.
The server is copying to a temporary table on disk. The temporary result set was larger than tmp_table_size and the thread is changing the temporary table from in-memory to disk-based format to save memory.
The thread is processing ALTER TABLE ... ENABLE KEYS for a MyISAM table.
The thread is processing a SELECT that is resolved using an internal temporary table.
The thread is creating a table. This includes creation of temporary tables.
The thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the state during that operation will be Copying to tmp table on disk.
The server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving fields and offsets to be used for deleting from the other (reference) tables.
deleting from reference tables
The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.
The thread is processing an ALTER TABLE ... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE statement.
This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.
The thread is executing statements in the value of the init_command system variable.
The thread has executed a command. This state is usually followed by cleaning up.
The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.
The server is preparing to perform a natural-language full-text search.
This occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements.
Someone has sent a KILL statement to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.
The query is locked by another query.
The thread is writing a statement to the slow-query log.
The initial state for a connection thread until the client has been authenticated successfully.
The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished.
This state occurs during query optimization.
The thread is removing unneeded relay log files.
This state occurs after processing a query but before the freeing items state.
The server is reading a packet from the network.
The query was using SELECT DISTINCT in such a way that MySQL could not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.
The thread is removing an internal temporary table after processing a SELECT statement. This state is not used if no temporary table was created.
The thread is renaming a table.
The thread is processing an ALTER TABLE statement, has created the new table, and is renaming it to replace the original table.
The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.
The repair code is using a sort to create indexes.
The thread has completed a multi-threaded repair for a MyISAM table.
The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.
The thread is rolling back a transaction.
For MyISAM table operations such as repair or analysis, the thread is saving the new table state to the .MYI file header. State includes information such as number of rows, the AUTO_INCREMENT counter, and key distributions.
The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.
Sending data
The thread is processing rows for a SELECT statement and also is sending data to the client.
The thread is beginning an ALTER TABLE operation.
The thread is doing a sort to satisfy a GROUP BY.
The thread is doing a sort to satisfy a ORDER BY.
The thread is sorting index pages for more efficient access during a MyISAM table optimization operation.
For a SELECT statement, this is similar to Creating sort index, but for non-temporary tables.
The server is calculating statistics to develop a query execution plan.
The thread is is going to request or is waiting for an external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the --skip-external-locking option. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).
The next thread state after System lock. The thread has acquired an external lock and is going to request an internal table lock.
The thread is searching for rows to update and is updating them.
The server is executing the first part of a multiple-table update. It is updating only the first table, and saving fields and offsets to be used for updating the other (reference) tables.
The server is executing the second part of a multiple-table update and updating the matched rows from the other tables.
The thread is going to request or is waiting for an advisory lock requested with a GET_LOCK() call. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).
Waiting for tables, Waiting for table
The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.
This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES , tbl_nameALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
A generic state in which the the thread is waiting for a condition to become true. No specific state information is available.
The server is writing a packet to the network.
These thread states are associated with processing for DELAYED inserts (see Section 13.2.4.2, “INSERT DELAYED Syntax”). Some states are associated with connection threads that process INSERT DELAYED statements from clients. Other states are associated with delayed-insert handler threads that insert the rows. There is a delayed-insert handler thread for each table for which INSERT DELAYED statements are issued.
States associated with a connection thread that processes an INSERT DELAYED statement from the client:
The thread is preparing to feed rows to the delayed-insert handler thread.
The thread is creating a handler for DELAYED inserts.
This occurs before the allocating local table state and afer the waiting for handler lock state, when the connection thread gets access to the delayed-insert handler thread.
This occurs after the waiting for handler open state. The delayed-insert handler thread has signaled that it has ended its initialization phase, which includes opening the table for delayed inserts.
The thread is adding a new row to the list of rows that the delayed-insert handler thread must insert.
This occurs during the initialization phase when the thread is trying to find the delayed-insert handler thread for the table, and before attempting to gain access to the list of delayed-insert threads.
An INSERT DELAYED handler has processed all pending inserts and is waiting for new ones.
This occurs before the allocating local table state when the connection thread waits for access to the delayed-insert handler thread.
This occurs after the Creating delayed handler state and before the got old table state. The delayed-insert handler thread has just been started, and the connection thread is waiting for it to initialize.
States associated with a delayed-insert handler thread that inserts the rows:
The state that occurs just before inserting rows into the table.
After inserting a number of rows, the delayed-insert thread sleeps to let other threads do work.
A delayed-insert handler is trying to get a lock for the table to insert rows.
A delayed-insert handler is waiting for a connection thread to add rows to the queue (see storing row into queue).
The thread is processing events for binary logging.
Processing events from schema table
The thread is doing the work of schema replication.
Syncing ndb table schema operation and binlog
This is used to have a correct binary log of schema operations for NDB.
Waiting for event from ndbcluster
The server is acting as an SQL node in a MySQL Cluster, and is connected to a cluster management node.
Waiting for ndbcluster binlog update to reach current position
The thread is waiting for a schema epoch (that is, a global checkpoint).
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 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 dynamically linked MySQL library for your client applications. It is the server that is most critical for performance.
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-safemalloc option. 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 -mcpu=v8 -Wa,-xarch=v8plusa options gives 4% more performance.
On Solaris 2.5.1, MIT-pthreads is 8–12% slower than Solaris native threads on a single processor. With greater loads or more CPUs, the difference should be larger.
Compiling on Linux-x86 using gcc without frame pointers (-fomit-frame-pointer or -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 is shared by all threads; its size is determined by the key_buffer_size variable. Other buffers used by the server are allocated as needed. See Section 7.5.2, “Tuning Server Parameters”.
Each connection uses some thread-specific space. The following list indicates these and which variables control their size:
A stack (default 192KB, variable thread_stack)
A connection buffer (variable net_buffer_length)
A result buffer (variable net_buffer_length)
The connection buffer and result buffer both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement. While a statement is running, a copy of the current statement string is also allocated.
All threads share the same base memory.
When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.
Only compressed MyISAM tables 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 hash tables. Temporary tables with a large row length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk.
If an internal heap table exceeds the size of tmp_table_size, MySQL handles this automatically by changing the in-memory heap table to a disk-based MyISAM table as necessary. You can also 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 13.5.3, “SET Syntax”.
MySQL Enterprise. Subscribers to the MySQL Network Monitoring and Advisory Service are alerted when temporary tables exceed tmp_table_size. Advisors make recommendations for the optimum value of tmp_table_size based on actual table usage. For more information about the MySQL Network Monitoring and Advisory Service please see http://www.mysql.com/products/enterprise/advisors.html.
Most requests that perform a sort allocate a sort buffer and zero to two temporary files depending on the result set size. See Section B.1.4.4, “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, so the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings. This is done with malloc() and free().
For each MyISAM table 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 size 3 Ч are allocated (where NN is the maximum row length, not counting BLOB columns). A BLOB column requires five to eight bytes plus the length of the BLOB data. The MyISAM storage engine maintains one extra row buffer for internal use.
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.
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 Section 7.4.8, “How MySQL Opens and Closes Tables”.
A FLUSH TABLES statement 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 TABLES does 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.
In some cases, the server creates internal temporary tables while processing queries. A temporary table can be held in memory and processed by the MEMORY storage engine, or stored on disk and processed by the MyISAM storage engine. Temporary tables can be created under conditions such as these:
If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table.
DISTINCT combined with ORDER BY may require a temporary table.
You can tell whether a query requires a temporary table by using EXPLAIN and checking the Extra column to see whether it says Using temporary. See Section 7.2.1, “Optimizing Queries with EXPLAIN”.
Some conditions prevent the use of a MEMORY temporary table, in which case the server uses a MyISAM table instead:
Presence of a TEXT or BLOB column in the table
Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes
A temporary table that is created initially as a MEMORY table might be converted to a MyISAM table and stored on disk if it becomes too large. The max_heap_table_size system variable determines how large MEMORY tables are allowed to grow. It applies to all MEMORY tables, including those created with CREATE TABLE. However, for internal MEMORY tables, the actual maximum size is determined by max_heap_table_size in combination with tmp_table_size: Whichever value is smaller is the one that applies. If the size of an internal MEMORY table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.
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() and gethostbyname_r() calls, the thread uses them to perform hostname resolution.
If the operating system does not support the thread-safe calls, the thread locks a mutex and calls gethostbyaddr() and gethostbyname() 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.
To disallow TCP/IP connections entirely, start mysqld with the --skip-networking option.