MySQL Server performance optimization

One: Configuration file read location, different system my.cnf configuration file location.

1. 例如debian位置:/etc/mysql/my.cnf 2. 找到mysqld二进制文件:  find    /  -name  mysqld 3. /usr/bin/mysqld   --verbose  --help  |  grep   -A 1   "Default options"

Two: Global cache

1. (key_buffer_size(默认值：384M) 2. innodb_buffer_pool_size 3. innodb_additional_mem_pool_size 4. innodb_log_buffer_size(默认值：8M) 5. query_cache_size(默认值：32M)

1.innodb_buffer_pool_size (default: 128M)

1. innodb_buffer_pool_size=24G

Pros: Cache index, cache row data, Adaptive hash index, insert cache, lock, internal data structure

Cons: InnoDB cache is too large, preheating and shutting down can take a lot of time. This time is determined by the number of dirty pages. Because the dirty page is written back to the data file before it is closed. The recovery time can be very long after a forced shutdown.

Workaround: To close the MySQL database, you can tell the number of dirty pages (innodb_max_dirty_pages_pct) slightly smaller, after the value is changed, wait for the new thread to clean up the buffer pool, and then close the database when the number of dirty pages is small.

However, the smaller the innodb_max_dirty_pages_pct, the smaller the number of dirty pages is not guaranteed.

Configuration: 80% of the memory (provided that the server only ran MySQL one consumes the memory type, IO type database)

Monitoring: Show status or Innotop tool

2.innodb_additional_mem_pool_size (default: 8M)

1. innodb_additional_mem_pool_size=16M

Store the data dictionary information and the memory space of some internal data structures, and when the MySQL instance database object is large, increase the value. Determine if it is sufficient to check the error log in MySQL to see if there is warnning information.

3.innodb_log_buffer_size (Default 8M)

1. innodb_log_buffer_size=8M 2. innodb_flush_log_trx_commit=2

Temporarily store the transaction log. InnoDB when writing the transaction log, in order to improve performance, the transaction log is written to Innodb_log_buffer, when the corresponding conditions set by the Innodb_flush_log_trx_commit parameter (or the log buffer is full) are met, The log is written to a file (or to a disk).

The ideal value is 1M to 8M, generally not more than 32M.

Note: The Innodb_flush_log_trx_commit parameter has a very critical effect on the write performance of InnoDB log, with a default value of 1. This parameter can be set to 0,1,2.

4. Transaction refresh rate (Innodb_flush_log_trx_commit)

1. innodb_flush_log_trx_commit=2

This value has a very large impact on the write performance of the database. MySQL official recommends merging inserts into one transaction, which can dramatically increase the speed

The actual test found that it only takes 2 seconds to insert 10,000 records when set to 2 o'clock

Set to 0 o'clock inserting 10,000 records takes 1 seconds,

Setting to 1 O'Clock inserts 10,000 records takes 229 seconds. This value can be set to 0 if there is a risk of losing the most recent part of the transaction.

Transaction refresh. Configurable as 0,1,2

0: Indicates that the data in log buffer is flushed to log file at a rate of one refresh per second. It also triggers a file system-to-disk synchronization operation.

1: Indicates that the data in log buffer is flushed to log file each time a new transaction commits. It also triggers a file system-to-disk synchronization operation.

2: Indicates that the data in log buffer is flushed to log file each time a new transaction commits, but the file system is flushed to disk once per second.

5. Query Cache (query_cache_size)

1. query_cache_size=256M 2. query_cache_type=ON

Caches the execution result of the SELECT statement. Single is not a full cache, the condition is that the size of the query to the result set must be less than or equal to query_cache_size.

Attention:

This is worth using or not, depending on whether the data in the query table changes frequently, such as my company's order form. The table's data is constantly changing, so this option cannot be applied. This is a fatal configuration. Because the data in the table changes, the results in the query cache are invalidated.

Use this option with multiple parameters.

Query_cache_size Cache result set size

Query_cache_type ={0|1|2}

0: No query cache is indicated

1: Indicates that the cache is not used. 1 (on) or 2 (Demond), which means that no query cache is used at all, except for explicitly requiring that all select other than query cache (using Sql_no_cache) use query cache,

Configuration: 256M Sufficient

Query Cache Hit Rate (qcache_hits/(qcache_hits+qcache_inserts) *100)) to adjust

6.MyISAM Storage Engine (key_buffer_size)

1. key_buffer_size=128M

Pros: Cache index data and cache only index data

