How to calculate free space in a MySQL datasource deployed dynamically via docker

user_756809 0
Viewed 122

I have a Spring Boot server that's connected to a MySQL server running on a separate docker image. As such I can't do something as simple as a df since the image is wired via a URI in the docker configuration and may change in production deployments. E.g. we might switch the DB to use clustering etc.

However, we might run out of space in the DB and need to detect that. Since there's a lot of data we can delete when running out of space this is pretty crucial.

There are a lot of answers related to calculating the amount of space used by the DB, that isn't what I want since:

  • It's expensive in terms of CPU and I'd like to keep the implementation efficient
  • I don't care how much space is taken, only how much is left, I can keep a variable of "total space" and use that to calculate but it would ignore potential increases in the disk size

I found the data_free query here, but it seems problematic based on the following answer.

Is there another way to calculate the disk size either via mySQL query or via similar API exposed through spring boot?

spring-data-jpa spring-boot mysql-connector mysql
1 Answers

It sounds like you need this to see the free space within one docker image:

docker system df

https://docs.docker.com/engine/reference/commandline/system_df/ https://www.percona.com/blog/2019/08/21/cleaning-docker-disk-space-usage/

And it can be run independently of MySQL. So, I don't see a CPU issue.

Because all the hassles related to MySQL's Data_free and predicting disk usage for an active table, it is not possible to accurately translate free space into the number of rows you can add before running out of space.

If your usage is fairly consistent, run docker system df every day (or every hour) and plot the results. Then guestimate when it will hit zero. Be pessimistic in drawing the line through the graph.

You say you can "delete data" to free up space. Be aware that MySQL, in many cases, does not return the freed space to the OS (that is, Docker). Instead it leaves the table fragmented. That is, DELETEing rows leaves room for new INSERTs into the same table. (There are variations on this; we can discuss further if you get more specific on "delete data".)

If the data size does not grow/shrink in a "regular" way, do you at least know when to expect "bursts"? Chew up some CPU in the lull between bursts.

If you can "delete some data" whenever you need to, why not keep the data pruned. This would spread out the overhead, and (hopefully) keep the space out of trouble.

If you are talking about 'huge' tables, I have several tips on doing big deletes efficiently.

Plan B

Docker can reach into the main filesystem for directories. Put MySQL's data tree there. Then you are not asking about the space in Docker, but space in the main system. (I am assuming you have lots more free space there??)

user_17668310
Related