cli command - add graph (very slow )

Post general support questions here that do not specifically fall into the Linux or Windows categories.

Moderators: Developers, Moderators

Post Reply
User avatar
Osiris
Cacti Guru User
Posts: 1424
Joined: Mon Jan 05, 2015 10:10 am

Re: cli command - add graph (very slow )

Post by Osiris »

Also, check if your machine is using swap. Oh, and how many graphs do you have?
Before history, there was a paradise, now dust.
Top
saulofonseca
Posts: 40
Joined: Mon Jun 25, 2018 5:35 pm

Re: cli command - add graph (very slow )

Post by saulofonseca »

Hello again,

Follows:

[root@localhost cli]# php audit_database.php --report
FATAL: Failed to find Audit Schema
---------------------------------------------------------------------------------------------
Checking Table: 'aggregate_graph_templates' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'aggregate_graph_templates_graph' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'aggregate_graph_templates_item' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'aggregate_graphs' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'aggregate_graphs_graph_item' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'aggregate_graphs_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_devices' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_graph_rule_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_graph_rules' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_ips' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_match_rule_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_networks' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_processes' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_snmp' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_snmp_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_templates' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_tree_rule_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'automation_tree_rules' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'cdef' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'cdef_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'color_template_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'color_templates' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'colors' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_debug' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_input' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_input_data' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_input_fields' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_local' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_profiles' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_profiles_cf' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_profiles_rra' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_purge_action' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_purge_temp' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_stats_daily' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_stats_hourly' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_stats_hourly_cache' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_stats_hourly_last' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_stats_monthly' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_stats_weekly' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_source_stats_yearly' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_template' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_template_data' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'data_template_rrd' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'external_links' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'graph_local' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'graph_template_input' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'graph_template_input_defs' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'graph_templates' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'graph_templates_gprint' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'graph_templates_graph' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'graph_templates_item' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'graph_tree' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'graph_tree_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'host' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'host_graph' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'host_snmp_cache' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'host_snmp_query' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'host_template' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'host_template_graph' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'host_template_snmp_query' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_config' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_db_changes' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_hooks' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_notification_lists' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_realms' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_thold_contacts' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_thold_daemon_data' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_thold_daemon_processes' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_thold_host_failed' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_thold_host_template' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_thold_log' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_thold_template_contact' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'plugin_thold_threshold_contact' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'poller' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_command' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_data_template_field_mappings' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_item' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_output' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_output_boost' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_output_boost_processes' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_output_realtime' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_reindex' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_resource_cache' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'poller_time' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'processes' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'reports' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'reports_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'rrdcheck' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'sessions' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'settings' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'settings_tree' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'settings_user' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'settings_user_group' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'sites' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmp_query' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmp_query_graph' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmp_query_graph_rrd' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmp_query_graph_rrd_sv' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmp_query_graph_sv' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmpagent_cache' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmpagent_cache_notifications' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmpagent_cache_textual_conventions' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmpagent_managers' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmpagent_managers_notifications' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmpagent_mibs' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'snmpagent_notifications_log' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'table_columns' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'table_indexes' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'thold_data' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'thold_template' - Plugin Detected
---------------------------------------------------------------------------------------------
Checking Table: 'user_auth' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_auth_cache' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_auth_group' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_auth_group_members' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_auth_group_perms' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_auth_group_realm' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_auth_perms' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_auth_realm' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_auth_row_cache' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_domains' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_domains_ldap' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'user_log' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'vdef' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'vdef_items' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'version' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_auth' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_data' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_groups' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_maps' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_settings' - Does not Exist. Possible Plugin
---------------------------------------------------------------------------------------------
Audit was clean, no errors or warnings
---------------------------------------------------------------------------------------------
Top
saulofonseca
Posts: 40
Joined: Mon Jun 25, 2018 5:35 pm

Re: cli command - add graph (very slow )

Post by saulofonseca »

Hello again,

On this server we have around of 2500 graphs...

Look below it shows my swap and, the processor 23 is 100% cause i'm executing the script again...Creating more 2300 graphs........

