Re: [PERFORM] Oddly slow queries

[PFC]

On Wed, 16 Apr 2008 06:07:04 +0200, Thomas Spreng <[EMAIL PROTECTED]> wrote:



On 16.04.2008, at 01:24, PFC wrote:


The queries in question (select's) occasionally take up to 5 mins even  
if they take ~2-3 sec under "normal" conditions, there are no  
sequencial scans done in those queries. There are not many users  
connected (around 3, maybe) to this database usually since it's still  
in a testing phase. I tried to hunt down the problem by playing around  
with resource usage cfg options but it didn't really made a difference.


Could that be caused by a CHECKPOINT ?



actually there are a few log (around 12 per day) entries concerning  
checkpoints:


LOG:  checkpoints are occurring too frequently (10 seconds apart)
HINT:  Consider increasing the configuration parameter  
"checkpoint_segments".


But wouldn't that only affect write performance? The main problems I'm  
concerned about affect SELECT queries.


	OK, so if you get 12 of those per day, this means your checkpoint  
interval isn't set to 10 seconds... I hope...
	Those probably correspond to some large update or insert query that comes  
from a cron or archive job ?... or a developer doing tests or filling a  
table...


	So, if it is checkpointing every 10 seconds it means you have a pretty  
high write load at that time ; and having to checkpoint and flush the  
dirty pages makes it worse, so it is possible that your disk(s) choke on  
writes, also killing the selects in the process.


-> Set your checkpoint log segments to a much higher value
	-> Set your checkpoint timeout to a higher value (5 minutes or  
something), to be tuned afterwards
	-> Tune bgwriter settings to taste (this means you need a realistic load,  
not a test load)

-> Use separate disk(s) for the xlog
-> For the love of God, don't keep the RAID5 for production !
(RAID5 + 1 small write = N reads + N writes, N=3 in your case)
	Since this is a test server I would suggest RAID1 for the OS and database  
files and the third disk for the xlog, if it dies you just recreate the  
DB...


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] SQL Function Slowness, 8.3.0

[Tom Lane]

"Gavin M. Roy" <[EMAIL PROTECTED]> writes:
> In 8.3.0, I'm seeing some oddities with SQL functions which I thought were
> immune to the planner data restrictions of plpgsql functions and the sort.

Without a specific example this discussion is pretty content-free, but
in general SQL functions face the same hazards of bad parameterized
plans as plpgsql functions do.

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Background writer underemphasized ...

[Marinos Yannikos]

Hi,

to save some people a headache or two: I believe we just solved our 
performance problem in the following scenario:


- Linux 2.6.24.4
- lots of RAM (32GB)
- enough CPU power (4 cores)
- disks with relatively slow random writes (SATA RAID-5 / 7 disks, 128K 
stripe, ext2)


Our database is around 86GB, the busy parts being 20-30GB. Typical load 
is regular reads of all sizes (large joins, sequential scans on a 8GB 
table, many small selects with few rows) interspersed with writes of 
several 1000s rows on the busier tables by several clients.


After many tests and research revolving around the Linux I/O-Schedulers 
(which still have some issues one should be wary about: 
http://lwn.net/Articles/216853/) because we saw problems when occasional 
(intensive) writes completely starved all I/O, we discovered that 
changing the default settings for the background writer seems to have 
solved all these problems. Performance is much better now with fsync on 
than it was with fsync off previously, no other configuration options 
had a noticeable effect on performance (or these problems rather).


This helped with our configuration:
bgwriter_delay = 1ms # 10-1ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round

Previously, our typical writes resulted in around 5-10MB/s going to disk 
and some reads stalling, now we are seeing typical disk I/O in the 
30-60MB/s range with write load present and no noticeable problems with 
reads except when autovacuum's "analyze" is running. Other options we 
have tried/used were shared_buffers between 200MB and 20GB, wal_buffers 
= 256MB, wal_writer_delay=5000ms ...


So, using this is highly recommended and I would say that the 
documentation does not do it justice... (and yes, I could have figured 
it out earlier)


-mjy

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] SQL Function Slowness, 8.3.0

