MySQL Bugs: #100530: Improve the document of concurrent

Bug #100530	Improve the document of concurrent_insert
Submitted:	14 Aug 2020 11:52	Modified:	19 Aug 2020 12:09
Reporter:	YIGONG HU	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S5 (Performance)
Version:	5.6	OS:	Ubuntu (18.04)
Assigned to:		CPU Architecture:	x86 (64bit)

Description:
The MySQL manual about the concurrent_insert in https://dev.mysql.com/doc/refman/5.6/en/concurrent-inserts.html doesn't mention that enabling concurrent_insert would cause scability issue for read-intensive workload. Enable the concurrent insert would cause the MySQL to call rdlock for every row and might cause a resource contention in the CPU when the number of client increase.

Rationale:

I test the concurrent_insert on MySQL 5.6 on Ubuntu 18.04 with 4 core. I run the sysbench with an read-intensive workload. The result is :

- Client Number | concurrent_insert=1  |       concurrent_insert=0   
-    1                 2555.52(TPS)               2717.72(TPS)
-    2                 3817.26(TPS)               4116.35(TPS)
-    4                 4445.7(TPS)                5047.16(TPS)
-    8                  4516(TPS)                 4996.49(TPS)

The result shows that in the read-intensive workload, the throughput is always higher with concurrent_insert disabled with about 15%.

How to repeat:
-- Create and populate the table:
Install sysbench 0.5
sysbench --mysql-socket=sock_path --mysql-db=test --table-size=500000 --num-threads=1 --max-time=10 --report-interval=10 --max-request=1000000 --mysql-table-engine=myisam  --test=select_random_ranges prepare prepare

--Warm up the cache
sysbench --mysql-socket=sock_path --mysql-db=test --table-size=500000 --num-threads=1 --max-time=300 --report-interval=60 --max-request=1000000 --mysql-table-engine=myisam  --test=select_random_ranges prepare run

--Run select benchmark with different workload
sysbench --mysql-socket=sock_path --mysql-db=test --table-size=500000 --num-threads=1 --max-time=300 --report-interval=60 --max-request=1000000 --mysql-table-engine=myisam  --test=select_random_ranges prepare run
sysbench --mysql-socket=sock_path --mysql-db=test --table-size=500000 --num-threads=2 --max-time=300 --report-interval=60 --max-request=1000000 --mysql-table-engine=myisam  --test=select_random_ranges prepare run
sysbench --mysql-socket=sock_path --mysql-db=test --table-size=500000 --num-threads=4 --max-time=300 --report-interval=60 --max-request=1000000 --mysql-table-engine=myisam  --test=select_random_ranges prepare run
sysbench --mysql-socket=sock_path --mysql-db=test --table-size=500000 --num-threads=8 --max-time=300 --report-interval=60 --max-request=1000000 --mysql-table-engine=myisam  --test=select_random_ranges prepare run

--Disable concurrent insert and rerun the benchmark

Suggested fix:
I would suggest to add a description in the document as:
> The use of concurrent insert would affects the select statement since enabling concurrent insert would increase the rdlock syscall and might cause resource contention in the CPU.

Hi Mr. HU,

Thank you for your bug report.

However, you are asking to document a very well known behaviour for MyISAM.

When MyISAM runs concurrent inserts, lock is applied, while without it, no locks. It is a simple deduction that with locks you get slower performance.

Also, we do not publish benchmarks in our documentation, especially for storage engines which are at the end of its lifetime.

Thanks for your quick response! The issue I encountered is that this parameter is enabled by default. While this default setting will help improve the performance of insert-intensive workloads, it hurts the performance when my workload is select-intensive. The official explanation of the parameter says "If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements.", which seems to suggest this is always good for performance. Thus, I think it might be good to mention this caveat in the documentation that although the default setting is good for insert-intensive workloads, it may hurt SELECT-intensive workloads so that the users can know the potential side-effect and decide whether they want to enable it.

Hi Mr. HU,

We understand what you are writing about, but our documentation mentions locking, which is sufficient.

Your remark would be quite justified if this was a Users Manual. But, this is not that kind of manual, this is Reference Manual. 

There are many MySQL User manuals available in shops and online.

Not a bug.