Re: [PERFORM] table partioning performance

On Wed, 2007-01-10 at 16:00 -0500, Steven Flatt wrote:
> On 1/9/07, Simon Riggs <simon <at> 2ndquadrant.com> wrote: 
>         If you are doing date range partitioning it should be fairly
>         simple to
>         load data into the latest table directly. That was the way I
>         originally 
>         intended for it to be used. The rules approach isn't something
>         I'd
>         recommend as a bulk loading option and its a lot more complex
>         anyway.
> The problem we have with blindly loading all data into the latest
> table is that some data (< 5%, possibly even much less) is actually
> delivered "late" and belongs in earlier partitions.  So we still
> needed the ability to send data to an arbitrary partition.

Yes, understand the problem.

COPY is always going to be faster than INSERTs anyhow and COPY doesn't
allow views, nor utilise rules. You can set up a client-side program to
pre-qualify the data and feed it to multiple simultaneous COPY commands,
as the best current way to handle this.

--
Next section aimed at pgsql-hackers, relates directly to above:

My longer term solution looks like this:

1. load all data into newly created partition (optimised in a newly
submitted patch for 8.3), then add the table as a new partition

Re: [PERFORM] unusual performance for vac following 8.2 upgrade

Kim <kim <at> myemma.com> writes:
> We were running on 8.1.1 previous to upgrading to 8.2, and yes, we 
> definitely have a heafty pg_class. The inheritance model is heavily used 
> in our schema (the results of the group by you wanted to see are down 
> below).  However, no significant problems were seen with vacs while we 
> were on 8.1.

Odd, because the 8.1 code looks about the same, and it is perfectly
obvious in hindsight that its runtime is about O(N^2) in the number of
relations .  At least that'd be the case if the stats collector
output were fully populated.  Did you have either stats_block_level or
stats_row_level turned on in 8.1?  If not, maybe the reason for the
change is that in 8.2, that table *will* be pretty fully populated,
because now it's got a last-vacuum-time entry that gets made even if the
stats are otherwise turned off.  Perhaps making that non-disablable
wasn't such a hot idea .

What I think we need to do about this is

(1) fix pgstat_vacuum_tabstats to have non-O(N^2) behavior; I'm thinking
of using a hash table for the OIDs instead of a linear list.  Should be
a pretty small change; I'll work on it today.

(2) Reconsider whether last-vacuum-time should be sent to the collector
unconditionally.

Comments from hackers?

			regards, tom lane

Re: [PERFORM] unusual performance for vac following 8.2 upgrade

Tom Lane wrote:

> What I think we need to do about this is
> 
> (1) fix pgstat_vacuum_tabstats to have non-O(N^2) behavior; I'm thinking
> of using a hash table for the OIDs instead of a linear list.  Should be
> a pretty small change; I'll work on it today.
> 
> (2) Reconsider whether last-vacuum-time should be sent to the collector
> unconditionally.

(2) seems a perfectly reasonably answer, but ISTM (1) would be good to
have anyway (at least in HEAD).

--

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] unusual performance for vac following 8.2 upgrade

On Thu, 2007-01-11 at 14:45 -0500, Tom Lane wrote:
> Kim <kim <at> myemma.com> writes:
> > We were running on 8.1.1 previous to upgrading to 8.2, and yes, we 
> > definitely have a heafty pg_class. The inheritance model is heavily used 
> > in our schema (the results of the group by you wanted to see are down 
> > below).  However, no significant problems were seen with vacs while we 
> > were on 8.1.
> 
> Odd, because the 8.1 code looks about the same, and it is perfectly
> obvious in hindsight that its runtime is about O(N^2) in the number of
> relations .  At least that'd be the case if the stats collector
> output were fully populated.  Did you have either stats_block_level or
> stats_row_level turned on in 8.1?  If not, maybe the reason for the
> change is that in 8.2, that table *will* be pretty fully populated,
> because now it's got a last-vacuum-time entry that gets made even if the
> stats are otherwise turned off.  Perhaps making that non-disablable
> wasn't such a hot idea .
> 
> What I think we need to do about this is
> 
> (1) fix pgstat_vacuum_tabstats to have non-O(N^2) behavior; I'm thinking
> of using a hash table for the OIDs instead of a linear list.  Should be
> a pretty small change; I'll work on it today.
> 
> (2) Reconsider whether last-vacuum-time should be sent to the collector
> unconditionally.
> 
> Comments from hackers?

It's not clear to me how this fix will alter the INSERT issue Kim

