Re: [PERFORM] Query planner issue

2006-01-30 Thread Tom Lane
Mark Lewis <[EMAIL PROTECTED]> writes: > You really, really want to upgrade as soon as possible, No, sooner than that. Show your boss the list of known data-loss-causing bugs in 7.2.1, and refuse to take responsibility if the database eats all your data before the "in good time" upgrade. The rel

Re: [PERFORM] Where is my bottleneck?

2006-01-30 Thread Richard Huxton
Arnau Rebassa Villalonga wrote: The configuration of postgresql is the default, I tried to tune the postgresql.conf and the results where disappointing, so I left again the default values. That's the first thing to fix. Go to the page below and read through the "Performance Tuning" article

Re: [PERFORM] Query planner issue

2006-01-30 Thread Emmanuel Lacour
On Mon, Jan 30, 2006 at 03:26:23PM -0800, Mark Lewis wrote: > You have lots of dead rows. Do a vacuum full to get it under control, > then run VACUUM more frequently and/or increase your FSM settings to > keep dead rows in check. In 7.2 vacuum is pretty intrusive; it will be > much better behaved

Re: [PERFORM] Query planner issue

2006-01-30 Thread Jim Buttafuoco
with Postgresql 7.2.1 you will need to do BOTH vacuum and reindex and with a table that gets many updates/deletes, you should run vacuum more than daily. Both issues have been solved in 8.1. Jim -- Original Message --- From: Emmanuel Lacour <[EMAIL PROTECTED]> To: pgsql-perfo

Re: [PERFORM] Query planner issue

2006-01-30 Thread Mark Lewis
You have lots of dead rows. Do a vacuum full to get it under control, then run VACUUM more frequently and/or increase your FSM settings to keep dead rows in check. In 7.2 vacuum is pretty intrusive; it will be much better behaved once you can upgrade to a more recent version. You really, really

[PERFORM] Query planner issue

2006-01-30 Thread Emmanuel Lacour
Hi everybody, I have the following problem, on a test server, if I do a fresh import of production data then run 'explain analyze select count(*) from mandats;' I get this result: Aggregate (cost=6487.32..6487.32 rows=1 width=0) (actual time=607.61..607.61 rows=1 loops=1) -> Seq Scan on ma

Re: [PERFORM] Huge Data sets, simple queries

2006-01-30 Thread Jim C. Nasby
On Fri, Jan 27, 2006 at 07:05:04PM -0800, Luke Lonergan wrote: > Sounds like you are running into the limits of your disk subsystem. You are > scanning all of the data in the transactions table, so you will be limited > by the disk bandwidth you have ? and using RAID-10, you should divide the > nu

Re: [PERFORM] Where is my bottleneck?

2006-01-30 Thread Jim C. Nasby
On Tue, Jan 24, 2006 at 07:40:22PM +0100, Arnau Rebassa Villalonga wrote: > Hi all, > > I have a performance problem and I don't know where is my bottleneck. > I have postgresql 7.4.2 running on a debian server with kernel You should really upgrade to the latest 7.4 version. You're probably vul

Re: [PERFORM] Huge Data sets, simple queries

2006-01-30 Thread Luke Lonergan
Depesz, On 1/30/06 9:53 AM, "hubert depesz lubaczewski" <[EMAIL PROTECTED]> wrote: >> double the performance on a reasonable number of drives. > > how many is reasonable? What I mean by that is: given a set of disks N, the read performance of RAID will be equal to the drive read rate A times th

Re: [PERFORM] Huge Data sets, simple queries

2006-01-30 Thread hubert depesz lubaczewski
On 1/29/06, Luke Lonergan <[EMAIL PROTECTED]> wrote: > Oh - and about RAID 10 - for large data work it's more often a waste of > disk performance-wise compared to RAID 5 these days. RAID5 will almost > double the performance on a reasonable number of drives. how many is reasonable? depesz -

Re: [PERFORM] Where is my bottleneck?

2006-01-30 Thread Luke Lonergan
Arnau, On 1/24/06 10:40 AM, "Arnau Rebassa Villalonga" <[EMAIL PROTECTED]> wrote: > I know it's a problem with a very big scope, but could you give me a > hint about where I should look to? Try this: time bash -c "dd if=/dev/zero of=bigfile bs=8k count=200 && sync" time dd if=bigfile o

Re: [PERFORM] Where is my bottleneck?

2006-01-30 Thread Michael Stone
On Tue, Jan 24, 2006 at 07:40:22PM +0100, Arnau Rebassa Villalonga wrote: I have a performance problem and I don't know where is my bottleneck. [snip] Most of the time the idle value is even higher than 60%. It's generally a fairly safe bet that if you are running slow and your cpu is id