MySQL의 디스크 읽기 속도 저하 INSERT

MySQL의 디스크 읽기 속도 저하 INSERT

InnoDB Table의 MariaDB(10.0.31)에서 대용량 INSERT 쿼리의 속도를 최적화하려고 합니다.

표(1억3100만 행)의 구조는 다음과 같습니다.

Field__     Type___     Null    Key     Default     Extra   
ID_num_     bigint(45)  NO      PRI     NULL    
Content     varchar(250)YES             NULL    
User_ID     bigint(24)  NO      MUL     NULL    
Location    varchar(70) YES             NULL    
Date_creat  datetime    NO      MUL     NULL    
Retweet_ct  int(7)      NO              NULL    
isRetweet   tinyint(1)  NO              NULL    
hasReetwet  tinyint(1)  NO              NULL    
Original    bigint(45)  YES             NULL    
Url____     varchar(150)YES             NULL    
Favorite_c  int(7)      NO              NULL    
Selected    int(11)     NO              0   
Sentiment   int(11)     NO              0   

다,하다,하다,하다의 입니다.CREATE TABLE:

CREATE TABLE `Twit` (
 `ID_num` bigint(45) NOT NULL,
 `Content` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `User_ID` bigint(24) NOT NULL,
 `Location` varchar(70) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `Date_create` datetime NOT NULL,
 `Retweet_count` int(7) NOT NULL,
 `isRetweet` tinyint(1) NOT NULL,
 `hasReetweet` tinyint(1) NOT NULL,
 `Original` bigint(45) DEFAULT NULL,
 `Url` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `Favorite_count` int(7) NOT NULL,
 `Selected` int(11) NOT NULL DEFAULT '0',
 `Sentiment` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID_num`),
 KEY `User_ID` (`User_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

인덱스의 구조는 다음과 같습니다.

Table   Non_unique  Key_name    Seq_in_index    Column_name     Collation   Cardinality     Sub_part Packed     Null    Index_type  Comment     Index_comment   
Twit    0           PRIMARY     1               ID_num          A           124139401       NULL     NULL       BTREE       
Twit    1           User_ID     1               User_ID         A           535083          NULL     NULL       BTREE       

.show engine innodb status:

BUFFER POOL AND MEMORY
----------------------
Total memory allocated 8942256128; in additional pool allocated 0
Total memory allocated by read views 184
Internal hash tables (constant factor + variable factor)
   Adaptive hash index 141954688 (141606424 + 348264)
   Page hash           4426024 (buffer pool 0 only)
   Dictionary cache    35656039 (35403184 + 252855)
   File system         845872 (812272 + 33600)
   Lock system         21251648 (21250568 + 1080)
   Recovery system     0 (0 + 0)
Dictionary memory allocated 252855
Buffer pool size        524286
Buffer pool size, bytes 8589901824
Free buffers            448720
Database pages          75545
Old database pages      27926
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 74639, created 906, written 39133
0.12 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 75545, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

다음 Python 코드를 사용하여 서드파티 소스에서 데이터를 다운로드한 후 테이블을 채웁니다.

add_twit = (" INSERT INTO Table (ID_num, Content,....) VALUES (%s, %s, ....)")
testtime=0
t0 = time.time()
data_twit = []

#### Data Retrieving  ####
for page in limit_handled(...):
    for status in page:
        data_twit.append(processed_tweet)
####


##### MySQL Insert 
tt0 = time.time()
cursorSQL.executemany(add_twit, data_twit)
testtime += time.time() - tt0
####

cnx.commit()
print('Total_TIME ' + str(time.time()-t0))
print('Sqlexecute_TIME ' + str(testtime))

코드의 기능:

서드파티 프로바이더로부터 twits를 취득하고, 그 중 16페이지가 페이지당 200twits(상태)이므로, 1회 반복(사용자)마다 합계 3200 행을 테이블에 추가할 수 있습니다.했습니다.cursorSQL.execute(add_twit, data_twit) 작성하는 입니다.cursorSQL.executemany★★★★★★ 。

3200개의 트윗은 다운로드에 10초, 데이터베이스에 쓰는 데 75초 정도 걸리는데, 현재 1개의 트윗(행)이 0.2ko인 것을 감안하면 3200개의 트윗은 640Ko에 불과하다.75초면...

할 때 하는 현상iotop:

• "CHANGE" (CHANGE: "CHANGE: (CHANGE: (CHANGE:
  • 판독치 = 0.00 B/s
  • 쓰기= 6.50 M/s

디스크는 실제로 대용량 삽입 후 6Mbs/s 속도로 몇 분 동안 쓰기를 계속합니다.

• 코드의 SQL 삽입 부분 중:

  • 판독치 = 1.5 M/s
  • 쓰기= 300K/s

  디스크 판독치(인덱스용인가?)로 인해 쓰기 속도가 저하되는 것 같습니다.

내가 시도한 것:

• 삽입 쿼리를 분할합니다(1*3200 행과 3200*1 행이 아닌, 16*200 행과 3200*1 행을 시도했지만 아무것도 변경되지 않았습니다. 1*3200이 약간 빠릅니다).

• 테이블 최적화 (15%의 속도 확보)

• 불필요한 인덱스 삭제

질문 사항:

• 쓰기 대신 INSERT 쿼리를 전송하면 디스크 판독이 시작되는 이유는 무엇입니까?그것을 막을 방법이 있나요?

• 모든 INDEX를 제거하면 INSERT 속도를 높일 수 있습니까?

• 잘못된 생각처럼 들리는데 기본 키(컬럼이 아니라 고유한 인덱스만)를 삭제해야 합니까? (MySQL은 삽입 후 속도가 느려집니다.)는 삭제하지 않는 것이 좋습니다.

• 다른 조언은 없나요?
• 또, 큰 INSERT가 끝난 후에도, 디스크는 6.00 Mbps의 속도로 계속 기입되는 이유는 무엇입니까?

• 테이블에 60GB 정도?
• 약 ? (ID의 (「Index_length」 참조)SHOW TABLE STATUS LIKE 'Twit
• ★★INSERT【3200】★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★」만약 이것이 틀렸다면, 이것이 가장 큰 문제입니다.
• ID_num을 사용하지 .AUTO_INCREMENT
• ID_num은 단조롭게 증가하고 있습니까?(또는 적어도 약)만약 이것이 틀렸다면, 그것은 가장 큰 문제이다.
• User_ID는 매우 랜덤합니다.

분석 및 결론:

• 데이터가 "추가"되고 있지만 캐시(buffer_pool, 8GB)에는 큰 영향이 없습니다.
• User_ID이치노이것에 의해, 대부분의 인덱스가 캐시에 보관 유지되거나 유출될 가능성이 있습니다.이제 막 유출을 시작한 경우 성능이 저하되고 캐시 누락이 증가할수록 성능이 점점 더 나빠집니다.
• "I/O는 쓰기 후에도 계속됩니다." -- 이것은 정상입니다."InnoDB Change" 입니다.: " " "INDEX(User_ID)지연되지만 결국 발생해야 합니다.

부분 솔루션:

• RAM 증설
• innodb_buffer_pool_sizeRAM 70 70% 。
• 40달러? ★★User_ID로로 합니다.INT UNSIGNED 바이트에서 (4 바이트)BIGINT(8시)25%로 하다
• DROP INDEX(User_ID)★★★★★★★★★★★★★★★★★?
• 사용하십니까?ID_num?? 해 주세요.만약 없다면, 그것의 존재를 설명해 주세요.
• 경경에서 NULL로로 합니다.NOT NULL 청소입니다.(속도 향상에 도움이 되지는 않지만 청소가 됩니다.)
• AUTO_INCREMENT(동료)

벤치마크:

• I/O 메트릭을 사용하지 않습니다. InnoDB와 Change 버퍼의 '차단'에 의해 혼동됩니다.
• "안정 상태"를 기다립니다.즉, 작은 테이블, 콜드 머신, 버스트 등을 피하십시오.각 3200이 걸린 시간의 그래프는 이러한 이유로 인해 기복이 있을 것입니다.그러나 결국 그것은 "안정된 상태"에 도달할 것이다.그러나 보조 인덱스를 분석한 결과, 3200 행이 32초 걸릴 정도로 감소할 수 있습니다(스피닝 디스크를 사용하는 경우).
• 75초 만에 3200은 의미가 없습니다.생성된 SQL을 꼭 봐야 할 것 같아요.

인덱스가 있는 경우 디스크 읽기를 통해 인덱스를 검색할 수 있습니다.디스크에서 적절한 위치를 찾기 위해 삽입할 때는 항상 판독이 이루어집니다.

인덱스를 삭제하면 나중에 읽기 작업을 수행하지 않아도 삽입 속도가 빨라집니다.

기본 인덱스를 제거할지 여부는 사용 사례에 따라 크게 달라지며, 데이터 원본이 전체 복제를 수행하지 않을 것으로 신뢰할 수 있는 정도에 따라 달라집니다.그러나 프라이머리 키를 사용하여 데이터베이스를 읽어야 하는 경우 나중에 성능이 크게 향상됩니다.다만, 이것에 의해 기입 조작이 고속이 됩니다.

부하를 분산할 수 있는 샤딩 등 RDBMS의 다른 셋업을 검토할 수 있습니다.하드웨어 확장이나 최소한 병렬 처리 없이 해결할 수 있는 문제는 매우 많으며, 이는 사용 사례에 적합하지 않을 수 있습니다.

언급URL : https://stackoverflow.com/questions/45754613/disk-read-slows-down-insert-in-mysql