db-show-lock status causes web-loading...

Document ID : KB000007116
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Primary symptom here was that 50% or more each day were being seen 'locked' as a query status when checking

         > mysql.exe -e "show processlist" | findstr Query

This status isn't unheard of but this is typically >= 900s at a time, and always tended to begin with rollup-select statements to summarize values from avail_rate into avail_ltd.

Performance issues were raised.

Environment:
Windows, NV 7.0, 7.1 SP2, 7.1 SP3
Cause:

Looking into the cause of the locked tables - we had expected to find in avail_ltd 1-row-per-device-per-hour.

However there were some unidentified process-error in their instance of avail-rollup generation and it ends

up inserting many-rows-per-device-per-hour (effectively repeating calls to redo rollups for past hours).

Resolution:

The following 'code' can be inserted into the poller-db to trim out this hourly-_ltd duplicates as they are inserted.


drop TRIGGER if exists avail_ltd_insert ;
drop TRIGGER if exists avail_ltd_cleanup ;
drop table if exists avail_ltd_latest;
replace into properties (property_set_id, property_type, property_name, property_value)
(select 1, 3, 'AvailCleanup', max(tstamp) from avail_ltd );
delimiter $$

CREATE TRIGGER avail_ltd_insert
BEFORE insert ON avail_ltd
FOR EACH ROW
BEGIN
IF new.tstamp < (select property_value from properties where property_set_id = 1 and property_name = 'AvailCleanup' ) THEN
SET NEW.tstamp = 0;
END IF;
END$$

CREATE TRIGGER avail_ltd_cleanup
AFTER insert ON avail_ltd_current
FOR EACH ROW
BEGIN
replace into properties (property_set_id, property_type, property_name, property_value)
(select 1, 3, 'AvailCleanup', max(tstamp) from avail_ltd );

delete from avail_ltd where tstamp=0;

END$$
delimiter ;

 

Additional Information:

Result have been verified over the 2-3 weeks as working.