There are several aspects of tuning database performance, such as hardware configuration, network setting, database configuration, rewriting of SQL queries, maintenance of indexes, and so on. In this chapter, we will focus only on basic configuration and query rewriting.
Generally speaking, tuning database performance requires knowledge about the system's nature; for example, we need to know whether the database system can be used for online analytical processing (OLAP) or online transactional processing (OLTP). The database system may be IO or CPU bound; these define the whole database cluster setup, such as the number of CPUs, CPU power, RAID setup, amount of RAM, and the database's cluster configuration. After the database server is configured, one could use benchmark framework, such as pgbench, to calculate the number of transactions per second (TPS) for the database server setup.
The second step in optimizing database performance is carried out after the system is up and running and often periodically. In this state, one could set up a monitoring system, such as a pgbadger monitoring tool, PostgreSQL load analyzer (PoWA), and pg_stat_ statements, to find the bottlenecks and the slow queries.
To optimize a slow query, it should be analyzed first. If the query is poorly written, rewriting it might be enough. Otherwise, missing indexes can be created, server configuration settings can be amended, the physical structure can be refactored, and so on.
PostgreSQL's default configuration values are not suitable for the production environment; several default values are often undersized.
In any PostgreSQL environment, the following configuration should be reviewed.
The maximum number of connections is an important parameter in configuring a database. Each client connection consumes memory, thus affecting also the total amount of memory that can be allocated for other purposes. The max_connections configuration parameter's default value is 100; lowering this value allows the database administrator to increase the work_mem setting.
In general, it is good practice to use connection pooling software to reduce the amount of used memory and increase performance, as killing and establishing a connection wastes time. There are a lot of connection-pooling tools, and the most mature ones are:
- Pgbouncer
- Pgpool-II
Also, one could use connection pooling on the business level, such as the Java connection-pooling API and C3P0.
There are several settings to control memory consumption and the way memory is consumed, and these settings are:
- Shared buffers (shared_buffers): The default value for shared buffers is 32 MB; however, it is recommended to set it around 25 percent of the total memory, but not more than 8 GB on Linux systems and 512 MB on windows system. Sometimes, increasing
shared_buffersto a very high value leads to an increase in performance because the database can be cached completely in the RAM. However, the drawback of increasing this value too much is that one can't allocate memory for CPU operations such as sorting and hashing. - Working memory (work_mem): The default value is 1 MB; for CPU-bound operations, it is important to increase this value. The
work_memsetting is linked with the number of connections, so the total amount of RAM used equals the number of connections multiplied bywork_mem. Working memory is used to sort and hash, so it affects the queries that use theORDER BY,DISTINCT,UNION, andEXCEPTconstructs. To test your working method, you could analyze a query that uses sort and take a look at whether the sort operation uses the memory or hard disk, as follows:EXPLAIN ANALYZE SELECT n FROM generate_series(1,5) as foo(n) order by n; Sort (cost=59.83..62.33 rows=1000 width=4) (actual time=0.075..0.075 rows=5 loops=1) Sort Key: n Sort Method: quicksort Memory: 25kB -> Function Scan on generate_series foo (cost=0.00..10.00 rows=1000 width=4) (actual time=0.018..0.018 rows=5 loops=1)" Total runtime: 0.100 ms
There are several hard disk settings that can boost IO performance; however, this boost comes with a penalty. For example, the fsync setting forces each transaction to be written to the hard disk after each commit. Turning this off will increase performance, especially for bulk upload operations. Also, a checkpoint_segment of small value might also lead to performance penalty in write-heavy systems; on the other hand, increasing the checkpoint_segments setting to a high value will increase recovery time.
In specific cases, such as bulk upload operations, performance can be increased by altering hard disk settings, changing the logging configuration to log minimal info, and finally disabling auto vacuuming; however, after the bulk operation is over, one should not forget to reset the server configurations and run the VACUUM ANALYZE command.
Effective cache size (effective_cache_size) should be set to an estimate of how much memory is available for disk caching in the operating system and within the database after taking into account what is used by the operating system and other applications. This value for a dedicated postgres server is around 50 percent to 70 percent of the total RAM. Also, one could play with a planner setting, such as random_page_cost, to favor index scan over sequential scans. The random_page_cost setting's default value is 4.0. In high-end SAN/NAS technologies, one could set this value to 3, and for SSD, one could use a random page cost of 1.5 to 2.5.
The preceding list of parameters is minimal; in reality, one needs to also configure the logging, checkpoint, wal, and vacuum settings. Also, note that some parameters cannot be changed easily on production systems because they require a system restart, such as max_connections, shared_buffers, fsync, and checkpoint_segments. In other cases, such as work_mem, it can be specified in the session, giving the developer the option of tuning queries that need specific work_mem.
pgbench is a simple program used to execute a prepared set of SQL commands to calculate the average transaction rate (transactions per second). pgbench is an implementation of the
Transaction Processing Performance Council (TPC) TPC-B standard. pgbench can also be customized with scripts. In general, when using a benching framework, one needs to set it up on a different client in order not to steal the RAM and CPU from the tested server. Also, one should run pgbench several times with different load scenarios and configuration settings.
Finally, in addition to pgbench, there are several open source implementations for different benchmarking standards, such as TPC-C and TPC-H.
The pgbench synopsis is as follows:
pgbench [options] dbname
The -i option is used to initialize the database with test tables, and the -s option determines the database scale factor, also known as the number of rows in each table. A default output of Pgbench using a default scale factor on a virtual machine with one CPU looks similar to the following:
$pgbench -i test_database creating tables... 100000 of 100000 tuples (100%) done (elapsed 0.71 s, remaining 0.00 s). vacuum... set primary keys... done. $pgbench -c 4 -T 50 test_database starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 query mode: simple number of clients: 4 number of threads: 1 duration: 50 s number of transactions actually processed: 5602 latency average: 35.702 ms tps = 111.906044 (including connections establishing) tps = 112.599272 (excluding connections establishing)
Finally, the pgbench manual pages (pgbench --help) explain the different query options.