I have set up a central syslog that stores the logs of 6 servers in a DB. After that I set a MySQL sensor, in PRTG, for the size of the DB. Although data is written to the database every second, the sensor has been showing the same size, 672 MB, for several days. Since I am aware that the size can never be correct, I checked the size directly at the database with an SQL statement. To my disappointment, I get the same size as in PRTG or as via PhpMyAdmin, 672 MB. In order to be able to monitor the current size of the DB, I have written a script that creates an image of the DB with MySQL Dump and saves the size of the file in a text file, which is then read into PRTG via SNMP.
The SQL Statement I used, to check for the DB Size within MySQL and from PhpMyAdmin:
SELECT table_schema "Syslogging_DB", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
I have been trying to find an answer to my question about how to get the correct size of the DB displayed for the last few days, but could not find an answer for my specific problem. Can someone please explain to me why information_schema.tables has been holding the wrong size of the DB for days, and which SQL statement I have to use to get the current size of the database displayed?
My Solution for this Situation is to let a one-liner run at every hour via crontab.
mysql -D YOUR_DB -e "analyze table YOUR_TABLE"