Discussion on the effect of single commit and multiple commits on the IOPS of system



The name and sensitive number desensitization statement in this article:

for the protection of personal privacy, the expert's real name for desensitization, the use of personal name instead, for mobile phone number and QQ number, part of the "X" symbol replaced.

1, domestic senior performance optimization expertsOld WhiteMr. (pseudonym: Eel with, person in old white) questions

Single COMMIT and more than one COMMIT , the system-generated IOPS what impact, how much impact, why?

2, Brother Li Junjie (Network name: casing) Answer

Li Junjie 134xxxxxxx1 (389338xx3) 9:41:57 SingleCommitof theIOPSmust be more than oneCommit, everyCommitonce,LGWRthere will be a log block to write, which occurs at least onceIOPSmultiple strips together.Commit, if the size of multiple records is smaller than the size of a block, it only happens onceIOPS, and usually a record that reaches a block size is relatively small. I do not know whether this understanding, please Xu general advice.

3, old Mr. White reply

Old White 138xxxxxxx0 (62xx5) 9:44:24 The first analysis of the BULK insert IO and the few pieces that were produced, Then how big is the assessment?

Younger brother thought, that is, the old Mr. White so reply, affirmation that my answer is not complete, good, look, continue down, see I come in depth.

4, younger brother Li Junjie (Network Name: casing) then answer

Li Junjie 134xxxxxxx1 (389338xx3)  2015/1/5 9:51:34 in case of bulk INSERT, the main should be LGWR DBWR two parts produce io

5, old Mr. White again reply

Old White 138xxxxxxx0 (62xx5) 2015/1/5 9:56:20 If the problem is to be analyzed more finely, consider the impact of the index

old White This word a, little brother I immediately take a selfie head three, insert data, if the table has index, certainly will maintain index, on this question answer, I did not widen the thinking, insert Data Maintenance index will also cause IO , but simply around the subject " Insert " this specific event, regardless of the related events that the event will cause. It seems, in front of experts, have to admit that they are the gap with the experts ah.

Here 's to answer the old white bar honestly. Down ...

6, Middle episode

Hu June military 150xxxxxxx8 (275475005) 2015/1/5 9:57:21 General BULK Insert data volume is large

Hu June The Army is my brother, is a son Geumcheon optimization team of a strength general, it seems this buddy is busy snatch a bit AH.

7, not waiting for me to gasp, old Mr. White continued to guide

Old White 138xxxxxxx0 (62xx5) 2015/1/5 9:57:35 IO generates a few chunks, a pre-read of formatted data blocks, a single block read generated by the index modification, aDBWR Write,LGWR Write Log

8, brother's heart reply

Li Junjie 134xxxxxxx1 (389338xx3) 2015/1/5 9:57:42 When I think about it, I really didn't think of the index.

I had to say that I did not think of the index, in this long knowledge. But fortunately, the answer to the question is half, indicating that the production of IO mainly because DBWR and LGWR

9and see the experts ' high-level words

Old White 138xxxxxxx0 (62xx5) 2015/1/5 9:58:00 There is also the possibility that the order generated by the Index large batch modification may result in temporary table space reading and writing DIRECT PATH

See this paragraph of guidance, have to say, "Experts are experts", the comprehensive and in-depth analysis of the problem, let the younger brother from the inner admiration!

Little brother today is really a long knowledge, and experts to communicate the feeling is really good, and really hope that with the experts to communicate more.

This article Li Junjie (Network Name: casing), engaged in " system architecture, operating systems, storage devices, databases, middleware, applications " six levels of systematic performance optimization work

Welcome to the System performance Optimization Professional group, to discuss performance optimization technology together. Group number:258187244

Discussion on the effect of single commit and multiple commits on the IOPS of system