InnoDB 插入计数减慢
我连续批量插入mysql innoDB表，每秒插入速度变慢。 有些行为是 - 如果关闭数据插入器（java）应用程序，mysql会暂时执行一些i/o操作。 - 添加一些插入然后关闭mysql服务器，关闭操作持续时间太长。如果启动和停止mysql没有任何插入，那么启动和停止操作会很快。 - 插入速度并不（很大程度上）取决于表上的数据量。如果重新启动 mysql 服务器，每秒插入类似于上次重新启动每秒插入值。
我在论坛上看到一些评论，不要连续添加，两次插入之间有间隙。有意义吗？为什么sql速度慢？
查询 SHOW VARIABLES LIKE 'inno%' 结果如下
innodb_adaptive_flushing = ON
innodb_adaptive_hash_index = ON
innodb_additional_mem_pool_size = 20971520
innodb_autoextend_increment = 8
innodb_autoinc_lock_mode = 1
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 268435456
innodb_change_buffering = all
innodb_checksums = ON
innodb_commit_concurrency = 0
innodb_concurrency_tickets = 500
innodb_data_file_path = ibdata1:50M:autoextend
innodb_data_home_dir =
innodb_doublewrite = ON
innodb_fast_shutdown = 1
innodb_file_format = Barracuda
innodb_file_format_check = ON
innodb_file_format_max = Antelope
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DSYNC
innodb_force_recovery = 0
innodb_io_capacity = 200
innodb_lock_wait_timeout = 50
innodb_locks_unsafe_for_binlog = OFF
innodb_log_buffer_size = 8388608
innodb_log_file_size = 268435456
innodb_log_files_in_group = 2
innodb_log_group_home_dir = ./
innodb_max_dirty_pages_pct = 75
innodb_max_purge_lag = 0
innodb_mirrored_log_groups = 1
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 0
innodb_open_files = 300
innodb_purge_batch_size = 20
innodb_purge_threads = 0
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 4
innodb_replication_delay = 0
innodb_rollback_on_timeout = OFF
innodb_spin_wait_delay = 6
innodb_stats_on_metadata = ON
innodb_stats_sample_pages = 8
innodb_strict_mode = ON
innodb_support_xa = ON
innodb_sync_spin_loops = 30
innodb_table_locks = ON
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 10000
innodb_use_native_aio = OFF
innodb_use_sys_malloc = ON
innodb_version = 1.1.1
innodb_write_io_threads = 4
谢谢
I batch insert to mysql innoDB table continuously, insert per second ratio is slow down.
Some behaviour are
- If shutdown data inserter (java) application, mysql do some i/o operation for a while.
- Add some insert then shutdown mysql server, shutdown operation duration is too long. If start and stop mysql without any insertion, start and stop operation so fast.
- Insert speed is not (so much) depend on data amount on table. If restart mysql server, insert per second is similar to last restart insert per second value.
I read some comment on forum, do not add continuosly, have gap between 2 insertions. Is it meaningful? Why sql is slow down?
query SHOW VARIABLES LIKE 'inno%' result is below
innodb_adaptive_flushing = ON
innodb_adaptive_hash_index = ON
innodb_additional_mem_pool_size = 20971520
innodb_autoextend_increment = 8
innodb_autoinc_lock_mode = 1
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 268435456
innodb_change_buffering = all
innodb_checksums = ON
innodb_commit_concurrency = 0
innodb_concurrency_tickets = 500
innodb_data_file_path = ibdata1:50M:autoextend
innodb_data_home_dir =
innodb_doublewrite = ON
innodb_fast_shutdown = 1
innodb_file_format = Barracuda
innodb_file_format_check = ON
innodb_file_format_max = Antelope
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DSYNC
innodb_force_recovery = 0
innodb_io_capacity = 200
innodb_lock_wait_timeout = 50
innodb_locks_unsafe_for_binlog = OFF
innodb_log_buffer_size = 8388608
innodb_log_file_size = 268435456
innodb_log_files_in_group = 2
innodb_log_group_home_dir = ./
innodb_max_dirty_pages_pct = 75
innodb_max_purge_lag = 0
innodb_mirrored_log_groups = 1
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 0
innodb_open_files = 300
innodb_purge_batch_size = 20
innodb_purge_threads = 0
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 4
innodb_replication_delay = 0
innodb_rollback_on_timeout = OFF
innodb_spin_wait_delay = 6
innodb_stats_on_metadata = ON
innodb_stats_sample_pages = 8
innodb_strict_mode = ON
innodb_support_xa = ON
innodb_sync_spin_loops = 30
innodb_table_locks = ON
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 10000
innodb_use_native_aio = OFF
innodb_use_sys_malloc = ON
innodb_version = 1.1.1
innodb_write_io_threads = 4
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
InnoDB默认工作在自动提交模式下，这意味着每次插入都需要写入磁盘两次。使用扩展插入（也称为多行插入）并将多个连续插入包含到事务中可以提高性能。
InnoDB works by default in autocommit mode, which means every insert requires writing to disk twice. Using extended inserts (a.k.a. multi-row inserts) and enclosing several consecutive inserts into a transaction increases performance.
速度变慢的原因是插入操作存储在缓存（脏页）上并定期写入硬盘。在脏页内存已满之前，插入操作会很快，然后缓存已满，并且插入速度成为磁盘写入（I/O）瓶颈。
您可以使用下面的sql来显示脏页大小“修改的数据库页”
Slow down reason is insert operation stored on to cache (dirty page) and periodically written hard-drive. Until dirty page memory is full insert operations are fast then cache is full and insert speed bottleneck disc written (I/O).
You can use below sql to show dirty page size "Modified db pages"