-
RDS slow read processing/WordPress
updated 3 years, 11 months ago
2 Members
·
2
Posts
-
6 options presented, select 2.
Obviously 1 option is Read replicas, leaves us to pick another one, from the most likely candidates: elasticache or db instance larger type.
a) is it not explained why “Upgrade the instance type of the RDS MySQL database instance to a larger type.” is incorrect (spoiler: it isn’t IMO)
b) then I was going to write ‘as utilizing Elasticache will incur application changes, this option is not an easy one’, only then to realize it was a WordPress site.
So this I found upon digging:
1) there is a plugin for WordPress that you can use to integrate with Amazon ElastiCache for Memcached:
https://aws.amazon.com/elasticache/memcached/wordpress-with-memcached/
Then I wanted to know about the read-replicas and WordPress, and found this whitepaper:
2) https://d1.awsstatic.com/whitepapers/wordpress-best-practices-on-aws.pdf
which states (about Aurora, but MySQL is similar):
“To improve the performance of your database you can select a large instance type to provide more CPU and memory resources. ” (! obviously this has limits, and elasticache will give you more improvements probably, agree)
and ” You can create […] low-latency read replicas across three Availability Zones.” but it doesn’t go into detail about how to use the read replicas.
So after more digging I found:
the Route53 distribution solution for RDS (MySQL) read replicas (Aurora has a reader endpoint built in, with auto-loadbalancing)
3) https://aws.amazon.com/premiumsupport/knowledge-center/requests-rds-read-replicas/
and this WordPress plugin as advanced database interface that supports read replicas configuration:
4) https://github.com/stuttter/ludicrousdb
Well, thats all then! (to add to the explanation maybe?)
oh well not…
There’s another scenario with the same issues (‘leading electronics company is getting ready to do a major public announcement of its latest smartphone’)
The problem stated is ‘customer information that is fetched from the database’
Caching doesn’t seem to make a lot of sense here I think, as a customer will only have an active session of let’s say <20min per day, and probably also not every day.
Why should I cache his info? Session info should be cached at the session handling layer.
Increasing instance size and IOPS make much more sense here IMO.
Only on the costing side this is indeed MUCH more expensive, e.g.
elasticache: cache.r4.8xlarge 32 vCPU 203GiB RAM, network 10Gigabit, us-east-1, reserved, 1 year, upfront $8656 (https://aws.amazon.com/elasticache/pricing/)
rds mysql: db.r5.12xlarge multi-az, us-east-1 reserved, 1 year, upfront $54,393 + storage iops (https://aws.amazon.com/rds/mysql/pricing/)
Cheers,
Robert -
Hi Robert,
Thank you for your analysis and feedback.
To tackle this question, we need to focus on the problem it is asking.
“root cause is due to the slow read processing in your database tier.”
With this, there are two main ways to improve your Database read performance: Read Replicas and caching (using Elasticache). ElastiCache, especially Memcached provides easy deployment for read-only caching, suitable for this scenario.
These two options are mainly chosen for “scalability”. It is very easy to add Read Replicas, one-click on the console and you will have another node joining the Read-Replica pool.
ElastiCache uses in-memory caching, which offers very high throughput and very fast response time.
Increasing the Disk IOPS and/or increasing the instance type will not come close to the performance that you will get with in-memory caching in terms of read-speeds. Another benefit of caching is that it reduces the load on the RDS server when multiple queries are on the cache, the application does not need to query the database.
https://aws.amazon.com/elasticache/
https://aws.amazon.com/rds/details/read-replicas/
https://aws.amazon.com/elasticache/memcached/
Now let’s tackle why other options are incorrect.
“Upgrade the RDS MySQL instance to use provisioned IOPS.”
“Implement sharding to distribute the incoming load to multiple RDS MySQL instances.”
– Yes, these options increase read-performance but it increases write performance too. You don’t want to pay for something you will not use. You will have to set up multiple databases for sharding, so that is difficult and not cost-effective, just to increase your read performance.
“Upgrade the instance type of the RDS MySQL database instance to a larger type.”
– There is a limit on how much you can scale and how much read-performance you can get from a bigger instance. An in-memory cache will outperform the performance of disk-based RDS read operation.
“Set up a Redis in-memory cache cluster running in an EC2 instance on each Availability Zone and enable replication on all nodes.”
– It’s not recommended to run your own caching mechanism on EC2. AWS offers a managed solution for that.
Hope this helps.
Regards,
Kenneth Samonte @ Tutorials Dojo
Log in to reply.