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

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.
LVL 2
Apurva BhandariLinux/Cloud/DB/DevOpsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
arnoldCommented:
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 EngineerCommented:
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.
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Apurva BhandariLinux/Cloud/DB/DevOpsAuthor 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/DevOpsAuthor 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.
arnoldCommented:
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 EngineerCommented:
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.
Apurva BhandariLinux/Cloud/DB/DevOpsAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.