[PERFORM] Filesystem

[Martin Fandel]

Hi @ all,

i have only a little question. Which filesystem is preferred for 
postgresql? I'm plan to use xfs (before i used reiserfs). The reason
is the xfs_freeze Tool to make filesystem-snapshots. 

Is the performance better than reiserfs, is it reliable?

best regards,
Martin


---(end of broadcast)---
TIP 3: 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] Adaptec/LSI/?? RAID

[Cosimo Streppone]

Stacy White wrote:


We're in the process of buying another Opteron server to run Postgres, and
based on the suggestions in this list I've asked our IT director to get an
LSI MegaRaid controller rather than one of the Adaptecs.

But when we tried to place our order, our vendor (Penguin Computing) advised
"we find LSI does not work well with 4GB of RAM. Our engineering find that
LSI card could cause system crashes. One of our customer ... has found that
Adaptec cards works well on PostGres SQL


Probably, your vendor is trying to avoid problems at all, but
"one of our customers" is not a pretty general case, and
"we find LSI does not work well", but is there a documented reason?

Anyway, my personal experience has been with an Acer Altos R701 + S300
external storage unit, equipped with LSI Logic Megaraid U320 aka
AMI Megaraid aka LSI Elite 1600
(honestly, these cards come with zillions of names and subnames, that
I don't know exactly how to call them).

This system was configured in various ways. The final layout is
3 x RAID1 arrays (each of 2 disks) and 1 x RAID10 array (12 disks).
This configuration is only available when you use 2 LSI cards (one
for each S300 scsi bus).

The system behaves pretty well, with a sustained sequential write rate
of 80Mb/s, and more importantly, a quite high load in our environment
of 10 oltp transactions per second, without any problems and
`cat /proc/loadavg` < 1.

I don't like the raid configuration system of LSI, that is
counter-intuitive for raid 10 arrays. It got me 4 hours and
a tech support call to figure out how to do it right.

Also, I think LSI cards don't behave well with particular
raid configurations, like RAID 0 with 4 disks, or RAID 10
with also 4 disks. It seemed that these configurations put
the controller under heavy load, thus behaving unreasonably
worse than, for example, 6-disks-RAID0 or 6-disks-RAID1.
Sorry, I can't be more "scientific" on this.

For Adaptec, I don't have any direct experience.

--
Cosimo


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] How to avoid database bloat

[Mindaugas Riauba]

> >>AFAICT the vacuum is doing what it is supposed to, and the problem has
> >>to be just that it's not being done often enough.  Which suggests either
> >>an autovacuum bug or your autovacuum settings aren't aggressive enough.
> >
> >  -D -d 1 -v 1000 -V 0.5 -a 1000 -A 0.1 -s 10
> >
> >  That is autovacuum settings. Should be aggressive enough I think?
>
> Might e aggressive enough, but might not.  I have seen some people run
> -V 0.1.  Also you probably don't need -A that low.  This could an issue
> where analyze results in an inaccurate reltuples value which is
> preventing autovacuum from doing it's job.  Could you please run it with
> -d 2 and show us the relevant log output.

  Relevant parts are below. And we had to set so aggressive analyze because
otherwise planer statistics were getting old too fast. As I said table has
very
high turnover most of the records live here only for a few seconds.

  And one more question - anyway why table keeps growing? It is shown that
it occupies
<1 pages and max_fsm_pages = 20 so vacuum should keep up with the
changes?
Or is it too low according to pg_class system table? What should be the
reasonable value?

select sum(relpages) from pg_class;
  sum
---
 77994
(1 row)

  Thanks,

  Mindaugas

[2005-06-03 09:30:31 EEST] DEBUG:   Performing: ANALYZE "queue"
[2005-06-03 09:30:31 EEST] INFO:  table name: database."queue"
[2005-06-03 09:30:31 EEST] INFO: relid: 465440;   relisshared: 0
[2005-06-03 09:30:31 EEST] INFO: reltuples: 98615.00;  relpages:
6447
[2005-06-03 09:30:31 EEST] INFO: curr_analyze_count: 39475111;
curr_vacuum_count: 30
953987
[2005-06-03 09:30:31 EEST] INFO: last_analyze_count: 39475111;
last_vacuum_count: 30
913733
[2005-06-03 09:30:31 EEST] INFO: analyze_threshold: 10861;
vacuum_threshold: 43700

