Summary of configuration techniques for MySQL database optimization technology_MySQL

The examples in this article describe the configuration method of MySQL database optimization technology. Share it with everyone for your reference, the details are as follows:

(1) Reduce database access

For pages that can be made static, make them as static as possible

Staticize the parts of a dynamic page that can be static

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

Use data caching technology such as: MemCached

(2) Optimized detection method

1.User experience detection

2.Mysql status detection

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

Mainly focus on the following attributes:

key_read_requests (number of index read requests) (affected by key_buffer_size setting)

key_reads (number of index read responses)

Key_blocks_used

Qcache_*

Open_tables (affected by table_cache settings)

Opened_tables

table_locks

3. Third-party tool detection

mysqlreport http://hackmysql.com/mysqlreport

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

System and Mysql Log

System commands: top, sar

Mysql Log: slow_query.log

(3) Hardware optimization

In terms of hardware, the part that is most likely to become the bottleneck of Mysql is the disk, followed by CPU and memory

Disk aspect

Using a faster disk will be of great help to Mysql

Use more hard drives and use Raid to increase the speed of a single disk

For Raid method, it is recommended to use Raid 0+1 or Raid 1+0

CPU

There is no doubt that higher frequency CPUs and more CPUs can give Mysql more performance

High performance

Memory

Higher memory often allows more data in Mysql to be cached in memory,

However, an important factor is the need for correct Mysql configuration

Network card

Use Gigabit network card and Gigabit network

(4)Optimization of operating system

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

2. Don’t use NFS disk

3. Increase the number of open files on the system and MySQL server

Use ulimit –n 65535

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

5. Close unnecessary applications, optimize hard disk parameters, and use hdparm to test

(5) Application-level optimization

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

2. Table partition (custom partition, mysql5.1 starts to support its own partition function)

3. Use data caching technology memcached

(6) Optimization of Mysql configuration

1.key_buffer(=512): The amount of memory used by the index buffer

This is very important for MyISAM tables. It is better to set it at 25%-30% of the available memory. By checking the status values ​​​​Key_read_requests and Key_reads,

You can know whether the key_buffer setting is reasonable. The ratio key_reads / key_read_requests should be as low as possible, at least 1:100, 1:1000 is better, otherwise it means that the key_buffer setting is a bit too small

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

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

Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents.

By checking the Open_tables and Opened_tables status values ​​during peak running times, you can decide whether the table_cache value needs to be adjusted.

If you find that the value of open_tables is equal to table_cache, and you find that the opened_tables status value is growing, then you need to increase the table_cache parameter value,

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

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

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

So, for a server with a memory of about 4GB, it is recommended to set it to 6-8M

5.join_buffer_size: The length of the buffer for associated queries

More than 4G of memory, it is recommended to be greater than 32M. The allocated memory corresponding to this parameter is also exclusive to each connection!

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

The number of clients allowed to connect to the MySQL server at the same time can be set by observing and estimating the maximum number of concurrent connections at the peak of the system

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. Innodb tables are more sensitive to buffering than MyISAM tables. MyISAM can run under the default key_buffer_size setting,

However, Innodb behaves like a snail under the default innodb_buffer_pool_size setting.

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

Some rules that apply to key_buffer are - If your data volume is not large and will not increase dramatically, then there is no need to set innodb_buffer_pool_size too large.

9.innodb_flush_logs_at_trx_commit(=1): Log flush mode after transaction commit

Are you worried that Innodb is 1000 times slower than MyISAM? It seems that you may have forgotten to modify this parameter. The default value is 1, which means that every updated transaction committed (or every statement outside a transaction) will be flushed to disk,

And this is quite resource intensive, especially without battery backup cache. Many applications, especially those converted from MyISAM, just set its value to 2, which means the logs are not flushed to disk,

And only flush to the operating system cache. The log is still flushed to disk every second, so the cost of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it is also relatively unsafe,

Some transactions will be lost when the MySQL server crashes. Set to 2 to lose the portion of the transaction flushed to the operating system cache.

Readers who are interested in more MySQL-related content can check out the special topics on this site: "Summary of MySQL Index Operation Skills", "Comprehensive Collection of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Comprehensive Collection of MySQL Stored Procedure Skills", " Summary of MySQL database lock related skills" and "Summary of commonly used MySQL functions"

I hope this article will be helpful to everyone’s MySQL database planning.