Summary of MySQL database optimization technology configuration skills, mysql database

Summary of MySQL database optimization technology configuration skills, mysql database

This article describes how to configure the MySQL database optimization technology. We will share this with you for your reference. The details are as follows:

(1) reducing Database Access

Static pages are possible

Static parts of a dynamic page

Some data can be generated as XML or saved as text files

Use data caching technology, such as MemCached

(2) optimization Detection Methods

1. User Experience Detection

2. Mysql status detection

Use the show status command in the Mysql command line to obtain the current mysql status.

Focus on the following attributes:

Key_read_requests (number of index read requests) (the impact of key_buffer_size setting)

Key_reads (number of index read responses)

Key_blocks_used

Qcache _*

Open_tables (influenced by table_cache settings)

Opened_tables

Table_locks

3. Third-party Tool Testing

Http://hackmysql.com/mysqlreport for mysqlreport

Mytop http://jeremy.zawodny.com/mysql/mytop/

System and Mysql Log

System Command: top, sar

Mysql Log: slow_query.log

(3) hardware optimization

In terms of hardware, the most vulnerable part of Mysql is disk, followed by CPU and memory.

Disk

Using a faster disk will be helpful to Mysql

Using more hard disks and Raid can speed up a single disk

For Raid, we recommend that you use Raid 0 + 1 or Raid 1 + 0.

CPU

There is no doubt that the CPU with a higher clock speed and more CPU can give Mysql more

High Performance

Memory

A higher memory usually allows Mysql to cache more data in the memory,

However, an important factor is that the correct Mysql configuration is required.

Nic

Use a gigabit Nic and a gigabit network

(4) Operating System Optimization

1. Do not use a swap zone. If the memory is insufficient, add more memory or configure your system to use less memory.

2. Do not use NFS Disks

3. Increase the number of files opened by the system and MySQL Server

Use ulimit-n 65535

4. Increase the number of processes and threads in the system.

5. disable unnecessary applications, optimize hard disk parameters, and use hdparm for testing.

(5) application-level optimization

1. Use multi-server Load balancer (multiple reads and writes, and use replication technology for data synchronization)

2. Table partition (custom partition, MySQL supports built-in Partition Function)

3. Use Data Cache Technology memcached

(6) Mysql Configuration Optimization

1. key_buffer (= 512): number of memory used by the index Buffer

This is very important for the MyISAM table. It is better to set it to 25%-30% of the available memory. Check the status values Key_read_requests and Key_reads,

You can check whether the key_buffer settings are reasonable. Proportional key_reads/key_read_requests should be as low as possible, at least, is better, otherwise it indicates that key_buffer settings are a little small

2. innodb_buffer_pool_size (= 512): The amount of memory used by the index buffer.

3. table_cache (= 1024): size of the data table cache area

When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly.

You can determine whether to adjust the value of table_cache by checking the Open_tables and Opened_tables Status values of the running peak time.

If you find that the value of open_tables is equal to table_cache and the status value of opened_tables is increasing, you need to add the table_cache parameter value,

You cannot blindly set the table_cache parameter to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.

4. sort_buffer_size (= 256): specify the length of the buffer for sorting.

The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 MB.

Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.

5. join_buffer_size: the length of the buffer for the associated query.

4 GB memory or above. We recommend that you increase the memory size to 32 MB. The allocated memory corresponding to this parameter is exclusive to each connection!

6. max_connections (= 1024): number of threads that can be reused

The number of customers allowed to connect to the MySQL server at the same time. You can observe and estimate the maximum number of concurrent connections at the peak of the system to set

7. thread_cache (= *): number of threads that can be reused

Generally set to the number of CPUs × 2

8. innodb_buffer_pool_size (= 512): innodb table cache pool size

This is very important for Innodb tables. Compared with MyISAM tables, Innodb is more sensitive to buffering. MyISAM can run under the default key_buffer_size setting,

However, the default innodb_buffer_pool_size setting of Innodb is similar to snail bait.

Because Innodb caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need Innodb, you can set it to up to 70-80% of available memory.

Some rules apply to key_buffer: If your data volume is small and does not increase rapidly, you do not need to set innodb_buffer_pool_size too large.

9. innodb_flush_logs_at_trx_commit (= 1): The log refresh mode after the transaction is committed

Is it because Innodb is 1000 times slower than MyISAM? Maybe you forgot to modify this parameter. The default value is 1, which means that every update transaction committed (or a statement other than each transaction) will be refreshed to the disk,

This is quite resource-consuming, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set the value to 2, that is, do not refresh the log to the disk,

Instead, it is only refreshed to the operating system cache. Logs are still refreshed to the disk every second, so the consumption of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it is relatively insecure,

Some transactions are lost when the MySQL server crashes. Set to 2 command for the part of the transaction that is lost and refreshed to the operating system cache.