[2005-06-03 09:31:11 EEST] DEBUG:   Performing: VACUUM ANALYZE "queue"
[2005-06-03 09:31:12 EEST] INFO:  table name: database."queue"
[2005-06-03 09:31:12 EEST] INFO: relid: 465440;   relisshared: 0
[2005-06-03 09:31:12 EEST] INFO: reltuples: 99355.00;  relpages:
6447
[2005-06-03 09:31:12 EEST] INFO: curr_analyze_count: 39480332;
curr_vacuum_count: 30
957872
[2005-06-03 09:31:12 EEST] INFO: last_analyze_count: 39480332;
last_vacuum_count: 30
957872
[2005-06-03 09:31:12 EEST] INFO: analyze_threshold: 10935;
vacuum_threshold: 50677



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

Re: SHMMAX / SHMALL Was (Re: [PERFORM] postgresql-8.0.1 performance tuning)

[Martin Fandel]

Aah ok :) 

I've set my values now as follow (2GB RAM):

SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
$1*1024/3}'`
echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf

sysctl.conf:
kernel.shmmax=708329472
kernel.shmall=44270592

postgresql.conf:
max_connections=500
shared_buffers=4 # ~312MB, min. 1000, max ~ 83000

best regards,
Martin


Am Freitag, den 03.06.2005, 18:11 +1200 schrieb Mark Kirkwood:
> Paul McGarry wrote:
> 
> > Based on the powerpostgresql.com Performance Checklist [1]  and
> > Annotated Postgresql.conf [2] I understand that:
> > -I should have less than 1/3 of my total memory as shared_buffers
> > -For my server 15000 is a fairly reasonable starting point for
> > shared_buffers which is ~120MB
> > -I have 100 max_connections.
> > 
> > So I was going to set SHMMAX to 134217728 (ie 128 Meg)
> > 
> > What should SHMALL be?
> > 
> > The current system values are
> > [EMAIL PROTECTED]:~/data$ cat /proc/sys/kernel/shmmax
> > 33554432
> > [EMAIL PROTECTED]:~/data$ cat /proc/sys/kernel/shmall
> > 2097152
> > 
> > ie SHMALL seems to be 1/16 of SHMMAX
> > 
> 
> No - shmall is in 4k pages _ so this amounts to 8G! This is fine - 
> unless you wish to decrease it in order to prevent too many shared 
> memory applications running.
> 
> BTW - the docs have been amended for 8.1 to suggest shmmax=134217728 and 
> shmall=2097152 (was going to point you at them - but I cannot find them 
> on the Postgresql site anymore...).
> 
> There seems to be some longstanding confusion in the Linux community 
> about the units for shmall (some incorrect documentation from Oracle on 
> the issue does not help I am sure) - to the point where I downloaded 
> kernel source to check (reproducing here):
> 
> 
> linux-2.6.11.1/include/linux/shm.h:13->
> 
> #define SHMMAX 0x200 /* max shared seg size (bytes) */
> #define SHMMIN 1 /* min shared seg size (bytes) */
> #define SHMMNI 4096  /* max num of segs system wide */
> #define SHMALL (SHMMAX/PAGE_SIZE*(SHMMNI/16)) /* max shm system wide 
> (pages) */
> #define SHMSEG SHMMNI
> 
> 
> Hope that helps
> 
> Best wishes
> 
> Mark


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

Re: SHMMAX / SHMALL Was (Re: [PERFORM] postgresql-8.0.1 performance

[Mark Kirkwood]

Martin Fandel wrote:
Aah ok :) 


I've set my values now as follow (2GB RAM):

SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
$1*1024/3}'`
echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf

sysctl.conf:
kernel.shmmax=708329472
kernel.shmall=44270592

postgresql.conf:
max_connections=500
shared_buffers=4 # ~312MB, min. 1000, max ~ 83000



Hmmm - shmall set to 168G... err why? Apologies for nit picking a little 
- but shmall seems unreasonably high. I can't see much reason for 
setting it bigger than (physical RAM in bytes)/4096 myself. So in your 
case this is 2*(1024*1024*1024)/4096 = 524288


