Skip site navigation (1) Skip section navigation (2)

Re: Where do a novice do to make it run faster?

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: A B <gentosaker(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Where do a novice do to make it run faster?
Date: 2008-04-28 16:34:14
Message-ID: 4815FC86.2020602@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-performance
A B wrote:
> So, it is time to improve performance, it is running to slow.
> AFAIK (as a novice) there are a few general areas:
>
> 1) hardware
> 2) rewriting my queries and table structures
> 3) using more predefined queries
> 4) tweek parameters in the db conf files
>
> Of these points:
> 1) is nothing I can do about right now, but in the future perhaps.
> 2) will be quite hard right now since there is more code than time.
> 3) almost like 2 but perhaps more do-able with the current constraints.
> 4) This seems to be the easiest one to start with...
>
> So what should I do/read concerning point 4?
> If you have other good suggestions  I'd be very interested in that.
>   
Go back to step zero - gather information that would be helpful in 
giving advice. For starters:
- What hardware do you currently have?
- What OS and version of PG?
- How big is the database?
- What is the nature of the workload (small queries or data-mining, how 
many simultaneous clients, transaction rate, etc.)?
- Is PG sharing the machine with other workloads?

Then edit your postgresql.conf file to gather data (see 
http://www.postgresql.org/docs/8.3/interactive/monitoring-stats.html). 
With stat collection enabled, you can often find some low-hanging fruit 
like indexes that aren't used (look in pg_stat_user_indexes) - sometime 
because the query didn't case something in the where-clause correctly.

Also look at 
http://www.postgresql.org/docs/8.3/interactive/runtime-config-logging.html 
- especially the log_min_duration_statement setting to find long-running 
queries. You will probably need to try different settings and watch the 
log. Logging impacts performance so don't just set to log everything and 
forget. You need to play with it.

Don't discount step 2 - you may find you can rewrite one inefficient but 
frequent query. Or add a useful index on the server.

Cheers,
Steve




In response to

pgsql-performance by date

Next:From: Chris BrowneDate: 2008-04-28 16:35:18
Subject: Re: Where do a novice do to make it run faster?
Previous:From: Andrew SullivanDate: 2008-04-28 16:23:45
Subject: Re: Replication Syatem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group