Re: dbt2 NOTPM numbers

Hi,

Jim Nasby wrote:
> I don't think that kind of testing is useful for good raid controllers
> on RAID5/6, because the controller will just be streaming the data out;
> it'll compute the parity blocks on the fly and just stream data to the
> drives as fast as possible.

That's why I called it 'simplistic throughput testing'...

> But that's not how writes in the database work (except for WAL); you're
> writing stuff all over the place, none of which is streamed. So in the
> best case (the entire stripe being updated is in the controller's
> cache), at a minimum it's going to have to write data + parity ( * 2 for
> RAID 6, IIRC) for every write. But any real-sized database is going to
> be far larger than your raid cache, which means there's a good chance a
> block being written will no longer have it's stripe in cache. In that
> case, the controller is going to have to read a bunch of data back off
> the drive, which is going to clobber performance.

I'm well aware. Our workload (hopefully) consists of a much lower
writes/reads ratio than dbt2, so RAID 6 might work anyway.

> Now, add that performance bottleneck on top of your WAL writes and
> you're in real trouble.

Well, I'm basically surprised of the low NOTPM numbers compared to my
desktop system, which also does around 200 NOTPMs, with only two
platters in RAID 1 config... How can a server with four Cores and 8
Platters be equaly slow?

Anyway, I've now reconfigured the system with RAID 1+0 and got more than
twice the NOTPMs:

Response Time (s)
Transaction % Average : 90th % Total
Rollbacks %
------------ ----- --------------------- ----------- ---------------
-----
Delivery 3.84 204.733 : 241.998 704
0 0.00
New Order 45.77 203.651 : 242.847 8382
75 0.90
Order Status 4.32 199.184 : 238.081 792 0
0.00
Payment 42.02 198.969 : 236.549 7695
0 0.00
Stock Level 4.04 198.668 : 236.113 740
0 0.00
------------ ----- --------------------- ----------- ---------------
-----

567.72 new-order transactions per minute (NOTPM)
14.5 minute duration
0 total unknown errors
529 second(s) ramping up

I'm still feeling that 550 is pretty low. The response times are beyond
good and evil.

As vmstat.out tells us, the CPUs are still pretty much idle or waiting
most of the time.

procs -----------memory---------- ---swap-- -----io---- -system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy
id wa
0 3 494560 2181964 8 1787680 13 15 317 242 140 2 4
1 72 23
0 9 494560 1558892 8 2298348 0 0 2973 2018 584 1114 2
1 76 21
1 14 494496 424116 8 3316000 2 0 5613 9293 935 2943 5
1 29 65
0 15 452840 150148 8 3487160 738 3 5662 8709 925 3444 5
2 21 73
0 11 439172 151052 8 3386556 263 0 5690 8293 969 4145 5
2 23 70
0 17 438996 149748 8 3308184 57 6 5036 7174 902 4104 5
2 25 69
1 25 439940 150344 8 3228304 9 28 4757 7479 922 4269 5
2 26 67

For everybody interested, these settings are different from Pg 8.2
default postgresql.conf:

listen_addresses = '*'
port = 54321
shared_buffers = 2048MB
work_mem = 10MB
maintenance_work_mem = 64MB
#max_stack_depth = 4MB
max_fsm_pages = 409600
eachcheckpoint_segments = 6
checkpoint_timeout = 1h
effective_cache_size = 3800MB
log_min_duration_statement = 500

For dbt2, I've used 500 warehouses and 90 concurrent connections,
default values for everything else.

Do I simply have to put more RAM (currently 4GB) in that machine? Or
what else can be wrong?

Is anybody else seeing low performance with the Areca SATA Controllers?
(in my case: "Areca Technology Corp. ARC-1260 16-Port PCI-Express to
SATA RAID Controller", according to lspci)

Then again, maybe I'm just expecting too much...

Regards

Markus