MySQL Bugs: #112154: Slow Queries Exceeding 1 Second in Concurrent Insert Scenario on MySQL 8.0.34

Bug #112154	Slow Queries Exceeding 1 Second in Concurrent Insert Scenario on MySQL 8.0.34
Submitted:	23 Aug 7:38	Modified:	25 Sep 3:55
Reporter:	yunfan zheng	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.34	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	8.0.34, INSERT sql, slow query

Description:
I.Background:

Connecting to the primary server in a dual-master replication relationship, and executing batch INSERT operations with 50 concurrent connections.

II.Problem:

Encountering slow queries exceeding 1 second in duration on MySQL version 8.0.34. 

How to repeat:
I. Connect to the primary server in a dual-master replication relationship on MySQL version 8.0.34. Create a physical database and 10 tables.

II. Perform benchmark testing using the JMeter tool. Use the following command to perform benchmark testing in a batch INSERT scenario:
-----
jmeter -n -t  SeaSQL_insert10table_batch.jmx  -l ./resu/test-`date +%Y%m%d%H%M%S`.jtl -e -o ./repo/test-`date +%Y%m%d%H%M%S`.html
-----

III. Check jmeter log. It reads 50 errors after 10 minutes and 30 seconds of JMeter benchmark testing. Here the error means a slow query exceeding 1 second.

IV. Check MySQL8.0.34 slow-query.log file. It reads lots of batch INSERT operations exceeding 1 second.

V. It seems this problem only appears on version 8.0.34. When downgrading MySQL to version 8.0.32 with the same configuration and environment, conducting 3 sets of benchmark tests involving 50 concurrent connections for 30 minutes each in the batch INSERT scenario resulted in no errors.

Hi Mr. zheng,

Thank you for your bug report.

However, we are not able to repeat your problem.

First, for performance problems, we use only mysqlslap and sysbench.

What we require are table structure, its contents and the INSERT command that takes too long time to execute. 

The problem may arise from the bad design of your schema. You should have normalised your schema to the third or fourth level. Then, all writing DML's would be very , very fast .......

In those cases, long_query_time of 1 seconds, under intense load , is very, very low .....

If you need urgent help with MySQL server, we recommend you to consider our offers that you will find on this page:

https://www.mysql.com/support/

Normalising schemas is not a bug in the database software.

Can't repeat.

Thanks for your reply.

However, the problem only occurs on version 8.0.34. When we use the same configuration and environment and the same table structure for version 8.0.32, the slow query does not occur. 

I wonder if there is any difference between the two versions which results in the slow query?

BTW, here is the table structure:
---
CREATE TABLE `employee_jmeter1` (
  `id` bigint(20) NOT NULL,
  `EMPLOYEE_NO` decimal(10,0) DEFAULT NULL,
  `EMPLOYEE_NAME` varchar(30) DEFAULT NULL,
  `JoB` varchar(30) DEFAULT NULL,
  `HIRE_DATE` varchar(30) DEFAULT NULL,
  `SAL` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
---

supplement：

Dear sir，

I uploaded a zip file. It includes:
1. insertmysqlbatch.jmx --> the script to execute INSERT command using jmeter tool
2. my.cnf --> the parameter we configured
3. table_structure.sql --> the table structure we used

The following is the download link for jmeter tool for benchmark tests. I wonder if you can download and unzip it. You can execute 'jmeter --help' under /bin route to find how to use it. It likes 'jmeter -n -t file.jmx -l file.jtl'

https://dlcdn.apache.org//jmeter/binaries/apache-jmeter-5.6.zip

I hope all these documents can help to repeat! 
Best Regards!

supplement:

Dear sir,

In the attached file 'mysql-bug-slow-query.zip', I added two important system configuration files: sysctl.conf and limits.conf. And I also added a jdbc driver doc: mysql-connector-java-8.0.16.jar

system version：CentOS Linux release 7.6.1810 (Core) 
core version：Linux hotdb208 3.10.0-1160.88.1.el7.x86_64 #1 SMP Tue Mar 7 15:41:52 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

Other information
Here is the command for setting up a Master-Master Replication:
---
change master to master_host='10.10.0.209',master_user='repl',master_password='DRDS_repl@2013',Master_Port=3206,master_auto_position=1;
---

necessary docs for repeat slow query problem

Attachment: mysql-bug-slow-query.zip (application/zip, text), 2.08 MiB.

Here is the sample for the batch INSERT statement:

---
insert into EMPLOYEE_JMETER1 values
('176491','8724','秦吴李','SALESMAN','2007-09-13','3619'),
('10176491','8724','秦吴李','SALESMAN','2007-09-13','3619'),
('20176491','8724','秦吴李','SALESMAN','2007-09-13','3619'),
('30176491','8724','秦吴李','SALESMAN','2007-09-13','3619'),
('40176491','8724','秦吴李','SALESMAN','2007-09-13','3619'),
('50176491','8724','秦吴李','SALESMAN','2007-09-13','3619'),
('60176491','8724','秦吴李','SALESMAN','2007-09-13','3619'),
('70176491','8724','秦吴李','SALESMAN','2007-09-13','3619'),
('80176491','8724','秦吴李','SALESMAN','2007-09-13','3619'),
('90176491','8724','秦吴李','SALESMAN','2007-09-13','3619');

Hi Mr. zheng,

I'm MySQL Community Manager and while I leave the verification team reply to you, I would like to point that you have some practices that are not optimal:

- using bi-directional replication should be avoided absolutely
- your first table doesn't have any primary key or any not null unique key and GIPK mode is not enabled (see https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html) and with 50 threads, this will lead to some InnoDB contention.

Also, if you don't plan to use MyISAM (which I don't recommend anyway), no need to increase the default size of the MyISAM buffers.

