Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2008/1/20, Tom Lane <tgl@xxxxxxxxxxxxx>:
> Clodoaldo <clodoaldo.pinto.neto@xxxxxxxxx> writes:
> > 2008/1/16, Tom Lane <tgl@xxxxxxxxxxxxx>:
> >> I don't know of any reason to think that insertion is slower in 8.3
> >> than it was in 8.2, and no one else has reported anything of the sort.
>
> > The old server reproduces the behavior of the new one.
>
> Okay, Clodoaldo kindly gave me access to his old server, and after
> nearly a full day of poking at it I think I've figured out what is going
> on.

Thank you very much for working on this. That server will still be
available for a while if you want to poke further.

> You can see from the query that it's inserting the same constant "data"
> value into every new row, and if I understand the context correctly this
> value will be higher than all prior entries in the "usuarios" table.

Yes, that is correct. That value is this:
nextval('datas_data_serial_seq'::regclass)


> From
> this we can see that 8.2 has a working set that is a relatively small
> part of the index at any instant, whereas 8.3 has the entire index as
> working set ... and it doesn't fit into RAM.  Ergo, lots of disk seek
> delays.
...
> A possibly usable workaround for now is "set enable_hashagg = off"
> to force a GroupAggregate plan, which will deliver the values sorted
> by (data, usuario) rather than by their hash values.  This helps both
> versions, bringing the runtime down to something like 250 seconds,
> because the index on usuario then has complete locality of access.
> Alternatively, doubling the server's RAM would probably make the problem
> go away (for awhile, until the index reaches 4GB).

I need to say that the new server has 4GB and suffers the same
symptoms. OTOH if the proposed solution works then I wonder if it
matters.

Will "enable_hashagg" be settable(*) per query or only at server start/reload?

Regards, Clodoaldo Pinto Neto

* I didn't find this world at the dictionary but also didn't find
another one to say it.

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux