6 ms·

From MySQL+MMM to MariaDB+Galera Cluster: A High Availability Makeover

31 points by itsderek23 13y ago

ck2 13y ago
What I want to know is how people deal with innodb's dirty horrible secret - how ibdata can grow to infinite massive sizes and can never be reduced, ever. Regardless if you are using "file per table". It is a ten year old bug [1] never addressed, no tools ever made to optimize it, and the larger your data, the sooner it will come to bite you. Doesn't matter if you are using mysql, percona or mariadb. [1] http://bugs.mysql.com/bug.php?id=1341 http://bugs.mysql.com/bug.php?id=1341
- lotsofcows 13y ago
  You want to "defrag" a live database? You're brave. Dump it and rebuild it - you should be able to do a node at a time.
  - ck2 13y ago
    With terabyte sized databases, thousands of tables, not really an option to dump and reload. Alter and optimize are already possible on a live innodb table, in theory it should not be exponentially harder to optimize ibdata. Note that oracle solved this problem on their commercial database product.
- HarrisonFisk 13y ago
  The easiest way is to avoid running into the problem. The main reason the central table space grows is due to rollback segments. To avoid collecting a lot of them there are two tips (in addition to file per table you mentioned): 1. Use multi-threaded purge. 2. Avoid very long running transactions (ie. 12+ hours) Once we have done that, we don't really see this problem any longer.
- jolan 13y ago
  With a default my.cnf + innodb_file_per_table=1, ibdata0/ibdata1 will stay at 512M which isn't a big deal.
- crazygringo 13y ago
  I agree it's not great, and annoying in theory. But if you use file-per-table, I've never actually had a problem in practice, since in production, most tables tend to only grow. And even if you remove half the rows of a large table, you're probably expecting that space to be reclaimed eventually. If, on the other hand, you do a big restructuring where you're deleting 90% of your rows in one big swoop... then it's probably actually easier and faster to just create a new table and copy the 10% valid rows into it, then drop the old table, and rename. All space reclaimed. It's definitely something annoying to have to keep in mind, and it's why I prefer to use MyISAM when doing big data manipulation on my local dev machine (everything's faster without transactions too). But using InnoDB in production, I've found the never-shrinking-table-files to be a nonissue. Curious if anyone else has had different experiences.
erevlydeux 13y ago
(shameless plug here) Galera doesn't come out of the box with a secure SST option. SST, or state snapshot transfer, is how it bootstraps a new/failed node to get it close enough to continue with regular Galera replication. If you're running on a public cloud, you might care about secured SST communication. I wrote a drop-in secure rsync SST option, based on socat, that uses SSL encryption to seamlessly secure that traffic. Not perfect by any means, but it works without any futzing around. https://github.com/tobz/galera-secure-rsync https://github.com/tobz/galera-secure-rsync
- jolan 13y ago
  They just released a socat-based solution a couple days ago: http://www.percona.com/doc/percona-xtradb-cluster/release-notes/Percona-XtraDB-Cluster-5.5.33-23.7.6.html http://www.percona.com/doc/percona-xtradb-cluster/release-no...
  - erevlydeux 13y ago
    Interesting. :)
davidjhall 13y ago
Why MariaDB+Galera vs. MySQL Cluster? I read the political decision of Maria being more robust and non-Oracle, but was MySQL Cluster considered? And if not, why?
- erevlydeux 13y ago
  At that rate, I'm curious why Percona XtraDB Cluster (PXC) wasn't in the lineup of considerations.
  - nknighthb 13y ago
    PXC and MariaDB+Galera are mostly the same code.
    - jolan 13y ago
      I think the only difference is MariaDB has done more work on the query optimizer. Maria uses Percona's InnoDB patchset (XtraDB). Both use the galera patch from codership. I don't think Percona merges anything back from Maria.
      - nknighthb 13y ago
        I'm under the vague impression Percona's codebase has some additional patches involving backup and management that haven't been pulled into MariaDB, but I might be mistaken. Obviously there's also simple release cycle lag.
        druiid 13y ago
        The percona tools are mostly outside the direct mysql environment and more on the innodb or linux end of things. I'm not personally aware of any that rely on codebase changes.
- nknighthb 13y ago
  Dunno about the author here, but when I looked at MySQL Cluster a while back, it immediately disqualified itself for lacking foreign key support. That's apparently been newly added in the last few months, but there's still no shortage of annoying limitations to be had. http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-limitations.html http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-limitat... And apparently they require a minimum of six nodes. And the supported installation method is a GUI installer. Sounds like typical "enterprise" Oracle trash, further eroding whatever confidence I might have in it.
contingencies 13y ago
Apologies for a slight tangent, but if you really need SQL's structure and you can't shard the data to avoid scaling issues, please let me know what kind of system you are running because it IMHO has to be pretty weird. Even in these cases, there are always other potential options, for instance using query playback with a combination less frequent FS or blockstore layer provided snapshot functionality. In practice, in my own experience, generally when people get an SQL based database to a huge size the often greater issue is that someone has a huge mess of bad application code relying completely on the current database configuration that is not time or cost-feasible to modify.

