Specify engines to be treated as transactional

[MikeG]

It would be useful to have a configuration option to specify which engines are to be considered transactional when determining whether or not to use --single-transaction in mysqldump providers. An example would be a schema that has 5 InnoDB tables and one HEAP table being downgraded to using --lock-tables.

[Andrew Garner (muzazzi)]

Hmm. I was looking at this more in depth and it occurs to me that in these cases I would always set single-transaction explicitly. Is there a good use case where you want to run through holland's engine auto-detect logic with modification but you wouldn't want to simply change the lock type?

Should we always treat the memory engine as transactional for the purpose of mysqldump? I'm not sure that's a good idea either.

[m00dawg (tim-moocowproductions)]

I suppose in a pinch having data from MEMORY tables is not a bad thing though I tend to run under the assumption that data should be assumed to be volatile. I wonder, in terms of consistency, where having locked backups of MEMORY tables would be beneficial. My thought is it's probably safe to use --single-transaction there. Backing up the data probably does not hurt to do normally but I'm not sure forcing locks in this case is worth it.

As far as engines go, I tend to agree with Andy that it would be a good case to modify behaviors using the tools already at our disposal (multiple backups sets, filters, specifying lock types, etc.).

[MikeG (mikegriffin)]

The main goal here would be to not lock by default on a schema which has innodb tables and one memory table. Whether that is to treat memory as transactional by default, just grab the DDL for memory tables before doing the dump, or anything similar, I would consider this resolved.

[Andrew Garner (muzazzi)]

In holland 1.1's mysqldump plugin I've added three settings for fine-tuning the --single-transaction auto-detection logic:

transactional-databases-override => Always treat an entire database (or list of databases) as transactional
transactional-tables-override => always treat a specific table (or list of tables) as transactional
transactional-engines-override => always treat a specific storage engine (or list of engines) as transactional

innodb is always considered transactional, but additional engines can be flagged as suitable for --single-transaction in holland as well (e.g. memory, sphinx, myisam, etc.)

I'm currently testing this in my branch here:

https://github.com/abg/holland/tree/amsterdam

And this will be merged into the main amsterdam branch of holland-backup/holland for alpha release probably later this week/early next week.

[m00dawg (tim-moocowproductions)]

Very nice! That will prove to be quite helpful! I'll make a note to test some of these features and report back any weirdness but regardless I think these features should be in 1.1 for sure!

[m00dawg (tim-moocowproductions)]

I was thinking - it might be better to shorten those variables a bit as they remind me of 'innodb_flush_log_at_trx_commit' which is a mouthful. How about:

tx-dbs-override
tx-tables-override
tx-engines-override

?

[Andrew Garner (muzazzi)]

yeah, I think that's a good idea. I'm not married to any of these new option names and I figured we would rename them a bit.

We can also have option aliases in holland 1.1, where we can have multiple names for the same option. So if we wanted a long-name and a short-hand that can be done as well.

[m00dawg (tim-moocowproductions)]

Ah you read my mind. I was thinking after posted my last update that actually having aliases would be the best of both worlds. I don't think it matters, but I think the long version should be the default (non-alised) since it explains the most about what the command does without any assumption.

How many aliases can we have? I don't think we need to go crazy making aliases but people who hate to type could use really compact things might be happy if we had really small config options (like 'tx-dbs-ovrrde'). I think just shortening transaction goes a long way though.

[m00dawg (tim-moocowproductions)]

My base test seems to be having oddly. Holland is using single-transaction even though I specified auto-detect as the lock-method:

root@mcp:/etc/holland/backupsets# cat transtest.conf
[holland:backup]
plugin = mysqldump
backups-to-keep = 5
estimated-size-factor = 1.0

[mysqldump]
lock-method = auto-detect
#transactional-databases-override = "test"
#transactional-tables-override = "t1"
databases = "test"
tables = "*"
dump-routines = no
dump-events = no
stop-slave = no
bin-log-position = no
flush-logs = no
file-per-database = no
additional-options = ""

[compression]
method = gzip
inline = yes
level = 1

However:

root@mcp:/etc/holland/backupsets# holland bk transtest
[INFO] Backup: transtest
[INFO] + Found plugin mysqldump
[INFO] + Locked spool /var/spool/holland/transtest/.holland
[INFO] + Initialized backup directory /var/spool/holland/transtest/20110421_214001.PyeNd_
[INFO] + Running setup-backup hooks
[INFO] + Configured plugin
[INFO] + Connected to MySQL
[INFO] Connected to localhost via unix socket /var/run/mysqld/mysqld.sock as root@localhost
[INFO] + Evaluating schema
[INFO] + Ran plugin pre
[INFO] + Running before-backup hooks
[INFO] + Estimating backup size
[INFO] Estimating backup size
[INFO] ----------------------
[INFO] * Using plugin estimate 2.08KB
[INFO] + Plugin estimated backup size of 2.08KB
[INFO] + Adjusted estimated size by 100.00% to 2.08KB
[INFO] + Spool directory /var/spool/holland/transtest/20110421_214001.PyeNd_ has 1.87GB available
[INFO] + Saved config to /var/spool/holland/transtest/20110421_214001.PyeNd_/backup.conf
[INFO] Running backup
[INFO] mysqldump backup
[INFO] ----------------
[INFO] :databases: djsweetums(excluded),harttest(excluded),mcp(excluded),moocowproductions(excluded),music(excluded),musicbiz(excluded),mysql(excluded),q2solutions(excluded),test
[INFO] + mkdir /var/spool/holland/transtest/20110421_214001.PyeNd_/backup_data
[INFO] + mkconfig /var/spool/holland/transtest/20110421_214001.PyeNd_/holland.my.cnf
[INFO] + exclusions >> /var/spool/holland/transtest/20110421_214001.PyeNd_/holland.my.cnf
[INFO] - Adding mysqldump options
[INFO] + --flush-privileges
[INFO] + --max-allowed-packet=128M
[INFO] mysqldump(test[28993])::
[INFO] /usr/bin/mysqldump --defaults-extra-file=/var/spool/holland/transtest/20110421_214001.PyeNd_/holland.my.cnf --flush-privileges --max-allowed-packet=128M --single-transaction test
[INFO] * mysqldump(test[28993]) complete
[INFO] + Running after-backup hooks
[INFO] Backup job transtest completed in 0.06 seconds
[INFO] + Final backup size 9.70KB
[INFO] + 466.42% of estimated size 2.08KB
[INFO] + Suggested estimated-size-factor = 4.66
[INFO] + Saved config to /var/spool/holland/transtest/20110421_214001.PyeNd_/backup.conf
[INFO] + Keep 5 backups
[INFO] + Purged old backup /var/spool/holland/transtest/20110421_213700.ZZbVRY
[INFO] + Kept backup /var/spool/holland/transtest/20110421_213715.HYdNmb
[INFO] + Kept backup /var/spool/holland/transtest/20110421_213737.7uAKHk
[INFO] + Kept backup /var/spool/holland/transtest/20110421_213746.rBnOTS
[INFO] + Kept backup /var/s...

[Andrew Garner (muzazzi)]

Good catch. :) This was caused by a typo where I was treating a function (Database.is_transactional()) as an attribute so it was always true introduced when I had cleaned up some of the auto-detection code. That really should be a property in order to be consistent with how Table.is_transactional is handled by the schema walking code. I just pushed a fix for that and I am getting the expected behavior with this test case now.

[m00dawg (tim-moocowproductions)]

Awesome, thanks Andy!

[Andrew Garner (muzazzi)]

Merged into holland-backup/holland here:

https://github.com/holland-backup/holland/tree/d9cc5e601e901dbfed0b9530ab2f384e1c33b025