cli command - add graph (very slow )

[TheWitness]

Okay, more clues. Go to Console > Utilities > Technical Support > General tab, then scroll down to the Database Recommendations. It should look similar to the following. Post the entirety of it.

[saulofonseca]

Hello,

On my second post, there is the entirely info in .pdf format of the cacti technical support.

Here what u asked:

Captura de tela de 2023-08-12 14-34-23.png (23.36 KiB) Viewed 1386 times

[TheWitness]

Super weird, what is the Cacti database name on this system? Could it be that InnoDB crashed and all the tables moved from InnoDB to MyISAM format? Somethings definitely wrong.

Try the command:

Code: Select all

SHOW ENGINES;

[saulofonseca]

I followed your suggestion and I upgrade mariadb to the version you are using:

[root@localhost autom]# mysql -V
mysql Ver 15.1 Distrib 10.5.21-MariaDB, for Linux (x86_64) using readline 5.1


follows your request:

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| cactidb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0,001 sec)



MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0,001 sec)

[TheWitness]

So, is the repair working now?

[TheWitness]

I'd really like to get to the bottom of this. So, in audit_database.php around line 328, you will find a query the returns a variable $tblinfo, add the following likes right after that line:

Code: Select all

            if (isset($tblinfo['COLLATION'])) {
                $collation = $tblinfo['COLLATION'];
            } else {
                $collation = 'utf8mb4';
            }

then right after that change the next 5 lines to match this:

Code: Select all

            if ($tblinfo['ENGINE'] == 'MyISAM') {
                $suffix = ",\n   ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=" . $collation;
            } else {
                $suffix = ",\n   ROW_FORMAT=Dynamic CHARSET=" . $collation;
            }

Then save, and run audit_database.php again.

[saulofonseca]

Ok. Now the --repair is working.

here is the logs:


