We help IT Professionals succeed at work.

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

155 Views
Last Modified: 2018-12-02
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

David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

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.
CERTIFIED EXPERT
Distinguished Expert 2019

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
CERTIFIED EXPERT
Distinguished Expert 2019

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.
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.
CERTIFIED EXPERT
Distinguished Expert 2019

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
CERTIFIED EXPERT
Distinguished Expert 2019

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 one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.