Re: [PERFORM] Monitoring buffercache...

2008-11-24 Thread Scott Marlowe
On Mon, Nov 24, 2008 at 9:40 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Mon, 24 Nov 2008, Scott Marlowe wrote: > >> My guess is that the period of time for which pg_buffercache takes locks >> on the buffer map are short enough that it isn't a real big deal on a fast >> enough server. > > As the

Re: [PERFORM] Monitoring buffercache...

2008-11-24 Thread Greg Smith
On Mon, 24 Nov 2008, Scott Marlowe wrote: My guess is that the period of time for which pg_buffercache takes locks on the buffer map are short enough that it isn't a real big deal on a fast enough server. As the server involved gets faster, the amount of time the locks are typically held for

Re: [PERFORM] Monitoring buffercache...

2008-11-24 Thread Greg Smith
On Mon, 24 Nov 2008, Kevin Kempter wrote: Currently I run this each 15min via cron: insert into buffercache_stats select now(), isdirty, count(*) as buffers, (count(*) * 8192) as memory from pg_buffercache group by 1,2; This query isn't going to save the information you need to figure out if

Re: [PERFORM] limit clause produces wrong query plan

2008-11-24 Thread Scott Carey
I believe his earlier (original) complaint was that it was slower with the LIMIT than with no limit. As in, the select (*) query was faster to get the whole thing than apply the limit. Wherever that is the case, it is broken. Certainly a complex join makes this more difficult, but one would ag

Re: [PERFORM] limit clause produces wrong query plan

2008-11-24 Thread PFC
SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET 100 LIMIT 100 I think pagination is overrated. If the query produces, for instance, something like 100 rows or less, more often than not, getting all the rows will take the exact same time as getting a portion of the rows... in

Re: [PERFORM] Increasing pattern index query speed

2008-11-24 Thread Scott Carey
Andrus, My first thought on the query where a pattern being faster than the query with an exact value is that the planner does not have good enough statistics on that column. Without looking at the explain plans further, I would suggest trying something simple. The fact that it is fasster on

Re: [PERFORM] limit clause produces wrong query plan

2008-11-24 Thread Chris
Andrus wrote: Scott, And how exactly should it be optimized? If a query is even moderately interesting, with a few joins and a where clause, postgresql HAS to create the rows that come before your offset in order to assure that it's giving you the right rows. SELECT ... FROM bigtable ORDER B

Re: [PERFORM] limit clause produces wrong query plan

2008-11-24 Thread Andrus
it was veery fast. To be honest I do not know what is happening?! This is really weird. It seems that PostgreSql OFFSET / LIMIT are not optimized and thus typical paging queries SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET pageno*100 LIMIT 100 or even first page query SELECT ... F

Re: [PERFORM] Monitoring buffercache...

2008-11-24 Thread Scott Marlowe
On Mon, Nov 24, 2008 at 12:52 PM, Brad Nicholson <[EMAIL PROTECTED]> wrote: >> I just ran it in a loop over and over on my 8 core opteron server and >> it ran the load factor up by almost exactly 1.0. Under our normal >> daily load, it sits at 1.9 to 2.5, and it climbed to 2.9 under the new >> loa

Re: [PERFORM] Hash join on int takes 8..114 seconds

2008-11-24 Thread Andrus
Tomas, OK, what was the number of unused pointer items in the VACUUM output? I posted it in this thread: VACUUM FULL ANALYZE VERBOSE; ... INFO: free space map contains 14353 pages in 314 relations DETAIL: A total of 2 page slots are in use (including overhead). 89664 page slots are requ

Re: [PERFORM] Increasing pattern index query speed

2008-11-24 Thread Andrus
Richard, These are the same but the times are different. I'd be very surprised if you can reproduce these times reliably. I re-tried today again and got same results: in production database pattern query is many times slower that equality query. toode and rid base contain only single produc

Re: [PERFORM] Perc 3 DC

2008-11-24 Thread PFC
Not yet no, but that's a good suggestion and I do intend to give it a whirl. I get about 27MB/s from raid 1 (10 is about the same) so hopefully I can up the throughput to the speed of about one disk with sw raid. FYI I get more than 200 MB/s out of a Linux Software RAID5 of 3 SATA drives (

Re: [PERFORM] limit clause produces wrong query plan

2008-11-24 Thread Andrus
Scott, And how exactly should it be optimized? If a query is even moderately interesting, with a few joins and a where clause, postgresql HAS to create the rows that come before your offset in order to assure that it's giving you the right rows. SELECT ... FROM bigtable ORDER BY intprimarykey

Re: [PERFORM] Monitoring buffercache...

2008-11-24 Thread Brad Nicholson
On Mon, 2008-11-24 at 12:46 -0700, Scott Marlowe wrote: > On Mon, Nov 24, 2008 at 11:43 AM, Kevin Kempter > <[EMAIL PROTECTED]> wrote: > > Hi All; > > > > I've installed pg_buffercache and I want to use it to help define the > > optimal > > shared_buffers size. > > > > Currently I run this each 15

Re: [PERFORM] Monitoring buffercache...

2008-11-24 Thread Scott Marlowe
On Mon, Nov 24, 2008 at 11:43 AM, Kevin Kempter <[EMAIL PROTECTED]> wrote: > Hi All; > > I've installed pg_buffercache and I want to use it to help define the optimal > shared_buffers size. > > Currently I run this each 15min via cron: > insert into buffercache_stats select now(), isdirty, count(*)

