slow dumping of mysql in production

[wakiki]

Just to let people know, I have a very large mysql database, and dumping it using backup gem takes a LONG time, and it locks all the tables, making the site unusable.

A solution is to add --single-transaction to the mysql dump command.
More info at http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction

Perhaps meskyanichi will integrate this?
It's pretty straight forward and it means it can then be used in production for large mysql databases.

Steve

[fmluizao]

You can inform this option in your configuration file using 'additional_options' like this:

adapter :mysql do
  user        'user'
  password    'password'
  database    'database'
  skip_tables ['table1', 'table2', 'table3']
  options do
    host    '123.45.678.90'
    port    '80'
    socket  '/tmp/socket.sock'
  end
  additional_options '--single-transaction'
end

[mrrooijen]

This isn't really an issue, but we could perhaps just implement that this is default behavior. What are the pros en cons of using --single-transaction?

[wakiki]

As far as I know you almost always want to use
additional_options '--single-transaction --quick'
if your database supports transactions (mysql does)

[mrrooijen]

Thanks for pointing that out, I will mark this as a consideration. Might be a good idea set these options as defaults.

[fmluizao]

Mysql doesn't support transactions, some storage engine does, like InnoDB. I don't think this option should be enabled by default, because other people uses other storage engines.

[mrrooijen]

Good point, I did not know that. In that case it's not wise to set it as default since it will literally break MySQL adapter for some users.

I guess the commented out template in the backup configuration file will be enough to point out the option IS available but needs to be activated: additional_options '--single-transaction'.

We could additionally add this that line: --quick so it would make: --single-transaction --quick.

[mrrooijen]

I will add the --single-transaction --quick to the generated template, but leave it commented out. Maybe add a comment that tell it to uncomment this line if the database is large so that it doesn't lock tables and such, but that it isn't supported for all storage engines.

[wakiki]

That sounds like a great idea. Thanks.