10 ways to tune the performance of < turn >mysql

Article for stroll public number see, because before a period of time has been doing performance testing, the company also uses MySQL database, the project MySQL optimization many aspects and this article similar, recommend to everybody ...

Article Original address: HTTP://MP.WEIXIN.QQ.COM/S/ORXJRZ_Y5DRMIRCBXSKOCW

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).

1.1 How to convert an existing MyISAM database to InnoDB:

' s/(search_[a-z_]+ engine=) innodb//1myisam/g ' alter_table.sqlmysql-u [user_name]-P [database_name] < Alter_ Table.sql

1.2 Create InnoDB FILE separately for each table:

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 that 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 Span class= "Hljs-keyword" >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:

1/80%

Method 2

Or use the iostat-d-x-k 1 command to view the operation of the hard disk.

Is there 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 and see real 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 the InnoDB database, the data can be warmed up in the following ways:

1. Save the following script as Makeselectqueriestoload.sql

SELECTDISTINCTCONCAT (' SELECT ', ndxcollist,' From ', DB,'. ', TB,' ORDER by ', Ndxcollist,‘;‘) SelectquerytoloadcacheFrom (SELECTEngine,table_schema db,table_name TB, index_name,Group_concat (column_nameORDERby Seq_in_index) ndxcollistFrom (SELECT B.engine,a.table_schema,a.table_name, A.index_name,a.column_name,a.seq_in_indexFrom Information_schema.statistics Ainner 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 ', Span class= "hljs-string" > ' 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

/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:

/dev/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:

Like mydb.mytable; From    mydb.mytable;   RENAME mydb.mytablezap;   RENAME mydb.mytable;  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. Fully use index 6.1 to view existing table structures and indexes

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

INDEX username_ndx (username);      INDEX username_password_ndx (username,password); 

Data preheating every time you restart the server

echo "fromusers;" >/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/t Mp/ramdisk/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 the performance of < turn >mysql