点击参与 GreatSQL 8.0.32-26 文档捉虫~~~

GreatSQL社区

搜索

[已解决] GreatSQL-8.0.32-26 在海光的cpu上插入数据慢

757 13 2025-6-30 14:12
不管表是INNODB 还是MyISAM ，单条插入 大概在 0.09秒~0.12 秒 。如果是intel的cpu，不存在该问题。
GreatSQL
全部回复(13)
只看楼主
yejr 2025-6-30 15:14:36
请再补充几个信息
1、my.cnf配置文件
2、执行show processlist结果
3、执行show engine innodb status\G结果
回复 采纳

举报

asdmusic 2025-6-30 15:28:02
本帖最后由 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
#
[client]
socket    = /usr/local/greatsql/data/3307/mysql.sock

[mysql]
loose-skip-binary-as-hex
#prompt = "(\\D)[\\u@GreatSQL][\\d]>"
no-auto-rehash

[mysqld]
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 < 4376 undo n < 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: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
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 pages  0
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[0]:cur[0], unzip sum[0]:cur[0]
----------------------
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 pages  0
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[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       8099
Database pages     92
Old database pages 0
Modified db pages  0
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[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       8105
Database pages     86
Old database pages 0
Modified db pages  0
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[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       7911
Database pages     280
Old database pages 0
Modified db pages  0
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[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       8116
Database pages     75
Old database pages 0
Modified db pages  0
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[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       8144
Database pages     47
Old database pages 0
Modified db pages  0
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[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       7949
Database pages     242
Old database pages 0
Modified db pages  0
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[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       7836
Database pages     355
Old database pages 0
Modified db pages  0
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[0]:cur[0], unzip sum[0]:cur[0]
--------------
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)




回复 采纳

举报

reddey 2025-6-30 15:39:08
目前国产CPU的性能和INTER相比，还是有差距的。
回复 采纳

举报

yejr 2025-6-30 16:05:43
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 慢请求日志，然后再发上来
回复 采纳

举报

asdmusic 2025-6-30 17:57:15
yejr 发表于 2025-6-30 16:05
执行 SHOW ENGINE INNODB STATUS\G 查看时也是执行 INSERT 插入请求的时候吗，没看到有 INSERT 请求在执 ...

插入的表 不是 innodb类型时  也是这样子 慢 ，速度基本差不多
回复 采纳

举报

asdmusic 2025-6-30 18:01:43
本帖最后由 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 < 2550 undo n < 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','[in,out] 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: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
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 pages  713
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[1208]:cur[48], unzip sum[0]:cur[0]
----------------------
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 pages  0
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[151]:cur[6], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       8079
Database pages     112
Old database pages 0
Modified db pages  55
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[151]:cur[6], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       8096
Database pages     95
Old database pages 0
Modified db pages  48
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[151]:cur[6], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       7870
Database pages     321
Old database pages 0
Modified db pages  241
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[151]:cur[6], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       8122
Database pages     69
Old database pages 0
Modified db pages  45
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[151]:cur[6], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       8132
Database pages     59
Old database pages 0
Modified db pages  0
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[151]:cur[6], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       7887
Database pages     304
Old database pages 0
Modified db pages  124
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[151]:cur[6], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       7816
Database pages     375
Old database pages 0
Modified db pages  200
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[151]:cur[6], unzip sum[0]:cur[0]
--------------
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','[in,out] 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','[in,out] 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','[in,out] 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','[in,out] 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','[in,out] 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','[in,out] 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','[in,out] 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','[in,out] 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','[in,out] 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','[in,out] SAFEARRAY(VARIANT)* ParameterSet','',1,'IDispatch',7,0,1,'')               |             1 |   0.2915 |       3.4304 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------+--------------+
10 rows in set (0.01 sec)
回复 采纳

举报

yejr 2025-7-1 10:09:24
asdmusic 发表于 2025-6-30 18:01
下面的是innodb类型

mysql> show processlist;

看起来是你服务器的物理I/O性能比较差，在事务两阶段提交时耗时较久，可以试试用下面的简易方法做测试：

  2. time dd oflag=direct if=/dev/zero of=./zero bs=1M count=20480
复制代码


同时在进行INSERT测试时，打开iotop观察状态，把上面两个结果都贴上来

关于两阶段提交可参考 https://greatsql.cn/docs/8.0.32- ... tsql-redo-log.html#两阶段提交-2pc
回复 采纳

举报

asdmusic 2025-7-1 10:22:21
yejr 发表于 2025-7-1 10:09
看起来是你服务器的物理I/O性能比较差，在事务两阶段提交时耗时较久，可以试试用下面的简易方法做测试：
...

  2. Total DISK READ :       0.00 B/s | Total DISK WRITE :     136.61 M/s
  3. Actual DISK READ:       0.00 B/s | Actual DISK WRITE:     136.61 M/s
  4.     TID  PRIO  USER     DISK READ DISK WRITE>    COMMAND                                                                                                            
  5.   67010 be/4 root        0.00 B/s  136.61 M/s dd oflag=direct if=/dev/zero of=./zero bs=1M count=20480



  9. [root@localhost ~]# time dd oflag=direct if=/dev/zero of=./zero bs=1M count=20480
  10. 20480+0 records in
  11. 20480+0 records out
  12. 21474836480 bytes (21 GB) copied, 150.574 s, 143 MB/s

  14. real    2m30.576s
  15. user    0m0.012s
  16. sys     0m3.194s
复制代码


这个是不insert  只是磁盘测试。  基本算正常吧。通系统mysql5.7 不存在慢（所以应该不是单纯i/o慢）。而且我上夜说了  就算是MyISAM 也慢，和 innodb差不多。
下面是单独插入时的iotop


  2. Total DISK READ :       0.00 B/s | Total DISK WRITE :     514.69 K/s
  3. Actual DISK READ:       0.00 B/s | Actual DISK WRITE:     727.52 K/s
  4.     TID  PRIO  USER     DISK READ DISK WRITE>    COMMAND                                                                                                            
  5.   13074 be/4 mysql       0.00 B/s  185.75 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf [ib_log_writer]
  6.   13136 be/4 mysql       0.00 B/s  112.22 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf [connection]   
  7.   13057 be/4 mysql       0.00 B/s   61.92 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf [ib_pg_flush_co]
  8.   13061 be/4 mysql       0.00 B/s   61.92 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf [ib_pg_flush-2]
  9.   13062 be/4 mysql       0.00 B/s   61.92 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf [ib_pg_flush-3]
  10.   13060 be/4 mysql       0.00 B/s   30.96 K/s mysqld --defaults-file=/usr/local/greatsql/3307.cnf [ib_pg_flush-1]
  11.       1 be/4 root        0.00 B/s    0.00 B/s systemd --switched-root --system --deserialize 17
  12.       2 be/4 root        0.00 B/s    0.00 B/s [kthreadd]
复制代码

回复 采纳

举报

yejr 2025-7-1 10:49:13
asdmusic 发表于 2025-7-1 10:22
这个是不insert  只是磁盘测试。  基本算正常吧。通系统mysql5.7 不存在慢（所以应该不是单纯i/o慢）。 ...

你的磁盘I/O写入确实慢，这是我本地在家用SSD盘上测试的结果
  1. time dd oflag=direct if=/dev/zero of=./zero bs=1M count=20480
  2. 20480+0 records in
  3. 20480+0 records out
  4. 21474836480 bytes (21 GB, 20 GiB) copied, 22.1804 s, 968 MB/s

  6. real        0m22.181s
  7. user        0m0.007s
  8. sys        0m1.790s
复制代码


如果说写入InnoDB慢是因为存在两阶段提交，尚可理解，连写入MyISAM都慢，那基本上就是磁盘I/O的瓶颈原因了

可以试着把binlog关了，并且设置 innodb_flush_log_at_trx_commit=0 再看看
回复 采纳

举报

下一页 »
12下一页
asdmusic

2

主题

0

博客

10

贡献

新手上路

Rank: 1

积分
18
发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2025-7-27 06:15 , Processed in 0.027753 second(s), 22 queries , Redis On.
快速回复 返回顶部 返回列表