0

Can someone share a method on how can I retrieve the database growth report from the date of creation till current for MySQL? Coming from Oracle background, I understand for Oracle we can do a script to query the creation date or from the snapshot. But I don't now if MySQL has a similar table/view which I can query and check from.

1

You cannot obtain the info about DB size in the past - server do not store those information.

You can obtain the info about current DB size. It can be obtained from the INFORMATION_SCHEMA.FILES system pseudotable. You can shedule the get of this info in some table using CREATE EVENT, so You will see the dynamic of size change.

But I think (especially assuming You use InnoDB engine with file_per_table enabled) the best way is to obtain DB size info directly from the OS' filesystem (and shedule it using OS' sheduler - cron or another). Of course, You can store that info in MYSQL server table :) - it's not a problem to add it via command-line client.

2
  • Thank you for the reply, yes I'm able to retrieve the current sizes from information_schema as well. I did google around and found most solution is based on what you mention which is to schedule a cron to check the growth size from current time & couldn't find anything about getting historical DB growth. Well thanks for the clarification, I'll try to schedule the cronjob to monitor the DB growth. – Jason Oon Jun 4 '18 at 8:31
  • @JasonOon If You'll monitor the DB growth getting data from the information_schema use MySQL' inner sheduler, not OS' one. But when You'll get data from the filesystem then use cronjob. To get data from information_schema and shedule it with cron is a poor idea I think. – Akina Jun 4 '18 at 8:41
0

I have one query, which works on SQL Server however not sure whether it would work in MySQL, you may need to modify a bit. Below query would give you statistics over last one year.

     DECLARE @startDate DATETIME;SET @startDate = GETDATE();
SELECT PVT.DatabaseName ,
PVT.[0] ,
PVT.[-1] ,
PVT.[-2] ,
PVT.[-3] ,
PVT.[-4] ,
PVT.[-5] ,
PVT.[-6] ,
PVT.[-7] ,
PVT.[-8] ,
PVT.[-9] ,
PVT.[-10] ,
PVT.[-11] ,
PVT.[-12]
FROM (
SELECT BS.database_name AS DatabaseName ,
DATEDIFF(mm, @startDate, BS.backup_start_date) AS MonthsAgo ,
CONVERT(NUMERIC(10, 1), AVG(BF.file_size / 1048576.0)) AS
AvgSizeMB
FROM msdb.dbo.backupset AS BS
INNER JOIN msdb.dbo.backupfile AS BF
ON BS.backup_set_id = BF.backup_set_id
WHERE NOT BS.database_name IN ( 'master', 'msdb', 'model', 'tempdb' )
AND BF.[file_type] = 'D'
AND BS.type = 'D'
AND BS.backup_start_date BETWEEN DATEADD(yy, -1, @startDate) AND
@startDate
GROUP BY BS.database_name ,
DATEDIFF(mm, @startDate, BS.backup_start_date)
) AS BCKSTAT PIVOT ( SUM(BCKSTAT.AvgSizeMB) FOR BCKSTAT.MonthsAgo IN ( [0] ,
[-1], [-2], [-3], [-4], [-5], [-6], [-7], [-8], [-9], [-10], [-11], [-12] ) )
AS PVT
ORDER BY PVT.DatabaseName;
1
  • 1
    There is no backupset/backupfile or something similar to them in MySQL. – Akina Jun 4 '18 at 7:54

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.