Score:2

Mysql consume lots of space in ubuntu 20.4

us flag
rai

I need help regarding my application deployed in ec2 instance using T3 medium and Ubuntu 20. My problem is, I encountered a server error twice, it happens 3 months after each error. Prior to server error, I noticed I am greeted this message 99.5% of **GB once I log into the terminal console. The first server error happened three months ago I used a t2 tier, so without hesitation, I upgraded it to much bigger t3 with 30GB disk capacity. Now the error happens again, after 3 months or more. Again I noticed the 99.5% of 29.02GB and after few days I encountere the 500 error. I tried to investigate, I found out that the var/lib/mysql folder consumed a lot of space!. I am just wondering because my application is under in development, so data stored in mysql are minimal. So I tried to dump the db and inspect the stored data, its only less 1GB, and i have only 1 database in my ec2 instance.

Running sudo du lib -h, it shows 23G var/lib/mysql, I tried to restart the mysql server, the error is gone, but still the size doesnt change.

So my question is, why the folder lib/mysql consumed lots of space but in fact the actual data stored in database is just below 1 gb? So clearly, restarting mysql everytime an error encountered is just a temporary solution, and increasing the database space is not also the solution. Do you have idea what causes this and how to prevent this?. My application is still in development and I would like to solve the issue before deployment to production.

Many thanks.

mforsetti avatar
tz flag
what's the result of `SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';` on your MySQL instance?
rai avatar
us flag
rai
@mforsetti it shows `ON` in its value
mforsetti avatar
tz flag
so it's not `ibdata*` bloat. it could be either MySQL logs or binary logs. What's the result of `SHOW GLOBAL VARIABLES LIKE 'log_%';` and `SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';` on your MySQL instance?
rai avatar
us flag
rai
Running `SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';`, it shows slow_query_log | OFF
rai avatar
us flag
rai
Running `SHOW GLOBAL VARIABLES LIKE 'log_%';`, it shows log_bin | ON , log_bin_basename | /var/lib/mysql/binlog, log_error_verbosity | 2
rai avatar
us flag
rai
log_slave_updates| ON, log_statements_unsafe_for_binlog | ON
mforsetti avatar
tz flag
alright you got binlog on, try running `SHOW BINARY LOGS` and see how many binary logs you have.
rai avatar
us flag
rai
Oh my God, binlog.000282 | 106260830 | No and there are lots of them(236 rows). Is this the reason ?How to prevent this ?
rai avatar
us flag
rai
@mforsetti, thanks for the help. I solve the issue.Maybe you can convert your comments to answer
ua flag
What is the name of the largest file?
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.