maria-discuss team mailing list archive

[Guillaume Lefranc <guillaume@xxxxxxxxxxxx>]

Hi Tristan,


> I have some basics about SQL. But I need your help.
> I must set up a SQL servers for a new web application. This web
> application will make a lot of write and some reads the night and
> enormously read and some writing on the day.
>

This is a complex case, as pure write workloads and pure read workloads
both answer to different setup constraints.


> Here are my questions:
> - What is the best solution for performance and security?
>

Using InnoDB would be the best solution engine-wise, if you need
performance , set innodb_flush_log_at_trx_commit=2 if you can afford losing
1 second of transactions in case of power failure. Live data set should
hold in memory if you want decent performance.


> - On a cluster Master / Master (three node) can I write on the 3 master
> node simultaneously (with HAProxy or another product)?
>

Yes, you can, but I would not recommended.
- First of all Galera has issues with heavy write loads. Keep in mind that
each write transaction has added latency due to the semi-synchronous
commit. So make sure that you have super high end hardware in case you want
to do that. I would recommend NVMe SSDs, large network bandwidth and
OS/Hardware tuned for lowest latency (governors to performance, IO
scheduler to none, vm cache to minimum ratio etc.). Also make sure that you
do not exceed maximum writeset size which is of 2GB (chunk your inserts
!!!).
- Writing on three nodes simultaneously will cause certification conflicts
!! *unless* you can be 100% sure that your app will write to different
tables on different masters. In most cases it is wiser to use a single node
as the master, and let the three nodes be available for reads. Make suse
wsrep_slave_threads=(CPU cores * 2) so your Galera slave nodes can absorb
the writes faster.
- And finally, before putting such a setup in production: Benchmark,
Benchmark, and Benchmark.

If MariaDB Galera Cluster has too many limitations for you, you may want to
look at some alternatives like Spider and semi-sync replication.

Cheers,


>
> Thank you in advance for your help.
>
> Best Regards, Tristan
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>