Cheers

Mark


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: SHMMAX / SHMALL Was (Re: [PERFORM] postgresql-8.0.1 performance tuning)

[Martin Fandel]

ok i set it to 524288. ;)

Am Freitag, den 03.06.2005, 21:10 +1200 schrieb Mark Kirkwood:
> Martin Fandel wrote:
> > Aah ok :) 
> > 
> > I've set my values now as follow (2GB RAM):
> > 
> > SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
> > $1*1024/3}'`
> > echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
> > SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
> > echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf
> > 
> > sysctl.conf:
> > kernel.shmmax=708329472
> > kernel.shmall=44270592
> > 
> > postgresql.conf:
> > max_connections=500
> > shared_buffers=4 # ~312MB, min. 1000, max ~ 83000
> > 
> 
> Hmmm - shmall set to 168G... err why? Apologies for nit picking a little 
> - but shmall seems unreasonably high. I can't see much reason for 
> setting it bigger than (physical RAM in bytes)/4096 myself. So in your 
> case this is 2*(1024*1024*1024)/4096 = 524288
> 
> Cheers
> 
> Mark
> 


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

   http://archives.postgresql.org

Re: [PERFORM] Filesystem

[Mark Kirkwood]

Martin Fandel wrote:

Hi @ all,

i have only a little question. Which filesystem is preferred for 
postgresql? I'm plan to use xfs (before i used reiserfs). The reason
is the xfs_freeze Tool to make filesystem-snapshots. 


Is the performance better than reiserfs, is it reliable?



I used postgresql with xfs on mandrake 9.0/9.1 a while ago - 
reliability was great, performance seemed better than ext3. I didn't 
compare with reiserfs - the only time I have ever lost data from a Linux 
box has been when I used reiserfs, hence I am not a fan :-(


best wishes

Mark

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

Re: [PERFORM] Query plan for very large number of joins

[philb]

>>> I am using PostgreSQL (7.4) with a schema that was generated
>>> automatically (using hibernate). The schema consists of about 650
>>> relations. One particular query (also generated automatically)
>>> consists of left joining approximately 350 tables.

[snip]

>One thought is that I am not sure I believe the conclusion that planning
>is taking only 36 ms; even realizing that the exclusive use of left
>joins eliminates options for join order, there are still quite a lot of
>plans to consider.  You should try both EXPLAIN and EXPLAIN ANALYZE
>from psql and see how long each takes.  It'd also be interesting to keep
>an eye on how large the backend process grows while doing this --- maybe
>it's being driven into swap.


Thanks for the suggestion. I've timed both the EXPLAIN and the EXPLAIN ANALYZE 
operations. 
Both operations took 1m 37s. The analyze output indicates that the query 
execution time was 950ms. This doesn't square with the JDBC prepareStatement 
executing in 36ms. My guess is that the prepare was actually a no-op but 
I haven't found anything about this yet. 

So, is it correct to interpret this as the query planner taking an awful long 
time? Is it possible to force the query planner to adopt a specific strategy 
and not search for alternatives (I'm aware of the noXX options, it's the 
reverse logic that I'm thinking of here). Alternatively, is there some way 
to check if the query planner is bottlenecking on a specific resource? 

Finally, PFC was asking about the nature of the application, it's not a 
specific application just a generic bit of infrastructure consisting of 
a transformation of the UBL schema. Despite being fairly restricted in scope, 
the schema is highly denormalized hence the large number of tables. 

Thanks for all your help.
 -phil







I'm using Vodafone Mail - to get your free mobile email account go to 
http://www.vodafone.ie
Use of Vodafone Mail is subject to Terms and Conditions  
http://www.vodafone.ie/terms/website



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

   http://archives.postgresql.org

Re: [PERFORM] Query plan for very large number of joins

[Tom Lane]

<[EMAIL PROTECTED]> writes:
> Thanks for the suggestion. I've timed both the EXPLAIN and the EXPLAIN 
> ANALYZE operations. 
> Both operations took 1m 37s. The analyze output indicates that the query 
> execution time was 950ms. This doesn't square with the JDBC prepareStatement 
> executing in 36ms. My guess is that the prepare was actually a no-op but 
> I haven't found anything about this yet. 

Only in very recent JDBCs does prepareStatement do much of anything.

> So, is it correct to interpret this as the query planner taking an
> awful long time?

Looks that way.

> Is it possible to force the query planner to adopt a specific strategy 
> and not search for alternatives (I'm aware of the noXX options, it's the 
> reverse logic that I'm thinking of here).

There's no positive forcing method.  But you could probably save some
time by disabling both mergejoin and hashjoin, now that you know it's
going to end up picking nestloop for each join anyway.  Even more
important: are you sure that *every* one of the joins is a LEFT JOIN?
Even a couple of regular joins will let it fool around choosing
different join orders.

> Alternatively, is there some way to check if the query planner is
> bottlenecking on a specific resource?

I think it would be interesting to try profiling it.  I'm not really
expecting to find anything easily-fixable, but you never know.  From
what you said before, the database is not all that large --- would
you be willing to send me a database dump and the text of the query
off-list?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Filesystem

[Alex Turner]

We have been using XFS for about 6 months now and it has even tolerated
a controller card crash.  So far we have mostly good things to
report about XFS.  I benchmarked raw throughputs at various stripe
sizes, and XFS came out on top for us against reiser and ext3.  I
also used it because of it's supposed good support for large files,
which was verified somewhat by the benchmarks.

I have noticed a problem though - if you have 80 files in a
directory, it seems that XFS chokes on simple operations like 'ls' or
'chmod -R ...' where ext3 doesn't, don't know about reiser, I went
straight back to default after that problem (that partition is not on a
DB server though).

Alex Turner
netEconomistOn 6/3/05, Martin Fandel <[EMAIL PROTECTED]> wrote:
Hi @ all,i have only a little question. Which filesystem is preferred forpostgresql? I'm plan to use xfs (before i used reiserfs). The reasonis the xfs_freeze Tool to make filesystem-snapshots.Is the performance better than reiserfs, is it reliable?
best regards,Martin---(end of broadcast)---TIP 3: 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] Query limitations (size, number of UNIONs ...)

[Stephen Frost]

* Marc Mamin ([EMAIL PROTECTED]) wrote:
> I've just made a first test wich resulted in a query being 15KB big annd
> containing 63 UNION.

If the data is distinct from each other or you don't mind duplicate
records you might try using 'union all' instead of 'union'.  Just a
thought.

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] Filesystem

[Martin Fandel]

Hi

i have tested a xfs+LVM installation with the scalix (HP OpenMail)
Mailserver (it's a little time ago). I had at that time some problems 
using xfs_freeze. I used a script for freezing the fs and making storing
the snapshots. Sometimes the complete Server hangs (no blinking cursor,
no possible logins, no network). I don't know if it was a hardware
problem or if it was the xfs-software. I installed/compiled the newest 
kernel for this system (i think it was a 2.6.9) to check out if it's 
maybe a kernel-problem. But on the next days, the system hangs
again. After that i used reiserfs again. 

I tested it with Suse Linux Enterprise Server 8.

Has someone heared about such problems? That is the only reason that
i have a bit fear to use xfs for a critical database :/. 

Best regards,
Martin
 
Am Freitag, den 03.06.2005, 09:18 -0400 schrieb Alex Turner:
> We have been using XFS for about 6 months now and it has even
> tolerated a controller card crash.  So far we have mostly good things
> to report about XFS.  I benchmarked raw throughputs at various stripe
> sizes, and XFS came out on top for us against reiser and ext3.  I also
> used it because of it's supposed good support for large files, which
> was verified somewhat by the benchmarks.
> 
> I have noticed a problem though - if you have 80 files in a
> directory, it seems that XFS chokes on simple operations like 'ls' or
> 'chmod -R ...' where ext3 doesn't, don't know about reiser, I went
> straight back to default after that problem (that partition is not on
> a DB server though).
> 
> Alex Turner
> netEconomist
> 
> On 6/3/05, Martin Fandel <[EMAIL PROTECTED]> wrote:
> Hi @ all,
> 
> i have only a little question. Which filesystem is preferred
> for
> postgresql? I'm plan to use xfs (before i used reiserfs). The
> reason
> is the xfs_freeze Tool to make filesystem-snapshots.
> 
> Is the performance better than reiserfs, is it reliable? 
> 
> best regards,
> Martin
> 
> 
> ---(end of
> broadcast)---
> TIP 3: 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
> 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Query plan for very large number of joins

[philb]

Anyone following this thread might be interested to know that disabling 
the merge and hash joins (as suggested below) resulted in the execution 
time dropping from ~90 seconds to ~35 seconds. Disabling GEQO has brought 
about a marginal reduction (~1 second, pretty much within the the margin 
of error)

Tom, a quick grep indicates that all of the joins are left joins so there's no 
scope for tweaking there. I'll send you the schema + query offlist, anyone 
else curious about it, let me know. 

Thanks again, 
 -phil



><[EMAIL PROTECTED]> writes:
>> Thanks for the suggestion. I've timed both the EXPLAIN and the EXPLAIN 
ANALYZE operations.
>> Both operations took 1m 37s. The analyze output indicates that the query
>> execution time was 950ms. This doesn't square with the JDBC prepareStatement
>> executing in 36ms. My guess is that the prepare was actually a no-op 
but
>> I haven't found anything about this yet.
>
>Only in very recent JDBCs does prepareStatement do much of anything.
>
>> So, is it correct to interpret this as the query planner taking an
>> awful long time?
>
>Looks that way.
>
>> Is it possible to force the query planner to adopt a specific strategy
>> and not search for alternatives (I'm aware of the noXX options, it's 
the
>> reverse logic that I'm thinking of here).
>
>There's no positive forcing method.  But you could probably save some
>time by disabling both mergejoin and hashjoin, now that you know it's
>going to end up picking nestloop for each join anyway.  Even more
>important: are you sure that *every* one of the joins is a LEFT JOIN?
>Even a couple of regular joins will let it fool around choosing
>different join orders.
>
>> Alternatively, is there some way to check if the query planner is
>> bottlenecking on a specific resource?
>
>I think it would be interesting to try profiling it.  I'm not really
>expecting to find anything easily-fixable, but you never know.  From
>what you said before, the database is not all that large --- would
>you be willing to send me a database dump and the text of the query
>off-list?



I'm using Vodafone Mail - to get your free mobile email account go to 
http://www.vodafone.ie
Use of Vodafone Mail is subject to Terms and Conditions  
http://www.vodafone.ie/terms/website



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] BUG #1697: Select getting slower on continously updating data

[Bruno Wolff III]

On Fri, Jun 03, 2005 at 00:09:00 -0700,
  Bahadur Singh <[EMAIL PROTECTED]> wrote:
> 
> Many thanks for this tip !
> But is this good idea to analyse/vacuuming the
> database tables while updates are taking place..
> Since, I update continuously say (100,000 ) times or
> more the same data set.
> 
> This is the result of analyze command.
> 
> INFO:  analyzing "public.salesarticle"
> INFO:  "salesarticle": scanned 3000 of 20850 pages,
> containing 62 live rows and 134938 dead rows; 62 rows
> in sample, 431 estimated total rows
> 
> Gesamtlaufzeit der Abfrage: 5531 ms.
> Total Time Taken : 5531 ms.
> 
> Can you suggest me some clever way to so, because I
> would prefer to do vaccumming while database is not
> loaded with queries/transactions.

While that may be a nice preference, under your usage pattern that does
not appear to be a good idea. As long as your disk I/O isn't saturated
you want to be running vacuums a lot more often than you are. (Analyze should
only be needed if the distrution of values is changing constantly. An example
would be timestamps indicating when an update occured.)

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

Re: [PERFORM] How to avoid database bloat

[Matthew T. O'Connor]

Mindaugas Riauba wrote:


Might e aggressive enough, but might not.  I have seen some people run
-V 0.1.  Also you probably don't need -A that low.  This could an issue
where analyze results in an inaccurate reltuples value which is
preventing autovacuum from doing it's job.  Could you please run it with
-d 2 and show us the relevant log output.
   



 Relevant parts are below. And we had to set so aggressive analyze because
otherwise planer statistics were getting old too fast. As I said table has
very
high turnover most of the records live here only for a few seconds.
 



Looked like pg_autovacuum is operating as expected.  One of the annoying 
limitations of pg_autovacuum in current releases is that you can't set 
thresholds on a per table basis.  It looks like this table might require 
an even more aggressive vacuum threshold.  Couple of thoughts, are you 
sure it's the table that is growing and not the indexes? (assuming this 
table has indexes on it). 


 And one more question - anyway why table keeps growing? It is shown that
it occupies
<1 pages and max_fsm_pages = 20 so vacuum should keep up with the
changes?
Or is it too low according to pg_class system table? What should be the
reasonable value?
 



Does the table keep growing?  Or does it grow to a point an then stop 
growing?  It's normal for a table to operate at a steady state size that 
is bigger that it's fresly "vacuum full"'d size.  And with -V set at 0.5 
it should be at a minimum 50% larger than it's minimum size.  Your email 
before said that this table went from 20M to 70M but does it keep 
going?  Perhaps it would start leveling off at this point, or some point 
shortly there-after.


Anyway, I'm not sure if there is something else going on here, but from 
the log it looks as though pg_autovacuum is working as advertised. 



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Filesystem

[J. Andrew Rogers]

On Fri, 3 Jun 2005 09:06:41 +0200
 "Martin Fandel" <[EMAIL PROTECTED]> wrote:
i have only a little question. Which filesystem is 
preferred for postgresql? I'm plan to use xfs

(before i used reiserfs). The reason
is the xfs_freeze Tool to make filesystem-snapshots. 



XFS has worked great for us, and has been both reliable 
and fast.  Zero problems and currently our standard server 
filesystem.  Reiser, on the other hand, has on rare 
occasion eaten itself on the few systems where someone was 
running a Reiser partition, though none were running 
Postgres at the time.  We have deprecated the use of 
Reiser on all systems where it is not already running.


In terms of performance for Postgres, the rumor is that 
XFS and JFS are at the top of the heap, definitely better 
than ext3 and somewhat better than Reiser.  I've never 
used JFS, but I've seen a few benchmarks that suggest it 
is at least as fast as XFS for Postgres.


Since XFS is more mature than JFS on Linux, I go with XFS 
by default.  If some tragically bad problems develop with 
XFS I may reconsider that position, but we've been very 
happy with it so far.  YMMV.


cheers,

J. Andrew Rogers

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

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

Re: [PERFORM] Query plan for very large number of joins

[Tom Lane]

<[EMAIL PROTECTED]> writes:
> I've attached the schema and query text, hopefully it will be of some use 
> to you. Note that both are taken from the HyperUBL project
> (https://hyperubl.dev.java.net/).  Sadly, at this stage I think it's
> time for me to try alternatives to either Hibernate or Postgresql. 

Thanks.  Profiling on 7.4 I get this for an EXPLAIN (after vacuum
analyzing the database):

  %   cumulative   self  self total   
 time   seconds   secondscalls  Ks/call  Ks/call  name
 61.66618.81   618.81 2244505819 0.00 0.00  compare_path_costs
 15.01769.44   150.63  1204882 0.00 0.00  add_path
  8.08850.5781.13   772077 0.00 0.00  nth
  3.76888.2737.70  1113598 0.00 0.00  nconc
  2.59914.3026.03   233051 0.00 0.00  find_joininfo_node
  2.23936.7022.40 30659124 0.00 0.00  bms_equal
  1.14948.1411.44 39823463 0.00 0.00  equal
  0.77955.84 7.7083300 0.00 0.00  find_base_rel

This is with no special planner settings.  Obviously the problem is that
it's considering way too many different paths.  We did do something
about that in 8.0 (basically, throw away paths with "nearly the same"
cost) ... but the bottom line didn't improve a whole lot.  CVS tip
profile for the same case is

  %   cumulative   self  self total   
 time   seconds   secondscalls   s/call   s/call  name
 38.37176.41   176.41 53344348 0.00 0.00  list_nth_cell
 35.26338.52   162.11   196481 0.00 0.00  
get_rte_attribute_is_dropped
  5.42363.4424.92   233051 0.00 0.00  find_joininfo_node
  4.72385.1421.70 30659416 0.00 0.00  bms_equal
  4.09403.9518.81 53344348 0.00 0.00  list_nth
  2.31414.5810.63 37347920 0.00 0.00  equal
  1.40421.03 6.4583299 0.00 0.00  find_base_rel
  1.08426.01 4.98   617917 0.00 0.00  SearchCatCache
  0.90430.13 4.12  5771640 0.00 0.00  AllocSetAlloc

The get_rte_attribute_is_dropped calls (and list_nth/list_nth_cell,
which are mostly being called from there) arise from a rather hastily
added patch that prevents failure when a JOIN list in a stored view
refers to a since-dropped column of an underlying relation.  I had not
realized that that check could have O(N^2) behavior in deeply nested
joins, but it does.  Obviously we'll have to rethink that.

After that it looks like the next hotspot is find_joininfo_node
(and bms_equal which is mostly getting called from there).  We could
maybe fix that by rethinking the joininfo data structure --- right now
it's a collection of simple Lists, which betrays the planner's Lispy
heritage ;-).  Again, that's not something I've ever seen at the top
of a profile before --- there may be some O(N^2) behavior involved
here too, but I've not analyzed it in detail.

It does look like 8.0 would be about a factor of 2 faster for you
than 7.4, but the real fix will probably have to wait for 8.1.

Also: the 8.0 problem is definitely an O(N^2) type of deal, which means
if you could reduce the depth of nesting by a factor of 2 the cost would
go down 4x.  You said this was an automatically generated query, so
there may not be much you can do about it, but if you could parenthesize
the FROM list a bit more intelligently the problem would virtually go
away.  What you have is effectively

FROM a left join b) left join c) left join d) 

