Shulou(Shulou.com)06/01 Report--

Let me give you a brief introduction to several common ways to optimize mysql. Have you known about similar topics before? If you are interested, let's take a look at this article. I believe it will be more or less helpful to you after reading several common methods of optimizing mysql.

1. Select the appropriate storage engine: InnoDB

Unless your datasheet is used for read-only or full-text search (I believe no one will use MYSQL when it comes to full-text search these days). You should choose InnoDB by default.

When you test it yourself, you may find that MyISAM is faster than InnoDB. This is because: MyISAM only caches indexes, while InnoDB caches data and indexes, and MyISAM does not support transactions. But suppose you use innodb_flush_log_at_trx_commit = 2 to get similar read performance (a hundredfold difference).

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.sqlperl-p-I-e's / (search_ [a murzz] + ENGINE=) InnoDB//1MyISAM/g' alter_table.sqlmysql-u [USER_NAME]-p [DATABASE_NAME]

< alter_table.sql 1.2 为每一个表分别创建 InnoDB FILE： innodb_file_per_table=1 　　这样能够保证 ibdata1 文件不会过大。失去控制。尤其是在运行 mysqlcheck -o -all-databases 的时候。 2. 保证从内存中读取数据。讲数据保存在内存中 2.1 足够大的 innodb_buffer_pool_size 　　推荐将数据全然保存在 innodb_buffer_pool_size ，即按存储量规划 innodb_buffer_pool_size 的容量。这样你能够全然从内存中读取数据。最大限度降低磁盘操作。 2.1.1 怎样确定 innodb_buffer_pool_size 足够大。数据是从内存读取而不是硬盘？ 方法 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 (sec)

If Innodb_buffer_pool_pages_free is found to be 0, it means that buffer pool has been used up and innodb_buffer_pool_size needs to be increased.

Several other parameters of InnoDB:

Innodb_additional_mem_pool_size = 1100 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 drive.

2.1.2 is there enough memory on the server for planning

Run echo 1 > / proc/sys/vm/drop_caches to clear the operating system's file cache. You can see the real memory usage.

2.2 data preheating

By default, a piece of data is cached in innodb_buffer_pool only if it is read once. Therefore, the database has just started, and it is necessary to warm up the data and cache all the data on the disk into memory.

Data preheating can improve the reading speed.

For InnoDB databases, you can warm up the data using the following methods:

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.Engineerie. A.seq_in_index FROM information_schema.statistics An 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

two。 Running

Mysql-uroot-AN

< /root/MakeSelectQueriesToLoad.sql >

/ root/SelectQueriesToLoad.sql

3. Run every time you restart the database, or when you need to warm up before backing up the entire database:

Mysql-uroot

< /root/SelectQueriesToLoad.sql >

/ dev/null 2 > & 1

2.3 do not allow data to be stored in SWAP

Suppose it is a dedicated MYSQL server. You can disable SWAP, assuming that you share server, and make sure that the innodb_buffer_pool_size is large enough. Or use a fixed memory space for caching and use the memlock instruction.

3. Optimize and rebuild the database regularly

Mysqlcheck-o-all-databases will make ibdata1 grow. The only real optimization is to rebuild the structure of the data table:

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 is important to be careful to assume the use of 1G innodb_log_file_size. If server crashes. It will take 10 minutes to recover.

It is recommended that the innodb_log_file_size be set to 0.25 * innodb_buffer_pool_size

4.2 innodb_flush_log_at_trx_commit

This option is closely related to write disk operations:

If innodb_flush_log_at_trx_commit = 1, each change is written to disk.

Innodb_flush_log_at_trx_commit = 0x2 write to disk per second

Suppose your application does not involve very high security (financial system), or the infrastructure is secure enough, or the transactions are 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 and write speed

RAID0, especially when using a virtual disk (EBS) such as EC2, it is important to use soft RAID0.

6. Make full use of the index

6.1 View existing table structure and indexes

SHOW CREATE TABLE db1.tb1/G

6.2 add the necessary indexes

Indexing is the only way to improve query speed. For example, the inverted index used by search engines is the same principle.

The addition of the index should be determined according to the query. For example, through slow query logs or query logs, or through the EXPLAIN command to analyze the query.

ADD UNIQUE INDEXADD INDEX

6.2.1 for example, optimize the user verification table:

Join the index

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

Restart server each time to warm up the data

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 use your own active indexing framework or your own active split table structure framework

For example. The framework of Rails. Will take the initiative to join the index. A framework like Drupal actively splits the table structure on its own.

Will point in the right direction in the early stages of your development. Therefore, it is actually a bad practice for less experienced people to pursue building from zero at the beginning.

7. Analyze query log and slow query log

Record all queries. This is very useful in using ORM systems or systems that generate query statements.

Log=/var/log/mysql.log

Be careful not to use it in the production environment. Otherwise, it will take up your disk space.

Record queries that have been run for more than 1 second:

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

8. A radical approach. Use memory disk

Today's infrastructure is so reliable that EC2 almost doesn't have to worry about server hardware downtime. And memory is really cheap. Very easy can buy dozens of gigabytes of memory server, which can use memory disk. Backup to disk on a regular basis.

Migrate MYSQL folder to 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/ramdisk/mysql / var/lib/mysqlchown mysql:mysql mysql

9. Using MYSQL in the NOSQL way

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

Use HandlerSocket to skip MYSQL's SQL parsing layer. MYSQL really becomes NOSQL.

10. Other

● adds LIMIT 1 at the end of a single query to stop full table scanning.

● separates non-"index" data, such as separating and storing large articles, without affecting other active queries.

● does not use the built-in functions of MYSQL. Because the built-in function does not establish a query cache.

● PHP is very fast to establish connections, all without connection pooling. Otherwise, the number of connections may be exceeded. Of course, without connection pooling, the PHP program may also set the

@ ignore_user_abort (TRUE) is used for the example of full number of ● connections.

● uses IP instead of the domain name as the database path. Avoid DNS parsing problems

What do you think of several common ways to optimize mysql? what do you think of this article and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.