Load balancing database server and read/write operations

[Anay]

Hello, 

We have been handling few high traffic KVS websites for our client and are using cluster of 3 big separate database servers for database load balancing. So far it is working fine and we can handle a lot more traffic now than compared to single DB server setup and that too with comparative lower expanses. However, time to time we are facing Deadlock Transaction issue. They are not much but we assume that in future with more traffic and increased write operations it could be an issue. And simple solution is to separate READ and WRITE operations at APP level. 

I went through previous multiple discussions here on KVS forums and I see that there is no Load Balancer support for Database yet. However, I was thinking, is it possible to configure KVS to separate READ and WRITE operations. 

Something in the order that via Config, instead of one DB server, we have ability to specify separate WRITE server and READ server, and if only 1 server is specified then it is used for both READ/WRITE operations. 

On coding level, though I have not seen any code but I am sure you must be using some custom layer/routine where all queries are centrally processed , so there we can use logic and send queries to separate servers based on function or routine called. ie if write or read. 

If it is already there or there is quick way around to achieve then would love to hear.

[Tech Support]

This is not currently supported by KVS. This is something that we are planning to have in future with our nextgen architecture, but in order to have this we need to refactor all the code, which will take some long time.

The problem with separating queries between multiple DB instances is not that simple from our understanding. You can probably route all INSERTS / UPDATES / DELETES into a master DB, while all SELECTS into other DBs. However this will produce logic errors, when selects and updates are connected to each other and belong to the same transaction (KVS doesn't use any InnoDB transactions, so not possible to tell if the current SELECT is part of any transaction or not). Therefore we state that using multiple DBs is not officially supported.

At coding level, all queries are executed via sql() function inside /admin/include/functions_base.php.

[hbarnetworks]

On 3/22/2023 at 8:08 PM, Anay said:

Hello, 

We have been handling few high traffic KVS websites for our client and are using cluster of 3 big separate database servers for database load balancing. So far it is working fine and we can handle a lot more traffic now than compared to single DB server setup and that too with comparative lower expanses. However, time to time we are facing Deadlock Transaction issue. They are not much but we assume that in future with more traffic and increased write operations it could be an issue. And simple solution is to separate READ and WRITE operations at APP level. 

I went through previous multiple discussions here on KVS forums and I see that there is no Load Balancer support for Database yet. However, I was thinking, is it possible to configure KVS to separate READ and WRITE operations. 

Something in the order that via Config, instead of one DB server, we have ability to specify separate WRITE server and READ server, and if only 1 server is specified then it is used for both READ/WRITE operations. 

On coding level, though I have not seen any code but I am sure you must be using some custom layer/routine where all queries are centrally processed , so there we can use logic and send queries to separate servers based on function or routine called. ie if write or read. 

If it is already there or there is quick way around to achieve then would love to hear.

Hi Anay,

I have the same setup using a 3 server Galera cluster with proxySQL in front, with proxySQL you can send all write requests to the same server. And send the reads to all 3.

[Tech Support]

10 hours ago, hbarnetworks said:

I have the same setup using a 3 server Galera cluster with proxySQL in front, with proxySQL you can send all write requests to the same server. And send the reads to all 3.

Yes, but then you may get data consistency issues, because it doesn't know if the current read is part of the active transaction or not. Not sure if there are many cases like that in KVS, but usually all background processes are working to read / write data.

[Tech Support]

I would add, if you want to reduce database load and do not get any side effects, you can put a change in functions_base.php sql() function to redirect all requests that have this "ktvs_videos.video_id<>" to secondary databases. These requests are for requesting related videos, they are the most overloading requests for the database. Moving them will give database performance benefits.

However if you have the latest KVS version, you can also move them to SphinxSearch using external search plugin and reduce MySQL load as well. Same effect with no custom changes in KVS code.

[Anay]

3 hours ago, Tech Support said:

I would add, if you want to reduce database load and do not get any side effects, you can put a change in functions_base.php sql() function to redirect all requests that have this "ktvs_videos.video_id<>" to secondary databases. These requests are for requesting related videos, they are the most overloading requests for the database. Moving them will give database performance benefits.

However if you have the latest KVS version, you can also move them to SphinxSearch using external search plugin and reduce MySQL load as well. Same effect with no custom changes in KVS code.

Have already implemented sphinx search and it was helpful. 

[Anay]

13 hours ago, hbarnetworks said:

Hi Anay,

I have the same setup using a 3 server Galera cluster with proxySQL in front, with proxySQL you can send all write requests to the same server. And send the reads to all 3.

With  READ-WRITE separation using ProxyQL, do you find any deadlock or other issues. How long you have been running the setup?

[hbarnetworks]

On 3/24/2023 at 8:18 AM, Tech Support said:

Yes, but then you may get data consistency issues, because it doesn't know if the current read is part of the active transaction or not. Not sure if there are many cases like that in KVS, but usually all background processes are working to read / write data.

So far I haven't had any issues. I don't think you get any data consistency when you only write to 1 specific server. Granted it is a nvme lan setup so it might process fast enough to not encounter these issues.

On 3/24/2023 at 11:47 AM, Anay said:

With  READ-WRITE separation using ProxyQL, do you find any deadlock or other issues. How long you have been running the setup?

I did not get any deadlocks. but I dont use MYISAM I use INNODB. Which is by itself doesnt really deadlock. However it is not supported in all cases on the KVS platform.

[Mich]

How much traffic do you have guys ?

Do you have too much traffic or too slow server ? On my end, KVS is really optimized and my CPU is a Ryzen 3600 with MariaDB 10.6 (well configured for using more ram than default)

My load average is 0.5 and having 6 232,06  MYSQL connections per hours or 6267k (42d)

or requests 103 172 per hours or 103m for 42d

Edited March 29, 2023 by Mich

[Tech Support]

It is not about traffic, it is about number of videos in your database. High traffic can be easily coped with caching, unless you have 200k+ videos and caching becomes less effective.

[Mich]

Ah yes, make sense.

Videos/Variations ..

Edited March 30, 2023 by Mich

[Helmuts DNMUM.com]

wow.. what a thread!!!

@Anay and @hbarnetworks, how many videos do you have? do you remember at which point you started hosting the database/s remotely? looking in the future as one of my clients grows (his main site has around 14k videos and around 1.2mil users/month) > it is super valuable to know what awaits behind the corner.

great day all!!

[Helmuts DNMUM.com]

@Tech Support, and, of course, thank you for your replies!!! of course! uber valuable!

I wish we had more valuable conversations like these :)

