I had create one table, that Table contain Log of meters.
Name of that table is "paramgraph_ems"
This table contain 1M rows, and insertion per minute is 350 rows.
10 Min takes for inserting 3500 rows.
In that table contain one Update Triagger.
CREATE TABLE paramgraph_ems (
UserIndex smallint(5) unsigned DEFAULT NULL,
SlaveIndex tinyint(3) unsigned DEFAULT NULL,
ParameterName varchar(50) DEFAULT NULL,
DataTypeIndex tinyint(3) unsigned DEFAULT NULL,
ParameterType tinyint(3) unsigned DEFAULT NULL,
ParameterAdd smallint(5) unsigned DEFAULT NULL,
ParamValue varchar(100) DEFAULT NULL,
OrgID smallint(5) unsigned DEFAULT NULL,
RgnID smallint(5) unsigned DEFAULT NULL,
SiteID smallint(5) unsigned DEFAULT NULL,
DeviceID smallint(5) unsigned DEFAULT NULL,
UnitOnOff tinyint(1) DEFAULT '0',
Category tinyint(3) DEFAULT NULL,
ParameterID smallint(5) unsigned DEFAULT NULL,
LastUpdate datetime DEFAULT NULL,
KEY UsrId (UserIndex),
KEY DTId (DataTypeIndex),
KEY PrmType (ParameterType),
KEY OrgID (OrgID),
KEY RgnID (RgnID),
KEY SiteID (SiteID),
KEY DeviceID (DeviceID),
KEY Cat (Category),
KEY Pid (ParameterID),
KEY pname (ParameterName),
KEY lastupdt (LastUpdate),
CONSTRAINT paramgraph_ems_ibfk_1 FOREIGN KEY (UserIndex) REFERENCES userlogin_ems (UserID) ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT paramgraph_ems_ibfk_10 FOREIGN KEY (ParameterID) REFERENCES modelparam_ems (ParameterID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT paramgraph_ems_ibfk_2 FOREIGN KEY (DataTypeIndex) REFERENCES datatype_ems (DataTypeID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT paramgraph_ems_ibfk_3 FOREIGN KEY (ParameterType) REFERENCES vartype_ems (VarTypeID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT paramgraph_ems_ibfk_4 FOREIGN KEY (OrgID) REFERENCES organizationdet_ems (OrganizationID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT paramgraph_ems_ibfk_5 FOREIGN KEY (RgnID) REFERENCES region_ems (RegionID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT paramgraph_ems_ibfk_6 FOREIGN KEY (SiteID) REFERENCES site_ems (SiteID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT paramgraph_ems_ibfk_7 FOREIGN KEY (DeviceID) REFERENCES device_ems (DeviceId) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT paramgraph_ems_ibfk_9 FOREIGN KEY (Category) REFERENCES category_ems (categoryid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And Update Trigger is,
CREATE DEFINER=`root`@`localhost` TRIGGER `trialdb`.`UpdateTri`
AFTER UPDATE ON `trialdb`.`paramgraph_ems`
FOR EACH ROW
BEGIN
DECLARE sp,i,j,atyp int default 0;
WHILE i < (SELECT count(alarm.AlarmType) FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID
) do
SELECT alarm.AlarmType into atyp FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1;
SELECT alarm.AlarmSetPoint into sp FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1;
if(atyp = 0) then
if(New.ParamValue > sp)then
UPDATE alarmmanagement_ems
SET AlarmStatus=1
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
else
UPDATE alarmmanagement_ems
SET AlarmStatus=0,
IsMailSend=0
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmInActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
END IF;
elseif(atyp =1)then
if(New.ParamValue < sp)then
UPDATE alarmmanagement_ems
SET AlarmStatus=1
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
else
UPDATE alarmmanagement_ems
SET AlarmStatus=0,
IsMailSend=0
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmInActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
END IF;
elseif(atyp =2)then
if(New.ParamValue <= sp)then
UPDATE alarmmanagement_ems
SET AlarmStatus=1
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
else
UPDATE alarmmanagement_ems
SET AlarmStatus=0,
IsMailSend=0
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmInActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
END IF;
elseif(atyp =3)then
if(New.ParamValue >= sp)then
UPDATE alarmmanagement_ems
SET AlarmStatus=1
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
else
UPDATE alarmmanagement_ems
SET AlarmStatus=0,
IsMailSend=0
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmInActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
END IF;
elseif(atyp =4)then
if(New.ParamValue = sp)then
UPDATE alarmmanagement_ems
SET AlarmStatus=1
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
else
UPDATE alarmmanagement_ems
SET AlarmStatus=0,
IsMailSend=0
WHERE
AlarmId = (select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x);
call AlarmInActDt((select * from (SELECT alarm.AlarmId FROM alarmmanagement_ems as alarm,paramgraph_ems as param
where
param.ParameterID = alarm.ParameterID
&& param.DeviceID = alarm.DeviceID
&& param.ParameterID=new.ParameterID
&& param.DeviceID=new.DeviceID limit j,1) as x));
END IF;
END if;
SET i = i+1;
SET j = i;
END WHILE;
END
I am not able to find out what is an exact error.
Why insertion is very slow.
Best Answer
You have an InnoDB without an explicit
PRIMARY KEY. This is a no-no.On each
INSERT, 12 BTrees must be updated -- 1 for the data, 11 more for the indexes.What is the value of
innodb_buffer_pool_size? It should be about 70% of available RAM. If it is sitting at some low value, the lack of caching is killing performance.SELECT count(alarm.AlarmType) FROM alarmmanagement_emsdoes not look like a cheap thing to do inside each INSERT. How many rows inalarmmanagement_ems? Does it have a compositeINDEX(ParameterID, DeviceID)? (Order of columns not important in this case.)Every field in `paramgraph_ems is NULLable. Isn't anything required in this table?
You have
limit j,1without anORDER BY. That is asking for big trouble. SQL can return the rows in any order it feels like, thereby defeating the intent of theLIMIT.(select * from (SELECT-- Why this 2-level structure? It unnecessarily requires an extra temporary table. (Temp tables take time.)How slow is
AlarmInActDt?That's one huge TRIGGER; I'm not surprised it is slow.
iandj; can they be done via the 'set' nature of SQL?There is probably a lot more to comment on, but I will stop at that.