Cacti: Extremely slow new graph/DS creation in Cacti 1.0.3

[CapitalF]

I am having a problem where new graph and DS creation in a new Cacti 1.0.3 installation is taking an extremely long time. Something is very wrong, but I'm not sure where the problem is yet.

On average, new graphs (3-5 DS per) are taking 6-8 seconds to be created. This is an estimate based on eight different tests creating various graphs on some 48 port switches. Creating approximately 120 graphs with 240 DS ( about two per graph) took over 13 minutes and 48 seconds on average.

I set up a nearly-identical VM on the same hardware but used the Debian Cacti 0.8.8h+ds1-8 package with identical Device Templates and it took less than two seconds to create the same number of graphs.

The host system is sitting idle most of time during graph creation. There is a slow steady stream of DB activity during that time, but it's just slow. I think I have ruled out any MariaDB configuration or host hardware problems.

It's possible that some configuration I've made to this particular testing installation is related, but I'm not certain what it would be.

I need to enable debugging and capture logs to see what Cacti is doing internally. I will set up another new 1.0.3 installation on the VM and install from scratch to see if I can duplicate the problem on a completely fresh install.

I will post more info later. If you have debugging suggestions, please attach. Thanks.

[cigamit]

This is the reason that VM's are dangerous. Every time I've had an issue with a VM was when another VM on the same host was driving I/O to the point where there was no I/O left for my VM. Before you open bugs like this, please insure that you have done adequate research and at least tracked the source to something like a hung query, or SQL errors. You should always enable the slow query log in MySQL, and if you are worried, set the slow query time to 2 seconds.

There are only a few things that slow down a Cacti system:

0) A slow VM on an overloaded master host (common)
1) I/O wait (common, and reason for boost and solved by flash)
2) Wait due to insufficient memory (swap) (common, and sometimes leads to 1)
3) Improperly tuned MySQL or MariaDB database (should be less common with Cacti 1.0 if you follow recommendations)
4) Race condition (bug), which would be seen by a php process running constantly at 100% (should be rare)

I hope that helps.

[CapitalF]

You have misread my statements. The problematic Cacti 1.03 installation is running directly on hardware, not on a VM. The older 0.8 system is running on a VM, on the same hardware.

I am absolutely certain there is no disk IO problems. There are no other applications running on this hardware. The hardware is sitting mostly idle while Cacti waits on creating the new items.

So far the MariaDB slow query log is empty with a 1-second trigger, and the stats table indicates that everything is running quickly.

[cigamit]

One thing on Cacti 1.0.x is that if you have a large number of objects on a page is large, there will be some delay drawing the page. You can assess this pretty well in chrome using it's tracing feature. From there, you can see if the delay is client side or server side. Try that and let us know what happens. If you have trouble with interpreting the tracing, you can export it and upload here.

[cigamit]

Going to reopen this issue. I would like you to do the following for us:

1) Turn on the MySQL/MariaDB slow_query_log
2) Set it to 2 seconds interval time
3) Do the graph creation as you did before.
4) Upload the log. Set to 1 second if you don't get results at 2.
5) Run the following command 'mysql -e "show table status" cacti | grep -i myisam'. Upload the results
6) Confirm that you have tuned your database per the settings in Technical Support

Thanks!

[cigamit]

I believe I found the root cause of this and it is corrected. Please test away and provide feedback.

[cigamit]

I'm pretty sure this one is good now. I'm just going to close.

[CapitalF]

This is still a problem in Cacti 1.1.7.

I just tried to add a new switch to this test system after upgrading and it's still super slow.

I will try to do some debugging as previously requested to try and give you a clue about what is going on. just leave this ticket open for awhile. I may be slow in replying.

The main system I'm using to test with has fairly slow hard drives (on purpose, I use these to ID issues), but I don't think this is a disk IO problem. While the system is trying to add these new graphs, the existing poller is working fine in the background and updating all of those existing DSs.

In fact, I just hit 100K data sources. You can see how slowly these DSs are being added in syslog. I'm maxing out at 20-30 new data sources per MINUTE:

