MYSQL: Many slave servers with smaller resources or less with larger resources

yodann

12/30/22, 3:27 AM

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

I am wondering which one is better:

Having smaller resources slaves but more servers or
Having larger resources slaves but less servers

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

1 + 0

mysql

mysql-replication

database-performance

database-replication

mariadb

Score:1

Server

Rick James

12/30/22, 4:47 AM

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.

0 + 4

yodann

12/30/22, 6:22 AM

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?

djdomi

12/30/22, 7:16 AM

is that 70 percent of the database size no? that's what it has been answered to keep the i o low

yodann

12/30/22, 7:39 AM

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?

Rick James

12/30/22, 5:24 PM

The buffer_pool should be bigger than the dataset, but definitely not bigger than available RAM. 70% of _RAM_ is a good setting.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: MYSQL: Many slave servers with smaller resources or less with larger resources

TH: MYSQL: เซิร์ฟเวอร์สเลฟจำนวนมากที่มีทรัพยากรน้อยกว่าหรือน้อยกว่าด้วยทรัพยากรขนาดใหญ่

RO: MYSQL: multe servere slave cu resurse mai mici sau mai puține cu resurse mai mari

RU: MYSQL: много подчиненных серверов с меньшими ресурсами или меньше с большими ресурсами

VI: MYSQL: Nhiều máy chủ nô lệ có tài nguyên nhỏ hơn hoặc ít hơn với tài nguyên lớn hơn

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.