[Gavin M. Roy]

In 8.3.0, I'm seeing some oddities with SQL functions which I thought were
immune to the planner data restrictions of plpgsql functions and the sort.
 Basically I have a query which executes in 5ms but when wrapped in a SQL
function, takes 500ms.  I've checked all the types passed in to make sure
they match so there is no type conversions taking place in execution.
I'm curious about the validity of my expectation that functions created with
SQL as the language should be as fast as the straight SQL counterpart.  I've
previously not run into such an order of magnitude difference in using SQL
functions.  Is this a change of behavior in 8.3 from 8.2?  Without specific
examples, are there any recommendations on how to speed up these functions?

Thanks,

Gavin

Re: [PERFORM] SQL Function Slowness, 8.3.0

[Mark Mielke]

Are you going to post the function? :-)

My PL/PGSQL functions are running fine in 8.3.x.

Cheers,
mark


Gavin M. Roy wrote:
In 8.3.0, I'm seeing some oddities with SQL functions which I thought 
were immune to the planner data restrictions of plpgsql functions and 
the sort.  Basically I have a query which executes in 5ms but when 
wrapped in a SQL function, takes 500ms.  I've checked all the types 
passed in to make sure they match so there is no type conversions 
taking place in execution.


I'm curious about the validity of my expectation that functions 
created with SQL as the language should be as fast as the straight SQL 
counterpart.  I've previously not run into such an order of magnitude 
difference in using SQL functions.  Is this a change of behavior in 
8.3 from 8.2?  Without specific examples, are there any 
recommendations on how to speed up these functions?


Thanks,

Gavin




--
Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] Oddly slow queries

[Chris Browne]