Yestherday I follows Htop and when it start the graph creation, mysql is around 50% ....and keeps rising....and rising.....than it stays on 100%

But again, it is very slow.....it is taking about 1 or 2 days to execute it all.

Looks like if a device has more than 500 graphs, after that ...it start to slow the next graphs that will be created..
Captura de tela de 2023-08-10 14-29-17.png
Captura de tela de 2023-08-10 14-29-17.png (189.23 KiB) Viewed 1049 times
Top
User avatar
TheWitness
Developer
Posts: 17047
Joined: Tue May 14, 2002 5:08 pm
Location: MI, USA
Contact:
Contact TheWitness

Re: cli command - add graph (very slow )

Post by TheWitness »

How did you install Cacti? The database audit is not working, which means that files in the docs folder are missing. Does not look like a standard install. Also, my production system has 1M+ Graphs and 2M+ Data Sources and yea it's busy, but never that busy. So, you might benefit in a 'prove me install' and ask for a physical machine with the same number of cores and physical ram to hold all your RRDfiles in cache. Then use SSD for the RRDfiles.

My bet is that you won't see this problem again.
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages

For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
Top
saulofonseca
Posts: 40
Joined: Mon Jun 25, 2018 5:35 pm

Re: cli command - add graph (very slow )

Post by saulofonseca »

Hello again,

I don't recall what tutorial did I follow the instructions, it was before 2020. But, I can do a fresh install of Cacti on a new VM and try to replicate just this template that I need. And we see what will happen.

Just to inform that I have three others cacti running on different location, and I've tried to create this graphs on this other locations, and the exactly same problem occur.
All this 4 cacti sites were installed with Centos 8 with same procedure, if I recall correctly.


What is your distro recommendation please ???

I will follow this manual:
https://github.com/Cacti/documentation/ ... stallation

I don't have a spare machine right now to test without virtualization, but I can acquire it.

Regards
Top
User avatar
Osiris
Cacti Guru User
Posts: 1424
Joined: Mon Jan 05, 2015 10:10 am

Re: cli command - add graph (very slow )

Post by Osiris »

What happens when you add graphs through the GUI?
Before history, there was a paradise, now dust.
Top
User avatar
Osiris
Cacti Guru User
Posts: 1424
Joined: Mon Jan 05, 2015 10:10 am

Re: cli command - add graph (very slow )

Post by Osiris »

Okay, thought about this. Do a few things.

Slow Graphs
1) set the slow query time to two seconds
2)ensure the slow query log is on and writing
3) see your bugger pool instances to 1 (MariaDB restart required)
4) add some graphs
5) upload the slow query log

For the audit issue
1) Locate the audit schema file from the Cacti release file
2) place it in the docs directory
3) rerun the audit schema with the help option, follow the instructions to load the file.
4) run the report again
Before history, there was a paradise, now dust.
Top
saulofonseca
Posts: 40
Joined: Mon Jun 25, 2018 5:35 pm

Re: cli command - add graph (very slow )

Post by saulofonseca »

Hello again,

About add a graph via web interface.

If I choose another template, the graph creation goes well and fast. If I choose the template that has been used for the creation of 2300 graphs, it give me timeout.
But if I make a fresh install of cacti again, as made before with centos 8, without creating this 2300 graphs, via web interface it goes well and fast too. Then I run the cli command for mass creation, then after 500 graphs, it start to be slow...and slow...

I will attach my template here.
Attachments
cacti_graph_template_fiberhome_-_coletas_info_onu_final.zip
(4.32 KiB) Downloaded 36 times
teste.sh
(818 Bytes) Downloaded 35 times
Top
saulofonseca
Posts: 40
Joined: Mon Jun 25, 2018 5:35 pm

Re: cli command - add graph (very slow )

Post by saulofonseca »

Hello again,

I did what u asked about slow query. Here is the log:


