From: Steve Crawford <scrawford@pinpointresearch.com>
To: A B <gentosaker@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Where do a novice do to make it run faster?
Date: Mon, 28 Apr 2008 09:34:14 -0700
Message-ID: <4815FC86.2020602@pinpointresearch.com> (raw)
In-Reply-To: <dbbf25900804280856m41c67d96heaa616d39d6c44e0@mail.gmail.com>
References: <dbbf25900804280856m41c67d96heaa616d39d6c44e0@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com, gentosaker@gmail.com
Subject: Re: Where do a novice do to make it run faster?
In-Reply-To: <4815FC86.2020602@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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