[EMAIL PROTECTED] (Thomas Spreng) writes:
> On 16.04.2008, at 01:24, PFC wrote:
>>
>>> The queries in question (select's) occasionally take up to 5 mins
>>> even if they take ~2-3 sec under "normal" conditions, there are no
>>> sequencial scans done in those queries. There are not many users
>>> connected (around 3, maybe) to this database usually since it's
>>> still in a testing phase. I tried to hunt down the problem by
>>> playing around with resource usage cfg options but it didn't really
>>> made a difference.
>>
>>  Could that be caused by a CHECKPOINT ?
>
> actually there are a few log (around 12 per day) entries concerning
> checkpoints:
>
> LOG:  checkpoints are occurring too frequently (10 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
>
> But wouldn't that only affect write performance? The main problems I'm
> concerned about affect SELECT queries.

No, that will certainly NOT just affect write performance; if the
postmaster is busy writing out checkpoints, that will block SELECT
queries that are accessing whatever is being checkpointed.

When we were on 7.4, we would *frequently* see SELECT queries that
should be running Very Quick that would get blocked by the checkpoint
flush.

We'd periodically see hordes of queries of the form:

  select id from some_table where unique_field = 'somevalue.something';

which would normally run in less than 1ms running for (say) 2s.

And the logs would show something looking rather like the following:

2008-04-03 09:01:52 LOG select id from some_table where unique_field = 
'somevalue.something'; - 952ms
2008-04-03 09:01:52 LOG select id from some_table where unique_field = 
'somevalue.something'; - 742ms
2008-04-03 09:01:52 LOG select id from some_table where unique_field = 
'another.something'; - 1341ms
2008-04-03 09:01:52 LOG select id from some_table where unique_field = 
'somevalue.something'; - 911ms
2008-04-03 09:01:52 LOG select id from some_table where unique_field = 
'another.something'; - 1244ms
2008-04-03 09:01:52 LOG select id from some_table where unique_field = 
'another.something'; - 2311ms
2008-04-03 09:01:52 LOG select id from some_table where unique_field = 
'another.something'; - 1799ms
2008-04-03 09:01:52 LOG select id from some_table where unique_field = 
'somevalue.something'; - 1992ms

This was happening because the checkpoint was flushing those two
tuples, and hence blocking 8 SELECTs that came in during the flush.

There are two things worth considering:

1.  If the checkpoints are taking place "too frequently," then that is
clear evidence that something is taking place that is injecting REALLY
heavy update load on your database at those times.

If the postmaster is checkpointing every 10s, that implies Rather
Heavy Load, so it is pretty well guaranteed that performance of other
activity will suck at least somewhat because this load is sucking up
all the I/O bandwidth that it can.

So, to a degree, there may be little to be done to improve on this.

2.  On the other hand, if you're on 8.1 or so, you may be able to
configure the Background Writer to incrementally flush checkpoint data
earlier, and avoid the condition of 1.

Mind you, you'd have to set BgWr to be pretty aggressive, based on the
"10s periodicity" that you describe; that may not be a nice
configuration to have all the time :-(.
-- 
output = reverse("ofni.sesabatadxunil" "@" "enworbbc")
http://cbbrowne.com/info/multiplexor.html
Nagging  is the  repetition  of unpalatable  truths. --Baroness  Edith
Summerskill

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Background writer underemphasized ...

[Chris Browne]

[EMAIL PROTECTED] (Marinos Yannikos) writes:
> This helped with our configuration:
> bgwriter_delay = 1ms # 10-1ms between rounds
> bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round

FYI, I'd be inclined to reduce both of those numbers, as it should
reduce the variability of behaviour.

Rather than cleaning 1K pages every 10s, I would rather clean 100
pages every 1s, as that will have much the same effect, but spread the
work more evenly.  Or perhaps 10 pages every 100ms...

Cut the delay *too* low and this might make the background writer, in
effect, poll *too* often, and start chewing resources, but there's
doubtless some "sweet spot" in between...
-- 
"cbbrowne","@","cbbrowne.com"
http://linuxdatabases.info/info/oses.html
"For systems, the analogue of a face-lift is to add to the control
graph an edge that creates a cycle, not just an additional node."
-- Alan J. Perlis

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Background writer underemphasized ...

[Bill Moran]

In response to Marinos Yannikos <[EMAIL PROTECTED]>:

> Hi,
> 
> to save some people a headache or two: I believe we just solved our 
> performance problem in the following scenario:
> 
> - Linux 2.6.24.4
> - lots of RAM (32GB)
> - enough CPU power (4 cores)
> - disks with relatively slow random writes (SATA RAID-5 / 7 disks, 128K 
> stripe, ext2)
> 
> Our database is around 86GB, the busy parts being 20-30GB. Typical load 
> is regular reads of all sizes (large joins, sequential scans on a 8GB 
> table, many small selects with few rows) interspersed with writes of 
> several 1000s rows on the busier tables by several clients.
> 
> After many tests and research revolving around the Linux I/O-Schedulers 
> (which still have some issues one should be wary about: 
> http://lwn.net/Articles/216853/) because we saw problems when occasional 
> (intensive) writes completely starved all I/O, we discovered that 
> changing the default settings for the background writer seems to have 
> solved all these problems. Performance is much better now with fsync on 
> than it was with fsync off previously, no other configuration options 
> had a noticeable effect on performance (or these problems rather).
> 
> This helped with our configuration:
> bgwriter_delay = 1ms # 10-1ms between rounds
> bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round

What other values have you tried for this?  Have you watched closely
under load to ensure that you're not seeing a huge performance hit
every 10s when the bgwriter kicks off?

I'm with Chris -- I would be inclined to try a range of values to find
a sweet spot, and I would be _very_ shocked to find that sweet spot
at the values you mention.  However, if that really is the demonstrable
sweet spot, there may be something we all can learn.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

[EMAIL PROTECTED]
Phone: 412-422-3463x4023

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] SQL Function Slowness, 8.3.0

[Gavin M. Roy]

After detailed examination of pg_stat_user_indexes usage, it's clear that
the functions don't use the same indexes.  I've casted everything to match
the indexes in the SQL function, to no success.  Any suggestions on next
steps?  Maybe for 8.4 we could find a way to explain analyze function
internals ;-)
Gavin

On Wed, Apr 16, 2008 at 11:09 AM, Tom Lane <[EMAIL PROTECTED]> wrote:

> "Gavin M. Roy" <[EMAIL PROTECTED]> writes:
> > In 8.3.0, I'm seeing some oddities with SQL functions which I thought
> were
> > immune to the planner data restrictions of plpgsql functions and the
> sort.
>
> Without a specific example this discussion is pretty content-free, but
> in general SQL functions face the same hazards of bad parameterized
> plans as plpgsql functions do.
>
>regards, tom lane
>

Re: [PERFORM] SQL Function Slowness, 8.3.0

[Joshua D. Drake]

On Wed, 16 Apr 2008 14:44:40 -0400
"Gavin M. Roy" <[EMAIL PROTECTED]> wrote:

> After detailed examination of pg_stat_user_indexes usage, it's clear
> that the functions don't use the same indexes.  I've casted
> everything to match the indexes in the SQL function, to no success.
> Any suggestions on next steps?  Maybe for 8.4 we could find a way to
> explain analyze function internals ;-)
> Gavin

To quote Tom in the appropriate bottom posting method:

> >
> > Without a specific example this discussion is pretty content-free,
> > but in general SQL functions face the same hazards of bad
> > parameterized plans as plpgsql functions do.
> >
> >regards, tom lane
> >

Sincerely,

Joshua D. Drake


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Background writer underemphasized ...

[Greg Smith]

On Wed, 16 Apr 2008, Marinos Yannikos wrote:

to save some people a headache or two: I believe we just solved our 
performance problem in the following scenario:


I was about to ask your PostgreSQL version but since I see you mention 
wal_writer_delay it must be 8.3.  Knowing your settings for shared_buffers 
and checkpoint_segments in particular would make this easier to 
understand.


You also didn't mention what disk controller you have, or how much write 
cache it has (if any).



This helped with our configuration:
bgwriter_delay = 1ms # 10-1ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round


The default for bgwriter_delay is 200ms = 5 passes/second.  You're 
increasing that to 1ms means one pass every 10 seconds instead. 
That's almost turning the background writer off.  If that's what improved 
your situation, you might as well as turn it off altogether by setting all 
the bgwriter_lru_maxpages parameters to be 0.  The combination you 
describe here, running very infrequently but with lru_maxpages set to its 
maximum, is a bit odd.


Other options we have tried/used were shared_buffers between 200MB and 
20GB, wal_buffers = 256MB, wal_writer_delay=5000ms ...


The useful range for wal_buffers tops at around 1MB, so no need to get 
extreme there.  wal_writer_delay shouldn't matter here unless you turned 
on asyncronous commit.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Background writer underemphasized ...

[Greg Smith]

On Wed, 16 Apr 2008, Bill Moran wrote:


bgwriter_delay = 1ms # 10-1ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round
Have you watched closely under load to ensure that you're not seeing a 
huge performance hit every 10s when the bgwriter kicks off?


bgwriter_lru_maxpages = 1000 means that any background writer pass can 
write at most 1000 pages = 8MB.  Those are buffered writes going into the 
OS cache, which it will write out at its own pace later.  That isn't going 
to cause a performance hit when it happens.


That isn't the real mystery though--where's the RAID5 rant I was expecting 
from you?


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Background writer underemphasized ...

[Bill Moran]

In response to Greg Smith <[EMAIL PROTECTED]>:

> On Wed, 16 Apr 2008, Bill Moran wrote:
> 
> >> bgwriter_delay = 1ms # 10-1ms between rounds
> >> bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round
> > Have you watched closely under load to ensure that you're not seeing a 
> > huge performance hit every 10s when the bgwriter kicks off?
> 
> bgwriter_lru_maxpages = 1000 means that any background writer pass can 
> write at most 1000 pages = 8MB.  Those are buffered writes going into the 
> OS cache, which it will write out at its own pace later.  That isn't going 
> to cause a performance hit when it happens.
> 
> That isn't the real mystery though--where's the RAID5 rant I was expecting 
> from you?

Oh crap ... he _is_ using RAID-5!  I completely missed an opportunity to
rant!

blah blah blah ... RAID-5 == evile, etc ...

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

[EMAIL PROTECTED]
Phone: 412-422-3463x4023

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Anybody using the Dell Powervault MD3000 array?

[Jeffrey Baker]

Thinking about buying the Powervault MD3000 SAS array with 15 15k
300GB disks for use as a postgres tablespace.  Is anyone using these
(or other LSI/Engenio rebadge jobs?).  I'm interested in hearing about
performance of the array, and problems (if any) with Dell's SAS HBA
that comes bundled.  Also interested in performance of the maximum
config of an MD3000 with two MD1000 shelves.

-Jeff

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Anybody using the Dell Powervault MD3000 array?

[Gavin M. Roy]

Might want to check out the HP MSA70 arrays.  I've had better luck with them
and you can get 25 drives in a smaller rack unit size.  I had a bad
experience with the MD3000 and now only buy MD1000's with Perc 6/e when I
buy Dell.
Good luck!

On Wed, Apr 16, 2008 at 4:15 PM, Jeffrey Baker <[EMAIL PROTECTED]> wrote:

> Thinking about buying the Powervault MD3000 SAS array with 15 15k
> 300GB disks for use as a postgres tablespace.  Is anyone using these
> (or other LSI/Engenio rebadge jobs?).  I'm interested in hearing about
> performance of the array, and problems (if any) with Dell's SAS HBA
> that comes bundled.  Also interested in performance of the maximum
> config of an MD3000 with two MD1000 shelves.
>
> -Jeff
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] Anybody using the Dell Powervault MD3000 array?

[Joshua D. Drake]

On Wed, 16 Apr 2008 16:17:10 -0400

> On Wed, Apr 16, 2008 at 4:15 PM, Jeffrey Baker <[EMAIL PROTECTED]>
> wrote:
> 
> > Thinking about buying the Powervault MD3000 SAS array with 15 15k
> > 300GB disks for use as a postgres tablespace.  Is anyone using these
> > (or other LSI/Engenio rebadge jobs?).  I'm interested in hearing
> > about performance of the array, and problems (if any) with Dell's
> > SAS HBA that comes bundled.  Also interested in performance of the
> > maximum config of an MD3000 with two MD1000 shelves.
> >
> > -Jeff



"Gavin M. Roy" <[EMAIL PROTECTED]> wrote:

> Might want to check out the HP MSA70 arrays.  I've had better luck
> with them and you can get 25 drives in a smaller rack unit size.  I
> had a bad experience with the MD3000 and now only buy MD1000's with
> Perc 6/e when I buy Dell.
> Good luck!
> 

I can second this. The MSA 70 is a great unit for the money.

Joshua D. Drake

-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] SQL Function Slowness, 8.3.0

[Tom Lane]

"Gavin M. Roy" <[EMAIL PROTECTED]> writes:
> After detailed examination of pg_stat_user_indexes usage, it's clear that
> the functions don't use the same indexes.  I've casted everything to match
> the indexes in the SQL function, to no success.  Any suggestions on next
> steps?  Maybe for 8.4 we could find a way to explain analyze function
> internals ;-)