Re: [PERFORM] table partioning performance

On Thu, Jan 11, 2007 at 12:15:50PM +0000, Simon Riggs wrote:
> On Wed, 2007-01-10 at 16:00 -0500, Steven Flatt wrote:
> > On 1/9/07, Simon Riggs <simon <at> 2ndquadrant.com> wrote: 
> >         If you are doing date range partitioning it should be fairly
> >         simple to
> >         load data into the latest table directly. That was the way I
> >         originally 
> >         intended for it to be used. The rules approach isn't something
> >         I'd
> >         recommend as a bulk loading option and its a lot more complex
> >         anyway.
> > The problem we have with blindly loading all data into the latest
> > table is that some data (< 5%, possibly even much less) is actually
> > delivered "late" and belongs in earlier partitions.  So we still
> > needed the ability to send data to an arbitrary partition.
> 
> Yes, understand the problem.
> 
> COPY is always going to be faster than INSERTs anyhow and COPY doesn't
> allow views, nor utilise rules. You can set up a client-side program to
> pre-qualify the data and feed it to multiple simultaneous COPY commands,
> as the best current way to handle this.
> 
> --
> Next section aimed at pgsql-hackers, relates directly to above:

I'm wondering if you see any issues with COPYing into a partitioned
table that's using triggers instead of rules to direct data to the
appropriate tables?

Re: [PERFORM] unusual performance for vac following 8.2 upgrade

"Simon Riggs" <simon <at> 2ndquadrant.com> writes:
> It's not clear to me how this fix will alter the INSERT issue Kim
> mentions.

I didn't say that it would; we have no information on the INSERT issue,
so I'm just concentrating on the problem that he did provide info on.

(BTW, I suppose the slow-\d issue is the regex planning problem we
already knew about.)

I'm frankly not real surprised that there are performance issues with
such a huge pg_class; it's not a regime that anyone's spent any time
optimizing.  It is interesting that 8.2 seems to have regressed but
I can think of several places that would've been bad before.  One is
that there are seqscans of pg_inherits ...

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] unusual performance for vac following 8.2 upgrade

On Thu, Jan 11, 2007 at 04:49:28PM -0300, Alvaro Herrera wrote:
> Tom Lane wrote:
> 
> > What I think we need to do about this is
> > 
> > (1) fix pgstat_vacuum_tabstats to have non-O(N^2) behavior; I'm thinking
> > of using a hash table for the OIDs instead of a linear list.  Should be
> > a pretty small change; I'll work on it today.
> > 
> > (2) Reconsider whether last-vacuum-time should be sent to the collector
> > unconditionally.
> 
> (2) seems a perfectly reasonably answer, but ISTM (1) would be good to
> have anyway (at least in HEAD).

Actually, I'd rather see the impact #1 has before adding #2... If #1
means we're good for even someone with 10M relations, I don't see much
point in #2.

BTW, we're now starting to see more users with a large number of
relations, thanks to partitioning. It would probably be wise to expand
test coverage for that case, especially when it comes to performance.
--

-- 
Jim Nasby                                            jim <at> nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Re: [PERFORM] unusual performance for vac following 8.2 upgrade

I wrote:
> (2) Reconsider whether last-vacuum-time should be sent to the collector
> unconditionally.

Actually, now that I look, the collector already contains this logic:

    /*
     * Don't create either the database or table entry if it doesn't already
     * exist.  This avoids bloating the stats with entries for stuff that is
     * only touched by vacuum and not by live operations.
     */

and ditto for analyze messages.  So my idea that the addition of
last-vac-time was causing an increase in the statistics file size
compared to 8.1 seems wrong.

How large is your $PGDATA/global/pgstat.stat file, anyway?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Re: [PERFORM] unusual performance for vac following 8.2 upgrade

Re: [PERFORM] unusual performance for vac following 8.2upgrade

On Thu, 2007-01-11 at 16:11 -0500, Tom Lane wrote:
> "Simon Riggs" <simon <at> 2ndquadrant.com> writes:
> > It's not clear to me how this fix will alter the INSERT issue Kim
> > mentions.
> 
> I didn't say that it would; we have no information on the INSERT issue,
> so I'm just concentrating on the problem that he did provide info on.

OK.

> I'm frankly not real surprised that there are performance issues with
> such a huge pg_class; it's not a regime that anyone's spent any time
> optimizing. 

Yeh, I saw a pg_class that big once, but it just needed a VACUUM.

Temp relations still make pg_class entried don't they? Is that on the
TODO list to change?

--

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend