Re: [PERFORM] Oddly slow queries

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. The

Re: [PERFORM] SQL Function Slowness, 8.3.0

"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

[PERFORM] Background writer underemphasized ...

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 aro

[PERFORM] SQL Function Slowness, 8.3.0

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 t

Re: [PERFORM] SQL Function Slowness, 8.3.0

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

Re: [PERFORM] Oddly slow queries

[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 >>> co

Re: [PERFORM] Background writer underemphasized ...

[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 vari

Re: [PERFORM] Background writer underemphasized ...

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 wr

Re: [PERFORM] SQL Function Slowness, 8.3.0

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 interna

Re: [PERFORM] SQL Function Slowness, 8.3.0

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

Re: [PERFORM] Background writer underemphasized ...

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 sh

Re: [PERFORM] Background writer underemphasized ...

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 o

Re: [PERFORM] Background writer underemphasized ...

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 se

[PERFORM] Anybody using the Dell Powervault MD3000 array?

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

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

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 PRO

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

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?

Re: [PERFORM] SQL Function Slowness, 8.3.0

"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 c

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

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 f

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

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, fr

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

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.

[PERFORM] Query running slow

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

Re: [PERFORM] Oddly slow queries

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 queri

Re: [PERFORM] Query running slow

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

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

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 fo

Re: [PERFORM] Oddly slow queries

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

Re: [PERFORM] db size

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