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

The performance optimization of mysql is a common problem faced by operators and DBA, and it is also one of the key points for major companies to recruit talents. Performance optimization sounds difficult, and it seems that only a god can do it. However, the performance optimization of mysql can not be done by operation and maintenance alone. DBA, development, and architecture should also be involved, and concerted efforts can win the battle of performance optimization.

This paper will comprehensively explain all aspects of database optimization, that is, hardware, network, system, architecture and software. The software mainly includes software version, table design, engine, SQl statement, configuration file my.cnf and so on.

Hardware and network

How should the hardware and network be optimized? Two words "throw money"! Buy high-quality servers, high-performance multicore cpu, high memory, high-performance disks or SSD disks, etc. High hardware configuration is the basis of high database performance. Generally speaking, a database machine with 72GB memory is sufficient, and the specific configuration can also be added according to the company's business needs. The higher the performance of the basic accessories of the network, the better, N-megabit optical fiber, N-megabit switch, N-megabit network card. Of course, if the company is short of money, then choose the appropriate basic configuration for the current business. In a word: hardware and network should be optimized with money!

Second system

Operating system choice, prefer the current popular and stable version, such as centos6.5 centos6.8, try not to use version 5, if the company is in the overall environment upgrade stage, you can directly change to the latest version 7, its performance is better than version 5, 6; if you are not short of money, you can also use the corresponding version of redhat, I suggest you still use centos, free and active community, convenient for later management upgrade. AIX system is not recommended, as for what reason, I can only give AIX system the word "hehe". Try to use ext4 on the file system!

Three-frame system

The architecture of mysql database is relatively simple, from single machine to one master and one slave, then to one master and multi-slave, and then to multi-master and multi-slave, and then to read-write separation, hierarchical storage, sub-database and sub-table. there are so many main forms. How to choose is based on business needs. Companies with less business can consider one master and multiple slaves, and generally companies can achieve master-slave + read-write separation, so there are not only performance but also backup. Only those with a large amount of data can be used for sub-database and sub-tables.

4. Database version control

The database version is closely related to the performance of the database, the higher the version, the richer the function, the stronger the performance, but the high version has hidden dangers that may not be discovered. At present, most systems come with mysql5.1. This version is stable and long, and can be used in database environments with low functional and performance requirements. However, it is recommended to upgrade to mysql5.6 or above in the enterprise environment. If the stand-alone performance requirements are extremely high, you can use mysql5.7 or mariadb10.1 or percona5.7;mysql5.6. The application in the enterprise environment is common and the performance is stable. It is recommended to use this version. If you want to use the latest version of the high-performance database (the read and write performance of version 5.7 is twice as good as that of version 5.6), mariadb10.1 is recommended because mysql Enterprise Edition charges and tends to be closed. To sum up: the corresponding version of mysql5.6 or mariadb10.1 is recommended.

The higher version of mysql5.7 Enterprise Edition does have a big improvement over the previous version:

1 in read mode, performance is improved by 3 times; in read and write mode, performance is improved by 2 times.

2 ssl security mode is enabled by default

> enable bin/mysql_ssl_rsa_setup

3 Buffer_pool can be dynamically resized without reboot

Set global innodb_buffer_pool_size=256*1024*1024 # # set 256m

4 buffer pool preheating

Innodb_buffer_pool_dump_at_shutdown=1 innodb_buffer_pool_load_at_startup=1

5 full Chinese indexing is supported

6 support deadlock printing to error log innodb_print_all_deadlocks=1

7 supports data storage in json format

8 supports kill slow sql set global max_statement_time=1

9 support log audit audit plugin

10 support error log printing to Syslog file

Optimization of five-meter design

The relational database is faced with the choice of paradigm, which at least satisfies the first paradigm: the fields in the table are all single attributes and can not be separated; the second paradigm requires that the attributes of entities are completely dependent on the primary keyword; the third paradigm is that there is no functional dependency of non-keywords on any candidate primary key. The design of the database should satisfy the three paradigms to the greatest extent, of course, the three paradigms also have problems, it usually requires a lot of join tables, resulting in inefficient query; sometimes redundancy can be done appropriately to reduce join, but be treated with caution!

The selection of table field type is also the focus of optimization. The principle of selection is to choose the small rather than the big as far as possible, and the fields that can use fewer bytes do not need the large fields. Smaller field types take up less memory, smaller disks and memory, and less broadband, so you must stick to "small rather than big" when making field choices. For example, for primary keys, int integers are highly recommended.

