Re: Is this way of testing a bad idea? - Mailing list pgsql-performance

Also, as Tom stated, defining your test cases is a good idea before you
start benchmarking. Our application has a load data phase, then a
query/active use phase. So, we benchmark both (data loads, and then
transactions) since they're quite different workloads, and there's
different ways to optimize for each.

For bulk loads, I would look into either batching several inserts into
one transaction or the copy command. Do some testing here to figure out
what works best for your hardware/setup (for example, we usually batch
several thousand inserts together for a pretty dramatic increase in
performance). There's usually a sweet spot in there depending on how
your WAL is configured and other concurrent activity.

Also, when testing bulk loads, be careful to setup a realistic test. If
your application requires foreign keys and indexes, these can
significantly slow down bulk inserts. There's several optimizations-
check the mailing lists and the manual.

And lastly, when you're loading tons of data, as previously pointed out,
the normal state of the system is to be heavily utilized (in fact, I
would think this is ideal since you know you're making full use of your
hardware).

HTH,

Bucky

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Mark Lewis
Sent: Thursday, August 24, 2006 9:40 AM
To: Fredrik Israelsson
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Is this way of testing a bad idea?

> Monitoring the processes using top reveals that the total amount of
> memory used slowly increases during the test. When reaching insert
> number 40000, or somewhere around that, memory is exhausted, and the
the
> systems begins to swap. Each of the postmaster processes seem to use a
> constant amount of memory, but the total memory usage increases all
the
> same.

So . . . . what's using the memory?  It doesn't sound like PG is using
it, so is it your Java app?

If it's the Java app, then it could be that your code isn't remembering
to do things like close statements, or perhaps the max heap size is set
too large for your hardware.  With early RHEL3 kernels there was also a
quirky interaction with Sun's JVM where the system swaps itself to death
even when less than half the physical memory is in use.

If its neither PG nor Java, then perhaps you're misinterpreting the
results of top.  Remember that the "free" memory on a properly running
Unix box that's been running for a while should hover just a bit above
zero due to normal caching; read up on the 'free' command to see the
actual memory utilization.

-- Mark

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