Re: [PERFORM] PgAdmin iii - Explain.

On Tue, Jun 1, 2010 at 1:47 PM, Jeres Caldeira Gomes wrote: > I'm needing some tutorial to use and understand the graphical feature > "Explain" of PgAdmin III? > > Do you have it? Hmm... you might want to ask about this on the pgadmin-support list. http://archives.postgresql.org/pgadmin-support/

Re: [PERFORM] how to force hashaggregate plan?

On Thu, May 27, 2010 at 3:34 PM, Slava Moudry wrote: > 1)  Is there a way to force plan that uses hashaggregate for the second > query? No, although if you crank work_mem up high enough you should get it, I think. > 2)  I am not trying to achieve any particular execution time for the > q

Re: [PERFORM] planner costs in "warm cache" tests

On Mon, May 31, 2010 at 3:55 PM, Tom Lane wrote: > Jesper Krogh writes: >> On 2010-05-30 20:34, Tom Lane wrote: >>> Well, hmm, I really doubt that that represents reality either.  A page >>> access is by no means "free" even when the page is already in cache. >>> I don't recall anyone suggesting

Re: [PERFORM] How filesystems matter with PostgreSQL

On 6/4/10 1:46 PM, Andres Freund wrote: > On Friday 04 June 2010 20:26:27 Jon Schewe wrote: > >> ext3 barrier=1: ~15 minutes >> ext4 nobarrier: ~15 minutes >> > Any message in the kernel log about barriers or similar? > > No. -- Jon Schewe | http://mtu.net/~jpschewe If you see an atta

Re: [PERFORM] How filesystems matter with PostgreSQL

On Friday 04 June 2010 20:26:27 Jon Schewe wrote: > ext3 barrier=1: ~15 minutes > ext4 nobarrier: ~15 minutes Any message in the kernel log about barriers or similar? Andres -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http:/

Re: [PERFORM] How filesystems matter with PostgreSQL

I just used standard mkfs for each filesystem and mounted them without options, unless otherwise specified. On 6/4/10 1:37 PM, Bryan Hinton wrote: > What types of journaling on each fs? > > > On Fri, Jun 4, 2010 at 1:26 PM, Jon Schewe > wrote: > > On 6/4/10 9:33 AM, A

Re: [PERFORM] How filesystems matter with PostgreSQL