Numerical types generally used int (4 bytes) and bigint (8 bytes), such as mobile phone number available bigint, age with tinyint; character types most commonly used are char (256) and varchar (65535), they are also related to character encoding, latin1 occupies one byte, gbk takes 2 bytes, utf8 takes 3 bytes; time types commonly used date (3 bytes) and datetime (8 bytes), timestamp (4 bytes). When selecting various field types, choose the one that takes up the least bytes and is the most appropriate.

To modify the table structure online, you can use percona's tool pt-online-schema-change without affecting the business.

6. Selection of database engine

MyISAM and InnoDB are the two most commonly used storage engines for mysql databases. Before version 5.5, the default is MyISAM, and later versions default to InnoDB. The main differences between the two are as follows:

1 MyISAM is non-transaction secure and InnoDB is transaction secure.

2 MyISAM application table-level lock, low overhead, InnoDB is a row-level lock, high overhead, support for better concurrent write operations

3 MyISAM supports full-text indexing, while Innodb supports it after version 5.6.

4 MyISAM is relatively simple, easy to manage and efficient. Small applications can consider using MyISAM engine.

5 MyISAM table is saved as a file, which is easy to migrate across platforms.

6 InnoDB is more secure than MyISAM and can be switched from non-transactional to transactional at any time

According to tests, in the same configuration of stress tests, the performance of InnoDB is about 10 times that of MyISAM, so in the selection of applications with a large number of read and write operations, InnoDB is recommended to give priority to the use of the engine!

7. SQL sentence optimization

SQL statements determine 70% of the performance of the database, and most poor performance is caused by sql statements.

Normal hardware + normal configuration + perfect sql statement = General performance

Perfect hardware + perfect configuration + junk sql statement = poor performance

Perfect hardware + perfect configuration + General sql statement = General performance

Perfect hardware + perfect configuration + perfect sql statement = excellent performance

1 locate slow SQL statement (record)

Enable the slow log feature and add configuration to my.cnf:

Slow_query_log=1

Slow_query_log_file=mysql.slow

Long_query_time=2 # record it for more than two seconds

When the number of database connections is high, you can intercept full logs for a certain period of time.

Sed-n'# time 2017-03-08 14 mysql.slow 3000 ordinance Endash p' mysql.slow > slow.log

Then use the mysqldumpslow command to extract the 10 slow sql analyses that take the longest time.

Mysqldumpslow-s t-t 10 slow.log

2 optimize not in subquery

Replace not in subquery with left join

Original statement: > select sql_cache count (*) from T1 where id not in (select id from T2)

Optimization: > select sql_cache count (*) from T1 left join T2 on t1.id=t2.id where t2.id is null

3 optimize like statement

In mysql, indexes can be used by like 'xxx%', but not by like'% xxx%'.

Using indexes can reduce IO and improve performance.

Original statement: > select * from T1 where name like'% game%'

Optimization: > select id from T1 where name like'% game%'

4 limit paging optimization

Original statement: > select game * from T1 order by id limit 999910

Optimization: > select game * from T1 where id > = 100order by id limit 10

5 optimize count statistics

Using secondary index and distinct optimization

Original statement: > select count (*) from my_user

Optimization: > select count (*) from my_user where id > = 0

> select count (*) from (select distinct k from my_user) tmp

6 optimize or statement

Use union all instead of or

Original statement: > select * from user where name='a' or age=18

Optimization: > select * from user where name='a' union all select * from user where age = 18

7 rational use of index

Eight my.cnf profile optimization

There are many items that can be set in the configuration file, and the following lists the important items that you often need to pay attention to.

The maximum number of connections at 1 max_connections. Default is 100. generally, you can set it to 500-1000.

2 innodb_buffer_pool_size, with a default of 128m, can be set to 60% of physical memory.

3 query_cache_size=64M query_cache_type=1 query_cache_limit=1M

4 wait_timeout=100 waiting time

5 connect_timeout=20 interactive_timeout=100

6. Enable slow log on slow_query_log=1

7 thread_cache_size=64

8 relay_log_recovery=1 Relay Log recovery

9 open_files_limit=28512

.

The specific configuration parameters need to be determined according to the hardware environment and business requirements.

Conclusion: mysql performance optimization is a huge system project, which requires the participation of operation and maintenance, architecture, development and other parties to improve. Do not blindly do tuning processing, carefully analyze the performance bottleneck, aiming at the bottleneck can get twice the result with half the effort!

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.