PGCon2017 - 20180510
PGCon 2017
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Ilya Kosmodemiansky |
| Schedule | |
|---|---|
| Day | Tutorials - Day 1 - 2017-05-23 |
| Room | DMS 1150 |
| Start time | 09:00 |
| Duration | 03:00 |
| Info | |
| ID | 1129 |
| Event type | Workshop |
| Track | Tutorial |
| Language used for presentation | English |
Linux tuning to improve PostgreSQL performance: from hardware to postgresql.conf
Linux operating system has lots of tuning options which can change performance of your PostgreSQL installation drastically. People often say that an Oracle DBA is 90% a DBA and 10% a UNIX engineer, for a Postgres DBA this correspondence can optimistically be 50:50 or even lower on the DBA part.
For PostgreSQL, obviously, most important are storage-related options like vm.dirty*, IO elevators or filesystem mount options, but there are many more of them: CPU scheduler options, NUMA settings, or even power saving policy. The first goal of this talk is to provide some guidelines how to chose proper hardware for a database server and explain to DBAs and engineers how to use all this settings in order to match hardware configuration and PostgreSQL workload.
The second goal is to explain the problem to hackers, which weak points we have in terms of integration with Linux and other operating systems from DBA point of view and to suggest some steps to make things better.
Linux folks often use a notion of "tunning target". A tuning target can be CPU, disks, memory, scheduler - something we focus on, with the gaol to achieve its maximum performance. PostgreSQL is a database, which entails a very complex workload. We can not simply tune a screw here or there, the whole operating system becomes our tuning target. We need to balance its settings well to achieve the maximum data throughput. Linux provides lots of settings for tuning the system, but the defaults are not about maximizing the database performance.
I will provide a guide to the key system settings that ensure the maximal PostgreSQL performance. In order to do that, I'll first give an overview of how PostgreSQL interacts with different parts of OS Linux. To demonstrate how poor Linux settings can affect PostgreSQL performance, I will provide a simple recipe:
- Which symptoms can we see on the PostgreSQL side?
- How to figure out that the problem lies in the OS configuration?
- What is actually going on and why?
- Which setting will be better and why?
Using this plan, I will cover NUMA-related settings, swap and memory-related settings, scheduler settings, I/O related settings, energy saving policies.