Hi Dave,

On Mon, 2005-11-14 at 18:51 -0500, Dave Cramer wrote:
> Joost,
>
> I've got experience with these controllers and which version do you
> have. I'd expect to see higher than 50MB/s although I've never tried
> RAID 5
>
> I routinely see closer to 100MB/s with RAID 1+0 on their 9000 series
OK, than there must be hope.

> I would also suggest that shared buffers should be higher than 7500,
> closer to 30000, and effective cache should be up around 200k
In my current 8.1 situation I use shared_buffers = 40000,
effective_cache_size = 131072 .

> work_mem is awfully high, remember that this will be given to each
> and every connection and can be more than 1x this number per
> connection depending on the number of sorts
> done in the query.
I use such a high number because I am the only user querying and my
queries do sorted joins etc.


> fsync=false ? I'm not even sure why we have this option, but I'd
> never set it to false.
I want as much speed as possible for a database conversion that MUST be
handled in 1 weekend (it lasts now, with the current speed almost 7
centuries. I may be off a millenium). If it fails because of hardware
problem (the only reason we want and need fsync?) we will try next
weekend until it finally goes right.

What I can see is that only the *write* performance of *long updates*
(and not inserts) are slow and they get slower in time: the first few
thousand go relatively fast, after that PostgreSQL crawls to a halt
(other "benchmarks" like bonnie++ or just dd'ing a big file don't have
this behavior).

I did notice that changing the I/O scheduler's nr_request from the
default 128 to 1024 or even 4096 made a remarkable performance
improvement. I suspect that experimenting with other I/O schedululers
could improve performance. But it is hard to find any useful
documentation about I/O schedulers.



--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Joost,

On 11/15/05 8:35 AM, "Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> wrote:

> thousand go relatively fast, after that PostgreSQL crawls to a halt
> (other "benchmarks" like bonnie++ or just dd'ing a big file don't have
> this behavior).

With RAID5, it could matter a lot what block size you run your ³dd bigfile²
test with. You should run ³dd if=/dev/zero of=bigfile bs=8k count=500000²
for a 2GB main memory machine, multiply the count by (<your mem>/2GB).

It is very important with the 3Ware cards to match the driver to the
firmware revision.

> I did notice that changing the I/O scheduler's nr_request from the
> default 128 to 1024 or even 4096 made a remarkable performance
> improvement. I suspect that experimenting with other I/O schedululers
> could improve performance. But it is hard to find any useful
> documentation about I/O schedulers.
>
You could try deadline, there¹s no harm, but I¹ve found that when you reach
the point of experimenting with schedulers, you are probably not addressing
the real problem.
>
On a 3Ware 9500 with HW RAID5 and 4 or more disks I think you should get
100MB/s write rate, which is double what Postgres can use. We find that
Postgres, even with fsync=false, will only run at a net COPY speed of about
8-12 MB/s, where 12 is the Bizgres number. 8.1 might do 10. But to get the
10 or 12, the WAL writing and other writing is about 4-5X more than the net
write speed, or the speed at which the input file is parsed and read into
the database.

So, if you can get your ³dd bigfile² test to write data at 50MB/s+ with a
blocksize of 8KB, you should be doing well enough.

Incidentally, we also find that using the XFS filesystem and setting the
readahead to 8MB or more is extremely beneficial for performance with the
3Ware cards (and with others, but especially for the older 3Ware cards).

Regards,

- Luke

Hi Luke,

On Tue, 2005-11-15 at 10:42 -0800, Luke Lonergan wrote:
> With RAID5, it could matter a lot what block size you run your ´dd
> bigfile¡ test with. You should run ´dd if=/dev/zero of=bigfile bs=8k
> count=500000¡ for a 2GB main memory machine, multiply the count by
> (<your mem>/2GB).
If I understand correctly (I have 4GB ram):

jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes transferred in 304.085269 seconds (26939812 bytes/sec)

Which looks suspicious: 26308 MB/sec???

