Facing issue with MySQL 5.7.22 and above (5.7.24) for inserting data

Apurva Bhandari
Apurva Bhandari used Ask the Experts™
on
Facing issue with MySQL 5.7.22 and (5.7.24) above
Hello Experts,
Facing issue while inserting data to MySQL 5.7.24 with OS Ubuntu 18.04. It is taking to long time to insert data. 5k-6k/min insert rate
As i checked with MyQL 5.7.19 with Ubuntu 16 it works fine with insert query. 100k-400k/min insert rate

For the above i have googled but no luck someone reported this issue with mysql forum but the only thing for performance that i got is configure the parameter in my.cnf

[mysqld]
skip-log-bin
#log_bin                        = /var/log/mysql/mysql-bin.log
#expire_logs_days       = 10
#max_binlog_size   = 100M

Open in new window


By this it is now increased to 20k-22k/min but this is not up to mark if we look at the performance of the MySQL 5.7.19 with Ubuntu 16

Can anybody help me out to tune mysql parameter or tuning at MySQL level or may be at Ubuntu 18.04 level.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
1) Run mysqltuner + see if anything stands out to tune.

2) Make sure your INSERT statements are wrapped inside a transaction.

3) Make sure your storage engine is InnoDB, rather than MyISAM.

4) Look at your indexes. If you have large complex indexes + you do bulk loads of data repeatedly, you may have to drop your indexes, do your bulk insert, then create your indexes afterward. Using FTS indexes with massively long/wide columns of data can really slow down bulk inserts.

5) Enable slow query logging + see if statements show up as slow.

6) After you do #1, make sure your system isn't swapping (top will tell you), as swapping will kill your performance.
Distinguished Expert 2017

Commented:
Please post an example of the insert, along with show create table into which these inserts are going.
Your HW number of devices making the storage.
Physical, virtual machine? Resource allocation memory, CPU.

Delays are often related to triggers, index, when the storage space is the bottleneck.
nociSoftware Engineer
Distinguished Expert 2018

Commented:
I have seen cases of huge interference when the same filesystem was shared between mysql and some other tooling (MQ) both doing sync's to save transactions. The performance of both suffered (and the throughput through the complete system as consequence) due to this. Giving each tools it's "own" "file system" (= "disk" not just directory) can help.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Apurva BhandariLinux/Cloud/DB/DevOps

Author

Commented:
@David Favor and @arnold,
Step 1 performed by
wget http://mysqltuner.com/mysqltuner.pl

but no luck still we are getting same response for insert
 Storage engine is innoDB

About performance 16core CPU 64GB RAM 8GB swap. This is all dedicated with respect to VM. top or free -m will show the utilization max 5GB. HDD 500GB (SSD) (RAM, CPU, HDD --> dedicated and not shared on Vsphere 6.7)

There is no issue related to insert query as I have performed same on Ubuntu 16 with MySQL 5.7.19   with same configuration without any tuning and it Works perfectly as I mentioned in question insert rate around 100k-400k/min where as with Ubuntu 18.04 with MySQL 5.7.24 insert rate will 20k-22k/min.

I have referred the link
link1    
link2

@noci, did you find any solution on this?

FYI, I have tested same scenario on MariaDB 10.3.9 and showing same issue. Ubuntu 18.04 will support MySQL 5.7.22 and above releases only.
Apurva BhandariLinux/Cloud/DB/DevOps

Author

Commented:
Hello Experts,
I had found the issue. This is related to Ubuntu 18.04 as I got the good performance for MySQL 5.7.24 with Ubuntu 16. So issue with Ubuntu 18.04 LTS. Can Anybody have any idea about this? I have tried with multiple environment with Ubuntu 18.04 and MySQL 5.7.24 and setting  up ulimits value but no luck.
Distinguished Expert 2017

Commented:
Check resource' prioritization of the MySQL VM compared to others, give this obey higher priority in vsphere.


How many VMs hit the same storage resource.
nociSoftware Engineer
Distinguished Expert 2018

Commented:
1) What software is running on this server   (mysql + syslog + ...)
2) How could it interact (esp. with 'sync'-ing io to storage (ie. doing write transaction, many log systems also use sync...)

The solution was to create several "devices" for each of the interacting tools and mount them in the right spot...
(The devices were in a SAN, which has battery backed caches so once it was there the transactions are safe,
it just needed decoupling on the linux system, so a sync for logging didn't need to wait for a halfway transaction underway from mysql).
And the IO didn;t need to be done twice because the blocks were updated by mysql as part of the transaction accross this sync.)

/var/log              to it's own device
/var/lib/mysql  to it's own device

etc.
Linux/Cloud/DB/DevOps
Commented:
This is the bug related to restricted usage of CPU in Ubuntu 18.04. I have searched for the change log from 16 to 18 it is found that they had fixed number of bugs related to CPU in Ubuntu 18.04

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial