Separation of the load on PostgreSQL disks

by 6 July 2017

Problem statement

Most often, problems with the database are associated with slow disks or with a high load on them. In this moment we don’t have guarantees that all our servers have fast disks. PostgreSQL uses a disk to store not only the user data (tables, indexes, sequences and etc.), but also for storing the service information (Write-Ahead Log, logs and etc.). These data are written to disk differently. For example, data in WA-log segments is written sequentially, but if PostgreSQL uses one disk for all data, then it needs to write to disk other information. If a magnetic-head disk is used, time will be wasted to move it. So if you place it on a separate disk, the magnetic head will not move once more, which will speed up the recording. SSD disks do not have a magnetic head, so the above problems for them are not relevant. However, partitioning the load into separate disks for different PostgreSQL directories is a good practice, because this will increase the performance of the database. In addition, it allows you to conveniently monitor the load on some components of PostgreSQL.

Transferring WAL segments to a separate disk.

At first, we can to separate the PostgreSQL data directory and the pg_xlog directory. The pg_xlog directory is contened the WAL’s files. Briefly, WAL’s central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records.(This is roll-forward recovery, also known as REDO.)

Steps:

  1. Stop the PostgreSQL server (!);
  2. Transfer the directories pg_clog and pg_xlog, located in the directory with databases, to another disk;
  3. Create a symbolic link in the old place;
  4. Start the server.

 Transferring pg_log files to a separate disk.

Secondary, we can additionally move the pg_log directory. The logging collector is designed to never lose messages. This means that in case of extremely high load, server processes could be blocked while trying to send additional log messages when the collector has fallen behindd.

Therefore, it is desirable to put pg_log on a separate (preferably fast) disk.

Steps:

  1. Stop the PostgreSQL server (!);
  2. Transfer the directory pg_log, located in the directory with databases, to another disk;
  3. Create a symbolic link in the old place;
  4. Start the server.

 Creating separate tablespaces for a tables, indexes and temp files

We can go even further in balancing the load on the disk system and improve the performance of the planner/optimizer. PostgreSQL has the opportunity to storege different database objects in the differents places. For this case are used TABLESPACES (https://www.postgresql.org/docs/9.6/static/manage-ag-tablespaces.html). A tablespace allows superusers to define an alternative location on the file system where the data files containing database objects (such as tables and indexes) can reside. Tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

Tablespaces are created for three differents objects: tables, indexes and temporary files

For example:

CREATE TABLESPACE tbsdata
LOCATION'/new/separate/disk1/data'
WITH (seq_page_cost = 1, random_page_cost = 2, effective_io_concurrency = 1);

CREATE TABLESPACE tbsidx
LOCATION '/new/separate/disk2/idx'
WITH (seq_page_cost = 1, random_page_cost = 2, effective_io_concurrency = 1);

CREATE TABLESPACE tbstmp
LOCATION'/new/separate/disk3/tmp'
WITH (seq_page_cost = 1, random_page_cost = 1, effective_io_concurrency = 1);

, where tablespace options:

seq_page_cost(floating point) – sets the planner’s estimate of the cost of a disk page fetch that is part of a series of sequential fetches. The default is 1.0. (https://www.postgresql.org/docs/9.6/static/runtime-config-query.html#GUC-SEQ-PAGE-COST)

random_page_cost(floating point) – sets the planner’s estimate of the cost of a non-sequentially-fetched disk page. The default is 4.0. (https://www.postgresql.org/docs/9.6/static/runtime-config-query.html#GUC-RANDOM-PAGE-COST)

effective_io_concurrency(integer) – sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously. Raising this value will increase the number of I/O operations that any individual PostgreSQL session
attempts to initiate in parallel. The allowed range is 1 to 1000, or zero to disable issuance of asynchronous I/O requests.

Currently, this setting only affects bitmap heap scans. (https://www.postgresql.org/docs/9.6/static/runtime-config-resource.html#GUC-EFFECTIVE-IO-CONCURRENCY)

These parameters can control the behavior of the planner / optimizer for differents disks (tablespaces).

Tablespaces in postgresql.conf

postgresql.conf file contains two parameters, which are sets the default tablespaces for database cluster: default_tablespace and temp_tablespaces.

When default_tablespace is set to anything but an empty string, it supplies an implicit TABLESPACE clause for CREATE TABLE and CREATE INDEX commands that do not have an explicit one. temp_tablespaces parameter determines the placement of temporary tables and indexes, as well as temporary files that are used for purposes such as sorting large data sets. This can be a list of tablespace names, rather than only one, so that the load associated with temporary objects can be spread over multiple tablespaces. A random member of the list is picked each time a temporary object is to be created.

For example:

default_tablespace = 'tbsdata'
temp_tablespaces ='tbstmp,tbsdata,tbsidx'

The user can specify the tablespace when the object is created. For example:

CREATE TABLE foo(i int) TABLESPACE tbsdata;
CREATE INDEX idx_foo ON foo USING btree (i) TABLESPACE tbsidx;

Or

SET default_tablespace = 'tbsdata';
CREATE TABLE foo(i int);
SET default_tablespace = 'tbsidx';
CREATE INDEX idx_foo ON foo USING btree (i);

Move the table data from default tablespace to new tablespace.

We have two ways:

  • Move data with standart comand. For example:

    ALTER TABLE my_table SET TABLESPACE tbsdata;
    ALTER TABLE ALL SET TABLESPACE tbsdata;
  • Move data with pg_repack extension (https://github.com/reorg/pg_repack). Install pg_repack96 on CentOS 7

    yum install pg_repack96.x86_64
    su- postgres
    psql -U postgres -d my_db -c "CREATE EXTENSION pg_repack;"

    For example:

    su- postgres
    /usr/pgsql-9.6/bin/pg_repack-t foo -d my_db -s tbsdata
    /usr/pgsql-9.6/bin/pg_repack-t foo -d my_db -x -s tbsidx -j 2
    /usr/pgsql-9.6/bin/pg_repack-a -d my_db -s tbsdata -S tbsidx

The ALTER commands requires ACCESS EXCLUSIVE lock for tables, so it can not be executed without stopping the work with the database.

pg_repack allows you to transfer tables and indexes to another TABLESPACE with minimal locks without stopping the work with the database.

Conclusions:

If we correctly determine on which disks tables, indexes, WA-logs, log files and temporary objects should be located, then we will get a high speed of writing and reading. And, accordingly, greater DB performance. Separation of the locations of the objects of the DB gives us great flexibility in setting up the DBMS, and also helps to conveniently monitor the load on each disk.

Comments

  • No comment

Leave a Reply

Your email address will not be published. Required fields are marked *