Thread: Configuration Optimisation
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
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/
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
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/
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.
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/
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.