Optimizing MySQL Server

7.5.1. System factors and start-up parameters adjustment

We start with system-level factors because we have to make some early decisions to achieve greater performance. In other cases, a quick tour of the section is sufficient. However, it makes sense to know how much performance improvement is achieved by changing the parameters of the hierarchy.

The operating system used is important. To better use a multi-CPU machine, you should use Solaris (because its threads work well) or Linux (because 2.4 and later kernels have good SMP support). Note that by default, the old Linux kernel has a 2GB file size limit. If you have such a kernel and need files larger than 2GB, you should get a large file support (LFS) patch for the ext2 file system. Other file systems such as ReiserFS and XFS do not have this 2GB limitation.

Before using MySQL for production, we recommend that you test it on the platform you want to use.

Other tips:

· If you have enough RAM, you can remove all the switching devices. Some operating systems use swap devices even if free memory is available.
· Use the--skip-external-locking MySQL option to avoid external locking. This option is turned on by default.

Please note that as long as you run only one server, the--skip-external-locking option does not affect the functionality of MySQL. Just remember to shut down the server (or lock and refresh the related table) before running Myisamchk. On some systems this option is mandatory because the external lock does not work under any circumstances.

The only case where--skip-external-locking cannot be used is when multiple MySQL servers (non-customers) are running on the same data, or if you run Myisamchk to check (not fix) the table without telling the server to refresh and lock a table in advance. Please note that it is generally not recommended to use multiple MySQL servers to access the same data in parallel, except when using MySQL cluster.

Even with--skip-external-locking, lock TABLES and unlock TABLES can still be used.

7.5.2. Tuning Server Parameters

You can use this command to get the MYSQLD server default cache size:
#/usr/libexec/mysqld--verbose--help
Variables (--variable-name=value)
and Boolean options {false| TRUE} Value (after reading options)
--------------------------------- -----------------------------
Abort-slave-event-count 0
Allow-suspicious-udfs FALSE
Auto-increment-increment 1
Auto-increment-offset 1
Automatic-sp-privileges TRUE
Back_log 50
basedir/usr/
Bind-address (No default value)
Binlog-direct-non-transactional-updates FALSE
Binlog-row-event-max-size 1024
Binlog_cache_size 32768
Binlog_format (No default value)
Bulk_insert_buffer_size 8388608
Character-set-client-handshake TRUE

#/usr/libexec/mysqld--verbose
120905 17:01:43 innodb:initializing buffer pool, size = 8.0M
120905 17:01:43 innodb:completed initialization of buffer pool
120905 17:01:43 innodb:started; Log sequence number 0 44233
120905 17:01:43 [Note] Event scheduler:loaded 0 Events
120905 17:01:43 [Note]/usr/libexec/mysqld:ready for connections.
Version: ' 5.1.61 ' socket: '/var/lib/mysql/mysql.sock ' port:3306 Source distribution

If there is a mysqld server running, by connecting it and executing this command, you can see the values of the variables that are actually used:
#/etc/init.d/mysqld Start
Starting mysqld: [OK]
[Email protected] ~]# mysql-uroot-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Mysql> Show variables;
+-----------------------------------------+-------------------------------------------------------------------- -----------------------+
| variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------- -----------------------+
| auto_increment_increment | 1 |
| Auto_increment_offset | 1 |
| autocommit | On |
| Automatic_sp_privileges | On |
| Back_log | 50 |
| Basedir | /usr/|
| Big_tables | OFF |
| Binlog_cache_size | 32768 |
| Binlog_direct_non_transactional_updates | OFF |
| Binlog_format | STATEMENT |
| Bulk_insert_buffer_size | 8388608 |
| character_set_client | Latin1 |
| character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_filesystem | binary |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
| collation_connection | Latin1_swedish_ci |
| Collation_database | Latin1_swedish_ci |
| Collation_server | Latin1_swedish_ci |
| Completion_type | 0 |
| Concurrent_insert | 1 |
| Connect_timeout | 10 |
| DataDir | /var/lib/mysql/|
| Date_format | %y-%m-%d |
| Datetime_format | %y-%m-%d%h:%i:%s |
| Default_week_format | 0 |
| Delay_key_write | On |
| Delayed_insert_limit | 100 |
| Version | 5.1.61 |
| version_comment | Source Distribution |
| Version_compile_machine | x86_64 |
| Version_compile_os | Redhat-linux-gnu |
| Wait_timeout | 28800 |
| Warning_count | 0 |
+-----------------------------------------+-------------------------------------------------------------------- -----------------------+
276 rows in Set (0.00 sec)

