0

I would like to store the database size for a particular database on daily basis on to a table which would help in understand the growth of the database over a period of time.

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES where table_schema = 'testdb' GROUP BY table_schema;

I get the output for the above query say 10456.3.

I need to insert this number on to a table which should contain three columns. DB name, Date and DB size.

How can I do this on daily basis by automating this?

2

If you're storing it into the same database you're measuring you could use a MySQL Scheduled Event to run the query at a certain time.

an example being:

DELIMITER $$
CREATE 
    EVENT IF NOT EXISTS db_measurement    
    ON SCHEDULE EVERY 1 DAY
    STARTS '2016-11-15 00:00:00'
    ENABLE
    DO 
    INSERT INTO tableName 
    SELECT 
        table_schema AS "Database", 
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
    FROM information_schema.TABLES 
    where table_schema = 'testdb' 
    GROUP BY table_schema$$
DELIMITER ;  

If it's a different database you'll need to use a shell script of some sort.


Bearing in mind I'm far from an expert on Linux (and ignoring the discussions around permissions, passwords in scripts, SSL etc etc), you could do something along the lines of:

mysql -hxxxx -Pxxxx -uxxxx -pxxxx -se"SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS Size FROM information_schema.TABLES where table_schema = 'dbtest' GROUP BY table_schema;" | { read dbSizeVal; mysql -hxxxx -Pxxxx -uxxxx -pxxxx -e"INSERT INTO dbname.tablename VALUES(${dbSizeVal})"}

Which should get the size value from the remote database, and load it into a table in the local database.


I just used:

CREATE TABLE `tableName` ( 
    `dbsize` varchar(45) NOT NULL
);

for testing this, but you would probably want a decimal, integer, float datatype depending on the data you return and how accurate you need it.

Also this doesn't include the dbname or time fields. You could use timeVal TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP which would give you the timestamp field.

The database name is tricky, as you would need to carry two variables across, and my script (above) only allows for one (if you are only looking at one database you could just type it directly into the query / set a default value).

  • Thanks, I will be storing it in a different database. Can you help me with that? – tesla747 Nov 14 '16 at 8:42
  • see above for more info – IGGt Nov 14 '16 at 10:10
  • one more doubt, what should the table definition for this to work? table wherein the db size goes into. – tesla747 Nov 14 '16 at 10:17
  • plus if this information important (not just indicative) - keep in mind query could lie for 10-15%, You can compare calculated size with real disk usage and make this adjustments if necessary – a_vlad Nov 14 '16 at 10:26
  • @a_vlad ok noted. But in the above case, what should be my table structure be? – tesla747 Nov 14 '16 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.