Oliver Crosby wrote:
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
Read the Performance Tuning article, there is an updated one for version
> 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
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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?