Tuning Cacti 1.0.x performance for large sites, running on CentOS 7 with MariaDB

Menu => Settings => Poller
Poller Type => spine

Menu => Settings => Paths
Spine Binary File Location => /usr/local/spine/bin/spine
Spine Config File Path => /usr/local/spine/etc/spine.conf

We don't have a config file yet, so
# cp /usr/local/spine/etc/spine.conf.dist /usr/local/spine/etc/spine.conf
# vi /usr/local/spine/etc/spine.conf
... modify DB_User, DB_Pass etc

Menu => System Utilities => View Boost Status

By default, cacti 1.0.1 has chosen InnoDB engine for Boost Storage, and that is why it shows unlimited on table size and maximum records (well, unlimited as long as your hard disk has enough free space).  As I remember, the DB engine used to be "MEMORY" in Cacti 0.8.x with the "boost" plugin, which would give maximum performance with some limitations. First, the maximum allowed table size and records would have a limit, i.e. the amount of RAM, and you really don't want to go over that limit.  Second, you will have to do some calculations in order to have the configuration set correctly.
...

Boost Storage Statistics
Database Engine:	InnoDB
Current Boost Table(s) Size:	16.00 KBytes
Avg Bytes/Record:	2 KBytes
Max Allowed Boost Table Size:	Unlimited
Estimated Maximum Records:	Unlimited Records

...

Steps:

Goto Menu => Settings => Performance
My initial settings are
- Enable On-demand RRD Updating => Checked
- How Often Should Boost Update All RRDs => 1 hour
- Maximum Records  => 100000
- Enable direct population of poller_output_boost table => Checked
- Enable Image Caching => Checked
- Location for Image Files  => /var/www/html/cacti/cache/boost/

Configure database
Configure heap table size, and restart MariaDB.  "128M" is just an example, it is likely to be on gigabyte level for large sites.

#vi /etc/my.cnf.d/server.cnf
[mysqld]
max_heap_table_size = 128M

systemctl restart mariadb

# mysql -p cacti

> alter table poller_output_boost ENGINE=MEMORY;

Now go back to Menu => System Utilities => View Boost Status

The engine type has become "MEMORY" and the table size is no longer unlimited. 

Boost Storage Statistics
Database Engine:	MEMORY
Current Boost Table(s) Size:	124.28 KBytes
Avg Bytes/Record:	25 KBytes
Max Record Length:	30 Bytes
Max Allowed Boost Table Size:	120.35 MBytes
Estimated Maximum Records:	4.958 K Records

If you do some math, you may notice that the data is taking more storage than it should.  This is because in MySQL/MariaDB, MEMORY tables use fixed-length row-storage format. So the default varchar(512) in the output column would just become char(512) and thus waste a lot of space.  If you know your polling results would not exceed a certain length, please do adjust the varchar length for the output column.

e.g. alter table poller_output_boost modify column output varchar(128);

So we have "Maximum records" set to 100,000 and update interval set to hourly.  Now you have to calculate whether your heap table can hold 100000 records or not.  You see the "Estimated Maximum Records" is only 4.957K here, and say "oh no it's gonna blow up my heap table".  Well this is not exactly true.  The "Avg bytes/record" is 25K bytes, which is not possible if you look at the poller_output_boost table structure seriously.   This is only because database system is allocating memory in chunks, in this case ~128K a time.

From the "show table..." statement below, we know that the maximum data length is 109506040, and the average row length is 452. So we have 109506040 / 452 bytes => 200,000+ records

> desc poller_output_boost;

+---------------+-----------------------+------+-----+---------------------+-------+

| Field         | Type                  | Null | Key | Default             | Extra |

+---------------+-----------------------+------+-----+---------------------+-------+

| local_data_id | mediumint(8) unsigned | NO   | PRI | 0                   |       |

| rrd_name      | varchar(19)           | NO   | PRI |                     |       |

| time          | timestamp             | NO   | PRI | 0000-00-00 00:00:00 |       |

| output        | varchar(128)          | YES  |     | NULL                |       |

+---------------+-----------------------+------+-----+---------------------+-------+

> show table status where name ='poller_output_boost';

+---------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| Name                | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |

+---------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| poller_output_boost | MEMORY |      10 | Fixed      |   35 |            452 |      127224 |       109506040 |         1596 |         0 |           NULL | 2017-02-12 23:50:52 | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         |

Finally, since the intermediate polling results are in memory, please remember to flush the data when you need to restart the database or the server.

# cd /var/www/html/cacti

# php poller_boost.php  --force