[Maria-discuss] Help to size: Best configuration for a lot read and write
Hello everyone, 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. Here are my questions: - What is the best solution for performance and security? - On a cluster Master / Master (three node) can I write on the 3 master node simultaneously (with HAProxy or another product)? Thank you in advance for your help. Best Regards, Tristan
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, 1. You have to start from metrics for your servers. Metrics from OS (cpu, memory, disk, network) and MySQL (grab 'show global status') 2. Look at them, think :) I think - without information from the first paragraph, good start point will be increasing innodb_buffer_pool_size <https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size> (for read access), and put log to another drive. It's, probably, most general things. Master-Master - is a complicated thing in a case of heavy writes (usually, solution is Galera - but there are lots limitations). Try to think about sharding or splitting business logic. 2016-02-19 17:26 GMT+02:00 Tristan Auriol <tristan.auriol@bettr.com>:
Hello everyone,
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.
Here are my questions: - What is the best solution for performance and security? - On a cluster Master / Master (three node) can I write on the 3 master node simultaneously (with HAProxy or another product)?
Thank you in advance for your help.
Best Regards, Tristan _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Guillaume Lefranc -
Igor Mazur -
Tristan Auriol