monitoring size own zabbix's db

[wilp]

Hi!

I wonder how can to monitor size own zabbix's database, MySQL database?

[kloczek]

https://github.com/kloczek/zabbix-te...ervice%20MySQL
Above template has LLD which staps set of items per database like size of the data. size of the indexes.
That template provides screen "SVC::MySQL:: DBs" which provides per database graph with data and indexes size.

[wilp]

many thanks!

[wilp]

kloczek, thanks for your answer!

I did this steps:

1. created user with name monitoring in mysql:

mysql>
CREATE USER 'monitoring'@'localhost' IDENTIFIED BY 'monitoring';
GRANT SELECT, INDEX, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'monitoring'@'localhost';
FLUSH PRIVILEGES;

2. Attached this template (Service MySQL.xml) on host Zabbix server.
3. And i see thois error 'Remote commands are not enabled' on Items zabbix server's host

Would you help me to resolve this this error?

[kloczek]

Yeah .. I need to add a bit more documentation about setup in template description :/

Here is the full description:
in "Service MySQL" template macros you can find "{$MYSQL_CMD}" macro with value "HOME=/var/lib/zabbix /usr/bin/mysql --login-path=monitoring -Nse"
As you see it means that mysql command will be looking for .mylogin.cnf file in /var/lib/zabbix.
All what you need to do is:

# mkdir -p /var/lib/zabbix
# chwon zabbix:zabbix /var/lib/zabbix
# chmod 700 /var/lib/zabbix
# su - zabbix -s /bin/bash -c "mysql_config_editor set --login-path=monitoring --user monitoring --password"

Than type "monitoring" as password.
As GRANT query grants to special monitoring user only permission to read some system tables only from localhost password used by that mysql user can be even well known (like "monitoring").
$HOME env variable needs to be passed because mysql command is looking for $MOME/.mylogin.cnf file with stored by mysql_config_editor credentials (system.run[] does not add $HOME env variable on execve())
Above generally is to not store/preserve anywhere in the zabbix data user password used to monitoring MySQL engine.

If you will do above you should see monitoring data in "Latest data" of the host with that template.

[wilp]

kloczek, many thanks!

I got it!

Would you please tell me what is different between values zabbix:::index size and zabbix:::data size ?
And their sum is value equal this ?:

mysql> SELECT table_schema "databases name", sum(data_length + index_length)/1024/1024  "DВ size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------+
| databases name     | DВ size in MB  |
+--------------------+----------------+
| information_schema |     0.15625000 |
| mysql              |     2.75522137 |
| performance_schema |     0.00000000 |
| sys                |     0.01562500 |
| zabbix             |    13.46875000 |
+--------------------+----------------+
5 rows in set (0.15 sec)

[kloczek]

information_schema, performance_schema and sys tables are effectively in memory databases and this is why "MySQL:B:" LLD filters off those databases.
As you see sizes of those tables effectively are negligible.

What actually is sampled is in LLD items definitions:

{#DB}::data size    system.run[{$MYSQL_CMD} "SELECT SUM(data_length) FROM information_schema.tables WHERE table_schema='{#DB}';",]
{#DB}::index size    system.run[{$MYSQL_CMD} "SELECT SUM(index_length) FROM information_schema.tables WHERE table_schema='{#DB}';",]

In template description notes is link to http://dev.mysql.com/doc/refman/5.7/...on-schema.html where is full description of the information_schema.
What exactly is in that items which are part of the set of items per each table is described on https://dev.mysql.com/doc/refman/5.7...ics-table.html

BTW: feel free to add more items and submit git PR if you need more metric
Temporary I'm busy doing other things and only in about month will be able to back to spend more time on extending the template.
In the past I had big issue with query caches so so far that part in my template is done peaty well. So on top of base set of metric that part is in state "done done"
However it is long list of other thing which would be good to monitor and automatically add in that template. It will be even more in case of MySQL 8.0.
BTW .. query cache one funny conclusion for zabbix database and query cache. If you will look on the data and graph about query cache you will see that it is 100% misses to that cache.
Try to guess why
Nevertheless kind of conclusion which was byproduct on using it on zabbix db is that MySQL should have changed query cache size to lowest possible value.

[kloczek]

To be honest I have kind of idea of DB workloads specific templates which should link "Service MySQL" template.
For example in that template you can find trigger around even_scheduler. That trigger is useful when zabbix database is using history and trends tables are partitioned and partitions maintenance is executed over MySQL event scheduler (which is useful because on moving database around, rebuilding or replicating you don't need to care about additional crontab tasks). That trigger warns if MySQL engine has didabled internal event scheduler (and gives hint in the trigger comment what needs to be done to fix this).
Such "Service MySQL::zabbix" (or "Service MySQL/zabbix") template could have set of specific trigger showing that tuning for that type of the database backend is not optimal like query cache size is not lowest possible. Other turntables should have similar triggers build on top of all items provided by "Service MySQL" template items grouped under "SVC::MySQL::cfg" Application.
For many other publicly available services/applications is possible to build such second layer of templates as well and start accumulating in them knowledge about best practices/turnings and other thing ..

[Pamela56]

kloczek, thanks for your answer!

I did this steps:

1. created user with name monitoring in mysql:

mysql>
CREATE USER 'monitoring'@'localhost' IDENTIFIED BY 'monitoring';
GRANT SELECT, INDEX, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'monitoring'@'localhost';
FLUSH PRIVILEGES Myprepaidbalance;

2. Attached this template (Service MySQL.xml) on host Zabbix server.
3. And i see thois error 'Remote commands are not enabled' on Items zabbix server's host

Would you help me to resolve this this error?

Temporary I'm busy doing other things and only in about month will be able to back to spend more time on extending the template.
In the past I had big issue with query caches so so far that part in my template is done peaty well. So on top of base set of metric that part is in state "done done"
However it is long list of other thing which would be good to monitor and automatically add in that template. It will be even more in case of MySQL 8.0.