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
237
April 24, 2017 02:11PM
90
April 24, 2017 04:05PM
92
May 01, 2017 08:50AM
102
May 01, 2017 10:18AM
92
May 01, 2017 10:45AM
Re: MYSQL Update performance
90
May 01, 2017 12:58PM
189
May 01, 2017 01:11PM
128
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.