title

text

Nikolay Samokhvalov
Nikolay Samokhvalov Nombox LLC
13:00 06 February
45 мин

Enterprise-level approach to PostgreSQL tuning: database experiments

Shared_buffers = 25% – is it too much or not enough? Or it's the right value?

How can we ensure that this – pretty much outdated – recommendation suit well our needs?

It is time to start apply enterprise-level approach to tuning postgresql.conf. Not using various blind auto-tuners or advices from old articles and blog posts, but based on the following two aspects:

  1. comprehensive database experiments, conducted in automated fashion, repeated multiple times in conditions as close to production as possible, and
  2. deep understanding of DBMS and OS internals.

Using Nancy CLI (https://gitlab.com/postgres.ai/nancy) we will consider a concrete example: infamous shared_buffers, under various circumstances, in various projects. We will try to figure out, how to optimize this settings for given infrastructure, database, and workload.

Слайды

Видео

Другие доклады

  • Lev Dragunov
    Lev Dragunov Juno
    22 мин

    PostgreSQL inside Docker

    DBMS inside container is a nightmare for DBA. I will describe how we use containerized Postgres in Juno. What problems we faced with and how did we solve them.

  • Alexander Kukushkin
    Alexander Kukushkin Zalando SE
    45 мин

    How not to screw up when building HA cluster

    You just set up your first PostgreSQL cluster, created a database schema, loaded some data, did some fine tuning of configuration. Now you want to make your cluster highly available. Unfortunately, PostgreSQL doesn't offer built-in automatic failover, but luckily for us, there are plenty of external tools for that. As a next logical step you start choosing a tool, and... you already doing it wrong, because first you have to define SLA, RTO, and RPO. In this talk I am going to cover most of the common mistakes people do when setting up a highly available cluster.

  • A
    Anna Akentyeva Postgres Professional
    22 мин

    Autovacuum: what can we learn if we read the code instead of the documentation

    In this talk we will have a look at the details of autovacuum's implementation and see what kind of practical implications they have. The talk will also provide a short overview of patches for autovacuum that are currently being considered by the developer community and that may be included in newer versions of PostgreSQL.

  • Alexander Fedorov
    Alexander Fedorov dbeaver.com
    Andrey Hitrin
    Andrey Hitrin RedSys
    22 мин

    The Ultimate Tool: Xobot IDE

    IIn the world of programming, the creation of source code for databases "procedural extensions" is something solitary. Most DBMS offers procedural languages and "stored procedures" to create procedural extensions. In Postgres the number of supported procedural languages has already exceeded a dozen.

    Traditionally, stored procedures have many applications: it is difficult to resist the attraction to perform a data operation directly in the storage, especially in the Enterprise development. This approach quickly leads to the spreading of the business logic and dramatically increases the cost of support and development of the system as a whole.

    The life cycle of stored procedures makes it difficult to use standard Change Management tools and practices. It is necessary to adapt the operations upon the stored procedures to the standards of Change Management, yet staying within the comfortable development practices.

    We'll look at the typical tasks of the procedural extensions development and discuss the solutions we are implementing in the Xobot IDE.