On 16/06/10 18:30, jgard...@jonathangardner.net wrote:
On Jun 15, 4:18 pm, j...@agliodbs.com (Josh Berkus) wrote:
On 6/15/10 10:37 AM, Chris Browne wrote:
I'd like to see some figures about WAL on RAMfs vs. simply turning off
fsync and full_page_writes. Per Gavin's tests, PostgreSQL is already
close to TokyoCabinet/MongoDB performance just with those turned off; I
wonder if actually having the WAL on a memory partition would make any
real difference in throughput.
I've seen a lot of call for this recently, especially since PostgreSQL
seems to be increasingly in use as a reporting server for Hadoop. Might
be worth experimenting with just making wal writing a no-op. We'd also
want to disable checkpointing, of course.
My back-of-the-envelope experiment: Inserting single integers into a
table without indexes using a prepared query via psycopg2.
Python Script:
import psycopg2
from time import time
conn = psycopg2.connect(database='jgardner')
cursor = conn.cursor()
cursor.execute("CREATE TABLE test (data int not null)")
conn.commit()
cursor.execute("PREPARE ins AS INSERT INTO test VALUES ($1)")
conn.commit()
start = time()
tx = 0
while time() - start< 1.0:
cursor.execute("EXECUTE ins(%s)", (tx,));
conn.commit()
tx += 1
print tx
cursor.execute("DROP TABLE test");
conn.commit();
Local disk, WAL on same FS:
* Default config => 90
* full_page_writes=off => 90
* synchronous_commit=off => 4,500
* fsync=off => 5,100
* fsync=off and synchronous_commit=off => 5,500
* fsync=off and full_page_writes=off => 5,150
* fsync=off, synchronous_commit=off and full_page_writes=off => 5,500
tmpfs, WAL on same tmpfs:
* Default config: 5,200
* full_page_writes=off => 5,200
* fsync=off => 5,250
* synchronous_commit=off => 5,200
* fsync=off and synchronous_commit=off => 5,450
* fsync=off and full_page_writes=off => 5,250
* fsync=off, synchronous_commit=off and full_page_writes=off => 5,500
NOTE: If I do one giant commit instead of lots of littler ones, I get
much better speeds for the slower cases, but I never exceed 5,500
which appears to be some kind of wall I can't break through.
If there's anything else I should tinker with, I'm all ears.
Seeing some profiler output (e.g oprofile) for the fastest case (and
maybe 'em all later) might be informative about what limit is being hit
here.
regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
