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

VLAN views all slow due to full table scans

    XMLWordPrintable

Details

    • Bug
    • Resolution: Unresolved
    • Major
    • None
    • None
    • Web Interface
    • None

    Description

      In CE-26.1.14545 (we don't have the L2 stuff turned in our enterprise instance, can't check there...) all the pages under Network–>VLANs are very slow, even for moderate data sets.

      According to the SQL Profiler information built-in to Observium (neat feature, BTW!), the problem query is somehow only the final one, which counts mac_addresses, and takes 6.959sec.

       

      MySQL EXPLAIN ANALYSE on this query:

      explain analyze
      SELECT
        `vlan_id`,
        COUNT(
          DISTINCT(`mac_address`)
        ) AS `count`
      FROM
        `vlans_fdb`
      WHERE
        `deleted` != 1
        AND (
          (`device_id` IS NOT NULL)
          AND (
            `port_id` NOT IN (
              24,
              3921,
              3922,
              3924,
              3925,
              3926,
              3927,
              3928,
              3929,
              3930,
              3931,
              3932,
              3933,
              3934,
              3935,
              3936,
              3937,
              3938,
              3939,
              3940,
              3941,
              3942,
              3943,
              3944,
              3945,
              3946,
              3947,
              3948,
              3949,
              3950,
              3951,
              3952,
              3953,
              3954,
              4277,
              4278,
              4279,
              4280,
              4281,
              4282,
              4283,
              4284,
              4285,
              4286,
              4287,
              4288,
              4289,
              4290,
              4291,
              4292,
              4293,
              4294,
              4295,
              4296,
              4297,
              4298,
              4299,
              4300,
              4301,
              4302,
              4303,
              4304,
              4305,
              4306,
              4307,
              4308,
              4309,
              4310,
              4311,
              4312,
              4313,
              4314,
              4315,
              4316,
              4317,
              4318,
              4319,
              4320,
              4256,
              4257,
              4258,
              4259,
              4260,
              4261,
              4262,
              4263,
              4264,
              4265,
              4266,
              4267,
              4268,
              4269,
              4270,
              4271,
              4272,
              4273,
              4274,
              4275,
              4276,
              3181,
              3191,
              3218,
              3227,
              3228,
              3229,
              3230,
              3231,
              3232,
              3233,
              3234,
              3235,
              3236,
              3237,
              3238,
              3239,
              3240,
              3241,
              3242,
              3243,
              3244,
              3245,
              3246,
              3247,
              3248,
              3249,
              3250,
              3357,
              3360,
              3386,
              3387,
              3388,
              3429,
              3430,
              3486,
              3487,
              3488,
              3489,
              3502,
              3512,
              3513,
              3514,
              3515,
              3517,
              3518,
              3519,
              3520,
              3521,
              3522,
              3523,
              3524,
              3525,
              4031,
              4058,
              4059,
              4060,
              4061,
              4062,
              4063,
              4064,
              4065,
              4066,
              4067
            )
            AND `port_id` IS NOT NULL
          )
        )
      GROUP BY
        `vlan_id` 

      produces this output:

      -> Group aggregate: count(distinct vlans_fdb.mac_address)  (cost=153794 rows=1036) (actual time=156..9596 rows=740 loops=1)
          -> Filter: ((vlans_fdb.deleted <> 1) and (vlans_fdb.port_id not in (24,3921,3922,3924,3925,3926,3927,3928,3929,3930,3931,3932,3933,3934,3935,3936,3937,3938,3939,3940,3941,3942,3943,3944,3945,3946,3947,3948,3949,3950,3951,3952,3953,3954,4277,4278,4279,4280,4281,4282,4283,4284,4285,4286,4287,4288,4289,4290,4291,4292,4293,4294,4295,4296,4297,4298,4299,4300,4301,4302,4303,4304,4305,4306,4307,4308,4309,4310,4311,4312,4313,4314,4315,4316,4317,4318,4319,4320,4256,4257,4258,4259,4260,4261,4262,4263,4264,4265,4266,4267,4268,4269,4270,4271,4272,4273,4274,4275,4276,3181,3191,3218,3227,3228,3229,3230,3231,3232,3233,3234,3235,3236,3237,3238,3239,3240,3241,3242,3243,3244,3245,3246,3247,3248,3249,3250,3357,3360,3386,3387,3388,3429,3430,3486,3487,3488,3489,3502,3512,3513,3514,3515,3517,3518,3519,3520,3521,3522,3523,3524,3525,4031,4058,4059,4060,4061,4062,4063,4064,4065,4066,4067)) and (vlans_fdb.port_id is not null))  (cost=136525 rows=172691) (actual time=6.49..9558 rows=131735 loops=1)
              -> Index scan on vlans_fdb using vlan_mac_lookup  (cost=136525 rows=1.29e+6) (actual time=6.48..9455 rows=1.28e+6 loops=1) 

      I've tried adding different indexes, but MySQL is choosing to not use them.

      My vlans_fdb table contains 1,284,337 rows, and count(distinct mac_address) gives me 113,987 rows.  Each mac_address appears an average of 11.2676 times.

      I'm not sure how I would make this page load faster, tbh... hoping you guys have ideas.

      Attachments

        Activity

          People

            adama Adam Armstrong
            athompson@merlin.mb.ca Adam Thompson
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: