Re: Performance problem. Could it be related to 8.3-beta4?

2008/1/7, Scott Marlowe <scott.marlowe@gmail.com>:
> On Jan 6, 2008 1:46 PM, Clodoaldo <clodoaldo.pinto.neto@gmail.com> wrote:
> > 2008/1/6, Scott Marlowe <scott.marlowe@gmail.com>:
> >
> > > On Jan 6, 2008 5:06 AM, Clodoaldo <clodoaldo.pinto.neto@gmail.com> wrote:
> > > >
> > > > Then I rebuilt and reinstalled postgresql with the xlog_seg_size set
> > > > to the default 16MB and did initdb. Now the time is 7,642 sec.
> > > >
> > > > I'm lost. It looks like 1GB xlog_seg_size is indeed faster than 16MB
> > > > but again it is slower than the production server which uses the
> > > > default xlog_seg_size.
> > >
> > > How fast was it the second time you ran it?
> >
> > You mean the new server with 16MB xlog_seg_size? Yes, I did run it
> > twice and both took about the same time. In all tests (all
> > configurations) I did an analyze before running so I think that is the
> > real time.
>
> OK, to eliminate the chance that it's sick hardware, I would suggest
> installing 8.2.5 with exactly the same settings and build (as much as
> possible) and see how that works.  If it's still slow, I would suspect
> the hardware is making the difference and investigate that first.
> Once you get even performance from 8.2.5 on both sets of hardware,
> then you can make a valid comparison with 8.3b4. Unless you've already
> done that... then I don't have a clue what to do...

I just did it. Built and installed 8.2.5. Copied the postgresql.conf
from the production. Issued an analyze and ran the insert query twice:

fahstats=> insert into usuarios (
fahstats(>   data,
fahstats(>   usuario,
fahstats(>   pontos,
fahstats(>   wus
fahstats(>   )
fahstats->   select
fahstats->     (select data_serial from data_serial) as data,
fahstats->     ui.usuario_serial as usuario,
fahstats->     sum(pontos) as pontos,
fahstats->     sum(wus) as wus
fahstats->   from usuarios_temp as ut inner join usuarios_indice as ui
fahstats->     on ut.usuario = ui.usuario_nome and ut.n_time = ui.n_time
fahstats->   group by data, ui.usuario_serial
fahstats->   ;
INSERT 0 880479
Time: 384991.682 ms

The second time it ran in 403 sec, half the production time.

Regards, Clodoaldo Pinto Neto