[已解决] 导入数据太慢了，有哪些常用的修改项

223 1 2024-3-8 17:34

本帖最后由 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[20380]:cur[369], unzip sum[0]:cur[0]
--------------
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)

只看楼主

yejr 2024-3-8 18:07:54

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，加大并发数