MySQL Forums
Forum List  »  Performance

Re: MYSQL Update performance
Posted by: Peter Brawley
Date: May 01, 2017 12:58PM

14 mins is better than 9 or 15 hrs, and it suggests the Update is doing FK busywork, so the update will benefit from disablings FKs for the transaaction.

But 14 minutes is still slow. Is caching on? And vmstat syays there's been considerable swapping, which kills performance, but innodb_buffer_pool_size is huge, so I'm unclear what's going on. Can you fetch the buffer pool hit rate from show engine innodb status? And run this as a check ...

select 
  concat(round(sum(table_rows)/1000000,2),'m') rows,
  concat(round(sum(data_length)/(1024*1024*1024),2),'g') data,
  concat(round(sum(index_length)/(1024*1024*1024),2),'g') idx,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'g') total_size,
  round(sum(index_length)/sum(data_length),2) idxfrac
from information_schema.tables
where table_name in( 'claim_provider_association','provider_attributes_lookup' );

Do you have a test server where you can test the effect of optimising the tables?



Edited 1 time(s). Last edit at 05/01/2017 01:05PM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
1433
April 24, 2017 02:11PM
725
April 24, 2017 04:05PM
865
May 01, 2017 08:50AM
768
May 01, 2017 10:18AM
814
May 01, 2017 10:45AM
Re: MYSQL Update performance
752
May 01, 2017 12:58PM
1527
May 01, 2017 01:11PM
674
May 01, 2017 01:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.