From: Richard Huxton <dev@archonet.com>
To: Oliver Crosby <ryusei@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Looking for tips
Date: Tue, 19 Jul 2005 19:10:20 +0100
Message-ID: <42DD420C.2010306@archonet.com> (raw)
In-Reply-To: <1efd553a05071910427b290203@mail.gmail.com>
References: <1efd553a05071910427b290203@mail.gmail.com>
Oliver Crosby wrote:
> Hi,
> I'm running Postgres 7.4.6 on a dedicated server with about 1.5gigs of ram.
> Running scripts locally, it takes about 1.5x longer than mysql, and the load
> on the server is only about 21%.
What scripts? What do they do?
Oh, and 7.4.8 is the latest release - worth upgrading for the fixes.
> I upped the sort_mem to 8192 (kB), and shared_buffers and
> effective_cache_size to 65536 (512MB), but neither the timing nor the server
> load have changed at all.
Well, effective_cache_size is the amount of RAM being used by the OS to
cache your files, so take a look at top/free and set it based on that
(pick a steady load).
What sort_mem should be will obviously depend how much sorting you do.
Drop shared_buffers down to about 10000 - 20000 (at a guess)
You may find the following useful
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
Read the Performance Tuning article, there is an updated one for version
8 at:
http://www.powerpostgresql.com/PerfList
> FYI, I'm going to be working on data sets in the
> order of GB.
Fair enough.
> I think I've gone about as far as I can with google.. can anybody give me
> some advice on how to improve the raw performance before I start looking at
> code changes?
Identify what the problem is first of all. Some things to consider:
- Are there particular queries giving you trouble?
- Is your load mostly reads or mostly writes?
- Do you have one user or 100?
- Are you block-loading data efficiently where necessary?
- Have you indexed both sides of your foreign-keys where sensible?
- Are your disks being used effectively?
- Are your statistics accurate/up to date?
Bear in mind that MySQL will probably be quicker for simple queries for
one user and always will be. If you have multiple users running a mix of
multi-table joins and updates then PG will have a chance to stretch its
legs.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, ryusei@gmail.com
Subject: Re: Looking for tips
In-Reply-To: <42DD420C.2010306@archonet.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