From MySQL+MMM to MariaDB+Galera Cluster: A High Availability Makeover | Hacker News Reader

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

blog.scoutapp.com

31 pointsitsderek2320 comments

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.

(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

They just released a socat-based solution a couple days ago:

http://www.percona.com/doc/percona-xtradb-cluster/release-no...

Interesting. :)

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

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.

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.

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.

With a default my.cnf + innodb_file_per_table=1, ibdata0/ibdata1 will stay at 512M which isn't a big deal.

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.

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?

At that rate, I'm curious why Percona XtraDB Cluster (PXC) wasn't in the lineup of considerations.

PXC and MariaDB+Galera are mostly the same code.

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.

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.

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.

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-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.

That's NDB cluster, not Galera. They are apples and oranges.

Please re-read the comment I replied to, which was asking why MySQL Cluster (NDB) would not be considered as an alternative to Galera. Using Galera in production myself, I'm well aware they are different things.

(I helped write the piece)

I went with MariaDB because of the features in MariaDB - because the goal wasn't just to improve availability, but to speed up queries, and the improvements MariaDB's made to complex queries and indexing made that decision easy when compared to Percona Server or MySQL Cluster.

Reply on news.ycombinator.com