Problem Description
Generally speaking, solving performance issues means finding a significant bottleneck which may cause the slowness of the database and removing it.
This implies the use of tools or methodology which can help in determining where the bottleneck is.
The activity of performance tuning should be executed as a process and being a routine step during the development phase.
Methodology and Tools
The first thing needed to face performance issues is a baseline
The baseline may be a set of performance statistics and graphs, showing the main database metrics taken when the system is first deployed and considered working with good performance.
Included in the baseline also query execution plans may be collected.
metrics baseline and execution plans may be be used later to understand what changed from the initial optimal configuration.
So a baseline may be a set of graphs like these ones:
and a list of execution plans like this one:
explain analyze select sum(id) from testing;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1855.06..1855.07 rows=1 width=8) (actual time=23.166..23.168 rows=1 loops=1)
-> Seq Scan on testing (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.150..13.197 rows=100000 loops=1)
Planning Time: 0.070 ms
Execution Time: 23.239 ms
(4 rows)
Additionally you can benchmark your database before deploying your application with your usual load and for-see the expected performance.
Tuning
When you tune your database you need to approach the operation having a direction, generally they can be top -> down or the opposite down -> top
So you may start tuning doing sequentially the following:
1. Inspecting application connections timings
2. Application queries through analyzing their execution plans or profilings
3. Database object tuning, like data types, indexes, etceteras...
4. Database instance parameters
5. Hardware setup or needed, increasing number of cpus or choosing faster disks
Conclusion
This easy steps may help you in facing and solving database performance issues:
- Finding and eliminating bottlenecks
- Using a baseline for comparison reasons and verifying what changed
- Benchmark your database before deploying your application with a real usual load
- Approaching the tuning activity with a sequential top -> down or down -> top direction