Re: Slow HashAggregate/cache access

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

 



Hi,

Kevin:

Second machine config parameters:

shared_buffers = 8GB
work_mem = 1 GB (was 512MB)
maintenace_work_mem = 4 GB

#seq_page_cost = 1.0
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.005
#cpu_operator_cost = 0.0025

random_page_cost = 2.0
effective_cache_size = 110GB

I try to change from_collapse_limit, join_collapse_limit and io_con, w/o success.

I create a database with this tables only, vaccum analyze them and test with only my connection to postgresql.
Now we have another querys(all with aggregates) that the time is 15x - 20x slower than Oracle and SQL Server.
All tables have indexes (btree) with fields in the where/order/group parameters.

Maxim:

The developer is changing from a Desktop application (ODBC with Use Declare/Fetch, 'single' querys with local summing and aggregation) for a client/server web application (.NET, most querys with aggregate). Unfortunattly we cant change this querys, but I will try your solution to see what happens.

Take a look at another big query generated by the development tool. Oracle/SQL Server runs the same query (with the same data but in a slow machine) in about 2 seconds:


http://explain.depesz.com/s/wxq


Best regards,

Alexandre


2015-08-05 14:24 GMT-03:00 Kevin Grittner <kgrittn@xxxxxxxxx>:
Alexandre de Arruda Paes <adaldeia@xxxxxxxxx> wrote:

> We did the following tests:
>
> 1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA disk,Core i5)
> 2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores, SAS disks)

That's only part of the information we would need to be able to
give specific advice.  Please read this page:

https://wiki.postgresql.org/wiki/SlowQueryQuestions

One possibility is that you are running with the default
configuration, rather than having tuned for the hardware.  You are
very likely to need to adjust shared_buffers, effective_cache_size,
work_mem, maintenance_work_mem, random_page_cost, cpu_tuple_cost,
and (at least for the second machine) effective_io_concurrency.  If
the queries have a lot of joins you may need to increase
from_collapse_limit and/or join_collapse_limit.  You also may need
to adjust [auto]vacuum and/or background writer settings.  Various
OS settings may matter, too.

To get a handle on all this, it might be worth looking for Greg
Smith's book on PostgreSQL high performance.


--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux