mysqld: The table 'poller_output' is full

[toni2816]

Hi All,

I;m face problem about table poller_output is full on mariadb, how to fix this issue ??

Thank you

[macan]

is your poller finishing on time?
More advices is here

http://forums.cacti.net/viewtopic.php?t=61651

[toni2816]

I see the poller status idle and I was use spine

[macan]

Logs->choose Type Stats and show few lines

[TheWitness]

Code: Select all

ALTER TABLE poller_output ENGINE=InnoDB;

Then, edit /etc/my.cnf.d/server.cnf and increase max_heap_table_size=Bigger#, then restart MariaDB/MySQL and after that:

Code: Select all

ALTER TABLE poller_output ENGINE=MEMORY;

With the memory table, the poller is real fast, but you have to ensure your server has enough memory to handle the data in it, and if you are in a growing spert (more graphs and data sources), you have to be careful that you don't exceed the limit.

If you system is going to always be small, you can keep it in InnoDB.

[toni2816]

Code: Select all

ALTER TABLE poller_output ENGINE=InnoDB;

Then, edit /etc/my.cnf.d/server.cnf and increase max_heap_table_size=Bigger#, then restart MariaDB/MySQL and after that:

Code: Select all

ALTER TABLE poller_output ENGINE=MEMORY;

With the memory table, the poller is real fast, but you have to ensure your server has enough memory to handle the data in it, and if you are in a growing spert (more graphs and data sources), you have to be careful that you don't exceed the limit.

If you system is going to always be small, you can keep it in InnoDB.

Thank you, but how to calculate max_heap_table_size value ?

[TheWitness]

You can go to Console > Utilities > System Utilities > View Boost Status while in Memory Engine and see how much memory and the row size. You basically multiply your number of data sources by the average row size and then I would add 100% just to be safe. Start out with something 3x what you already had though so you don't crash it.

[toni2816]

You can go to Console > Utilities > System Utilities > View Boost Status while in Memory Engine and see how much memory and the row size. You basically multiply your number of data sources by the average row size and then I would add 100% just to be safe. Start out with something 3x what you already had though so you don't crash it.

Thanks in advance for the support, I was monitor this case in last 2 weeks and isn't appear anymore.

[TheWitness]

Good to hear. Having the poller_output table as a memory table is a key performance requirement for high levels of scalability not so much any more for the poller_output_boost as we no longer delete records from it as a general practice (RRDtool 1.5+ and above a requirement there).

Anyway, to reduce the memory footprint of the poller_output table, avoiding data input methods that have very wide outputs is also important as the 'output' column's width in the poller_output table should be minimized as much as possible to allow more records to be held without blowing out the memory buffer.

The reason that the boost view was important (or I thought it was) was that it would give you a good look at the average and more importantly, the max width of the output column. But looking at it today, it's not showing the 'max'. So, something to fix or enhance in the future.

[toni2816]

Good to hear. Having the poller_output table as a memory table is a key performance requirement for high levels of scalability not so much any more for the poller_output_boost as we no longer delete records from it as a general practice (RRDtool 1.5+ and above a requirement there).

Anyway, to reduce the memory footprint of the poller_output table, avoiding data input methods that have very wide outputs is also important as the 'output' column's width in the poller_output table should be minimized as much as possible to allow more records to be held without blowing out the memory buffer.

The reason that the boost view was important (or I thought it was) was that it would give you a good look at the average and more importantly, the max width of the output column. But looking at it today, it's not showing the 'max'. So, something to fix or enhance in the future.

I'm using InnoDB by the way, and I saw couple graphs has gap/blank graph but I don't know it's happen cause this tuning or something else, maybe any suggestions