Details
-
Bug
-
Resolution: Unresolved
-
Major
-
None
-
None
-
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.