http://developer.osdl.org/markw/74/

I had a couple of hiccups doubling the database size,
but I have results with proper linux kernel profile data now.  The
increase in database size has decreased the overall performance, as
expected...

I haven't had the opportunity to try different database parameters, such
as effective cache sizes, WAL buffers, etc, yet.  I'm trying to track
down why I'm attempting to insert duplicate keys into one of the tables,
which will hopefully be a simple driver error.  But will get back with
more data when I can.

-- 
Mark Wong - - markw@osdl.org
Open Source Development Lab Inc - A non-profit corporation
12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005
(503) 626-2455 x 32 (office)
(503) 626-2436      (fax)

Mon, 22 Sep 2003 14:40:27 -0700 (PDT)
markw@osdl.org
More Prelimiary DBT-2 Test Results with PostgreSQL 7.3.4

markw@osdl.org writes:

> http://developer.osdl.org/markw/74/

Are those response times in the right unit? 7-10s?

Are these mostly full table scans and big batch updates?

Personally, I'm more interested in seeing OLTP-oriented benchmarks testing
quick index based transactions in the 20-200ms range, not big i/o-bound batch
transactions. There's certainly a need for both, but I think the former are
much more important to micro-optimize, at least for my needs.

-- 
greg


22 Sep 2003 21:41:14 -0400
Greg Stark <gsstark@mit.edu>
Re: More Prelimiary DBT-2 Test Results with PostgreSQL 7.3.4
On 22 Sep, Greg Stark wrote:
> 
> markw@osdl.org writes:
> 
>> http://developer.osdl.org/markw/74/
> 
> Are those response times in the right unit? 7-10s?

Yeah, the database really isn't tuned at all.  I've gotten some
suggestions off the list that I will be trying.  I'll report them as I
complete them.
 
> Are these mostly full table scans and big batch updates?

The plans (http://developer.osdl.org/markw/74/db/plan0.out) don't show
any table scans.  They appears to be mostly index scans.  There aren't
any batch updates.
 
> Personally, I'm more interested in seeing OLTP-oriented benchmarks testing
> quick index based transactions in the 20-200ms range, not big i/o-bound batch
> transactions. There's certainly a need for both, but I think the former are
> much more important to micro-optimize, at least for my needs.

Right, and when this thing is tuned better, I expect the transactions to
be down in the 20-200ms range.  I do have about 70 drives for the
database, so it really shouldn't be i/o bound at with the size of the
database I'm using now, which is about 20 or 30 GB.

Mark

Wed, 24 Sep 2003 14:31:47 -0700 (PDT)
markw@osdl.org
Re: More Prelimiary DBT-2 Test Results with PostgreSQL
markw@osdl.org writes:

> On 22 Sep, Greg Stark wrote:
> > Are those response times in the right unit? 7-10s?
> 
> The plans (http://developer.osdl.org/markw/74/db/plan0.out) don't show
> any table scans.  They appears to be mostly index scans.  There aren't
> any batch updates.

Would it be easy to generate this file doing "explain analyze" instead? It
would give me a better idea what you're doing since I don't know much about
the data.

Just looking at the plans I don't see how any of these queries could possibly
take 7s. There are two that could possibly take a few hundred milliseconds,
the others I would expect to be under 100ms. Obviously this is all just a gut
reaction, not having looked at the data, and it would depend on the hardware.
But none of the queries look like they should measuring reaction times in
seconds.

> Right, and when this thing is tuned better, I expect the transactions to
> be down in the 20-200ms range.  I do have about 70 drives for the
> database, so it really shouldn't be i/o bound at with the size of the
> database I'm using now, which is about 20 or 30 GB.

70 drives for 20-30G? That's pretty extreme.

-- 
greg


24 Sep 2003 18:15:41 -0400
Greg Stark <gsstark@mit.edu>
Re: More Prelimiary DBT-2 Test Results with PostgreSQL 7.3.4
On 24 Sep, Greg Stark wrote:
> markw@osdl.org writes:
> 
>> On 22 Sep, Greg Stark wrote:
>> > Are those response times in the right unit? 7-10s?
>> 
>> The plans (http://developer.osdl.org/markw/74/db/plan0.out) don't show
>> any table scans.  They appears to be mostly index scans.  There aren't
>> any batch updates.
> 
> Would it be easy to generate this file doing "explain analyze" instead? It
> would give me a better idea what you're doing since I don't know much about
> the data.

No problem: http://developer.osdl.org/markw/misc/plana.out

> Just looking at the plans I don't see how any of these queries could possibly
> take 7s. There are two that could possibly take a few hundred milliseconds,
> the others I would expect to be under 100ms. Obviously this is all just a gut
> reaction, not having looked at the data, and it would depend on the hardware.
> But none of the queries look like they should measuring reaction times in
> seconds.

I have a 4-way 1.5Ghz Xeon with 256KB cache and hyper-threading enabled
and 4GB of memory.  Although the disk controllers are reducing my
physical memory by about 500MB.  I can provide more details if you like.
 
>> Right, and when this thing is tuned better, I expect the transactions to
>> be down in the 20-200ms range.  I do have about 70 drives for the
>> database, so it really shouldn't be i/o bound at with the size of the
>> database I'm using now, which is about 20 or 30 GB.
> 
> 70 drives for 20-30G? That's pretty extreme.

Yeah, we're starting small but we'll increase the database size as we
move along.

Mark

Wed, 24 Sep 2003 15:24:34 -0700 (PDT)
markw@osdl.org
Re: More Prelimiary DBT-2 Test Results with PostgreSQL
markw@osdl.org writes:

> On 24 Sep, Greg Stark wrote:
> > markw@osdl.org writes:
> > 
> >> On 22 Sep, Greg Stark wrote:
> >> > Are those response times in the right unit? 7-10s?
>
> No problem: http://developer.osdl.org/markw/misc/plana.out

Ok, I guess I misunderstood you. These queries are taking 0.5ms - 300ms except
for the last aggregate query which takes just over 1s.

-- 
greg


24 Sep 2003 19:14:53 -0400
Greg Stark <gsstark@mit.edu>
Re: More Prelimiary DBT-2 Test Results with PostgreSQL 7.3.4
On 24 Sep, Greg Stark wrote:
> Ok, I guess I misunderstood you. These queries are taking 0.5ms - 300ms except
> for the last aggregate query which takes just over 1s.

http://developer.osdl.org/markw/dbt2-pgsql/120/

I have more data where I got the response times for each transaction
much lower by increasing shared_buffers and wal_buffers.  I wanted to
let everyone know in case someone wanted to take another look. I'm
working on increasing the database size to drive the system harder.

Mark

Mon, 6 Oct 2003 12:15:53 -0700 (PDT)
markw@osdl.org
Re: More Prelimiary DBT-2 Test Results with PostgreSQL