InnoDB 엔진에서 삽입 지연을 사용하고 삽입 명령문에 더 적은 연결을 사용하는 방법은 무엇입니까? / hr이고 99.89

많은 데이터베이스 쓰기, 약 ~ 70 % 삽입 및 30 % 읽기와 관련된 응용 프로그램을 개발 중입니다. 이 비율에는 한 번의 읽기와 한 번의 쓰기로 간주되는 업데이트도 포함됩니다. insert 문을 통해 여러 클라이언트가 아래 insert 문을 통해 데이터베이스에 데이터를 삽입합니다. 

$mysqli->prepare("INSERT INTO `track` (user, uniq_name, ad_name, ad_delay_time ) values (?, ?, ?, ?)");

문제는 내가 중 하나를 사용해야입니다 insert_delay 또는 사용 mysqli_multi_query의 삽입 문은 서버에 ~ 100 %의 CPU를 사용하기 때문에 메커니즘을. 데이터베이스에서 InnoDB 엔진을 사용하고 있으므로 삽입 지연이 불가능합니다. 서버에서의 삽입은 ~ 36k / hr이고 99.89 % 읽습니다. 또한 단일 쿼리에서 7 번 데이터를 검색 하는 select 문을 사용하고 있습니다 .이 쿼리는 서버에서 실행하는 데 150 초가 걸립니다. 이 작업에 어떤 종류의 기술이나 메커니즘을 사용할 수 있습니까? 서버 메모리가 2GB인데 메모리를 확장해야합니까?. 이 문제를 살펴보면 어떤 제안이라도 감사 할 것입니다.

테이블의 구조 :

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| user            | varchar(100) | NO   |     | NULL              |                |
| uniq_name       | varchar(200) | NO   |     | NULL              |                |
| ad_name         | varchar(200) | NO   |     | NULL              |                |
| ad_delay_time   | int(11)      | NO   |     | NULL              |                |
| track_time      | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

내 데이터베이스의 현재 상태는 41k 삽입 (쓰기)을 표시하며 데이터베이스에 대해 매우 느립니다.

데이터베이스 상태

답변

더 많은 쓰기와 읽기가 있기 때문에 다음을 권장하고 싶습니다.

InnoDB의 적절한 튜닝이 핵심 일 것입니다

버퍼 풀 ( innodb_buffer_pool_size 크기 조정 )

InnoDB는 INSERT DELAYED를 지원하지 않기 때문에 큰 InnoDB 버퍼 풀을 사용하는 것이 INSERT DELAYED에 가장 가깝습니다. 모든 DML (INSERT, UPDATE 및 DELETE)은 InnoDB 버퍼 풀에 캐시됩니다. 쓰기에 대한 트랜잭션 정보는 Redo 로그 (ib_logfile0, ib_logfile1)에 즉시 기록됩니다. 버퍼 풀에 게시 된 쓰기는 ibdata1 (이차 인덱스 삽입 버퍼, 이중 쓰기 버퍼)을 통해 메모리에서 디스크로 주기적으로 플러시됩니다. 버퍼 풀이 클수록 많은 양의 INSERT를 캐시 할 수 있습니다. RAM이 8GB 이상인 시스템에서는 RAM의 75-80 %를 innodb_buffer_pool_size로 사용하십시오. RAM이 매우 적은 시스템에서 25 % (OS를 수용하기 위해).

주의 사항 : innodb_doublewrite 를 0으로 설정 하면 쓰기 속도가 훨씬 빨라지지만 데이터 무결성이 위험해질 수 있습니다. InnoDB를 OS에 캐싱하지 않도록 innodb_flush_method 를 O_DIRECT로 설정하여 속도를 높일 수도 있습니다 .

재실행 로그 ( innodb_log_file_size 크기 조정 )

기본적으로 리두 로그의 이름은 ib_logfile0 및 ib_logfile1이며 각각 5MB입니다. 크기는 innodb_buffer_pool_size의 25 % 여야합니다. 재실행 로그가 이미 존재하면 my.cnf에 새 설정을 추가하고 mysql을 종료 한 후 삭제하고 mysql을 다시 시작하십시오 .

로그 버퍼 ( innodb_log_buffer_size로 크기 조정 )

