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 =:->
Description: OpenPGP digital signature