MySQL Bugs: #95933: Performance regression of MySQL8.0 on 8 core environment

Bug #95933	Performance regression of MySQL8.0 on 8 core environment
Submitted:	23 Jun 2019 16:06	Modified:	23 Aug 2024 11:05
Reporter:	Zongzhi Chen (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.16	OS:	Any
Assigned to:		CPU Architecture:	x86

Description:
We have compared the performance of MySQL8.0.16 and MySQL 5.6.32 on 8 core environment, the MySQL8 get almost 50% performance regression, the data is show below. The oltp_write_only sysbench test case is the most serious performance regression.

We test the 8 core environment since  8 core is almost the most popular production on cloud environment. However, I find the upstream don't pay much attention on that small size.

I find the root reason is the log_writer and log_flush thread can't get the CPU time in busy time, so after we bound the log_writer thread and log_flusher to specific thread, we get about 12% performance improvement.

How to repeat:
Run the oltp_write_only sysbench

Suggested fix:
Try to bound the log_writer to specific cpu.. or increase log_writer and log_flusher cpu priority.

performance data

Attachment: WechatIMG71653.png (image/png, text), 150.67 KiB.

Below is the sysbench command that I am running, and I use almost the same configure as dimistric

build/bin/sysbench oltp_write_only --mysql-host=xxxxxxxxxx
--mysql-port=2250 --mysql-password=xxxxxxxx --mysql-user=replicator
--tables=48 --table_size=500000 --threads=512 --report-interval=10
--rand-type=uniform prepare
build/bin/sysbench oltp_write_only --mysql-host=xxxxxxxxxx
--mysql-port=2250 --mysql-password=xxxxxxxx --mysql-user=replicator
--tables=48 --table_size=500000 --threads=512  --max-time=1800
--report-interval=10 --rand-type=uniform run

Hi Mr. Zongzhi,

Thank you for your performance regression report.

This is the summary of results that I have got. First, 5.6.36:

 transactions per second:                      316.98 per sec.
 queries per second:                            1901.91 per sec.

and those are for 8.0.16:

    transactions per second:                      938.93 per sec.
    queries per second:                            5633.61 per sec.

As you can see 8.0.16 is drastically better than 5.6.36. Settings for both version are identical, except that with 8.0, I used some settings that are not available in 5.6.

Also, my machine has 6 cores, instead of 8.

Can't repeat.

Could you show me all the result of threads from 8, 16, 32, 64, 128, ... 1024, since the more user thread the regression is more serious..
And did you turn off the SSL option and runing in cpu-bound test case?  I find that your performance data is much lower than mine..

Hi,

No, I do not use SSL ..... no need to use it on my own machine, or on the machines that are in our lab.

I ran only tests with 128, 256 and 512 threads and I remember that results were similar.

Hi 
your test result is much lower than mine, so the result of MySQL 5.6 get better performance thant MySQL 8.0 may be wrong..
can you also running the sysbench in linux environment?

Hi,

Your request has been forwarded to the appropriate department.

Hi Sinisa
what's going on of this issue,
I doubt about your result since you get a too low performance.

In my test case, it is easy to get sysbench write_only case about 30000+

following is the result we get running MySQL on SSD of 8core 

Threads	MySQL5.6	MySQL8.0
8	            21644 	20051 
16	            31474 	30626 
32	            36381 	35084 
64	            37090 	33678 
128	            37237 	31831 
256	            38007 	29596 
512	            38795 	29132 
1024	   40707 	28637

Hi Mr. Zongzhi,

We do not have access to the scheduling details of other departments.