GreatSQL-8.0.32-26 在海光的cpu上插入数据慢
不管表是INNODB 还是MyISAM ，单条插入 大概在 0.09秒~0.12 秒 。如果是intel的cpu，不存在该问题。请再补充几个信息
1、my.cnf配置文件
2、执行show processlist结果
3、执行show engine innodb status\G结果 本帖最后由 asdmusic 于 2025-6-30 15:34 编辑
yejr 发表于 2025-6-30 15:14
请再补充几个信息
1、my.cnf配置文件
2、执行show processlist结果
#
# my.cnf example for GreatSQL 8.0.32-27
#
# 下面参数选项设置仅作为参考，假定服务器配置为128C256G
#
socket = /usr/local/greatsql/data/3307/mysql.sock
loose-skip-binary-as-hex
#prompt = "(\\D)[\\u@GreatSQL][\\d]>"
no-auto-rehash
user = mysql
port = 3307
server_id = 3307
default_authentication_plugin = mysql_native_password
basedir = /usr/local/greatsql
datadir = /usr/local/greatsql/data/3307
socket = /usr/local/greatsql/data/3307/mysql.sock
pid-file = mysql.pid
character-set-server = UTF8MB4
skip_name_resolve = ON
#default_time_zone = "+8:00"
bind_address = "127.0.0.1"
secure_file_priv =
log_bin_trust_function_creators=1
#
event_scheduler=ON
sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
low-priority-updates = 1
concurrent_insert = 2
explicit_defaults_for_timestamp=true
ssl=0
#myisam settings
key_buffer_size = 256M
myisam_sort_buffer_size = 128M
# Performance
lock_wait_timeout = 3600
open_files_limit = 65535
back_log = 1024
max_connections = 1024
max_connect_errors = 1000000
table_open_cache = 2048
table_definition_cache = 2048
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 96M
max_heap_table_size = 96M
max_allowed_packet = 64M
net_buffer_shrink_interval = 180
sql_generate_invisible_primary_key = ON
loose-lock_ddl_polling_mode = ON
loose-lock_ddl_polling_runtime = 200
# Logs
log_timestamps = SYSTEM
log_error = error.log
log_error_verbosity = 3
slow_query_log = ON
log_slow_extra = ON
slow_query_log_file = slow.log
#设置slow log文件大小1G及总文件数10
max_slowlog_size = 1073741824
max_slowlog_files = 3
long_query_time = 0.01
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = ON
log_slow_replica_statements = ON
log_slow_verbosity = FULL
log_bin = binlog
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 6G
max_binlog_size = 1G
#控制binlog总大小，避免磁盘空间被撑爆
binlog_space_limit = 300G
binlog_rows_query_log_events = ON
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32
binlog_order_commits = OFF
gtid_mode = ON
enforce_gtid_consistency = ON
# Replication
relay-log = relaylog
relay_log_recovery = ON
replica_parallel_type = LOGICAL_CLOCK
#并行复制线程数可以设置为逻辑CPU数量的2倍
replica_parallel_workers = 16
binlog_transaction_dependency_tracking = WRITESET
replica_preserve_commit_order = ON
replica_checkpoint_period = 2
loose-rpl_read_binlog_speed_limit = 100
# 启用InnoDB并行查询优化功能
loose-force_parallel_execute = OFF
#设置每个SQL语句的并行查询最大并发度
loose-parallel_default_dop = 8
#设置系统中总的并行查询线程数，可以和最大逻辑CPU数量一样
loose-parallel_max_threads = 64
#并行执行时leader线程和worker线程使用的总内存大小上限，可以设置物理内存的5-10%左右
loose-parallel_memory_limit = 4G
# Parallel LOAD DATA
loose-gdb_parallel_load = ON
loose-gdb_parallel_load_chunk_size = 4M
loose-innodb_optimize_no_pk_parallel_load = ON
# Rapid
loose-plugin_load_add = 'ha_rapid.so'
loose-rapid_memory_limit = 64G
loose-rapid_worker_threads = 32
loose-rapid_hash_table_memory_limit = 30
loose-secondary_engine_parallel_load_workers = 16
# Turbo
#loose-plugin_load_add = 'turbo.so'
loose-turbo_memory_limit= 64G
loose-turbo_worker_threads = 32
# Clone
loose-plugin_load_add = 'mysql_clone.so'
# MGR
#loose-plugin_load_add = 'group_replication.so'
#loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
#loose-group_replication_view_change_uuid = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1"
#loose-group_replication_local_address = "172.16.16.10:33071"
#loose-group_replication_group_seeds = '172.16.16.10:33071,172.16.16.12:33071,172.16.16.12:33071'
#loose-group_replication_ip_allowlist = '172.160.16.0/8,192.168.0.0/24,::1/128'
#loose-group_replication_communication_stack = "XCOM"
#loose-group_replication_recovery_use_ssl = OFF
#loose-group_replication_ssl_mode = DISABLED
#loose-group_replication_start_on_boot = OFF
#loose-group_replication_bootstrap_group = OFF
#loose-group_replication_exit_state_action = READ_ONLY
#loose-group_replication_flow_control_mode = "DISABLED"
#loose-group_replication_single_primary_mode = ON
#loose-group_replication_enforce_update_everywhere_checks = OFF
#loose-group_replication_majority_after_mode = ON
#loose-group_replication_communication_max_message_size = 10M
#loose-group_replication_arbitrator = OFF
#loose-group_replication_single_primary_fast_mode = 1
#loose-group_replication_request_time_threshold = 100
#loose-group_replication_primary_election_mode = GTID_FIRST
#loose-group_replication_unreachable_majority_timeout = 0
#loose-group_replication_member_expel_timeout = 5
#loose-group_replication_autorejoin_tries = 288
#loose-group_replication_recovery_get_public_key = ON
#loose-group_replication_donor_threshold = 100
# greatdb_ha
#loose-plugin_load_add = 'greatdb_ha.so'
#loose-greatdb_ha_enable_mgr_vip = OFF
#loose-greatdb_ha_mgr_vip_nic = 'eth0'
#loose-greatdb_ha_mgr_vip_ip = '172.16.16.252'
#loose-greatdb_ha_mgr_vip_mask = '255.255.255.0'
#loose-greatdb_ha_port = 33072
#loose-greatdb_ha_mgr_read_vip_ips = "172.16.16.251,172.16.16.252"
#loose-greatdb_ha_mgr_read_vip_floating_type = "TO_ANOTHER_SECONDARY"
#loose-greatdb_ha_send_arp_packge_times = 5
#loose-greatdb_ha_mgr_exit_primary_kill_connection_mode = OFF
#report_host = 127.0.0.1
#report_port = 3307
# InnoDB
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_redo_log_capacity = 2G
innodb_undo_log_truncate = ON
innodb_doublewrite_files = 2
innodb_max_undo_log_size = 2G
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_open_files = 65534
#提醒：当需要用CLONE加密特性时，不要选用O_DIRECT模式，否则会比较慢
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = ON
innodb_print_all_deadlocks = ON
innodb_online_alter_log_max_size = 4G
innodb_print_ddl_logs = ON
innodb_status_file = ON
innodb_status_output = OFF
innodb_status_output_locks = ON
innodb_sort_buffer_size = 64M
innodb_adaptive_hash_index = OFF
innodb_numa_interleave = OFF
innodb_spin_wait_delay = 20
innodb_print_lock_wait_timeout_info = ON
innodb_change_buffering = none
#自动杀掉超过5分钟不活跃事务，避免行锁被长时间持有
kill_idle_transaction = 300
innodb_data_file_async_purge = ON
# InnoDB monitor
#innodb_monitor_enable = "module_innodb,module_server,module_dml,module_ddl,module_trx,module_os,module_purge,module_log,module_lock,module_buffer,module_index,module_ibuf_system,module_buffer_page,module_adaptive_hash"
# PFS
#performance_schema = 1
#performance_schema_instrument = '%memory%=on'
#performance_schema_instrument = '%lock%=on'
就是网上的配置基本没有修改
show processlist显示就是我insert语句 就测试中的一条，我把insert 复制了10000条，然后修改了内容，插入的是同一个表。只是数据不通。
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-06-30 23:32:08 140123368318720 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6 srv_active, 0 srv_shutdown, 1230 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 24
OS WAIT ARRAY INFO: signal count 22
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 4376
Purge done for trx's n:o < 4376 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421600260265656, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421600260264808, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421600260263960, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421600260263112, not started
0 lock struct(s), heap size 1128, 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 (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: , aio writes: ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1173 OS file reads, 396 OS file writes, 138 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 22713093
Log buffer assigned up to 22713093
Log buffer completed up to 22713093
Log written up to 22713093
Log flushed up to 22713093
Added dirty pages up to 22713093
Pages flushed up to 22713093
Last checkpoint at 22713093
Log minimum file id is 0
Log maximum file id is 0
Modified age no less than 22713093
Checkpoint age 0
Max checkpoint age 3614826496
66 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 498809
Buffer pool size 65528
Buffer pool size, bytes 1073610752
Free buffers 64240
Database pages 1288
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1145, created 143, written 263
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1288, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8080
Database pages 111
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 106, created 5, written 29
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 111, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8099
Database pages 92
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 90, created 2, written 12
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 92, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8105
Database pages 86
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 86, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 7911
Database pages 280
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 279, created 1, written 23
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 280, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8116
Database pages 75
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 73, created 2, written 14
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 75, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8144
Database pages 47
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 46, created 1, written 3
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 47, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 7949
Database pages 242
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 174, created 68, written 99
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 242, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 7836
Database pages 355
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 291, created 64, written 83
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 355, 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
0 RW transactions active inside InnoDB
Process ID=67892, Main thread ID=140123727460096 , state=sleeping
Number of 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
Number of system rows inserted 53, updated 353, deleted 75, read 5468
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
目前国产CPU的性能和INTER相比，还是有差距的。 asdmusic 发表于 2025-6-30 15:28
yejr 发表于 2025-6-30 15:14
请再补充几个信息
1、my.cnf配置文件
执行 SHOW ENGINE INNODB STATUS\G 查看时也是执行 INSERT 插入请求的时候吗，没看到有 INSERT 请求在执行
还是需要补充 SHOW PROCESSLIST 的状态，我要看 INSERT 的请求状态是怎样的，同样地，也是在 INSERT 等待期间执行
另外，可以修改 long_query_time = 0，记录 INSERT 慢请求日志，然后再发上来 yejr 发表于 2025-6-30 16:05
执行 SHOW ENGINE INNODB STATUS\G 查看时也是执行 INSERT 插入请求的时候吗，没看到有 INSERT 请求在执 ...
插入的表 不是 innodb类型时也是这样子 慢 ，速度基本差不多 本帖最后由 asdmusic 于 2025-6-30 18:04 编辑
asdmusic 发表于 2025-6-30 17:57
插入的表 不是 innodb类型时也是这样子 慢 ，速度基本差不多下面的是innodb类型
mysql> show processlist;
+----+-----------------+-----------+--------------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------+--------------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
|5 | event_scheduler | localhost | NULL | Daemon| 1100 | Waiting on empty queue | NULL | 1099678 | 0 | 0 |
| 13 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 |
| 14 | root | localhost | Data_Monitor | Query | 0 | waiting for handler commit | INSERT INTO `ComObjectList1` VALUES (1,3198502171,3899309670,'DC440E8B-8B54-41EF-9B09-93BEC43FD71C', | 19 | 0 | 0 |
+----+-----------------+-----------+--------------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
3 rows in set (0.00 sec)
mysql>SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-07-01 01:58:33 140169210492672 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 47 srv_active, 0 srv_shutdown, 1086 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 25
OS WAIT ARRAY INFO: signal count 25
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2552
Purge done for trx's n:o < 2550 undo n:o < 0 state: running but idle
History list length 34
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421645061148872, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645061147176, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645061146328, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 2550, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 14, OS thread handle 140169278875392, query id 638 localhost root waiting for handler commit
INSERT INTO `ComObjectList1` VALUES (1,1680897625,2339795195,'66F86EE4-FFEF-401B-AD9F-608A55665FE9','9E840A11-EEB5-4E1D-AD28-E4D629CCF955',3,1610809347,'IP4640.DALimiter._DALimiter.rstGetInPatientDALimiterList',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'')
TABLE LOCK table `Data_Monitor`.`ComObjectList1` trx id 2550 lock mode IX
--------
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 (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: , aio writes: ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 1; buffer pool: 0
1090 OS file reads, 2313 OS file writes, 1347 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 30.46 writes/s, 19.75 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
0.00 hash searches/s, 31.22 non-hash searches/s
---
LOG
---
Log sequence number 21348395
Log buffer assigned up to 21348395
Log buffer completed up to 21348395
Log written up to 21348395
Log flushed up to 21348385
Added dirty pages up to 21348395
Pages flushed up to 20461353
Last checkpoint at 20461353
Log minimum file id is 0
Log maximum file id is 0
Modified age no less than 887042
Checkpoint age 887042
Max checkpoint age 1806958592
1498 log i/o's done, 22.67 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 558401
Buffer pool size 65528
Buffer pool size, bytes 1073610752
Free buffers 64085
Database pages 1443
Old database pages 0
Modified db pages713
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1058, created 389, written 593
0.00 reads/s, 0.73 creates/s, 4.47 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: 1443, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8083
Database pages 108
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 103, created 5, written 57
0.00 reads/s, 0.10 creates/s, 0.10 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: 108, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8079
Database pages 112
Old database pages 0
Modified db pages55
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 110, created 2, written 42
0.00 reads/s, 0.00 creates/s, 0.73 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: 112, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8096
Database pages 95
Old database pages 0
Modified db pages48
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 95, created 0, written 7
0.00 reads/s, 0.00 creates/s, 0.73 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: 95, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 7870
Database pages 321
Old database pages 0
Modified db pages241
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 223, created 102, written 108
0.00 reads/s, 0.21 creates/s, 0.73 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: 321, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8122
Database pages 69
Old database pages 0
Modified db pages45
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 67, created 2, written 38
0.00 reads/s, 0.00 creates/s, 0.73 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: 69, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8132
Database pages 59
Old database pages 0
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58, created 1, written 30
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 59, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 7887
Database pages 304
Old database pages 0
Modified db pages124
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 114, created 190, written 177
0.00 reads/s, 0.10 creates/s, 0.73 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: 304, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 7816
Database pages 375
Old database pages 0
Modified db pages200
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 288, created 87, written 134
0.00 reads/s, 0.31 creates/s, 0.73 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: 375, 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
1 RW transactions active inside InnoDB
Process ID=13037, Main thread ID=140167974336256 , state=sleeping
Number of rows inserted 355, updated 0, deleted 0, read 0
5.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 968, updated 517, deleted 431, read 7045
4.89 inserts/s, 0.11 updates/s, 5.55 deletes/s, 5.78 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
SELECT SQL_TEXT, ROWS_AFFECTED, TIMER_WAIT / 1000000000000 AS time_sec, ROWS_AFFECTED / (TIMER_WAIT / 1000000000000) AS rows_per_sec FROM performance_schema.events_statements_history WHERE SQL_TEXT LIKE '%INSERT%' AND ROWS_AFFECTED > 0 ORDER BY TIMER_START DESC LIMIT 10;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------+--------------+
| SQL_TEXT | ROWS_AFFECTED | time_sec | rows_per_sec |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------+--------------+
| INSERT INTO `ComObjectList1` VALUES (1,3549973875,1354721761,'176BFB8D-D8E5-4A2B-8745-6D7EB79FB6AB','212ABBE9-BCD6-4F90-A340-0EDC5256531E',0,1610809344,'OP0013.FeeKindTree._FeeKindTree.rstGetFeeKindTreeByFlag',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.2991 | 3.3433 |
| INSERT INTO `ComObjectList1` VALUES (1,1423743161,2393951217,'E8CF1FF6-DB42-42AF-9757-4A7B11BF1BBF','62206861-2D36-4499-A9E6-C61F1B9DA5A0',5,1610809350,'IP4626.ExecDAREQ._ExecDAREQ.lUpdateDAListReturnMediHideFlag',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.3072 | 3.2551 |
| INSERT INTO `ComObjectList1` VALUES (1,1423734969,2393951086,'E8CF1FF6-DB42-42AF-9757-4A7B11BF1BBF','62206861-2D36-4499-A9E6-C61F1B9DA5A0',4,1610809349,'IP4626.ExecDAREQ._ExecDAREQ.blnUpdateExecStatus',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.3551 | 2.8164 |
| INSERT INTO `ComObjectList1` VALUES (1,1423726777,2393950955,'E8CF1FF6-DB42-42AF-9757-4A7B11BF1BBF','62206861-2D36-4499-A9E6-C61F1B9DA5A0',3,1610809348,'IP4626.ExecDAREQ._ExecDAREQ.blnSetNurseFlagByExecDA',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.2474 | 4.0422 |
| INSERT INTO `ComObjectList1` VALUES (1,1423718585,2393950693,'E8CF1FF6-DB42-42AF-9757-4A7B11BF1BBF','62206861-2D36-4499-A9E6-C61F1B9DA5A0',2,1610809346,'IP4626.ExecDAREQ._ExecDAREQ.lngSaveExecDABatchList',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.2930 | 3.4135 |
| INSERT INTO `ComObjectList1` VALUES (1,1423710393,2393950562,'E8CF1FF6-DB42-42AF-9757-4A7B11BF1BBF','62206861-2D36-4499-A9E6-C61F1B9DA5A0',1,1610809345,'IP4626.ExecDAREQ._ExecDAREQ.lngConfirmPrandialDA',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.3975 | 2.5160 |
| INSERT INTO `ComObjectList1` VALUES (1,1423702201,2393950431,'E8CF1FF6-DB42-42AF-9757-4A7B11BF1BBF','62206861-2D36-4499-A9E6-C61F1B9DA5A0',0,1610809344,'IP4626.ExecDAREQ._ExecDAREQ.lngDepartmentPrint',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.3499 | 2.8583 |
| INSERT INTO `ComObjectList1` VALUES (1,589799461,3760090748,'34313006-A081-4A36-82FD-96BF66E55657','357247AB-7615-41CB-B8EA-20D0BEB3B5DF',11,1610809358,'IP4626.ExecDA._ExecDA.rstGetDetailByExecDAID',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.3555 | 2.8131 |
| INSERT INTO `ComObjectList1` VALUES (1,589791269,3760090617,'34313006-A081-4A36-82FD-96BF66E55657','357247AB-7615-41CB-B8EA-20D0BEB3B5DF',10,1610809357,'IP4626.ExecDA._ExecDA.rstGetByExecDAID',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.3613 | 2.7676 |
| INSERT INTO `ComObjectList1` VALUES (1,589783077,3760090486,'34313006-A081-4A36-82FD-96BF66E55657','357247AB-7615-41CB-B8EA-20D0BEB3B5DF',9,1610809356,'IP4626.ExecDA._ExecDA.rstGetExecDAByExecStatus',' SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'') | 1 | 0.2915 | 3.4304 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------+--------------+
10 rows in set (0.01 sec)
asdmusic 发表于 2025-6-30 18:01
下面的是innodb类型
mysql> show processlist;
看起来是你服务器的物理I/O性能比较差，在事务两阶段提交时耗时较久，可以试试用下面的简易方法做测试：
time dd oflag=direct if=/dev/zero of=./zero bs=1M count=20480
同时在进行INSERT测试时，打开iotop观察状态，把上面两个结果都贴上来
关于两阶段提交可参考 https://greatsql.cn/docs/8.0.32-27/2-about-greatsql/4-5-greatsql-redo-log.html#两阶段提交-2pc yejr 发表于 2025-7-1 10:09
看起来是你服务器的物理I/O性能比较差，在事务两阶段提交时耗时较久，可以试试用下面的简易方法做测试：
...
Total DISK READ : 0.00 B/s | Total DISK WRITE : 136.61 M/s
Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 136.61 M/s
TIDPRIOUSER DISK READ DISK WRITE> COMMAND
67010 be/4 root 0.00 B/s136.61 M/s dd oflag=direct if=/dev/zero of=./zero bs=1M count=20480
# time dd oflag=direct if=/dev/zero of=./zero bs=1M count=20480
20480+0 records in
20480+0 records out
21474836480 bytes (21 GB) copied, 150.574 s, 143 MB/s
real 2m30.576s
user 0m0.012s
sys 0m3.194s
这个是不insert只是磁盘测试。基本算正常吧。通系统mysql5.7 不存在慢（所以应该不是单纯i/o慢）。而且我上夜说了就算是MyISAM 也慢，和 innodb差不多。
下面是单独插入时的iotop
Total DISK READ : 0.00 B/s | Total DISK WRITE : 514.69 K/s
Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 727.52 K/s
TIDPRIOUSER DISK READ DISK WRITE> COMMAND
13074 be/4 mysql 0.00 B/s185.75 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf
13136 be/4 mysql 0.00 B/s112.22 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf
13057 be/4 mysql 0.00 B/s 61.92 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf
13061 be/4 mysql 0.00 B/s 61.92 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf
13062 be/4 mysql 0.00 B/s 61.92 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf
13060 be/4 mysql 0.00 B/s 30.96 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf
1 be/4 root 0.00 B/s 0.00 B/s systemd --switched-root --system --deserialize 17
2 be/4 root 0.00 B/s 0.00 B/s
asdmusic 发表于 2025-7-1 10:22
这个是不insert只是磁盘测试。基本算正常吧。通系统mysql5.7 不存在慢（所以应该不是单纯i/o慢）。 ...
你的磁盘I/O写入确实慢，这是我本地在家用SSD盘上测试的结果
time dd oflag=direct if=/dev/zero of=./zero bs=1M count=20480
20480+0 records in
20480+0 records out
21474836480 bytes (21 GB, 20 GiB) copied, 22.1804 s, 968 MB/s
real 0m22.181s
user 0m0.007s
sys 0m1.790s
如果说写入InnoDB慢是因为存在两阶段提交，尚可理解，连写入MyISAM都慢，那基本上就是磁盘I/O的瓶颈原因了
可以试着把binlog关了，并且设置 innodb_flush_log_at_trx_commit=0 再看看
页:
[1]
2