0

I want to create High availability with Master Slave replication (for Select query).

I am wondering which one is better:

  1. Having smaller resources slaves but more servers or
  2. Having larger resources slaves but less servers

I only use One Master for modify query: (update / insert / delete)

yodann
  • 101
  • 1

1 Answers1

1

It depends.

The most likely thing to adjust in hardware is RAM. If you tune innodb_buffer_pool_size to about 70% of RAM and that is bigger than the size of all the data, then there will be very little I/O.

If that is not practical (or too expensive), then if the "working set size" is small enough, you still get benefit for that cache.

Else you will need some I/O horsepower.

Don't worry about the speed or the number of CPU cores. That is rarely the bottleneck.

Use this to discover which queries are in the most need for optimizing: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Add 2 cheap Replicas and see how well they perform, then come back with some more numbers to discuss.

Rick James
  • 2,058
  • 5
  • 11
  • My slave database is 16Gb, and this is the innodb config innodb_buffer_pool_size=2G innodb_buffer_pool_instance=7 is it good enough? – yodann Aug 30 '21 at 06:22
  • is that 70 percent of the database size no? that's what it has been answered to keep the i o low – djdomi Aug 30 '21 at 07:16
  • ah yes, now I get it. I have set innodb_buffer_pool_size=12G, around 70%. but how many innodb_buffer_pool_instance to set? – yodann Aug 30 '21 at 07:39
  • The buffer_pool should be bigger than the dataset, but definitely not bigger than available RAM. 70% of _RAM_ is a good setting. – Rick James Aug 30 '21 at 17:24