Re: [PERFORM] Monitoring buffercache...

2008-11-24 Thread Brad Nicholson
On Mon, 2008-11-24 at 11:43 -0700, Kevin Kempter wrote: > Hi All; > > I've installed pg_buffercache and I want to use it to help define the optimal > shared_buffers size. > > Currently I run this each 15min via cron: > insert into buffercache_stats select now(), isdirty, count(*) as buffers, >

Re: [PERFORM] limit clause produces wrong query plan

2008-11-24 Thread Scott Marlowe
On Mon, Nov 24, 2008 at 10:26 AM, Andrus <[EMAIL PROTECTED]> wrote: >> it was veery fast. To be honest I do not know what is happening?! > > This is really weird. > It seems that PostgreSql OFFSET / LIMIT are not optimized and thus typical > paging queries And how exactly should it be optimized?

[PERFORM] Monitoring buffercache...

2008-11-24 Thread Kevin Kempter
Hi All; I've installed pg_buffercache and I want to use it to help define the optimal shared_buffers size. Currently I run this each 15min via cron: insert into buffercache_stats select now(), isdirty, count(*) as buffers, (count(*) * 8192) as memory from pg_buffercache group by 1,2; and here

Re: [PERFORM] Perc 3 DC

2008-11-24 Thread Scott Marlowe
On Mon, Nov 24, 2008 at 8:41 AM, Simon Waters <[EMAIL PROTECTED]> wrote: > That said a couple of weeks back ours corrupted a volume on replacing a dead > hard disk, so I'm never touching these cheap and tacky LSI RAID cards ever > again. It is suppose to just start rebuilding the array when you in

Re: [PERFORM] Perc 3 DC

2008-11-24 Thread Simon Waters
On Monday 24 November 2008 14:49:17 Glyn Astill wrote: > --- On Mon, 24/11/08, Steve Clark <[EMAIL PROTECTED]> wrote: > > > Yeah the battery's on it, that and the 128Mb is > > > > really the only reason I thought I'd give it a whirl. > > > > > > Is the battery functioning? We found that the unit h

Re: [PERFORM] Perc 3 DC

2008-11-24 Thread Scott Marlowe
On Mon, Nov 24, 2008 at 8:06 AM, Glyn Astill <[EMAIL PROTECTED]> wrote: > --- Scott Marlowe <[EMAIL PROTECTED]> wrote: > >> > >> > Yeah the battery is on there, and in the BIOS it says it's >> "PRESENT" and the status is "GOOD". >> >> If I remember correctly, older LSI cards had pretty poor >> perf

Re: [PERFORM] Perc 3 DC

2008-11-24 Thread Glyn Astill
--- Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > > Yeah the battery is on there, and in the BIOS it says it's > "PRESENT" and the status is "GOOD". > > If I remember correctly, older LSI cards had pretty poor > performance > in RAID 1+0 (or any layered RAID really). Have you tried setting > up

Re: [PERFORM] Perc 3 DC

2008-11-24 Thread Scott Marlowe
On Mon, Nov 24, 2008 at 7:49 AM, Glyn Astill <[EMAIL PROTECTED]> wrote: > --- On Mon, 24/11/08, Steve Clark <[EMAIL PROTECTED]> wrote: > >> > Yeah the battery's on it, that and the 128Mb is >> really the only reason I thought I'd give it a whirl. >> > >> > >> Is the battery functioning? We found t

Re: [PERFORM] Perc 3 DC

2008-11-24 Thread Glyn Astill
--- On Mon, 24/11/08, Steve Clark <[EMAIL PROTECTED]> wrote: > > Yeah the battery's on it, that and the 128Mb is > really the only reason I thought I'd give it a whirl. > > > > > Is the battery functioning? We found that the unit had to > be on and charged before write back caching > would work

Re: [PERFORM] Hash join on int takes 8..114 seconds

2008-11-24 Thread tv
>> Given the fact that the performance issues are caused by bloated tables >> and / or slow I/O subsystem, moving to a similar system won't help I >> guess. > > I have ran VACUUM FULL ANALYZE VERBOSE > and set MAX_FSM_PAGES = 15 > > So there is no any bloat except pg_shdepend indexes which shou

Re: [PERFORM] Perc 3 DC

2008-11-24 Thread Steve Clark
Glyn Astill wrote: --- On Sat, 22/11/08, Scott Marlowe <[EMAIL PROTECTED]> wrote: You really have two choices. First is to try and use it as a plain SCSI card, maybe with caching turned on, and do the raid in software. Second is to cut it into pieces and make jewelry out of it. Haha, I'm

Re: [PERFORM] Hash join on int takes 8..114 seconds

2008-11-24 Thread Andrus
Tomas, Let's suppose you set a reasonable value (say 8096) instead of 2GB. That gives about 160MB. Anyway this depends - if you have a lot of slow queries caused by on-disk sorts / hash tables, use a higher value. Otherwise leave it as it is. Probably product orders table is frequently joined

Re: [PERFORM] Increasing pattern index query speed

2008-11-24 Thread Richard Huxton
Andrus wrote: > Both queries return same result (19) and return same data. > Pattern query is a much slower (93 sec) than equality check (13 sec). > How to fix this ? > Using 8.1.4, utf-8 encoding, et-EE locale They're different queries. The fact that they return the same results is a coincidence