What types of journaling on each fs? On Fri, Jun 4, 2010 at 1:26 PM, Jon Schewe wrote: > On 6/4/10 9:33 AM, Andres Freund wrote: > > On Friday 04 June 2010 16:25:30 Tom Lane wrote: > > > >> Andres Freund writes: > >> > >>> On Friday 04 June 2010 14:17:35 Jon Schewe wrote: > >>> > XFS (log

Re: [PERFORM] How filesystems matter with PostgreSQL

On 6/4/10 9:33 AM, Andres Freund wrote: > On Friday 04 June 2010 16:25:30 Tom Lane wrote: > >> Andres Freund writes: >> >>> On Friday 04 June 2010 14:17:35 Jon Schewe wrote: >>> XFS (logbufs=8): ~4 hours to finish ext4: ~1 hour 50 minutes to finish ext3: 15 minutes to

Re: [PERFORM] How filesystems matter with PostgreSQL

I'm running on Linux, so that's not really an option here. On 6/4/10 1:20 PM, Bryan Hinton wrote: > UFS2 w/ soft updates on FreeBSD might be an interesting addition to > the list of test cases > > On Fri, Jun 4, 2010 at 9:33 AM, Andres Freund > wrote: > > On Friday

Re: [PERFORM] slow query

The behaviour is different in postgres 8.1.9 (much faster) (the table has 9 million rows instead of 25 million..but the query comes back very fast (8 seconds).. Wonder if this is very specific to 8.4.0 On Fri, Jun 4, 2010 at 11:01 AM, Anj Adu wrote: > Does the difference in expected and actual

Re: [PERFORM] How filesystems matter with PostgreSQL

UFS2 w/ soft updates on FreeBSD might be an interesting addition to the list of test cases On Fri, Jun 4, 2010 at 9:33 AM, Andres Freund wrote: > On Friday 04 June 2010 16:25:30 Tom Lane wrote: > > Andres Freund writes: > > > On Friday 04 June 2010 14:17:35 Jon Schewe wrote: > > >> XFS (logbufs

Re: [PERFORM] Performance tuning for postgres

Is this a bulk insert? Are you wrapping your statements within a transaction(s)? How many columns in the table? What do the table statistics look like? On Fri, Jun 4, 2010 at 9:21 AM, Michael Gould < mgo...@intermodalsoftwaresolutions.net> wrote: > In my opinion it depends on the application,

Re: [PERFORM] slow query

Does the difference in expected and actual rows as seen by the planner a big factor? Even after an analyze...the results are similar. (there is a big diff between expected and actual) Partition has 25 million rows On Fri, Jun 4, 2010 at 10:41 AM, Anj Adu wrote: > 2010/6/4  : >>> I am reposting as

Re: [PERFORM] slow query

2010/6/4 : >> I am reposting as my original query was mangled >> >> The link to the explain plan is here as it does not paste well into >> the email body. >> >> http://explain.depesz.com/s/kHa >> >> >> The machine is a 2 cpu quad core 5430 with 32G RAM and 6x450G 15K >> single raid-10 array >> >>

Re: [PERFORM] slow query

I'm interested in why the two partitions dev4_act_dy_fact and > dev4_act_dy_fact_2010_05_t3 are treated so differently. I'm guessing that > the former is the parent and the latter the child table? Yes..you are correct. > > When accessing the parent table, Postgres is able to use a bitmap AND inde

Re: [PERFORM] Weird XFS WAL problem

Kevin Grittner wrote: > Bruce Momjian wrote: > > Kevin Grittner wrote: > > >> Any decent RAID controller will ensure that the drives themselves > >> aren't using write-back caching. When we've mentioned write-back > >> versus write-through on this thread we've been talking about the > >> behavi

Re: [PERFORM] Weird XFS WAL problem

Bruce Momjian wrote: > Kevin Grittner wrote: >> Any decent RAID controller will ensure that the drives themselves >> aren't using write-back caching. When we've mentioned write-back >> versus write-through on this thread we've been talking about the >> behavior of the *controller*. We have our

Re: [PERFORM] Weird XFS WAL problem

Kevin Grittner wrote: > Bruce Momjian wrote: > > Kevin Grittner wrote: > > >> The controller waits for the drive to tell it that it has made it > >> to the platter before it discards it. What made you think > >> otherwise? > > > > Because a write-back drive cache says it is on the drive before

Re: [PERFORM] performance regression with Linux 2.6.33 and glibc 2.12

The Friday 04 June 2010 15:59:05, Tom Lane wrote : > Marc Cousin writes: > > I hope I'm not going to expose an already known problem, but I couldn't > > find it mailing list archives (I only found > > http://archives.postgresql.org/pgsql- hackers/2009-12/msg01543.php). > > You sure this isn't the

Re: [PERFORM] performance regression with Linux 2.6.33 and glibc 2.12

On Friday 04 June 2010 15:59:05 Tom Lane wrote: > Marc Cousin writes: > > I hope I'm not going to expose an already known problem, but I couldn't > > find it mailing list archives (I only found > > http://archives.postgresql.org/pgsql- hackers/2009-12/msg01543.php). > > You sure this isn't the we

Re: [PERFORM] Weird XFS WAL problem

Bruce Momjian wrote: > Kevin Grittner wrote: >> The controller waits for the drive to tell it that it has made it >> to the platter before it discards it. What made you think >> otherwise? > > Because a write-back drive cache says it is on the drive before it > hits the platters, which I think

Re: [PERFORM] Weird XFS WAL problem

Kevin Grittner wrote: > Bruce Momjian wrote: > > > On another topic, I am a little unclear on how things behave when > > the drive is write-back. If the RAID controller card writes to the > > drive, but the data isn't on the platers, how does it know when it > > can discard that information from

Re: [PERFORM] Weird XFS WAL problem

Bruce Momjian wrote: > On another topic, I am a little unclear on how things behave when > the drive is write-back. If the RAID controller card writes to the > drive, but the data isn't on the platers, how does it know when it > can discard that information from the BBU RAID cache? The control

Re: [PERFORM] Weird XFS WAL problem

Greg Smith wrote: > Kevin Grittner wrote: > > I've seen this, too (with xfs). Our RAID controller, in spite of > > having BBU cache configured for writeback, waits for actual > > persistence on disk for write barriers (unlike for fsync). This > > does strike me as surprising to the point of borde

Re: [PERFORM] Performance tuning for postgres

In my opinion it depends on the application, the priority of the application and whether or not it is a commercially sold product, but depending on your needs you might want to consider having a 3rd party vendor who has expertise in this process review and help tune the application. One vendor tha

Re: [PERFORM] How filesystems matter with PostgreSQL

On Friday 04 June 2010 16:25:30 Tom Lane wrote: > Andres Freund writes: > > On Friday 04 June 2010 14:17:35 Jon Schewe wrote: > >> XFS (logbufs=8): ~4 hours to finish > >> ext4: ~1 hour 50 minutes to finish > >> ext3: 15 minutes to finish > >> ext3 on LVM: 15 minutes to finish > > > > My guess is

Re: [PERFORM] How filesystems matter with PostgreSQL

Andres Freund writes: > On Friday 04 June 2010 14:17:35 Jon Schewe wrote: >> XFS (logbufs=8): ~4 hours to finish >> ext4: ~1 hour 50 minutes to finish >> ext3: 15 minutes to finish >> ext3 on LVM: 15 minutes to finish > My guess is that some of the difference comes from barrier differences. ext4

Re: [PERFORM] Performance tuning for postgres

Yogesh Naik wrote: > I am performing a DB insertion and update for 3000+ records and > while doing so i get CPU utilization to 100% with 67% of CPU used > by postgres > > I have also done optimization on queries too... > > Is there any way to optimized the CPU utilization for postgres

Re: [PERFORM] performance regression with Linux 2.6.33 and glibc 2.12

Marc Cousin writes: > I hope I'm not going to expose an already known problem, but I couldn't find > it mailing list archives (I only found http://archives.postgresql.org/pgsql- > hackers/2009-12/msg01543.php). You sure this isn't the well-known "ext4 actually implements fsync where ext3 didn't"

[PERFORM] performance regression with Linux 2.6.33 and glibc 2.12

Hi. I hope I'm not going to expose an already known problem, but I couldn't find it mailing list archives (I only found http://archives.postgresql.org/pgsql- hackers/2009-12/msg01543.php). On one of my (non production) machines, I've just seen a very big performance regression (I was doing a ve

Re: [PERFORM] How filesystems matter with PostgreSQL

On Friday 04 June 2010 14:17:35 Jon Schewe wrote: > Some interesting data about different filesystems I tried with > PostgreSQL and how it came out. > > I have an application that is backed in postgres using Java JDBC to > access it. The tests were all done on an opensuse 11.2 64-bit machine, > on

Re: [PERFORM] How filesystems matter with PostgreSQL

On Friday 04 June 2010 14:17:35 Jon Schewe wrote: > Some interesting data about different filesystems I tried with > PostgreSQL and how it came out. > > I have an application that is backed in postgres using Java JDBC to > access it. The tests were all done on an opensuse 11.2 64-bit machine, > on

[PERFORM] How filesystems matter with PostgreSQL

Some interesting data about different filesystems I tried with PostgreSQL and how it came out. I have an application that is backed in postgres using Java JDBC to access it. The tests were all done on an opensuse 11.2 64-bit machine, on the same hard drive (just ran mkfs between each test) on the

Re: [PERFORM] Weird XFS WAL problem

On Thu, 3 Jun 2010, Greg Smith wrote: And it's also quite reasonable for a RAID controller to respond to that "flush the whole cache" call by flushing its cache. Remember that the RAID controller is presenting itself to the OS as a large disc, and hiding the individual discs from the OS. Why s

Re: [PERFORM] slow query

On Thu, 3 Jun 2010, Anj Adu wrote: http://explain.depesz.com/s/kHa I'm interested in why the two partitions dev4_act_dy_fact and dev4_act_dy_fact_2010_05_t3 are treated so differently. I'm guessing that the former is the parent and the latter the child table? When accessing the parent table

Re: [PERFORM] slow query

> I am reposting as my original query was mangled > > The link to the explain plan is here as it does not paste well into > the email body. > > http://explain.depesz.com/s/kHa > > > The machine is a 2 cpu quad core 5430 with 32G RAM and 6x450G 15K > single raid-10 array > > 1G work_mem > default_st