Innodb:经过48小时的优化，10 of /秒的写入速度

Question

我在一个具有NVME存储器(2TB)和64 GB内存的8核心服务器上运行这个程序。磁盘速度极快，1.1GB/秒seq和70-100k IOPS全双工。

因为MySQL5.7的性能非常糟糕，所以我在一个苗条的码头容器上安装了MariaDB10.3.8。

总之，我要写的表有20亿TB和10亿行大小。但是让我明确一点:这种速度性能发生在前几千行的空磁盘上，它与一个大表无关。

在过去的一周，日日夜夜，I投入了大约50个小时的工作时间，我阅读了我能找到的所有文档页面，以及各种平台上的数百个指南和问题。我把它全部测试出来了，几乎所有的组合你都能想到。我尝试了纯内存缓冲，纯磁盘缓冲，有和没有大型日志，日志缓冲区，各种刷新方法，没有刷新，所有这些设置你可以忽略。

I测试了如何使用 mydumper、mysql控制台、mysqlimport、load data infile、PHP插入、我编写的多线程PDO脚本进行导入。

I测试表有索引和没有索引，只有主索引。

I尝试使用和不使用事务导入，尝试单行插入和多行插入。

我尝试了不同的表类型，通常是20-30列，主要包含varchars和几次日期。

单线程的性能为3-5k行/秒，多线程(荒谬的.)为10-25k/秒。CPU和磁盘大部分时间都处于空闲状态，iostat显示3-20MB/秒的写入性能，通常约为7mb-12mb。取决于我尝试的设置。

因此，大约比它应该执行的慢100倍，没有什么明显的阻碍它。这是当前的配置：

innodb_buffer_pool_size = 14G
innodb_buffer_pool_chunk_size=1G
innodb_log_buffer_size  = 32M
innodb_file_per_table   = 1
innodb_open_files       = 600
#innodb_flush_method    = O_DIRECT
innodb_flush_method     = O_DSYNC 
innodb_log_file_size    = 512M
innodb_io_capacity=800 
innodb_io_capacity_max=3000 
innodb_flush_neighbors=0
innodb_write_io_threads=8 
innodb_read_io_threads=8 
innodb_change_buffer_max_size=70
innodb_doublewrite=0 # corruption risk

想想看几乎每一个组合都是可能的，我都试过了。

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
xvda              0.50         0.00        19.00          0         76
xvdg            102.50     13120.00         0.00      52480          0
xvdh           1381.50     19708.00     30984.00      78832     123936
xvdf              0.00         0.00         0.00          0          0
nvme0n1         222.00         0.00     10957.00          0      43828

这里唯一相关的磁盘是nvme0n1，您可以看到使用多线程插入的当前写入性能。

