Re: performance on new linux box

On Wed, Jul 7, 2010 at 10:07 PM, Andy Colson <andy(at)squeakycode(dot)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