Monitoring zabbix[history] parameter

[ysus]

Hi,

Official Zabbix manual says "Do not use if MySQL InnoDB, Oracle or PostgreSQL is used!" for zabbix[history] parameter.

https://www.zabbix.com/documentation/3.2/manual/config/items/itemtypes/internal

Any idea why they put this restriction?

I would like to monitor the size of database tables without any select count queries which take a long time to execute.

Thanks,
Yuri

[kloczek]

Hi,

Official Zabbix manual says "Do not use if MySQL InnoDB, Oracle or PostgreSQL is used!" for zabbix[history] parameter.

https://www.zabbix.com/documentation/3.2/manual/config/items/itemtypes/internal

Any idea why they put this restriction?

I would like to monitor the size of database tables without any select count queries which take a long time to execute.

None of those engines provides current counter with number table rows. Only way to count those rows is by do full scan of whole table and this operation is very heavy. Effectively started full scan can block other write and read operations on such table.
history* tables are biggest part of the zabbix DB and scanning them almost instantly is causing that all MFU/MRU cached data are flushed away and whole interaction with database even with quite fast storage is causing big slowdown interaction with database.
Bigger database than this impact is even bigger.

[ysus]

Only way to count those rows is by do full scan of whole table and this operation is very heavy

Sure not. See simple query below and how long it takes.

# time mysql -u*** -p*** -e 'SELECT table_name "Table Name", table_rows "Rows Count" FROM information_schema.TABLES WHERE table_schema = "zabbix"' | grep history

history 1544123
history_log     0
history_str     108011
history_text    0
history_uint    26837319
proxy_dhistory  0
proxy_history   0

real    0m0.021s
user    0m0.004s
sys     0m0.002s

I could run this command and parse the output as agent's UserParamenter but prefer to use build-in functions which for some reasons are not recommended by developers.

[kloczek]

I could run this command and parse the output as agent's UserParamenter but prefer to use build-in functions which for some reasons are not recommended by developers.

Yes you are right that it is possible queries against information_schema.TABLES. Problem only is that vales stored in this table IIRC are not real values (cannot find ATM documentation abut this).

[ysus]

vales stored in this table IIRC are not real values

Could you please explain? Sorry I do not understand what you mean, my databases experience is very limited :-(

[kloczek]

Could you please explain? Sorry I do not understand what you mean, my databases experience is very limited :-(

Values representing number of rows and other like free space in tables are IIRC kind of estimations.

[ysus]

Yes, now I found it in documentation - "For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)"

http://dev.mysql.com/doc/refman/5.7/...les-table.html

However for big tables those estimations are quite close to SELECT COUNT(*), see last column below:

| history                    |    1828857 |          145.23 | |  1827233 |
| history_uint               |   25447767 |         2218.25 | | 23431635 |

| trends                     |      34266 |            6.52 | |    50825 |
| trends_uint                |     587116 |           90.63 | |   632559 |
| triggers                   |       1559 |            0.50 | |     1612 |

Thanks.
Thread closed.