[root@localhost autom]# cat /var/log/mysql-slow.log
/usr/libexec/mysqld, Version: 10.3.28-MariaDB-log (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 230811 9:48:31
# User@Host: root[root] @ localhost []
# Thread_id: 524 Schema: cactidb QC_hit: No
# Query_time: 88.535773 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 48759960
# Rows_affected: 0 Bytes_sent: 423
use cactidb;
SET timestamp=1691758111;
SELECT dl.* FROM data_template AS dt INNER JOIN data_local AS dl 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 = '217' AND dtr.data_source_name = 'fiberhome_sinal_rx' AND gti.local_graph_id > 0 AND gti.graph_template_id = '194' GROUP BY dt.id ORDER BY dt.name;
# Time: 230811 9:49:58
# User@Host: root[root] @ localhost []
# Thread_id: 534 Schema: cactidb QC_hit: No
# Query_time: 86.751210 Lock_time: 0.000069 Rows_sent: 1 Rows_examined: 48771595
# Rows_affected: 0 Bytes_sent: 423
SET timestamp=1691758198;
SELECT dl.* FROM data_template AS dt INNER JOIN data_local AS dl 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 = '217' AND dtr.data_source_name = 'fiberhome_sinal_rx' AND gti.local_graph_id > 0 AND gti.graph_template_id = '194' GROUP BY dt.id ORDER BY dt.name;
# Time: 230811 9:51:26
# User@Host: root[root] @ localhost []
# Thread_id: 542 Schema: cactidb QC_hit: No
# Query_time: 87.330121 Lock_time: 0.000098 Rows_sent: 1 Rows_examined: 47895622
# Rows_affected: 0 Bytes_sent: 0
SET timestamp=1691758286;
SELECT dl.* FROM data_template AS dt INNER JOIN data_local AS dl 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 = '217' AND dtr.data_source_name = 'fiberhome_sinal_rx' AND gti.local_graph_id > 0 AND gti.graph_template_id = '194' GROUP BY dt.id ORDER BY dt.name;

--------------------------------------------------------------------------------------------

[root@localhost autom]# mysqldumpslow -a /var/log/mysql-slow.log

Reading mysql slow query log from /var/log/mysql-slow.log
Count: 2 Time=87.64s (175s) Lock=0.00s (0s) Rows_sent=1.0 (2), Rows_examined=48765777.5 (97531555), Rows_affected=0.0 (0), root[root]@localhost
SELECT dl.* FROM data_template AS dt INNER JOIN data_local AS dl 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 = '217' AND dtr.data_source_name = 'fiberhome_sinal_rx' AND gti.local_graph_id > 0 AND gti.graph_template_id = '194' GROUP BY dt.id ORDER BY dt.name
Top
saulofonseca
Posts: 40
Joined: Mon Jun 25, 2018 5:35 pm

Re: cli command - add graph (very slow )

Post by saulofonseca »

Helo again,

Here it is the audit log:
Attachments
cacti_audit.txt
(32.91 KiB) Downloaded 57 times
Top
User avatar
TheWitness
Developer
Posts: 17047
Joined: Tue May 14, 2002 5:08 pm
Location: MI, USA
Contact:
Contact TheWitness

Re: cli command - add graph (very slow )

Post by TheWitness »

What MySQL/MariaDB did you install? NM, 10.3.x Did you run the --repair option?

Also, try this:

Code: Select all

mysql -A mysql
SELECT * FROM innodb_index_stats;
SELECT * FROM innodb_table_stats;
quit;
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages

For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
Top
User avatar
TheWitness
Developer
Posts: 17047
Joined: Tue May 14, 2002 5:08 pm
Location: MI, USA
Contact:
Contact TheWitness

Re: cli command - add graph (very slow )

Post by TheWitness »

Here is the output to my explain. Note how different it is. I'm running 10.5.21 here. It's pretty easy to upgrade:

https://mariadb.com/kb/en/yum/

I've also tested on 10.11.4. We have not officially started supporting 11.0 as there are some ambiguities in the installation guidance the we have to overcome. Anyway, explain syntax below. If you end the query with \G instead of ;, you get this nicer output.

Code: Select all

MariaDB [cacti]> explain SELECT dl.* FROM data_template AS dt INNER JOIN data_local AS dl 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 = '60' AND dl.data_template_id = '35' AND dtr.data_source_name = 'fiberhome_sinal_rx' AND gti.local_graph_id > 0 AND gti.graph_template_id = '48' GROUP BY dt.id ORDER BY dt.name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dl
         type: ref
possible_keys: host_id_snmp_query_id,data_template_id
          key: host_id_snmp_query_id
      key_len: 3
          ref: const
         rows: 26
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: dt
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: cacti.dl.data_template_id
         rows: 1
        Extra:
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: dtr
         type: ref
possible_keys: PRIMARY,duplicate_dsname_contraint,data_template_id
          key: data_template_id
      key_len: 3
          ref: cacti.dl.data_template_id
         rows: 3
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: gti
         type: ref|filter
possible_keys: graph_template_id,local_graph_id_sequence,task_item_id,lgi_gti
          key: task_item_id|graph_template_id
      key_len: 4|3
          ref: cacti.dtr.id
         rows: 2 (0%)
        Extra: Using where; Using rowid filter
4 rows in set (0.001 sec)
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages

For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
Top
saulofonseca
Posts: 40
Joined: Mon Jun 25, 2018 5:35 pm

Re: cli command - add graph (very slow )

Post by saulofonseca »

Hello again,

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


Here it's the audit with --repair output :

[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
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : aggregate_graph_templates_graph - Failed
ALTER TABLE `aggregate_graph_templates_graph`
MODIFY COLUMN `image_format_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `auto_scale_opts` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : aggregate_graph_templates_item - Failed
ALTER TABLE `aggregate_graph_templates_item`
MODIFY COLUMN `color_template` int(10) unsigned NOT NULL DEFAULT "0",
MODIFY COLUMN `graph_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : aggregate_graphs_graph_item - Failed
ALTER TABLE `aggregate_graphs_graph_item`
MODIFY COLUMN `color_template` int(10) unsigned NOT NULL DEFAULT "0",
MODIFY COLUMN `graph_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : automation_devices - Failed
ALTER TABLE `automation_devices`
MODIFY COLUMN `snmp_version` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `snmp_port` mediumint(8) unsigned NOT NULL DEFAULT '161',
MODIFY COLUMN `snmp` tinyint(3) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `known` tinyint(3) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `up` tinyint(3) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `time` int(10) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : automation_processes - Failed
ALTER TABLE `automation_processes`
MODIFY COLUMN `pid` int(10) unsigned NOT NULL DEFAULT "0",
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : automation_snmp_items - Failed
ALTER TABLE `automation_snmp_items`
MODIFY COLUMN `snmp_version` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `snmp_port` mediumint(8) unsigned NOT NULL DEFAULT '161',
MODIFY COLUMN `snmp_retries` tinyint(3) unsigned NOT NULL DEFAULT '3',
MODIFY COLUMN `max_oids` int(10) unsigned DEFAULT "10",
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : automation_templates - Failed
ALTER TABLE `automation_templates`
MODIFY COLUMN `id` int(10) unsigned NOT NULL auto_increment,
MODIFY COLUMN `host_template` int(10) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : cdef_items - Failed
ALTER TABLE `cdef_items`
MODIFY COLUMN `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : color_template_items - Failed
ALTER TABLE `color_template_items`
MODIFY COLUMN `color_template_item_id` int(10) unsigned NOT NULL auto_increment,
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_debug - Failed
ALTER TABLE `data_debug`
MODIFY COLUMN `id` int(10) unsigned NOT NULL auto_increment,
MODIFY COLUMN `started` int(10) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `done` int(10) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `user` int(10) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `datasource` int(10) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `issue` mediumtext NOT NULL DEFAULT '',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_input - Failed
ALTER TABLE `data_input`
MODIFY COLUMN `type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_local - Failed
ALTER TABLE `data_local`
MODIFY COLUMN `id` int(10) unsigned NOT NULL auto_increment,
MODIFY COLUMN `orphan` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_source_purge_action - Failed
ALTER TABLE `data_source_purge_action`
MODIFY COLUMN `action` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_source_purge_temp - Failed
ALTER TABLE `data_source_purge_temp`
MODIFY COLUMN `in_cacti` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : data_template_rrd - Failed
ALTER TABLE `data_template_rrd`
DROP INDEX data_source_name,
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : external_links - Failed
ALTER TABLE `external_links`
MODIFY COLUMN `id` int(10) unsigned NOT NULL auto_increment,
MODIFY COLUMN `sortorder` int(10) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : graph_template_input_defs - Failed
ALTER TABLE `graph_template_input_defs`
MODIFY COLUMN `graph_template_item_id` int(10) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : graph_templates_graph - Failed
ALTER TABLE `graph_templates_graph`
MODIFY COLUMN `image_format_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `auto_scale_opts` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : graph_templates_item - Failed
ALTER TABLE `graph_templates_item`
MODIFY COLUMN `graph_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `consolidation_function_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : graph_tree - Failed
ALTER TABLE `graph_tree`
MODIFY COLUMN `locked` tinyint(3) unsigned DEFAULT "0",
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : host - Failed
ALTER TABLE `host`
MODIFY COLUMN `snmp_version` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `snmp_port` mediumint(8) unsigned NOT NULL DEFAULT '161',
MODIFY COLUMN `ping_port` int(10) unsigned DEFAULT "0",
MODIFY COLUMN `ping_timeout` int(10) unsigned DEFAULT "500",
MODIFY COLUMN `ping_retries` int(10) unsigned DEFAULT "2",
MODIFY COLUMN `max_oids` int(10) unsigned DEFAULT "10",
MODIFY COLUMN `device_threads` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `total_polls` int(10) unsigned DEFAULT "0",
MODIFY COLUMN `failed_polls` int(10) unsigned DEFAULT "0",
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : host_snmp_cache - Failed
ALTER TABLE `host_snmp_cache`
MODIFY COLUMN `present` tinyint(3) unsigned NOT NULL DEFAULT '1',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : plugin_config - Failed
ALTER TABLE `plugin_config`
MODIFY COLUMN `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : plugin_hooks - Failed
ALTER TABLE `plugin_hooks`
MODIFY COLUMN `status` int(10) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : poller - Failed
ALTER TABLE `poller`
MODIFY COLUMN `dbretries` int(10) unsigned DEFAULT "2",
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : poller_item - Failed
ALTER TABLE `poller_item`
MODIFY COLUMN `action` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `present` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `snmp_version` tinyint(3) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `snmp_port` mediumint(8) unsigned NOT NULL DEFAULT '161',
MODIFY COLUMN `rrd_num` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : poller_reindex - Failed
ALTER TABLE `poller_reindex`
MODIFY COLUMN `present` tinyint(3) unsigned NOT NULL DEFAULT '1',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : poller_time - Failed
ALTER TABLE `poller_time`
MODIFY COLUMN `pid` int(10) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : processes - Failed
ALTER TABLE `processes`
MODIFY COLUMN `timeout` int(10) unsigned DEFAULT "300",
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : reports - Failed
ALTER TABLE `reports`
MODIFY COLUMN `offset` int(10) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : reports_items - Failed
ALTER TABLE `reports_items`
MODIFY COLUMN `item_type` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `site_id` int(11) NOT NULL DEFAULT '-1',
MODIFY COLUMN `host_template_id` int(11) NOT NULL DEFAULT '-1',
MODIFY COLUMN `host_id` int(11) NOT NULL DEFAULT '-1',
MODIFY COLUMN `graph_template_id` int(11) NOT NULL DEFAULT '-1',
MODIFY COLUMN `align` tinyint(3) unsigned NOT NULL DEFAULT '1',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : settings - Failed
ALTER TABLE `settings`
MODIFY COLUMN `value` varchar(2048) NOT NULL DEFAULT '',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : settings_tree - Failed
ALTER TABLE `settings_tree`
MODIFY COLUMN `status` tinyint(4) NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : snmpagent_managers - Failed
ALTER TABLE `snmpagent_managers`
MODIFY COLUMN `id` int(10) unsigned NOT NULL auto_increment,
MODIFY COLUMN `max_log_size` tinyint(4) NOT NULL DEFAULT "0",
MODIFY COLUMN `snmp_version` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `snmp_port` mediumint(8) unsigned NOT NULL DEFAULT '161',
MODIFY COLUMN `snmp_message_type` tinyint(4) NOT NULL DEFAULT "0",
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : snmpagent_managers_notifications - Failed
ALTER TABLE `snmpagent_managers_notifications`
MODIFY COLUMN `manager_id` int(10) unsigned NOT NULL DEFAULT "0",
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : snmpagent_mibs - Failed
ALTER TABLE `snmpagent_mibs`
MODIFY COLUMN `id` int(10) unsigned NOT NULL auto_increment,
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : snmpagent_notifications_log - Failed
ALTER TABLE `snmpagent_notifications_log`
MODIFY COLUMN `id` int(10) unsigned NOT NULL auto_increment,
MODIFY COLUMN `severity` tinyint(4) NOT NULL DEFAULT "0",
MODIFY COLUMN `manager_id` int(10) unsigned NOT NULL DEFAULT "0",
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_auth - Failed
ALTER TABLE `user_auth`
MODIFY COLUMN `login_opts` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `policy_graphs` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `policy_trees` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `policy_hosts` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `policy_graph_templates` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `lastchange` int(11) NOT NULL DEFAULT '-1',
MODIFY COLUMN `lastlogin` int(11) NOT NULL DEFAULT '-1',
MODIFY COLUMN `lastfail` int(10) unsigned NOT NULL DEFAULT '0',
MODIFY COLUMN `reset_perms` int(10) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_auth_group - Failed
ALTER TABLE `user_auth_group`
MODIFY COLUMN `login_opts` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `policy_graphs` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `policy_trees` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `policy_hosts` tinyint(3) unsigned NOT NULL DEFAULT '1',
MODIFY COLUMN `policy_graph_templates` tinyint(3) unsigned NOT NULL DEFAULT '1',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_auth_group_perms - Failed
ALTER TABLE `user_auth_group_perms`
MODIFY COLUMN `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_auth_perms - Failed
ALTER TABLE `user_auth_perms`
MODIFY COLUMN `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_domains - Failed
ALTER TABLE `user_domains`
MODIFY COLUMN `defdomain` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : user_log - Failed
ALTER TABLE `user_log`
MODIFY COLUMN `result` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
PHP Notice: Undefined index: COLLATION in /var/lib/cacti/cli/audit_database.php on line 329
---------------------------------------------------------------------------------------------
Executing Alter for Table : vdef_items - Failed
ALTER TABLE `vdef_items`
MODIFY COLUMN `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=;
---------------------------------------------------------------------------------------------
Repair Completed! 0 Alters succeeded and 44 failed!
Top
saulofonseca
Posts: 40
Joined: Mon Jun 25, 2018 5:35 pm

Re: cli command - add graph (very slow )

Post by saulofonseca »

Here it is attached the:

innodb_index_stats;
innodb_table_stats;
Attachments
innodb_index_stats.txt
(261.73 KiB) Downloaded 182 times
innodb_table_stats.txt
(18.14 KiB) Downloaded 29 times
Top
saulofonseca
Posts: 40
Joined: Mon Jun 25, 2018 5:35 pm

Re: cli command - add graph (very slow )

Post by saulofonseca »

Here the output of explain with the \G option :

MariaDB [cactidb]> EXPLAIN SELECT dl.* FROM data_template AS dt INNER JOIN data_local AS dl 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 dt.id ORDER BY dt.name \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dl
type: ref
possible_keys: host_id_snmp_query_id,data_template_id
key: data_template_id
key_len: 3
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: dtr
type: ref
possible_keys: PRIMARY,duplicate_dsname_contraint,data_template_id,data_source_name
key: data_source_name
key_len: 78
ref: const
rows: 1
Extra: Using index condition; Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: dt
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: cactidb.dl.data_template_id
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: gti
type: ref
possible_keys: graph_template_id,local_graph_id_sequence,task_item_id,lgi_gti
key: task_item_id
key_len: 4
ref: cactidb.dtr.id
rows: 3
Extra: Using where
4 rows in set (0.002 sec)
Top
Post Reply

Return to “Help: General”

Who is online

Users browsing this forum: No registered users and 2 guests