Audit
This is often the starting point for most of my interventions. Before working on specific problems, we need to make sure that the foundations are well established.
I always start with the lower layers, then work my way up to the applications:
Linux configuration and tuning
I inspect storage (controller cards, raid, LVM…), Linux kernel configuration (memory, scheduler, disk).
With cloud providers, we obviously don’t have access to this information, but we do need to perform a similar step, which consists in studying the documentation and technical specifications of the instances (CPU, Burst, IOPS…).
System activity review
I look at how the server or instance is behaving. Are storage, CPU and RAM saturated?
Postgres activity
Same as system activity, I understand what Postgres is doing : maintenance tasks (vacuum, checkpoint…), locking, errors….
This will help me to understand what is your database workload, know what and where to look for. So, I will be able to suggest a better configuration.
Postgres Tuning
Did you know that there are over 350 configuration parameters? The number increases (reasonably) with each new version. Some disappear, others appear.
Review of all Postgres configuration. The default configuration is very conservative, and needs to be reviewed. Automatic configuration tools such as PGTune are often inadequate and unsuitable.
Tell yourself that if it were enough to apply simple rules, they would already be implemented in Postgres!
Review of slow / power-hungry requests
Now that I have a better idea of the status of your instance, I can start inspecting slow and/or time-consuming queries.
It’s usually at this step that you can expect the biggest performance gains.
Of course, I won’t just point out the problematic queries. I’m going to suggest :
- Correct the query
- Creation of new indexes
- Review application behavior
Schema review
- Are any indexes missing? Are you using the right index type?
- Are the types used correctly?
Report
The review is a complete PDF report. I don’t just apply corrections. I explain the corrections so that you can become more independent and avoid making the same mistakes.
