Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

When editing a data query, graph template picker shows poor performance #2519

Closed
sysres-dev opened this issue Mar 14, 2019 · 6 comments
Closed

Comments

@sysres-dev
Copy link
Contributor

Bug description
With a pretty large Cacti installation (nearly 27k devices), the SQL query used to populate the Graph Template picker when editing a data query is slowing the page down.

I played the request from global_form.php in the MariaDB Shell and added some stats.

MariaDB [cacti]> select count(*) from data_template_data\G
count(*): 507354
MariaDB [cacti]> select count(*) from data_template_rrd\G
count(*): 1217536
MariaDB [cacti]> select count(*) from graph_templates_item\G
count(*): 6987369
MariaDB [cacti]> select count(*) from graph_templates\G
count(*): 1457
MariaDB [cacti]> select count(*) from graph_templates_graph\G
count(*): 459594
MariaDB [cacti]> explain SELECT DISTINCT gt.id, gt.name FROM graph_templates AS gt INNER JOIN graph_templates_graph AS gtg ON gt.id = gtg.graph_template_id INNER JOIN graph_templates_item AS gti ON gtg.graph_template_id=gti.graph_template_id INNER JOIN data_template_rrd AS dtr ON gti.task_item_id=dtr.id INNER JOIN data_template_data AS dtd ON dtd.data_template_id=dtr.data_template_id AND dtd.local_data_id = 0 WHERE gtg.local_graph_id=0 AND dtr.local_data_id = 0 AND dtd.local_data_id = 0 AND dtd.data_input_id in (2,11,12) ORDER BY gt.name;
+------+-------------+-------+--------+-------------------------------------------------------------------+----------------------------+---------+-----------------------------+------+----------------------------------------------+
| id   | select_type | table | type   | possible_keys                                                     | key                        | key_len | ref                         | rows | Extra                                        |
+------+-------------+-------+--------+-------------------------------------------------------------------+----------------------------+---------+-----------------------------+------+----------------------------------------------+
|    1 | SIMPLE      | dtr   | ref    | PRIMARY,duplicate_dsname_contraint,local_data_id,data_template_id | duplicate_dsname_contraint | 3       | const                       | 1931 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | gti   | ref    | graph_template_id,task_item_id                                    | task_item_id               | 3       | cacti.dtr.id                |    3 |                                              |
|    1 | SIMPLE      | gt    | eq_ref | PRIMARY                                                           | PRIMARY                    | 3       | cacti.gti.graph_template_id |    1 |                                              |
|    1 | SIMPLE      | gtg   | ref    | local_graph_id,graph_template_id                                  | graph_template_id          | 3       | cacti.gti.graph_template_id |  160 | Using where; Distinct                        |
|    1 | SIMPLE      | dtd   | ref    | local_data_id,data_template_id,data_input_id                      | data_template_id           | 3       | cacti.dtr.data_template_id  |  185 | Using where; Distinct                        |
+------+-------------+-------+--------+-------------------------------------------------------------------+----------------------------+---------+-----------------------------+------+----------------------------------------------+
MariaDB [cacti]> SELECT DISTINCT gt.id, gt.name FROM graph_templates AS gt INNER JOIN graph_templates_graph AS gtg ON gt.id = gtg.graph_template_id INNER JOIN graph_templates_item AS gti ON gtg.graph_template_id=gti.graph_template_id
INNER JOIN data_template_rrd AS dtr ON gti.task_item_id=dtr.id INNER JOIN data_template_data AS dtd ON dtd.data_template_id=dtr.data_template_id AND dtd.local_data_id = 0 WHERE gtg.local_graph_id=0 AND dtr.local_data_id = 0 AND dtd.lo
cal_data_id = 0 AND dtd.data_input_id in (2,11,12) ORDER BY gt.name;
+------+------------------------------------------------------------------------------+
| id   | name                                                                         |
+------+------------------------------------------------------------------------------+
[...]
683 rows in set (19.64 sec)

That's almost 20 seconds to get 683 rows.

I played with the request to try and optimize it, I will make a PR shortly with a proposal for a faster request.

Expected behavior
A faster page load time :)