> It is very important with the 3Ware cards to match the driver to the
> firmware revision.
OK, I am running 1 driver behind the firmware.

> I did notice that changing the I/O scheduler's nr_request from
> the
> default 128 to 1024 or even 4096 made a remarkable performance
> improvement. I suspect that experimenting with other I/O
> schedululers
> could improve performance. But it is hard to find any useful
> documentation about I/O schedulers.
>
> You could try deadline, thereÿs no harm, but Iÿve found that when you
> reach the point of experimenting with schedulers, you are probably not
> addressing the real problem.
It depends. I/O Schedulers (I assume) have a purpose: some schedulers
should be more appropriate for some circumstances. And maybe my specific
circumstances (converting a database with *many updates*) is a specific
circumstance. I really don't know....

> On a 3Ware 9500 with HW RAID5 and 4 or more disks I think you should
> get 100MB/s write rate, which is double what Postgres can use. We
> find that Postgres, even with fsync=false, will only run at a net COPY
> speed of about 8-12 MB/s, where 12 is the Bizgres number. 8.1 might
> do 10. But to get the 10 or 12, the WAL writing and other writing is
> about 4-5X more than the net write speed, or the speed at which the
> input file is parsed and read into the database.
As I have an (almost) seperate WAL disk: iostat does not show any
significant writing on the WAL disk....

> So, if you can get your ´dd bigfile¡ test to write data at 50MB/s+
> with a blocksize of 8KB, you should be doing well enough.
See above.

> Incidentally, we also find that using the XFS filesystem and setting
> the readahead to 8MB or more is extremely beneficial for performance
> with the 3Ware cards (and with others, but especially for the older
> 3Ware cards).
I don't have problems with my read performance but *only* with my
*update* performance (and not even insert performance). But than again I
am not the only one with these problems:

http://www.issociate.de/board/goto/8...3ware_+_RAID5_
+_xfs_performance.html#msg_894541
http://lkml.org/lkml/2005/4/20/110
http://seclists.org/lists/linux-kern.../Oct/1171.html

I am happy to share the tables against which I am running my checks....

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Joost Kraaijeveld wrote:
> If I understand correctly (I have 4GB ram):
>
> jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
> 1000000+0 records in
> 1000000+0 records out
> 8192000000 bytes transferred in 304.085269 seconds (26939812 bytes/sec)
>
> Which looks suspicious: 26308 MB/sec???

Eh? That looks more like ~25.7 MB/sec, assuming 1MB = 1024*1024 bytes.

--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Tue, 2005-11-15 at 12:41 -0700, Steve Wampler wrote:
> Joost Kraaijeveld wrote:
> > If I understand correctly (I have 4GB ram):
> >
> > jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
> > 1000000+0 records in
> > 1000000+0 records out
> > 8192000000 bytes transferred in 304.085269 seconds (26939812 bytes/sec)
> >
> > Which looks suspicious: 26308 MB/sec???
>
> Eh? That looks more like ~25.7 MB/sec, assuming 1MB = 1024*1024 bytes.
Oooops. This calculation error is not typical for my testing (I think ;-)).

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Joost,

On 11/15/05 11:51 AM, "Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> wrote:

> On Tue, 2005-11-15 at 12:41 -0700, Steve Wampler wrote:
>> > Joost Kraaijeveld wrote:
>>> > > If I understand correctly (I have 4GB ram):
>>> > >
>>> > > jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
>>> > > 1000000+0 records in
>>> > > 1000000+0 records out
>>> > > 8192000000 bytes transferred in 304.085269 seconds (26939812 bytes/sec)
>>> > >
>>> > > Which looks suspicious: 26308 MB/sec???
>> >
>> > Eh? That looks more like ~25.7 MB/sec, assuming 1MB = 1024*1024 bytes..
> Oooops. This calculation error is not typical for my testing (I think ;-)).

Summarizing the two facts of note: the write result is 1/4 of what you
should be getting, and you are running 1 driver behind the firmware.