Yeah, this could be easier, but it's certainly possible to examine the
plan generated for a function's parameterized statement.  For instance,
say you're wondering about the plan for

create function foo(int, text) ... as
$$ select * from bar where f1 = $1 and f2 = $2 $$
language sql

What you do is

prepare p(int, text) as select * from bar where f1 = $1 and f2 = $2 ;

explain analyze execute p(42, 'hello world');

It works exactly the same for statements in plpgsql functions,
remembering that both parameters and local variables of the function
have to become $n placeholders.  Remember to make the parameters
of the prepared statement have the same declared types as the
function's parameters and variables.

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Anybody using the Dell Powervault MD3000 array?

[Jeffrey Baker]

On Wed, Apr 16, 2008 at 1:20 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
> On Wed, 16 Apr 2008 16:17:10 -0400
>
>
>  > On Wed, Apr 16, 2008 at 4:15 PM, Jeffrey Baker <[EMAIL PROTECTED]>
>  > wrote:
>  >
>  > > Thinking about buying the Powervault MD3000 SAS array with 15 15k
>  > > 300GB disks for use as a postgres tablespace.  Is anyone using these
>  > > (or other LSI/Engenio rebadge jobs?).  I'm interested in hearing
>  > > about performance of the array, and problems (if any) with Dell's
>  > > SAS HBA that comes bundled.  Also interested in performance of the
>  > > maximum config of an MD3000 with two MD1000 shelves.
>  > >
>  > > -Jeff
>
>  
>  "Gavin M. Roy" <[EMAIL PROTECTED]> wrote:
>
>  > Might want to check out the HP MSA70 arrays.  I've had better luck
>  > with them and you can get 25 drives in a smaller rack unit size.  I
>  > had a bad experience with the MD3000 and now only buy MD1000's with
>  > Perc 6/e when I buy Dell.
>  > Good luck!
>  >
>
>  I can second this. The MSA 70 is a great unit for the money.

