Upgrade pontoon's PostgreSQL plan to increase speed of queries

RESOLVED WONTFIX

Status

Webtools
Pontoon
P2
normal
RESOLVED WONTFIX
a year ago
a year ago

People

(Reporter: adrian, Assigned: mathjazz)

Tracking

(Blocks: 1 bug)

Trunk
Dependency tree / graph

Firefox Tracking Flags

(Not tracked)

Details

(Reporter)

Description

a year ago
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.
(Assignee)

Updated

a year ago
Assignee: nobody → m
(Assignee)

Comment 1

a year ago
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?
Blocks: 1357361, 1377099
Flags: needinfo?(adrian)
Priority: -- → P2
(Reporter)

Comment 2

a year ago
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!
Flags: needinfo?(adrian)
(Assignee)

Comment 3

a year ago
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).
Blocks: 1379149
Status: NEW → RESOLVED
Last Resolved: a year ago
Resolution: --- → WONTFIX
(Assignee)

Updated

a year ago
See Also: → bug 1382207
You need to log in before you can comment on or make changes to this bug.