You might update your driver, rerun the test, and if you still have the slow
result, verify that your filesystem isn¹t fragmented (multiple undisciplined
apps on the same filesystem will do that).

WAL on a separate disk, on a separate controller? What is the write
performance there?

Regards,

- Luke

Hi Luke,

On Tue, 2005-11-15 at 22:07 -0800, Luke Lonergan wrote:

> You might update your driver,
I will do that (but I admit that I am not looking forward to it. When I
was young and did not make money with my computer, I liked challenges
like compiling kernels and not being able to boot the computer. Not any
more :-)).


>
> WAL on a separate disk, on a separate controller? What is the write
> performance there?
WAL is on a separate disk and a separate controller, write performance:

jkr@Panoramix:/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes transferred in 166.499230 seconds (49201429 bytes/sec)

The quest continues...


--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hi Luke,


> It is very important with the 3Ware cards to match the driver to the
> firmware revision.
> So, if you can get your ´dd bigfile¡ test to write data at 50MB/s+
> with a blocksize of 8KB, you should be doing well enough.

I recompiled my kernel, added the driver and:

jkr@Panoramix:~$ dmesg | grep 3w
3ware 9000 Storage Controller device driver for Linux v2.26.03.019fw.
scsi4 : 3ware 9000 Storage Controller
3w-9xxx: scsi4: Found a 3ware 9000 Storage Controller at 0xfd8ffc00,
IRQ: 28.
3w-9xxx: scsi4: Firmware FE9X 2.08.00.005, BIOS BE9X 2.03.01.052, Ports:
8.


jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes transferred in 200.982055 seconds (40759858 bytes/sec)

Which is an remarkable increase in speed (38.9 MB/sec vs 25.7 MB/sec).

Thanks for your suggestions.


--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

I am mystified by the behavior of "alarm" in conjunction with Postgres/perl/DBD. Here is roughly what I'm doing:

eval {
local $SIG{ALRM} = sub {die("Timeout");};
$time = gettimeofday;
alarm 20;
$sth = $dbh->prepare("a query that may take a long time...");
$sth->execute();
alarm 0;
};
if ($@ && $@ =~ /Timeout/) {
my $elapsed = gettimeofday - $time;
print "Timed out after $elapsed seconds";
}

Now the mystery: It works, but it hardly matters what time I use for the alarm call, the actual alarm event always happens at 26 seconds. I can set "alarm 1" or "alarm 20", and it almost always hits right at 26 seconds.

Now if I increase alarm to anything in the range of about 25-60 seconds, the actual alarm arrives somewhere around the 90 second mark. It seems as though there are "windows of opportunity" for the alarm, and it is ignored until those "windows" arrive.

Anyone have a clue what's going on and/or how I can fix it?

A secondary question: It appears that $sth->cancel() is not implemented in the Pg DBD module. Is that true?

Thanks,
Craig

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I am mystified by the behavior of "alarm" in conjunction with
> Postgres/perl/DBD. Here is roughly what I'm doing:

> Anyone have a clue what's going on and/or how I can fix it?

Not really, but alarm has never worked well (or claimed to) with
DBI. The DBI docs recommend trying out Sys::Sigaction:

http://search.cpan.org/~lbaxter/Sys-SigAction/

> A secondary question: It appears that $sth->cancel() is not
> implemented in the Pg DBD module. Is that true?

Correct. DBD::Pg does not support asynchronous connections. It's
possible it may in the future, but there has not been much of a
perceived need. Feel free to enter a request on CPAN:

http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBD-Pg

There may be another way around it, if you can tell us some more
about what exactly it is you are trying to do.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200511161830
http://biglumber.com/x/web?pk=2529DF...9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFDe8ErvJuQZxSWSsgRAoZ6AJ9h6gV5U7PyLDJIqXLpSB 6r7NWaaQCdESSR
CdNexfvYvSQjOLkEdPXd26U=
=/W5F
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq