[MDEV-15294] extremely slow insert using single thread on a single table when binlog_commit_wait_count > 0 Created: 2018-02-13  Updated: 2018-02-13  Resolved: 2018-02-13

Status: Closed
Project: MariaDB Server
Component/s: Replication
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Michael Xu Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

A stand-alone server running mariadb 10.2.12.

binlog_commit_wait_count = 10

10,000 records insert

# time mysql sbtest < sbtest.sql
 
 
 
real    1m6.715s
 
user    0m0.280s
 
sys     0m0.694s
 
 
 
# time mysql sbtest < sbtest.sql
 
 
 
real    1m6.700s
 
user    0m0.290s
 
sys     0m0.656s


binlog_commit_wait_count = 0


# time mysql sbtest < sbtest.sql
 
 
 
real    0m2.211s
 
user    0m0.178s
 
sys     0m0.143s
 
 
 
# time mysql sbtest < sbtest.sql
 
 
 
real    0m2.280s
 
user    0m0.236s
 
sys     0m0.188s

According to the official manual, the system variable will only impact parallel replication, is that normal on a stand-alone server?

 Comments   
Comment by Sergei Golubchik [ 2018-02-13 ]

This is expected behavior. binlog_commit_wait_count=10 means that at every commit, binlog will wait hoping to collect 10 commits and write them together. Because you insert in only one connection, this never happens, so practically every your commit is simply delayed by binlog_commit_wait_usec microseconds.

Generated at Thu Feb 08 08:20:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.