独家揭秘丨GreatSQL 没开Binlog时多线程插入数据性能劣化之谜
独家揭秘丨GreatSQL 没开Binlog时多线程插入数据性能劣化之谜一、问题发现
在一次数据迁移中，用到了INSERT INTO t1 SELECT * FROM t2这样的 SQL 用来搬迁大表，为了提高插入效率关闭了Binlog，考虑用多线程来插入提高速度。表的类型信息和插入效率如下所示。
测试环境：
- Linux node-76-11 4.19.90-17.ky10.aarch64，128核CPU，512G内存。
GreatSQL参数配置如下（为降低 I/O 因素影响，关闭 Binlog）：
#**********************Performance*********************
#******connect
max_connections=10000
max_connect_errors=1000000
open_files_limit=65535
back_log=1500
table_definition_cache=10000
thread_stack=256K
thread_cache_size=3000
#******session
sort_buffer_size=4M
join_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=4M
bulk_insert_buffer_size=64M
tmp_table_size=64M
max_heap_table_size=64M
net_buffer_length=16K
max_allowed_packet=1G
#******timeout
lock_wait_timeout=600
connect_timeout=10
interactive_timeout=31536000
wait_timeout=31536000
net_read_timeout=86400
net_write_timeout=86400
net_retry_count=10
#**********************InnoDB**************************
transaction_isolation=READ-COMMITTED
innodb_buffer_pool_size=200G
innodb_buffer_pool_instances=16
innodb_max_dirty_pages_pct=90
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=1G
innodb_page_cleaners=8
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON
innodb_buffer_pool_dump_pct=100
innodb_checksum_algorithm=NONE
innodb_log_checksums=NO
innodb_undo_log_truncate=OFF
innodb_change_buffering = none
innodb_spin_wait_delay=6
innodb_spin_wait_pause_multiplier=50
innodb_sync_spin_loops=30
#******feature
innodb_open_files=65535
innodb_flush_method=O_DIRECT
innodb_flush_neighbors=0
innodb_flush_sync=ON
innodb_io_capacity=20000
innodb_io_capacity_max=40000
innodb_lru_scan_depth=9000
innodb_lock_wait_timeout=30
innodb_print_all_deadlocks=ON
innodb_online_alter_log_max_size=4G
innodb_thread_concurrency=0
innodb_read_io_threads=32
innodb_write_io_threads=32
innodb_doublewrite=ON
innodb_doublewrite_pages=64
innodb_adaptive_hash_index=OFF
innodb_status_file=OFF
