From: | Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com> |
---|---|
To: | |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Slow HashAggregate/cache access |
Date: | 2015-08-05 19:29:36 |
Message-ID: | CAGewt-vMjhJ6vNfs=+iJoHgNdo-buDYHuwRBhSDOzV_2nCQVww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)ymail(dot)com>:
> Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2015-08-05 19:55:58 | Re: Slow HashAggregate/cache access |
Previous Message | Maxim Boguk | 2015-08-05 18:25:25 | Re: Slow HashAggregate/cache access |