导入数据太慢了，有哪些常用的修改项
本帖最后由 chestnutsj 于 2024-3-11 10:56 编辑tpch的测试数据
1.不能关闭binlog
2. 配置用了默认配置，只修改了
bulk_insert_buffer_size = 80M
innodb_buffer_pool_size = 5G
binlog_cache_size=4M
导入用了
/*+ SET_VAR(gdb_parallel_load=ON) SET_VAR(gdb_parallel_load_workers=8)*/
效率明显有问题，还有什么参数可以比较快的插入数据
Pending writes: LRU 0, flush list 5, single page 0
Pages made young 118201, not young 155199
0.65 youngs/s, 0.00 non-youngs/s
Pages read 77978, created 65630, written 1379369
0.00 reads/s, 11.64 creates/s, 424.71 writes/s
Buffer pool hit rate 1000 / 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: 52486, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
3 RW transactions active inside InnoDB
Process ID=3072631, Main thread ID=140336336709184 , state=sleeping
Number of rows inserted 5229620, updated 0, deleted 150000, read 150000
1114.42 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 173, updated 338, deleted 158, read 5894
0.29 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
机器 cpu 8,内存 32 G
1. 关闭binlog，若不能关闭binlog，则修改 sync_binlog = 0
2. 修改 innodb_flush_log_at_trx_commit = 0
3. 加大 innodb_buffer_pool_size 至约24G（最大可用物理内存的80%）
4. 修改HINT gdb_parallel_load_workers=16，加大并发数
页:
[1]