10 MySQL Performance tuning methods _ MySQL

This article introduces 10 MySQL Performance tuning methods. each method is very detailed and practical. For more information, see MYSQL, which is the most popular WEB backend database. WEB development languages have developed rapidly recently. PHP, Ruby, Python, and Java have their own characteristics. although NOSQL has been mentioned more recently, I believe most architects will choose MYSQL for data storage.

MYSQL is so convenient and stable that we seldom think of it when developing WEB programs. Even if optimization is thought of, it is program-level. for example, do not write SQL statements that consume too much resources. However, there are still many optimizations in the entire system.

The code is as follows:

Mysql-u [USER_NAME]-p-e "show tables in [DATABASE_NAME]; "| tail-n + 2 | xargs-I '{} 'echo" alter table {} ENGINE = InnoDB; "> alter_table. SQL
Perl-p-I-e's/(search _ [a-z _] + ENGINE =) InnoDB // 1 MyISAM/g' alter_table. SQL
Mysql-u [USER_NAME]-p [DATABASE_NAME] <alter_table. SQL

1.2 Create an InnoDB FILE for each table:

The code is as follows:

Innodb_file_per_table = 1

This ensures that the ibdata1 file is not too large and out of control. Especially when executing mysqlcheck-o-all-databases.

The code is as follows:

Innodb_additional_mem_pool_size = 1/200 of buffer_pool
Innodb_max_dirty_pages_pct 80%

Method 2

You can also run the iostat-d-x-k 1 Command to View disk operations.

2.1.2 whether the server has enough memory for planning
Run echo 1>/proc/sys/vm/drop_caches to clear the file cache of the operating system. the actual memory usage is displayed.

2.2 Data Push

By default, data is cached in innodb_buffer_pool only once. Therefore, when the database is just started, data needs to be preheated to cache all data on the disk to the memory. Data push can increase the read speed.

For InnoDB databases, use the following method to push data:

1. Save The following script as MakeSelectQueriesToLoad. SQL

SELECT DISTINCT  CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,  ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache  FROM  (    SELECT      engine,table_schema db,table_name tb,      index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist    FROM    (      SELECT        B.engine,A.table_schema,A.table_name,        A.index_name,A.column_name,A.seq_in_index      FROM        information_schema.statistics A INNER JOIN        (          SELECT engine,table_schema,table_name          FROM information_schema.tables WHERE          engine='InnoDB'        ) B USING (table_schema,table_name)      WHERE B.table_schema NOT IN ('information_schema','mysql')      ORDER BY table_schema,table_name,index_name,seq_in_index    ) A    GROUP BY table_schema,table_name,index_name  ) AAORDER BY db,tb;

2. run

The code is as follows:

Mysql-uroot-AN </root/MakeSelectQueriesToLoad. SQL>/root/SelectQueriesToLoad. SQL

3. execute the following command every time you restart the database or push data before the full-database backup:

Mysql-uroot </root/SelectQueriesToLoad. SQL>/dev/null 2> & 1

2.3 do not store data in SWAP

For a dedicated MYSQL server, you can disable SWAP. for a shared server, make sure innodb_buffer_pool_size is large enough. Or use a fixed memory space for caching and the memlock command.

The code is as follows:

Innodb_flush_method = O_DIRECT

The code is as follows:

Show create table db1.tb1/G

6.2 add necessary indexes

Indexes are the only way to increase the query speed. for example, inverted indexes are the same principle for search engines.

The index must be added based on the query. for example, you can use the slow query log or query log, or use the EXPLAIN command to analyze and query the index.

The code is as follows:

ADD UNIQUE INDEX
ADD INDEX

6.2.1 for example, optimize the user verification table:
Add index

The code is as follows:

Alter table users add unique index username_ndx (username );
Alter table users add unique index username_password_ndx (username, password );

Data is pushed every time the server is restarted.

The code is as follows:

Echo "select username, password from users;">/var/lib/mysql/upcache. SQL

Add the startup script to my. cnf

The code is as follows:

[Mysqld]
Init-file =/var/lib/mysql/upcache. SQL

6.2.2 use an automatic index adding framework or an automatic table sharding framework
For example, a framework like Rails will automatically add indexes, while a framework like Drupal will automatically split the table structure. It will indicate the correct direction at the beginning of your development. Therefore, it is not a good practice for people with less experience to start from 0.

The code is as follows:

Log =/var/log/mysql. log

Be sure not to use it in the production environment, otherwise it will occupy your disk space.

Query records that have been executed for more than 1 second:

The code is as follows:

Long_query_time = 1
Log-slow-queries =/var/log/mysql/log-slow-queries.log

8. use memory disks in a radical way

Now the infrastructure reliability is very high. for example, EC2 has almost no need to worry about server hardware as a machine. In addition, the memory is really cheap, it is easy to buy dozens of GB memory servers, you can use the memory disk, regular backup to the disk.

Migrate the MYSQL directory to a 4 GB memory disk

mkdir -p /mnt/ramdisksudo mount -t tmpfs -o size=4000M tmpfs /mnt/ramdisk/mv /var/lib/mysql /mnt/ramdisk/mysqlln -s /tmp/ramdisk/mysql /var/lib/mysqlchown mysql:mysql mysql

9. use MYSQL using NOSQL

B-TREE is still one of the most efficient indexes, and all MYSQL is still not outdated.

Using HandlerSocket to skip the SQL parsing layer of MYSQL, MYSQL becomes NOSQL.

10. others

LIMIT 1 is added at the end of a single query to stop full table scanning.
Separate non-indexed data, such as storing large articles without affecting other automatic queries.
MYSQL built-in functions are not required, because built-in functions do not create query caches.
The connection speed of PHP is very fast, so you can skip the connection pool. Otherwise, the number of connections may exceed. Of course, the PHP program in the connection pool may also
The number of connections is full. for example, @ ignore_user_abort (TRUE) is used );
Use IP address instead of domain name as the database path to avoid DNS resolution problems

The above are 10 MySQL Performance tuning methods, and hope to help you learn.