Jump to content

PW 3.0.175 – Core updates: new DB scalability feature


ryan
 Share

Recommended Posts

This is great @ryan - Quick question. 

Digital Oceon doesn't automatically balance between all the read only nodes as RDS does. 

Is it possible or is it on the roadmap to allow adding multiple nodes in PW and PW distributes between that list of nodes. 

Could be as simple as an array of dbReaders, with PW randomly selecting from the array for each read request. Or round robin. 

That would allow it to be much more flexible setup and support self hosting read replicas for example. 

Otherwise fantastic work this is great. 

  • Like 5
Link to comment
Share on other sites

On 4/10/2021 at 3:20 PM, StanLindsey said:

This is great @ryan - Quick question. 

Digital Oceon doesn't automatically balance between all the read only nodes as RDS does. 

Is it possible or is it on the roadmap to allow adding multiple nodes in PW and PW distributes between that list of nodes. 

Could be as simple as an array of dbReaders, with PW randomly selecting from the array for each read request. Or round robin. 

That would allow it to be much more flexible setup and support self hosting read replicas for example. 

Otherwise fantastic work this is great. 

Oh, good to know. Currently I have one Read/Write Node (for the Admin) and one Read-Only Node for the Frontend.

Link to comment
Share on other sites

On 4/11/2021 at 11:49 AM, ryan said:

@markus_blue_tomato Great, glad to hear it's working well!

@StanLindsey This would be very simple to add, I'll plan to add it this week. Question: would just an array of DB hosts be adequate, or would it need separate configuration (host plus db name, user, pass, port, etc.) for each of the readonly db hosts?

I think it's best if each have seperate configuration but work the way this initial release works where they fall back to the primary config. 

In many cases dB names and users etc will be the same, but I can totally see cases where they will be different. 

It would be a huge help!

  • Like 1
Link to comment
Share on other sites

On 4/10/2021 at 9:20 AM, StanLindsey said:

Is it possible or is it on the roadmap to allow adding multiple nodes in PW and PW distributes between that list of nodes. 

This is such a crazy good suggestion. Thank you!

Link to comment
Share on other sites

  • 10 months later...

@StanLindsey - I've just started experimenting with Digital Ocean's managed DB clusters, having one standby node and one readonly node. Everything works, but the site is quite a bit slower when I use these vs a normal MySQL DB on the droplet where the PW install is hosted. I did go for the lowest end DB cluster they offer, so:

2 GB RAM / 1vCPU

Compare this to the main droplet which is:

8 GB Memory / 4 Intel vCPUs (with an innodb_buffer_pool_size = 4G setting)

So obviously the managed DB has potentially fewer resources, but I am wondering if it's the DB's lack of resources or if it might be something else - perhaps just the requirement of connecting to another server. I see I am not the only one who has experience this issue: https://laracasts.com/discuss/channels/servers/same-query-in-digital-ocean-managed-mysql-db-more-than-doubly-slow-than-on-droplet but I would love to know your experience and if you might have any tips to share.

Thanks!

Link to comment
Share on other sites

23 hours ago, adrian said:

perhaps just the requirement of connecting to another server

Not the exact same scenario, but I experienced this using Amazon RDS and even if queries where fast, I did notice the hit of latency. But like 99% of my experience is with mysql hosted on the same machine, so nothing more to add here.

Link to comment
Share on other sites

It looks like it's just DO that has poor performance on their managed DBs. I presume you've seen this thread - https://www.digitalocean.com/community/questions/managed-db-become-very-very-slow ?

As a final test, you could match the DB machine size to the current server and try again, but I have a feeling it might not help much and there's a wider problem.

If separating out your DB is the goal, perhaps running it on a separate VM might be more than enough.

  • Like 1
Link to comment
Share on other sites

Thanks @elabx and @Craig - I hadn't seen that particular post, but I had seen others complaining of performance issues. I have been in contact with DO via a support ticket and so far they haven't been very helpful - first response basically said that it's expected because their managed DBs run on low spec'ed servers. The next response wanted me to provide examples of queries that were slow. The reality is that all queries are significantly slower and they are even worse when connecting to one of their readonly nodes. At the moment I am considering ScaleGrid, or maybe manually setting up replication something like this: https://www.digitalocean.com/community/tutorials/how-to-set-up-replication-in-mysql - although that doesn't provide all the features of the managed DB setup. 

I'd love to hear from anyone else who has a good solution for having instant DB replication, fallover/standby nodes and readonly nodes that are at least as fast as a local DB - thanks!

Link to comment
Share on other sites

So, some good news - I upped the DB clusters to 4 GB RAM / 2vCPU machines and now the performance is on par with the local DB. The downside is that this is quite expensive but I am thinking it will be worth it for the:

  • Daily point-in-time backups. Full cluster backups are taken daily and write-ahead-logs are maintained to allow you to restore to any point-in-time within the previous seven days.
  • High availability with automated failover. In the event of a failure, managed databases with a standby node will automatically switch data handling to the standby node to prevent unplanned downtime.

Obviously not needed for most sites, but I think when you have frontend users regularly modifying info in the database, having the ability to restore to any point-in-time is quite reassuring.

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...