boost: table 'poller_output_boost' is full

[toe_cutter]

Hi all!

So, i got to work this morning hoping it would be a nice day. And i notice that cacti is unusually slow. I check my logs and find these lines filled it:

Code: Select all

05/02/2011 07:22:42 AM - SPINE: Poller[0] ERROR: SQL Failed! Error:'1114', Message:'The table 'poller_output_boost' is full', SQL Fragment:'INSERT INTO poller_output_boost (local_data_id... 

My poller_output table is built like so:

Code: Select all

mysql> explain poller_output;
+---------------+-----------------------+------+-----+---------------------+-------+
| Field         | Type                  | Null | Key | Default             | Extra |
+---------------+-----------------------+------+-----+---------------------+-------+
| local_data_id | mediumint(8) unsigned | NO   | PRI | 0                   |       |
| rrd_name      | varchar(19)           | NO   | PRI |                     |       |
| time          | datetime              | NO   | PRI | 0000-00-00 00:00:00 |       |
| output        | varchar(250)          | NO   |     |                     |       |
+---------------+-----------------------+------+-----+---------------------+-------+

mysql> show create table poller_output;
+---------------+-------------------------------------------------------------------------------------+
| Table         | Create Table                                                                
+---------------+-------------------------------------------------------------------------------------+
| poller_output | CREATE TABLE `poller_output` (
  `local_data_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `rrd_name` varchar(19) NOT NULL DEFAULT '',
  `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `output` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`local_data_id`,`rrd_name`,`time`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+---------------+-------------------------------------------------------------------------------------+

And my poller_output_boost:

Code: Select all

mysql> describe 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          | datetime              | NO   | PRI | 0000-00-00 00:00:00 |       |
| output        | varchar(512)          | NO   |     | NULL                |       |
+---------------+-----------------------+------+-----+---------------------+-------+
4 rows in set (2.00 sec)


mysql> show create table poller_output_boost;
+---------------+-------------------------------------------------------------------------------------+
| Table               | Create Table                        
+---------------+-------------------------------------------------------------------------------------+
| poller_output_boost | CREATE TABLE `poller_output_boost` (
  `local_data_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `rrd_name` varchar(19) NOT NULL DEFAULT '',
  `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `output` varchar(512) NOT NULL,
  PRIMARY KEY (`local_data_id`,`rrd_name`,`time`),
  KEY `time_local_data_id` (`time`,`local_data_id`) USING BTREE,
  KEY `local_data_id` (`local_data_id`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+---------------+-------------------------------------------------------------------------------------+

I know varchar(512) in poller_output_boost is a bad idea. It is now altered accordingly to the README and output column is now varchar(250) along with default "".

My 'max_heap_table_size' is 4G, and the amount of rrd updates stored should only be about 900 000. I dump to SAN-disk once an hour.
So, the poller_output_boost shouldnt grow beyond some 450MB, and i have room for ~11million rows.
Any idea why this would be happening and poller_output_boost growing uncontrollably?

BR,
David

[gandalf]

Please pm TheWitness
R.

[grodno5]

having same errors even Boost plugin is disabled. Installed Bust plugin, enabled, then after number of errors disabled, but still getting errors:
ERROR: SQL Failed! Error:'1114', Message:'The table 'poller_output_boost' is full', SQL Fragment:'INSERT INTO poller_output_boost (local_data_id, rrd_name, time, output) VALUES ....."

any solutions for this??

[rsanto]

change the table engine from memory to inodb.

[ptaylor874]

We ran into this problem as well when building our new Cacti box. Initially, when it was only polling a few hosts, there was no problem. When we added the hosts from our production config (1800+ hosts, 7700+ data sources).

In addition to the 'poller_output_boost' table filling up, we saw errors as if the MySQL server went down. A quick check of the process table showed that not to be the case.

We fixed both issues by adding this to the MySQL config file:

Code: Select all

max_connections = 4000
max_heap_table_size = 1G

As a side benefit, we can keep running boost as a memory table now.