Index on eventtime of alarm journal table?

Ignition
1

We are getting these warnings in our gateway log. I believe this is part of the automatic pruning for our catchall alarm journal.

Slow query "DELETE FROM alarm_event_data WHERE "id" IN ( SELECT "id" FROM alarm_events WHERE "eventtime"<=?)" took 34 seconds

image
image1186×190 13.3 KB

The alarm_events table has ~11 million rows (1-year alarm retention). Queries executed against different tables in the same database take milliseconds, so I’m inclined to think that problems are related to this table specifically.

I don’t think I can change these queries, so, in an attempt to fix them, I added an index to eventtime on our development gateway/database. This seems to have resolved the slow query errors and hasn’t introduced any noticeable performance impacts.

Some questions before I add the same index to our production database:

  1. Can anyone think of a reason I shouldn’t?
  2. Are there performance or maintainability concerns I have failed to consider?
  3. If this solution has no drawbacks is there a reason why it’s not default behavior when the table is automatically created?

Thanks for your thoughts on the subject.

2

TL/DR: Adding the index is the Right Answer™.

  1. Slight increase in INSERT overhead.
  2. Tag History queries in general should benefit too, not just pruning.
  3. No clue. It should be default behavior.
3 Likes
3

Thanks for the confirmation, @pturmel. I’ll add the index during the next production maintenance window.

4

Like this for MySQL/MariaDB?

ALTER TABLE alarm_events ADD INDEX IF NOT EXISTS (eventtime);

The majority of my slow queries status page is alarm_event related, so I thought I'd ask the forum and found this thread. :100:

5

Not sure that's the correct syntax. Doesn't the index need a name?

{ But yes, add an index for eventtime. }

1 Like
6

Looks like MariaDB will auto-name it if you don't give an explicit one. I just tried that command on a test server and SHOW CREATE TABLE is now giving me this, which looks about like I'd expect:

CREATE TABLE `alarm_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eventid` varchar(255) DEFAULT NULL,
  `source` varchar(255) DEFAULT NULL,
  `displaypath` varchar(255) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `eventtype` int(11) DEFAULT NULL,
  `eventflags` int(11) DEFAULT NULL,
  `eventtime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `eventtime` (`eventtime`)
) ENGINE=InnoDB AUTO_INCREMENT=71205 DEFAULT CHARSET=utf8
1 Like