Uploaded image for project: 'Observium'
  1. Observium
  2. OBS-3186

Index to speed up eventlog

Details

    Description

      Here is an index to speed up eventlog in some cases

      I had one query taking 22 minutes!! Now taking 4 seconds - but this is an extreme example.

      Usually they went from around 10-20 seconds to around 1s

      (yeah we have many events on ports)

       

      ALTER TABLE `observium`.`eventlog` ADD INDEX `entity` (`entity_type`, `entity_id`);

       

      Happy new year!

      Attachments

        Activity

          [OBS-3186] Index to speed up eventlog
          kentbjoh Kent Johannessen added a comment -

          kentbjoh Kent Johannessen added a comment -
          landy Mike Stupalov added a comment -

          Just added in r10222.

          landy Mike Stupalov added a comment - Just added in r10222.
          kentbjoh Kent Johannessen added a comment - - edited

          Hi Mike,

          Queries like this; SELECT COUNT(*) FROM `eventlog` WHERE 1 AND `entity_id` IN ('3675','3686','3687','5019','6855','7062','7241','7242','9477','9479','11156','11309','11339','11354','11356','11416','11535','11608','11674','11680','11707','11708','11709','11850','11868','11886','12051','12065','12471','12475','13109','13110','13111','13188','13349','13695','13738','13752','13930','13990','14945','14950','14952','14956','15191','15934','19312','19335','167852','308917','3106688','3106692','12288668','12288988','12288994','12290347','12290411','12295347','12297520','12297613','12299032','12309677','12327140') AND `entity_type` = 'port' AND (( `device_id` NOT IN (407,898,1418,1155,1216,1684,503,1141,1379,1064,1571,1572,643,1089,1090,1092,1093,1094,1096,1098,1099,1100,1101,1174,1807,1808,1436,1071,151,1678,876,874,873,1140,1298) AND `device_id` IS NOT NULL))

          Also imagine this with several thousand more ports.

          kentbjoh Kent Johannessen added a comment - - edited Hi Mike, Queries like this; SELECT COUNT(*) FROM `eventlog` WHERE 1 AND `entity_id` IN ('3675','3686','3687','5019','6855','7062','7241','7242','9477','9479','11156','11309','11339','11354','11356','11416','11535','11608','11674','11680','11707','11708','11709','11850','11868','11886','12051','12065','12471','12475','13109','13110','13111','13188','13349','13695','13738','13752','13930','13990','14945','14950','14952','14956','15191','15934','19312','19335','167852','308917','3106688','3106692','12288668','12288988','12288994','12290347','12290411','12295347','12297520','12297613','12299032','12309677','12327140') AND `entity_type` = 'port' AND (( `device_id` NOT IN (407,898,1418,1155,1216,1684,503,1141,1379,1064,1571,1572,643,1089,1090,1092,1093,1094,1096,1098,1099,1100,1101,1174,1807,1808,1436,1071,151,1678,876,874,873,1140,1298) AND `device_id` IS NOT NULL)) Also imagine this with several thousand more ports.
          landy Mike Stupalov added a comment -

          Thanks Kent!

          But, please, show examples of what queries (cases) do you have such long-term requests?

          landy Mike Stupalov added a comment - Thanks Kent! But, please, show examples of what queries (cases) do you have such long-term requests?

          People

            landy Mike Stupalov
            kentbjoh Kent Johannessen
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: