PgConf.Russia 2017 talks

Linux IO internals for PostgreSQL administrators

Ilya Kosmodemiansky
Data Egret

Ilya Kosmodemiansky is an expert of Data Erget, specialist in database management systems PostgreSQL, DB2, Oracle. His area of professional interests covers issues of transactional data processing and fault-tolerant productive storage development. Ilya pays much attention to popularization of PostgreSQL technology among Russian-speaking audience. Regularly he holds lectures and workshops on effective use of PostgreSQL features in high-load web-, telecom- and related areas projects.

Input-output (IO) performance issues have been on DBAs’ agenda since the beginning of databases. The volume of data grows rapidly and time is of an essence when one needs to get necessary data fast from the disk and, more importantly, to the disk.

For most databases it is relatively easy to find checklist of recommended Linux settings to maximize IO throughput and, in most cases, this checklist is indeed good enough. It is however essential always to understand how the optimisation of those settings actually works, especially, if you run into corner cases. In this talk I will explain how IO in Linux works; how PostgreSQL pages travel from disk level to database own shared memory and back; and what kind of mechanisms exist to control this. We will also touch upon memory structures, swap and pageout daemons, filesystems, schedullers and IO methods.

Some fundamental differences in IO approaches between PostgreSQL, Oracle and MySQL will also be covered.