so the nesting goes all the way down.  With something like

FROM ((a left join b) left join c ...)
 left join
 ((d left join e) left join f ...)

the max nesting depth would be halved.  I don't understand your schema
at all so I'm not sure what an appropriate nesting might look like, but
maybe there is a short-term workaround to be found there.  (This will
*not* help on 7.4, as the bottleneck there is completely different.)

regards, tom lane

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

[PERFORM] Postgresql and Software RAID/LVM

[Steve Poe]

I have a small business client that cannot afford high-end/high quality
RAID cards for their next server. That's a seperate argument/issue right
there for me, but what the client wants is what the client wants.

Has anyone ran Postgres with software RAID or LVM on a production box?
What have been your experience?

I don't forsee more 10-15 concurrent sessions running for an their OLTP
application.

Thanks.

Steve Poe


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

   http://archives.postgresql.org

Re: [PERFORM] Postgresql and Software RAID/LVM

[Joshua D. Drake]

Steve Poe wrote:

I have a small business client that cannot afford high-end/high quality
RAID cards for their next server. That's a seperate argument/issue right
there for me, but what the client wants is what the client wants.

Has anyone ran Postgres with software RAID or LVM on a production box?
What have been your experience?


I would not run RAID + LVM in a software scenario. Software RAID is fine 
however.


