[PERFORM] Slow update

[Hilary Forbes]

Hello everyone

I must be doing something very wrong here so help please!  I have two tables

tableA has 300,000 recs
tableB has 20,000 recs

I need to set the value of a field in table A to a value in table B depending 
on the existence of the record in table B.  So what I have done is

UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE 
a.key1=b.key1;

The primary key of tableA is key1 and that of tableB is key1 ie the join is on 
primary keys.

The "optimizer" has elected to d a sequential scan on tableA to determine which 
fields to update rather than the query being driveb by tableB and it is taking 
forever.  Surely I must be able to force the system to read down tableB in 
preference to reading down tableA?

(Please don't ask why tableA and tableB are not amalgamated - that's another 
story altogether!!!)

Many thanks in advance
Hilary


Hilary Forbes
The DMR Information and Technology Group  (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330 
DMR is a UK registered trade mark of DMR Limited
**


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

Re: [PERFORM] LEFT JOIN optimization

[Manfred Koizar]

On Mon, 12 Sep 2005 00:47:57 +0300, Ksenia Marasanova
<[EMAIL PROTECTED]> wrote:
>   ->  Seq Scan on user_  (cost=0.00..7430.63 rows=12763 width=245)
>(actual time=360.431..1120.012 rows=12763 loops=1)

If 12000 rows of the given size are stored in more than 7000 pages, then
there is a lot of free space in these pages.  Try VACUUM FULL ...

Servus
 Manfred

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

   http://archives.postgresql.org

Re: [PERFORM] Slow update

[Richard Huxton]

Hilary Forbes wrote:


I need to set the value of a field in table A to a value in table B depending 
on the existence of the record in table B.  So what I have done is

UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE 
a.key1=b.key1;


Check the EXPLAIN carefully, are you sure the tableA in "UPDATE" is the 
same as that in your "FROM" clause. If so, why are you SETting a.val1?


If not, you've probably got an unconstrained join.
--
  Richard Huxton
  Archonet Ltd

---(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

Re: [PERFORM] Prepared statement not using index

[Dave Cramer]

The difference between the 7.4 driver and the 8.0.3 driver is the  
8.0.3 driver is using server side prepared statements and binding the  
parameter to the type in setXXX(n,val).


The 7.4 driver just replaces the ? with the value and doesn't use  
server side prepared statements.


Dave


On 1-Sep-05, at 7:09 PM, Guido Neitzer wrote:


Hi.

I have an interesting problem with the JDBC drivers. When I use a  
select like this:


"SELECT t0.aktiv, t0.id, t0.ist_teilnehmer, t0.nachname, t0.plz,  
t0.vorname FROM public.dga_dienstleister t0 WHERE t0.plz  
like ?::varchar(256) ESCAPE '|'" withBindings: 1:"53111"(plz)>


the existing index on the plz column is not used.

When I the same select with a concrete value, the index IS used.

I use PostgreSQL 8.0.3 on Mac OS X and the JDBC driver 8.0-312 JDBC 3.

After a lot of other things, I tried using a 7.4 driver and with  
this, the index is used in both cases.


Why can this happen? Is there a setting I might have not seen?  
Something I do wrong?


cug

---(end of  
broadcast)---

TIP 5: don't forget to increase your free space map settings





---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] Prepared statement not using index

[Guido Neitzer]

On 12.09.2005, at 14:38 Uhr, Dave Cramer wrote:

The difference between the 7.4 driver and the 8.0.3 driver is the  
8.0.3 driver is using server side prepared statements and binding  
the parameter to the type in setXXX(n,val).


Would be a good idea when this were configurable.

I found my solution (use the JDBC2 drivers with protocolVersion=2),  
but how long will this work?


cug

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

Re: [PERFORM] Prepared statement not using index

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


> The difference between the 7.4 driver and the 8.0.3 driver is the
> 8.0.3 driver is using server side prepared statements and binding the
> parameter to the type in setXXX(n,val).
>
> The 7.4 driver just replaces the ? with the value and doesn't use
> server side prepared statements.

DBD::Pg has a few flags that enables you to do things like purposely avoid
using server side prepares, and force a reprepare of a particular statement.
Perhaps something like that is available for the JDBC driver? If not,
maybe someone would be willing to add it in?

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200509120925
https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iEYEARECAAYFAkMlgdAACgkQvJuQZxSWSsjMlQCePc4dpE0BCT3W//y/N9uolkmK
ViIAnjR1fF14KbP+cX+xV8lmdlL6Be2k
=NtXw
-END PGP SIGNATURE-



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

Re: [PERFORM] Slow update

[Bruno Wolff III]

On Mon, Sep 12, 2005 at 10:14:25 +0100,
  Hilary Forbes <[EMAIL PROTECTED]> wrote:
> Hello everyone
> 
> I must be doing something very wrong here so help please!  I have two tables
> 
> tableA has 300,000 recs
> tableB has 20,000 recs
> 
> I need to set the value of a field in table A to a value in table B depending 
> on the existence of the record in table B.  So what I have done is
> 
> UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE 
> a.key1=b.key1;
> 
> The primary key of tableA is key1 and that of tableB is key1 ie the join is 
> on primary keys.
> 
> The "optimizer" has elected to d a sequential scan on tableA to determine 
> which fields to update rather than the query being driveb by tableB and it is 
> taking forever.  Surely I must be able to force the system to read down 
> tableB in preference to reading down tableA?

It would help to see the exact query and the explain analyze output. Hopefully
you didn't really write the query similar to above, since it is using illegal
syntax and the if it was changed slightly to become legal than it would do a
cross join of table A with the inner join of tableA and tableB, which isn't
what you want.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Slow update

[Tom Lane]

Hilary Forbes <[EMAIL PROTECTED]> writes:
> I need to set the value of a field in table A to a value in table B depending 
> on the existence of the record in table B.  So what I have done is

> UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE 
> a.key1=b.key1;

You've written an unconstrained join to a second copy of tableA.

regards, tom lane

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

Re: [PERFORM] Prepared statement not using index

[Dave Cramer]

On 12-Sep-05, at 9:22 AM, Guido Neitzer wrote:


On 12.09.2005, at 14:38 Uhr, Dave Cramer wrote:


The difference between the 7.4 driver and the 8.0.3 driver is the  
8.0.3 driver is using server side prepared statements and binding  
the parameter to the type in setXXX(n,val).




Would be a good idea when this were configurable.

You found the configuration for it


I found my solution (use the JDBC2 drivers with protocolVersion=2),  
but how long will this work?


I think you would be better understanding what the correct type is  
for the index to work properly.


cug





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

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

Re: [PERFORM] Prepared statement not using index

[Dave Cramer]

It's added, just use the old protocol .

Here are the connection parameters

http://jdbc.postgresql.org/documentation/head/connect.html#connection- 
parameters


Dave


On 12-Sep-05, at 9:26 AM, Greg Sabino Mullane wrote:



-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1




The difference between the 7.4 driver and the 8.0.3 driver is the
8.0.3 driver is using server side prepared statements and binding the
parameter to the type in setXXX(n,val).

The 7.4 driver just replaces the ? with the value and doesn't use
server side prepared statements.



DBD::Pg has a few flags that enables you to do things like  
purposely avoid
using server side prepares, and force a reprepare of a particular  
statement.

Perhaps something like that is available for the JDBC driver? If not,
maybe someone would be willing to add it in?

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200509120925
https://www.biglumber.com/x/web? 
pk=2529DF6AB8F79407E94445B4BC9B906714964AC8


-BEGIN PGP SIGNATURE-

iEYEARECAAYFAkMlgdAACgkQvJuQZxSWSsjMlQCePc4dpE0BCT3W//y/N9uolkmK
ViIAnjR1fF14KbP+cX+xV8lmdlL6Be2k
=NtXw
-END PGP SIGNATURE-



---(end of  
broadcast)---

TIP 5: don't forget to increase your free space map settings





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

[PERFORM] Performance considerations for very heavy INSERT traffic

[Brandon Black]

I'm in the process of developing an application which uses PostgreSQL
for data storage.  Our database traffic is very atypical, and as a
result it has been rather challenging to figure out how to best tune
PostgreSQL on what development hardware we have, as well as to figure
out exactly what we should be evaluating and eventually buying for
production hardware.

The vast, overwhelming majority of our database traffic is pretty much
a non-stop stream of INSERTs filling up tables.  It is akin to
data acquisition.  Several thousand clients are sending
once-per-minute updates full of timestamped numerical data at our
central server, which in turn performs INSERTs into several distinct
tables as part of the transaction for that client.  We're talking
on the order of ~100 transactions per second, each containing INSERTs
to multiple tables (which contain only integer and floating point
columns and a timestamp column - the primary key (and only index) is on
a unique integer ID for the client and the timestamp).  The
transaction load is spread evenly over time by having the clients send
their per-minute updates at random times rather than on the exact
minute mark.

There will of course be users using a web-based GUI to extract data
from these tables and display them in graphs and whatnot, but the
SELECT query traffic will always be considerably less frequent and
intensive than the incessant INSERTs, and it's not that big a deal if
the large queries take a little while to run.

This data also expires - rows with timestamps older than X days will be
DELETEd periodically (once an hour or faster), such that the tables
will reach a relatively stable size (pg_autovacuum is handling
vacuuming for now, but considering our case, we're thinking of killing
pg_autovacuum in favor of having the periodic DELETE process also do a
vacuum of affected tables right after the DELETE, and then have it
vacuum the other low traffic tables once a day while it's at it).

There is an aggregation layer in place which proxies the inbound data
from the clients into a small(er) number of persistent postgresql
backend processes.  Right now we're doing one aggregator per 128
clients (so instead of 128 seperate database connections over the
course of a minute for a small transaction each, there is a single
database backend that is constantly committing transactions at a rate
of ~ 2/second).  At a test load of ~1,000 clients, we would have 8
aggregators running and 8 postgresql backends.  Testing has seemed
to indicate we should aggregate even harder - the planned production
load is ~5,000 clients initially, but will grow to almost double that
in the not-too-distant future, and that would mean ~40 backends at 128
clients each initially.  Even on 8 cpus, I'm betting 40 concurrent
backends doing 2 tps is much worse off than 10 backends doing 8 tps.


Test hardware right now is a dual Opteron with 4G of ram, which we've
barely gotten 1,000 clients running against.  Current disk hardware in
testing is whatever we could scrape together (4x 3-ware PCI hardware
RAID controllers, with 8 SATA drives in a RAID10 array off of each -
aggregated up in a 4-way stripe with linux md driver and then formatted
as ext3 with an appropriate stride parameter and data=""
Production will hopefully be a 4-8-way Opteron, 16 or more G of RAM,
and a fiberchannel hardware raid array or two (~ 1TB available RAID10 storage) with 15krpm disks and
battery-backed write cache.

I know I haven't provided a whole lot of application-level detail here,
but does anyone have any general advice on tweaking postgresql to deal
with a very heavy load of concurrent and almost exclusively write-only
transactions?  Increasing shared_buffers seems to always help,
even out to half of the dev box's ram (2G).  A 100ms commit_delay
seemed to help, but tuning it (and _siblings) has been difficult. 
We're using 8.0 with the default 8k blocksize, but are strongly
considering both developing against 8.1 (seems it might handle the
heavy concurrency better), and re-compiling with 32k blocksize since
our storage arrays will inevitably be using fairly wide stripes. 
Any advice on any of this (other than drop the project while you're
still a little bit sane)?

--Brandon

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

[PFC]

I know I haven't provided a whole lot of application-level detail here,


You did !

What about :

- using COPY instead of INSERT ?
(should be easy to do from the aggregators)

- using Bizgres ?   
(which was designed for your use case)

- splitting the xlog and the data on distinct physical drives or arrays

- benchmarking something else than ext3
(xfs ? reiser3 ?)

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

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

[Qingqing Zhou]

 

  "Brandon Black" <[EMAIL PROTECTED]> wrote ...
  Increasing shared_buffers seems to always help, even out to half of 
  the dev box's ram (2G).  
   
  Though officially PG does not prefer huge 
  shared_buffers size, I did see several times that performance was 
  boosted in case IO is the bottleneck. Also, if you want to use big bufferpool 
  setting, make sure your version has Tom's split BufMgrLock patch
  (http://archives.postgresql.org/pgsql-committers/2005-03/msg00025.php), 
  which might already in 8.0.x somewhere. And if you want to use bufferpool 
  bigger than 2G on 64-bit machine, you may need 8.1 (http://archives.postgresql.org/pgsql-committers/2005-08/msg00221.php).
   
  Regards,
  Qingqing

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

[Alex Turner]

Split your system into multiple partitions of RAID 10s.For max
performance,  ten drive RAID 10 for pg_xlog (This will max out a
PCI-X bus) on Bus A, multiple 4/6Drive RAID 10s for tablespaces on Bus
B. For max performance I would recommend using one RAID 10 for raw data
tables, one for aggregate tables and one for indexes.  More RAM
will only help you with queries against your data, if you are
pre-aggregating, then you may not need all that much RAM.

You can easily get 100 tansacts per second with even less hardware with a little data partitioning.

Choose your controller carefully as many don't co-operate with linux well.
Alex Turner
NetEconomist
On 9/12/05, Brandon Black <[EMAIL PROTECTED]> wrote:

I'm in the process of developing an application which uses PostgreSQL
for data storage.  Our database traffic is very atypical, and as a
result it has been rather challenging to figure out how to best tune
PostgreSQL on what development hardware we have, as well as to figure
out exactly what we should be evaluating and eventually buying for
production hardware.

The vast, overwhelming majority of our database traffic is pretty much
a non-stop stream of INSERTs filling up tables.  It is akin to
data acquisition.  Several thousand clients are sending
once-per-minute updates full of timestamped numerical data at our
central server, which in turn performs INSERTs into several distinct
tables as part of the transaction for that client.  We're talking
on the order of ~100 transactions per second, each containing INSERTs
to multiple tables (which contain only integer and floating point
columns and a timestamp column - the primary key (and only index) is on
a unique integer ID for the client and the timestamp).  The
transaction load is spread evenly over time by having the clients send
their per-minute updates at random times rather than on the exact
minute mark.

There will of course be users using a web-based GUI to extract data
from these tables and display them in graphs and whatnot, but the
SELECT query traffic will always be considerably less frequent and
intensive than the incessant INSERTs, and it's not that big a deal if
the large queries take a little while to run.

This data also expires - rows with timestamps older than X days will be
DELETEd periodically (once an hour or faster), such that the tables
will reach a relatively stable size (pg_autovacuum is handling
vacuuming for now, but considering our case, we're thinking of killing
pg_autovacuum in favor of having the periodic DELETE process also do a
vacuum of affected tables right after the DELETE, and then have it
vacuum the other low traffic tables once a day while it's at it).

There is an aggregation layer in place which proxies the inbound data
from the clients into a small(er) number of persistent postgresql
backend processes.  Right now we're doing one aggregator per 128
clients (so instead of 128 seperate database connections over the
course of a minute for a small transaction each, there is a single
database backend that is constantly committing transactions at a rate
of ~ 2/second).  At a test load of ~1,000 clients, we would have 8
aggregators running and 8 postgresql backends.  Testing has seemed
to indicate we should aggregate even harder - the planned production
load is ~5,000 clients initially, but will grow to almost double that
in the not-too-distant future, and that would mean ~40 backends at 128
clients each initially.  Even on 8 cpus, I'm betting 40 concurrent
backends doing 2 tps is much worse off than 10 backends doing 8 tps.


Test hardware right now is a dual Opteron with 4G of ram, which we've
barely gotten 1,000 clients running against.  Current disk hardware in
testing is whatever we could scrape together (4x 3-ware PCI hardware
RAID controllers, with 8 SATA drives in a RAID10 array off of each -
aggregated up in a 4-way stripe with linux md driver and then formatted
as ext3 with an appropriate stride parameter and data=""
Production will hopefully be a 4-8-way Opteron, 16 or more G of RAM,
and a fiberchannel hardware raid array or two (~ 1TB available RAID10 storage) with 15krpm disks and
battery-backed write cache.

I know I haven't provided a whole lot of application-level detail here,
but does anyone have any general advice on tweaking postgresql to deal
with a very heavy load of concurrent and almost exclusively write-only
transactions?  Increasing shared_buffers seems to always help,
even out to half of the dev box's ram (2G).  A 100ms commit_delay
seemed to help, but tuning it (and _siblings) has been difficult. 
We're using 8.0 with the default 8k blocksize, but are strongly
considering both developing against 8.1 (seems it might handle the
heavy concurrency better), and re-compiling with 32k blocksize since
our storage arrays will inevitably be using fairly wide stripes. 
Any advice on any of this (other than drop the project while you're
still a little bit sane)?

--Brandon

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

[Brandon Black]

On 9/12/05, PFC <[EMAIL PROTECTED]> wrote:
> I know I haven't provided a whole lot of application-level detail here,You did !What about :- using COPY instead of INSERT ?(should
be easy to do from the aggregators)
Possibly, although it would kill the current design of returning the
database transaction status for a single client packet back to the
client on transaction success/failure.   The aggregator could put
several clients' data into a series of delayed multi-row copy
statements.
- using Bizgres ?(which
was designed for your use case)
I only briefly scanned their "About" page, but they didn't sound
particularly suited to my case at the time (it sounded kinds buzzwordy
actually, which I suppose is great for business apps people :) ). 
We're more of a sciency shop.  I'll go look at the project in more
detail tonight in light of your recommendation.
- splitting the xlog and the data on distinct physical drives or arrays

That would almost definitely help, I haven't tried it yet. 
Speaking of the xlog, anyone know anything specific about the WAL
tuning parameters for heavy concurrent write traffic?  What little
I could dig up on WAL tuning was contradictory, and testing some random
changes to the parameters hasn't been very conclusive yet.  I
would imagine the WAL buffers stuff could potentially have a large
effect for us.
- benchmarking something else than ext3(xfs ? reiser3 ?)

We've had bad experiences under extreme and/or strange workloads with
XFS here in general, although this is the first major postgresql
project - the rest were with other applications writing to XFS. 
Bad experiences like XFS filesystems "detecting internal
inconsistencies" at runtime and unmounting themselves from within the
kernel module (much to the dismay of applications with open files on
the filesystem), on machines with validated good hardware.  It has
made me leary of using anything other than ext3 for fear of stability
problems.  Reiser3 might be worth taking a look at though.

Thanks for the ideas,
-- Brandon

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

[Alan Stange]

Brandon Black wrote:




On 9/12/05, *PFC* <[EMAIL PROTECTED] 
> wrote:




- benchmarking something else than ext3
(xfs ? reiser3 ?)


We've had bad experiences under extreme and/or strange workloads with 
XFS here in general, although this is the first major postgresql 
project - the rest were with other applications writing to XFS.  Bad 
experiences like XFS filesystems "detecting internal inconsistencies" 
at runtime and unmounting themselves from within the kernel module 
(much to the dismay of applications with open files on the 
filesystem), on machines with validated good hardware.  It has made me 
leary of using anything other than ext3 for fear of stability 
problems.  Reiser3 might be worth taking a look at though.


Just one tidbit.   We tried XFS on a very active system similar to what 
you describe.   Dual opterons, 8GB memory, fiber channel drives, 2.6 
kernel, etc.   And the reliability was awful.   We spent a lot of time 
making changes one at a time to try and isolate the cause; when we 
switched out from XFS to ReiserFS our stability problems went away.


It may be the case that the XFS problems have all been corrected in 
newer kernels, but I'm not going to put too much effort into trying that 
again.



I recently built a postgres with 32KB block sizes and have been doing 
some testing.  For our particular workloads it has been a win.


-- Alan

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

[Greg Stark]

Brandon Black <[EMAIL PROTECTED]> writes:

> The vast, overwhelming majority of our database traffic is pretty much a 
> non-stop stream of INSERTs filling up tables. 

That part Postgres should handle pretty well. It should be pretty much limited
by your I/O bandwidth so big raid 1+0 arrays are ideal. Putting the WAL on a
dedicated array would also be critical.

The WAL parameters like commit_delay and commit_siblings are a bit of a
mystery. Nobody has done any extensive testing of them. It would be quite
helpful if you find anything conclusive and post it. It would also be
surprising if they had a very large effect. They almost got chopped recently
because they weren't believed to be useful.

You might also ponder whether you need to by issuing a commit for every datum.
If you only need to commit periodically you can get much better throughput. I
suppose that's the same as commit_siblings. It would be interesting to know if
you can get those parameters to perform as well as batching up records
yourself.

> There will of course be users using a web-based GUI to extract data from 
> these tables and display them in graphs and whatnot, but the SELECT query 
> traffic will always be considerably less frequent and intensive than the 
> incessant INSERTs, and it's not that big a deal if the large queries take a 
> little while to run.

I do fear these queries. Even if they aren't mostly terribly intensive if
you're pushing the edges of your write I/O bandwidth then a single seek to
satisfy one of these selects could really hurt your throughput. 

That said, as long as your WAL is on a dedicated drive Postgres's architecture
should in theory be ideal and allow you do run these things with impunity. The
WAL is purely write-only and it's the only thing your inserts will be blocking
on.

> This data also expires - rows with timestamps older than X days will be 
> DELETEd periodically (once an hour or faster), such that the tables will 
> reach a relatively stable size (pg_autovacuum is handling vacuuming for now, 
> but considering our case, we're thinking of killing pg_autovacuum in favor 
> of having the periodic DELETE process also do a vacuum of affected tables 
> right after the DELETE, and then have it vacuum the other low traffic tables 
> once a day while it's at it).

Ay, there's the rub.

Taking this approach means you have vacuums running which have to scan your
entire table and your inserts are being sprayed all over the disk. 

An alternative you may consider is using partitioned tables. Then when you
want to expire old records you simply drop the oldest partition. Or in your
case you might rotate through the partitions, so you just truncate the oldest
one and start inserting into it.

Unfortunately there's no built-in support for partitioned tables in Postgres.
You get to roll your own using UNION ALL or using inherited tables. Various
people have described success with each option though they both have
downsides too.

Using partitioned tables you would never need to delete any records except for
when you delete all of them. So you would never need to run vacuum except on
newly empty partitions. That avoids having to scan through all those pages
that you know will never have holes. If you use TRUNCATE (or VACUUM ALL or
CLUSTER) that would mean your inserts are always sequential too (though it
also means lots of block allocations along the way, possibly not an
advantage).

This may be a lot more work to set up and maintain but I think it would be a
big performance win. It would directly speed up the WAL writes by avoiding
those big full page dumps. And it would also cut out all the I/O traffic
vacuum generates.


> Increasing shared_buffers seems to always help, even out to half of the dev
> box's ram (2G).

Half should be a pessimal setting. It means virtually everything is buffered
twice. Once in the kernel and once in Postgres. Effectively halving your
memory. If that's really helping try raising it even further, to something
like 90% of your memory. But the conventional dogma is that shared buffers
should never be more than about 10k no matter how much memory you have. Let
the kernel do the bulk of the buffering.

That said it's even more mysterious in your situation. Why would a large
shared buffers help insert-only performance much at all? My guess is that it's
letting the vacuums complete quicker. Perhaps try raising work_mem?

-- 
greg


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

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

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

[Christopher Petrilli]

On 9/12/05, Brandon Black <[EMAIL PROTECTED]> wrote:
> 
>  I'm in the process of developing an application which uses PostgreSQL for
> data storage.  Our database traffic is very atypical, and as a result it has
> been rather challenging to figure out how to best tune PostgreSQL on what
> development hardware we have, as well as to figure out exactly what we
> should be evaluating and eventually buying for production hardware.

A few suggestions...

1) Switch to COPY if you can, it's anywhere from 10-100x faster than
INSERT, but it does not necessarily fit your idea of updating multiple
tables.  In that case, try and enlarge the transaction's scope and do
multiple INSERTs in the same transaction.  Perhaps batching once per
second, or 5 seconds, and returning the aggregate result ot the
clients.

2) Tune ext3.  The default configuration wrecks high-write situations.
 Look into data=writeback for mounting, turning off atime (I hope
you've done this already) updates, and also modifying the scheduler to
the elevator model.  This is poorly documented in Linux (like just
about everything), but it's crtical.

3) Use 8.1 and strongly look at Bizgres. The data partitioning is critical.

4) Make sure you are not touching more data than you need, and don't
have any extraneous indexes.  Use the planner to make sure every index
is used, as it substantially increases the write load.

I've worked on a few similar applications, and this is a hard thing in
any database, even Oracle.

Chris 

-- 
| Christopher Petrilli
| [EMAIL PROTECTED]

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

   http://archives.postgresql.org

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

[Brandon Black]

On 12 Sep 2005 23:07:49 -0400, Greg Stark <[EMAIL PROTECTED]> wrote:
The WAL parameters like commit_delay and commit_siblings are a bit of amystery. Nobody has done any extensive testing of them. It would be quite
helpful if you find anything conclusive and post it. It would also besurprising if they had a very large effect. They almost got chopped recentlybecause they weren't believed to be useful.You might also ponder whether you need to by issuing a commit for every datum.
If you only need to commit periodically you can get much better throughput. Isuppose that's the same as commit_siblings. It would be interesting to know ifyou can get those parameters to perform as well as batching up records
yourself.
Ideally I'd like to commit the data seperately, as the data could
contain errors which abort the transaction, but it may come down to
batching it and coding things such that I can catch and discard the
offending row and retry the transaction if it fails (which should be
fairly rare I would hope).  I was hoping that the
commit_delay/commit_siblings stuff would allow me to maintain
simplistic transaction failure isolation while giving some of the
benefits of batching things up, as you've said.  I have seen
performance gains with it set at 100ms and a 3-6 siblings with 8
backends running, but I haven't been able to extensively tune these
values, they were mostly random guesses that seemed to work.  My
cycles of performance testing take a while, at least a day or two per
change being tested, and the differences can even then be hard to see
due to variability in the testing load (as it's not really a static
test load, but a window on reality).  On top of that, with the
time it takes, I've succumbed more than once to the temptation of
tweaking more than one thing per performance run, which really muddies
the results.

> Increasing shared_buffers seems to always help, even out to half of the dev> box's ram (2G).
Half should be a pessimal setting. It means virtually everything is bufferedtwice. Once in the kernel and once in Postgres. Effectively halving yourmemory. If that's really helping try raising it even further, to something
like 90% of your memory. But the conventional dogma is that shared buffersshould never be more than about 10k no matter how much memory you have. Letthe kernel do the bulk of the buffering.That said it's even more mysterious in your situation. Why would a large
shared buffers help insert-only performance much at all? My guess is that it'sletting the vacuums complete quicker. Perhaps try raising work_mem?
I find it odd as well.  After reading the standard advice on
shared_buffers, I had only intended on raising it slightly.  But
seeing ever-increasing performance gains, I just kept tuning it upwards
all the way to the 2G limit, and saw noticeable gains every time. 
During at least some of the test cycles, there was no deleting or
vacuuming going on, just insert traffic.  I guessed that
shared_buffers' caching strategy must have been much more effective
than the OS cache at something or other, but I don't know what
exactly.  The only important read traffic that comes to mind is
the index which is both being constantly updated and constantly checked
for primary key uniqueness violations.

All of these replies here on the list (and a private one or two) have
given me a long list of things to try, and I feel confident that at
least some of them will gain me enough performance to comfortably
deploy this application in the end on somewhat reasonable
hardware.  Thanks to everyone here on the list for all the
suggestions, it has been very helpful in giving me directions to go
with this that I hadn't thought of before.

When I finally get all of this sorted out and working reasonably
optimally, I'll be sure to come back and report what
techniques/settings did and didn't work for this workload.

-- Brandon

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

[Brandon Black]

On 9/12/05, Christopher Petrilli <[EMAIL PROTECTED]> wrote:
2) Tune ext3.  The default configuration wrecks high-write situations. Look into data="" for mounting, turning off atime (I hopeyou've done this already) updates, and also modifying the scheduler to
the elevator model.  This is poorly documented in Linux (like justabout everything), but it's crtical.
I'm using noatime and data="" already.  I changed my
scheduler from the default anticipatory to deadline and saw an
improvement, but I haven't yet delved into playing with specific
elevator tunable values per-device.
3) Use 8.1 and strongly look at Bizgres. The data partitioning is critical.

I've just started down the path of getting 8.1 running with a larger
block size, and tommorow I'm going to look at Bizgres's partitioning as
opposed to some manual schemes.  Will the current Bizgres have a
lot of the performance enhancements of 8.1 already (or will 8.1 or 8.2
eventually get Bizgres's partitioning?)?
-- Brandon

Re: [PERFORM] Postgresql Hardware - Recommendations

[Christian.Kastner]

Andrew, Matthew, thanks to you both four your advice. I'm sorry I couldn't 
provide more details to the situation, I will post again as soon I get them. 

Time to share your insights with the colleagues :)

Best Regards,
Chris

-Ursprüngliche Nachricht-
Von: Paul Ramsey [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 06. September 2005 06:13
An: Kastner Christian; Kastner Christian
Betreff: Re: [PERFORM] Postgresql Hardware - Recommendations

For a database, I would almost always prioritize:
- I/O
- RAM
- CPU

So, fast drives (SCSI 1RPM or better in a RAID configuration,  
more spindles == more throughput), then memory (more memory == more  
of the database off disk in cache == faster response), then more CPU  
(more concurrent request handling).

Paul

On 5-Sep-05, at 6:50 AM, <[EMAIL PROTECTED]>  
<[EMAIL PROTECTED]> wrote:

> Hello,
>
> My company has decided to migrate our Oracle database to  
> postgresql8. We
> will aquire a new server for this, and would very much appreciate your
> advice.
>
> NOTE: The applications accessing the database are developed and
> maintained externally, and unfortunately, the developers have not yet
> given us detailed information on their requirements. The only info  
> I can
> give so far is that the database size is about 60GB, and that it  
> will be
> frequently accessed by multiple users (about 100 will be connected
> during business hours). The applications accessing the database are
> mostly reporting tools.
>
> I know that the performance question will ultimately boil down to "it
> depends what you want to do with it", but at the moment I'm very much
> interested if there are any general issues we should look out for.
>
> The questions we are asking us now are:
>
> 1) Intel or AMD (or alternate Platform)
> Are we better of with Xeons or Opterons? Should we consider the IBM
> OpenPower platform?
>
> 2) CPUs vs cache
> Would you rather have more CPUs or more cache? Eg: 4x Xeon 1MB vs 2x
> Xeon 8MB
>
> 3) CPUs vs Memory
> Would you rather have 4x CPUs and 8GB of memory, or 2x CPUs with  
> 16GB of
> memory?
>
> Thanks in advance for all your replies!
>
> Best Regards,
> Christian Kastner
>
> ---(end of  
> broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>


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

Re: [PERFORM] Performance considerations for very heavy INSERT

[Ron Peacetree]

>From: Brandon Black <[EMAIL PROTECTED]>
>Sent: Sep 12, 2005 5:04 PM
>To: pgsql-performance@postgresql.org
>Subject: [PERFORM] Performance considerations for very heavy INSERT traffic

>I'm in the process of developing an application which uses PostgreSQL for 
>data storage. Our database traffic is very atypical, and as a result it has 
>been rather challenging to figure out how to best tune PostgreSQL on what 
>development hardware we have, as well as to figure out exactly what we 
>should be evaluating and eventually buying for production hardware.

>The vast, overwhelming majority of our database traffic is pretty much a 
>non-stop stream of INSERTs filling up tables. It is akin to data 
>acquisition. Several thousand clients are sending once-per-minute updates 
>full of timestamped numerical data at our central server, which in turn 
>performs INSERTs into several distinct tables as part of the transaction for 
>that client. We're talking on the order of ~100 transactions per second, 
>each containing INSERTs to multiple tables (which contain only integer and 
>floating point columns and a timestamp column - the primary key (and only 
>index) is on a unique integer ID for the client and the timestamp). The 
>transaction load is spread evenly over time by having the clients send their 
>per-minute updates at random times rather than on the exact minute mark.

I have built two such systems.  TBF, neither used PostgreSQL.  OTOH, the 
principles are the same.

One perhaps non-obvious point: You definitely are going to want a way to adjust 
exactly when a specific client is sending its approximately once-per-minute 
update via functionality similar to adjtime().  Such functionality will be 
needed to smooth the traffic across the clients as much as possible over the 1 
minute polling period given the real-world vagracies of WAN connections.

Put your current active data acquisition table on its own RAID 10 array, and 
keep it _very_ small and simple in structure (see below for a specific 
suggestion regarding this).  If you must have indexes for this table, put them 
on a _different_ array.  Basically, you are going to treat the active table 
like you would an log file: you want as little HD head movement as possible so 
appending to the table is effectively sequential IO.

As in a log file, you will get better performance if you batch your updates to 
HD into reasonably sized chunks rather than writing to HD every INSERT.

The actual log file will have to be treated in the same way to avoid it 
becoming a performance bottleneck.


>There will of course be users using a web-based GUI to extract data from 
>these tables and display them in graphs and whatnot, but the SELECT query 
>traffic will always be considerably less frequent and intensive than the 
>incessant INSERTs, and it's not that big a deal if the large queries take a 
>little while to run.

More details here would be helpful.


>This data also expires - rows with timestamps older than X days will be 
>DELETEd periodically (once an hour or faster), such that the tables will 
>reach a relatively stable size (pg_autovacuum is handling vacuuming for now, 
>but considering our case, we're thinking of killing pg_autovacuum in favor 
>of having the periodic DELETE process also do a vacuum of affected tables 
>right after the DELETE, and then have it vacuum the other low traffic tables 
>once a day while it's at it).

Design Idea: split the data into tables where _at most_ the tables are of the 
size that all the data in the table expires at the same time and DROP the 
entire table rather than scanning a big table for deletes at the same time you 
want to do inserts to said.  Another appropriate size for these tables may be 
related to the chunk you want to write INSERTS to HD in.  

This will also have the happy side effect of breaking the data into smaller 
chunks that are more likely to be cached in their entirety when used. 


>There is an aggregation layer in place which proxies the inbound data from 
>the clients into a small(er) number of persistent postgresql backend 
>processes. Right now we're doing one aggregator per 128 clients (so instead 
>of 128 seperate database connections over the course of a minute for a small 
>transaction each, there is a single database backend that is constantly 
>committing transactions at a rate of ~ 2/second). At a test load of ~1,000 
>clients, we would have 8 aggregators running and 8 postgresql backends. 
>Testing has seemed to indicate we should aggregate even harder - the planned 
>production load is ~5,000 clients initially, but will grow to almost double 
>that in the not-too-distant future, and that would mean ~40 backends at 128 
>clients each initially. Even on 8 cpus, I'm betting 40 concurrent backends 
>doing 2 tps is much worse off than 10 backends doing 8 tps.

Experience has taught me that the above is not likely to be the proper 
architecture for this kind of application.

The best exact approac