Solved Low disk space troubleshooting

fred974

fred974

Hi guys,

I lost access to my server a few hours ago..
As it happened I had 9 website and went offline..

I am curently unable to start any jail due to out of disk error...
At 3 am today we had 168GB of free space..

How can I find out what happen and see where the sudent data are stored?

Thank you
 
gofer_touch said:
What was the solution that you found to deal with the problem?
Click to expand...

I ran df -h and saw that my zroot/DATA/mysql dataset was full..
I went to its mountpoint and ran du -hsx * | sort -rh | head -10.
My mysql-slow.log file was 71GB and I had 50+ /var/db/mysql/mysql-bin.00xx files of 1GB each.

Removed /var/db/mysql/mysql-slow.log manually and set /etc/newsyslog.com to log rotate with
Code: 
/var/db/mysql/mysql-slow.log   mysql:mysql  600  7  1000  *  J   /var/db/mysql/holy.mydomain.co.uk.pid
/var/db/mysql/mysql-error.log  mysql:mysql  600  7  1000  *  J   /var/db/mysql/holy.mydomain.co.uk.pid
To delete the mysql-bin files i login to MariaDB and ran mysql> RESET MASTER;
The two commands above free up about 121GB of disk space..

Now I need to work out a solution so this never happen again.. any sugestion?
 
fred974 said:
To delete the mysql-bin files i login to MariaDB and ran mysql> RESET MASTER;
Click to expand...
This is a really bad idea if this server is replicated. Doing so will cause the slave to lose track and you will need to set up replication again. The proper way to remove them is by using "PURGE BINARY LOGS": http://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html

Now I need to work out a solution so this never happen again.. any sugestion?
Click to expand...
Regarding binlogs: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_expire_logs_days
 
There is really no need and it is a general bad practice to log slow queries, unless you plan to do something about them. In MariaDB, logging is disabled by default.
 
gofer_touch said:
Since you are using ZFS could quotas help?
Click to expand...
I could set a quota for zroot/DATA/mysql but in the end if I don't set monitoring on that dataset, if it run out of disk space again, I will end up in same scenario a think and DB will crash along with all websites..
I will spend next few day looking at zabbix or equivalent
 
Hi Guys,

I had the same problem again this morning ad I clean the log using PURGE BINARY LOGS as sugested by SirDice
Could somene please explain me what the mysql-bin.* file are ?
Why do they get so big that it uses the entire system disk space?
/usr/local/etc/my.cnf
Code: 
# BINARY LOGGING #
log-bin                        = /var/db/mysql/mysql-bin
expire-logs-days               = 10
sync-binlog                    = 1
binlog_format                  = MIXED
 
fred974 said:
Could somene please explain me what the mysql-bin.* file are ?
Click to expand...
https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

fred974 said:
Why do they get so big that it uses the entire system disk space?
Click to expand...
It depends on how the database is used. If there are a lot of mutations (inserts, updates, deletes, etc) it can grow quite big fast.

If you don't use replication you could turn them off completely. But I would advise against that, it's good to have a few days worth of binary logs, just in case things go belly up and you need to rebuild the database. For not so important databases I tend to keep around 3 days, just enough to make it through a weekend.
 
You must log in or register to reply here.
Back
Top