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

Editor to share with you an introduction to mysql tuning of the case study, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

A brief introduction

Let's not talk about the frequency of cpu, the size of memory (which is as important as indexing, but not what this article discusses), and the seek time of the hard disk. To think of mysql tuning, you must at least know explain execution plans, slow sql logs, old profile commands, new performance_schema performance views and tables related to current transactions and memory footprint information in information_schema, as well as show engine innodb status diagnostics, and some tps,qps,iops metrics in metrix. (related recommendation: "MySQL tutorial")

These are some of the tools for tuning, and databases provide a lot of large and small functions for high availability, including: replication, group replication, partitions, file links: that is, log logs and data files can be placed on different hard drives. The small ones are: calculating columns, calculating hash for columns, index merging, index push-down, MRR,BKA,Loose Index and other algorithms, as well as fill factors.

Of course, there are no view indexes and distributed partitioned views, and join only supports nested, which is the deficiency of mysql, while sql server join's algorithm supports three kinds, loop while hash, which greatly improves the speed of join. Mysql does not have many features to improve performance, and others are based on experience, such as static tables, do not use functions in subqueries, try to turn subqueries into join queries, non-string and blob columns are always slower than other numbers or time columns, join | order by | group must not let them generate temporary tables on the hard disk, of course, this is related to memory, narrow table and wide table design, of course, ultimately depends on your business type.

There are two ways to optimize, one is run-time, that is, it is optimized on the running server, and the other is in the process of development. Either way, performance_schema will need it.

Two performance_schema explanation

The performance view is available in every database, and sql server is a series of memory tables that begin with dm_*. Mysql is the various tables in the performance_schema library. First, take a look at the tables in the entry:

SELECT * FROM setup_timers;-timing definition table select * from setup_actors;-those users need to collect information select * from Setup_objects;-those objects need to collect information, such as mysql table, select * from setup_consumers;-the classification of those instruments need to collect select * from setup_instruments -- collecting instruments, each function point will have the event, start and end of the instrument, and then turn on that instrument, and then collect the data of that instrument.

First, let's take a look at the switch that turns on performance_schema:

Show variables like 'performance_schema'-- this is a read only variable

If OFF, you need to open it in the configuration file.

Then let's introduce these entry tables one by one.

1, setup_ Actors table

All users can collect it.

2,Setup_objects

Those objects can be collected, such as table or trigger, etc. As for turning off the two column controls, the enabled and timed fields are set to No, as is the case for both tables.

3 setup_consumers

For the classification of events, stages is a step, and a statement is a process step executed on the server. The result is the same as profile. Profile is not recommended because it will be removed later. Transaction is event collection of transactions, and so on.

4 setup_instruments

This is the main event monitoring device, as follows:

5 finally, there is setup_timers, which defines the time type of those instruments in conjunction with performance_timers, as follows:

CYCLE:cpu clock, TIMER_FREQUENCY is how many seconds, TIMER_RESOLUTION is how many increments each time, and finally how often to get this time.

Using performance_schema to obtain priofile data

Open the relevant instrument:

We look at the information in the above instrument classification table setup_consumers. The lines about stage are all NO, so we need to change it to YES. At the same time, we need to get the information in the statements monitoring table later, so we also need to enable statements:

UPDATE setup_consumers SET ENABLED = 'YES'WHERE NAME LIKE'% stage%';UPDATE setup_consumers SET ENABLED = 'YES'WHERE NAME LIKE'% statements%'

Then turn on stage's instrument.

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED =' YES'WHERE NAME LIKE'% stage/%';-- turn on monitoring UPDATE performance_schema.setup_instruments SET ENABLED for all execution steps = 'YES', TIMED =' YES'WHERE NAME LIKE'% statement/%'

Execution is based on sql

Select * from quartz.TestOne

Query the queryid of this statement:

SELECT EVENT_ID, TRUNCATE (TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like'% quartz%'

So id is 509.

Then execute the performance monitoring table:

SELECT event_name AS Stage, TRUNCATE (TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=509

The content is the same as the old version of profile.

Mainly look at the stage/sql/Sending data line, this line is the main io-related events, in general, sql is slow, and the value of this line is relatively large, then the hard disk must be slow to read data or lock conflicts.

Then error log is used. If there is a deadlock, mysql will enter the deadlock information into the error log. Show engine innodb status is just some global information. If you want to see more details, monitor the corresponding instrument.

And at present, mysql8 mostly supports NOWAIT and skiplocked statements, and the usage is still select... From shows that for update/for nowait and others are very flexible in dealing with deadlocks. Of course, you can also make its transaction isolation level dirty read level, but it can not solve more business types. Setting deadlock timeout is also a feasible way.

The above is all the contents of the article "case study of an introduction to tuning of mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.