1

I test with stress test tool (to run many concurrent user and many query). There is no speed added when i use more db server (i use 5 servers).

I have check on each server, and i see the queries have been distributed on each server.

What should i do, if i want to scale-up the database server, to add more speed on queries?

tesmojones
  • 2,496
  • 2
  • 21
  • 40

1 Answers1

1

Galera maxes out at somewhere around 5 nodes in the cluster. Probably it is due to the broadcasting of each write to every other node, and waiting for replies.

There are many ways to scale up a MySQL; Galera is one of them, and it is perhaps the best today for write scaling.

For read scaling, replication slaves provide virtually unlimited scaling. You can hang traditional replication slaves off of each Galera node. This will let you offload the reads from your 5 nodes. Slaves can be cascaded (using "relays"), thereby giving unlimited scaling. A server can easily have 10 slaves hanging off it; do 6 levels of that, and you have a million servers. (I have not worked with more than 3 levels and 30+ slaves.)

A common way of scaling up is to look at the code. Composite indexes is unknown to a lot of novices. For inserting, batching and LOAD DATA is very effective. For Data Warehousing, Summary tables can often speed up "reports" 10-fold. For high speed ingestion, ping-ponging a staging table is very good. For GUID/UUID indexes, abandoning them is best. Ditto for EAV. For huge deletes there are several approaches.

And my tips on Galera.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thank you, how about this statement "perhaps the best today for write scaling.", i have test with heavy insert queries, but there is no improve when i add nodes. – tesmojones Nov 08 '16 at 08:38
  • Single-row inserts? Batched? Using auto-increment? Etc. Please describe your "heavy insert queries"; there may be ways to improve the performance. – Rick James Nov 12 '16 at 00:34