0

I have a MariaDB database on a Linux Server. I want to calculate the amount of changes by the database per day.

what I need = the volume of changed data (update/delete) + new data (insert).

This helps me to calculate if the amount of free space is suitable for the lvm-snapshot or not.

Only the database uses our linux sever and there isn't another application.

1 Answer 1

2

If its all Innodb then innodb_pages_written * innodb_page_size.

As changes are written to the redo log, add to this amount the lesser of innodb_log_file_size and the amount already calculated. We use the minimum as the redo log is a circular buffer and won't ever use more than its maximum amount.

What this doesn't account for is the number of times the same page is changed multiple times in different flushes which will mean the snapshot requirement becomes less. But for a pessimistic estimate the above should be a good metric.

3
  • Yes. It's innodb. But there is no "innodb_pages_written" Parameter in mariadb 10.3. If the database had this parameter, how could I calculate the volume changes in the last twenty-four hours.
    – TimLer
    Commented Sep 1, 2022 at 11:20
  • 1
    @TimLer innodb_pages_written is a status variable. Can be retrieved like this: SHOW GLOBAL STATUS LIKE 'innodb_pages_written';
    – dbdemon
    Commented Sep 1, 2022 at 14:38
  • 2
    STATUS values start with a capital letter (mostly). Divide that value by Uptime to get the average since MariaDB was restarted. Then multiply by 16KB.
    – Rick James
    Commented Sep 1, 2022 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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