Conversation

The insert benchmark now supports PostgreSQL. Time to burn out the SSDs attached to my Intel NUCs. Sorry, iibench.py is messy -

github.com

support postgres · mdcallag/mytools@3415a5a

Replying to

Cool! Low cardinality index would be interesting, especially with Postgres. (Perhaps a single column index on "productid").

Replying to

Indexes are wide to increase IO stress and make index covering for a query. I need to revisit PG support in Linkbench to see if wide indexes there can use “include”.

Replying to

Right -- the biggest LB index covers all columns in the table. Also, Postgres should have a storage param to force "split after new tuple" optimization, since the heuristics are too conservative to work with LinkBench despite clear benefits. (See git.postgresql.org/gitweb/?p=post)

Replying to

I expect to experiment with btree fill factor to determine whether that helps

Replying to

Check out pgstatindex() function from pgstattuple contrib extension for info on space utilization. Looks like Postgres gets classic "Natural logarithm of 2" space utilization with insert benchmark (i.e. ~69% full excluding the PK), so wouldn't expect FF to make a difference.

Replying to

I just learned of that via this post. Does it do a full index scan to compute the result or something faster?

citusdata.com

Postgres Autovacuum is Not the Enemy

It’s a common misconception that high volume read-write workloads in PostgreSQL eventually cause database inefficiency due to bloat. Autovacuum, a Postgres tool that combats bloat, is often misunde...

Replying to

It just scans the whole index, but in physical order, without blowing out shared_buffers (it reuses a small, dedicated area of shared buffers). This seems to almost saturate I/O bandwidth. So it's very fast.

Replying to

and

This is how it works (code is very simple): github.com/postgres/postg

Replying to

Thanks. Will add it to my test scripts.

Peter Geoghegan

@petervgeoghegan

Replying to

@MarkCallaghanDB

Also observed that insert benchmark doesn't trigger a VACUUM, unless you opt to do UPDATEs/DELETEs. This affects ability to do index-only scans (VACUUM maintains visibility map). Not sure if you should consider that to be a problem in the benchmark, in Postgres, or in both.

9:39 PM · Dec 3, 2019Twitter Web App

Replying to

I will run linkbench soon which has insert, update and delete. Starting with insert only might have been good. Less configuration tuning to worry about while learning about PG. Can focus on checkpoint and worry about vacuum later.

Replying to

Makes sense. To improve space utilization for LinkBench, hack the function _bt_afternewitemoff() within nbtsplitloc.c (in Postgres 12+). Make it return true where safe, and rebuild. If you're interested I can send a patch (will take me about 3 minutes). Makes a big difference.

Show replies