In order to increase the speed on Pontoon, we want to try upgrading our postgresql plan to something beefier. However, to make sure this has any impacts, we'll want to gather some numbers on how the website behaves from different places. I propose we write a little script that gathers data about how fast several key parts of the website load, and that exports that data in some form. Then we ask people from different parts of the world to run those, and gather the data. Then we upgrade the database, and we re-run that script and take a look to see if there's any difference. Note that instead of writing a script, we might be able to use some existing tools. I'll investigate that.
I created a new DB instance with the Premium 2 plan (vs. Standard 0 used on the current instance) and compared performance of the slowest views. =============================================== Notable differences between plans =============================================== Standard 0 Premium 2 RAM: 1GB 3.5 GB Connection limit: 120 400 Storage capacity: 64GB 256 GB High Availability: No Yes =============================================== Execution times [ms] =============================================== VIEW /contributors/ 5986 6044 /sl/ajax/contributors/ 7607 6132 /sl/ajax/permissions/ 5866 5946 /get-entities/* 3983 2287 =============================================== * sl, firefox, all-resources, status=missing I noticed that the Data size on the newly created (Premium 2) DB was 2.85 GB vs 4.24 GB on the old one, possibly because of bloat: https://devcenter.heroku.com/articles/managing-vacuum-on-heroku-postgres#determining-bloat. So I created another Standard 0 database and performed tests on it. =============================================================== Execution times [ms] =============================================================== VIEW Standard 0 Premium 2 Premium 2 New /contributors/ 5986 6044 5320 /sl/ajax/contributors/ 7607 6132 4720 /sl/ajax/permissions/ 5866 5946 5625 /get-entities/* 3983 2287 2123 =============================================================== * sl, firefox, all-resources, status=missing Based on these numbers, I believe we should: 1. Keep using the existing DB plan. 2. Recreate production DB instance using the same plan for some instant performance wins. 3. Monitor bloat on production DB and VACUUM it regularly if necessary. Adrian, any objections?
Why do you want to recreate the database instance? Can you not instead just remove the bloat using the command mentioned in your link? Other than that, that sounds like an easy improvement, so yay!
I'm not exactly sure it's only bloat that contributes to the size increase. I'll certainly try to VACUUM first, and if that won't be sufficient, I'll create a new instance (which will require the site to be in the maintenance mode for 3-5 minutes).
Status: NEW → RESOLVED
Last Resolved: a year ago
Resolution: --- → WONTFIX
You need to log in before you can comment on or make changes to this bug.