Helmuts

[hbarnetworks]

On 4/7/2023 at 4:57 PM, Helmuts DNMUM.com said:

wow.. what a thread!!!

@Anay and @hbarnetworks, how many videos do you have? do you remember at which point you started hosting the database/s remotely? looking in the future as one of my clients grows (his main site has around 14k videos and around 1.2mil users/month) > it is super valuable to know what awaits behind the corner.

great day all!!

Currently 50k videos with a between 22-25 mil users/month. I always had the database remotely. I have alot of custom features that requires alot of database queries so results may differ. If you have a heavy caching solution 1 database can handle a couple million users easily. (depending on core and GHz of-course)

I needed to scale more servers not just the database but also the front-end at around 10ish due to PHP limitations

Edited April 10, 2023 by hbarnetworks
Extra info

[xvids]

On 3/30/2023 at 7:56 AM, Tech Support said:

It is not about traffic, it is about number of videos in your database. High traffic can be easily coped with caching, unless you have 200k+ videos and caching becomes less effective.

What about If we create separate DB for more than 200k+ videos.

 

if (Videos Less than< 200K)
   {
   define('DB_HOST','db_host_1);
   }
   
if (videos > 200K)
   {
   define('DB_HOST','db_host_2);
   }
   
if (videos > 400K)
   {
   define('DB_HOST','db_host_3);
   }

 

[Tech Support]

KVS doesn't officially support running under multiple databases.

[xvids]

12 hours ago, Tech Support said:

KVS doesn't officially support running under multiple databases.

And what about If you make it support, is it possible?

[Tech Support]

It is not possible at the moment, because we think we need to rewrite the whole KVS code to add this support.

[hbarnetworks]

9 minutes ago, Tech Support said:

It is not possible at the moment, because we think we need to rewrite the whole KVS code to add this support.

Just curious. But aren't you guys rewriting the entire code right now?

[Anay]

KVS with its caching is quite optimized but you have limits and hence as you grow you will need horizontal scaling. 

@Helmuts DNMUM.com, @Mich we have more than 200K videos and initially we went for vertical scaling but it was getting slow, now with horizontal scaling ie. cluster, its doing good and looks like it has quite a room left for future growth.

[Anay]

@xvids, multiple DBs can be considered but better approach would be separation of read / write operation, it is pretty common with large operations or deployments. 

[Tech Support]

15 hours ago, hbarnetworks said:

Just curious. But aren't you guys rewriting the entire code right now?

Yes, we constantly add new classes into the nextgen architecture and expand it with new features, but this process will take time.

[Helmuts DNMUM.com]

On 4/17/2023 at 5:17 PM, Anay said:

KVS with its caching is quite optimized but you have limits and hence as you grow you will need horizontal scaling. 

@Helmuts DNMUM.com, @Mich we have more than 200K videos and initially we went for vertical scaling but it was getting slow, now with horizontal scaling ie. cluster, its doing good and looks like it has quite a room left for future growth.

@Anay Are you coming to TES (Prague) this September? .. it would be great to meet.

And, what about you @hbarnetworks?

It would be great to meet KVS users that have found the ways how to deal with traffic you both are getting.

.. a friend of mine @Westcircle is also coming (his tube network is getting around 2.5 mil/month)

--

I am planning to take a small stand there > I plan to attend both European TES conventions every year and will start offering KVS multi-server setup solutions.

 

[hbarnetworks]

On 5/2/2023 at 11:24 AM, Helmuts DNMUM.com said:

@Anay Are you coming to TES (Prague) this September? .. it would be great to meet.

And, what about you @hbarnetworks?

It would be great to meet KVS users that have found the ways how to deal with traffic you both are getting.

.. a friend of mine @Westcircle is also coming (his tube network is getting around 2.5 mil/month)

--

I am planning to take a small stand there > I plan to attend both European TES conventions every year and will start offering KVS multi-server setup solutions.

Not quite sure how attending this would make KVS multi-server XD? But no I am not really into going to conventions. So sadly I will not be attending.

[Helmuts DNMUM.com]

On 5/27/2023 at 11:47 AM, hbarnetworks said:

Not quite sure how attending this would make KVS multi-server XD?

Many x industry webmaster are using KVS professionally. This is a great chance to create personal relationships with other kvs users, other hosting companies that provide servers for kvs.. and, as a bonus, there is a good chance that Stan from kvs will be there as well.

:) is it worth it? 1000% for me.