PW 3.0.175 – Core updates: new DB scalability feature

[ryan]

This week ProcessWire gained the ability to maintain separate read-only and read-write database connections to optimize scalability, cost and performance. The post covers why this can be so valuable and how to configure it in ProcessWire—

https://processwire.com/blog/posts/pw-3.0.175/

[bernhard]

Crazy stuff - I hope I'll need this feature one day ?

[markus_blue_tomato]

Just tried activated it for https://corporate.blue-tomato.com/ which runs on Digital Ocean. Seems it works well.

[StanLindsey]

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. 

[ryan]

@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?

[markus_blue_tomato]

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.

[StanLindsey]

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!

[HMCB]

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!

[adrian]

@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!

[elabx]

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.

[Craig]

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.

[adrian]

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!

[adrian]

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.