search

Cacti / plugin_thold

Thold Plugin for Cacti
GNU General Public License v2.0
68 stars 63 forks source link

Display all thold is very slow (function get_allowed_thresholds) #440

Closed xmacan closed 4 years ago

xmacan commented 4 years ago

Display tholds (management->thold) lasts abou 12-18 seconds. I have about 7000 tholds. I have problem only here, other things are faster.

I found it when I tried use this function in Intropage plugin. My plugin now displays bit different breached and trigerred tholds because I'm using queries from older thold version (maybe before get_allowed_thresholds).

Poller_intropage.php Stats before (old queries): 2020/08/12 20:41:20 - SYSTEM INTROPAGE STATS: Time:6.68 Checks:26 2020/08/12 20:51:04 - SYSTEM INTROPAGE STATS: Time:7.93 Checks:26 with get_allowed_thresholds: 2020/08/12 21:11:40 - SYSTEM INTROPAGE STATS: Time:41.72 Checks:26 2020/08/12 21:21:49 - SYSTEM INTROPAGE STATS: Time:56.78 Checks:26

Problem is with query for displaying all allowed tholds. MariaDB [cacti]> explain SELECT td.*, dtd.rrd_step, tt.name AS template_name, dtr.data_source_name AS data_source, IF(IFNULL(td.lastread,'')='',NULL,(td.lastread + 0.0)) AS flastread, IF(IFNULL(td.oldvalue,'')='',NULL,(td.oldvalue + 0.0)) AS foldvalue, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(lastchanged) AS instate, uap0.user_id AS user0, uap1.user_id AS user1, uap2.user_id AS user2 FROM thold_data AS td INNER JOIN graph_local AS gl ON gl.id=td.local_graph_id LEFT JOIN graph_templates AS gt ON gt.id=gl.graph_template_id LEFT JOIN host AS h ON h.id=gl.host_id LEFT JOIN thold_template AS tt ON tt.id=td.thold_template_id LEFT JOIN data_template_data AS dtd ON dtd.local_data_id=td.local_data_id LEFT JOIN data_template_rrd AS dtr ON dtr.id=td.data_template_rrd_id LEFT JOIN user_auth_perms AS uap0 ON (gl.id=uap0.item_id AND uap0.type=1) LEFT JOIN user_auth_perms AS uap1 ON (gl.host_id=uap1.item_id AND uap1.type=3) LEFT JOIN user_auth_perms AS uap2 ON (gl.graph_template_id=uap2.item_id AND uap2.type=4) WHERE td.thold_enabled = 'on';

7228 rows in set, 114 warnings (10.502 sec)

I don't want create own similiar function. I need only $total_rows from your function. Is it possible extend your function? function get_allowed_thresholds($sql_where = '', $order_by = 'td.name', $limit = '', &$total_rows = 0, $user = 0, $graph_id = 0, $only_count = '') ... if ( $only_count == '') { $tholds = db_fetch_assoc($tholds_sql); } else { $tholds = null; }