root@localhost cli]# php audit_database.php --repair
SUCCESS: Loaded the Audit Schema
Scanning Table: 'aggregate_graph_templates' - Completed
Scanning Table: 'aggregate_graph_templates_graph' - Completed
Scanning Table: 'aggregate_graph_templates_item' - Completed
Scanning Table: 'aggregate_graphs' - Completed
Scanning Table: 'aggregate_graphs_graph_item' - Completed
Scanning Table: 'aggregate_graphs_items' - Completed
Scanning Table: 'automation_devices' - Completed
Scanning Table: 'automation_graph_rule_items' - Completed
Scanning Table: 'automation_graph_rules' - Completed
Scanning Table: 'automation_ips' - Completed
Scanning Table: 'automation_match_rule_items' - Completed
Scanning Table: 'automation_networks' - Completed
Scanning Table: 'automation_processes' - Completed
Scanning Table: 'automation_snmp' - Completed
Scanning Table: 'automation_snmp_items' - Completed
Scanning Table: 'automation_templates' - Completed
Scanning Table: 'automation_tree_rule_items' - Completed
Scanning Table: 'automation_tree_rules' - Completed
Scanning Table: 'cdef' - Completed
Scanning Table: 'cdef_items' - Completed
Scanning Table: 'color_template_items' - Completed
Scanning Table: 'color_templates' - Completed
Scanning Table: 'colors' - Completed
Scanning Table: 'data_debug' - Completed
Scanning Table: 'data_input' - Completed
Scanning Table: 'data_input_data' - Completed
Scanning Table: 'data_input_fields' - Completed
Scanning Table: 'data_local' - Completed
Scanning Table: 'data_source_profiles' - Completed
Scanning Table: 'data_source_profiles_cf' - Completed
Scanning Table: 'data_source_profiles_rra' - Completed
Scanning Table: 'data_source_purge_action' - Completed
Scanning Table: 'data_source_purge_temp' - Completed
Scanning Table: 'data_source_stats_daily' - Completed
Scanning Table: 'data_source_stats_hourly' - Completed
Scanning Table: 'data_source_stats_hourly_cache' - Completed
Scanning Table: 'data_source_stats_hourly_last' - Completed
Scanning Table: 'data_source_stats_monthly' - Completed
Scanning Table: 'data_source_stats_weekly' - Completed
Scanning Table: 'data_source_stats_yearly' - Completed
Scanning Table: 'data_template' - Completed
Scanning Table: 'data_template_data' - Completed
Scanning Table: 'data_template_rrd' - Completed
Scanning Table: 'external_links' - Completed
Scanning Table: 'graph_local' - Completed
Scanning Table: 'graph_template_input' - Completed
Scanning Table: 'graph_template_input_defs' - Completed
Scanning Table: 'graph_templates' - Completed
Scanning Table: 'graph_templates_gprint' - Completed
Scanning Table: 'graph_templates_graph' - Completed
Scanning Table: 'graph_templates_item' - Completed
Scanning Table: 'graph_tree' - Completed
Scanning Table: 'graph_tree_items' - Completed
Scanning Table: 'host' - Completed
Scanning Table: 'host_graph' - Completed
Scanning Table: 'host_snmp_cache' - Completed
Scanning Table: 'host_snmp_query' - Completed
Scanning Table: 'host_template' - Completed
Scanning Table: 'host_template_graph' - Completed
Scanning Table: 'host_template_snmp_query' - Completed
Scanning Table: 'plugin_config' - Completed
Scanning Table: 'plugin_db_changes' - Completed
Scanning Table: 'plugin_hooks' - Completed
Scanning Table: 'plugin_notification_lists' - Completed
Scanning Table: 'plugin_realms' - Completed
Scanning Table: 'plugin_thold_contacts' - Completed
Scanning Table: 'plugin_thold_daemon_data' - Completed
Scanning Table: 'plugin_thold_daemon_processes' - Completed
Scanning Table: 'plugin_thold_host_failed' - Completed
Scanning Table: 'plugin_thold_host_template' - Completed
Scanning Table: 'plugin_thold_log' - Completed
Scanning Table: 'plugin_thold_template_contact' - Completed
Scanning Table: 'plugin_thold_threshold_contact' - Completed
Scanning Table: 'poller' - Completed
Scanning Table: 'poller_command' - Completed
Scanning Table: 'poller_data_template_field_mappings' - Completed
Scanning Table: 'poller_item' - Completed
Scanning Table: 'poller_output' - Completed
Scanning Table: 'poller_output_boost' - Completed
Scanning Table: 'poller_output_boost_processes' - Completed
Scanning Table: 'poller_output_realtime' - Completed
Scanning Table: 'poller_reindex' - Completed
Scanning Table: 'poller_resource_cache' - Completed
Scanning Table: 'poller_time' - Completed
Scanning Table: 'processes' - Completed
Scanning Table: 'reports' - Completed
Scanning Table: 'reports_items' - Completed
Scanning Table: 'rrdcheck' - Completed
Scanning Table: 'sessions' - Completed
Scanning Table: 'settings' - Completed
Scanning Table: 'settings_tree' - Completed
Scanning Table: 'settings_user' - Completed
Scanning Table: 'settings_user_group' - Completed
Scanning Table: 'sites' - Completed
Scanning Table: 'snmp_query' - Completed
Scanning Table: 'snmp_query_graph' - Completed
Scanning Table: 'snmp_query_graph_rrd' - Completed
Scanning Table: 'snmp_query_graph_rrd_sv' - Completed
Scanning Table: 'snmp_query_graph_sv' - Completed
Scanning Table: 'snmpagent_cache' - Completed
Scanning Table: 'snmpagent_cache_notifications' - Completed
Scanning Table: 'snmpagent_cache_textual_conventions' - Completed
Scanning Table: 'snmpagent_managers' - Completed
Scanning Table: 'snmpagent_managers_notifications' - Completed
Scanning Table: 'snmpagent_mibs' - Completed
Scanning Table: 'snmpagent_notifications_log' - Completed
Scanning Table: 'table_columns' - Completed
Scanning Table: 'table_indexes' - Completed
Scanning Table: 'thold_data' - Completed
Scanning Table: 'thold_template' - Completed
Scanning Table: 'user_auth' - Completed
Scanning Table: 'user_auth_cache' - Completed
Scanning Table: 'user_auth_group' - Completed
Scanning Table: 'user_auth_group_members' - Completed
Scanning Table: 'user_auth_group_perms' - Completed
Scanning Table: 'user_auth_group_realm' - Completed
Scanning Table: 'user_auth_perms' - Completed
Scanning Table: 'user_auth_realm' - Completed
Scanning Table: 'user_auth_row_cache' - Completed
Scanning Table: 'user_domains' - Completed
Scanning Table: 'user_domains_ldap' - Completed
Scanning Table: 'user_log' - Completed
Scanning Table: 'vdef' - Completed
Scanning Table: 'vdef_items' - Completed
Scanning Table: 'version' - Completed
Scanning Table: 'weathermap_auth' - Completed
Scanning Table: 'weathermap_data' - Completed
Scanning Table: 'weathermap_groups' - Completed
Scanning Table: 'weathermap_maps' - Completed
Scanning Table: 'weathermap_settings' - Completed
---------------------------------------------------------------------------------------------
Executing Alter for Table : aggregate_graph_templates_graph - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : aggregate_graph_templates_item - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : aggregate_graphs_graph_item - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : automation_devices - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : automation_processes - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : automation_snmp_items - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : automation_templates - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : cdef_items - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : color_template_items - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_debug - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_input - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_local - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_source_purge_action - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_source_purge_temp - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_template_rrd - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : external_links - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : graph_template_input_defs - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : graph_templates_graph - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : graph_templates_item - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : graph_tree - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : host - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : host_snmp_cache - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : plugin_config - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : plugin_hooks - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : poller - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : poller_item - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : poller_reindex - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : poller_time - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : processes - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : reports - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : reports_items - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : settings - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : settings_tree - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : snmpagent_managers - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : snmpagent_managers_notifications - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : snmpagent_mibs - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : snmpagent_notifications_log - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_auth - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_auth_group - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_auth_group_perms - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_auth_perms - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_domains - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_log - Success
---------------------------------------------------------------------------------------------
Executing Alter for Table : vdef_items - Success
---------------------------------------------------------------------------------------------
Repair Completed! All 44 Alters succeeded!

