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

book

Article ID: 7116

calendar_today

Updated On:

Products

CA NetVoyant (NetQoS / NV)

Issue/Introduction

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.

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

Environment

Windows, NV 7.0, 7.1 SP2, 7.1 SP3

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.