Message-ID: <46646065.50806@bluegap.ch>
Date: Mon, 04 Jun 2007 20:56:37 +0200
From: Markus Schiltknecht <markus@bluegap.ch>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
Mime-Version: 1.0
Content-Type: multipart/mixed;
	boundary="=_nabagan.bluegap.ch-20053-1180986654-0001-2"
To: PFC <lists@peufeu.com>
CC: Heikki Linnakangas <heikki@enterprisedb.com>,
	pgsql-performance@postgresql.org
Subject: Re: dbt2 NOTPM numbers
References: <466428F7.5010808@bluegap.ch> <46642B33.6030101@enterprisedb.com>
	<46642F2E.3080509@bluegap.ch> <op.tteow6v7cigqcu@apollo13>
In-Reply-To: <op.tteow6v7cigqcu@apollo13>

This is a MIME-formatted message.  If you see this text it means that your
E-mail software does not support MIME-formatted messages.

--=_nabagan.bluegap.ch-20053-1180986654-0001-2
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

PFC wrote:
>     You have a huge amount of iowait !

Yup.

>     Did you put the xlog on a separate disk ?

No, it's all one big RAID6 for the sake of simplicity (plus I doubt 
somewhat, that 2 disks for WAL + 5 for data + 1 spare would be much 
faster than 7 disks for WAL and data + 1 spare - considering that RAID6 
needs two parity disks, that's 3 vs 5 disks for data...)

>     What filesystem do you use ?

XFS

>     Did you check that your BBU cache works ?

Thanks to you're hint, yes. I've attached the small python script, in 
case it might help someone else, too.

>     For that run a dumb script which does INSERTS in a test table in 
> autocommit mode ; if you get (7200rpm / 60) = 120 inserts / sec or less, 
> the good news is that your drives don't lie about fsync, the bad news is 
> that your BBU cache isn't working...

According to my little script, I constantly get somewhat around 6000 
inserts per second, so I guess either my BBU works, or the drives are 
lying ;-)   Simplistic troughput testing with dd gives > 200MB/s, which 
also seems fine.


Obviously there's something else I'm doing wrong. I didn't really care 
much about postgresql.conf, except setting a larger shared_buffers and a 
reasonable effective_cache_size.


Oh, something else that's probably worth thinking about (and just came 
to my mind again): the XFS is on a lvm2, on that RAID6.


Regards

Markus


Simplistic throughput testing with dd:

dd of=test if=/dev/zero bs=10K count=800000
800000+0 records in
800000+0 records out
8192000000 bytes (8.2 GB) copied, 37.3552 seconds, 219 MB/s
pamonth:/opt/dbt2/bb# dd if=test of=/dev/zero bs=10K count=800000
800000+0 records in
800000+0 records out
8192000000 bytes (8.2 GB) copied, 27.6856 seconds, 296 MB/s


--=_nabagan.bluegap.ch-20053-1180986654-0001-2
Content-Type: text/x-python; name="bbu-check.py"; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="bbu-check.py"

#!/usr/bin/python

import sys, time
import psycopg

count = 500000

db = psycopg.connect("user=postgres dbname=test")
db.autocommit(True)

dbc = db.cursor()
dbc.execute("CREATE TABLE test (data TEXT);")

sys.stdout.flush()
start_t = time.time()
for i in range(count):
        dbc.execute("INSERT INTO test VALUES('insert no. %d');" % i)

diff_t = time.time() - start_t
print "%d inserts in %0.3f seconds, %f inserts/sec" % (count, diff_t, count / diff_t)

dbc.execute("DROP TABLE test;")


--=_nabagan.bluegap.ch-20053-1180986654-0001-2
Content-Type: text/plain; name="bonnie-results.txt"; charset=iso-8859-1
Content-Transfer-Encoding: base64
Content-Disposition: inline;
 filename="bonnie-results.txt"

VmVyc2lvbiAgMS4wMyAgICAgICAtLS0tLS1TZXF1ZW50aWFsIE91dHB1dC0tLS0tLSAtLVNl
cXVlbnRpYWwgSW5wdXQtIC0tUmFuZG9tLQogICAgICAgICAgICAgICAgICAgIC1QZXIgQ2hy
LSAtLUJsb2NrLS0gLVJld3JpdGUtIC1QZXIgQ2hyLSAtLUJsb2NrLS0gLS1TZWVrcy0tCk1h
Y2hpbmUgICAgICAgIFNpemUgSy9zZWMgJUNQIEsvc2VjICVDUCBLL3NlYyAlQ1AgSy9zZWMg
JUNQIEsvc2VjICVDUCAgL3NlYyAlQ1AKcGFtb250aCAgICAgICAgICA4RyA0ODE3MyAgOTkg
MjE0MDE2ICAzMyA5Mzk3MiAgMjAgNDkyNDQgIDkyIDI2Njc2MyAgMzIgNjE1LjMgICAwCiAg
ICAgICAgICAgICAgICAgICAgLS0tLS0tU2VxdWVudGlhbCBDcmVhdGUtLS0tLS0gLS0tLS0t
LS1SYW5kb20gQ3JlYXRlLS0tLS0tLS0KICAgICAgICAgICAgICAgICAgICAtQ3JlYXRlLS0g
LS1SZWFkLS0tIC1EZWxldGUtLSAtQ3JlYXRlLS0gLS1SZWFkLS0tIC1EZWxldGUtLQogICAg
ICAgICAgICAgIGZpbGVzICAvc2VjICVDUCAgL3NlYyAlQ1AgIC9zZWMgJUNQICAvc2VjICVD
UCAgL3NlYyAlQ1AgIC9zZWMgJUNQCiAgICAgICAgICAgICAgICAgMTYgMjA1MTAgIDgyICsr
KysrICsrKyAyNTY1NSAgOTggMjM5NTQgIDk5ICsrKysrICsrKyAyNTQ0MSAgOTkKcGFtb250
aCw4Ryw0ODE3Myw5OSwyMTQwMTYsMzMsOTM5NzIsMjAsNDkyNDQsOTIsMjY2NzYzLDMyLDYx
NS4zLDAsMTYsMjA1MTAsODIsKysrKyssKysrLDI1NjU1LDk4LDIzOTU0LDk5LCsrKysrLCsr
KywyNTQ0MSw5OQoK
--=_nabagan.bluegap.ch-20053-1180986654-0001-2--