Questions for 'Tips on Converting to Galera' on MariaDB KB [closed]

Question

Some questions about the excellent writing Tips on Converting to Galera.

Is "cross-colo writing" cross connect datacenters write?

Also, you should use "START TRANSACTION READONLY" wherever appropriate.

this means the transaction will be handled only by one node (no replication latency)?. Are there cases when a SELECT without START TRANSACTION READONLY involving more than one node? SELECT marks it's a read?

Transaction "size"

(This section assumes you have Galera nodes in multiple colos.) Because of some of the issues discussed, it is wise to group your write statements into moderate sized BEGIN...COMMIT transactions. There is one latency hit per COMMIT or autocommit. So, combining statements will decrease those hits. On the other hand, it is unwise (for other reasons) to make huge transactions, such as inserting/modifying millions of rows in a single transaction.

Is this the max transaction size? Is it recommend to use close to max transaction size? Can you give a value what number you mean with moderate sized BEGIN...COMMIT transactions

MariaDB [(none)]> select @@wsrep_max_ws_size;
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
|          1073741824 |
+---------------------+
1 row in set (0.00 sec)

Critical reads

Here is a 'simple' (but not 'free') way to assure that a read-after-write, even from a different connection, will see the updated data.

when an app needs to use a read-after-write? What's the use case for read-after-write? But anyway then this read-after-write should be in the same transaction? Please a little more info about read-after-write and when to use it.

Variables that may need to be different (...) innodb_doublewrite - ON: When an IST occurs, want there to be no torn pages? (With FusionIO or other drives that guarantee atomicity, OFF is better.)

what are torn pages? I translated torn in my native language and now even more confused.

Miscellany (...) DROP USER may not replicate?

Any more details and infos? We use automated DROP USER since a few years and never noticed issues. Did we just didn't saw the problems?

Answer 1

(I guess I better answer this.)

"colo" = "co-location" = "datacenter" -- meaning a non-trivial latency between nodes.

SELECT ... FOR UPDATE should be in a transaction.

An example of where you would like to do stuff on one node without bothering the others: You need to compute a bunch of stuff to get the data to INSERT in a table. But you are happy to do the computation on a single node and not worry if the node crashes. For example, you might have some other mechanism to recover from a crash. Think "idempotency".

"Transaction size" -- well that should probably say "how long the transaction takes to run". At some level, lots of rows being modified makes it take longer, but that is not the only cause of "size".

Too big -- more chance of cross-node deadlocks. Too small -- more effort and delay in doing the cross-node COMMIT.

Critical read: User write a comment on a blog, then clicks submit. The next page comes up but fails to show that comment. Why? The write went to one node; the read went to another node, but it had not gotten there yet. (This is perhaps the only realistic example of why Galera does not claim to be fully synchronous. The check in the gcache only checks for "will it successfully commit", not "has it already been written to disk".)

"Torn page" -- An innodb unit of data is a 16KB block. The typical disk thinks in units of 512-byte blocks. InnoDB can recover from a power failure if its blocks are entirely written or not written at all. But if only part (N*512, but less than 16K) is written it is a "torn page". So, InnoDB stands on its head with "doublewrite buffer" to be able to recover from such. This involves a non-trivial amount of I/O. But, since Galera can completely recover a dead server, you can allow torn pages if you are willing to rebuild the node after a power failure.

DROP USER involves MyISAM tables in the database mysql. MyISAM tables are (or were) not replicated. I suspect that Galera has made sure that DROP USER works. However, since all the GRANT stuff involves MyISAM, I suspect there are cases where it can fail. (MySQL 8.0 moves GRANT stuff into InnoDB tables, thereby making it crash-safe.)

Thanks for pointing out the flaws in the article. I will try to make improvements to http://mysql.rjweb.org/doc.php/galera and the MariaDB link you gave. (Some day)