Additional context
Here are some stats about my Cacti installation:

  • Cacti Version | 1.1.38
  • Cacti OS | unix
  • RSA Fingerprint | 22:9a:ef:ad:d8:de:1d:e7:95:7d:89:98:bd:4f:b9:df
  • NET-SNMP Version | NET-SNMP version: 5.7.2
  • RRDtool Version | RRDtool 1.4.x
  • Devices | 26897
  • Graphs | 458451
  • Data Sources:
    • Script/Command: 16597
    • SNMP Get: 76962
    • SNMP Query: 291955
    • Script Query: 62267
    • Script Server: 26697
    • Script Query - Script Server: 31729
    • Total: 506207
@netniV
Copy link
Member

netniV commented Mar 14, 2019

You can use the mysql EXPLAIN command to see why there is such a performance difference. Also, as I will be about to comment on the PR, the SQL style is using the older implicit join (using = in the where statement) rather than the newer, more clearly defined, INNER JOIN syntax as proposed in our Coding Standards.

See https://github.com/Cacti/documentation/blob/develop/Standards-SQL.md. We are updating our documentation so that page may be improved on in the future but it's a good guide for now along with our https://github.com/Cacti/documentation/blob/develop/Standards-Code-Formatting.md.

@netniV
Copy link
Member

netniV commented Mar 15, 2019

So, in my tests, the SQL_NO_CACHE versions of the queries were:

System Original Updated Count
1 0.08 0.02 26
2 0.04 0.01 17
3 0.11 0.10 25
4 0.95 7.57 68

That's a considerable increase when there were more rows. The following are the two queries I used:

SELECT SQL_NO_CACHE DISTINCT gt.id, gt.name
FROM graph_templates AS gt
INNER JOIN graph_templates_graph AS gtg
ON gt.id = gtg.graph_template_id 
INNER JOIN graph_templates_item AS gti 
ON gtg.graph_template_id=gti.graph_template_id 
INNER JOIN data_template_rrd AS dtr 
ON gti.task_item_id=dtr.id 
INNER JOIN data_template_data AS dtd 
ON dtd.data_template_id=dtr.data_template_id 
AND dtd.local_data_id = 0 
WHERE gtg.local_graph_id=0 
AND dtr.local_data_id = 0 
AND dtd.local_data_id = 0 
AND dtd.data_input_id in (2,11,12) 
ORDER BY gt.name;

SELECT SQL_NO_CACHE DISTINCT gt.id, gt.name 
FROM graph_templates AS gt 
INNER JOIN graph_templates_item AS gti 
ON gt.id=gti.graph_template_id 
WHERE gti.id IN (
   SELECT DISTINCT gti.id 
   FROM graph_templates_item AS gti
   INNER JOIN data_template_rrd AS dtr 
   ON gti.task_item_id=dtr.id 
   AND dtr.local_data_id = 0 
   INNER JOIN data_template_data AS dtd 
   ON dtd.data_template_id=dtr.data_template_id 
   AND dtd.local_data_id = 0 
   AND dtd.data_input_id in (2,11,12) 
   WHERE gti.local_graph_id = 0
) ORDER BY gt.name;

@netniV
Copy link
Member

netniV commented Mar 15, 2019

Just to be sure, I ran the system 4 test again, 0.42 vs 6.19. Could use a few people to try the queries out and see what they get back.

Maybe @jpobeda, @jdcoats, @camerabob, @eschoeller, or @thurban ? Just need the total rows and time it takes for each query.

@cigamit
Copy link
Member

cigamit commented Mar 15, 2019

I have also asked the user to analyze his/her tables. I'm asking for additional information as well. This is a pretty big system. I would like to get to the bottom of it. Sometimes analyzing the tables helps. This could me be MariaDB/MySQL version specific issue as well. In my test database with over 400k graphs, the queries were almost identical in run time of ~1.08-1.21 seconds. I was able to also carve off another 0.02 seconds from the revised query above through additional modification.

This might also be an issue where the tables are so large, that any derived tables are not being stored in memory. So, it could simply be a tuning issue. Just not to certain yet.

cigamit added a commit that referenced this issue Mar 15, 2019
Performance of graph template picker when editing a data query
@cigamit
Copy link
Member

