What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
300.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

Get MariaDB database size

Votes:

0

Your Vote:

Up

Down

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

Databasemysql
Channel #1
Channel NameDatabase
Column NameDatabase
ModeAbsolute
UnitBytesDisk
Channel #2
Channel NameSize
Column NameSize
ModeAbsolute
UnitBytesDisk

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.

mariadb mysql-v2-sensor prtg

Created on May 29, 2020 4:02:57 AM by  Glen Escuadra (0) 1



3 Replies

Votes:

0

Your Vote:

Up

Down

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 [email protected] including the original question.

Kind Regards,
Timo Dambach
Paessler Tech Support

Created on May 29, 2020 1:26:47 PM by  Timo Dambach [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hi the output goes like this:

DatabaseSize
mysql8.36
information_schema0.17
performance_schema0.00
wordpress1.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_NAMECOLUMN_TYPEMAX_VALUEAUTO_INCREMENTUsage (%)
glpi_alertsint(11)2147483647156.71
glpi_apiclientsint(11)2147483647252.21
glpi_authldapreplicatesint(11)2147483647150.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!

Created on Jun 1, 2020 12:39:00 AM by  Glen Escuadra (0) 1



Votes:

0

Your Vote:

Up

Down

It's OK now, I used the Key Value Pair option to get the database name and it's corresponding database size, Thanks!

Created on Jun 2, 2020 9:47:39 AM by  Glen Escuadra (0) 1



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.