Sincerely,

Joshua D. Drake




I don't forsee more 10-15 concurrent sessions running for an their OLTP
application.

Thanks.

Steve Poe


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

   http://archives.postgresql.org



--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

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

  http://archives.postgresql.org

[PERFORM] Insert slow down on empty database

[Morgan]

Hi,

I am having a problem with inserting  a large amount of data with my libpqxx
program into an initially empty database. It appears to be the EXACT same
problem discussed here:

http://archives.postgresql.org/pgsql-bugs/2005-03/msg00183.php

In fact my situation is nearly identical, with roughly 5 major tables, with
foreign keys between each other. All the tables are being loaded into
similtaneously with about 2-3 million rows each. It seems that the problem
is caused by the fact that I am using prepared statments, that cause the
query planner to choose sequential scans for the foreign key checks due to
the table being initially empty.  As with the post above, if I dump my
connection after about 4000 inserts, and restablish it the inserts speed up
by a couple of orders of magnitude and remain realtively constant through
the whole insertion.

At first I was using straight insert statments, and although they were a bit
slower than the prepared statments(after the restablished connection) they
never ran into this problem with the database being initially empty. I only
changed to the prepared statements because it was suggested in the
documentation for advice on bulk data loads =).

I can work around this problem, and I am sure somebody is working on fixing
this, but I thought it might be good to reaffirm the problem.

