Showing posts from April, 2015

Linux tuning for PostgreSQL at Secon'2015

Slides from Secon'2015 - Software Developers Conference. Penza, 

The database is an essential element of any project. The database must be stable and provide good performance. If you plan to use PostgreSQL in your project, you will run into question the choice of operating system. Linux is one of the most popular operating system today. The combination of flexibility and stability makes Linux a good candidate as a platform for PostgreSQL. However, the default settings are suitable for a wide range of workloads. In this report, I will talk about what settings should pay attention and how they affect the performance of PostgreSQL. Which of these settings are more important, and what - no. How do the PostgreSQL more predictable and stable under normal circumstances or in cases of increasing load.

Linux Performance Observability Tools. Sysstat.

We continue the series of posts about tools for performance monitoring. Today I would like to tell discuss sysstat package. This is a very interesting package. First of all, it is interesting because it allows to keep a history of performance metrics. This is very useful if you have a small number of hosts, and you do not want to setup and maintain a separate monitoring system, such as nagios/zabbix/munin. Also, sysstat provides several tools for online performance monitoring.

PostgreSQL autovacuum, explained for engeneers - slides from PostgreSQL Meetup at Zalando Berlin HQ

At the first Russian PGDay there was a sort of joke, right after PostgreSQL DBA tutorial: "we need an autovacuum tutorial". The topic is utmost important, poor configured vacuuming can easily screw your database up, but documentation covers only how to configure something (and that is the purpose of documentation), not a word about strategy and common approaches of autovacuum configuration for different workloads. 

My talk at Berlin PostgreSQL meetup is some sort of alfa version: I improve it based on the  feedback and will include more details and corner cases to present it on PGDay Russia 2015 at Sankt-Petersburg this July.

Stay tuned and thanks for German colleagues for a nice discussion!

Warning: pgstat wait timeout... pgstat what???

pgstat wait timeout, what does it mean?Today I would like to tell about warning message "pgstat wait timeout". Pretty uninformative message, right?

If you see this error in the PostgreSQL logs, it means that the statistics collector does not work. Meanwhile, statistics plays a very important role in PostgreSQL. Query planner uses statistics to choose the optimal execution plan for the query. If you have broken statistics, the query planner might build the wrong plans. Wrong query plans usually cause bad performance.

Let's try to figure out why that happens.

Linux Performance Observability Tools. Top.

Linux Performance Observability Tools. Top.
Picture is a courtesy of Brendan Gregg Blog.

Given above are Linux Performance Observability Tools. At a first glance, the picture may seem confusing, so let's see what it is all about. This picture, in a very simplified form, represents the structure of the Linux operating system, as well as each component of the structure of the signed instrument that allows you to monitor the operation of this component. These tools are very useful in the case when the server is "slow" and you need to identify which component causes the it. System and network administrator or engineer should be able to use most of these tools. As you can see, that’s a lot of instruments, some of which may be familiar to you and some not. Some of these tools are fairly simple to use, and some, on the contrary require high qualifications and additional knowledge. In the following series of posts I will try to consider the most popular and interesting tools, …