May 23 20:18:40 myhost Cacti[14034]: SYSTEM: STATS: Time:35.3924 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9581 RRDsProcessed:2083 May 23 20:19:38 myhost Cacti[14134]: SYSTEM: STATS: Time:36.9194 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9620 RRDsProcessed:2086 May 23 20:20:35 myhost Cacti[14239]: SYSTEM: STATS: Time:33.7271 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9659 RRDsProcessed:2089 May 23 20:21:43 myhost Cacti[14343]: SYSTEM: STATS: Time:39.1887 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9698 RRDsProcessed:2092 May 23 20:22:41 myhost Cacti[14447]: SYSTEM: STATS: Time:39.2623 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9737 RRDsProcessed:2096 May 23 20:23:39 myhost Cacti[14544]: SYSTEM: STATS: Time:36.6225 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9789 RRDsProcessed:2099 May 23 20:24:43 myhost Cacti[14644]: SYSTEM: STATS: Time:42.5960 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9807 RRDsProcessed:2108 May 23 20:25:46 myhost Cacti[14734]: SYSTEM: STATS: Time:45.0025 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9829 RRDsProcessed:2119 May 23 20:26:53 myhost Cacti[14833]: SYSTEM: STATS: Time:44.0334 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9849 RRDsProcessed:2129 May 23 20:27:51 myhost Cacti[14930]: SYSTEM: STATS: Time:48.9735 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9867 RRDsProcessed:2132 May 23 20:28:50 myhost Cacti[15029]: SYSTEM: STATS: Time:42.4382 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9885 RRDsProcessed:2135 May 23 20:29:49 myhost Cacti[15128]: SYSTEM: STATS: Time:41.2973 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9903 RRDsProcessed:2138 May 23 20:31:39 myhost Cacti[15233]: SYSTEM: STATS: Time:92.9734 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9927 RRDsProcessed:2142 May 23 20:31:50 myhost Cacti[15534]: SYSTEM: STATS: Time:43.6022 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9945 RRDsProcessed:2145 May 23 20:32:39 myhost Cacti[15618]: SYSTEM: STATS: Time:36.1159 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9957 RRDsProcessed:2147 May 23 20:33:42 myhost Cacti[15748]: SYSTEM: STATS: Time:37.1754 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9975 RRDsProcessed:2150 May 23 20:34:42 myhost Cacti[15833]: SYSTEM: STATS: Time:40.7091 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:9999 RRDsProcessed:2154 May 23 20:35:40 myhost Cacti[15935]: SYSTEM: STATS: Time:39.0709 Method:cmd.php Processes:64 Threads:N/A Hosts:21 HostsPerProcess:1 DataSources:10023 RRDsProcessed:2159

[CapitalF]

Here, this is more readable. This is from the same lines above:

DataSources:9581
DataSources:9620
DataSources:9659
DataSources:9698
DataSources:9737
DataSources:9789
DataSources:9807
DataSources:9829
DataSources:9849
DataSources:9867
DataSources:9885
DataSources:9903
DataSources:9927
DataSources:9945
DataSources:9957
DataSources:9975
DataSources:9999
DataSources:10023

[sajw1991]

I had the same issue with 1.1.29.

I solved it with these SQL settings in /etc/mysql/my.cnf.

max_heap_table_size = 16M
max_allowed_packet = 128M
tmp_table_size = 32M
join_buffer_size = 32M
innodb_buffer_pool_size = 768M
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_doublewrite = OFF
innodb_flush_log_at_timeout = 10
innodb_read_io_threads = 32

innodb_write_io_threads = 16

Hope this works for u!

[netniV]

Tip: You should really add any custom mysql settings into a separate configuration file, such as zzz_cacti.cnf in the /etc/mysql/mysql.conf.d/ folder so that you are not overriding the package file.

If I remember rightly, files in that directory are parsed in alphabetical order so files starting with numbers are applied in numeric order, A-Z order then a-z order.

[sajw1991]

netniV, Thanks for the tip!
One question on that, if I have a custom my.cnf in /etc/mysql/mysql.conf.d/, how do I associate that with my Cacti DB/installation?

[netniV]

You don't. All settings are global as normally you only have a single MySQL instance running configuration from the /etc/mysql/mysql.cnf file which includes /etc/mysql/mysql.conf.d/ files when it loads.

The only reason I would specify zzz_cacti.cnf in the file name is because these settings are to make the system cacti compliant. The actual name is up to you, but remember any other files in there will be parsed in the order I mentioned above.

If you are running multiple instances of MySQL that's not something I've looked into myself yet so I wouldn't be able to comment on how that works.