The table 'poller_output' is full

[MI1]

Greetings, anwice needed, Cacti is failing, aprox 12x per 24 hours, there are blank spaces around all graphs, it looks like this could be and MySQL issue but since i can also see error like The "table 'poller_output' is full" this could have something to do with pooler. When graph stops i can see errors like this in MySQL error log:

Code: Select all

head -n 200 /var/lib/mysql/my_hostname.err

130129  6:41:22 [ERROR] /usr/sbin/mysqld: The table 'poller_output' is full
130129  6:41:22 [ERROR] /usr/sbin/mysqld: The table 'poller_output' is full
130129  6:44:45 [ERROR] /usr/sbin/mysqld: The table 'plugin_camm_syslog_incoming' is full
130129  6:44:45 [ERROR] /usr/sbin/mysqld: The table 'plugin_camm_syslog_incoming' is full

Also in PHPMYADMIN I can see that pooler output table has around 1000 KiB overhead [this value is changing, but its still overhead] What should I do with this table?

Another thing is that when all running OK, there are around 100 max conn teMySQL network usage, if this value gets higher then cacti start freeze and there are no graphs drawn for some time.

Please advice how to resolve this situation, or what my next steps should be.

[MI1]

Additional info, there was manage_uptime_method table crashed, after repair there are no overhead in pooler_output table. Few hours later there are messages about " [ERROR] /usr/sbin/mysqld: The table 'poller_output' is full" all the time and Cacti if failing again.

[gandalf]

Table poller output is heavily written to. It stores all data from polling. This data is then offloaded to rrd files and scratched. This repeats for each polling interval. I never heard of an issue of a poller_output table being filled up like that. So I suspect some specific mysql setting, that might have such an effect. My "default setup" runs since months and months without such an issue ...
R.

[MI1]

Thx, yes, i can confim that this issue is not standard, main reason this table is full is because Camm plugin integration, syslog-ng is beying processed into MySQL which is causing troubles.

[gandalf]

Would it help to have some daily mysql table housekeeping? Like "optimize table ..."?
R.

[MI1]

That was my fist clue, but it seems that this 'poller_output' table gets full when one of Camm tables [plugin_camm_syslog_incoming] gets full and this table cannot be optimized as such because uses storage engine MEMORY.

Code: Select all

SHOW TABLE STATUS WHERE Name = 'plugin_camm_syslog_incoming';
+-----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| 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 |
+-----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| plugin_camm_syslog_incoming | MEMORY |      10 | Fixed      | 8409 |            520 |    12569056 |        14836640 |      1489408 |   7226440 |       17121810 | NULL        | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+-----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+ 

Next step would be check if that table was created by Camm plugin or by hand [previous admin who left company] and if would be wise to convert it to MyISAM.

[gandalf]

agreed
R.

[MI1]

So the table plugin_camm_syslog_incoming was obvoiously created as MEMORY by author of Camm plugin. This was not that hard to find.

Code: Select all

CREATE TABLE  `plugin_camm_syslog_incoming` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `host` varchar(128) default NULL,
   `sourceip` varchar(45) NOT NULL,
   `facility` varchar(10) default NULL,
   `priority` varchar(10) default NULL,
   `sys_date` datetime default NULL,
   `message` varchar(255),
   `status` tinyint(4) NOT NULL default '0',
   `alert` tinyint(3) NOT NULL default '0',
   PRIMARY KEY  (`id`),
   KEY `facility` (`facility`),
   KEY `priority` (`priority`),
   KEY `sourceip` (`sourceip`),
   KEY `status` (`status`),
   KEY `status_date` (`status`,`sys_date`),
   KEY `sys_date` (`sys_date`),
   KEY `alert` (`alert`)
 ) ENGINE=memory DEFAULT CHARSET=latin1;

If not properly rotated [db/tables] this can cause that this table and 'poller_output' can get full and that will cause Cacti disfunction.