MySQL Server optimization method _ MySQL-mysql tutorial

MySQL Server optimization method bitsCN.com

1. adjust system factors and startup parameters
We start with system-level factors, because some factors must be decided as soon as possible to achieve major performance improvements. In other cases, you only need to take a quick look at this chapter. However, at this level, it is more appropriate to see what can be done to achieve higher performance.

It is important to use the default operating system. To use multiple CPU machines most effectively, use Solaris (because its thread implementation is really good) or Linux (because the 2.2 kernel has good support for SMP ). Note that the Linux kernel of the old version has a limit of 2 GB by default. If such a kernel is used and the file actually needs to be larger than 2 GB, a large file support (LFS) patch must be applied to the ext2 file system. Other file systems such as ReiserFS and XFS do not have this restriction.

Before MySQL is put into production, we recommend that you perform a test on the platform you want to use.

Other tips:

If there is enough RAM (random memory), all swap devices should be removed. Some operating systems use swap devices even if there is surplus memory in some scenarios.

Use the MySQL option -- skip-external-locking to avoid external locks. This option is enabled by default from MySQL 4.0. Before that, only compilation is supported.

MIT-pthreads can be enabled by default, because the MIT-pthreads on all platforms cannot all support flock (). This is also enabled by default in Linux, because the file lock in Linux is not secure yet. Note that the -- skip-external-locking option does not affect its functionality when the server is running. Just remember to run

Shut down the server (or lock and refresh the data table) before myisamchk ). In some operating systems, this option is mandatory because external locks cannot be used under any circumstances. The only condition that the -- skip-external-locking option cannot be used is to run multiple MySQL servers (not clients) on the same data or

Myisamchk does not tell the server to refresh and lock the table before checking (not restoring) the data table. The lock tables and unlock tables statements can still be used after the -- skip-external-locking option is used.
2. adjust server parameters
You can use the following mysqld command (ignore -- verbose before MySQL 4.1) to determine the default buffer size:

Shell> mysqld -- verbose -- help

This command generates all mysqld options and a list of system variables that can be configured. The result contains the default value, which looks like the following:

Back_log current value: 5
Bdb_cache_size current value: 1048540
Binlog_cache_size current value: 32768
Connect_timeout current value: 5
Delayed_insert_limit current value: 100
Delayed_insert_timeout current value: 300
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
Long_query_time current value: 10
Lower_case_table_names current value: 0
Max _allowed_packet current value: 1048576
Max_binlog_cache_size current value: 4294967295
Max_connect_errors current value: 10
Max_connections current value: 100
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_read_timeout current value: 30
Net_retry_count current value: 10
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
Thread_stack current value: 131072
Tmp_table_size current value: 1048576
Wait_timeout current value: 28800

BitsCN.com