| InnoDB |      | 
=====================================
2018-07-22 06:42:31 0x7fe7341c9700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4883 srv_active, 0 srv_shutdown, 1061 srv_idle
srv_master_thread log flush and writes: 5944
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2215493
OS WAIT ARRAY INFO: signal count 3968391
RW-shared spins 0, rounds 6388873, OS waits 1674234
RW-excl spins 0, rounds 34932124, OS waits 431565
RW-sx spins 13782, rounds 169207, OS waits 2879
Spin rounds per wait: 6388873.00 RW-shared, 34932124.00 RW-excl, 12.28 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1036891
Purge done for trx's n:o < 1036891 undo n:o < 0 state: running but idle
History list length 53
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422106005755512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422106005754696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
263597 OS file reads, 2045302 OS file writes, 161983 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 282.56 writes/s, 28.08 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1134, seg size 1136, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4425293, node heap has 1 buffer(s)
Hash table size 4425293, node heap has 4113 buffer(s)
Hash table size 4425293, node heap has 1 buffer(s)
Hash table size 4425293, node heap has 41738 buffer(s)
Hash table size 4425293, node heap has 1 buffer(s)
Hash table size 4425293, node heap has 9223 buffer(s)
Hash table size 4425293, node heap has 1 buffer(s)
Hash table size 4425293, node heap has 5900 buffer(s)
7968.05 hash searches/s, 13692.47 non-hash searches/s
---
LOG
---
Log sequence number 185044128669
Log flushed up to   185044077028
Pages flushed up to 185040571130
Last checkpoint at  185017989085
0 pending log flushes, 0 pending chkp writes
37148 log i/o's done, 6.79 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 17649631232
Dictionary memory allocated 419728
Buffer pool size   1048576
Free buffers       8032
Database pages     979545
Old database pages 361426
Modified db pages  275
Percent of dirty pages(LRU & free pages): 0.028
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 817, not young 38547
0.00 youngs/s, 0.00 non-youngs/s
Pages read 263539, created 1493647, written 2008209
0.00 reads/s, 204.33 creates/s, 275.76 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: 979545, unzip_LRU len: 0
I/O sum[116336]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   131072
Free buffers       1025
Database pages     122400
Old database pages 45162
Modified db pages  19
Percent of dirty pages(LRU & free pages): 0.015
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 70, not young 6961
0.00 youngs/s, 0.00 non-youngs/s
Pages read 33027, created 186873, written 272369
0.00 reads/s, 19.72 creates/s, 32.72 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: 122400, unzip_LRU len: 0
I/O sum[14542]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   131072
Free buffers       1007
Database pages     122444
Old database pages 45179
Modified db pages  26
Percent of dirty pages(LRU & free pages): 0.021
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 74, not young 9194
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32952, created 187093, written 242787
0.00 reads/s, 22.49 creates/s, 28.87 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: 122444, unzip_LRU len: 0
I/O sum[14542]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   131072
Free buffers       1023
Database pages     122421
Old database pages 45170
Modified db pages  14
Percent of dirty pages(LRU & free pages): 0.011
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 45, not young 177
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32936, created 186494, written 236773
0.00 reads/s, 23.97 creates/s, 30.69 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: 122421, unzip_LRU len: 0
I/O sum[14542]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   131072
Free buffers       994
Database pages     122454
Old database pages 45182
Modified db pages  39
Percent of dirty pages(LRU & free pages): 0.032
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 173, not young 254
0.00 youngs/s, 0.00 non-youngs/s
Pages read 33012, created 185887, written 258491
0.00 reads/s, 26.49 creates/s, 39.49 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: 122454, unzip_LRU len: 0
I/O sum[14542]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   131072
Free buffers       1006
Database pages     122449
Old database pages 45180
Modified db pages  46
Percent of dirty pages(LRU & free pages): 0.037
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 120, not young 12679
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32937, created 187265, written 249442
0.00 reads/s, 29.79 creates/s, 39.18 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: 122449, unzip_LRU len: 0
I/O sum[14542]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   131072
Free buffers       1012
Database pages     122452
Old database pages 45182
Modified db pages  25
Percent of dirty pages(LRU & free pages): 0.020
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 202, not young 5093
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32867, created 187025, written 253352
0.00 reads/s, 30.69 creates/s, 40.20 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: 122452, unzip_LRU len: 0
I/O sum[14542]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   131072
Free buffers       1021
Database pages     122423
Old database pages 45171
Modified db pages  13
Percent of dirty pages(LRU & free pages): 0.011
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 64, not young 2573
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32909, created 187403, written 243809
0.00 reads/s, 25.82 creates/s, 31.64 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: 122423, unzip_LRU len: 0
I/O sum[14542]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   131072
Free buffers       944
Database pages     122502
Old database pages 45200
Modified db pages  93
Percent of dirty pages(LRU & free pages): 0.075
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 69, not young 1616
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32899, created 185607, written 251186
0.00 reads/s, 25.36 creates/s, 32.97 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: 122502, unzip_LRU len: 0
I/O sum[14542]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=21173, Main thread ID=140612953028352, state: sleeping
Number of rows inserted 68789564, updated 0, deleted 0, read 0
10659.42 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT

Mysql的行为就好像它的内部IO限制大约为25 of的写/秒。

我知道我没有增加基准，我已经连续20个小时了，而且手头没有结果。请相信我磁盘是非常快的。分配给mysql的内存不起作用，我从1GB增加到50。没什么区别。

我已经花了半周16小时的时间来完成这个任务，我已经筋疲力尽了。我最后能想到的就是买一个像Oracle这样的商业数据库，但这是又一场噩梦。