Disadvantage: mysql5.0 Maximum 4G

Three: Local cache

1. read_buffer_size 2. sort_buffer_size 3. read_rnd_buffer_size 4. tmp_table_size

These parts are allocated when needed, and then the memory is freed immediately after the operation is completed.

1.read_buffer_size (default: 2M)

1. read_buffer_size=4M

Sequential read buffers, a buffer allocated for sequential scanning of tables.

If the program periodically scans the data table, you should increase the value to improve performance.

2.read_rnd_buffer_size (default: 8M)

1. read_rnd_buffer_size=8M

A random read buffer that allocates a buffer when the table randomly reads data.

3.sort_buffer_size (default: 2M)

1. sort_buffer_size=4M

For Oder by statements to store sort queries

4.tmp_table_size (default: 16M)

1. tmp_table_size=16M

Federated query Cache Size

5. Table Caching

Table_open_cache

1. table_open_cache=4096

Explanation: The storage object is a data table.

Monitoring: If the Opend_tables state variable is large or growing, it is possible that the table cache is not large enough and should be increased.

Disadvantage: When a table in a database has many MyISAM tables, the shutdown time can be very long. Because the index block must be refreshed before shutting down. The table is marked as no longer open.

Monitoring: If you find Open_tables equals Table_open_cache, and opened_tables is growing, you need to increase the value of Table_open_cache.

The value is calculated as: max_connections*n

N represents the largest table in a query statement.

Four: Thread caching (thread_cache_size)

1. thread_cache_size=64

Explanation: The thread that was prepared for the new MySQL connection. Thread_cache_size guarantees the number of threads in the cache. You typically do not need to configure this value unless the database has a large number of connection requests. When a connection is created, if a wired thread exists in the cache, the MySQL cache

Delete a thread and assign it to the new connection, and if there is room in the cache when the connection is closed, MySQL will put the connection in the cache. If there is no space, MySQL destroys the thread.

Monitoring: Check that the thread cache is sufficient to view the THREADS_CONNECTD state variable.

Example:

threads_connected between 100-200, then thread_cache_size=20, enough.

threads_connected between 500-700, then thread_cache_size=200, enough.

Is it appropriate to determine the setting value by connecting the thread pool's hit ratio? Hit more than 90%, set reasonable.

(connections-threads_created)/Connections * 100

Thread_concurrency

Explanation: This value should be twice times the number of CPU cores.

Example: 2 physical CPUs, each CPU8 core, then

Thread_concurrency=2*8*2=32

Five. InnoDB concurrency limit (innodb_thread_concurrency)

Innodb_thread_concurrency=24

Explanation: It restricts how many threads can enter the kernel at a time. 0 means no limit. It is important to set this value under any architectural and business pressure.

Number of INNODB_THREAD_CONCURRENCY=CPU * Number of disks * *

Example: 2 physical CPUs, each CPU8 core, then

Thread_concurrency=2*6*2=32

Six. Database file descriptor (OPEN_FILES_LIMIT)

1. open_files_limit=65535

Seven. Request Queue (Back_log)

1. back_log=500

MySQL new request queue, only the max_connections reached the maximum, you can also accept how many requests, first put in the queue. each

IX: Other

1. The parameters are as follows

Innodb_data_file_path = Ibdata1:1g:autoextend

Innodb_log_file_size = 512M

Note: The above parameters must be configured before starting the database, otherwise the error is as follows:

1. 2015-12-23 17:03:06 16182 [ERROR] InnoDB: auto-extending data  file  ./ibdata1 is of a different size 768 pages (rounded down to MB) than specified  in  the .cnf  file : initial 65536 pages, max 0 (relevant  if  non-zero) pages! 2. 2015-12-23 17:03:06 16182 [ERROR] InnoDB: Could not  open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path  in my.cnf back to what it was, and remove the new ibdata files InnoDB created  in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them  in any way. But be careful:  do not remove old data files  which contain your precious data! 3. 2015-12-23 17:03:06 16182 [ERROR] Plugin ‘InnoDB‘ init  function  returned error. 4. 2015-12-23 17:03:06 16182 [ERROR] Plugin ‘InnoDB‘ registration as a STORAGE ENGINE failed. 5. 2015-12-23 17:03:06 16182 [ERROR] Unknown/unsupported storage engine: INNODB 6. 2015-12-23 17:03:06 16182 [ERROR] Aborting

2.innodb_log_files_in_group = 2

MySQL Server performance optimization