Thanks,
Morgan Kita



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Forcing use of specific index

[William Yu]

A pretty awful way is to mangle the sql statement so the other field 
logical statements are like so:


select * from mytable where 0+field = 100




Tobias Brox wrote:

Is it any way to attempt to force the planner to use some specific index
while creating the plan?  Other than eventually dropping all the other
indices (which is obiously not a solution in production setting anyway)?

I have one case where I have added 16 indices to a table, many of them
beeing partial indices.  The table itself has only 50k of rows, but are
frequently used in heavy joins.  I imagine there can be exponential order on
the number of alternative paths the planner must examinate as function of
the number of indices?

It seems to me that the planner is quite often not choosing the "best"
index, so I wonder if there is any easy way for me to check out what the
planner think about a specific index :-)



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Query plan for very large number of joins

[Simon Riggs]

On Fri, 2005-06-03 at 13:22 +0100, [EMAIL PROTECTED] wrote:
> 
> >>> I am using PostgreSQL (7.4) with a schema that was generated
> >>> automatically (using hibernate). The schema consists of about 650
> >>> relations. One particular query (also generated automatically)
> >>> consists of left joining approximately 350 tables.

> Despite being fairly restricted in scope, 
> the schema is highly denormalized hence the large number of tables. 

Do you mean normalized? Or do you mean you've pushed the superclass
details down onto each of the leaf classes?