Thank you both.  The MSA 70 looks like an ordinary disk shelf.  What
controllers do you use?  Or, do you just go with a software RAID?

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Anybody using the Dell Powervault MD3000 array?

[Joshua D. Drake]

On Wed, 16 Apr 2008 13:37:32 -0700
"Jeffrey Baker" <[EMAIL PROTECTED]> wrote:

> >  I can second this. The MSA 70 is a great unit for the money.
> 
> Thank you both.  The MSA 70 looks like an ordinary disk shelf.  What
> controllers do you use?  Or, do you just go with a software RAID?
> 

P800, from HP.

Joshua D. Drake

-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Anybody using the Dell Powervault MD3000 array?

[Gavin M. Roy]

On Wed, Apr 16, 2008 at 4:39 PM, Joshua D. Drake <[EMAIL PROTECTED]>
wrote:

> On Wed, 16 Apr 2008 13:37:32 -0700
> "Jeffrey Baker" <[EMAIL PROTECTED]> wrote:
>
> > >  I can second this. The MSA 70 is a great unit for the money.
> >
> > Thank you both.  The MSA 70 looks like an ordinary disk shelf.  What
> > controllers do you use?  Or, do you just go with a software RAID?
> >
>
> P800, from HP.


In a Dell box I use a Perc 6/E with a SAS to Mini SAS cable.

Gavin

[PERFORM] Query running slow

[samantha mahindrakar]

Hi
Iam finding the following query is working a bit slow:
EXECUTE '(SELECT ARRAY(SELECT DISTINCT date_part(''day'', measurement_start)
FROM ' || gettablestring(dates)|| '
WHERE lane_id IN (' || lanesidarr || ')))'
INTO temparr;

This function is trying to find all the days in a prticular month
whihc has data for the particular lane and put it in an array...which
can be used later.
gettablestring(dates) returns the partition name from which the data
needs to be extracted. These partitions have index on the
measurement_start field.
lanesidarr is a lane number. The partition has an index on this field to.
Could anyone give me some hints???/

Thanks
Sam

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Oddly slow queries

[Thomas Spreng]

On 16.04.2008, at 17:42, Chris Browne wrote:

[EMAIL PROTECTED] (Thomas Spreng) writes:

On 16.04.2008, at 01:24, PFC wrote:



The queries in question (select's) occasionally take up to 5 mins
even if they take ~2-3 sec under "normal" conditions, there are no
sequencial scans done in those queries. There are not many users
connected (around 3, maybe) to this database usually since it's
still in a testing phase. I tried to hunt down the problem by
playing around with resource usage cfg options but it didn't really
made a difference.


Could that be caused by a CHECKPOINT ?


actually there are a few log (around 12 per day) entries concerning
checkpoints:

LOG:  checkpoints are occurring too frequently (10 seconds apart)
HINT:  Consider increasing the configuration parameter
"checkpoint_segments".

But wouldn't that only affect write performance? The main problems  
I'm

concerned about affect SELECT queries.


No, that will certainly NOT just affect write performance; if the
postmaster is busy writing out checkpoints, that will block SELECT
queries that are accessing whatever is being checkpointed.


What I meant is if there are no INSERT's or UPDATE's going on it  
shouldn't

affect SELECT queries, or am I wrong?

All the data modification tasks usually run at night, during the day  
there

shouldn't be many INSERT's or UPDATE's going on.


When we were on 7.4, we would *frequently* see SELECT queries that
should be running Very Quick that would get blocked by the checkpoint
flush.


How did you actually see they were blocked by the checkpoint flushes?
Do they show up as separate processes?


There are two things worth considering:

1.  If the checkpoints are taking place "too frequently," then that is
clear evidence that something is taking place that is injecting REALLY
heavy update load on your database at those times.

If the postmaster is checkpointing every 10s, that implies Rather
Heavy Load, so it is pretty well guaranteed that performance of other
activity will suck at least somewhat because this load is sucking up
all the I/O bandwidth that it can.

So, to a degree, there may be little to be done to improve on this.


I strongly assume that those log entries showed up at night when the
heavy insert routines are being run. I'm more concerned about the query
performance under "normal" conditions when there are very few  
modifications

done.


2.  On the other hand, if you're on 8.1 or so, you may be able to
configure the Background Writer to incrementally flush checkpoint data
earlier, and avoid the condition of 1.

Mind you, you'd have to set BgWr to be pretty aggressive, based on the
"10s periodicity" that you describe; that may not be a nice
configuration to have all the time :-(.


I've just seen that the daily vacuum tasks didn't run, apparently. The  
DB

has almost doubled it's size since some days ago. I guess I'll have to
VACUUM FULL (dump/restore might be faster, though) and check if that  
helps

anything.

Does a bloated DB affect the performance alot or does it only use up  
disk

space?

Thanks for all the hints/help so far from both of you.

Cheers,

Tom

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Query running slow

[PFC]

On Wed, 16 Apr 2008 23:14:11 +0200, samantha mahindrakar  
<[EMAIL PROTECTED]> wrote:



Hi
Iam finding the following query is working a bit slow:
EXECUTE '(SELECT ARRAY(SELECT DISTINCT date_part(''day'',  
measurement_start)

FROM ' || gettablestring(dates)|| '
WHERE lane_id IN (' || lanesidarr || ')))'
INTO temparr;

This function is trying to find all the days in a prticular month
whihc has data for the particular lane and put it in an array...which
can be used later.
gettablestring(dates) returns the partition name from which the data
needs to be extracted. These partitions have index on the
measurement_start field.
lanesidarr is a lane number. The partition has an index on this field to.
Could anyone give me some hints???/


	OK so I guess you have one partition per month since there is no month in  
your WHERE.
	If this is a table which hasn't got much write activity (probably the  
case for last month's partition, for instance), CLUSTER it on something  
appropriate that you use often in queries, like lane_id here.
	And you can use SELECT foo GROUP BY foo, this will use a hash, it is  
faster than a sort.

Example :