Only有一次在导入IBD文件"RAW“时具有可接受的速度：，然后使用导入TABLESPACE。但是，这需要对源数据库进行许多小时的锁定才能获得二进制快照，然后复制它(通过网络需要时间)，然后再次导入它。导入TABLESPACE本身是好的，大约600 MB/秒--这是最快的，但对我的事业来说是没用的。

这里的桌子：

    CREATE TABLE `eval` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `intern_id` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `first_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `last_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `middle_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `location` varchar(196) COLLATE utf8_bin DEFAULT NULL,
  `i` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `e` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `country_code` varchar(4) COLLATE utf8_bin DEFAULT NULL,
  `country_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `state_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `city_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `education` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `num_c` smallint(6) DEFAULT NULL,
  `num_j` smallint(6) DEFAULT NULL,
  `j_t` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `c_name` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `e_a` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `flag_existent` tinyint(4) DEFAULT NULL COMMENT '1/0',
  `public_p_u` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `c_intern_id` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `unmatched_facts` varchar(2048) COLLATE utf8_bin DEFAULT NULL,
  `dt_snapshot` datetime DEFAULT NULL,
  `change_small` tinyint(4) DEFAULT NULL,
  `change_significant` tinyint(4) DEFAULT NULL,
  `j_t_auth` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT 'sure about it',
  `c_name_auth` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT 'sure about it',
  `c_intern_id_guess` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `ut_created` int(11) DEFAULT NULL,
  `reserve_int_2` int(11) DEFAULT NULL,
  `reserve_vc1` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `reserve_vc2` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `reserve_vc_3` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `intern_id` (`intern_id`),
  KEY `location` (`location`),
  KEY `country_name` (`country_name`),
  KEY `country_name_location_notnull` (`country_name`(1),`location`(1)),
  KEY `location_country_name` (`location`,`country_name`(1)),
  KEY `location_null_country_name` (`location`(1),`country_name`),
  KEY `dt_snapshot` (`dt_snapshot`),
  KEY `state_name` (`state_name`),
  KEY `city_name` (`city_name`),
  KEY `c_name` (`c_name`),
  KEY `c_name_auth` (`c_name_auth`),
  KEY `j_t` (`j_t`)
) ENGINE=InnoDB AUTO_INCREMENT=19883676 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

我测试导入时没有索引，只有主键和完整索引。基本上速度总是一样的。唯一的区别是磁盘IO速度随着索引的增加而增加，但是行/秒保持不变。

Update使用"IMPORT“导入表工作得很快(可怕的方法要求删除IBD文件，任何中断都会损坏表，而且我必须锁定主源数据库一个小时)此方法允许大约350 k行/秒。

现在，我正在使用服务器上加载的数据库，使用需要完全扫描的简单选择。(在xxx不为空的情况下，计数(*))数据库只在100 at /秒进行完全扫描！有一个瓶颈限制了90%的速度。

Update:我试图绕过查询性能瓶颈，进行5个数据库会话，对同一个数据库表执行SELECT查询，但按ID 1-10000000、1000000-2000000、300000-4000000等分隔查询。每一次会话都会使磁盘负载增加100 ID/秒。因此，使用5个并发select查询/会话比使用一个查询快5倍。但实际上，这应该要慢得多。这意味着需要大量的随机IO，因为5个线程可以在不同的位置快速访问同一个文件，从而减少了顺序IO的可能性。

我也有类似的问题，写5次到同一个数据库比写1次要快5倍，但是它以非常慢的速度( times的1-5%)饱和。

1在表处选择：

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.21    0.00    0.35   10.66    0.26   87.52

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
xvda              0.67         0.00        21.33          0         64
xvdg              0.00         0.00         0.00          0          0
xvdh            134.33         5.33       721.33         16       2164
xvdf              4.67         0.00        25.33          0         76
nvme0n1        7032.00    112512.00         0.00     337536          0