You can also see the statistics and status metrics for running the server by using the following statement:
Mysql> Show status;
+-----------------------------------+-----------+
| variable_name | Value |
+-----------------------------------+-----------+
| aborted_clients | 0 |
| aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| bytes_received | 134 |
| bytes_sent | 8058 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| com_alter_event | 0 |
| com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| com_alter_table | 0 |
| threads_created | 2 |
| threads_running | 1 |
| Uptime | 126 |
| Uptime_since_flush_status | 126 |
+-----------------------------------+-----------+
291 Rows in Set (0.00 sec)

System variables and status information can also be obtained using mysqladmin:
# mysqladmin-uroot-p variables
Enter Password:
+-----------------------------------------+-------------------------------------------------------------------- -----------------------+
| variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------- -----------------------+
| auto_increment_increment | 1 |
| Auto_increment_offset | 1 |
| autocommit | On |
| Automatic_sp_privileges | On |
| Back_log | 50 |
| Basedir | /usr/|
| Big_tables | OFF |
| Binlog_cache_size | 32768 |
| Binlog_direct_non_transactional_updates | OFF |
| Binlog_format | STATEMENT |
| Bulk_insert_buffer_size | 8388608 |
| Version_compile_machine | x86_64 |
| Version_compile_os | Redhat-linux-gnu |
| Wait_timeout | 28800 |
| Warning_count | 0 |
+-----------------------------------------+-------------------------------------------------------------------- -----------------------+

# mysqladmin-uroot-p Extended-status
Enter Password:
+-----------------------------------+----------+
| variable_name | Value |
+-----------------------------------+----------+
| aborted_clients | 0 |
| aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| bytes_received | 511 |
| bytes_sent | 23628 |
| Com_admin_commands | 1 |
| threads_connected | 2 |
| threads_created | 6 |
| threads_running | 1 |
| Uptime | 266 |
| Uptime_since_flush_status | 266 |
+-----------------------------------+----------+

MySQL uses an algorithm that can be upgraded completely, so it usually runs with very little memory. However, it is usually better to give MySQL more memory performance.

When tuning the MySQL server, the two most important variables to configure are key_buffer_size and Table_cache. Before attempting to change other variables, you should be sure that these variables have been properly configured.

The following example shows the variable values for some typical different runtime configurations.

1. If you have at least 256MB of memory and many tables and want maximum performance for a moderate number of customers, you should use
# mysqld_safe--key_buffer_size=64m--table_cache=256--sort_buffer_size=4m--read_buffer_size=1m &
[1] 3251
[Email protected] ~]# 120905 17:12:28 mysqld_safe Logging to '/var/log/mysqld.log '.
120905 17:12:28 Mysqld_safe starting mysqld daemon with databases From/var/lib/mysql

2. If there is only 128MB of memory and a few tables, but still a lot of sorting, you can use:
Mysqld_safe--key_buffer_size=16m--table_cache=256--sort_buffer_size=1m &
[1] 3579
[Email protected] ~]# 120905 17:15:14 mysqld_safe Logging to '/var/log/mysqld.log '.
120905 17:15:14 Mysqld_safe starting mysqld daemon with databases From/var/lib/mysql

If there are many parallel connections, the Exchange problem occurs unless MYSQLD has been configured to allocate very little memory per connection. If enough memory is available for all connections, MYSQLD will perform better.
For a small amount of memory and a large number of connections, use:
# mysqld_safe--key_buffer_size=512k--table_cache=256--sort_buffer_size=100k--read_buffer_size=100k &

Or even for:

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 a group by or order by operation on a table that is much larger than the available memory, you should increase the value of read_rnd_buffer_size to speed the row reads after the sort operation.

If you have already installed the Mysql,support-files directory contains some different my.cnf sample files: my-huge.cnf, my-. CNF, My-medium.cnf and my-small.cnf. You can use these files to optimize your system.

Note that if you specify an option for Mysqld or mysqld_safe on the command line, it remains valid only in that secondary server call. To use this option every time the server runs, place it in an options file.

To see the effect of a parameter change, you should do the following:

shell> mysqld--key_buffer_size=32m--verbose---Help
The value of the variable is listed at the end of the output. Make sure the--verbose and---help options are at the end. Otherwise, the effects of the options listed on the command line after them are not reflected in the output.

