Message-ID: <42DD420C.2010306@archonet.com>
Date: Tue, 19 Jul 2005 19:10:20 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Oliver Crosby <ryusei@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Looking for tips
References: <1efd553a05071910427b290203@mail.gmail.com>
In-Reply-To: <1efd553a05071910427b290203@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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