During your test having the output of SHOW FULL PROCESSLIST and SHOW ENGINE INNODB STATUS , on both system, could also help.

Thank you, 

Best regards,

Dear Descamps,

Thanks for your suggestions. I will pass them on to my team.

I sincerely apologize that I have given the wrong table structure. Please let the verification team refer to the following. We used the primary key here.

---
CREATE TABLE `employee_jmeter1` (
  `id` bigint NOT NULL,
  `EMPLOYEE_NO` decimal(10,0) DEFAULT NULL,
  `EMPLOYEE_NAME` varchar(30) DEFAULT NULL,
  `JoB` varchar(30) DEFAULT NULL,
  `HIRE_DATE` varchar(30) DEFAULT NULL,
  `SAL` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
---

Btw, why is there such a difference in query for 8.0.34 vs. 8.0.32?

Thank you

Best Regards

Hi,

Only one additional request from us.

Since this is not a problem in our Connector/J, please send table rows in the SQL format, not JDBC format.

You can simply run mysqldump command on that table .....

We can not proceed without that.

Hi,

Do you mean you need a copy of the mysqldump export?

Hi,

I have uploaded the table row in SQL format exported by mysqldump in the attachment.

Please note that this issue only occurs after concurrent benchmark testing. The mysqldump data is just sample data.

Hi,

This is just to inform you that we shall try to repeat the problem with concurrent benchmark.

Can you share us with exact details of what kind of benchmark did you use and which concurrency.

If it is a high concurrency, then 1 (one) second or more is expected behaviour.

Thanks for your reply!

The following is the download link for jmeter tool for benchmark tests. I wonder if you can download and unzip it. You can execute 'jmeter --help' under /bin route to find how to use it. It likes 'jmeter -n -t file.jmx -l file.jtl'

https://dlcdn.apache.org//jmeter/binaries/apache-jmeter-5.6.zip

In the 'Files' catalog, I uploaded a zip file. You can download it to get other materials for repeating.

Best Regards!

Hi Mr. zheng,

We have to repeat the questions that we asked for before and we have to repeat some facts about our standards.

We do not support benchmarking by any other tools except mysqlslap and sysbench.

Next, we can not repeat what you are reporting without having the following:

* Full dump of the tables and not just DDL

* The above presumes that all INSERTs should be in the dump

* SQL benchmark queries that run

* Concurrency that you have used

We also have to repeat that one second is not a slow query, if it is at all complex or if the table is large. That could also be a problem with your MySQL and OS configuration. Hence, if you provide us with all data that we need for sysbench or mysqlslap and if we get similar or better timing, then this can  not be considered  a bug.

Can't repeat.

*

Dear Sir,

Thanks for your kind cooperation.

We learned about your standards and know that you cannot repeat the problem with the materials we provided. But we have to kindly repeat our query once again.

Why is it that performing the same operation on a lower version does not have this problem? This issue only occurs on version 8.0.34. Can you provide some possible influences?

Thanks！

Hi Mr. zheng,

Sorry, but that is not possible.

Simply, source code of MySQL is larger then 100 Mb and there could be millions of causes.