First off, what is the context this database is being used in? Is it the backend for a web server? Data warehouse? Etc?
Second, you didn't mention the use of indexes. Do you have any indexes on the table in question, and if so, does EXPLAIN ANALYZE show the planner utilizing the index(es)?
Third, you have 8 GB of RAM on a dedicated machine. Consider upping the memory settings in postgresql.conf. For instance, on my data warehouse machines (8 GB RAM each) I have shared_buffers set to almost 2 GB and effective_cache_size set to nearly 5.5 GB. (This is dependent on how you're utilizing this database, so don't blindly set these values!)
Last, you didn't mention what RAID level the other server you tested this on was running.
On Wed, Nov 12, 2008 at 10:27 AM, - - <themanatuf@xxxxxxxxx> wrote:
I've been searching for performance metrics and tweaks for a few weeks now. I'm trying to determine if the length of time to process my queries is accurate or not and I'm having a difficult time determining that. I know postgres performance is very dependent on hardware and settings and I understand how difficult it is to tackle. However, I was wondering if I could get some feedback based on my results please.
The database is running on a dual-core 2GHz Opteron processor with 8GB of RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad for Postgres, but moving the database to another server didn't change performance at all). Some of the key parameters from postgresql.conf are:
max_connections = 100
shared_buffers = 16MB
work_mem = 64MB
everything else is set to the default
One of my tables has 660,000 records and doing a SELECT * from that table (without any joins or sorts) takes 72 seconds. Ordering the table based on 3 columns almost doubles that time to an average of 123 seconds. To me, those numbers are crazy slow and I don't understand why the queries are taking so long. The tables are UTF-8 encode and contain a mix of languages (English, Spanish, etc). I'm running the query from pgadmin3 on a remote host. The server has nothing else running on it except the database.
As a test I tried splitting up the data across a number of other tables. I ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join the results together. This was even slower, taking an average of 103 seconds to complete the generic select all query.
I'm convinced something is wrong, I just can't pinpoint where it is. I can provide any other information necessary. If anyone has any suggestions it would be greatly appreciated.
--
Computers are like air conditioners...
They quit working when you open Windows.
