Anay Posted March 22 Share Posted March 22 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. 1 Quote Link to comment Share on other sites More sharing options...
Tech Support Posted March 23 Share Posted March 23 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. 1 Quote Link to comment Share on other sites More sharing options...
hbarnetworks Posted March 23 Share Posted March 23 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. Quote Link to comment Share on other sites More sharing options...
Tech Support Posted March 24 Share Posted March 24 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. 1 Quote Link to comment Share on other sites More sharing options...
Tech Support Posted March 24 Share Posted March 24 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. 1 Quote Link to comment Share on other sites More sharing options...
Anay Posted March 24 Author Share Posted March 24 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. Quote Link to comment Share on other sites More sharing options...
Anay Posted March 24 Author Share Posted March 24 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? Quote Link to comment Share on other sites More sharing options...
hbarnetworks Posted March 27 Share Posted March 27 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. Quote Link to comment Share on other sites More sharing options...
Mich Posted March 29 Share Posted March 29 (edited) 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 by Mich Quote Link to comment Share on other sites More sharing options...
Tech Support Posted March 30 Share Posted March 30 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. Quote Link to comment Share on other sites More sharing options...
Mich Posted March 30 Share Posted March 30 (edited) Ah yes, make sense. Videos/Variations .. Edited March 30 by Mich Quote Link to comment Share on other sites More sharing options...
Helmuts DNMUM.com Posted April 7 Share Posted April 7 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!! Quote Link to comment Share on other sites More sharing options...
Helmuts DNMUM.com Posted April 7 Share Posted April 7 @Tech Support, and, of course, thank you for your replies!!! of course! uber valuable! I wish we had more valuable conversations like these :) Helmuts Quote Link to comment Share on other sites More sharing options...
hbarnetworks Posted April 10 Share Posted April 10 (edited) 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 by hbarnetworks Extra info 1 Quote Link to comment Share on other sites More sharing options...
xvids Posted April 14 Share Posted April 14 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); } 1 Quote Link to comment Share on other sites More sharing options...
Tech Support Posted April 14 Share Posted April 14 KVS doesn't officially support running under multiple databases. Quote Link to comment Share on other sites More sharing options...
xvids Posted April 14 Share Posted April 14 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? Quote Link to comment Share on other sites More sharing options...
Tech Support Posted April 17 Share Posted April 17 It is not possible at the moment, because we think we need to rewrite the whole KVS code to add this support. Quote Link to comment Share on other sites More sharing options...
hbarnetworks Posted April 17 Share Posted April 17 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? Quote Link to comment Share on other sites More sharing options...
Anay Posted April 17 Author Share Posted April 17 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. 1 Quote Link to comment Share on other sites More sharing options...
Anay Posted April 17 Author Share Posted April 17 @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. 1 Quote Link to comment Share on other sites More sharing options...
Tech Support Posted April 18 Share Posted April 18 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. Quote Link to comment Share on other sites More sharing options...
Helmuts DNMUM.com Posted May 2 Share Posted May 2 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. Quote Link to comment Share on other sites More sharing options...
hbarnetworks Posted May 27 Share Posted May 27 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. Quote Link to comment Share on other sites More sharing options...
Helmuts DNMUM.com Posted May 29 Share Posted May 29 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.