10 ways to tune MySQL performance

MySQL optimization, in general, we rarely think about it, even if the idea of optimization is generally more program-level, such as not to write too much resource-intensive SQL statements, but in addition, there are still many areas of the system can be optimized.

1. Select the appropriate storage engine: InnoDB

Unless your data sheet is used for read-only or full-text search (believe that you are now referring to full-text search, no one will use MYSQL), you should choose InnoDB by default.

You may find that MyISAM is faster than InnoDB at the time of testing, because MyISAM only caches indexes, and InnoDB caches data and indexes, MyISAM does not support transactions. But if you use Innodb_flush_log_at_trx_commit = 2 You can get close reading performance (a few times).

We need to create InnoDB FILE for each table separately,

Innodb_file_per_table=1

This ensures that the Ibdata1 file will not be too large and out of control. Especially when it comes to executing mysqlcheck-o–all-databases.

2. Ensure that data is read from memory and the data is stored in memory

2.1 Large enough Innodb_buffer_pool_size
It is recommended that the data be fully stored in the innodb_buffer_pool_size, that is, the capacity of innodb_buffer_pool_size planned by storage. This allows you to read the data completely from memory, minimizing disk operations.

2.1.1 How to determine innodb_buffer_pool_size is large enough that the data is read from memory instead of hard disk?

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)

When the innodb_buffer_pool_pages_free is found to be 0, the buffer pool has been exhausted and needs to be increased innodb_buffer_pool_size
Several other parameters of InnoDB:

Innodb_additional_mem_pool_size = 1/200 of buffer_poolinnodb_max_dirty_pages_pct 80%

Method 2
or use the iostat-d-x-k 1 command to view the operation of the hard disk.
If there is enough memory on the 2.1.2 server to plan
perform echo 1 >/proc/sys/vm/drop_caches to clear the operating system's file cache, you can see true memory usage.
2.2 data preheating
By default, only one piece of data is read once before it is cached in Innodb_buffer_pool. As a result, the database has just started and data preheating is required to cache all the data on the disk into memory. Data preheating can improve read speed.
for InnoDB databases, data preheating can be done in the following ways:
1. Save the following script as Makeselectqueriestoload.sql

Select DISTINCT    CONCAT (' select ', Ndxcollist, ' from ', db, '. ', terabytes,    ' 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. Implementation

Mysql-uroot-an </root/makeselectqueriestoload.sql >/root/selectqueriestoload.sql

3. Do this every time you restart the database, or if you need to warm up before backing up the whole library:

Mysql-uroot </root/selectqueriestoload.sql >/dev/null 2>&1

2.3 Do not allow data to be stored in SWAP
If you are a dedicated MYSQL server, you can disable SWAP and, if it is a shared server, make sure Innodb_buffer_pool_size is large enough. Or use a fixed memory space for caching, using the Memlock directive.
3. Regular optimization of the rebuilding database
Mysqlcheck-o–all-databases will make ibdata1 grow, real optimization only to reconstruct the data table structure:

CREATE TABLE mydb.mytablenew like Mydb.mytable;insert to 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 is important to note that if you use a 1G innodb_log_file_size, it takes 10 minutes to recover if the server is a machine.
Recommended Innodb_log_file_size set to 0.25 * innodb_buffer_pool_size
4.2 Innodb_flush_log_at_trx_commit
This option is closely related to the write disk operation:
Innodb_flush_log_at_trx_commit = 1 writes to disk each time it is modified
Innodb_flush_log_at_trx_commit = 0/2 writes to disk per second
If your application does not involve high security (financial systems), or the infrastructure is secure enough, or the transaction is small, you can reduce disk operations by 0 or both.
4.3 Avoid double-write buffering

Innodb_flush_method=o_direct

5. Improve disk read and write speed
RAID0 the use of soft RAID0 is especially important when using EC2 as a virtual disk (EBS).
6. Full use of the index
6.1 Viewing existing table structures and indexes

SHOW CREATE TABLE db1.tb1/g

6.2 Adding the necessary indexes
Indexing is the only way to improve query speed, such as a search engine with inverted index is the same principle.
The addition of the index needs to be determined by the query, such as by slow query log or query log, or by EXPLAIN command to parse the query.

ADD UNIQUE Indexadd INDEX

6.2.1 For example, optimize the user authentication table:
Add index

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username); ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (Username,password);

Data preheating every time you restart the server

echo "Select Username,password from Users;" >/var/lib/mysql/upcache.sql

Add startup script to My.cnf

[Mysqld]init-file=/var/lib/mysql/upcache.sql

6.2.2 frame with auto-indexed frame or auto-split table structure
For example, a framework such as Rails will automatically add an index, and a framework like Drupal will automatically split the table structure. Will indicate the right direction at the beginning of your development. Therefore, the less experienced people from the beginning of the pursuit of starting from 0 to build, is actually bad practice.
7. Analyze query logs and slow query logs
Log all queries, which are useful for systems that use an ORM system or generate query statements.

Log=/var/log/mysql.log

Be careful not to use it in a production environment, or it will fill your disk space.
Record a query that takes more than 1 seconds to execute:

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

8. Aggressive methods, using a memory disk
The reliability of the infrastructure is now very high, such as EC2 almost no need to worry about server hardware. And the memory is really cheap, it is easy to buy dozens of g of memory of the server, you can use the memory disk, regular backup to disk.
Migrating a MYSQL directory to a 4G memory disk

Mkdir-p/mnt/ramdisksudo mount-t tmpfs-o size=4000m tmpfs/mnt/ramdisk/mv/var/lib/mysql/mnt/ramdisk/mysqlln-s/tmp/r Amdisk/mysql/var/lib/mysqlchown Mysql:mysql MySQL

9. Using MYSQL in a NOSQL way
B-tree is still one of the most efficient indexes, and all MYSQL is still not obsolete.
By skipping MySQL's SQL parsing layer with Handlersocket, MySQL is really becoming NOSQL.
10. Other

• A single query finally adds LIMIT 1 to stop the full table scan.

• Separating non-"indexed" data, such as separating large articles from storage, does not affect other automatic queries.

• No MYSQL built-in functions, because built-in functions do not establish a query cache.

• PHP is very fast to establish connections, all of which can be used without a connection pool, or it may cause more than the number of connections. Of course, without the connection pool PHP program may also be

• The number of connections is full, such as @ignore_user_abort (TRUE);

• Use IP instead of domain name to do database path, avoid DNS parsing problem

11. End
You will find that after optimization, the performance of the database is increased several times to hundreds of times. So MYSQL basically still can apply most of the scene of the application. The cost of optimizing an existing system is much lower than the system refactoring or migrating to NOSQL.

10 ways to tune MySQL performance