Cacti (home)ForumsDocumentation
Cacti: offical forums and support
It is currently Fri Aug 18, 2017 1:14 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Large Cacti Setups..
PostPosted: Mon Apr 07, 2014 4:19 am 
Offline

Joined: Tue Jun 05, 2012 6:24 am
Posts: 11
Location: Athens, Greece
Hi,

I have 2 Cacti large scale setups. Each one have nearly 2000 devices and 250k data sources.

I am using boost and multiserver plugins, and the last few months I am experiencing problems with the DB performance. I have made a few changes, that dramaticaly change the performace of the database.

Cacti Poller Process
  • I have added SQL_NO_CACHE statement on all SELECT queryies that dont need to be cached. Most of the Poller SELECT queries don't need to be cached. This way, MySQL has more cycle to process real data, and more cache memory to store queries that really need to be cached, such as Graph Permitions, Graph Tree, etc
  • I have change the sleep time on the Poller.php main loop from 500uSec to 10000uSec. With 500uSec, there is a tremendus number of queries just query if a poller_process has ended the process. I prefered the latency than killing my db.

Multiserver Plugin
  • On poller_boost.php. I have changed the way, boost_update is checking if remaining data are present on the temp table. I dont care who many remaining data are on the table, just that the are.
    Code:
          #$rows = db_fetch_cell("SELECT count(*) FROM $archive_table");
          $rows = db_fetch_cell("SELECT EXISTS(SELECT 10 FROM $archive_table)");
  • When multiserver is enable, there is a join query beteen poller_output and poller_item that is filter on poller_id. But poller_id, by default is only present on poller_item table. This lead on huge tmp tables. I have altered poller_output table and added poller_id. I also changed spine to populate the poller_id on poller_output table and chagne the join query.

    Code:
            /* create/update the rrd files */
            //$results = db_fetch_assoc("select
            //      poller_output.output,
            //      poller_output.time,
            //      UNIX_TIMESTAMP(poller_output.time) as unix_time,
            //      poller_output.local_data_id,
            //      poller_item.rrd_path,
            //      poller_item.rrd_name,
            //      poller_item.rrd_num
            //      from (poller_output,poller_item)
            //      where (poller_output.local_data_id=poller_item.local_data_id and poller_output.rrd_name=poller_item.rrd_name and poller_item.poller_id='$poller_server_id')
            //      $limit");

            /* change by vang!!! */

        $results = db_fetch_assoc("SELECT SQL_NO_CACHE  poller_output.output,
                    poller_output.time,
                    UNIX_TIMESTAMP(poller_output.time) as unix_time,
                    poller_output.local_data_id,
                    poller_item.rrd_path,
                    poller_item.rrd_name,
                    poller_item.rrd_num
            FROM poller_output
            LEFT JOIN poller_item
            ON poller_output.local_data_id=poller_item.local_data_id
            AND poller_output.rrd_name=poller_item.rrd_name
            WHERE poller_output.poller_id='$poller_server_id' $limit ");

  • By default Cacti's function process_poller_output() does have the poller_id. With multiserver, this function needs to know the poller_id and hence it is querying the db for that info. I have altered the function to accept poller_id as input and comment out the queries.

    On lib/poller.php
    Code:
    #function process_poller_output(&$rrdtool_pipe ,$remainder = FALSE) {
    function process_poller_output(&$rrdtool_pipe,$poller_server_id ,$remainder = FALSE) {

            //$server_remote_name = gethostname(); 
            //$poller_server_id = db_fetch_cell("SELECT SQL_NO_CACHE id FROM poller_server WHERE name ='$server_remote_name' AND aktive = 'on'");


  • Mainly, after multiserver the db is lucking of right INDEXes, since most of the processes need the poller_id on the querys.

Regards,
Vangelis

_________________
Cacti 0.8.8a with Boost and Multiserver Poller Plugin
Over 2.5k devices and 300k datasources!


Top
 Profile  
 
 Post subject: Re: Large Cacti Setups..
PostPosted: Thu Apr 17, 2014 6:57 am 
Offline

Joined: Thu Sep 27, 2012 5:24 am
Posts: 17
Hi Hol,

thanks for your input and tips! I´m always interested to see how other people set up large scale monitoring with cacti.
I did check out multiserver but it doesn´t really work across WAN-links when you have 2 seperate RRD-Caches.
Our setup has roughly 1.2k hosts and about 20k DSs but so far I´ve never run across DB-Perfomance issues tbh.
I avoided the boost plugin so far since we were able to put all RRDs into RAM-drive, so polling cycles are about
9-20s (including all sorts of scripts like WMI). The only tweaks I applied are some DB-synchronisations between my 2 Cacti-Servers (hosts, tree, etc.).
If we ever run into DB issues I´ll be sure to revisit your entry :).

Cheers!


Top
 Profile  
 
 Post subject: Re: Large Cacti Setups..
PostPosted: Mon Jan 19, 2015 9:37 am 
Offline

Joined: Mon May 17, 2010 2:41 am
Posts: 43
Thanks for your Post.

Im Really interessted to Setup my Cacti like yours. How did you install the Multipollerserver Plugin? It works without Problems?
Do you have a Master Slave Replication for your mysql?

It would really nice if you can help me to Setup my Cacti like yours. PM also ok.

Much Thanks
Great_f


Top
 Profile  
 
 Post subject: Re: Large Cacti Setups..
PostPosted: Sun Feb 15, 2015 3:31 am 
Offline

Joined: Sun Feb 15, 2015 2:21 am
Posts: 1
interesting post! though i've never set up that scale to benefit from it.
that should help the CACTI designer to improve.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

Protected by Anti-Spam ACP Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group