在同一表上选择不同主键位置的5：

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.98    0.00    0.63   43.35    0.77   53.28
    Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
    xvda              0.67         0.00        22.67          0         68
    xvdg              0.00         0.00         0.00          0          0
    xvdh            111.33        13.33       598.67         40       1796
    xvdf              0.00         0.00         0.00          0          0
    nvme0n1       30051.33    480821.33         0.00    1442464          0

Latest结论这个问题似乎是MySQL和MariaDB内部的一个缺陷，很可能是由单线程设计造成的。似乎随着列数的增加，最大性能下降，每列都会造成一定的延迟开销。InnoDb/XtraDb似乎不是问题所在。这是我目前唯一能找到的解释，除了编写多线程自定义代码之外，没有任何解决方案。

all全局变量和显示状态： https://paste.ee/p/Yk1Le

Here整个配置文件(当前变体，我尝试了所有我认为的)

[client]
port            = 3316
socket          = /maincache/share/mysqld.sock


[mysqld_safe]
socket          = /maincache/share/mysqld.sock
nice            = 0

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /maincache/share/mysqld.sock
port            = 3316
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /instance_store/tmp
lc_messages_dir = /usr/share/mysql
lc_messages     = en_US
skip-external-locking
bind-address            = 127.0.0.1
max_connections         = 100
connect_timeout         = 5
wait_timeout            = 600
max_allowed_packet      = 160M
thread_cache_size       = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 16M
max_heap_table_size     = 16M
join_buffer_size=32k
sort_buffer_size=32k
myisam_recover_options = BACKUP
key_buffer_size         = 6M
table_open_cache        = 1000
table_open_cache_instances = 8
myisam_sort_buffer_size = 16M
concurrent_insert       = 2
read_buffer_size        = 2M
read_rnd_buffer_size    = 1M
query_cache_limit               = 16M
query_cache_size                = 256M
log_error = /maincache/share/cluster/mysql_error.log
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time = 10

expire_logs_days        = 10
max_binlog_size         = 100M
default_storage_engine  = InnoDB
innodb_force_recovery=1
innodb_buffer_pool_size = 10G
innodb_buffer_pool_chunk_size=512M
innodb_file_per_table   = 1
innodb_open_files       = 600
innodb_flush_method     = O_DIRECT_NO_FSYNC
innodb_log_file_size    = 512M
innodb_io_capacity=5000
innodb_io_capacity_max=80000
innodb_flush_neighbors=0
innodb_write_io_threads=64
innodb_read_io_threads=64
innodb_change_buffer_max_size=70
innodb_buffer_pool_instances=128
innodb_thread_concurrency=144

[galera]

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

服务器有64 in的ram，但在这个变体中，我将mysql服务器限制为10 in。它显示的性能绝对没有差别，无论是无害数据库缓冲区。服务器是空闲的，在插入过程中也是80%-90%空闲(IO/cpu)，当然不是交换。

Answer 1

两年过去了，多个SQL版本升级已经通过，测试了5台不同的高性能服务器(基本上是存储速度最快的服务器和cpu AWS提供的)。

从mysql 5.7跳转。对于mysql 8，性能下降的唯一好处是更复杂的SQL支持，但在性能方面，没有什么比这更好了。伯爵(*)现在速度很快，它使用多线程，但只有在没有使用条件的情况下才使用，这使得它更像是一个笑话，而不是真正的改进。

在分析和测试任何参数和优化组合的许多周中，我可以验证我最初的疑虑:这些问题来自于Mysql资源的使用效率极低(目前为8.0.19 Percona)，操作是单线程的，而且没有优化，具体取决于您正在做什么--行写入之间的延迟非常大。

我发现有两个“解决方案”，因为在我的时间内不可能从Mysql切换到更好的数据库服务器：

1. 有了一个非常强大的服务器，它拥有近无限的IOPS和数十个CPU核心，最快的插入方法是将数据分割成块(取决于表和数据块100-10000)，并将它们并行插入服务器。像xargs或linux上的并行工具是有帮助的(seq 0 100000\ xargs .mysql插入id之间)--尽管这是处理高性能服务器的最低效方法之一(极端的IOPS使用、大量锁定、对主键群集没有很好地对齐的纯随机写入)--这可以将性能从10倍提高到100倍。我确实有过几个星期后才完成的插入查询。

