Now Reading
PostgreSQL: effective_io_concurrency benchmarked

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

effective_io_concurrency Common runtime in ms
1 65604
20 17807
100 16776
1000 16713

Growing the setting, makes the question 4 instances quicker!

Right here you possibly can see how the disk is impacted on a comparability take a look at run:


First 6 peaks: effective_io_concurrency = 1

See Also

Final 4 peaks: effective_io_concurrency = 100

Additionally, the variety of IOPS is impacted, leading to much less IOPS:


The graphs for values of 20 and 1000 are just like the one above, as are the outcomes.

It’s nicely price tuning effective_io_cache in your database.

And as soon as once more, it’s confirmed that the time spent in exploring, experimenting and tuning is time nicely spent!

Concepts or feedback? You’ll find me on Linkedin

Source Link

What's Your Reaction?
In Love
Not Sure
View Comments (0)

Leave a Reply

Your email address will not be published.

2022 Blinking Robots.
WordPress by Doejo

Scroll To Top