pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtunning strategy needed
4+ messages / 3 participants
[nested] [flat]
* tunning strategy needed
@ 2011-08-17 22:40 hyelluas <helen_yelluas@mcafee.com>
0 siblings, 2 replies; 4+ messages in thread
From: hyelluas @ 2011-08-17 22:40 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have an old application that was written on Postgres 8.1.
There are a few hundreds tables, 30-40 columns per table, hundreds of
views, and all the sql is inside java code.
We are moving it to 8.4, it seems to be VERY slow.
There are 20-30 tables transactions - the objects are spread acrross
multiple tables and some tables have data from different objects.
I need a short term tuning strategy minimizing rewrite & redesign.
Should I start with replacing the sql with procedures?
Should I start with replacing the views with the procedures to save time on
recreating an execution plan and parsing?
Should I start with tuning server parameters ?
all your suggestions are greatly appreciated!
thank you.
Helen
--
View this message in context: http://postgresql.1045698.n5.nabble.com/tunning-strategy-needed-tp4710245p4710245.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: tunning strategy needed
@ 2011-08-18 05:45 Craig Ringer <ringerc@ringerc.id.au>
parent: hyelluas <helen_yelluas@mcafee.com>
1 sibling, 0 replies; 4+ messages in thread
From: Craig Ringer @ 2011-08-18 05:45 UTC (permalink / raw)
To: hyelluas <helen_yelluas@mcafee.com>; +Cc: pgsql-performance
On 18/08/2011 6:40 AM, hyelluas wrote:
> Hello,
>
> I have an old application that was written on Postgres 8.1.
> There are a few hundreds tables, 30-40 columns per table, hundreds of
> views, and all the sql is inside java code.
>
> We are moving it to 8.4, it seems to be VERY slow.
> There are 20-30 tables transactions - the objects are spread acrross
> multiple tables and some tables have data from different objects.
>
> I need a short term tuning strategy minimizing rewrite& redesign.
>
>
- Turn on auto explain and slow query logging
- Examine the slow queries and plans. Run them manually with EXPLAIN
ANALYZE. Check that the statistics make sense and if they're inaccurate,
increase the statistics targets on those columns/tables then re-ANALYZE.
- If the stats are accurate but the query is still slow, try playing
with the cost parameters and see if you get a better result, then test
those settings server-wide to see if they improve overall performance.
--
Craig Ringer
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: tunning strategy needed
@ 2011-08-18 08:52 Tomas Vondra <tv@fuzzy.cz>
parent: hyelluas <helen_yelluas@mcafee.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tomas Vondra @ 2011-08-18 08:52 UTC (permalink / raw)
To: hyelluas <helen_yelluas@mcafee.com>; +Cc: pgsql-performance
On 18 Srpen 2011, 0:40, hyelluas wrote:
> Should I start with replacing the sql with procedures?
>
> Should I start with replacing the views with the procedures to save time
> on
> recreating an execution plan and parsing?
>
> Should I start with tuning server parameters ?
Yes, you should start by tuning the server as a whole. Did you just
install the DB and restored your database? Have you tuned the config?
Tell us what are the basic performance-related parameters, i.e.
shared_buffers
effective_cache_size
checkpoint_segments
checkpoint_completion_target
work_mem
maintainance_work_mem
seq_page_cost
random_page_cost
and more information about the hardware and setup too (RAM, database size).
There's a quite nice guide regarding general tuning:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Tomas
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: tunning strategy needed
@ 2011-08-19 21:14 hyelluas <helen_yelluas@mcafee.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 4+ messages in thread
From: hyelluas @ 2011-08-19 21:14 UTC (permalink / raw)
To: pgsql-performance
thank you, it is a great article.
the current on 8.1 checkpoint_segments = 3.
it looks too low for me , but I'm not tuning the 8.1 schema.
I'm looking for a generic approach of improving that beast while moving it
to 8.4
I'm trying to understand the internals for Views vs. Functions
there was none on 8.1 and I'm using pgpsql for 8.4.
Does it make sence to put view's sql into a function? would it save on
re-creating the execution plan and parsing?
thank you.
Helen
--
View this message in context: http://postgresql.1045698.n5.nabble.com/tunning-strategy-needed-tp4710245p4717048.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-08-19 21:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-17 22:40 tunning strategy needed hyelluas <helen_yelluas@mcafee.com>
2011-08-18 05:45 ` Craig Ringer <ringerc@ringerc.id.au>
2011-08-18 08:52 ` Tomas Vondra <tv@fuzzy.cz>
2011-08-19 21:14 ` hyelluas <helen_yelluas@mcafee.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox