cli command - add graph (very slow )

[saulofonseca]

Hello all,

It is my third cacti server having this same situation:

I have a script that calls a file that contains the serial number of many ONUs. Then it creates the graphs.
Follow:

----------------------------------------------
[root@localhost autom]# cat nomes.sh
arq="nomes.txt"
while IFS= read -r USERNAME
do

/usr/share/cacti/cli/add_graphs.php --graph-type=cg --graph-template-id=175 --host-id=48 --graph-title=$USERNAME --input-fields="69:serial=$USERNAME 182:serial=$USERNAME 183:serial=$USERNAME 184:serial=$USERNAME" --force

done < "$arq"


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

[root@localhost autom]# cat nomes.txt
SUMCb2893b9c
STGUbc0615d8
STGUbc058418
FHTT07f350a8
SU10b203a5e3
STGUbc04df98
SU10b20391f9
and so on....more 2k lines...

What happens:

It start creating the graphs very fast.....about 100 graphs in 2 minutes,,,,,,then it start to be slow....then more slow....and finally it creates only 1 or 2 graphs per minute......
I notice that only one cpu it's beeing used to make this operation (100% usage).....

What should I do to fix this ???? so I can create this 2k graphs more quickly.

Regards

[TheWitness]

You need to be more specific. What processes are slowing the system, what are their arguments?

[saulofonseca]

Looks like mysql is the problem. Please, guide me.

Follows attached my cacti Technical Support [Summary]


This is my htop screen:

Captura de tela de 2023-08-05 17-45-50.png (216.08 KiB) Viewed 1537 times

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

This is my conf:

Cacti 1.2.23

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

-------------------------------------------------------------------
[root@localhost my.cnf.d]# cat /etc/os-release
NAME="CentOS Linux"
VERSION="8"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Linux 8"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-8"
CENTOS_MANTISBT_PROJECT_VERSION="8"

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


[root@localhost my.cnf.d]# cat mariadb-server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4
max_heap_table_size=256M
tmp_table_size=256M
join_buffer_size=32M
#sort_buffer_size=500M
# 25% Of Total System Memory
innodb_buffer_pool_size=4096M
# pool_size/128 for less than 1GB of memory
innodb_buffer_pool_instances=64
innodb_flush_log_at_timeout=3
innodb_read_io_threads=32
innodb_write_io_threads=16
innodb_io_capacity=5000
innodb_file_format=Barracuda
innodb_large_prefix=1
innodb_io_capacity_max=10000
innodb_doublewrite=off
#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.3]

[Osiris]

Do a MySQL top command as root. Close to this, also, take you buffer pool instances to 1. You don't have enough memory

Code: Select all

#!/bin/sh

while true; do
   clear
   uptime
   mysql -e "show processlist" | grep -v Sleep`
   sleep 2
 done
 

[saulofonseca]

Hello again,

Here it is:

Id User Host db Command Time State Info Progress
3 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000
1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
2 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000
30753 root localhost cactidb Query 20 Sending data SELECT dl.* FROM data_template AS dt INNER JOIN data_local AS dl ON dl.data_template_id=dt.id INNER 0.000
30755 root localhost NULL Query 0 Init show processlist 0.000

[saulofonseca]

MariaDB [(none)]> show full processlist;
+-------+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 30769 | root | localhost | cactidb | Query | 3 | Sending data | 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 | 0.000 |
| 30776 | root | localhost | NULL | Query | 0 | Init | show full processlist | 0.000 |
+-------+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
7 rows in set (0.000 sec)

[saulofonseca]

Hello,

Looks like It takes 22 seconds to process each --input-fields...

[Osiris]

What version of database? Can you run an explain command on one of those queries?

[Osiris]

Also, are you using virtual or physical disk? Is this a VM?

[saulofonseca]

Ok, I will try to execute the explain command.....never done it before.... The process of graph create is still being executed.....it have created 1800 graph till now.....

But for other times that I have done this, it will continue to be slow even for the Cacti GUI graph creation....for each time that I try to create....even one graph....will continue to be slow....

Only if I fresh install cacti....and execute the command....then will be fast again......but only for the first 100 graphs.....

I tryed with different hardware.....still same result...(I have 4 Cacti servers on four different locations)...
The only thing to mention is that all instances was always virtual....never done it without virtualization ....

Database info:

Captura de tela de 2023-08-06 13-13-22.png (37.97 KiB) Viewed 1417 times

Captura de tela de 2023-08-06 13-13-38.png (31.17 KiB) Viewed 1417 times

[TheWitness]

What type of vdisk? Are you sure you don't have a noisy neighbor? Have you figured out how to run explain yet?

[saulofonseca]

Hello again,

Sorry for delayed response....

I'm using vmware:
.vmdk
VMFS6
Thick provisioned, lazily zeroed.

When u say noyse neighbor, you mean other machine that is consuming all resorses ??? If so, i'm sure that is not the case.

About (explain)......

The below command is correct ?? Does it help ??? .....the graph process creation was not working on the moment that i ran this command....just to inform


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

MariaDB [(none)]> use cactidb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
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 ;
+------+-------------+-------+--------+----------------------------------------------------------------+------------------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+----------------------------------------------------------------+------------------+---------+-----------------------------+------+----------------------------------------------+
| 1 | SIMPLE | dl | ref | host_id_snmp_query_id,data_template_id | data_template_id | 3 | const | 2302 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | dt | eq_ref | PRIMARY | PRIMARY | 3 | cactidb.dl.data_template_id | 1 | |
| 1 | SIMPLE | dtr | ref | PRIMARY,duplicate_dsname_contraint,data_template_id | data_template_id | 3 | cactidb.dl.data_template_id | 28 | Using where |
| 1 | SIMPLE | gti | ref | graph_template_id,local_graph_id_sequence,task_item_id,lgi_gti | task_item_id | 4 | cactidb.dtr.id | 3 | Using where |
+------+-------------+-------+--------+----------------------------------------------------------------+------------------+---------+-----------------------------+------+----------------------------------------------+
4 rows in set (0.021 sec)

[TheWitness]

You might benefit from creating an index on data_source_name;

Code: Select all

ALTER TABLE data_template_rrd ADD INDEX data_source_name(data_source_name);

Do that and let me know if it fixes the performance.

[saulofonseca]

Hello again, I did what u asked, but is still slow when I run the script. Looks like it's slower than before...
I measure it, and it takes about 4 minutes to create a graph.

Here what I've done:

MariaDB [cactidb]> ALTER TABLE data_template_rrd ADD INDEX data_source_name(data_source_name);
Query OK, 0 rows affected (0.142 sec)
Records: 0 Duplicates: 0 Warnings: 0

[Osiris]

Can you run the audit databasecli script and check for missing indexes?