두 컴퓨터 사이에서 MySQL 5.5 복제 성능에 심각한 문제가 있습니다. 주로 명령문 기반 복제가있는 myISAM 테이블입니다. 이진 로그와 mysql 데이터 디렉토리는 모두 동일한 Fusion ioDrive에 있습니다.
이 문제는 최근에 복제를 일시 중지해야 할 때 큰 문제였습니다. 3 시간. 다른 하중없이 다시 잡는 데 약 10 시간이 걸렸습니다.
복제 성능을 어떻게 향상시킬 수 있습니까? 머신 B는 기본적으로 유휴 상태입니다 (작은 IO, 16 개 코어 중 최대 2 개, 많은 여유 RAM). 단 하나의 mySQL 스레드 만 데이터를 쓰고있었습니다. 내가 가진 아이디어는 다음과 같습니다.
- 행 기반 복제로 전환하십시오. 테스트에서 이것은 10-20 %의 성능 향상만을 가져 왔습니다
- 다중 스레드 복제를 사용하여 mySQL 5.6으로 업그레이드하십시오. 우리는 데이터를 별도의 데이터베이스로 쉽게 분리 할 수 있었고 벤치 마크에서 이것이 도움이 될 것으로 보이지만 코드는 생산 준비가되지 않은 것으로 보입니다.
- 복제 속도를 높이는 일부 구성 변수
가장 큰 문제는 3 시간 동안 일시 중지 한 후 따라 잡는 데 10 시간이 걸리면 복제가 10 시간 내에 13 시간의 데이터를 쓰거나 들어오는 데이터 속도의 130 %에서 쓸 수 있다는 것입니다. 머지 않아 마스터 시스템에 최소 두 번의 쓰기 작업이 필요하므로 복제 성능을 향상시킬 수있는 방법이 절실히 필요합니다.
기계 A :
- 석사
- 24GB 램
- 1.2TB Fusion ioDrive2
- 2x E5620
- 기가비트 상호 연결
my.cnf:
[mysqld]
server-id=71
datadir=/data_fio/mysqldata
socket=/var/lib/mysql/mysql.sock
tmpdir=/data_fio/mysqltmp
log-error = /data/logs/mysql/error.log
log-slow-queries = /data/logs/mysql/stats03-slowquery.log
long_query_time = 2
port=3306
log-bin=/data_fio/mysqlbinlog/mysql-bin.log
binlog-format=STATEMENT
replicate-ignore-db=mysql
log-slave-updates = true
# Performance Tuning
max_allowed_packet=16M
max_connections=500
table_open_cache = 2048
max_connect_errors=1000
open-files-limit=5000
# mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections
key_buffer=4G
max_heap_table_size = 1G
tmp_table_size = 4G
myisam_sort_buffer_size = 256M
sort_buffer_size=4M
read_buffer_size=2M
query_cache_size=16M
query_cache_type=2
thread_concurrency=32
user=mysql
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
socket=/var/lib/mysql/mysql.sock
[client]
socket=/var/lib/mysql/mysql.sock
기계 B :
- 노예
- 36GB 램
- 1.2TB Fusion ioDrive2
- 2x E5620
- 기가비트 상호 연결
my.cnf:
[mysqld]
server-id=72
datadir=/data_fio/mysqldata
socket=/var/lib/mysql/mysql.sock
tmpdir=/data_fio/mysqltmp
log-error = /data/logs/mysql/error.log
log-slow-queries = /data/logs/mysql/stats03-slowquery.log
long_query_time = 2
port=3306
# Performance Tuning
max_allowed_packet=16M
max_connections=500
table_open_cache = 2048
max_connect_errors=1000
open-files-limit=5000
# mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections
key_buffer=4G
max_heap_table_size = 1G
tmp_table_size = 4G
myisam_sort_buffer_size = 256M
sort_buffer_size=4M
read_buffer_size=2M
query_cache_size=16M
query_cache_type=2
thread_concurrency=32
user=mysql
symbolic-links=0
plugin-load=archive=ha_archive.so;blackhole=ha_blackhole.so
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
socket=/var/lib/mysql/mysql.sock
[client]
socket=/var/lib/mysql/mysql.sock
답변
와우, 당신은이 문제에 대해 끔찍한 하드웨어를 가지고 있습니다. Btree 검색 등에서 20-50 % 더 나은 성능을 위해 Sandy / Ivy Bridge CPU로 업그레이드하는 것을 제외하고는 하드웨어 적으로 현명하게 던질 수는 없습니다.
제 장점은 Innodb입니다.
- 당신이 신비주의임을 무시하고 마치 차이를 만들지 않는 것처럼 행동하십시오.
- 이 문제가 업그레이드를하기에 충분한 자극이라고 가정하십시오. 예, 업그레이드입니다.
Innodb는 자주 액세스하는 행을 버퍼 풀에 저장하여 모든 메모리를 최대한 활용할 수 있습니다. 원하는만큼 크게 (예 : 메모리의 80 %) 조정할 수 있으며 최신 읽기 / 쓰기는 최신 액세스 데이터를위한 더 많은 공간을 확보하기 위해 디스크로 푸시해야 할 때까지 메모리에 남아 있습니다. 메모리는 FusionIO보다 훨씬 빠릅니다.
환경에 도움이 될 수있는 적응 형 해시, 자동 암호화 잠금 메커니즘 등과 같은 더 많은 Innodb 기능이 있습니다. 그러나 당신은 나보다 당신의 데이터를 더 잘 알고 있습니다.
innodb 세계에서 좋은 단기 솔루션은 슬레이브를 최적화하는 것입니다. 실제로 마스터에있는 슬레이브의 모든 인덱스가 필요합니까? 인덱스는 삽입 / 업데이트 / 삭제에 공 및 체인입니다 EVEN 퓨전 IO 카드와 함께. IOPS가 여기에있는 것은 아닙니다. Sandy / Ivy bridge procs는 훨씬 더 나은 메모리 처리량과 컴퓨팅 성능을 가지고 있으며, 현재 보유하고있는 Westmere를 크게 변화시킬 수 있습니다. (그림 20-50 % 전체). 슬레이브에 필요없는 인덱스를 모두 제거하십시오 !
둘째, 거의 확실히 innodb에만 적용되는 것은 mk-prefetch가 어떤 업데이트를 알 수 있고 슬레이브가 쓰기 전에 알 수 있다는 것입니다. 이를 통해 mk-prefetch는 먼저 읽기 쿼리를 실행할 수 있으므로 단일 repl이 쓰기 쿼리를 실행할 때까지 데이터가 메모리에있게됩니다. 이는 데이터가 메모리에 있고 fusionIO가 아니라 빠른 속도의 성능 향상을 의미합니다. 이것은 예상보다 큰 차이를 만듭니다 . 많은 회사에서이 솔루션을 영구적 인 솔루션으로 사용합니다. Percona Toolkit 을 확인하여 자세한 내용을 알아보십시오 .
셋째, 가장 중요한 것은 Innodb로 업그레이드 한 후에는 Tokutek을 확인하십시오. 이 사람들은 오랫동안 Innodb의 쓰기 / 업데이트 / 삭제 성능을 능가하는 사악한 멋진 것들을 가지고 있습니다. 주요 이점 중 하나로서 복제 속도를 향상 시켰 으며 Btrees의 경우 Fusions crazy IOPS가 여전히 도움이되지 않는 이유를 벤치 마크에서 확인할 수 있습니다 . (참고 : 나에 의해 독립적으로 확인되지는 않습니다.) 이들은 btree 인덱스의 드롭 인 대체를 사용하지만, 훨씬 더 복잡하지만 btree 인덱스의 알고리즘 속도 제한을 많이 개선합니다.
Tokutek의 채택을 고려하고 있습니다. 쓰기 속도가 너무 빠르면 인덱스를 더 추가 할 수 있습니다. 그것들은 데이터와 인덱스를 훌륭한 비율 (견적의 25 배)로 압축하기 때문에, 데이터 증가에 대해 (성능, 유지 보수) 가격을 지불하지 않아도됩니다. 하지만 사전 압축 된 GB (IIRC) 당 연간 $ 2500의 엔진 비용 ($)을 지불합니다. 데이터를 복제하면 할인이 가능하지만 슬레이브에 Tokutek을 설치하고 마스터를 그대로 유지할 수도 있습니다. MIT Algoritms Open Courseware 강의 에서 기술적 인 세부 사항을 확인하십시오 . 또는, 비디오를 볼 1:20이없는 사람들을 위해 블로그에 기술 자료가 많이 있습니다. 이 비디오는 또한 읽기 속도에 대한 Big-O 공식을 제공한다고 생각합니다. 나는 이읽기 속도가 느리다고 가정하기 위해 (항상 트레이드 오프가 있습니다!)하지만 수식이 너무 복잡하여 측정하는 것이 어렵습니다. 그들은 거의 동일하다고 주장하지만 수학을 이해하고 싶습니다 (아마도 아닙니다!). 당신은 나보다 이것을 발견하기에 더 좋은 상황에있을 수 있습니다.
추신 : 나는 Tokutek과 제휴하지 않았으며 제품을 실행 한 적이 없으며 심지어 내가보고 있다는 것을 모릅니다.
업데이트 :
이 페이지에서 다른 궁금한 점이있는 것으로 확인되었습니다.
첫째, 예외적 인 환경이 없다면 슬레이브 프리 페치는 거의 확실하게 myisam에서 작동하지 않습니다. 이는 프리 페치가 작성하려는 테이블을 잠 그거나 슬레이브 스레드가 프리 페치 디먼에 필요한 테이블을 잠그기 때문입니다. 테이블이 복제에 대해 균형이 잘 잡혀 있고 다른 테이블이 라운드 로빈 방식으로 기록되는 경우에는 효과가있을 수 있지만 이는 매우 이론적입니다. “고성능 MySQL”책에는 “복제 문제”섹션에 자세한 정보가 있습니다.
둘째, 아마도 슬레이브에 1.0-1.5의 부하가 있으며 아마도 다른 procs 또는 쿼리가 실행 중이지만 기준이 1.0 인 경우 더 높을 수 있습니다. 이는 CPU에 바인딩되어 있고 FusionIO가 탑재되어있을 가능성이 있음을 의미합니다. 앞서 언급했듯이 Sandy / Ivy Bridge는 조금 더 많은 움푹 파임을 줄 것이지만, 최소한의 시차로 거친 시간을 보내기에는 충분하지 않을 것입니다. 이 슬레이브의로드가 대부분 쓰기 전용 인 경우 (즉, 읽기가 많지 않은 경우) CPU는 거의 확실하게 btree 삽입 / 삭제 위치를 계산하는 데 시간을 소비합니다. 이것은 중요하지 않은 인덱스를 제거하는 것에 대한 위의 요점을 강화해야합니다. 나중에 언제든지 다시 추가 할 수 있습니다. 하이퍼 스레딩을 비활성화하면 작동하지 않으며 더 많은 CPU가 적이 아닙니다. 32GB RAM을 초과하면 (64GB) 걱정할 필요가 있습니다. 램 배포에그러나 증상이 다른 경우에도 마찬가지입니다.
마지막으로 가장 중요한 것은 (이 부분을 건너 뛰지 마십시오.)) 전환 할 때 사소한 성능 향상을 언급했기 때문에 RBR (행 기반 복제)을 실행하고 있다고 가정합니다. 그러나 여기서 더 많은 성능 을 얻을 수있는 방법이있을 수 있습니다 . 기본 키없이 복제되는 테이블이있는 경우 MySQL 버그 53375 가 나타날 수 있습니다. 슬레이브는 기본적으로 기본 키 이외의 다른 것을 사용할 정도로 똑똑하지 않으므로 복제 스레드가 없으면 모든 업데이트에 대해 전체 테이블 스캔을 수행해야합니다.. 수정은 단순히 양성 대리 자동 증가 기본 키를 추가하는 것입니다. 테이블이 큰 경우 (예 : 수만 행 이상)에만이 작업을 수행합니다. 물론 이것은 테이블에 다른 인덱스를 두는 비용으로 인해 CPU에서 지불하는 가격을 가져옵니다. 그렇지 않다면 InnoDB가 배후에 하나를 추가하기 때문에 이에 대한 이론적 인 주장은 거의 없습니다. 팬텀 하나는, 그러나, 너무이 문제를 극복 할 수 53375. 텅스텐에 대한 유용한 방어하지,하지만 당신은 필요 당신이 바로 인코딩을 가지고 텅스텐을 사용하면 확인 할 수 있습니다. 마지막으로 플레이했을 때 비 UTFF8 문자열이 복제가 필요할 때 끔찍하게 죽었습니다. 그것은 내가 포기한 시간입니다.
답변
대답은 아니지만 텅스텐 레 플리 케이 터 와 상용 제품을 더 유연하게 고려할 수 있습니다 . 병목 현상 인 단일 코어에서 100 % CPU 사용량입니까?
답변
따라서 슬레이브에서 백업을 수행하고 있고 myiasm 테이블을 사용하는 경우 손상을 방지하기 위해 백업을 수행하기 위해 테이블을 잠그는 것입니다. 따라서 백업이 완료 될 때까지 복제를 수행 할 수 없습니다.