I have a MariaDB 10.3 on my Linux server that I want to monitor. I wanted to get the database size with the following query:
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size"
FROM information_schema.TABLES
GROUP BY table_schema;
I was following the set up from this guide
| Database | mysql |
|---|---|
| Channel #1 | |
| Channel Name | Database |
| Column Name | Database |
| Mode | Absolute |
| Unit | BytesDisk |
| Channel #2 | |
| Channel Name | Size |
| Column Name | Size |
| Mode | Absolute |
| Unit | BytesDisk |
After saving it, I get the following Error:
Id 3: "information_schema" [System.String] value can not be parsed. Please keep in mind that any date or time formats need to use the "EN-US" format.
Article Comments
Hi the output goes like this:
| Database | Size |
|---|---|
| mysql | 8.36 |
| information_schema | 0.17 |
| performance_schema | 0.00 |
| wordpress | 1.13 |
Also, I have a query that goes like this:
SELECT
c.TABLE_NAME,
c.COLUMN_TYPE,
c.MAX_VALUE,
t.AUTO_INCREMENT,
IF (c.MAX_VALUE > 0, ROUND(100 * t.AUTO_INCREMENT / c.MAX_VALUE, 2), -1) AS "Usage (%)"
FROM
(SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_TYPE,
CASE
WHEN COLUMN_TYPE LIKE 'tinyint(1)' THEN 127
WHEN COLUMN_TYPE LIKE 'tinyint(1) unsigned' THEN 255
WHEN COLUMN_TYPE LIKE 'smallint(%)' THEN 32767
WHEN COLUMN_TYPE LIKE 'smallint(%) unsigned' THEN 65535
WHEN COLUMN_TYPE LIKE 'mediumint(%)' THEN 8388607
WHEN COLUMN_TYPE LIKE 'mediumint(%) unsigned' THEN 16777215
WHEN COLUMN_TYPE LIKE 'int(%)' THEN 2147483647
WHEN COLUMN_TYPE LIKE 'int(%) unsigned' THEN 4294967295
WHEN COLUMN_TYPE LIKE 'bigint(%)' THEN 9223372036854775807
WHEN COLUMN_TYPE LIKE 'bigint(%) unsigned' THEN 0
ELSE 0
END AS "MAX_VALUE"
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE EXTRA LIKE '%auto_increment%'
) c
JOIN INFORMATION_SCHEMA.TABLES t ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME)
WHERE
c.TABLE_SCHEMA = 'Database_Name' AND IF (c.MAX_VALUE > 0, ROUND(100 * t.AUTO_INCREMENT / c.MAX_VALUE, 2), -1) > 50
ORDER BY
`Usage (%)` DESC;
With example output like this:
| TABLE_NAME | COLUMN_TYPE | MAX_VALUE | AUTO_INCREMENT | Usage (%) |
|---|---|---|---|---|
| glpi_alerts | int(11) | 2147483647 | 1 | 56.71 |
| glpi_apiclients | int(11) | 2147483647 | 2 | 52.21 |
| glpi_authldapreplicates | int(11) | 2147483647 | 1 | 50.01 |
and so on..
It should return the table with the biggest index usage in descending order. And I want to get at least the first three rows to display on my sensor, just TABLE_NAME and Usage will be fine. Thanks!
Jun, 2020 - Permalink
It's OK now, I used the Key Value Pair option to get the database name and it's corresponding database size, Thanks!
Jun, 2020 - Permalink
Hello,
Please make sure that the query always returns a valid value. Can you send us the output for further troubleshooting? Also please open the sensor settings and enable the "Write Result to Disk" option. Please send the logfiles (usually located in "C:\ProgramData\Paessler\PRTG Network Monitor\Logs\sensors") to support@paessler.com including the original question.
Kind Regards,
Timo Dambach
Paessler Tech Support
May, 2020 - Permalink