Message-ID: <42DD3F3C.7000502@arbash-meinel.com>
Date: Tue, 19 Jul 2005 12:58:20 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/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: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigCB437C5FBC85BBD7FF02B6D1"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigCB437C5FBC85BBD7FF02B6D1
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%.
> 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. FYI, I'm going to be working on data
> sets in the order of GB.
>
> 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?
>
> Thanks in advance.

First, try to post in plain-text rather than html, it is easier to read. :)

Second, if you can determine what queries are running slow, post the
result of EXPLAIN ANALYZE on them, and we can try to help you tune
them/postgres to better effect.

Just a blanket question like this is hard to answer. Your new
shared_buffers are probably *way* too high. They should be at most
around 10% of ram. Since this is a dedicated server effective_cache_size
should be probably ~75% of ram, or close to 1.2GB.

There are quite a few things that you can tweak, so the more information
you can give, the more we can help.

For instance, if you are loading a lot of data into a table, if
possible, you want to use COPY not INSERT.
If you have a lot of indexes and are loading a significant portion, it
is sometimes faster to drop the indexes, COPY the data in, and then
rebuild the indexes.

For tables with a lot of inserts/updates, you need to watch out for
foreign key constraints. (Generally, you will want an index on both
sides of the foreign key. One is required, the other is recommended for
faster update/deletes).

John
=:->


--------------enigCB437C5FBC85BBD7FF02B6D1
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC3T9AJdeBCYSNAAMRAp+FAKDRs5s7EatjgMvPMJRl5VVzbb24XwCdGCVV
HqKtYtqNvXBExk7Jv7MYFF8=
=/XjI
-----END PGP SIGNATURE-----

--------------enigCB437C5FBC85BBD7FF02B6D1--