PostgreSQL: effective_io_concurrency benchmarked

2023-10-28 23:43:26

2019-07-22 replace: add specs about CPU (variety of cores)

As talked about in a recent post, I’m periodically reviewing all of the settings of my beloved databases and right now I want to discuss to you a couple of setting referred to as ‘effective_io_concurrency’.

effective_io_concurrency instructs PostgreSQL on how our disks are quick (or gradual).

It has a default worth of 1, and the guide defines it this manner:

“Units the variety of concurrent disk I/O operations that PostgreSQL expects may be executed concurrently.
Elevating this worth will enhance the variety of I/O operations that any particular person PostgreSQL session makes an attempt to provoke in parallel.
The allowed vary is 1 to 1000, or zero to disable issuance of asynchronous I/O requests. At the moment, this setting solely impacts bitmap heap scans.
For magnetic drives, start line for this setting is the variety of separate drives comprising a RAID 0 stripe or RAID 1 mirror getting used for the database. (For RAID 5 the parity drive shouldn’t be counted.)
Nonetheless, if the database is usually busy with a number of queries issued in concurrent classes, decrease values could also be enough to maintain the disk array busy. A worth increased than wanted to maintain the disks busy will solely end in further CPU overhead.
SSDs and different memory-based storage can typically course of many concurrent requests, so the perfect worth could be within the lots of.
[..]”

PostgreSQL 9.6 on a Linux host. {Hardware} served by 32 cores

With the intention to take a look at on a bitmap heap scan, I created an ad-hoc desk:

CREATE TABLE take a look at (main int PRIMARY KEY, minor int);

.. along with a multicolumn index:

CREATE INDEX minor_idx ON take a look at (main, minor);

After which I populated it:

INSERT INTO take a look at VALUES ( generate_series(0,100000000), random()*1000 );

I selected the next question:

SELECT * FROM take a look at WHERE main BETWEEN 10 AND 100 OR minor BETWEEN 800 AND 900;'

and the question plan confirms {that a} bitmap heap scan is there:

 Bitmap Heap Scan on take a look at (value=1279391.23..2027903.67 rows=10398562 width=12)
   Recheck Cond: (((main >= 10) AND (main <= 100)) OR ((minor >= 800) AND (min
or <= 900)))
   ->  BitmapOr  (value=1279391.23..1279391.23 rows=10398572 width=0)
         ->  Bitmap Index Scan on minor_idx  (value=0.00..2.50 rows=93 width=0)
               Index Cond: ((main >= 10) AND (main <= 100))
         ->  Bitmap Index Scan on minor_idx  (value=0.00..1274189.45 rows=103984
79 width=0)
               Index Cond: ((minor >= 800) AND (minor <= 900))

The take a look at runs in opposition to a machine outfitted with SSDs and configured in RAID 10, however I obtained comparable outcomes on a machine with just one SSD (VM with 6 cores).

Since we’re testing disk performances, I’m first dropping the cache, then restart PostgreSQL and after that working the question.

for i in {1..10} ; do echo 3 > /proc/sys/vm/drop_caches  ; /and many others/init.d/postgresql restart  ; sleep 5 ;  psql  -c 'EXPLAIN ANALYZE SELECT * FROM take a look at WHERE main BETWEEN 10 AND 100 OR minor BETWEEN 800 AND 900;' ; completed

Each set of assessments runs in opposition to completely different values of effective_io_concurrency