From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Oliver Crosby <ryusei(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Looking for tips |
Date: | 2005-07-19 18:10:20 |
Message-ID: | 42DD420C.2010306@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Crosby | 2005-07-19 18:21:22 | Re: Looking for tips |
Previous Message | Tom Lane | 2005-07-19 18:09:56 | Re: Impact of checkpoint_segments under continual load conditions |