10 Mysql Performance Tuning Methods _mysql

MYSQL should be the most popular WEB back-end database. The WEB development language has recently grown rapidly, with PHP, Ruby, Python, and Java features, although NOSQL has recently become more and more mentioned, but it is believed that most architects will still choose MYSQL for data storage.

MYSQL is so convenient and stable that we rarely think about it when developing WEB applications. Even if the idea of optimization is program-level, for example, do not write overly resource-consuming SQL statements. But beyond that, there is still a lot of room for optimization throughout the system.

1. Select the appropriate storage engine: InnoDB

Unless your datasheet is used to do read-only or Full-text search (I believe you now refer to Full-text search, no one will use MYSQL), you should choose InnoDB by default.

You may find that MyISAM is faster than InnoDB when you test yourself, 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 read performance (a hundredfold).

1.1 How to convert an existing MyISAM database to InnoDB:

Copy Code code 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//1myisam/g ' Alter_table.sql
Mysql-u [user_name]-P [database_name] < Alter_table.sql

1.2 Create InnoDB FILE for each table individually:

Copy Code code as follows:

Innodb_file_per_table=1

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

2. Ensure that data is read from memory and that data is kept in memory

2.1 Big enough to innodb_buffer_pool_size

It is recommended that the data be fully stored in innodb_buffer_pool_size, that is, the capacity of the innodb_buffer_pool_size is planned in storage. This allows you to read the data completely from memory and minimize disk operation.

2.1.1 How do I determine if the innodb_buffer_pool_size is large enough that the data is read from memory rather than 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)

The discovery of Innodb_buffer_pool_pages_free as 0 indicates that the buffer pool has been depleted and needs to be increased innodb_buffer_pool_size

Several other parameters of InnoDB:

Copy Code code as follows:

Innodb_additional_mem_pool_size = 1/200 of Buffer_pool
innodb_max_dirty_pages_pct 80%

Method 2

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

Is there enough memory on the 2.1.2 server to plan
performing the echo 1 >/proc/sys/vm/drop_caches clears the operating system's file cache to see the true amount of memory used.

2.2 Data preheating

By default, only a single piece of data is read once before it is cached in Innodb_buffer_pool. As a result, the database is just starting up and needs to be warmed up to cache all the data on the disk into memory. Data preheating can improve reading speed.

For InnoDB databases, you can use the following methods to preheat data:

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
  ) AA ORDER by
DB,TB
;

2. Implementation

Copy Code code as follows:

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

3. Perform every time you restart the database, or when you need to warm up before a full library backup:

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

2.3 Do not allow data to be stored in SWAP

If it is a dedicated MYSQL server, you can disable SWAP and, if it is a shared server, make sure the innodb_buffer_pool_size is large enough. Or use a fixed memory space for caching, using the memlock instruction.

3. Periodically optimize the rebuilding of the database

Mysqlcheck-o–all-databases will allow ibdata1 to grow, the real optimization is only to rebuild 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

But note that if you use the 1G innodb_log_file_size, if the server is a machine, it will take 10 minutes to recover.

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 Write disk every time you modify it
Innodb_flush_log_at_trx_commit = 0/2 Write disk per second

If your application does not involve a high security (financial system), or if the infrastructure is secure enough, or the transaction is small, you can reduce the disk operation by 0 or both.

4.3 Avoid double write buffering

Copy Code code as follows:

Innodb_flush_method=o_direct

5. Improve disk read and write speed

RAID0 especially when using EC2 virtual Disk (EBS), the use of soft RAID0 is very important.

6. Full use of the index

6.1 View existing table structures and indexes

Copy Code code as follows:

Show CREATE TABLE db1.tb1/g

6.2 Adding the necessary indexes

Indexing is the only way to improve the speed of queries, such as the inverted index used by search engines is the same principle.

Index additions need to be determined by query, such as through slow query logs or query logs, or through the EXPLAIN command to parse the query.

Copy Code code as follows:

ADD UNIQUE INDEX
ADD INDEX

6.2.1 For example, to optimize user authentication tables:
Add index

Copy Code code as follows:

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

Data preheating each time the server is restarted

Copy Code code as follows:

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

Add startup script to My.cnf

Copy Code code as follows:

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

6.2.2 frames that use an automatically indexed frame or an automatic split table structure
For example, a framework such as Rails automatically adds indexes, and a framework like Drupal automatically splits the table structure. Will indicate the right direction at the beginning of your development. Therefore, it is not good practice for people with less experience to begin to build from 0 in the beginning.

7. Analyze query log and slow query log

Log all queries, which are useful in ORM systems or systems that generate query statements.

Copy Code code as follows:

Log=/var/log/mysql.log

Be careful not to use in the production environment, or you will fill up your disk space.

Record a query that is more than 1 seconds in execution time:

Copy Code code as follows:

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

8. Radical way to use memory disk

The reliability of the infrastructure is now very high, for example, EC2 has little to worry about server hardware. And the memory is really cheap, easy to buy dozens of G memory server, can use memory disk, regular backup to disk.

Migrate the MYSQL directory to a 4G memory disk

Mkdir-p/mnt/ramdisk
sudo mount-t tmpfs-o size=4000m tmpfs/mnt/ramdisk/
mv/var/lib/mysql/mnt/ramdisk/mysql< C5/>ln-s/tmp/ramdisk/mysql/var/lib/mysql
chown mysql:mysql MySQL

9. Use MYSQL in a NOSQL way

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

Using Handlersocket to skip MySQL's SQL parsing layer, MySQL really becomes NOSQL.

10. Other

A single query finally increases LIMIT 1, stopping the full table scan.
Separating non-"indexed" data, such as separating large articles from storage, does not affect other automated queries.
No MYSQL built-in functions, because built-in functions do not create a query cache.
PHP is built to connect very quickly, all can not be connected to the pool, otherwise it may cause more than the number of connections. Of course, no connection to the pool PHP program may also
The number of connections is full, such as @ignore_user_abort (TRUE);
Use IP rather than domain name to do database path to avoid DNS resolution problems

The above is 10 MySQL performance tuning method, I hope to help you learn.