uncertainty about "isolation levels" and risk of deadlocks with drupal

[mj | galera]

Hi,

Quoting from this galera (https://galeracluster.com/library/documentation/isolation-levels.html) page:

"WARNING: When using Galera Cluster in master-slave mode, all four isolation levels levels are available to you. In multi-master mode, however, you can only use the REPEATABLE-READ level"

We did not know you can run a galera cluster in master-slave mode? We wanted to run galera in multi-master mode, and connect our drupal web sites to galera through proxysql.

Drupal (https://www.drupal.org/docs/system-requirements/setting-the-mysql-transaction-isolation-level) says: "The recommended transaction isolation level for Drupal sites is 'READ-COMMITTED'." ProxySQL by default is also configured READ-COMMITTED.

Questions:

1) where can I read more on galera in master-slave node, in order to make all four (including the read-commited) isolation levels levels available?

2) if configure proxysql to redirect all WRITES to the same galera backend server and use the other galera nodes only for reading, how real is the risk of these deadlocks?

But also more generally: anyone with tips and ideas on the subject of drupal with galera, do's and don'ts, anything is welcome really. (success stories, as well as failures)

Or ideas for other DB solutions to achieve HA and maximise db uptime, spread load and reduce SPoFs.

Thanks in advance!

MJ

[cYuSeDfZfb cYuSeDfZfb]

Hi,

Could anyone here kindly give some feedback on this?

Thanks!

> --
> You received this message because you are subscribed to the Google Groups "codership" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to codership-tea...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/codership-team/29d25128-780b-4bd1-be0f-3edb61dcf4d1n%40googlegroups.com.

[mohsen shahbazi]

Hi mj, 

about isolation levels , i didn't find any links even on severalnines or percona to describe all levels completely but you can find about this levels separatly on percona blogs . 

and also about read-commited on drupal , as you can see on isolation levels if you choose this level it will end up in non-repeatable read and inconsistency in select queries data . it may not use dirty pages but it's not affect any performance increase if you need . 

i suggest if you have enough resource , switch to multi-master cluster using galera and then distribute read/write between nodes then optimize nodes based on DB structure /index fields or any OS based optimization that may need .