[saulofonseca]

I tested and unfortunately the graph creation still slow for my template.

What else do you suggest ??

[TheWitness]

It's interesting that your Query plan is not the same as mine, but it could be due to the number of data sources and graphs. So, let's try playing with this query a bit. I'm going to type in a few examples that I want you to run and time:

Code: Select all

SELECT dl.* 
FROM data_local AS dl 
INNER JOIN data_template AS dt 
ON dl.data_template_id=dt.id 
INNER JOIN data_template_rrd AS dtr 
ON dtr.data_template_id=dt.id 
INNER JOIN graph_templates_item AS gti 
ON gti.task_item_id=dtr.id 
WHERE dtr.local_data_id > 0 
AND dl.host_id = '48' 
AND dl.data_template_id = '184' 
AND dtr.data_source_name = 'fiberhome_onu_volt' 
AND gti.local_graph_id > 0 
AND gti.graph_template_id = '175' 
GROUP BY dl.data_template_id;

Code: Select all

SELECT dl.* 
FROM data_local AS dl 
INNER JOIN data_template_rrd AS dtr 
ON dtr.data_template_id = dt.id 
INNER JOIN graph_templates_item AS gti 
ON gti.task_item_id = dtr.id 
WHERE dtr.local_data_id > 0 
AND dl.host_id = '48' 
AND dl.data_template_id = '184' 
AND dtr.data_source_name = 'fiberhome_onu_volt' 
AND gti.local_graph_id > 0 
AND gti.graph_template_id = '175' 

[TheWitness]

Change line 2269 in file lib/template.php to the block below from what it was. It's a minor change, but should made a huge difference.

Code: Select all

      $data_source = db_fetch_row_prepared('SELECT dl.*
            FROM data_local AS dl
            INNER JOIN data_template_rrd AS dtr
            ON dtr.data_template_id = dl.data_template_id
            AND dtr.local_data_id = dl.id
            INNER JOIN graph_templates_item AS gti
            ON gti.task_item_id = dtr.id
            WHERE dtr.local_data_id > 0
            AND dl.host_id = ?
            AND dl.data_template_id = ?
            AND dtr.data_source_name = ?
            AND gti.local_graph_id > 0
            AND gti.graph_template_id = ?
            LIMIT 1',
            array($host_id, $data_template['id'], $data_template['data_source_name'], $graph_template_id));

[TheWitness]

I've got a minor edit. Did not test first.

[TheWitness]

Corrected above now.

[TheWitness]

Even cleaner now below:

Code: Select all

        /* create each data source, but don't duplicate */
        $data_source = db_fetch_row_prepared('SELECT dl.*
            FROM data_local AS dl
            INNER JOIN data_template_rrd AS dtr
            ON dtr.data_template_id = dl.data_template_id
            AND dtr.local_data_id = dl.id
            INNER JOIN graph_templates_item AS gti
            ON gti.task_item_id = dtr.id
            WHERE dl.host_id = ?
            AND dl.data_template_id = ?
            AND dtr.data_source_name = ?
            AND gti.graph_template_id = ?
            LIMIT 1',
            array($host_id, $data_template['id'], $data_template['data_source_name'], $graph_template_id));

[TheWitness]

Reported here now:

https://github.com/Cacti/cacti/issues/5451

[saulofonseca]

Wow....you solve the problem...