I want to scale mariadb database for huge number of query requests

Question

I use Moodle on centos7 with Php, Mariadb, Nginx. There are huge number of users that use this Moodle. If the number of users grows more than 300user per sec, the Moodle has delay in response and seems to be hanged!

I read about:

Galera (multi master clustering with 3nodes)
slave-master (separate read and write)
MaxScale
increase ram and cpu (I have up to: 288GB ram, 24coreCPU, SSD drive)

What is the best practice to serve huge number of requests without delay? How can I scale my database (because it is the bottleneck)? I want scale it for serve huge request (most of them is read from database)

What is a "user per second"?? Perhaps you mean "query per second"? — Rick James, Aug 05 '19 at 23:42
What query/queries are you running? Maybe they need a better index? — Rick James, Aug 05 '19 at 23:43
Please don't ask about "throwing hardware at the problem" until we have discussed the item above. — Rick James, Aug 05 '19 at 23:45
also what about: 500 user per second = about 5*500 query per second? — m.Azadi, Aug 06 '19 at 12:13
If that means 500 connections per second, that is extremely high. — Rick James, Aug 06 '19 at 16:45
Yes, and my question is: How can I scale mariadb database for huge number of query requests? If you can explain the best practice or tutorial? — m.Azadi, Aug 08 '19 at 05:40

score 2 · Answer 1 · answered Aug 05 '19 at 08:28

2

Obviously you can do generic MySQL/MariaDB tuning first, and use a recent version of Moodle (3.7 is current right now)

After that, one thing you can check is how you have sessions implemented.

https://docs.moodle.org/37/en/Session_handling

This page also has many more tips:

https://docs.moodle.org/37/en/Performance_recommendations

answered Aug 05 '19 at 08:28

Martin Dougiamas

21
3

Yes, I do all the above, also I use Redis for session and cache. My problem is the `database`. – m.Azadi Aug 05 '19 at 09:52

score 2 · Accepted Answer · answered Aug 08 '19 at 22:58

MariaDB (and MySQL) can scale 'infinitely' for reads by using Replication and sending read requests to Slave servers.

500 connections per second is very high. (But I don't know what the practical limit is.)

There are several extra tools that can do "connection pooling". Search for this; it may let you go well past 500 logical connections on a single server.

In the case of Galera, you could have 3 read-write nodes, plus any number of Slaves hanging off each of the 3.

For simple Master-Slave, there can be any number of Slaves hanging off the one Master.

I want to scale mariadb database for huge number of query requests

2 Answers2