전용 mariadb 서버에 적합한 구성 찾기 - 거대한 innodb 테이블에 적합
mariadb를 설정하기 위해 다른 서버에서 작업을 하고 있지만 여전히 쿼리는 반환에 시간이 오래 걸리고 대부분 반환되지 않습니다.
Mariadb 버전: 10.1.31-1 서버는 50GB의 RAM과 2200GHz*10코어 Centos7을 OP로 탑재
대상 데이터베이스에는 현재 매우 큰 innodb 테이블이 있으며 색인이 잘 되어 있지 않습니다.빈정대는 다른 테이블 타입은 없습니다.
현재 중첩된 선택 쿼리를 만들고 결과를 다른 빈 클론 데이터베이스에 저장하여 삽입하거나 대체하고 있습니다.
예를 들어 다음과 같습니다.
REPLACE INTO db1.table1
SELECT * FROM db0.table1 WHERE ART_ID IN (
SELECT ART_ID FROM db0.table2 WHERE BRA_ID IN (
SELECT BRA_ID FROM db0.table3 WHERE BRAND IN (
SELECT BRAND FROM db0.table4 WHERE ID IN (... bunch of comma seperated ids)
)
)
);
유일한 접속은 로컬 접속 및/또는1개의 리모트 접속뿐입니다.서버의 유일한 작업은 웹 서버 없이 대용량 데이터를 다른 DB로 필터링하는 것입니다.
아래에 /etc/my.cnf 파일을 둡니다.이 파일에 많은 테스트를 했기 때문에 불필요한 추가사항을 볼 수 있습니다.
문제는 cnf 파일에 설정 오류가 있는지 여부입니다.또, 이러한 일련의 쿼리를 적절한 시간에 실행하기 위한 설정 힌트 등이 있는지 여부입니다.
동작은 양호했지만, 설정을 변경하거나 서버를 업데이트한 후에는 모든 것이 훨씬 느려졌습니다.(예, db 버전을 업데이트한 후 테이블도 업데이트합니다.)
# The MySQL server
[mysqld]
performance_schema=0
bind-address = 0.0.0.0
max_connections = 32
socket = /var/lib/mysql/mysql.sock
skip-external-locking
max_allowed_packet = 1024M
connect_timeout=300
wait_timeout=1200
interactive_timeout=300
key_buffer_size = 2G
bulk_insert_buffer_size=256M
sort_buffer_size = 32M
#read_buffer_size = 8M
#read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 32M
join_buffer_size=32M
#max_heap_table_size= 8G
tmp_table_size=8G
concurrent_insert=2
#external-locking=FALSE
#open_files_limit=50000
default-storage-engine=innodb
innodb_file_per_table=1
#lower_case_table_names=1
# Try number of CPU's*2 for thread_concurrency
innodb_write_io_threads=10
innodb_read_io_threads=20
#innodb_buffer_pool_instances=8
#thread_stack = 1M
#thread_cache_size = -1
innodb_buffer_pool_size = 16G
#query_cache_limit = 1073741824
#query_cache_size = 1073741824
#query_cache_type = 1
query_cache_size=0
query_cache_type=0
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_fast_shutdown=0
#innodb_log_file_size = 1G
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 0
innodb_lock_wait_timeout = 1073741823
innodb_thread_concurrency=0
innodb_commit_concurrency=0
innodb_flush_method=O_DSYNC
innodb_log_compressed_pages = 0
#innodb_flush_neighbors = 0
#innodb_adaptive_hash_index_partitions = 8
log-bin=mysql-bin
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeout
퍼코나 마법사를 사용하여 조언을 병합했습니다.현재 my.cnf는 다음과 같습니다.
어제 저녁(2018-03-05 22:41:30 UTC)에 php 어플리케이션을 실행했지만 mysql은 결과를 반환하지 못했습니다.
애플리케이션은 한계 10000 및 오프셋만 추가하고 있습니다(처음부터 시작하고 싶지 않았습니다. 현재 마지막 오프셋을 추적하고 있고 그 시점부터 시작합니다).
현재 mariadb : (감정적인 반응이 될 것입니다)를 삭제하고 재설치할 예정입니다.
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
[mysql]
# CLIENT #
port = 9999
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
port = 9999
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
# MyISAM # - there is no myisam table
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 30
#thread-cache-size = 16
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 5G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 42G
innodb-buffer-pool-instances = 42
thread_cache_size=100
innodb_lru_scan_depth=100
innodb_purge_threads=4
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=0
max_seeks_for_key=32
max_write_lock_count=16
thread_concurrency=35
innodb_fast_shutdown=0
innodb_file_per_table=1
default-storage-engine=innodb
concurrent_insert=2
join_buffer_size=32M
tmp_table_size=8G
read_buffer_size = 8M
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
질문의 my.cnf 데이터가 SHOW GLOBAL VARIAS; 값과 일치하지 않습니다.
.
https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/httpsmariadb.com/kb/en/library//
정확한 일치값을 얻을 수 있도록 지원합니다.
다음으로 my.cnf [mysqld]섹션에서 고려해야 할 사항은 다음과 같습니다.
innodb_buffer_pool_size=8G # from > 40G (95% of RAM will not work well)
innodb_lru_scan_depth=100 # from 1024 see refman every second digging too deep
log_warnings=2 # from 1 to include connection errors more detail
max_connect_errors=10 # from 1,000,000 no need to waste a million cycles
thread_cache_size=100 # from 16 CAP per V8 MySQ to be prepared for volume
have_symlink=NO # to protect you server, unless you NEED it
innodb_purge_threads=4 # from 1 to accommodate when needed
innodb_read_io_threads=64 # from 4 see dba.stackexhange.com Q 5666 9/12/11 Rolando
innodb_write_io_threads=64 # from 4 per Rolando to use multi-core
innodb_thrad_concurrency=0 # another part of multi-core enabling
max_seeks_for_key=32 # from huge number, not found in 32, will not be found
max_write_lock_count=16 # give RD opportunity after nn locks
thread_concurrency=35 # to limit concurrent processing load
@Erce, 올바른 활성 my.cnf로 적용해주세요.당신의 mysqld 섹션을 다시 하겠다는 제 제안을 철회했습니다.
퍼코나 마법사를 사용하여 조언을 병합했습니다.현재 my.cnf는 다음과 같습니다.
어제 저녁(2018-03-05 22:41:30 UTC)에 php 어플리케이션을 실행했지만 mysql은 결과를 반환하지 못했습니다.
애플리케이션은 한계 10000 및 오프셋만 추가하고 있습니다(처음부터 시작하고 싶지 않았습니다. 현재 마지막 오프셋을 추적하고 있고 그 시점부터 시작합니다).
[mysql]
# CLIENT #
port = 9999
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
port = 9999
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
# MyISAM # - there is no myisam table
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 30
#thread-cache-size = 16
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 5G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 42G
innodb-buffer-pool-instances = 42
thread_cache_size=100
innodb_lru_scan_depth=100
innodb_purge_threads=4
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=0
max_seeks_for_key=32
max_write_lock_count=16
thread_concurrency=35
innodb_fast_shutdown=0
innodb_file_per_table=1
default-storage-engine=innodb
concurrent_insert=2
join_buffer_size=32M
tmp_table_size=8G
read_buffer_size = 8M
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
@erce
이들 3개의 라인을 삭제해야 합니다(Logging 영역 직전).
join_buffer_size=32M
tmp_table_size=8G
read_buffer_size = 8M
로깅
tmp_table_size는 이전에 max_size_table_size와 정확하게 일치했습니다.이 회선은 나중에 활성화되어 언밸런스 상태가 됩니다.이것은 BAD입니다.
join_buffer_size 및 read_buffer_size로 인해 RAM 사용 공간이 줄어듭니다.mysqlcalculator.com을 사용하여 이유를 확인하십시오.
성능 문제는 주로 쿼리 형식에서 발생하며 반드시 튜닝 또는 인덱스에서 발생하는 것은 아닙니다.
쿼리 공식화
됩니다.IN ( SELECT ... )JOIN.
마지막 단계:
SELECT bra_id
FROM t3
JOIN t4 ON t3.brand = t4.brand
WHERE t4.id IN (...commalist...)
인덱스 포함:
t4: INDEX(id, brand)
t3: INDEX(brand, bra_id)
이것은 t4부터 시작하여 다양한 ID를 검색하여 브랜드에 매핑합니다.그리고 브랜드별로 t3에 도달하여 bra_id를 얻습니다.
t2에서 t1까지를 통과하기 위해 다음 두 단계를 수행하여 연습하도록 하겠습니다.
튜닝 어드바이스
my.cnf 값을 무작정 늘리지 마십시오. 문제가 발생할 수 있습니다.
innodb_buffer_pool_size = 35G -- the most important
42G같은 서버에서 실행 중인 다른 앱이 너무 많지 않은 경우 문제가 없을 수 있습니다. 42는 동작할수 .가 최대.
나머지 튜너블은 아마 그대로 둘 수 있을 겁니다.
max_heap_table_size= 8G
tmp_table_size=8G
그것들은 위험할 정도로 높다.이 기능을 사용하면 연결당 한 번(또는 여러 번) 8GB를 할당할 수 있습니다.【램】50 GB 【램】RAM 의 1% 를 넘지 않게 설정하는 것이 좋습니다).500M32MM의
#lower_case_table_names=1
코멘트를 남깁니다.OS에 필요한 것을 덮어쓰려고 하면 큰 문제가 생기기 쉽습니다.
port = 9999
디폴트 3306의 문제점은 무엇입니까?
다른.
다음 블록의 8시간경과 6만 달러입니다.SHOW CREATE TABLE읽을 4개의 테이블과 쓸 테이블에 대한 추가 정보입니다.★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★대상 테이블에서 인덱스 업데이트를 빠르게 검토하고 있는 것 같습니다.간단한 해결책이 있습니다.
mysqld가 스왑 중인지 확인합니다.
선택 쿼리 스타일을 변경해야 할 것 같습니다.실행되지 않았기 때문에 제공된 쿼리 대안을 사용해 보겠습니다.쿼리는 아직 느립니다(내 추측).
그러나 이는 현재 서버 통계(RAM 49GB, 코어 10개)이며, mysqls 가상 메모리는 약 47GB로 정상이며, 다른 앱은 실행되지 않습니다.괜찮은 것 같아요?
tuning-primer.sh
https://pastebin.com/bcjS1PWf
mysqltuner.pl
https://pastebin.com/mXUTLPQ6
my.cnf
https://pastebin.com/9jMLzhZS
my.cnf-ini [mysqld]섹션에 대한 제안 (3/15/18에 게재된 데이터 사용)
thread_cahce_size=40 # from 20, 8 required for overhead, room to wiggle
innodb_io_capacity=400 # from 200 new iostat -x will confirm use
# read_buffer_size=8388608 # lead with # for default of 128K - extreme at 8M
read_rnd_buffer_size=128K # from 256K will affect handler_read_rnd_next
sync_binlog=32 # from 1 sec frequency, unless you fear recovery required
# join_buffer_size=4M # lead with # for default of 128K
key_buffer_size=1M # from 32M less than 1M used - primarily Innodb tables
innodb_adapative_max_sleep_delay=15 # from 150000 (15 secs - n0 other users)
innodb_buffer_pool_instances=8 # from 42 to minimize mgmt. overhead
innodb_buffer_pool_size=40G # from 44G allow OS a little breathing room
innodb_change_buffer_max_size=3 # from 25% - no need to set aside 10G when not changing anything significant
innodb_doublewrite=OFF # from ON you are not a PROD effort - crash recovery NA for run of this instance
innodb_print_all_deadlocks=ON # from OFF you always need in error log
innodb_read_ahead_threshold=8 # from 56, why wait to read NXT extent?
innodb_stats_sample_pages=32 # from 8 for more accurate index cardinality
log_warnings=2 # from 1 to record addl connection error details
max_join_size=1000000000 # from more huge number to 1B rows max for join
sql_select_limit=1000000000 # from more huge number to 1B rows selected
query_alloc_block_size=32K # from 16K - minimize RAM alloc AvgQrySz 20K
query_cache_type=0 # for OFF, not used
query_cache_size=0 # from 8M not needed for one time selections
query_cache_limit=1K # from 256K, just to keep the framework, in case QC needed
query_cache_min_res_unit=512 # from 2K for optimal QC storage when QC used
query_prealloc_size=32K # from 24K to minimize RAM alloc for parsing
transaction_alloc_block_size=32K # from 8K to minimize RAM alloc
transaction_prealloc_size=32K # from 4K to minimize RAM alloc
updatable_views_with_limit=NO # from YES to reduce handler_external_lock count
이것은 YES로 돌아가야 할 수 있습니다.이것은 DYNAMIC 변수입니다.
세션을 종료하고 API용 refman PREAPE 또는 mysql_stmt_prepare에 따라 리소스를 릴리스하기 전에 ONE com_stmt_prepare CODE를 찾아 CLOSE가 설정되어 있는지 확인하십시오.
실행 중 문제가 있는 쿼리를 실행하기 전에 ALTER TABLE tbl_name ENGINE=INNODB를 각 테이블에 사용하여 추가 페이지를 활용하여 카디널리티를 향상시킨 후 장기 실행 쿼리를 사용해 보십시오.
언급URL : https://stackoverflow.com/questions/49094911/finding-right-configuration-for-a-dedicated-mariadb-server-for-huge-innodb-tab
'programing' 카테고리의 다른 글
| 코드 변경 후 magento 셋업 업그레이드를 실행할 때 Mysql 서버가 사라짐 (0) | 2022.09.22 |
|---|---|
| strict type은 PHP에서 무엇을 합니까? (0) | 2022.09.22 |
| 범위별 그룹화 및 임시 없는 상수 기준 (0) | 2022.09.22 |
| 양방향 암호화:검색할 수 있는 암호를 저장해야 합니다. (0) | 2022.09.22 |
| 특정 table.column을 참조하는 외부 키가 있고 외부 키에 대한 값이 있는 모든 테이블을 찾으려면 어떻게 해야 합니까? (0) | 2022.09.22 |