I guess I'm interested in what type of modelling led you to have so many
tables in the first place?

Gotta say, never seen 350 table join before in a real app. 

Wouldn't it be possible to smooth out the model and end up with less
tables? Or simply break things up somewhere slightly down from the root
of the class hierarchy?

Best Regards, Simon Riggs




---(end of broadcast)---
TIP 3: 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] Insert slow down on empty database

[Christopher Browne]

In an attempt to throw the authorities off his trail, "Morgan" <[EMAIL 
PROTECTED]> transmitted:
> At first I was using straight insert statments, and although they
> were a bit slower than the prepared statments(after the restablished
> connection) they never ran into this problem with the database being
> initially empty. I only changed to the prepared statements because
> it was suggested in the documentation for advice on bulk data loads
> =).

I remember encountering this with Oracle, and the answer being "do
some loading 'til it slows down, then update statistics and restart."

I don't know that there's an obvious alternative outside of perhaps
some variation on pg_autovacuum...
-- 
If this was helpful,  rate me
http://linuxdatabases.info/info/spreadsheets.html
So long and thanks for all the fish.

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Forcing use of specific index

[Junaili Lie]

HI all,
I also would like to know if there is a way to force a use of a
specific index for a specific query. I am currently using Postgresql
7.4.6

In my case I have a relatively big table (several millions of records)
that are frequently used to join with other tables (explicit join or
through view).
The table has several indices, some are single column and some are multi column.
Some queries are faster if using single colum index while other are
faster using multi column indexes.
I have play around with SET STATISTICS, but it doesn't seem to make
any differences (I tried to set it to 1000 one time, but still the
same). I did analyze and vacuum after SET STATISTICS.
Any pointer on how to do this is greatly appreciated.
Thank you in advance,


J



On 6/1/05, Tobias Brox <[EMAIL PROTECTED]> wrote:
> Is it any way to attempt to force the planner to use some specific index
> while creating the plan?  Other than eventually dropping all the other
> indices (which is obiously not a solution in production setting anyway)?
> 
> I have one case where I have added 16 indices to a table, many of them
> beeing partial indices.  The table itself has only 50k of rows, but are
> frequently used in heavy joins.  I imagine there can be exponential order on
> the number of alternative paths the planner must examinate as function of
> the number of indices?
> 
> It seems to me that the planner is quite often not choosing the "best"
> index, so I wonder if there is any easy way for me to check out what the
> planner think about a specific index :-)
> 
> --
> Tobias Brox, Beijing
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>

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

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