31 points by itsderek23 13y ago

ck2 13y ago

What I want to know is how people deal with innodb's dirty horrible secret - how ibdata can grow to infinite massive sizes and can never be reduced, ever. Regardless if you are using "file per table". It is a ten year old bug [1] never addressed, no tools ever made to optimize it, and the larger your data, the sooner it will come to bite you. Doesn't matter if you are using mysql, percona or mariadb. [1] http://bugs.mysql.com/bug.php?id=1341 http://bugs.mysql.com/bug.php?id=1341

lotsofcows 13y ago
You want to "defrag" a live database? You're brave. Dump it and rebuild it - you should be able to do a node at a time.
- ck2 13y ago
  With terabyte sized databases, thousands of tables, not really an option to dump and reload. Alter and optimize are already possible on a live innodb table, in theory it should not be exponentially harder to optimize ibdata. Note that oracle solved this problem on their commercial database product.
HarrisonFisk 13y ago
The easiest way is to avoid running into the problem. The main reason the central table space grows is due to rollback segments. To avoid collecting a lot of them there are two tips (in addition to file per table you mentioned): 1. Use multi-threaded purge. 2. Avoid very long running transactions (ie. 12+ hours) Once we have done that, we don't really see this problem any longer.
jolan 13y ago
With a default my.cnf + innodb_file_per_table=1, ibdata0/ibdata1 will stay at 512M which isn't a big deal.
crazygringo 13y ago
I agree it's not great, and annoying in theory. But if you use file-per-table, I've never actually had a problem in practice, since in production, most tables tend to only grow. And even if you remove half the rows of a large table, you're probably expecting that space to be reclaimed eventually. If, on the other hand, you do a big restructuring where you're deleting 90% of your rows in one big swoop... then it's probably actually easier and faster to just create a new table and copy the 10% valid rows into it, then drop the old table, and rename. All space reclaimed. It's definitely something annoying to have to keep in mind, and it's why I prefer to use MyISAM when doing big data manipulation on my local dev machine (everything's faster without transactions too). But using InnoDB in production, I've found the never-shrinking-table-files to be a nonissue. Curious if anyone else has had different experiences.

erevlydeux 13y ago

(shameless plug here) Galera doesn't come out of the box with a secure SST option. SST, or state snapshot transfer, is how it bootstraps a new/failed node to get it close enough to continue with regular Galera replication. If you're running on a public cloud, you might care about secured SST communication. I wrote a drop-in secure rsync SST option, based on socat, that uses SSL encryption to seamlessly secure that traffic. Not perfect by any means, but it works without any futzing around. https://github.com/tobz/galera-secure-rsync https://github.com/tobz/galera-secure-rsync

jolan 13y ago
They just released a socat-based solution a couple days ago: http://www.percona.com/doc/percona-xtradb-cluster/release-notes/Percona-XtraDB-Cluster-5.5.33-23.7.6.html http://www.percona.com/doc/percona-xtradb-cluster/release-no...
- erevlydeux 13y ago
  Interesting. :)

davidjhall 13y ago

Why MariaDB+Galera vs. MySQL Cluster? I read the political decision of Maria being more robust and non-Oracle, but was MySQL Cluster considered? And if not, why?

erevlydeux 13y ago
At that rate, I'm curious why Percona XtraDB Cluster (PXC) wasn't in the lineup of considerations.
- nknighthb 13y ago
  PXC and MariaDB+Galera are mostly the same code.
  - jolan 13y ago
    I think the only difference is MariaDB has done more work on the query optimizer. Maria uses Percona's InnoDB patchset (XtraDB). Both use the galera patch from codership. I don't think Percona merges anything back from Maria.
    - nknighthb 13y ago
      I'm under the vague impression Percona's codebase has some additional patches involving backup and management that haven't been pulled into MariaDB, but I might be mistaken. Obviously there's also simple release cycle lag.
      - druiid 13y ago
        The percona tools are mostly outside the direct mysql environment and more on the innodb or linux end of things. I'm not personally aware of any that rely on codebase changes.
nknighthb 13y ago
Dunno about the author here, but when I looked at MySQL Cluster a while back, it immediately disqualified itself for lacking foreign key support. That's apparently been newly added in the last few months, but there's still no shortage of annoying limitations to be had. http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-limitations.html http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-limitat... And apparently they require a minimum of six nodes. And the supported installation method is a GUI installer. Sounds like typical "enterprise" Oracle trash, further eroding whatever confidence I might have in it.

contingencies 13y ago

Apologies for a slight tangent, but if you really need SQL's structure and you can't shard the data to avoid scaling issues, please let me know what kind of system you are running because it IMHO has to be pretty weird. Even in these cases, there are always other potential options, for instance using query playback with a combination less frequent FS or blockstore layer provided snapshot functionality. In practice, in my own experience, generally when people get an SQL based database to a huge size the often greater issue is that someone has a huge mess of bad application code relying completely on the current database configuration that is not time or cost-feasible to modify.