Re: 9.4 regression

On 08/08/2013 05:28 PM, Jon Nelson wrote:
> A follow-up.
> I found this thread that seems to explain some things:
>
> http://comments.gmane.org/gmane.comp.file-systems.ext4/33024
>
> Short version: if we are writing into the "middle" of the
> newly-fallocated file on ext4 (with extents) the extent tree can
> fragment badly, causing poor performance due to extent merging.
>
> I also ran some tests with xfs, and the results were even stranger:
>
> xfs performed very slightly better with fallocate vs. without.
> xfs (fallocate) 216 tps vs. (no fallocate) 206
> ext4 (no fallocate) 605 vs (fallocate) 134.
>
> I made an ext4 filesystem without extents using the same block device
> as above - a real, single hard drive with nothing else on it.
> On this filesystem, the performance remained the same (or - perhaps -
> improved very slightly): 633tps (with fallocate) vs. 607.
>
>
> Using the following additions postgresql.conf:
>
> max_connections = 500
> shared_buffers = 1GB
> effective_cache_size = 1GB
> random_page_cost = 2.0
> cpu_tuple_cost = 0.03
> wal_buffers = 32MB
> work_mem = 100MB
> maintenance_work_mem = 512MB
> commit_delay = 50
> commit_siblings = 15
> checkpoint_segments = 256
> log_checkpoints = on
>
> and this partial script for testing:
>
> pg_ctl initdb -D tt -w
> cp ~/postgresql.conf tt
> pg_ctl -D tt -w start
> createdb -p 54320 pgb
> pgbench -s 20 -p 54320 -d pgb -i
Just an idea - can you check if using a fillfactor different form 100
changes anything

pgbench -s 20 -p 54320 -d pgb -F 90 -i

> pgbench -j 80 -c 80 -T 120 -p 54320 pgb
> pg_ctl -D tt -w stop

That is, does extending tables and indexes to add updated tuples play
any role here

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