解决方案1只在拥有大量空闲资源的服务器上工作，就像在我的问题中，当90+%服务器资源空闲时，为什么mysql基本上只在一个线程循环中休眠()。Mysql在parsin中有一个巨大的性能瓶颈，每一个INSERTed行都调用解析器，而解析器的调用时间似乎有一半时间是睡眠()。

1. 考虑到您的服务器没有那么大的空闲资源，例如，我的服务器有200k IOPS数据存储，但只有4个CPU核心。在这种情况下，并行插入将使CPU快速饱和，IOPS将被浪费。正如我前面提到的，mysql在SQL解析方面效率很低。在这些情况下，我尝试了很多次，加载数据比insert更快，但是自动化是一个巨大的麻烦，而且速度的提高很小。此外，加载数据浪费cpu周期，就像电影院里的爆米花。在这些情况下，最快的解决方案是导入TABLESPACE。这意味着我们回到了单线程写入和读取(DOS游戏性能提示计数)，但是现在读取是连续的，而且写入只是改变了一个数字，所以表被集成到了有点尴尬的内部now存储中(缺少了那些now热拷贝，对吗？)

旁白：

1. 不管你做什么，mysql就像一个老DOS游戏。它使用一个核心。因此，在选择服务器时，您应该将重点放在高单核心性能上，这就产生了很大的不同。
2. 当使用导入、TABLESPACE或加载数据时，不能将任何类型的二进制列(blobs)与表压缩结合使用。Mysql 8有未经修正的错误，导致它在处理表空间压缩(ROW_FORMAT=compressed)和blobs时出现分段错误(信号11)。这是一个罕见的错误，您可能不会在最初的200万行中遇到它，但是总有一天会出现mysql分段错误，这会导致数据库中隐藏的表损坏，并且可能会根据bug和配置丢失最近的数据。(从8.0到8.0.19+都有Bugs)
3. 请记住，单CPU核心性能优于mysql单次操作的一切。如果您有数百个并发连接，那么这种情况就会发生变化，多线程和IOPS在其中起着更大的作用。但是，每个连接只能在单个核心允许的情况下才能运行。

Answer 2

根据所提供的可见信息，为您的my.cnf部分提供建议。整个块将在该部分的末尾删除任何相同的命名变量，以避免在请求上发生冲突。

innodb_io_capacity=40000  # from 5000 to open the door for NVME speed
read_rnd_buffer_size=256K  # from 1M to reduce handler_read_rnd_next RPS
innodb_lru_scan_depth=128  # from 1024 to conserve CPU every second
innodb_adaptive_max_sleep_delay=10000 # from 150000 for 1 sec sleep delay
innodb_flushing_avg_loops=4  # from 30 for reduce the loop delay
innodb_thread_concurrency=0  # from 144 see dba.stackexhange Question 5666
max_seeks_for_key=32  # to limit optimizer to nn vs ~ 4 Billion possible
max_write_locks_count=16  # to allow RD after nn lcks vs up to 4 Billion lcks
thread_concurrency=30  # from 10 for additional conc - may be DEPR
innodb_buffer_pool_instances=8  # from 64 see REFMAN for innodb_lru_scan_depth details
innodb_log_file_size=6G  # from ~ 512M to reduce log rotation
innodb_log_buffer_size=3G  # from 16M for ~ 30 minutes buffering
query_cache_type=OFF  # from ON no need to waste CPU for mgmt
query_cache_size=0  # from ~256M to conserve RAM and CPU cycles
slow_query_log=ON  # from OFF always good to have ON

在每秒加载10,000+行时使用RAM策略

内存总数=64 75，允许内存达到48 75 (~ 75%)

当你加载这么大的音量时，

innodb_buffer_pool_size=30G  # for 62.5% of your 48G
innodb_change_buffer_max_size=50  # for 50% to have best insert rate per second

当装载完成后，

为常规维护需求预留15%的全局innodb_change_buffer_max_size=15 #；

并且，您将在合理的正常运行时间内，处理needed缓冲池中所需的典型数据。

更多建议，请查看我的个人资料，网络配置文件的联系信息，包括SKYPE ID联系，请。谢谢