MySQL Performance Tuning and new users must read the tutorial

MYSQL should be the most popular WEB backend database. WEB development languages have developed rapidly recently. PHP, Ruby, Python, and Java have their own characteristics? Why do I have to go to the tornado ?? Why? Therefore, zookeeper MYSQL is used 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.
1. Select an appropriate storage engine: INNODB

Unless your data table is used for read-only or full-text retrieval (I believe that MYSQL is no longer used for full-text retrieval), you should select INNODB by default.
During the test, you may find that MYISAM is faster than INNODB because MYISAM only caches indexes, while INNODB caches data and indexes. MYISAM does not support transactions. However, if you use innodb_flush_log_at_trx_commit = 2, you can achieve close read performance (a hundred times different ).
1.1 How to convert an existing MYISAM database to INNODB:
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:
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.
2. Ensure that data is read from memory and stored in memory.

2.1 enough innodb_buffer_pool_size
We recommend that you fully store the data in innodb_buffer_pool_size, that is, plan the innodb_buffer_pool_size capacity based on the storage capacity. In this way, you can completely read data from the memory to minimize disk operations.
2.1.1 How do I determine whether innodb_buffer_pool_size is large enough to read data from memory rather than hard disks?
Method 1
Mysql> show global status like 'innodb _ buffer_pool_pages _ % ';
------------------------------------------
| Variable_name | Value |
------------------------------------------
| Innodb_buffer_pool_pages_data | 129037 |
| Innodb_buffer_pool_pages_dirty | 362 |
| Innodb_buffer_pool_pages_flushed | 9998 |
| Innodb_buffer_pool_pages_free | 0 | !!!!!!!!
| Innodb_buffer_pool_pages_misc | 2035 |
| Innodb_buffer_pool_pages_total | 131072 |
------------------------------------------
6 rows in set (0.00 sec)
If Innodb_buffer_pool_pages_free is 0, the buffer pool has been used up and innodb_buffer_pool_size needs to be increased.
Other parameters of INNODB
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 _ scheme', 'mysql ')
Order by table_schema, table_name, index_name, seq_in_index
)
Group by table_schema, table_name, index_name
) AA
Order by db, tb
;
2. Run
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.
3. Regular optimization and database reconstruction

Mysqlcheck-o-all-databases will increase ibdata1. The true optimization is only to reconstruct the data table structure:
Create table mydb. mytablenew LIKE mydb. mytable;
Insert into mydb. mytablenew SELECT * FROM mydb. mytable;
Alter table mydb. mytable RENAME mydb. mytablezap;
Alter table mydb. mytablenew RENAME mydb. mytable;
Drop table mydb. mytablezap;
4. Reduce disk write operations

4.1 Use a large enough write cache
Innodb_log_file_size
However, it should be noted that if the innodb_log_file_size of 1 GB is used, it will take 10 minutes to restore the server.
Innodb_log_file_size = 0.25 innodb_buffer_pool_size is recommended
4.2 innodb_flush_log_at_trx_commit
This option is closely related to disk write operations:
Innodb_flush_log_at_trx_commit = 1. Each modification is written to the disk.
Innodb_flush_log_at_trx_commit = 0/2 writes to disk per second
If your application does not involve high security (financial system), or the infrastructure is secure enough, or the transaction is very small, you can use 0 or 2 to reduce disk operations.
4.3 avoid double write buffering
Innodb_flush_method = O_DIRECT
5. Improve disk read/write speed

RAID0, especially when using a virtual disk (EBS) such as EC2, is very important to use soft RAID 0.
6. Full use of indexes

6.1 view existing table structures and indexes
Show create table db1.tb1G
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.
ADD UNIQUE INDEX
ADD INDEX
6.2.1 for example, optimize the user verification table:
Add index
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.
Echo "select username, password from users;">/var/lib/mysql/upcache. SQL
Add the startup script to my. cnf
[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.
7. Analyze query logs and slow query logs

Record all queries, which is useful in the ORM system or the system that generates query statements.
Log =/var/log/mysql. log
Be sure not to use it in the production environment, otherwise it will occupy your disk space.
Query with record execution time exceeding 1 second
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/ramdisk
Sudo mount-t tmpfs-o size = 4000 M tmpfs/mnt/ramdisk/
Mv/var/lib/mysql/mnt/ramdisk/mysql
Ln-s/tmp/ramdisk/mysql/var/lib/mysql
Chown 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
11. End

You will find that the database performance is improved several times to several hundred times after optimization. Therefore, MYSQL is applicable to most scenarios. The cost of optimizing the existing system is much lower than that of restructuring the system or migrating it to NOSQL.