Thread: Configuration Optimisation

Configuration Optimisation

From
Howard Cole
Date:
Hi,

I am trying to optimise a database server to give the best performance
possible, so I switched from windows 2k3 to linux (ubuntu 9.10) on the
basis that most people seem to be of the opinion that postgres runs
better on linux than windows. To test my optimisation of the system, I
run a simple pgbench setup for 2 minutes. Not very scientific I realise
but it has helped me tune systems quite well in the past.

My problem is that I am getting nowhere near the performance of windows
on my linux build.

For example,

Test setup: pgbench -i -s 5
Test run: pgbench -T 120

You may think this is a short test, but running it for much longer does
not seem to make a significant difference.

Now running on windows I get ~ 700 TPS, but on linux I am getting ~70 TPS.

The hardware configuration is Dual Opteron, 8GB Ram and 4 sata disks in
a Hardware Raid 10 configuration.

There is a change in hardware configuration, from Raid5 on the Windows
system - But I would expect that switching to raid 10 would give a
performance boost.

Here are some of the changed settings in my liunx configuration:

shared_buffers = 1900MB
work_mem = 50MB
checkpoint_segments = 32

Am I missing something obvious - or is it a waste of time using the
pgbench as a judge of optimisation in these circumstances?

Thanks.

Howard Cole
www.selestial.com


Re: Configuration Optimisation

From
Leif Biberg Kristensen
Date:
On Thursday 14. January 2010 14.31.07 Howard Cole wrote:

> Test setup: pgbench -i -s 5
> Test run: pgbench -T 120
>
> You may think this is a short test, but running it for much longer does
> not seem to make a significant difference.
>
> Now running on windows I get ~ 700 TPS, but on linux I am getting ~70 TPS.
>
> The hardware configuration is Dual Opteron, 8GB Ram and 4 sata disks in
> a Hardware Raid 10 configuration.

For what it's worth, on my aging Gentoo Linux workstation with a Pentium 4 @ 3
GHz, 1 GB RAM and a single 400 GB SATA Seagate Barracuda disk, default
Postgres 8.4.2 installation, I get:

leif@balapapa ~ $ pgbench -i -s 5
                                   
...
leif@balapapa ~ $ pgbench -T 120
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 5
query mode: simple
number of clients: 1
duration: 120 s
number of transactions actually processed: 29990
tps = 249.694058 (including connections establishing)
tps = 249.700935 (excluding connections establishing)

regards,
--
Leif Biberg Kristensen
http;//solumslekt.org/

Re: Configuration Optimisation

From
Andy Colson
Date:
On 1/14/2010 7:31 AM, Howard Cole wrote:
> Hi,
>
> I am trying to optimise a database server to give the best performance
> possible, so I switched from windows 2k3 to linux (ubuntu 9.10) on the
> basis that most people seem to be of the opinion that postgres runs
> better on linux than windows. To test my optimisation of the system, I
> run a simple pgbench setup for 2 minutes. Not very scientific I realise
> but it has helped me tune systems quite well in the past.
>
> My problem is that I am getting nowhere near the performance of windows
> on my linux build.
>
> For example,
>
> Test setup: pgbench -i -s 5
> Test run: pgbench -T 120
>
> You may think this is a short test, but running it for much longer does
> not seem to make a significant difference.
>
> Now running on windows I get ~ 700 TPS, but on linux I am getting ~70 TPS.
>
> The hardware configuration is Dual Opteron, 8GB Ram and 4 sata disks in
> a Hardware Raid 10 configuration.
>
> There is a change in hardware configuration, from Raid5 on the Windows
> system - But I would expect that switching to raid 10 would give a
> performance boost.
>
> Here are some of the changed settings in my liunx configuration:
>
> shared_buffers = 1900MB
> work_mem = 50MB
> checkpoint_segments = 32
>
> Am I missing something obvious - or is it a waste of time using the
> pgbench as a judge of optimisation in these circumstances?
>
> Thanks.
>
> Howard Cole
> www.selestial.com
>
>

I think an important question is: are both os's really flushing all the
way to disk, or is someone lying to you?

Assuming your workload is IO bound, I'd bet windows is write caching and
linux is not.

Did you try a dd test on linux?

This is a HW Raid, are there any params you can set for
caching/writeback/etc?  Do you know what the windows settings were?

-Andy

Re: Configuration Optimisation

From
Leif Biberg Kristensen
Date:
On Thursday 14. January 2010 16.02.12 Andy Colson wrote:
> I think an important question is: are both os's really flushing all the
> way to disk, or is someone lying to you?
>
> Assuming your workload is IO bound, I'd bet windows is write caching and
> linux is not.

See my reply above. My 250 tps seems in line with the 700 tps on a modern
system that the OP gets on his Windows setup. To me it seems like something is
broken on his Ubuntu setup.

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/

Re: Configuration Optimisation

From
Howard Cole
Date:
Leif Biberg Kristensen wrote:
> On Thursday 14. January 2010 16.02.12 Andy Colson wrote:
>
> See my reply above. My 250 tps seems in line with the 700 tps on a modern
> system that the OP gets on his Windows setup. To me it seems like something is
> broken on his Ubuntu setup.
>
> regards,
>
I am hoping your assumptions are correct. I have reinstalled ubuntu from
scratch as I had been tinkering with the power management on the server.
Once the RAID has rebuilt, I shall try again.

Re: Configuration Optimisation

From
Leif Biberg Kristensen
Date:
On Thursday 14. January 2010 16.55.07 Howard Cole wrote:
> Leif Biberg Kristensen wrote:
> > On Thursday 14. January 2010 16.02.12 Andy Colson wrote:
> >
> > See my reply above. My 250 tps seems in line with the 700 tps on a modern
> > system that the OP gets on his Windows setup. To me it seems like
something is
> > broken on his Ubuntu setup.
> >
> > regards,
> >
> I am hoping your assumptions are correct. I have reinstalled ubuntu from
> scratch as I had been tinkering with the power management on the server.
> Once the RAID has rebuilt, I shall try again.

What kind of file system are you running?

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/

Re: Configuration Optimisation

From
Howard Cole
Date:
Leif Biberg Kristensen wrote:
> On Thursday 14. January 2010 16.55.07 Howard Cole wrote:
>
>> Leif Biberg Kristensen wrote:
>>
>>> On Thursday 14. January 2010 16.02.12 Andy Colson wrote:
>>>
>>> See my reply above. My 250 tps seems in line with the 700 tps on a modern
>>> system that the OP gets on his Windows setup. To me it seems like
>>>
> something is
>
>>> broken on his Ubuntu setup.
>>>
>>> regards,
>>>
>>>
>> I am hoping your assumptions are correct. I have reinstalled ubuntu from
>> scratch as I had been tinkering with the power management on the server.
>> Once the RAID has rebuilt, I shall try again.
>>
>
> What kind of file system are you running?
>
> regards,
>
The file system is ext4. If I turn fsync off I get a TPS of 1120 so I
think there is an issue with the speed of the array.

The dd test give a speed of 125MB/s which seems reasonable to me.

I can leave fsync off but I suspect that I am just ignoring the problem.

Howard.