CREATE TABLE blop AS SELECT '2008-01-01'::TIMESTAMP + ((n%30)*'1  
DAY'::INTERVAL) AS t FROM generate_series(1,10) AS n;

ALTER TABLE blop ADD d DATE NULL;
UPDATE blop SET d=t;
VACUUM FULL ANALYZE blop;

-- Now blop contains 100K timestamps and 100K dates from the month 2008-01

EXPLAIN ANALYZE SELECT DISTINCT EXTRACT( DAY from t )  FROM blop;
   QUERY PLAN
-
 Unique  (cost=10051.82..10551.82 rows=30 width=8) (actual  
time=221.740..289.801 rows=30 loops=1)
   ->  Sort  (cost=10051.82..10301.82 rows=10 width=8) (actual  
time=221.737..250.911 rows=10 loops=1)

 Sort Key: (date_part('day'::text, t))
 Sort Method:  quicksort  Memory: 5955kB
 ->  Seq Scan on blop  (cost=0.00..1747.00 rows=10 width=8)  
(actual time=0.021..115.254 rows=10 loops=1)

 Total runtime: 290.237 ms
(6 lignes)

Temps : 290,768 ms

EXPLAIN ANALYZE SELECT EXTRACT( DAY from t ) AS day FROM blop GROUP BY day;
QUERY PLAN
---
 HashAggregate  (cost=1997.00..1997.38 rows=30 width=8) (actual  
time=198.375..198.390 rows=30 loops=1)
   ->  Seq Scan on blop  (cost=0.00..1747.00 rows=10 width=8) (actual  
time=0.021..129.779 rows=10 loops=1)

 Total runtime: 198.437 ms
(3 lignes)

Temps : 198,894 ms

==> Hash is faster than Sort

EXPLAIN ANALYZE SELECT d FROM blop GROUP BY d;
QUERY PLAN
--
 HashAggregate  (cost=1747.00..1747.30 rows=30 width=4) (actual  
time=101.829..101.842 rows=30 loops=1)
   ->  Seq Scan on blop  (cost=0.00..1497.00 rows=10 width=4) (actual  
time=0.012..33.428 rows=10 loops=1)

 Total runtime: 101.905 ms
(3 lignes)

Temps : 102,516 ms

==> Not computing the EXTRACT is faster obviously

(actually EXPLAIN ANALYZE adds some overhead, the query really takes 60 ms)


If you have an index lane_id, measurement_date, you can always do :

for day in 1..31:
find 1 row with which has this day
reutrn the days you found











--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Anybody using the Dell Powervault MD3000 array?

[Craig James]

Gavin M. Roy wrote:
On Wed, Apr 16, 2008 at 4:39 PM, Joshua D. Drake <[EMAIL PROTECTED] 
> wrote:


On Wed, 16 Apr 2008 13:37:32 -0700
"Jeffrey Baker" <[EMAIL PROTECTED] > wrote:

 > >  I can second this. The MSA 70 is a great unit for the money.
 >
 > Thank you both.  The MSA 70 looks like an ordinary disk shelf.  What
 > controllers do you use?  Or, do you just go with a software RAID?
 >

P800, from HP.


In a Dell box I use a Perc 6/E with a SAS to Mini SAS cable.


There was a fairly long recent thread discussing the Dell Perc 6 controller 
starting here:

 http://archives.postgresql.org/pgsql-performance/2008-03/msg00264.php

and one relevant follow-up regarding the MD1000 box:

 http://archives.postgresql.org/pgsql-performance/2008-03/msg00280.php

(Unfortunately, the Postgres web archive does a terrible job formatting 
plain-old-text messages, it doesn't seem to know that it should wrap 
paragraphs, so some of these are pretty hard to read as web pages.)

Craig

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Oddly slow queries

[Andrew Sullivan]

On Wed, Apr 16, 2008 at 11:48:21PM +0200, Thomas Spreng wrote:
> What I meant is if there are no INSERT's or UPDATE's going on it  
> shouldn't
> affect SELECT queries, or am I wrong?

CHECKPOINTs also happen on a time basis.  They should be short in that case,
but they still have to happen.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] db size

[Adrian Moisey]

Hi


Running VACUUM VERBOSE will give you a detailed view of space usage of
each individual table.


I did that.

Not too sure what I'm looking for, can someone tell me what this means:

INFO:  "blahxxx": scanned 27 of 27 pages, containing 1272 live rows and 
0 dead rows; 1272 rows in sample, 1272 estimated total rows

INFO:  free space map contains 4667977 pages in 1199 relations
DETAIL:  A total of 4505344 page slots are in use (including overhead).
4505344 page slots are required to track all free space.
Current limits are:  15537488 page slots, 1200 relations, using 91172 kB.



--
Adrian Moisey
Systems Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED]
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance