How a single commit and multiple commit affect the IOPS of the system.

How a single commit and multiple commit affect the IOPS of the system.
Zookeeper

The name and sensitive number desensitization statement in this article:

In order to protect personal privacy, the real name of the expert is desensitized and replaced by the personal name. For mobile phone numbers and QQ numbers, some are replaced by the "X" symbol.

 

1. Questions from domestic senior Performance Optimization Expert Mr. laobai

How does a single COMMIT and multiple COMMIT affect the system's IOPS? Why?

2. Xiao Di LI Junjie (Network Name: Click to answer)

9:41:57 LI Junjie 134XXXXXXX1 (389338XX3) The IOPS of a single commit must be greater than that of multiple commit entries. Once each commit is performed, LGWR will have a log block for writing, that is, at least one IOPS occurs, if the size of multiple records is smaller than the size of one block, only one IOPS occurs. Generally, a single record is smaller than the size of one block. I wonder if this is the case. Please advise Xu.

3. reply from Mr. laobai

Laobai 138XXXXXXX0 (62XX5) 9:44:24 First, analyze the pieces of IO generated by batch inserts, Then estimate the approximate size.

The younger brother thought, even if Mr. laobai replied this way, it would definitely indicate that my answer was incomplete. Well, let's take a look at it. Let's take a look at it.

4. The younger brother Li Junjie (Network Name: Click it) then responds

LI Junjie 134XXXXXXX1 (389338XX3) 9:51:34 When batch inserts are performed, the LGWR and DBWR sections generate IO.

5. Mr. laobai replied again

Laobai 138XXXXXXX0 (62XX5) 9:56:20 If the problem needs to be further analyzed, the impact of indexes should also be considered.

In this case, the younger brother immediately takes a selfie and inserts data into his head. If there is an index on the table, the index will be maintained. In the answer to this question, how can I not broaden my mind? inserting data to maintain indexes will also lead to I/O, but simply focusing on the specific "insert" event in the question, regardless of the event. It seems that we have to admit the gap between ourselves and our experts.

Let's answer it honestly. Down...

6. Intercept

Hu jun 150XXXXXXX8 (275475005) 9:57:21 Generally, the data volume inserted in batches is large.

Hu jun is my buddy and a senior of the Optimization Team of the sub-worker. It seems that this guy is busy.

7. Wait for your breath, and Mr. laobai will continue his guidance.

Laobai 138XXXXXXX0 (62XX5) 9:57:35 IOSeveral blocks are generated, including pre-read generated by formatting data blocks, single-block read generated by index modification, DBWR write, and LGWR write logs.

8. Reply from the inner's heart

LI Junjie 134XXXXXXX1 (389338XX3) 9:57:42 I did not think of an index when I was thinking about it.

I have to say that I did not think of indexing at the time, so I have a long knowledge here. But fortunately, the answer is half the answer, indicating that the main cause of IO is DBWR and LGWR.

9. Let's look at the expert's extraordinary speech.

Laobai 138XXXXXXX0 (62XX5) 9:58:00 There are also temporary tablespace read/write DIRECT paths that may be generated by the sorting generated by mass index modifications.

Seeing this guiding remark, I have to say that "experts are experts". The problem analysis is comprehensive and in-depth, so that the younger brother can admire me!

 

Today, my younger brother has a long knowledge. It is really good to communicate with experts, and I really hope to have more exchanges with experts.

 

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244