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
128
April 24, 2017 02:11PM
54
April 24, 2017 04:05PM
44
May 01, 2017 08:50AM
43
May 01, 2017 10:45AM
Re: MYSQL Update performance
46
May 01, 2017 12:58PM
86
May 01, 2017 01:11PM


Sorry, only registered users may post in this forum.

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.