2

I have two different MariaDB servers and plan to merge data into one database and create MariaDB Cluster with 3 nodes. I read about Galera and multi-master replication - which MariaDB Cluster use and have some questions. And I not use transactions, foreign keys, etc...

1) If I understand it well - unlike of MySQL Cluster on Galera I do not have to worry about auto increment values. Config auto_increment_offset is not needed with Galera. Is this true?

2) I read many articles and discussions about data consistency and replication but still have questions. If application not use transaction and for example run 10x query "UPDATE a SET b=b+1 WHERE c=1 LIMIT 1". Can I be 100% sure that "SELECT b FROM a WHERE c=1 LIMIT 1" return right value? On one slideshare presentation I read - "Data does not belong to a node - Node belong to data". So, it's true that Galera replicator take all care about consistency and no changes in application is needed?

1

1 - Yes, Galera takes care of auto increments for you, but you will get lots of missed ids, so make sure your fields are big enough (e.g. you might need a smallint somewhere whereas without missing ids you could have got away with a tinyint).

2 - Yes, no changes should be needed to the application, except for occasionally you might get deadlocks. You should come up with a system to check error messages automatically and if you get errors like Deadlock found when trying to get lock; try restarting transaction and Lock wait timeout exceeded; try restarting transaction then redo the query and it should go through ok. The data will remain consistent. There is also a Galera setting for automatic retries, but we found it was better to try them again yourself. Obviously you shouldn't be using mysql_query() at all, but for the purposes of a simple to follow example, you could swap your queries to use something like this instead of your usual query function

function vio_mysql_query_deadlock_safe($query, $db = null) {
    $deadlock_retries_done = 0;
    $deadlock_retries_max = 10;
    while ($deadlock_retries_done < $deadlock_retries_max) {
        if (is_null($db)) {
            $q = mysql_query($query);
        } else {
            $q = mysql_query($query, $db);
        }
        if ($q === false) {
            $error = mysql_error();
            if ($error == 'Deadlock found when trying to get lock; try restarting transaction' || $error == 'Lock wait timeout exceeded; try restarting transaction') {
                $deadlock_retries_done++;
                continue;
            } else {
                throw new Exception($error . '. Query: ' . $query);
            }
        }
        return $q;
    }
    throw new SQLException($error . '. Re-tried with deadlock ' . $deadlock_retries_done . ' times. Query: ' . $query);
}
2
  • Thanks for answer. This is old question and I run cluster over month. In beginning I have some conflicts (but no deadlocks). This is perfectly solved with sending all writes (insert,update,delete,...) to only one server. And for better balancing I give to "write" server lower priority in haproxy. So S1 have all writes and read balancing is S1 20%, S2 40%, S3 40%. From many tests, this is best solution and all works fine. In peak hours I have about 150k users online and no conflicts :-) – stix Jun 16 '14 at 22:01
  • 1
    I'm pleased it's working well for you, we found sometimes at busy times that a write might happen on one node and not instantly be there on another node to read. We had to switch on wsrep_causal_reads="ON" on my.cnf as slightly slower performance (not noticeable though) was better than somebody placing an order but the order not existing when the got the confirmation page a fraction of a second later! Surely if you split your reads and writes, you're not taking advantage of one of Galera's best features (multi-master)? – Luke Cousins Jun 17 '14 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.