Re: [PERFORM] Where is my bottleneck?
Hi, Michael, Michael Stone 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 idle, your i/o isn't fast enough. Or the query is misoptimized (low work_mem, missing indices) and cause much more I/O than necessary. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] partitioning and locking problems
Hi, Marc, Marc Morin wrote: > 1- long running report is running on view > 2- continuous inserters into view into a table via a rule > 3- truncate or rule change occurs, taking an exclusive lock. > Must wait for #1 to finish. > 4- new reports and inserters must now wait for #3. > 5- now everyone is waiting for a single query in #1. Results > in loss of insert data granularity (important for our application). Apart from having two separate views (one for report, one for insert) as Richard suggested: If you have fixed times for #3, don't start any #1 that won't finish before it's time for #3. You could also use the LOCK command on an empty lock table at the beginning of each #1 or #3 transaction to prevent #3 from getting the view lock before #1 is finished. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] partitioning and locking problems
Using a separate lock table is what we've decided to do in this particular case to serialize #1 and #3. Inserters don't take this lock and as such will not be stalled. > -Original Message- > From: Markus Schaber [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 02, 2006 7:44 AM > To: Marc Morin > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] partitioning and locking problems > > Hi, Marc, > > Marc Morin wrote: > > > 1- long running report is running on view > > 2- continuous inserters into view into a table via a rule > > 3- truncate or rule change occurs, taking an exclusive lock. > > Must wait for #1 to finish. > > 4- new reports and inserters must now wait for #3. > > 5- now everyone is waiting for a single query in #1. Results > > in loss of insert data granularity (important for our application). > > Apart from having two separate views (one for report, one for > insert) as Richard suggested: > > If you have fixed times for #3, don't start any #1 that won't > finish before it's time for #3. > > You could also use the LOCK command on an empty lock table at > the beginning of each #1 or #3 transaction to prevent #3 from > getting the view lock before #1 is finished. > > > HTH, > Markus > > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS > > Fight against software patents in EU! www.ffii.org > www.nosoftwarepatents.org > > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Planner reluctant to start from subquery
"Kevin Grittner" <[EMAIL PROTECTED]> writes [offlist]: > Attached is a pg_dump -c file with only the required rows (none of > which contain confidential data), and 0.1% of the rows from the larger > tables. It does show the same pattern of costing and plan choice. Thanks for the test case. The first thing I found out was that HEAD does generate the fast plan from the IN case, while 8.1 does not, and after a bit of digging the reason became clear. The initial state that the planner starts from is essentially SELECT ... FROM ((C JOIN P) LEFT JOIN WPCT) IN-JOIN D (IN-JOIN being a notation for the way the planner thinks about IN, which is that it's a join with some special runtime behavior). The problem with this is that outer joins don't always commute with other joins, and up through 8.1 we didn't have any code to analyze whether or not re-ordering outer joins is safe. So we never did it at all. HEAD does have such code, and so it is able to re-order the joins enough to generate the fast plan, which is essentially SELECT ... FROM ((C IN-JOIN D) JOIN P) LEFT JOIN WPCT This is why eliminating the OUTER JOIN improved things for you. Your manual rearrangement into a JOIN-with-GROUP-BY inside the OUTER JOIN essentially duplicates the IN-JOIN rearrangement that HEAD is able to do for itself. BTW, the reason why getting rid of the OR improved matters is that: (a) with the "WPCT"."profileName" IS NOT NULL part as a top-level WHERE clause, the planner could prove that it could reduce the OUTER JOIN to a JOIN (because no null-extended row would pass that qual), whereupon it had join order flexibility again. (b) with the "C"."caseType" = 'PA' AND "C"."isConfidential" = false part as a top-level WHERE clause, there still wasn't any join order flexibility, but this added restriction on C reduced the number of C rows enough that there wasn't a performance problem anyway. So it's all fairly clear now what is happening. The good news is we have this fixed for 8.2, the bad news is that that patch is much too large to consider back-patching. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Huge Data sets, simple queries
Jeffrey W. Baker wrote: On Tue, 2006-01-31 at 09:00 -0800, Luke Lonergan wrote: Jim, On 1/30/06 12:25 PM, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: Why divide by 2? A good raid controller should be able to send read requests to both drives out of the mirrored set to fully utilize the bandwidth. Of course, that probably won't come into play unless the OS decides that it's going to read-ahead fairly large chunks of the table at a time... I've not seen one that does, nor would it work in the general case IMO. In RAID1 writes are duplicated and reads come from one of the copies. You could alternate read service requests to minimize rotational latency, but you can't improve bandwidth. Then you've not seen Linux. Linux does balanced reads on software mirrors. I'm not sure why you think this can't improve bandwidth. It does improve streaming bandwidth as long as the platter STR is more than the bus STR. FYI: so does the Solaris Volume Manager (by default) on Solaris. One can choose alternate access methods like "First" (if the other mirrors are slower than the first) or "Geometric". It's been doing this for a good 10 years now (back when it was called DiskSuite), so it's nothing new. -- Alan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
