5 ms·

I don't really know anything about compression algos, but something that struck me as interesting with this one when it came up on KF before is the ability to c

I don't really know anything about compression algos, but something that struck me as interesting with this one when it came up on KF before is the ability to create a custom dictionary - not sure if that's really common among other compression algos or not. But since I often work with compressed MySQL dumps with a lot of repetitive data from my client's site that come out to almost 50MB when compressed with xz, I wonder if I could make a dictionary with all the MySQL keywords you'd see in dumps, plus other repetitive strings unique to that site (for example, something like two-thirds of all user email addresses end with "@gmail.com") and get those dump sizes even smaller. I imagine that xz might be good enough at making its own dictionary that what it ends up with wouldn't be much different from what I'd make manually, so I could see a decent chance that the improvements would be minimal at best. Has anyone done any experimentation along these lines?

killingtime74 4y ago
The dictionaries mentioned here speed up for small files where the dictionary would be bigger than the raw source. You don't need that for a Mysql dump. Its many times bigger than the dictionary. You could theoretically save space for large files by sharing a dictionary but the saving is probably negligible.
- Cyberdog 4y ago
  Okay, interesting. I guess I'll stick with what I've got now for the time being, then. Thanks.
johannes1234321 4y ago
Sorry for the little hijacking: An alternative to using "regular" dumps might be MySQL Shells dump utility. Contrary to mysqldump etc. it doesn't save data in SQL but some CSV-like format. This won't solve the "@gmail.com" case but avoid the repetitive "INSERT INTO tablename VALUES (" for each record and what isn't there doesn't have to be compressed to begin with. (And then is paralizable etc., downside might be that it is not using a single file, but a number of files) https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-uti... Depending on your needs you could also look at the MySQL clone plugin, which gives you a simple way to clone the data dictionary as physical backup, avoiding the textual representation, but has its restriction. https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html Disclaimer: I work at the MySQL development team at Oracle

killingtime74 4y ago

The dictionaries mentioned here speed up for small files where the dictionary would be bigger than the raw source. You don't need that for a Mysql dump. Its many times bigger than the dictionary. You could theoretically save space for large files by sharing a dictionary but the saving is probably negligible.

Cyberdog 4y ago
Okay, interesting. I guess I'll stick with what I've got now for the time being, then. Thanks.

johannes1234321 4y ago

Sorry for the little hijacking: An alternative to using "regular" dumps might be MySQL Shells dump utility. Contrary to mysqldump etc. it doesn't save data in SQL but some CSV-like format. This won't solve the "@gmail.com" case but avoid the repetitive "INSERT INTO tablename VALUES (" for each record and what isn't there doesn't have to be compressed to begin with. (And then is paralizable etc., downside might be that it is not using a single file, but a number of files) https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-uti... Depending on your needs you could also look at the MySQL clone plugin, which gives you a simple way to clone the data dictionary as physical backup, avoiding the textual representation, but has its restriction. https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html Disclaimer: I work at the MySQL development team at Oracle