Tuning Input/Output (I/O) Operations for PostgreSQL
Tuning PostgreSQL I/O:
Tuning PostgreSQL I/O is imperative for building a highly performant and scalable database architecture. Let us look at various factors impacting I/O performance:
Indexing
Partitioning
Checkpoints
VACUUM, ANALYZE (with FILLFACTOR)
Other I/O problems
PostgreSQL I/O on Cloud
Tools
Indexing
Indexing is one of the core tuning techniques which plays an imperative role in improving database I/O performance. This applies to any database really. PostgreSQL supports various index types which can speed up read operations to a great extent, yielding enhanced scalability for applications. Whilst creating indexes is fairly simple and straightforward, it is essential for DBAs and developers to have the knowledge of what type of index to choose, and on what columns. The latter is based on various factors like query complexity, data type, data cardinality, volume of writes, data size, disk architecture, infrastructure (public cloud, private cloud or on-premises), etc..
Whilst indexing can dramatically improve query read performance, it can also slow down the writes hitting the indexed columns. Let us look at an example:
Impact of Indexes on READ operations
A table called emp with around 1 million rows.
READ Performance without an Index
postgres=# select * from emp where eid=10;
eid | ename | peid | did | doj
-----+---------------+--------+------+------------
10 | emp | | 1 | 2018-06-06
(1 row)
Time: 70.020 ms => took about 70+ milli-seconds to respond with on row
READ Performance with an Index
Let us put an index on eid column and see the difference
postgres=# create index indx001 on emp ( eid );
CREATE INDEX
postgres=# select * from emp where eid=10;
eid | ename | peid | did | doj
------+-------------+-------+------+------------
10 | emp | | 1 | 2018-06-06
(1 row)
Time: 0.454 ms => 0.4+ milli-seconds!!! thats a huge difference - isn’t it?
So, Indexing is important.
Impact of Indexes on WRITE operations
Indexes slow down the performance of writes. Whilst the Indexes have an impact on all types of write operations, let us look at some analysis on the impact of Indexes on INSERTs
Inserting 1 million rows into a Table without indexes
postgres=# do $$
postgres$# declare
postgres$# i integer;
postgres$# begin
postgres$# for i in 1..1000000 loop
postgres$# insert into emp values (i,'emp',null,1,current_date);
postgres$# end loop;
postgres$# end $$;
DO
Time: 4818.470 ms (00:04.818) => Takes about 4.8 seconds
Inserting the same 1 million rows with an Index
Let us create an Index first
postgres=# create index indx001 on emp ( eid );
CREATE INDEX
postgres=# do $$
postgres$# declare
postgres$# i integer;
postgres$# begin
postgres$# for i in 1..1000000 loop
postgres$# insert into emp values (i,'emp',null,1,current_date);
postgres$# end loop;
postgres$# end $$;
DO
Time: 7825.494 ms (00:07.825) => Takes about 7.8 seconds
So, as we can observe, the INSERT time increased by 80% with just one index and can take much higher time to finish when there are multiple indexes. It can get even worse when there are function based indexes. That is what DBAs have to live with! Indexes will increase the write performance. There are ways to tackle this problem though, which is disk architecture dependent. If the database server is using multiple disk file systems, then the indexes and tables can be placed across multiple tablespaces sitting across multiple disk file systems. In this way, better I/O performance can be achieved.
Index management TIPS
Understand the need for indexes. Intelligent indexing is key.
Avoid creating multiple indexes, and definitely no unnecessary indexes, this can really degrade write performance.
Monitor the usage of indexes and drop any unused indexes.
When indexed columns are subjected to data changes, indexes get bloated as well. So, regularly reorganize indexes.
Partitioning
An effective partitioning strategy can reduce I/O performance problems to a great extent. Large tables can be partitioned based on business logic. PostgreSQL supports table partitioning. Although it does not fully support all the features at the moment, it can only help with some of the real-time use-cases. In PostgreSQL, partitioned child tables are completely individual to the master table which is a bottleneck. E.g., Constraints created on the master table cannot be automatically inherited to the child tables.
However, from balancing I/O perspective, partitioning can really help. All the child partitions can be split across multiple tablespaces and disk file systems. Queries with a date range in the “where” clause hitting the table, partitioned based on date range, can benefit from partitioning by just scanning one or two partitions instead of the full table.
Checkpointing
Checkpoints define the consistent state of the database. They are critical and it is important that checkpoints occur regularly enough to ensure data changes are permanently saved to disk and the database is at consistent state all the time. That being said, improper configuration of checkpoints can lead to I/O performance issues. DBAs must be meticulous about configuring checkpoints to ensure there is no I/O spike and also this depends on how good the disks are and how well the data files layout is architected.
What checkpoint does ?
In simple terms, checkpoints will ensure:
All the committed data is written to the data files on the disk.
clog files are updated with commit status.
Transaction log files in pg_xlog (now pg_wal) directory are recycled.
That explains how I/O intensive checkpoints are. There are parameters in postgresql.conf which can be configured / tuned to control checkpoint behavior and those parameters are max_wal_size, min_wal_size, checkpoint_timeout and checkpoint_completion_target. These parameters will decide how frequently the checkpoints should occur, and within how much time the checkpoints have to finish.
How to understand what configuration is better for checkpoints? How to tune them?
Here are some tips:
Evaluate the database TPS. Evaluate the total volume of transactions occurring in the database in a business day and also identify at what time the highest number of transactions hits the database.
Discuss with application developers and other technical teams regularly to understand the database transaction rate statistics as well as future transaction growth.
This can be done from the database end as well:
Monitor the database and evaluate the number of transactions occurring during the day. This can be done by querying pgcatalog tables like pg_stat_user_tables.
Evaluate the number of wal archive files generated per day
Monitor to understand how the checkpoints are performing by enabling log_checkpoints parameter
2018-06-06 15:03:16.446 IST [2111] LOG: checkpoint starting: xlog
2018-06-06 15:03:22.734 IST [2111] LOG: checkpoint complete: wrote 12112 buffers (73.9%); 0 WAL file(s) added, 0 removed, 25 recycled; write=6.058 s, sync=0.218 s, total=6.287 s; sync files=4, longest=0.178 s, average=0.054 s; distance=409706 kB, estimate=412479 kB
Understand if the current checkpoint configuration is good enough for the database. Configure checkpoint_warning parameter (by default configured to 30 seconds) to see the below warnings in the postgres log files.
2018-06-06 15:02:42.295 IST [2111] LOG: checkpoints are occurring too frequently (11 seconds apart)
2018-06-06 15:02:42.295 IST [2111] HINT: Consider increasing the configuration parameter "max_wal_size".
What does the above warning mean?
Checkpoints generally occur whenever max_wal_size (1 GB by default which means 64 WAL files) worth of logfiles are filled up or when checkpoint_timeout (every 5 mins every default) is reached. The above warning means configured max_wal_size is not adequate and the checkpoints are occurring every 11 seconds, that in-turn means 64 WAL files in PG_WAL directory are getting filled up in just 11 seconds, which is too frequent. In other words, if there are less frequent transactions, then, the checkpoints will occur every 5 minutes. So, as the hint suggests, increase the max_wal_size parameter to a higher value, max_min_size parameter can be increased to the same or a lesser than former.
Another critical parameter to consider from I/O performance perspective is checkpoint_completion_target which is by default configured to 0.5.
checkpoint_completion_target = 0.5 x checkpoint_timeout = 2.5 minutes
That means, checkpoints have got 2.5 mins to sync the dirty blocks to the disk. Are 2.5 minutes enough? That needs to be evaluated. If the number of dirty blocks to be written is very high, then 2.5 minutes can seem very very aggressive and that is when an I/O spike can be observed. Configuring the completion_target parameter must be done based on max_wal_size and checkpoint_timeout values. If these parameters are raised to a higher value, consider raising checkpoint_completion_target accordingly.
VACUUM, ANALYZE (with FILLFACTOR)
VACUUM is one of the most powerful features of PostgreSQL. It can be used to remove bloats (fragmented space) within tables and indexes, and is generated by transactions. The database must be subjected to VACUUMing regularly to ensure healthy maintenance and better performance. Again, not VACUUMing the database regularly can lead to serious performance problems. ANALYZE must be performed along with VACUUM (VACUUM ANALYZE) to ensure up-to-date statistics for the query planner.
VACUUM ANALYZE can be performed in two ways: manual, automatic or both. In a real-time production environment, it is generally both. Automatic VACUUM is enabled by the parameter “autovacuum” which is by default configured to “on”. With autovacuum enabled, PostgreSQL automatically starts VACUUMing the Tables periodically. The candidate tables in need of vacuuming are picked up by autovacuum processes based on various thresholds set by various autovacuum* parameters, these parameters can be tweaked / tuned to ensure bloats of the tables are cleared periodically. Let us look at some parameters and their use –
Autovacuum parameters
autovacuum=on This parameter is used to enable / disable autovacuum. Default is “on”.
log_autovacuum_min_duration = -1 Logs the duration of the autovacuum process. This is important to understand how long the autovacuum process was running for.
autovacuum_max_workers = 3 Number of autovacuum processes needed. This depends on how aggressive database transactions are, and how many CPUs you can offer for autovacuum processes.
autovacuum_naptime = 1 min Autovacuum rest time between autovacuum runs.
Parameters defining threshold for Autovacuum process to kick off
Autovacuum job(s) kick off when a certain threshold is reached. Below are the parameters which can be used to set a certain threshold, based on which, the autovacuum process will start.
autovacuum_vacuum_threshold = 50 The table will be vacuumed when minimum of 50 rows will be updated / deleted in a Table.
autovacuum_analyze_threshold = 50 The table will be analyzed when minimum of 50 rows will be updated / deleted in a Table.
autovacuum_vacuum_scale_factor = 0.2 The table will be vacuumed when minimum of 20% of the rows are updated / deleted in a Table.
autovacuum_analyze_scale_factor = 0.1 The table will be vacuumed when minimum of 10% of the rows are updated / deleted in a Table.
Above threshold parameters can be modified based on database behavior. DBAs will need to analyze and identify the hot tables and ensure those tables are vacuumed as frequently as possible to ensure good performance. Arriving at a certain value for these parameters could be a challenge in a high-transaction environment, wherein data changes happen every second. Many-a-times I did notice that autovacuum processes take quite long to complete, ending up consuming too much resources in production systems.
I would suggest not to depend completely on autovacuum process, the best way is to schedule a nightly VACUUM ANALYZE job so that the burden on autovacuum is reduced. To start with, consider manually VACUUMing big tables with a high-transaction rate.
VACUUM FULL
VACUUM FULL helps reclaim the bloated space in the tables and indexes. This utility cannot be used when the database is online as it locks the table. Tables must be subjected to VACUUM FULL only when the applications are shutdown. Indexes will also be re-organized along with tables during VACUUM FULL.
Let us take a look at the impact of VACUUM ANALYZE
Bloats: How to identify bloats? When are bloats generated?
Here are some tests:
I have got a table of size 1 GB with 10 million rows.
postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;
?column?
----------------
1
postgres=# select count(*) From pgbench_accounts ;
count
-----------------
10000000
Let us look at the impact of bloats on a simple query: select * from pgbench_accounts;
Below is the explain plan for the query:
postgres=# explain analyze select * from pgbench_accounts;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..263935.00 rows=10000000 width=97)
(actual time=0.033..1054.257 rows=10000000 loops=1)
Planning time: 0.255 ms
Execution time: 1494.448 ms
Now, let us update all the rows in the table and see the impact of the above SELECT query.
postgres=# update pgbench_accounts set abalance=1;
UPDATE 10000000
postgres=# select count(*) From pgbench_accounts ;
count
-----------------
10000000
Below is the EXPLAIN PLAN of the query post UPDATE execution.
postgres=# explain analyze select * from pgbench_accounts;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..527868.39 rows=19999939 width=97)
(actual time=404.474..1520.175 rows=10000000 loops=1)
Planning time: 0.051 ms
Execution time: 1958.532 ms
The size of the table increased to 2 GB after the UPDATE
postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;
?column?
-----------------
2
If you can observe and compare the cost numbers of the earlier EXPLAIN PLAN, there is a huge difference. The cost has increased by a big margin. More importantly if you observe carefully, the number of rows (just over 19 million) being scanned after the UPDATE is higher which is almost two times the actual existing rows (10 million). That means, the number of bloated rows are 9+ million and actual time increased as well and the execution time increased from 1.4 seconds to 1.9 seconds.
So, that is the impact of not VACUUMing the TABLE after the UPDATE. The above EXPLAIN PLAN numbers precisely means, the table is bloated.
How to identify if the table is bloated? Use pgstattuple contrib module:
postgres=# select * from pgstattuple('pgbench_accounts');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------
2685902848 | 10000000 | 1210000000 | 45.05 | 9879891 | 1195466811 | 44.51 | 52096468 | 1.94
The above number indicates that half of the table is bloated.
Let us VACUUM ANALYZE the table and see the impact now:
postgres=# VACUUM ANALYZE pgbench_accounts ;
VACUUM
postgres=# explain analyze select * from pgbench_accounts;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..428189.05 rows=10032005 width=97)
(actual time=400.023..1472.118 rows=10000000 loops=1)
Planning time: 4.374 ms
Execution time: 1913.541 ms
After VACUUM ANALYZE, the cost numbers have decreased. Now, the number of rows scanned is showing up close to 10 million, also the actual time and the execution time did not change much. That is because, though the bloats in the table have vanished, the size of the table to be scanned remains the same. Below is the pgstattuple output post VACUUM ANALYZE.
postgres=# select * from pgstattuple('pgbench_accounts');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------
2685902848 | 10000000 | 1210000000 | 45.05 | 0 | 0 | 0 | 1316722516 | 49.02
Above number indicates that all the bloats (dead tuples) have vanished.
Let us look at the impact of VACUUM FULL ANALYZE and see what happens:
postgres=# vacuum full analyze pgbench_accounts ;
VACUUM
postgres=# explain analyze select * from pgbench_accounts;
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..263935.35 rows=10000035 width=97)
(actual time=0.015..1089.726 rows=10000000 loops=1)
Planning time: 0.148 ms
Execution time: 1532.596 ms
If you observe, the actual time and the execution time numbers are similar to the numbers before UPDATE. Also, the size of the table has now decreased from 2 GB to 1 GB.
postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;
?column?
-----------------
1
That is the impact of VACUUM FULL.
FILLFACTOR
FILLFACTOR is a very important attribute which can make real difference to the database maintenance strategy at a table and index level. This value indicates the amount of space to be used by the INSERTs within a data block. FILLFACTOR value defaults to 100%, which means, INSERTs can utilize all the space available in a data block. It also means, no space is available for UPDATEs. This value can be decreased to a certain value for heavily updated tables.
This parameter can be configured to each table and an index. If FILLFACTOR is configured to the optimal value, you can see real difference in VACUUM performance and query performance too. In short, optimal FILLFACTOR values ensure unnecessary number of blocks are not allocated.
Let us look at the same example above –
The table has one million rows
postgres=# select count(*) From pgbench_accounts ;
count
-----------------
10000000
Before update the size of the table is 1 GB
postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;
?column?
--------
1
postgres=# update pgbench_accounts set abalance=1;
UPDATE 10000000
After update, the size of the table increased to 2 GB after the UPDATE
postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;
?column?
---------
2
That means, number of blocks allocated to the table has increased by 100%. If the FILLFACTOR was configured, the size of the table may not have increased by that margin.
How to know what value to configure to FILLFACTOR?
It all depends on what columns are being updated and the size of the updated columns. In general, it would be good to evaluate the FILLFACTOR value by testing it out in UAT Databases. If the columns being updated are say 10% of the whole table, then, consider configuring fillfactor to 90% or 80%.
Important Note: If you change the FILLFACTOR value for the existing table with the data, you will need to do a VACUUM FULL or a re-organization of the table to ensure FILLFACTOR value is in effect for the existing data.
VACUUMing TIPS
As said above, consider running VACUUM ANALYZE job manually every night on the heavily used tables even when autovacuum is enabled.
Consider running VACUUM ANALYZE on tables after bulk INSERT. This is important as many believe that VACUUMing may not be needed after INSERTs.
Monitor to ensure highly active tables are getting VACUUMed regularly by querying the table pg_stat_user_tables.
Use pg_stattuple contrib module to identify the size of the bloated space within the table segments.
VACUUM FULL utility cannot be used on production database systems. Consider using tools like pg_reorg or pg_repack which will help reorganize tables and indexes online without locks.
Ensure AUTOVACUUM process does run for a longer time during business (high traffic) hours.
Enable log_autovacuum_min_duration parameter to log the timings and duration of AUTOVACUUM processes.
Importantly, ensure FILLFACTOR is configured to an optimal value on high transaction Tables and Indexes.
Other I/O problems
Disk Sorting
Queries performing sorting is another common occurrence in real-time production databases and most of these cannot be avoided. Queries using clauses like GROUP BY, ORDER BY, DISTINCT, CREATE INDEX, VACUUM FULL etc. perform sorting and the sorting can take place on disk. Sorting takes place in the memory if the selection and sorting is done based on indexed columns. This is where composite-indexes play a key role. Indexes are aggressively cached into memory. Otherwise, if there arises a need to sort the data on disk, the performance would slow down drastically.
To ensure sorting takes place in memory, the work_mem parameter can be used. This parameter can be configured to a value such that the whole sorting can be done in memory. The core advantage of this parameter is that, apart from configuring it in postgresql.conf, it can also be configured at session level, user level or database level. How much should the work_mem value be? How to know which queries are performing disk sorting? How to monitor queries performing disk sorting on a real-time production database?
The answer is – configure log_temp_files parameter to a certain value. The value is in bytes, a value of 0 logs all the temp files (along with their sizes) generated on disk due to disk sorting. Once the parameter is configured, you will be able to see the following messages in the log files
2018-06-07 22:48:02.358 IST [4219] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp4219.0", size 200425472
2018-06-07 22:48:02.358 IST [4219] STATEMENT: create index bid_idx on pgbench_accounts(bid);
2018-06-07 22:48:02.366 IST [4219] LOG: duration: 6421.705 ms statement: create index bid_idx on pgbench_accounts(bid);
The above message means that the CREATE INDEX query was performing disk sorting and has generated a file of size 200425472 bytes which is 191+ MB. That precisely means, the work_mem parameter must be configured to 191+ MB or above for this particular query to perform memory sorting.
Well, for the application queries, work_mem parameter can only be configured at the user level. Before doing so, beware of the number of connections that user is making to the database and number of sorting queries being executed by that user. Because PostgreSQL tries to allocate work_mem to each process (performing sorting) in each connection which could potentially starve the memory on the database server.
Database file-system layout
Designing efficient and performance conducive database file-system layout is important from performance and scalability perspective. Importantly, this is not dependent on the database size. In general, the perception is that huge size databases will need high performance disk architecture which is NOT true. Even if the database size is 50 GB, you may be in need of a good disk architecture. And this may not be possible without incurring extra costs.
Here are some TIPS for the same:
Ensure the database has multiple tablespaces, with tables and indexes grouped based on the transaction rates.
The tablespace must be placed across multiple disk file systems for balanced I/O. This will also ensure multiple CPUs come into play to perform transactions across multiple disks.
Consider placing pg_xlog or pg_wal directory on a separate disk on a high transaction database.
Ensure *_cost parameters are configured based on the infrastructure
Use iostat, mpstat and other I/O monitoring tools to understand the I/O stats across all the disks and architect / manage the database objects accordingly.
PostgreSQL on Cloud
Infrastructure is critical for good database performance. Performance engineering strategies differ based on infrastructure and environment. Special care needs to be taken for PostgreSQL databases hosted in the cloud. Performance benchmarking for databases hosted on physical barebone servers in a local data center can be entirely different from databases hosted in the public cloud.
In general, cloud instances could be a little slower and benchmarks differ by considerable margin especially in terms of I/O. Always perform I/O latency checks before choosing / building a cloud instance. To my surprise, I learnt that performance of cloud instances can vary depending on the regions too, even though they are from the same cloud provider. To explain this further, a cloud instance with same specs built in two different regions could give you different performance results.
Bulk data load
Offline bulk data loading operations are pretty common in the database world. They can generate significant I/O load, which in turn slows down the data load performance. I have faced such challenges in my experience as DBA. Often, data load gets terribly slow and has to be tuned. Here are some tips. Mind you, these apply to offline data loading operations only and cannot be considered for data loading on live production database.
Since most of the data load operations are carried out during off business hours, ensure the following parameters are configured during the data load –
Configure checkpoint related values big enough so that checkpoints do not cause any performance issues.
Switch off full_page_write
Switch off wal archiving
Configure synchronous_commit parameter to “off”
Drop constraints and indexes for those tables subjected to the data load (Constraints and indexes can be re-created post the data load with a bigger work_mem value)
If you are doing the data load from a CSV file, bigger maintenance_work_mem can get you good results.
Though there will be a significant performance benefit, DO NOT switch off fsync parameter as that could lead to data corruption.
TIPS for cloud performance analysis
Perform thorough I/O latency tests using pgbench. In my experience, I had pretty ordinary performance results when doing disk latency checks as part of TPS evaluation. There were issues with cache performance on some public cloud instances. This will help chose the appropriate specs for the cloud instance chosen for the databases.
Cloud instances can perform differently from region to region. A cloud instance with certains specs in a region can give different performance results compared to a cloud instance with same specs in another region. My pgbench tests executed on multiple cloud instances (all same specs with the same cloud vendor) across different regions gave me different results on some of them. This is important especially when you are migrating to cloud.
Query performance on the cloud might need a different tuning approach. DBAs will need to be using *_cost parameters to ensure healthy query execution plans are generated.
Tools to Monitor PostgreSQL Performance
There are various tools to monitor PostgreSQL performance. Let me highlight some of those.
pg_top is a GREAT tool to monitor PostgreSQL database dynamically. I would highly recommend this tool for DBAs for various reasons. This tool has numerous advantages, let me list them out:
pg_top tool uses textual interface and is similar to Unix “top” utility.
Will clearly list out the processes and the hardware resources utilized. What excites me with this tool is that it will clearly tell you if a particular process is currently on DISK or CPU – in my view that’s excellent. DBAs can clearly pick the process running for longer time on the disk.
You can check the EXPLAIN PLAN of the top SQLs dynamically or instantly
You can also find out what Tables or Indexes are being scanned instantly
Nagios is a popular monitoring tool for PostgreSQL which has both open-source and commercial versions. Open source version should suffice for monitoring. Custom Perl scripts can be built and plugged into Nagios module.
Pgbadger is a popular tool which can be used to analyze PostgreSQL log files and generate performance reports. This report can be used to analyze the performance of checkpoints, disk sorting.
Zabbix is another popular tool used for PostgreSQL monitoring.
ClusterControl is an up-and-coming management platform for PostgreSQL. Apart from monitoring, it also has functionality to deploy replication setups with load balancers, automatic failover, backup management, among others.