$total_rows = db_fetch_cell("SELECT count(*) ....

$return $tholds;

My plugin will be faster and returns correct counts. It helps me but display tholds in GUI still will be slow.

xmacan commented 4 years ago

For my function I can use limit=1 and it is faster (not ideal but faster). Is it 7000 tholds too many in GUI (page limit = 500)?

TheWitness commented 4 years ago

Run an explain on that query and post the results.

xmacan commented 4 years ago 

| id   | select_type | table | type   | possible_keys                | key           | key_len | ref                           | rows | Extra                                                               |
+------+-------------+-------+--------+------------------------------+---------------+---------+-------------------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | td    | ALL    | local_graph_id,thold_enabled | NULL          | NULL    | NULL                          | 7011 | Using where                                                         |
|    1 | SIMPLE      | gl    | eq_ref | PRIMARY                      | PRIMARY       | 3       | cacti.td.local_graph_id       |    1 | Using where                                                         |
|    1 | SIMPLE      | tt    | eq_ref | PRIMARY,id                   | PRIMARY       | 4       | cacti.td.thold_template_id    |    1 |                                                                     |
|    1 | SIMPLE      | dtd   | ref    | local_data_id                | local_data_id | 3       | cacti.td.local_data_id        |    1 | Using where                                                         |
|    1 | SIMPLE      | dtr   | eq_ref | PRIMARY                      | PRIMARY       | 3       | cacti.td.data_template_rrd_id |    1 | Using where                                                         |
|    1 | SIMPLE      | uap0  | index  | NULL                         | PRIMARY       | 7       | NULL                          |  287 | Using where; Using index; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | uap1  | index  | NULL                         | PRIMARY       | 7       | NULL                          |  287 | Using where; Using index; Using join buffer (incremental, BNL join) |
|    1 | SIMPLE      | uap2  | index  | NULL                         | PRIMARY       | 7       | NULL                          |  287 | Using where; Using index; Using join buffer (incremental, BNL join) |
+------+-------------+-------+--------+------------------------------+---------------+---------+-------------------------------+------+---------------------------------------------------------------------+```
TheWitness commented 4 years ago

Made a little edit there for readability.

TheWitness commented 4 years ago

@xmacan, can you also cacti_log() the SQL and paste it here. That'll help too. Could just be a re-organization of the sub-queries that'll speed it up. Not sure yet. I'm concerned that td is not using an index though. That seems wrong.

TheWitness commented 4 years ago

Also, there is a script called "analyze_database.php". Run that script and then check the explain again. See if anything changes.

xmacan commented 4 years ago

after analyze_database.php is result of explain the same

xmacan commented 4 years ago

My fault - in query in first post is missing HAVING part, sorry.

But still - this query is called after click to thold tab (limit 100), duration 12-15 sec.

explain SELECT td.*, dtd.rrd_step, tt.name AS template_name, dtr.data_source_name AS data_source, IF(IFNULL(td.`lastread`,'')='',NULL,(td.`lastread` + 0.0)) AS `flastread`, IF(IFNULL(td.`oldvalue`,'')='',NULL,(td.`oldvalue` + 0.0)) AS `foldvalue`, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(lastchanged) AS `instate`, uap0.user_id AS user0, uap1.user_id AS user1, uap2.user_id AS user2 FROM thold_data AS td INNER JOIN graph_local AS gl ON gl.id=td.local_graph_id LEFT JOIN graph_templates AS gt ON gt.id=gl.graph_template_id LEFT JOIN host AS h ON h.id=gl.host_id LEFT JOIN thold_template AS tt ON tt.id=td.thold_template_id LEFT JOIN data_template_data AS dtd ON dtd.local_data_id=td.local_data_id LEFT JOIN data_template_rrd AS dtr ON dtr.id=td.data_template_rrd_id LEFT JOIN user_auth_perms AS uap0 ON (gl.id=uap0.item_id AND uap0.type=1) LEFT JOIN user_auth_perms AS uap1 ON (gl.host_id=uap1.item_id AND uap1.type=3) LEFT JOIN user_auth_perms AS uap2 ON (gl.graph_template_id=uap2.item_id AND uap2.type=4) HAVING  (user0 IS NULL OR (user1 IS NULL OR user2 IS NULL)) ORDER BY `name_cache` ASC LIMIT 0,100;
+------+-------------+-------+--------+----------------+---------------+---------+-------------------------------+------+---------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys  | key           | key_len | ref                           | rows | Extra                                                               |
+------+-------------+-------+--------+----------------+---------------+---------+-------------------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | td    | ALL    | local_graph_id | NULL          | NULL    | NULL                          | 6991 | Using temporary; Using filesort                                     |
|    1 | SIMPLE      | gl    | eq_ref | PRIMARY        | PRIMARY       | 3       | cacti.td.local_graph_id       |    1 | Using where                                                         |
|    1 | SIMPLE      | tt    | eq_ref | PRIMARY,id     | PRIMARY       | 4       | cacti.td.thold_template_id    |    1 |                                                                     |
|    1 | SIMPLE      | dtd   | ref    | local_data_id  | local_data_id | 3       | cacti.td.local_data_id        |    1 | Using where                                                         |
|    1 | SIMPLE      | dtr   | eq_ref | PRIMARY        | PRIMARY       | 3       | cacti.td.data_template_rrd_id |    1 | Using where                                                         |
|    1 | SIMPLE      | uap0  | index  | NULL           | PRIMARY       | 7       | NULL                          |  287 | Using where; Using index; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | uap1  | index  | NULL           | PRIMARY       | 7       | NULL                          |  287 | Using where; Using index; Using join buffer (incremental, BNL join) |
|    1 | SIMPLE      | uap2  | index  | NULL           | PRIMARY       | 7       | NULL                          |  287 | Using where; Using index; Using join buffer (incremental, BNL join) |
+------+-------------+-------+--------+----------------+---------------+---------+-------------------------------+------+---------------------------------------------------------------------+

Second query is called from thold tab with thold_status enabled and it lasts 12-15 sec

explain SELECT td.*, dtd.rrd_step, tt.name AS template_name, dtr.data_source_name AS data_source, IF(IFNULL(td.`lastread`,'')='',NULL,(td.`lastread` + 0.0)) AS `flastread`, IF(IFNULL(td.`oldvalue`,'')='',NULL,(td.`oldvalue` + 0.0)) AS `foldvalue`, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(lastchanged) AS `instate`, uap0.user_id AS user0, uap1.user_id AS user1, uap2.user_id AS user2 FROM thold_data AS td INNER JOIN graph_local AS gl ON gl.id=td.local_graph_id LEFT JOIN graph_templates AS gt ON gt.id=gl.graph_template_id LEFT JOIN host AS h ON h.id=gl.host_id LEFT JOIN thold_template AS tt ON tt.id=td.thold_template_id LEFT JOIN data_template_data AS dtd ON dtd.local_data_id=td.local_data_id LEFT JOIN data_template_rrd AS dtr ON dtr.id=td.data_template_rrd_id LEFT JOIN user_auth_perms AS uap0 ON (gl.id=uap0.item_id AND uap0.type=1) LEFT JOIN user_auth_perms AS uap1 ON (gl.host_id=uap1.item_id AND uap1.type=3) LEFT JOIN user_auth_perms AS uap2 ON (gl.graph_template_id=uap2.item_id AND uap2.type=4) WHERE **(td.thold_enabled = 'on')** HAVING  (user0 IS NULL OR (user1 IS NULL OR user2 IS NULL)) ORDER BY `name_cache` ASC LIMIT 0,100;
+------+-------------+-------+--------+------------------------------+---------------+---------+-------------------------------+------+---------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys                | key           | key_len | ref                           | rows | Extra                                                               |
+------+-------------+-------+--------+------------------------------+---------------+---------+-------------------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | td    | range  | local_graph_id,thold_enabled | thold_enabled | 1       | NULL                          | 6979 | Using index condition; Using temporary; Using filesort              |
|    1 | SIMPLE      | gl    | eq_ref | PRIMARY                      | PRIMARY       | 3       | cacti.td.local_graph_id       |    1 | Using where                                                         |
|    1 | SIMPLE      | tt    | eq_ref | PRIMARY,id                   | PRIMARY       | 4       | cacti.td.thold_template_id    |    1 |                                                                     |
|    1 | SIMPLE      | dtd   | ref    | local_data_id                | local_data_id | 3       | cacti.td.local_data_id        |    1 | Using where                                                         |
|    1 | SIMPLE      | dtr   | eq_ref | PRIMARY                      | PRIMARY       | 3       | cacti.td.data_template_rrd_id |    1 | Using where                                                         |
|    1 | SIMPLE      | uap0  | index  | NULL                         | PRIMARY       | 7       | NULL                          |  287 | Using where; Using index; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | uap1  | index  | NULL                         | PRIMARY       | 7       | NULL                          |  287 | Using where; Using index; Using join buffer (incremental, BNL join) |
|    1 | SIMPLE      | uap2  | index  | NULL                         | PRIMARY       | 7       | NULL                          |  287 | Using where; Using index; Using join buffer (incremental, BNL join) |
+------+-------------+-------+--------+------------------------------+---------------+---------+-------------------------------+------+---------------------------------------------------------------------+
xmacan commented 4 years ago

SELECT td.*, dtd.rrd_step, tt.name AS template_name, dtr.data_source_name AS data_source, IF(IFNULL(td.lastread,'')='',NULL,(td.lastread + 0.0)) AS flastread, IF(IFNULL(td.oldvalue,'')='',NULL,(td.oldvalue + 0.0)) AS foldvalue, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(lastchanged) AS instate, uap0.user_id AS user0, uap1.user_id AS user1, uap2.user_id AS user2 FROM thold_data AS td INNER JOIN graph_local AS gl ON gl.id=td.local_graph_id LEFT JOIN graph_templates AS gt ON gt.id=gl.graph_template_id LEFT JOIN host AS h ON h.id=gl.host_id LEFT JOIN thold_template AS tt ON tt.id=td.thold_template_id LEFT JOIN data_template_data AS dtd ON dtd.local_data_id=td.local_data_id LEFT JOIN data_template_rrd AS dtr ON dtr.id=td.data_template_rrd_id LEFT JOIN user_auth_perms AS uap0 ON (gl.id=uap0.item_id AND uap0.type=1) LEFT JOIN user_auth_perms AS uap1 ON (gl.host_id=uap1.item_id AND uap1.type=3) LEFT JOIN user_auth_perms AS uap2 ON (gl.graph_template_id=uap2.item_id AND uap2.type=4) WHERE (td.thold_enabled = 'on') HAVING (user0 IS NULL OR (user1 IS NULL OR user2 IS NULL)) ORDER BY name_cache ASC LIMIT 0,10; 10 rows in set, 28 warnings (10.403 sec)

MariaDB [cacti]> SHOW WARNINGS; +---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'U' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'U' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'U' |

xmacan commented 4 years ago

without ORDER BY name_cache it is faster - 500 rows in set (1.046 sec)

with order by: | 1 | SIMPLE | td | ALL | local_graph_id,thold_enabled | NULL | NULL | NULL | 6695 | Using where; Using temporary; Using filesort

without order by: | 1 | SIMPLE | td | ALL | local_graph_id,thold_enabled | NULL | NULL | NULL | 6695 | Using where

xmacan commented 4 years ago

hehe :-) for me I have found easy fix - call your function with order by null.

But in gui it is slow ... :-(

TheWitness commented 4 years ago

Show me the output of this:

SHOW CREATE TABLE data_template_data;
TheWitness commented 4 years ago

Lastly how many tholds? Also, if you tholds have long names, you might want to:

ALTER TABLE data_template_data DROP INDEX name_cache, ADD INDEX name_cache(name_cache);
TheWitness commented 4 years ago

Might be an artifact of the partial index on the name_cache field.

xmacan commented 4 years ago

| data_template_data | CREATE TABLE data_template_data ( id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, local_data_template_data_id mediumint(8) unsigned NOT NULL DEFAULT 0, local_data_id mediumint(8) unsigned NOT NULL DEFAULT 0, data_template_id mediumint(8) unsigned NOT NULL DEFAULT 0, data_input_id mediumint(8) unsigned NOT NULL DEFAULT 0, 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 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 '', rrd_step mediumint(8) unsigned NOT NULL DEFAULT 0, t_data_source_profile_id char(2) COLLATE utf8mb4_unicode_ci DEFAULT '', data_source_profile_id mediumint(8) unsigned NOT NULL DEFAULT 1, PRIMARY KEY (id), KEY local_data_id (local_data_id), KEY data_template_id (data_template_id), KEY data_input_id (data_input_id), KEY name_cache (name_cache(191)) ) ENGINE=InnoDB AUTO_INCREMENT=27422 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

xmacan commented 4 years ago

after ALTER TABLE data_template_data DROP INDEX name_cache, ADD INDEX name_cache(name_cache) is result the same. With 7000 tholds I'm waiting about 12 seconds. The same query - SELECT td.*, dtd.rrd_step, tt.name AS template_name, dtr.data_source_name AS data_source, IF(IFNULL(td.lastread,'')='',NULL,(td.lastread + 0.0)) AS flastread, IF(IFNULL(td.oldvalue,'')='',NULL,(td.oldvalue + 0.0)) AS foldvalue, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(lastchanged) AS instate, uap0.user_id AS user0, uap1.user_id AS user1, uap2.user_id AS user2 FROM thold_data AS td INNER JOIN graph_local AS gl ON gl.id=td.local_graph_id LEFT JOIN graph_templates AS gt ON gt.id=gl.graph_template_id LEFT JOIN host AS h ON h.id=gl.host_id LEFT JOIN thold_template AS tt ON tt.id=td.thold_template_id LEFT JOIN data_template_data AS dtd ON dtd.local_data_id=td.local_data_id LEFT JOIN data_template_rrd AS dtr ON dtr.id=td.data_template_rrd_id LEFT JOIN user_auth_perms AS uap0 ON (gl.id=uap0.item_id AND uap0.type=1) LEFT JOIN user_auth_perms AS uap1 ON (gl.host_id=uap1.item_id AND uap1.type=3) LEFT JOIN user_auth_perms AS uap2 ON (gl.graph_template_id=uap2.item_id AND uap2.type=4) WHERE (td.thold_enabled = 'on') HAVING (user0 IS NULL OR (user1 IS NULL OR user2 IS NULL)) ORDER BY name_cache ASC LIMIT 0,10; 10 rows in set, 99 warnings (9.494 sec)

TheWitness commented 4 years ago

Show me the output of:

SHOW GLOBAL VARIABLES LIKE '%tmp%';

Also, is tmpdir (by default /tmp) tmpfs? If not, can you create a mount that is tmpfs, and then change the tmpdir in mysql to point to the new mount point? After that, without making any settings in mysql, run the query again.

xmacan commented 4 years ago

MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%tmp%'; +--------------------------------------+----------------------+ | Variable_name | Value | +--------------------------------------+----------------------+ | default_tmp_storage_engine | | | encrypt_tmp_disk_tables | OFF | | encrypt_tmp_files | OFF | | innodb_tmpdir | | | max_tmp_tables | 32 | | slave_load_tmpdir | /tmp/ | | tmp_disk_table_size | 18446744073709551615 | | tmp_memory_table_size | 33554432 | | tmp_table_size | 33554432 | | tmpdir | /tmp/ | +----------------------------+----------------------+

I have FreeBSD and my /tmp is normal UFS filesystem: /dev/da0p4 on /tmp (ufs, local, journaled soft-updates)

Tmpfs is supported in FreeBSD. I will try it and send the result ASAP.

TheWitness commented 4 years ago

Cool, the reason that I ask is that I was performing some rather large queries lately that was litterally overwhelming local storage with I/O, and I noted that /tmp was tmpfs, so I moved the MySQL tmpdir their, and the problem went away. Not saying that's your problem, but just curious if it speeds things up.

xmacan commented 4 years ago

with tmpfs the same result: SELECT td.*, dtd.rrd_step, tt.name AS template_name, dtr.data_source_name AS data_source, IF(IFNULL(td.lastread,'')='',NULL,(td.lastread + 0.0)) AS flastread, IF(IFNULL(td.oldvalue,'')='',NULL,(td.oldvalue + 0.0)) AS foldvalue, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(lastchanged) AS instate, uap0.user_id AS user0, uap1.user_id AS user1, uap2.user_id AS user2 FROM thold_data AS td INNER JOIN graph_local AS gl ON gl.id=td.local_graph_id LEFT JOIN graph_templates AS gt ON gt.id=gl.graph_template_id LEFT JOIN host AS h ON h.id=gl.host_id LEFT JOIN thold_template AS tt ON tt.id=td.thold_template_id LEFT JOIN data_template_data AS dtd ON dtd.local_data_id=td.local_data_id LEFT JOIN data_template_rrd AS dtr ON dtr.id=td.data_template_rrd_id LEFT JOIN user_auth_perms AS uap0 ON (gl.id=uap0.item_id AND uap0.type=1) LEFT JOIN user_auth_perms AS uap1 ON (gl.host_id=uap1.item_id AND uap1.type=3) LEFT JOIN user_auth_perms AS uap2 ON (gl.graph_template_id=uap2.item_id AND uap2.type=4) WHERE (td.thold_enabled = 'on') HAVING (user0 IS NULL OR (user1 IS NULL OR user2 IS NULL)) ORDER BY name_cache ASC LIMIT 0,10; 10 rows in set, 100 warnings (9.520 sec)

without order by about 1.1 sec.

TheWitness commented 4 years ago

Do an order by SUBSTRING(name_cache, 1, 10), is it still slow?

TheWitness commented 4 years ago

Also, what MariaDB version?

xmacan commented 4 years ago

ORDER BY substring(td.name_cache,1,10) ASC LIMIT 0,10; 10 rows in set, 100 warnings (9.408 sec)

root@kaktus ~ # pkg info | grep maria mariadb103-client-10.3.21 Multithreaded SQL database (client) mariadb103-server-10.3.21 Multithreaded SQL database (server)

TheWitness commented 4 years ago

Okay, let's trim down the query a bit. Just do this one:

SELECT td.* 
FROM thold_data AS td 
INNER JOIN graph_local AS gl 
ON gl.id = td.local_graph_id
ORDER BY name_cache ASC
LIMIT 0, 10;

How long does that run for?

xmacan commented 4 years ago

I tried the same thing on centos (MariaDB-server.x86_64 10.1.45-1.el7.centos) and there it is ok. 24069 rows in set, 109 warnings (0.94 sec)

xmacan commented 4 years ago

SELECT td.* FROM thold_data AS td INNER JOIN graph_local AS gl ON gl.id = td.local_graph_id ORDER BY name_cache ASC LIMIT 0, 10; 10 rows in set (0.028 sec)

TheWitness commented 4 years ago

Oh, then run this CLI script for me, then the query again:

php -q analyze_database.php --debug
TheWitness commented 4 years ago

If the Analyze get's it, you should definitely crontab that ones a day or so. Would even accept a pull request to merge it into the poller_maint.php.

TheWitness commented 4 years ago

BTW Petr, I'm using 10.5.5 on my development box. All is working fine.

xmacan commented 4 years ago

root@kaktus cli # php -q analyze_database.php --debug Analyzing All Cacti Database Tables Analyzing Table -> 'aggregate_graph_templates' Successful Analyzing Table -> 'aggregate_graph_templates_graph' Successful Analyzing Table -> 'aggregate_graph_templates_item' Successful Analyzing Table -> 'aggregate_graphs' Successful Analyzing Table -> 'aggregate_graphs_graph_item' Successful Analyzing Table -> 'aggregate_graphs_items' Successful Analyzing Table -> 'automation_devices' Successful Analyzing Table -> 'automation_graph_rule_items' Successful Analyzing Table -> 'automation_graph_rules' Successful Analyzing Table -> 'automation_ips' Successful Analyzing Table -> 'automation_match_rule_items' Successful Analyzing Table -> 'automation_networks' Successful Analyzing Table -> 'automation_processes' Successful Analyzing Table -> 'automation_snmp' Successful Analyzing Table -> 'automation_snmp_items' Successful Analyzing Table -> 'automation_templates' Successful Analyzing Table -> 'automation_tree_rule_items' Successful Analyzing Table -> 'automation_tree_rules' Successful Analyzing Table -> 'cdef' Successful Analyzing Table -> 'cdef_items' Successful Analyzing Table -> 'color_template_items' Successful Analyzing Table -> 'color_templates' Successful Analyzing Table -> 'colors' Successful Analyzing Table -> 'data_debug' Successful Analyzing Table -> 'data_input' Successful Analyzing Table -> 'data_input_data' Successful Analyzing Table -> 'data_input_fields' Successful Analyzing Table -> 'data_local' Successful Analyzing Table -> 'data_source_profiles' Successful Analyzing Table -> 'data_source_profiles_cf' Successful Analyzing Table -> 'data_source_profiles_rra' Successful Analyzing Table -> 'data_source_purge_action' Successful Analyzing Table -> 'data_source_purge_temp' Successful Analyzing Table -> 'data_source_stats_daily' Successful Analyzing Table -> 'data_source_stats_hourly' Successful Analyzing Table -> 'data_source_stats_hourly_cache' Successful Analyzing Table -> 'data_source_stats_hourly_last' Successful Analyzing Table -> 'data_source_stats_monthly' Successful Analyzing Table -> 'data_source_stats_weekly' Successful Analyzing Table -> 'data_source_stats_yearly' Successful Analyzing Table -> 'data_template' Successful Analyzing Table -> 'data_template_data' Successful Analyzing Table -> 'data_template_rrd' Successful Analyzing Table -> 'external_links' Successful Analyzing Table -> 'graph_local' Successful Analyzing Table -> 'graph_template_input' Successful Analyzing Table -> 'graph_template_input_defs' Successful Analyzing Table -> 'graph_templates' Successful Analyzing Table -> 'graph_templates_gprint' Successful Analyzing Table -> 'graph_templates_graph' Successful Analyzing Table -> 'graph_templates_item' Successful Analyzing Table -> 'graph_tree' Successful Analyzing Table -> 'graph_tree_items' Successful Analyzing Table -> 'host' Successful Analyzing Table -> 'host_graph' Successful Analyzing Table -> 'host_snmp_cache' Successful Analyzing Table -> 'host_snmp_query' Successful Analyzing Table -> 'host_template' Successful Analyzing Table -> 'host_template_graph' Successful Analyzing Table -> 'host_template_snmp_query' Successful Analyzing Table -> 'intropage_settings' Successful Analyzing Table -> 'mac_track_aggregated_ports' Successful Analyzing Table -> 'mac_track_approved_macs' Successful Analyzing Table -> 'mac_track_device_types' Successful Analyzing Table -> 'mac_track_devices' Successful Analyzing Table -> 'mac_track_dot1x' Successful Analyzing Table -> 'mac_track_interface_graphs' Successful Analyzing Table -> 'mac_track_interfaces' Successful Analyzing Table -> 'mac_track_ip_ranges' Successful Analyzing Table -> 'mac_track_ips' Successful Analyzing Table -> 'mac_track_macauth' Successful Analyzing Table -> 'mac_track_macwatch' Successful Analyzing Table -> 'mac_track_oui_database' Successful Analyzing Table -> 'mac_track_ports' Successful Analyzing Table -> 'mac_track_processes' Successful Analyzing Table -> 'mac_track_scan_dates' Successful Analyzing Table -> 'mac_track_scanning_functions' Successful Analyzing Table -> 'mac_track_sites' Successful Analyzing Table -> 'mac_track_snmp' Successful Analyzing Table -> 'mac_track_snmp_items' Successful Analyzing Table -> 'mac_track_temp_ports' Successful Analyzing Table -> 'mac_track_vlans' Successful Analyzing Table -> 'mbv_thold_lists_contacts' Successful Analyzing Table -> 'plugin_config' Successful Analyzing Table -> 'plugin_db_changes' Successful Analyzing Table -> 'plugin_hooks' Successful Analyzing Table -> 'plugin_intropage_panel_dashboard' Successful Analyzing Table -> 'plugin_intropage_panel_data' Successful Analyzing Table -> 'plugin_intropage_panel_definition' Successful Analyzing Table -> 'plugin_intropage_trends' Successful Analyzing Table -> 'plugin_intropage_user_auth' Successful Analyzing Table -> 'plugin_maint_hosts' Successful Analyzing Table -> 'plugin_maint_schedules' Successful Analyzing Table -> 'plugin_notification_lists' Successful Analyzing Table -> 'plugin_pihi_data' Successful Analyzing Table -> 'plugin_realms' Successful Analyzing Table -> 'plugin_thold_contacts' Successful Analyzing Table -> 'plugin_thold_daemon_data' Successful Analyzing Table -> 'plugin_thold_daemon_processes' Successful Analyzing Table -> 'plugin_thold_host_failed' Successful Analyzing Table -> 'plugin_thold_host_template' Successful Analyzing Table -> 'plugin_thold_log' Successful Analyzing Table -> 'plugin_thold_template_contact' Successful Analyzing Table -> 'plugin_thold_threshold_contact' Successful Analyzing Table -> 'plugin_uptime_data' Successful Analyzing Table -> 'poller' Successful Analyzing Table -> 'poller_command' Successful Analyzing Table -> 'poller_data_template_field_mappings' Successful Analyzing Table -> 'poller_item' Successful Analyzing Table -> 'poller_output' Successful Analyzing Table -> 'poller_output_boost' Successful Analyzing Table -> 'poller_output_boost_processes' Successful Analyzing Table -> 'poller_output_realtime' Successful Analyzing Table -> 'poller_reindex' Successful Analyzing Table -> 'poller_resource_cache' Successful Analyzing Table -> 'poller_time' Successful Analyzing Table -> 'poller_time_stats' Successful Analyzing Table -> 'processes' Successful Analyzing Table -> 'reports' Successful Analyzing Table -> 'reports_items' Successful Analyzing Table -> 'sessions' Successful Analyzing Table -> 'settings' Successful Analyzing Table -> 'settings_tree' Successful Analyzing Table -> 'settings_user' Successful Analyzing Table -> 'settings_user_group' Successful Analyzing Table -> 'sites' Successful Analyzing Table -> 'snmp_query' Successful Analyzing Table -> 'snmp_query_graph' Successful Analyzing Table -> 'snmp_query_graph_rrd' Successful Analyzing Table -> 'snmp_query_graph_rrd_sv' Successful Analyzing Table -> 'snmp_query_graph_sv' Successful Analyzing Table -> 'snmpagent_cache' Successful Analyzing Table -> 'snmpagent_cache_notifications' Successful Analyzing Table -> 'snmpagent_cache_textual_conventions' Successful Analyzing Table -> 'snmpagent_managers' Successful Analyzing Table -> 'snmpagent_managers_notifications' Successful Analyzing Table -> 'snmpagent_mibs' Successful Analyzing Table -> 'snmpagent_notifications_log' Successful Analyzing Table -> 'syslog' Successful Analyzing Table -> 'syslog_alert' Successful Analyzing Table -> 'syslog_facilities' Successful Analyzing Table -> 'syslog_host_facilities' Successful Analyzing Table -> 'syslog_hosts' Successful Analyzing Table -> 'syslog_incoming' Successful Analyzing Table -> 'syslog_logs' Successful Analyzing Table -> 'syslog_priorities' Successful Analyzing Table -> 'syslog_programs' Successful Analyzing Table -> 'syslog_remove' Successful Analyzing Table -> 'syslog_removed' Successful Analyzing Table -> 'syslog_reports' Successful Analyzing Table -> 'syslog_statistics' Successful Analyzing Table -> 'test' Successful Analyzing Table -> 'thold_data' Successful Analyzing Table -> 'thold_template' Successful Analyzing Table -> 'user_auth' Successful Analyzing Table -> 'user_auth_cache' Successful Analyzing Table -> 'user_auth_group' Successful Analyzing Table -> 'user_auth_group_members' Successful Analyzing Table -> 'user_auth_group_perms' Successful Analyzing Table -> 'user_auth_group_realm' Successful Analyzing Table -> 'user_auth_perms' Successful Analyzing Table -> 'user_auth_realm' Successful Analyzing Table -> 'user_domains' Successful Analyzing Table -> 'user_domains_ldap' Successful Analyzing Table -> 'user_log' Successful Analyzing Table -> 'vdef' Successful Analyzing Table -> 'vdef_items' Successful Analyzing Table -> 'version' Successful

After: ORDER BY substring(td.name_cache,1,10) ASC LIMIT 0,10; 10 rows in set, 100 warnings (9.508 sec)

xmacan commented 4 years ago

any misconfiguration in my mysql? I tried use default my.cnf - the same result. I can try upgrade maria to 10.5, I haven't any other idea.

TheWitness commented 4 years ago

Was my alter ego ;) So, compare your two 'show global variables'. Also, show the sizes of these two output:

cd /var/lib/mysql
du -hs .

From both systems.

xmacan commented 4 years ago

On my problematic FreeBSD installation - upgrade mariadb to 10.5.5 and cacti from 1.2.12 to 1.2.14. root@kaktus cache # du -sh /var/db/mysql/ 1,5G /var/db/mysql/ root@kaktus cache # du -sh /var/db/mysql/* 4,0K /var/db/mysql/aria_log_control 216K /var/db/mysql/aria_log.00000001 740M /var/db/mysql/cacti 14M /var/db/mysql/cacti_develop 16K /var/db/mysql/ib_buffer_pool 256M /var/db/mysql/ib_logfile0 256M /var/db/mysql/ib_logfile1 195M /var/db/mysql/ibdata1 12M /var/db/mysql/ibtmp1 16M /var/db/mysql/kaktus.log 0B /var/db/mysql/multi-master.info 12M /var/db/mysql/mysql 4,0K /var/db/mysql/mysql_upgrade_info 284K /var/db/mysql/mysql-slow.log 8,0K /var/db/mysql/performance_schema 24K /var/db/mysql/tc.log 1,3M /var/db/mysql/test 208K /var/db/mysql/test2

xmacan commented 4 years ago

!YES!

Display thresholds in GUI is now faster. Not ideal but much better then before.

Thank you.

TheWitness commented 4 years ago

Cool, what is your innodb_buffer_pool_size set to? Should be about 4GB.

xmacan commented 4 years ago

little bit strange ;-) 571M /var/db/mysql/cacti 741M /var/db/mysql_orig/cacti But cacti works fine.

Now I have default value: innodb_buffer_pool_size | 134217728
Changing to 4G (I have had 5GB before) With 4G without change 10 rows in set, 100 warnings (3.380 sec)

xmacan commented 4 years ago

Last thing - everything in GUI is now faster than before.

Hmmm, not only in GUI - poller is faster (about 33%), gathering intropage stats too, ...

In top utility was mysql process most of time "on the top" and it consumed a lot of CPU and memory. Now it is better.

I have in processlist only: +------+-----------+-----------------+-------+---------+------+----------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +------+-----------+-----------------+-------+---------+------+----------+------------------+----------+ | 5 | cactiuser | localhost:28869 | cacti | Sleep | 369 | | NULL | 0.000 | | 646 | cactiuser | localhost:11748 | cacti | Sleep | 308 | | NULL | 0.000 | | 649 | cactiuser | localhost:48269 | cacti | Sleep | 247 | | NULL | 0.000 | | 650 | cactiuser | localhost:55570 | cacti | Sleep | 212 | | NULL | 0.000 | | 651 | cactiuser | localhost:37401 | cacti | Sleep | 212 | | NULL | 0.000 | | 652 | cactiuser | localhost:26834 | cacti | Sleep | 212 | | NULL | 0.000 | | 653 | cactiuser | localhost:49168 | cacti | Sleep | 212 | | NULL | 0.000 | | 654 | cactiuser | localhost:17487 | cacti | Sleep | 212 | | NULL | 0.000 | | 655 | cactiuser | localhost:49684 | cacti | Sleep | 212 | | NULL | 0.000 | | 656 | cactiuser | localhost:27007 | cacti | Sleep | 2 | | NULL | 0.000 | | 1296 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |

Before there were few innodb processes very often.

Something was wrong with my MariaDB. Mysqltuner gave me few advices but the result was still the same.

@TheWitness - thank you!

TheWitness commented 4 years ago

Could have been memory settings. If you don't have sufficient innodb_buffer_pool, then everything goes haywire. Now that you are on 10.5, if your DB is on SSD or Flash, you can really speed things up with 'innodb_io_capacity=10000' and 'innodb_io_capacity_max=20000', and settings like read and write threads.

TheWitness commented 4 years ago

Most of them are in Technical Support, but there may be a few missing depending on your version. I have not audited lately.

TheWitness commented 4 years ago

Here are the recommendations for my little 32GB 4/8 core/thread server. image

xmacan commented 4 years ago

My cacti is on vmware and datastore is 10K raid1. SSD is better but speed is sufficient now. I don't know what was wrong before. Thank you.