7.5.3. Controlling the performance of the query optimizer

The task of the query optimizer is to discover the best scenario for executing a SQL query. Because the performance difference between a "good" and a "bad" scenario is huge (that is, the seconds are relative to hours or even days), most query optimizers, including the MySQL query optimizer, always search for the best solution in all possible query evaluation scenarios. For join queries, the number of possible scenarios investigated by the MySQL optimizer increases exponentially with the number of tables referenced in the query. For a small number of tables (typically less than 7-10), this is not an issue. However, when a query is submitted larger, the time it takes to query optimization can easily become a major bottleneck for server performance.

A more flexible approach to query optimization is to allow the user to control the optimizer to search the best query evaluation scheme in detail. The general idea is that the fewer scenarios the optimizer investigates, the less time it takes to compile a query. On the other hand, because the optimizer skipped some scenarios, it might miss the best scenario.

The optimizer's behavior with regard to the evaluation of the number of scenarios can be controlled by two system variables:

· The Optimizer_prune_level variable tells the optimizer to skip some scenarios based on an estimate of the number of rows accessed per table. Our experiments show that this type of "based guess" rarely misses the best scenario and can significantly reduce the number of query edits. This is why this option is on (optimizer_prune_level=1) by default. However, if you think the optimizer missed a better query scenario, this option can be turned off (optimizer_prune_level=0), and the risk is that the query edits take longer. Note that even with this heuristic, the optimizer can still detect an exponential number of scenarios.

· The optimizer_search_depth variable tells the optimizer how much depth should be viewed for each unfinished "future" scenario to assess whether it should be further expanded. A small optimizer_search_depth value can greatly reduce the number of query edits. For example, if Optimizer_search_depth is close to the number of tables in the query, queries for 12, 13, or more tables are likely to take hours or even days to compile. Also, if you edit with optimizer_search_depth equal to 3 or 4, for the same query, the compiler compilation time can be less than 1 minutes. If a reasonable optimizer_search_depth value cannot be determined, the variable can be set to 0, which tells the optimizer to automatically determine the value.

7.5.4. How does compiling and linking affect the speed of MySQL

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

When you use the-static link, you can get the fastest executable file.

On Linux, it is best to compile the server with PGCC and-o3. In order to compile "sql_yacc.cc" with these options, approximately 200M of memory is required because GCC or PGCC requires a large amount of memory to embed all functions (inline). When configuring MySQL, you should also set CXX=GCC to avoid including the Libstdc++ library (which it does not need). Note that for some versions of PGCC, the generated binaries can only run on the true Pentium processor, even if you use the compiler option to show that you want the final code to work on all x586-class processors (for example, AMD).

By using a better compiler or a better compiler option, you can get 10-30% acceleration in your application. This is especially important if you compile your own SQL Server!

When we test the Cygnus codefusion or the Fujitsu compiler, neither of them is enough to make MySQL enable optimizations for compiling.

Standard MySQL binary distribution compiles to support all character sets. When you compile MySQL yourself, you should only include support for the character set that will be used. Controlled by Configure's--with-charset option.

Here are some of the gauges we have done:

• If you use PGCC and compile with-o6, the MYSQLD server is 11% faster than GCC 2.95.2.
• If you dynamically link (without-static), the results in Linux are 13% slower. Note that you can still use the dynamic Link MySQL library in your customer application. Only the server is critical to performance.
• If you peel mysqld binary with strip mysqld, the resulting binary can be 4% faster.
• For connections between clients and servers running on the same host, the result is 7.5% slower if you connect using TCP/IP instead of a UNIX socket file. (In Unix, if you connect to localhost, MySQL uses a socket file by default).
• For TCP/IP connections from client to server, connecting a remote server from another host is slower than 8-11% the server on the same host, even if it is connected via 100mb/s Ethernet.
• When running our benchmark with a secure connection (all data is encrypted with internal SSL support), the performance is 55% slower than unencrypted connections.
• If you compile with--with-debug=full, most queries are 20% slower. Some queries can take a long time; for example, the MySQL benchmark runs 35% slower. If you use--with-debug (no =full), the speed drops by only 15%. For mysqld versions compiled with--with-debug=full, you can start with the--SKIP-SAFEMALLOC option to disable memory checking at run time. Execution speed is close to--with-debug configuration.
• On Sun Ultrasparc-iie, servers compiled with Forte 5.0 are 4% faster than those compiled with GCC 3.2.
• on Sun Ultrasparc-iie, 32-bit mode servers compiled with Forte 5.0 are 4% faster than 64-bit mode servers.
• Using gcc 2.95.2 to compile UltraSPARC with-mcpu=v8-wa, performance will increase by 4% with the-XARCH=V8PLUSA option.
• On a Solaris 2.5.1, mit-pthreads is slower than a Solaris 8-12% with a native thread on a single processor. If there is a greater load/cpus, the difference should be greater.
• Use GCC compilation on linux-x86 without the frame pointer (-fomit-frame-pointer OR-FOMIT-FRAME-POINTER-FFIXED-EBP) to make mysqld fast 1-4%.

