2

Currently, i am researching about galera cluster using many of servers(linux centos). Scaling up read traffic is very effective and easy, but scaling for write seems difficult(not improved).

I have used many servers, using maxscale as router(Readconnroute) to distribute write queries in paralles to all servers. But the write speed is not improved.

tesmojones
  • 2,496
  • 2
  • 21
  • 40
  • Please describe what your writes are like. There are several techniques for speeding up writes other than depending on Galera. Some involve 10-fold speedup. – Rick James Nov 12 '16 at 22:26
  • i use mariadb, and then i want to scale-out (add some servers) to improve the write speed, also for availability. – tesmojones Nov 14 '16 at 01:34
  • Are you inserting 1 row at a time? Are the rows coming from more than one client? Are you using autocommit? Is it a simple `INSERT` versus `INSERT IGNORE`, IODKU, or some other flavor? – Rick James Nov 14 '16 at 07:18
  • If your base time comes from a 3-nodes system, I don't think you can more than double the write throughput by adding more nodes. Since there is a limit here, I want to help you in other ways. – Rick James Nov 14 '16 at 07:20
  • i use huge insert queries just for test, and use some of concurrent users/threads. Please tell if there are ways to improve the write speed after scale/add other servers. It is ok to use other plugin/module, instead of galera. – tesmojones Nov 16 '16 at 09:19
  • "huge" = long string? or 100 rows? Or a million rows? How many clients? Hopefully more clients than nodes? – Rick James Nov 16 '16 at 17:16
  • What kind of data? Logs? "Likes"? "Views"? Clicks? Something else. I ask because different techniques work better with some kinds of data than others. – Rick James Nov 16 '16 at 17:19

2 Answers2

1

One option would be to use the Spider storage engine in MariaDB. It supports sharding of tables and should improve write speeds compared to a Galera cluster. On the other hand, you will lose the high availability of the Galera cluster in favor of increased write speeds.

This slide set by Kentoku Shiba on Spider is a good overview of how Spider improves write scalability.

markusjm
  • 2,358
  • 1
  • 11
  • 23
0

Galera does not improve write speed, as all servers will have to process all writes. MySQL is very poor for scaling writes. You could do it with a proxy (like you mentioned maxscale). Then you can shard your data. You have to pick a key for each table to distribute keys to multiple servers.

I would suggest to use another nosql server i.e. mongodb, which have sharding capabilities built in for write heavy use cases. Mongodb is much easier to set up and to maintain than mysql for this job.

edlerd
  • 2,145
  • 1
  • 16
  • 24