로그 버퍼는 리두 로그로 플러시하기 전에 RAM의 변경 사항을 유지합니다. 기본값은 8M입니다. 로그 버퍼가 클수록 디스크 I / O가 줄어 듭니다. 매우 큰 트랜잭션에주의하십시오. 커밋은 밀리 초만큼 느려질 수 있습니다.

여러 CPU에 액세스

MySQL 5.5 및 MySQL 5.1 InnoDB 플러그인에는 InnoDB 스토리지 엔진이 여러 CPU에 액세스하도록 설정되어 있습니다. 설정해야 할 옵션은 다음과 같습니다.

  • innodb_thread_concurrency 는 InnoDB가 열린 상태로 유지할 수있는 동시 스레드 수의 상한을 설정합니다. 일반적으로 (2 X CPU 수) + 디스크 수로 설정하는 것이 좋습니다. 작년에 저는 Percona NYC Conference에서 InnoDB Storage Engine이 실행중인 환경에 가장 적합한 스레드 수를 찾도록 경고하기 위해 이것을 0으로 설정해야한다는 것을 직접 배웠습니다.
  • innodb_concurrency_tickets 는 동시성 검사를 무시할 수없는 스레드 수를 설정합니다. 이 한계에 도달하면 스레드 동시성 검사가 다시 표준이됩니다.
  • innodb_commit_concurrency 는 커밋 할 수있는 동시 트랜잭션 수를 설정합니다. 기본값은 0이므로 설정하지 않으면 여러 트랜잭션을 동시에 커밋 할 수 있습니다.
  • innodb_thread_sleep_delay 는 InnoDB 대기열에 다시 들어가기 전에 InnoDB 스레드가 휴면 될 수있는 시간 (밀리 초)을 설정합니다. 기본값은 10000 (10 초)입니다.
  • innodb_read_io_threads (3000으로 설정) 및 innodb_write_io_threads (7000으로 설정) (MySQL 5.1.38 이후)는 읽기 및 쓰기에 지정된 수의 스레드를 할당합니다. 기본값은 4이고 최대 값은 64입니다. 64로 설정하십시오. 또한 innodb_io_capacity 를 10000으로 설정하십시오 .

MySQL 5.5로 업그레이드

MySQL 5.0이있는 경우 MySQL 5.5로 업그레이드하십시오. MySQL 5.1.37 이전 버전이있는 경우 MySQL 5.5로 업그레이드하십시오. MySQL 5.1.38 이상이 설치되어 있고 MySQL 5.1을 유지하려면 InnoDB 플러그인을 설치하십시오. 이렇게하면 InnoDB의 모든 CPU를 활용할 수 있습니다.

답변

INT (2)는 여전히 4 바이트를 사용합니다. TINYINT UNSIGNED를 의미했을까요?

setno에는 몇 개의 다른 값이 있습니까? 작 으면 KEY (setno)는 사용되지 않습니다. INSERTing은 해당 인덱스를 업데이트해야합니다. 키를 제거하면 일부 삽입 속도가 빨라집니다.

CHAR (10)- flag항상 10 자 길이입니까? 그리고 utf8에서? 아마도 VARCHAR (10) CHARACTER SET ascii 플래그를 사용할 수 있습니다.

인서트 배치-한 번에 100 개가 10 배 빠르게 실행됩니다. (100을 넘어서서 ‘수익 감소’에 들어갔다.)

자동 커밋의 가치는 무엇입니까? BINSIN … COMMIT에 각 INSERT를 래핑하고 있습니까? innodb_flush_log_at_trx_commit의 값은 무엇입니까?

답변

대기열을 설정하십시오. 애플리케이션은 한 번에 1 행씩 큐에 쓴 다음 행을 가져 와서 마지막 삽입 이후 경과 된 시간에 대한 행 수를 기반으로 배치로 데이터베이스에 삽입합니다.

한 번에 10,000 개씩 인서트를 일괄 처리하는 것이 가장 빠르다는 것을 알았으므로 스위트 스팟을 찾기 위해 테스트해야합니다.

간단한 큐 시스템을 만들거나 기존 큐 시스템을 사용할 수 있습니다. 다음은 몇 가지 예입니다. HornetQ 및 File :: Queue . 다음은 다른 좋은 옵션을 나열하는 SE에 대한 게시물입니다 . perl, php, python의 메시지 큐 .

답변