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 ...

  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

Written By
April 24, 2017 02:11PM
April 24, 2017 04:05PM
May 01, 2017 08:50AM
May 01, 2017 10:45AM
Re: MYSQL Update performance
May 01, 2017 12:58PM
May 01, 2017 01:11PM
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.