10 June 2008

Filling up my root partition with mysql logs

I was trying to optimize a script on Rails to send 50 000 emails (TODO Add link) and I came across a problem of space with my root partition:

The script crashed with an error message explaining that the hard drive was full.

Using commands like

du -s * | sort -nr | head

or the slower Ubuntu "Disk Usage Analyser", it turned out that the /var/log/mysq was full of huge logs!!!

/var/log/mysql$ ll
total 1925788
-rw-rw---- 1 mysql adm 98 2007-04-10 10:57 mysql-bin.000558
-rw-rw---- 1 mysql adm 117 2008-06-01 15:01 mysql-bin.001456
-rw-rw---- 1 mysql adm 117 2008-06-01 21:45 mysql-bin.001457
-rw-rw---- 1 mysql adm 141 2008-06-02 09:45 mysql-bin.001458
-rw-rw---- 1 mysql adm 104940360 2008-06-10 16:24 mysql-bin.001468
-rw-rw---- 1 mysql adm 104940040 2008-06-10 16:25 mysql-bin.001469
-rw-rw---- 1 mysql adm 104941440 2008-06-10 16:25 mysql-bin.001470
... etc

As a quick fix, I just deleted manually these files with
sudo rm mysql-bin.00148*

A more permanent solution, found on the web, was to edit:
sudo gedit /etc/mysql/my.cnf

Comment all references to log_bin:
# log_bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
# expire_logs_days = 10
# max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
sudo /etc/init.d/mysql restart

Obviously, I will have to refactor a bit the code of ar_mailer to take in account the fact that I am sending 50 000 identical newsletters to 50 000 people.
ar_mailer uses a "emails" table with a "mail" column which is the email content. In my case, the "mail" is duplicated 50000 times + it's a 340KB HTML email. No wonder it filed my sql logs!

I just found out about Evan Weaver top-secret-tuned-mysql-configurations-for-rails post, I really loved the PS ;-)
# PS. Do not under any circumstances enable binlog

Next time, I have to configure mysql, I know what I'll use ...

1 comment:

marcushd said...

I always disable the bin-log and the regular log. just leave the slow-log and the not indexed queries log enabled.

but the bin log is required to mysql replication.