On Wed, Jul 7, 2010 at 10:07 PM, Andy Colson <a...@squeakycode.net> wrote:
> On 07/07/2010 06:06 PM, Ryan Wexler wrote: > >> Postgresql was previously running on a single cpu linux machine with 2 >> gigs of memory and a single sata drive (v8.3). Basically a desktop with >> linux on it. I experienced slow performance. >> >> So, I finally moved it to a real server. A dually zeon centos machine >> with 6 gigs of memory and raid 10, postgres 8.4. But, I am now experiencing >> even worse performance issues. >> >> My system is consistently highly transactional. However, there is also >> regular complex queries and occasional bulk loads. >> >> On the new system the bulk loads are extremely slower than on the previous >> machine and so are the more complex queries. The smaller transactional >> queries seem comparable but i had expected an improvement. Performing a db >> import via psql -d databas -f dbfile illustrates this problem. It takes 5 >> hours to run this import. By contrast, if I perform this same exact import >> on my crappy windows box with only 2 gigs of memory and default postgres >> settings it takes 1 hour. Same deal with the old linux machine. How is >> this possible? >> >> Here are some of my key config settings: >> max_connections = 100 >> shared_buffers = 768MB >> effective_cache_size = 2560MB >> work_mem = 16MB >> maintenance_work_mem = 128MB >> checkpoint_segments = 7 >> checkpoint_timeout = 7min >> checkpoint_completion_target = 0.5 >> >> I have tried varying the shared_buffers size from 128 all the way to >> 1500mbs and got basically the same result. Is there a setting change I >> should be considering? >> >> Does 8.4 have performance problems or is this unique to me? >> >> thanks >> >> > Yeah, I inherited a "server" (the quotes are sarcastic air quotes), with > really bad disk IO... er.. really safe disk IO. Try the dd test. On my > desktop I get 60-70 meg a second. On this "server" (I laugh) I got about > 20. I had to go out of my way (way out) to enable the disk caching, and > even then only got 50 meg a second. > > http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm<http://www.westnet.com/%7Egsmith/content/postgresql/pg-disktesting.htm> For about $2k - $3k, you can get a server that will do upwards of 300 MB/sec, assuming the bulk of that cost goes to a good hardware-based RAID controller with a battery backed-up cache and some good 15k RPM SAS drives. Since it sounds like you are disk I/O bound, it's probably not worth it for you to spend extra on CPU and memory. Sink the money into the disk array instead. If you have an extra $4k more money in your budget, you might even try 4 of these in a RAID 10: http://www.provantage.com/ocz-technology-oczssd2-2vtxex100g~7OCZT0L9.htm -- Eliot Gable
