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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

Reply via email to