cigamit commented Mar 15, 2019

I have just made a change. Please test this change. I believe you should be quite pleased with the results.

@netniV
Copy link
Member

netniV commented Mar 15, 2019

@cigamit's revised query seems to take a LOT less time than the original proposal.

System Original Proposed Revised Count
1 0.14 0.01 0.19 26
2 0.01 0.01 0.01 17
3 0.04 0.02 0.03 25
4 0.46 5.81 0.03 68

Using the following SQL I compared system 1 to system 4:

show create table graph_templates\G
show create table graph_templates_item\G
show create table data_template_rrd\G
show create table data_template_data\G

The following difference has the slower database in red and the only real difference is in the number of records

index 67a401fc..42a41d9f 100644
--- a/tmp/1
+++ b/tmp/2
@@ -1,4 +1,4 @@
-Database: 4
+Database: 1

 Create Table: CREATE TABLE `graph_templates` (
   `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
@@ -6,9 +6,9 @@ Create Table: CREATE TABLE `graph_templates` (
   `name` char(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
   `multiple` char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
-  KEY `multiple_name` (`multiple`,`name`(171)),
-  KEY `name` (`name`(171))
-) ENGINE=InnoDB AUTO_INCREMENT=154 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains each graph template name.'
+  KEY `name` (`name`),
+  KEY `multiple_name` (`multiple`,`name`(171))
+) ENGINE=InnoDB AUTO_INCREMENT=258 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains each graph template name.'
 1 row in set (0.00 sec)

 Create Table: CREATE TABLE `graph_templates_item` (
@@ -36,10 +36,10 @@ Create Table: CREATE TABLE `graph_templates_item` (
   `sequence` mediumint(8) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   KEY `graph_template_id` (`graph_template_id`),
-  KEY `local_graph_id_sequence` (`local_graph_id`,`sequence`),
   KEY `task_item_id` (`task_item_id`),
+  KEY `local_graph_id_sequence` (`local_graph_id`,`sequence`),
   KEY `lgi_gti` (`local_graph_id`,`graph_template_id`)
-) ENGINE=InnoDB AUTO_INCREMENT=1009029 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores the actual graph item data.'
+) ENGINE=InnoDB AUTO_INCREMENT=130807 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores the actual graph item data.'
 1 row in set (0.00 sec)

 Create Table: CREATE TABLE `data_template_rrd` (
@@ -66,7 +66,7 @@ Create Table: CREATE TABLE `data_template_rrd` (
   KEY `data_template_id` (`data_template_id`),
   KEY `local_data_template_rrd_id` (`local_data_template_rrd_id`),
   KEY `data_source_name` (`data_source_name`)
-) ENGINE=InnoDB AUTO_INCREMENT=162950 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+) ENGINE=InnoDB AUTO_INCREMENT=20304 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 1 row in set (0.00 sec)

 Create Table: CREATE TABLE `data_template_data` (
@@ -78,7 +78,7 @@ Create Table: CREATE TABLE `data_template_data` (
   `t_name` char(2) COLLATE utf8mb4_unicode_ci DEFAULT '',
   `name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
   `name_cache` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
-  `data_source_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
+  `data_source_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `t_active` char(2) COLLATE utf8mb4_unicode_ci DEFAULT '',
   `active` char(2) COLLATE utf8mb4_unicode_ci DEFAULT '',
   `t_rrd_step` char(2) COLLATE utf8mb4_unicode_ci DEFAULT '',
@@ -89,6 +89,5 @@ Create Table: CREATE TABLE `data_template_data` (
   KEY `local_data_id` (`local_data_id`),
   KEY `data_template_id` (`data_template_id`),
   KEY `data_input_id` (`data_input_id`)
-) ENGINE=InnoDB AUTO_INCREMENT=35250 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+) ENGINE=InnoDB AUTO_INCREMENT=13607 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 1 row in set (0.00 sec)
-

@cigamit cigamit closed this as completed Mar 15, 2019
@netniV netniV changed the title Performance of graph template picker when editing a data query When editing a data query, graph template picker shows poor performance Mar 30, 2019
@github-actions github-actions bot locked as resolved and limited conversation to collaborators Jun 30, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants