Re: 9.4 regression

From: Hannu Krosing <hannu(at)2ndQuadrant(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Thom Brown <thom(at)linux(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
Subject: Re: 9.4 regression
Date: 2013-08-08 19:50:33
Message-ID: 5203F689.1010204@2ndQuadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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Ü

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jon Nelson 2013-08-08 21:12:06 Re: 9.4 regression
Previous Message Josh Berkus 2013-08-08 19:44:10 Re: question about HTTP API