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.