MySQL AB provides a binary MySQL distribution on Linux that is generally compiled with PGCC. We have to go back to regular GCC because there is a bug in PGCC that makes the generated binaries not run on AMD. We will continue to use GCC until the bug is resolved. Also, if you have a non-AMD machine, you can build a faster binary with PGCC compilation. Standard MySQL Linux binaries are statically linked to make it faster and easier to port.

7.5.5. How MySQL uses memory

The following list shows some of the ways that the MYSQLD server uses memory. Where applicable, the memory-related system variable name is given:

· The key cache (variable key_buffer_size) is shared by all threads, and other caches used by the server are allocated as needed.
· Each connection uses a specific thread of space:

o Stack (default 64KB, variable thread_stack)
o Connecting buffers (variable net_buffer_length)
o result buffers (variable net_buffer_length)

Connection buffers and result buffers can be dynamically expanded to max_allowed_packet as needed. When a query is run, memory is also allocated for the current query string.

· All threads share the same basic memory.
· Only compressed MyISAM tables are mapped to memory. This is because the 32-bit memory space of 4GB is not enough to accommodate most large tables. When the 64-bit address space system becomes more common, we can increase the general memory mapping support.
· Requests for sequential scans of tables will be allocated a buffer (variable read_buffer_size).
· When rows are read in any order (for example, in sort order), a random read buffer (variable read_rnd_buffer_size) is allocated to avoid hard disk searches.
· All unions are completed within a token, and most unions can even be done without a temporary table. Most temporary tables are memory-based (HEAP) tables. Temporary tables with large record lengths (the and the length of all columns) or tables containing BLOB columns are stored on the hard disk.

If an internal heap (stacked) table is larger than Tmp_table_size,mysql, you can automatically change the in-memory heap table to the hard disk-based MyISAM table as needed. You can also increase the size of the staging table by setting the Tmp_table_size option for mysqld, or set the SQL option sql_big_table in the client program. See section 13.5, 3, "set syntax".

· Most requests that are sorted are assigned a sort buffer, and zero is assigned to two temporary files based on the size of the result set. See section a.4.4, "where MySQL stores temporary files."
· Almost all parsing and computation is done in local memory. Small projects do not require memory, so normal slow memory allocations and releases are avoided. Allocates memory only for large, undesirable strings, using the function malloc () and free () to complete.
· For each open MyISAM table, the index file is opened once, and the data file is opened once for each thread that runs in parallel. For each parallel thread, a table structure, a column structure for each column, and a buffer size of 3 * n are allocated (where n is the length of the largest row, not the computed BLOB column). A BLOB column requires 5 to 8 bytes plus the length of the BLOB data. The MyISAM Storage Engine maintains an additional line buffer for internal applications.
· For each table with a BLOB column, the buffers are dynamically expanded to read into large BLOB values. If you scan a table, allocate a buffer that is as large as the largest BLOB value.
· The handle structure of all tables used is saved in the cache and managed in FIFO. By default, the cache has 64 portals. If a table is used by two running threads at the same time, the cache provides two portals. See section 7.4, 9, "How MySQL opens and closes tables".
· The FLUSH table statement or the Mysqladmin flush-table command can immediately close all unused tables and mark all tables in use as closed when the thread that is executing in parallel ends. This effectively frees most of the memory in use. FLUSH table does not return results until all tables have been closed.

PS and other System state programs can report mysqld using a lot of memory. This can be caused by a thread stack on a different memory address. For example, the Solaris version of PS counts unused memory between stacks as memory used. You can verify it by checking the available swap area with Swap-s. We tested mysqld with the commercial Memory vulnerability Profiler, so there should be no memory leaks.