pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to get higher tps
23+ messages / 8 participants
[nested] [flat]
* How to get higher tps
@ 2006-08-21 20:45 Marty Jia <mjia@ask.com>
2006-08-21 21:23 ` Re: How to get higher tps Jeff Davis <pgsql@j-davis.com>
2006-08-21 21:47 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-21 23:43 ` Re: How to get higher tps Scott Marlowe <smarlowe@g2switchworks.com>
2006-08-22 15:47 ` Re: How to get higher tps Ron <rjpeace@earthlink.net>
0 siblings, 5 replies; 23+ messages in thread
From: Marty Jia @ 2006-08-21 20:45 UTC (permalink / raw)
To: pgsql-performance
I'm exhausted to try all performance tuning ideas, like following
parameters
shared_buffers
fsync
max_fsm_pages
max_connections
shared_buffers
work_mem
max_fsm_pages
effective_cache_size
random_page_cost
I believe all above have right size and values, but I just can not get
higher tps more than 300 testd by pgbench
Here is our hardware
Dual Intel Xeon 2.8GHz
6GB RAM
Linux 2.4 kernel
RedHat Enterprise Linux AS 3
200GB for PGDATA on 3Par, ext3
50GB for WAL on 3Par, ext3
With PostgreSql 8.1.4
We don't have i/o bottle neck.
Whatelse I can try to better tps? Someone told me I can should get tps
over 1500, it is hard to believe.
Thanks
Marty
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-21 21:23 ` Jeff Davis <pgsql@j-davis.com>
2006-08-21 21:38 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
4 siblings, 1 reply; 23+ messages in thread
From: Jeff Davis @ 2006-08-21 21:23 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; +Cc: pgsql-performance
On Mon, 2006-08-21 at 16:45 -0400, Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following
> parameters
>
> shared_buffers
> fsync
By "tuning" fsync, what do you mean? Did you turn it off?
If you turned fsync off, that could compromise your data in case of any
kind of crash or power failure. However, if you turn fsync off you
should much higher TPS on pgbench than you're getting.
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
Does your disk controller have battery-backed writeback cache? How much?
> With PostgreSql 8.1.4
>
> We don't have i/o bottle neck.
>
Well, chances are PostgreSQL is waiting for fsync, which means you do
have an I/O bottleneck (however, you're not using all of your I/O
bandwidth, most likely).
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 21:23 ` Re: How to get higher tps Jeff Davis <pgsql@j-davis.com>
@ 2006-08-21 21:38 ` Marty Jia <mjia@ask.com>
0 siblings, 0 replies; 23+ messages in thread
From: Marty Jia @ 2006-08-21 21:38 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-performance
Jeff,
Thanks for your response, I did turn the fsync off, no performance
improvement.
Since the application is a network monring program, data is not critical
for us.
Marty
-----Original Message-----
From: Jeff Davis [mailto:pgsql@j-davis.com]
Sent: Monday, August 21, 2006 5:23 PM
To: Marty Jia
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to get higher tps
On Mon, 2006-08-21 at 16:45 -0400, Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following
> parameters
>
> shared_buffers
> fsync
By "tuning" fsync, what do you mean? Did you turn it off?
If you turned fsync off, that could compromise your data in case of any
kind of crash or power failure. However, if you turn fsync off you
should much higher TPS on pgbench than you're getting.
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
Does your disk controller have battery-backed writeback cache? How much?
> With PostgreSql 8.1.4
>
> We don't have i/o bottle neck.
>
Well, chances are PostgreSQL is waiting for fsync, which means you do
have an I/O bottleneck (however, you're not using all of your I/O
bandwidth, most likely).
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-21 21:47 ` Mark Lewis <mark.lewis@mir3.com>
2006-08-22 13:16 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
4 siblings, 1 reply; 23+ messages in thread
From: Mark Lewis @ 2006-08-21 21:47 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; +Cc: pgsql-performance
Not much we can do unless you give us more info about how you're testing
(pgbench setup), and what you've done with the parameters you listed
below. It would also be useful if you told us more about your drive
array than just "3Par". We need to know the RAID level, number/speed of
disks, whether it's got a battery-backed write cache that's turned on,
things like this.
Like Jeff just said, it's likely that you're waiting for rotational
latency, which would limit your maximum tps for sequential jobs based on
the number of disks in your array. For example, a 2-disk array of 10k
RPM disks is going to max out somewhere around 333 tps. (2*10000/60).
-- Mark Lewis
On Mon, 2006-08-21 at 16:45 -0400, Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following
> parameters
>
> shared_buffers
> fsync
> max_fsm_pages
> max_connections
> shared_buffers
> work_mem
> max_fsm_pages
> effective_cache_size
> random_page_cost
>
> I believe all above have right size and values, but I just can not get
> higher tps more than 300 testd by pgbench
>
> Here is our hardware
>
>
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
>
> With PostgreSql 8.1.4
>
> We don't have i/o bottle neck.
>
> Whatelse I can try to better tps? Someone told me I can should get tps
> over 1500, it is hard to believe.
>
> Thanks
>
> Marty
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 21:47 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
@ 2006-08-22 13:16 ` Marty Jia <mjia@ask.com>
2006-08-22 15:26 ` Re: How to get higher tps Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 23+ messages in thread
From: Marty Jia @ 2006-08-22 13:16 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: pgsql-performance; DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Hi, Mark
Thanks, here is our hardware info:
RAID 10, using 3Par virtual volume technology across ~200 physical FC
disks. 4 virtual disks for PGDATA, striped with LVM into one volume, 2
virtual disks for WAL, also striped. SAN attached with Qlogic SAN
surfer multipathing to load balance each LUN on two 2GBs paths. HBAs
are Qlogic 2340's. 16GB host cache on 3Par.
Detailed major config values
shared_buffers = 80000
fsync = on
max_fsm_pages = 350000
max_connections = 1000
work_mem = 65536
effective_cache_size = 610000
random_page_cost = 3
Marty
-----Original Message-----
From: Mark Lewis [mailto:mark.lewis@mir3.com]
Sent: Monday, August 21, 2006 5:47 PM
To: Marty Jia
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to get higher tps
Not much we can do unless you give us more info about how you're testing
(pgbench setup), and what you've done with the parameters you listed
below. It would also be useful if you told us more about your drive
array than just "3Par". We need to know the RAID level, number/speed of
disks, whether it's got a battery-backed write cache that's turned on,
things like this.
Like Jeff just said, it's likely that you're waiting for rotational
latency, which would limit your maximum tps for sequential jobs based on
the number of disks in your array. For example, a 2-disk array of 10k
RPM disks is going to max out somewhere around 333 tps. (2*10000/60).
-- Mark Lewis
On Mon, 2006-08-21 at 16:45 -0400, Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following
> parameters
>
> shared_buffers
> fsync
> max_fsm_pages
> max_connections
> shared_buffers
> work_mem
> max_fsm_pages
> effective_cache_size
> random_page_cost
>
> I believe all above have right size and values, but I just can not get
> higher tps more than 300 testd by pgbench
>
> Here is our hardware
>
>
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
>
> With PostgreSql 8.1.4
>
> We don't have i/o bottle neck.
>
> Whatelse I can try to better tps? Someone told me I can should get tps
> over 1500, it is hard to believe.
>
> Thanks
>
> Marty
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 21:47 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 13:16 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-22 15:26 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 23+ messages in thread
From: Scott Marlowe @ 2006-08-22 15:26 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; +Cc: Mark Lewis <mark.lewis@mir3.com>; pgsql-performance@postgresql.org, DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
On Tue, 2006-08-22 at 08:16, Marty Jia wrote:
> Hi, Mark
>
> Thanks, here is our hardware info:
>
> RAID 10, using 3Par virtual volume technology across ~200 physical FC
> disks. 4 virtual disks for PGDATA, striped with LVM into one volume, 2
> virtual disks for WAL, also striped. SAN attached with Qlogic SAN
> surfer multipathing to load balance each LUN on two 2GBs paths. HBAs
> are Qlogic 2340's. 16GB host cache on 3Par.
A few points.
Someone (Luke I think) posted that Linux's LVM has a throughput limit of
around 600 Megs/second.
Why are you using multiple virtual disks on an LPAR? Did you try this
with just a single big virtual disk first to have something to compare
it to? I think your disk subsystem is overthought for an LPAR. If you
were running physical disks on a locally attached RAID card, it would be
a good idea. But here you're just adding layers of complexity for no
gain, and in fact may be heading backwards.
I'd make two volumes on the LPAR, and let the LPAR do all the
virtualization for you. Put a couple disks in a mirror set for the
pg_xlog, format it ext2, and mount it noatime. Make another from a
dozen or so disks in an RAID 0 on top of RAID 1 (i.e. make a bunch of
mirror sets and stripe them into one big partition) and mount that for
PGDATA. Simplify, and get a baseline. Then, start mucking about to see
if you can get better performance. change ONE THING at a time, and only
one thing, and test it well.
Got the latest and greatest drivers for the qlogic cards?
I would suggest some component testing to make sure everything is
working well. bonnie++ and dd come to mind.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-21 22:08 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
4 siblings, 1 reply; 23+ messages in thread
From: Joshua D. Drake @ 2006-08-21 22:08 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; +Cc: pgsql-performance
Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following
> parameters
>
> shared_buffers
> fsync
> max_fsm_pages
> max_connections
> shared_buffers
> work_mem
> max_fsm_pages
> effective_cache_size
> random_page_cost
>
> I believe all above have right size and values, but I just can not get
> higher tps more than 300 testd by pgbench
What values did you use?
>
> Here is our hardware
>
>
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
>
> With PostgreSql 8.1.4
>
> We don't have i/o bottle neck.
Are you sure? What does iostat say during a pgbench? What parameters are
you passing to pgbench?
Well in theory, upgrading to 2.6 kernel will help as well as making your
WAL ext2 instead of ext3.
> Whatelse I can try to better tps? Someone told me I can should get tps
> over 1500, it is hard to believe.
1500? Hmmm... I don't know about that, I can get 470tps or so on my
measily dual core 3800 with 2gig of ram though.
Joshua D. Drake
>
> Thanks
>
> Marty
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
@ 2006-08-22 13:19 ` Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
0 siblings, 1 reply; 23+ messages in thread
From: Marty Jia @ 2006-08-22 13:19 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-performance; DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Joshua,
Here is
shared_buffers = 80000
fsync = on
max_fsm_pages = 350000
max_connections = 1000
work_mem = 65536
effective_cache_size = 610000
random_page_cost = 3
Here is pgbench I used:
pgbench -c 10 -t 10000 -d HQDB
Thanks
Marty
-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Monday, August 21, 2006 6:09 PM
To: Marty Jia
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to get higher tps
Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following
> parameters
>
> shared_buffers
> fsync
> max_fsm_pages
> max_connections
> shared_buffers
> work_mem
> max_fsm_pages
> effective_cache_size
> random_page_cost
>
> I believe all above have right size and values, but I just can not get
> higher tps more than 300 testd by pgbench
What values did you use?
>
> Here is our hardware
>
>
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
>
> With PostgreSql 8.1.4
>
> We don't have i/o bottle neck.
Are you sure? What does iostat say during a pgbench? What parameters are
you passing to pgbench?
Well in theory, upgrading to 2.6 kernel will help as well as making your
WAL ext2 instead of ext3.
> Whatelse I can try to better tps? Someone told me I can should get tps
> over 1500, it is hard to believe.
1500? Hmmm... I don't know about that, I can get 470tps or so on my
measily dual core 3800 with 2gig of ram though.
Joshua D. Drake
>
> Thanks
>
> Marty
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-22 14:31 ` Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:31 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
0 siblings, 2 replies; 23+ messages in thread
From: Mark Lewis @ 2006-08-22 14:31 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance@postgresql.org, DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Well, at least on my test machines running gnome-terminal, my pgbench
runs tend to get throttled by gnome-terminal's lousy performance to no
more than 300 tps or so. Running with 2>/dev/null to throw away all the
detailed logging gives me 2-3x improvement in scores. Caveat: in my
case the db is on the local machine, so who knows what all the
interactions are.
Also, when you initialized the pgbench db what scaling factor did you
use? And does running pgbench with -v improve performance at all?
-- Mark
On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> Joshua,
>
> Here is
>
> shared_buffers = 80000
> fsync = on
> max_fsm_pages = 350000
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 610000
> random_page_cost = 3
>
> Here is pgbench I used:
>
> pgbench -c 10 -t 10000 -d HQDB
>
> Thanks
>
> Marty
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Monday, August 21, 2006 6:09 PM
> To: Marty Jia
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to get higher tps
>
> Marty Jia wrote:
> > I'm exhausted to try all performance tuning ideas, like following
> > parameters
> >
> > shared_buffers
> > fsync
> > max_fsm_pages
> > max_connections
> > shared_buffers
> > work_mem
> > max_fsm_pages
> > effective_cache_size
> > random_page_cost
> >
> > I believe all above have right size and values, but I just can not get
>
> > higher tps more than 300 testd by pgbench
>
> What values did you use?
>
> >
> > Here is our hardware
> >
> >
> > Dual Intel Xeon 2.8GHz
> > 6GB RAM
> > Linux 2.4 kernel
> > RedHat Enterprise Linux AS 3
> > 200GB for PGDATA on 3Par, ext3
> > 50GB for WAL on 3Par, ext3
> >
> > With PostgreSql 8.1.4
> >
> > We don't have i/o bottle neck.
>
> Are you sure? What does iostat say during a pgbench? What parameters are
> you passing to pgbench?
>
> Well in theory, upgrading to 2.6 kernel will help as well as making your
> WAL ext2 instead of ext3.
>
> > Whatelse I can try to better tps? Someone told me I can should get tps
>
> > over 1500, it is hard to believe.
>
> 1500? Hmmm... I don't know about that, I can get 470tps or so on my
> measily dual core 3800 with 2gig of ram though.
>
> Joshua D. Drake
>
>
> >
> > Thanks
> >
> > Marty
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
@ 2006-08-22 15:26 ` Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Alex Turner @ 2006-08-22 15:26 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: Marty Jia <mjia@ask.com>; Joshua D. Drake <jd@commandprompt.com>; pgsql-performance@postgresql.org, DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
First things first, run a bonnie++ benchmark, and post the numbers. That
will give a good indication of raw IO performance, and is often the first
inidication of problems separate from the DB. We have seen pretty bad
performance from SANs in the past. How many FC lines do you have running to
your server, remember each line is limited to about 200MB/sec, to get good
throughput, you will need multiple connections.
When you run pgbench, run a iostat also and see what the numbers say.
Alex.
On 8/22/06, Mark Lewis <mark.lewis@mir3.com> wrote:
>
> Well, at least on my test machines running gnome-terminal, my pgbench
> runs tend to get throttled by gnome-terminal's lousy performance to no
> more than 300 tps or so. Running with 2>/dev/null to throw away all the
> detailed logging gives me 2-3x improvement in scores. Caveat: in my
> case the db is on the local machine, so who knows what all the
> interactions are.
>
> Also, when you initialized the pgbench db what scaling factor did you
> use? And does running pgbench with -v improve performance at all?
>
> -- Mark
>
> On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> > Joshua,
> >
> > Here is
> >
> > shared_buffers = 80000
> > fsync = on
> > max_fsm_pages = 350000
> > max_connections = 1000
> > work_mem = 65536
> > effective_cache_size = 610000
> > random_page_cost = 3
> >
> > Here is pgbench I used:
> >
> > pgbench -c 10 -t 10000 -d HQDB
> >
> > Thanks
> >
> > Marty
> >
> > -----Original Message-----
> > From: Joshua D. Drake [mailto:jd@commandprompt.com]
> > Sent: Monday, August 21, 2006 6:09 PM
> > To: Marty Jia
> > Cc: pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] How to get higher tps
> >
> > Marty Jia wrote:
> > > I'm exhausted to try all performance tuning ideas, like following
> > > parameters
> > >
> > > shared_buffers
> > > fsync
> > > max_fsm_pages
> > > max_connections
> > > shared_buffers
> > > work_mem
> > > max_fsm_pages
> > > effective_cache_size
> > > random_page_cost
> > >
> > > I believe all above have right size and values, but I just can not get
> >
> > > higher tps more than 300 testd by pgbench
> >
> > What values did you use?
> >
> > >
> > > Here is our hardware
> > >
> > >
> > > Dual Intel Xeon 2.8GHz
> > > 6GB RAM
> > > Linux 2.4 kernel
> > > RedHat Enterprise Linux AS 3
> > > 200GB for PGDATA on 3Par, ext3
> > > 50GB for WAL on 3Par, ext3
> > >
> > > With PostgreSql 8.1.4
> > >
> > > We don't have i/o bottle neck.
> >
> > Are you sure? What does iostat say during a pgbench? What parameters are
> > you passing to pgbench?
> >
> > Well in theory, upgrading to 2.6 kernel will help as well as making your
> > WAL ext2 instead of ext3.
> >
> > > Whatelse I can try to better tps? Someone told me I can should get tps
> >
> > > over 1500, it is hard to believe.
> >
> > 1500? Hmmm... I don't know about that, I can get 470tps or so on my
> > measily dual core 3800 with 2gig of ram though.
> >
> > Joshua D. Drake
> >
> >
> > >
> > > Thanks
> > >
> > > Marty
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 2: Don't 'kill -9' the postmaster
> > >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
@ 2006-08-22 15:27 ` Alex Turner <armtuk@gmail.com>
2006-08-22 15:44 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 15:46 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
0 siblings, 2 replies; 23+ messages in thread
From: Alex Turner @ 2006-08-22 15:27 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: Marty Jia <mjia@ask.com>; Joshua D. Drake <jd@commandprompt.com>; pgsql-performance@postgresql.org, DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Oh - and it's usefull to know if you are CPU bound, or IO bound. Check top
or vmstat to get an idea of that
Alex
On 8/22/06, Alex Turner <armtuk@gmail.com> wrote:
>
> First things first, run a bonnie++ benchmark, and post the numbers. That
> will give a good indication of raw IO performance, and is often the first
> inidication of problems separate from the DB. We have seen pretty bad
> performance from SANs in the past. How many FC lines do you have running to
> your server, remember each line is limited to about 200MB/sec, to get good
> throughput, you will need multiple connections.
>
> When you run pgbench, run a iostat also and see what the numbers say.
>
> Alex.
>
>
> On 8/22/06, Mark Lewis < mark.lewis@mir3.com> wrote:
> >
> > Well, at least on my test machines running gnome-terminal, my pgbench
> > runs tend to get throttled by gnome-terminal's lousy performance to no
> > more than 300 tps or so. Running with 2>/dev/null to throw away all the
> > detailed logging gives me 2-3x improvement in scores. Caveat: in my
> > case the db is on the local machine, so who knows what all the
> > interactions are.
> >
> > Also, when you initialized the pgbench db what scaling factor did you
> > use? And does running pgbench with -v improve performance at all?
> >
> > -- Mark
> >
> > On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> > > Joshua,
> > >
> > > Here is
> > >
> > > shared_buffers = 80000
> > > fsync = on
> > > max_fsm_pages = 350000
> > > max_connections = 1000
> > > work_mem = 65536
> > > effective_cache_size = 610000
> > > random_page_cost = 3
> > >
> > > Here is pgbench I used:
> > >
> > > pgbench -c 10 -t 10000 -d HQDB
> > >
> > > Thanks
> > >
> > > Marty
> > >
> > > -----Original Message-----
> > > From: Joshua D. Drake [mailto:jd@commandprompt.com]
> > > Sent: Monday, August 21, 2006 6:09 PM
> > > To: Marty Jia
> > > Cc: pgsql-performance@postgresql.org
> > > Subject: Re: [PERFORM] How to get higher tps
> > >
> > > Marty Jia wrote:
> > > > I'm exhausted to try all performance tuning ideas, like following
> > > > parameters
> > > >
> > > > shared_buffers
> > > > fsync
> > > > max_fsm_pages
> > > > max_connections
> > > > shared_buffers
> > > > work_mem
> > > > max_fsm_pages
> > > > effective_cache_size
> > > > random_page_cost
> > > >
> > > > I believe all above have right size and values, but I just can not
> > get
> > >
> > > > higher tps more than 300 testd by pgbench
> > >
> > > What values did you use?
> > >
> > > >
> > > > Here is our hardware
> > > >
> > > >
> > > > Dual Intel Xeon 2.8GHz
> > > > 6GB RAM
> > > > Linux 2.4 kernel
> > > > RedHat Enterprise Linux AS 3
> > > > 200GB for PGDATA on 3Par, ext3
> > > > 50GB for WAL on 3Par, ext3
> > > >
> > > > With PostgreSql 8.1.4
> > > >
> > > > We don't have i/o bottle neck.
> > >
> > > Are you sure? What does iostat say during a pgbench? What parameters
> > are
> > > you passing to pgbench?
> > >
> > > Well in theory, upgrading to 2.6 kernel will help as well as making
> > your
> > > WAL ext2 instead of ext3.
> > >
> > > > Whatelse I can try to better tps? Someone told me I can should get
> > tps
> > >
> > > > over 1500, it is hard to believe.
> > >
> > > 1500? Hmmm... I don't know about that, I can get 470tps or so on my
> > > measily dual core 3800 with 2gig of ram though.
> > >
> > > Joshua D. Drake
> > >
> > >
> > > >
> > > > Thanks
> > > >
> > > > Marty
> > > >
> > > > ---------------------------(end of
> > > > broadcast)---------------------------
> > > > TIP 2: Don't 'kill -9' the postmaster
> > > >
> > >
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
@ 2006-08-22 15:44 ` Marty Jia <mjia@ask.com>
1 sibling, 0 replies; 23+ messages in thread
From: Marty Jia @ 2006-08-22 15:44 UTC (permalink / raw)
To: Alex Turner <armtuk@gmail.com>; Mark Lewis <mark.lewis@mir3.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance; DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
----------- Here is vmstat
procs memory swap io system
cpu
r b swpd free buff cache si so bi bo in cs us sy
id wa
0 1 15416 18156 73372 4348488 1 1 3 2 4 1 2
1 2 2
----------- Here is iostat
avg-cpu: %user %nice %sys %iowait %idle
11.59 0.00 6.13 10.77 71.50
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 2.76 6.88 36.35 16036474 84688320
sda1 0.00 0.01 0.00 30100 1056
sda2 0.27 2.36 1.72 5509296 4017224
sda3 1.85 0.78 21.99 1819850 51242800
sda4 0.00 0.00 0.00 20 0
sda5 0.15 0.49 1.47 1131624 3425672
sda6 0.49 3.14 11.12 7320616 25899088
sda7 0.01 0.09 0.04 219960 102480
sdb 2.75 6.78 36.35 15803532 84688320
sdb1 0.00 0.01 0.00 24322 1056
sdb2 0.27 2.31 1.72 5391682 4017224
sdb3 1.84 0.79 21.99 1836088 51242800
sdb4 0.00 0.00 0.00 20 0
sdb5 0.15 0.49 1.47 1134546 3425672
sdb6 0.49 3.12 11.12 7273816 25899088
sdb7 0.01 0.06 0.04 138138 102480
sdc 0.00 0.00 0.00 632 0
sdd 0.00 0.00 0.00 80 0
sde 0.00 0.00 0.00 80 0
sdf 0.00 0.00 0.00 80 0
sdg 0.00 0.00 0.00 112 0
sdh 0.00 0.00 0.00 112 0
sdi 139.89 680.59 839.42 1585722266 1955771032
sdj 139.72 680.21 835.90 1584829368 1947590800
sdk 139.82 680.30 840.74 1585053608 1958864880
sdl 139.86 680.56 841.26 1585657408 1960079576
sdm 54.80 6.67 891.38 15547618 2076836720
sdn 54.71 6.66 891.35 15509096 2076776352
________________________________
From: Alex Turner [mailto:armtuk@gmail.com]
Sent: Tuesday, August 22, 2006 11:27 AM
To: Mark Lewis
Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps
Oh - and it's usefull to know if you are CPU bound, or IO bound. Check
top or vmstat to get an idea of that
Alex
On 8/22/06, Alex Turner < armtuk@gmail.com <mailto:armtuk@gmail.com> >
wrote:
First things first, run a bonnie++ benchmark, and post the
numbers. That will give a good indication of raw IO performance, and is
often the first inidication of problems separate from the DB. We have
seen pretty bad performance from SANs in the past. How many FC lines do
you have running to your server, remember each line is limited to about
200MB/sec, to get good throughput, you will need multiple connections.
When you run pgbench, run a iostat also and see what the numbers
say.
Alex.
On 8/22/06, Mark Lewis < mark.lewis@mir3.com
<mailto:mark.lewis@mir3.com> > wrote:
Well, at least on my test machines running
gnome-terminal, my pgbench
runs tend to get throttled by gnome-terminal's lousy
performance to no
more than 300 tps or so. Running with 2>/dev/null to
throw away all the
detailed logging gives me 2-3x improvement in scores.
Caveat: in my
case the db is on the local machine, so who knows what
all the
interactions are.
Also, when you initialized the pgbench db what scaling
factor did you
use? And does running pgbench with -v improve
performance at all?
-- Mark
On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> Joshua,
>
> Here is
>
> shared_buffers = 80000
> fsync = on
> max_fsm_pages = 350000
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 610000
> random_page_cost = 3
>
> Here is pgbench I used:
>
> pgbench -c 10 -t 10000 -d HQDB
>
> Thanks
>
> Marty
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Monday, August 21, 2006 6:09 PM
> To: Marty Jia
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to get higher tps
>
> Marty Jia wrote:
> > I'm exhausted to try all performance tuning ideas,
like following
> > parameters
> >
> > shared_buffers
> > fsync
> > max_fsm_pages
> > max_connections
> > shared_buffers
> > work_mem
> > max_fsm_pages
> > effective_cache_size
> > random_page_cost
> >
> > I believe all above have right size and values, but
I just can not get
>
> > higher tps more than 300 testd by pgbench
>
> What values did you use?
>
> >
> > Here is our hardware
> >
> >
> > Dual Intel Xeon 2.8GHz
> > 6GB RAM
> > Linux 2.4 kernel
> > RedHat Enterprise Linux AS 3
> > 200GB for PGDATA on 3Par, ext3
> > 50GB for WAL on 3Par, ext3
> >
> > With PostgreSql 8.1.4
> >
> > We don't have i/o bottle neck.
>
> Are you sure? What does iostat say during a pgbench?
What parameters are
> you passing to pgbench?
>
> Well in theory, upgrading to 2.6 kernel will help as
well as making your
> WAL ext2 instead of ext3.
>
> > Whatelse I can try to better tps? Someone told me I
can should get tps
>
> > over 1500, it is hard to believe.
>
> 1500? Hmmm... I don't know about that, I can get
470tps or so on my
> measily dual core 3800 with 2gig of ram though.
>
> Joshua D. Drake
>
>
> >
> > Thanks
> >
> > Marty
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an
appropriate
subscribe-nomail command to
majordomo@postgresql.org so that your
message can get through to the mailing list
cleanly
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
@ 2006-08-22 15:46 ` Marty Jia <mjia@ask.com>
2006-08-22 16:15 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
1 sibling, 1 reply; 23+ messages in thread
From: Marty Jia @ 2006-08-22 15:46 UTC (permalink / raw)
To: Alex Turner <armtuk@gmail.com>; Mark Lewis <mark.lewis@mir3.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance; DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Here is iostat when running pgbench:
avg-cpu: %user %nice %sys %iowait %idle
26.17 0.00 8.25 23.17 42.42
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 0.00 0.00 0.00 0 0
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 0.00 0.00 0.00 0 0
sda4 0.00 0.00 0.00 0 0
sda5 0.00 0.00 0.00 0 0
sda6 0.00 0.00 0.00 0 0
sda7 0.00 0.00 0.00 0 0
sdb 0.00 0.00 0.00 0 0
sdb1 0.00 0.00 0.00 0 0
sdb2 0.00 0.00 0.00 0 0
sdb3 0.00 0.00 0.00 0 0
sdb4 0.00 0.00 0.00 0 0
sdb5 0.00 0.00 0.00 0 0
sdb6 0.00 0.00 0.00 0 0
sdb7 0.00 0.00 0.00 0 0
sdc 0.00 0.00 0.00 0 0
sdd 0.00 0.00 0.00 0 0
sde 0.00 0.00 0.00 0 0
sdf 0.00 0.00 0.00 0 0
sdg 0.00 0.00 0.00 0 0
sdh 0.00 0.00 0.00 0 0
sdi 40.33 0.00 413.33 0 1240
sdj 34.33 0.00 394.67 0 1184
sdk 36.00 0.00 410.67 0 1232
sdl 37.00 0.00 429.33 0 1288
sdm 375.00 0.00 3120.00 0 9360
sdn 378.33 0.00 3120.00 0 9360
________________________________
From: Alex Turner [mailto:armtuk@gmail.com]
Sent: Tuesday, August 22, 2006 11:27 AM
To: Mark Lewis
Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps
Oh - and it's usefull to know if you are CPU bound, or IO bound. Check
top or vmstat to get an idea of that
Alex
On 8/22/06, Alex Turner < armtuk@gmail.com <mailto:armtuk@gmail.com> >
wrote:
First things first, run a bonnie++ benchmark, and post the
numbers. That will give a good indication of raw IO performance, and is
often the first inidication of problems separate from the DB. We have
seen pretty bad performance from SANs in the past. How many FC lines do
you have running to your server, remember each line is limited to about
200MB/sec, to get good throughput, you will need multiple connections.
When you run pgbench, run a iostat also and see what the numbers
say.
Alex.
On 8/22/06, Mark Lewis < mark.lewis@mir3.com
<mailto:mark.lewis@mir3.com> > wrote:
Well, at least on my test machines running
gnome-terminal, my pgbench
runs tend to get throttled by gnome-terminal's lousy
performance to no
more than 300 tps or so. Running with 2>/dev/null to
throw away all the
detailed logging gives me 2-3x improvement in scores.
Caveat: in my
case the db is on the local machine, so who knows what
all the
interactions are.
Also, when you initialized the pgbench db what scaling
factor did you
use? And does running pgbench with -v improve
performance at all?
-- Mark
On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> Joshua,
>
> Here is
>
> shared_buffers = 80000
> fsync = on
> max_fsm_pages = 350000
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 610000
> random_page_cost = 3
>
> Here is pgbench I used:
>
> pgbench -c 10 -t 10000 -d HQDB
>
> Thanks
>
> Marty
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Monday, August 21, 2006 6:09 PM
> To: Marty Jia
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to get higher tps
>
> Marty Jia wrote:
> > I'm exhausted to try all performance tuning ideas,
like following
> > parameters
> >
> > shared_buffers
> > fsync
> > max_fsm_pages
> > max_connections
> > shared_buffers
> > work_mem
> > max_fsm_pages
> > effective_cache_size
> > random_page_cost
> >
> > I believe all above have right size and values, but
I just can not get
>
> > higher tps more than 300 testd by pgbench
>
> What values did you use?
>
> >
> > Here is our hardware
> >
> >
> > Dual Intel Xeon 2.8GHz
> > 6GB RAM
> > Linux 2.4 kernel
> > RedHat Enterprise Linux AS 3
> > 200GB for PGDATA on 3Par, ext3
> > 50GB for WAL on 3Par, ext3
> >
> > With PostgreSql 8.1.4
> >
> > We don't have i/o bottle neck.
>
> Are you sure? What does iostat say during a pgbench?
What parameters are
> you passing to pgbench?
>
> Well in theory, upgrading to 2.6 kernel will help as
well as making your
> WAL ext2 instead of ext3.
>
> > Whatelse I can try to better tps? Someone told me I
can should get tps
>
> > over 1500, it is hard to believe.
>
> 1500? Hmmm... I don't know about that, I can get
470tps or so on my
> measily dual core 3800 with 2gig of ram though.
>
> Joshua D. Drake
>
>
> >
> > Thanks
> >
> > Marty
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an
appropriate
subscribe-nomail command to
majordomo@postgresql.org so that your
message can get through to the mailing list
cleanly
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:46 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-22 16:15 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-22 19:22 ` Re: How to get higher tps Bucky Jordan <bjordan@lumeta.com>
0 siblings, 1 reply; 23+ messages in thread
From: Joshua D. Drake @ 2006-08-22 16:15 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; +Cc: Alex Turner <armtuk@gmail.com>; Mark Lewis <mark.lewis@mir3.com>; pgsql-performance@postgresql.org, DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Marty Jia wrote:
> Here is iostat when running pgbench:
>
> avg-cpu: %user %nice %sys %iowait %idle
> 26.17 0.00 8.25 23.17 42.42
You are are a little io bound and fairly cpu bound. I would be curious
if your performance goes down if you increase the number of connections
you are using.
Joshua D. Drake
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 0.00 0.00 0.00 0 0
> sda1 0.00 0.00 0.00 0 0
> sda2 0.00 0.00 0.00 0 0
> sda3 0.00 0.00 0.00 0 0
> sda4 0.00 0.00 0.00 0 0
> sda5 0.00 0.00 0.00 0 0
> sda6 0.00 0.00 0.00 0 0
> sda7 0.00 0.00 0.00 0 0
> sdb 0.00 0.00 0.00 0 0
> sdb1 0.00 0.00 0.00 0 0
> sdb2 0.00 0.00 0.00 0 0
> sdb3 0.00 0.00 0.00 0 0
> sdb4 0.00 0.00 0.00 0 0
> sdb5 0.00 0.00 0.00 0 0
> sdb6 0.00 0.00 0.00 0 0
> sdb7 0.00 0.00 0.00 0 0
> sdc 0.00 0.00 0.00 0 0
> sdd 0.00 0.00 0.00 0 0
> sde 0.00 0.00 0.00 0 0
> sdf 0.00 0.00 0.00 0 0
> sdg 0.00 0.00 0.00 0 0
> sdh 0.00 0.00 0.00 0 0
> sdi 40.33 0.00 413.33 0 1240
> sdj 34.33 0.00 394.67 0 1184
> sdk 36.00 0.00 410.67 0 1232
> sdl 37.00 0.00 429.33 0 1288
> sdm 375.00 0.00 3120.00 0 9360
> sdn 378.33 0.00 3120.00 0 9360
>
> ________________________________
>
> From: Alex Turner [mailto:armtuk@gmail.com]
> Sent: Tuesday, August 22, 2006 11:27 AM
> To: Mark Lewis
> Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org; DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: Re: [PERFORM] How to get higher tps
>
>
> Oh - and it's usefull to know if you are CPU bound, or IO bound. Check
> top or vmstat to get an idea of that
>
> Alex
>
>
> On 8/22/06, Alex Turner < armtuk@gmail.com <mailto:armtuk@gmail.com> >
> wrote:
>
> First things first, run a bonnie++ benchmark, and post the
> numbers. That will give a good indication of raw IO performance, and is
> often the first inidication of problems separate from the DB. We have
> seen pretty bad performance from SANs in the past. How many FC lines do
> you have running to your server, remember each line is limited to about
> 200MB/sec, to get good throughput, you will need multiple connections.
>
> When you run pgbench, run a iostat also and see what the numbers
> say.
>
>
> Alex.
>
>
>
> On 8/22/06, Mark Lewis < mark.lewis@mir3.com
> <mailto:mark.lewis@mir3.com> > wrote:
>
> Well, at least on my test machines running
> gnome-terminal, my pgbench
> runs tend to get throttled by gnome-terminal's lousy
> performance to no
> more than 300 tps or so. Running with 2>/dev/null to
> throw away all the
> detailed logging gives me 2-3x improvement in scores.
> Caveat: in my
> case the db is on the local machine, so who knows what
> all the
> interactions are.
>
> Also, when you initialized the pgbench db what scaling
> factor did you
> use? And does running pgbench with -v improve
> performance at all?
>
> -- Mark
>
> On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> > Joshua,
> >
> > Here is
> >
> > shared_buffers = 80000
> > fsync = on
> > max_fsm_pages = 350000
> > max_connections = 1000
> > work_mem = 65536
> > effective_cache_size = 610000
> > random_page_cost = 3
> >
> > Here is pgbench I used:
> >
> > pgbench -c 10 -t 10000 -d HQDB
> >
> > Thanks
> >
> > Marty
> >
> > -----Original Message-----
> > From: Joshua D. Drake [mailto:jd@commandprompt.com]
> > Sent: Monday, August 21, 2006 6:09 PM
> > To: Marty Jia
> > Cc: pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] How to get higher tps
> >
> > Marty Jia wrote:
> > > I'm exhausted to try all performance tuning ideas,
> like following
> > > parameters
> > >
> > > shared_buffers
> > > fsync
> > > max_fsm_pages
> > > max_connections
> > > shared_buffers
> > > work_mem
> > > max_fsm_pages
> > > effective_cache_size
> > > random_page_cost
> > >
> > > I believe all above have right size and values, but
> I just can not get
> >
> > > higher tps more than 300 testd by pgbench
> >
> > What values did you use?
> >
> > >
> > > Here is our hardware
> > >
> > >
> > > Dual Intel Xeon 2.8GHz
> > > 6GB RAM
> > > Linux 2.4 kernel
> > > RedHat Enterprise Linux AS 3
> > > 200GB for PGDATA on 3Par, ext3
> > > 50GB for WAL on 3Par, ext3
> > >
> > > With PostgreSql 8.1.4
> > >
> > > We don't have i/o bottle neck.
> >
> > Are you sure? What does iostat say during a pgbench?
> What parameters are
> > you passing to pgbench?
> >
> > Well in theory, upgrading to 2.6 kernel will help as
> well as making your
> > WAL ext2 instead of ext3.
> >
> > > Whatelse I can try to better tps? Someone told me I
> can should get tps
> >
> > > over 1500, it is hard to believe.
> >
> > 1500? Hmmm... I don't know about that, I can get
> 470tps or so on my
> > measily dual core 3800 with 2gig of ram though.
> >
> > Joshua D. Drake
> >
> >
> > >
> > > Thanks
> > >
> > > Marty
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 2: Don't 'kill -9' the postmaster
> > >
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an
> appropriate
> subscribe-nomail command to
> majordomo@postgresql.org so that your
> message can get through to the mailing list
> cleanly
>
>
>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:46 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 16:15 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
@ 2006-08-22 19:22 ` Bucky Jordan <bjordan@lumeta.com>
2006-08-22 19:37 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
0 siblings, 1 reply; 23+ messages in thread
From: Bucky Jordan @ 2006-08-22 19:22 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; Marty Jia <mjia@ask.com>; +Cc: Alex Turner <armtuk@gmail.com>; Mark Lewis <mark.lewis@mir3.com>; pgsql-performance; DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Marty,
Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install
on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
cache/socket) with 6x300GB 10k SAS drives:
pgbench -c 10 -t 10000 -d bench 2>/dev/null
pghost: pgport: (null) nclients: 10 nxacts: 10000 dbName: bench
`transaction type: TPC-B (sort of)
scaling factor: 20
number of clients: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 561.056729 (including connections establishing)
tps = 561.127760 (excluding connections establishing)
Here's some iostat samples during the test:
tty mfid0 da0 cd0
cpu
tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy in
id
6 77 16.01 1642 25.67 0.00 0 0.00 0.00 0 0.00 3 0 8
2 87
8 157 17.48 3541 60.43 0.00 0 0.00 0.00 0 0.00 24 0 28
4 43
5 673 17.66 2287 39.44 0.00 0 0.00 0.00 0 0.00 10 0 13
2 75
6 2818 16.37 2733 43.68 0.00 0 0.00 0.00 0 0.00 17 0 23
3 56
1 765 18.05 2401 42.32 0.00 0 0.00 0.00 0 0.00 15 0 17
3 65
Note- the above was with no tuning to the kernel or postgresql.conf.
Now for my question- it seems that I've still got quite a bit of
headroom on the hardware I'm running the above tests on, since I know
the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
appears mostly idle. This would indicate I should be able to get some
significantly better numbers with postgresql.conf tweaks correct?
I guess the other problem is ensuring that we're not testing RAM speeds,
since most of the data is probably in memory (BSD io buffers)? Although,
for the initial run, that doesn't seem to be the case, since subsequent
runs without rebuilding the benchmark db are slightly not believable
(i.e. 1,200 going up to >2,500 tps over 5 back-to-back runs). So, as
long as I re-initialize the benchdb before each run, it should be a
realistic test, right?
Thanks,
Bucky
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joshua D.
Drake
Sent: Tuesday, August 22, 2006 12:16 PM
To: Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps
Marty Jia wrote:
> Here is iostat when running pgbench:
>
> avg-cpu: %user %nice %sys %iowait %idle
> 26.17 0.00 8.25 23.17 42.42
You are are a little io bound and fairly cpu bound. I would be curious
if your performance goes down if you increase the number of connections
you are using.
Joshua D. Drake
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 0.00 0.00 0.00 0 0
> sda1 0.00 0.00 0.00 0 0
> sda2 0.00 0.00 0.00 0 0
> sda3 0.00 0.00 0.00 0 0
> sda4 0.00 0.00 0.00 0 0
> sda5 0.00 0.00 0.00 0 0
> sda6 0.00 0.00 0.00 0 0
> sda7 0.00 0.00 0.00 0 0
> sdb 0.00 0.00 0.00 0 0
> sdb1 0.00 0.00 0.00 0 0
> sdb2 0.00 0.00 0.00 0 0
> sdb3 0.00 0.00 0.00 0 0
> sdb4 0.00 0.00 0.00 0 0
> sdb5 0.00 0.00 0.00 0 0
> sdb6 0.00 0.00 0.00 0 0
> sdb7 0.00 0.00 0.00 0 0
> sdc 0.00 0.00 0.00 0 0
> sdd 0.00 0.00 0.00 0 0
> sde 0.00 0.00 0.00 0 0
> sdf 0.00 0.00 0.00 0 0
> sdg 0.00 0.00 0.00 0 0
> sdh 0.00 0.00 0.00 0 0
> sdi 40.33 0.00 413.33 0 1240
> sdj 34.33 0.00 394.67 0 1184
> sdk 36.00 0.00 410.67 0 1232
> sdl 37.00 0.00 429.33 0 1288
> sdm 375.00 0.00 3120.00 0 9360
> sdn 378.33 0.00 3120.00 0 9360
>
> ________________________________
>
> From: Alex Turner [mailto:armtuk@gmail.com]
> Sent: Tuesday, August 22, 2006 11:27 AM
> To: Mark Lewis
> Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org;
DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: Re: [PERFORM] How to get higher tps
>
>
> Oh - and it's usefull to know if you are CPU bound, or IO bound.
Check
> top or vmstat to get an idea of that
>
> Alex
>
>
> On 8/22/06, Alex Turner < armtuk@gmail.com <mailto:armtuk@gmail.com> >
> wrote:
>
> First things first, run a bonnie++ benchmark, and post the
> numbers. That will give a good indication of raw IO performance, and
is
> often the first inidication of problems separate from the DB. We have
> seen pretty bad performance from SANs in the past. How many FC lines
do
> you have running to your server, remember each line is limited to
about
> 200MB/sec, to get good throughput, you will need multiple connections.
>
> When you run pgbench, run a iostat also and see what the numbers
> say.
>
>
> Alex.
>
>
>
> On 8/22/06, Mark Lewis < mark.lewis@mir3.com
> <mailto:mark.lewis@mir3.com> > wrote:
>
> Well, at least on my test machines running
> gnome-terminal, my pgbench
> runs tend to get throttled by gnome-terminal's lousy
> performance to no
> more than 300 tps or so. Running with 2>/dev/null to
> throw away all the
> detailed logging gives me 2-3x improvement in scores.
> Caveat: in my
> case the db is on the local machine, so who knows what
> all the
> interactions are.
>
> Also, when you initialized the pgbench db what scaling
> factor did you
> use? And does running pgbench with -v improve
> performance at all?
>
> -- Mark
>
> On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> > Joshua,
> >
> > Here is
> >
> > shared_buffers = 80000
> > fsync = on
> > max_fsm_pages = 350000
> > max_connections = 1000
> > work_mem = 65536
> > effective_cache_size = 610000
> > random_page_cost = 3
> >
> > Here is pgbench I used:
> >
> > pgbench -c 10 -t 10000 -d HQDB
> >
> > Thanks
> >
> > Marty
> >
> > -----Original Message-----
> > From: Joshua D. Drake [mailto:jd@commandprompt.com]
> > Sent: Monday, August 21, 2006 6:09 PM
> > To: Marty Jia
> > Cc: pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] How to get higher tps
> >
> > Marty Jia wrote:
> > > I'm exhausted to try all performance tuning ideas,
> like following
> > > parameters
> > >
> > > shared_buffers
> > > fsync
> > > max_fsm_pages
> > > max_connections
> > > shared_buffers
> > > work_mem
> > > max_fsm_pages
> > > effective_cache_size
> > > random_page_cost
> > >
> > > I believe all above have right size and values, but
> I just can not get
> >
> > > higher tps more than 300 testd by pgbench
> >
> > What values did you use?
> >
> > >
> > > Here is our hardware
> > >
> > >
> > > Dual Intel Xeon 2.8GHz
> > > 6GB RAM
> > > Linux 2.4 kernel
> > > RedHat Enterprise Linux AS 3
> > > 200GB for PGDATA on 3Par, ext3
> > > 50GB for WAL on 3Par, ext3
> > >
> > > With PostgreSql 8.1.4
> > >
> > > We don't have i/o bottle neck.
> >
> > Are you sure? What does iostat say during a pgbench?
> What parameters are
> > you passing to pgbench?
> >
> > Well in theory, upgrading to 2.6 kernel will help as
> well as making your
> > WAL ext2 instead of ext3.
> >
> > > Whatelse I can try to better tps? Someone told me I
> can should get tps
> >
> > > over 1500, it is hard to believe.
> >
> > 1500? Hmmm... I don't know about that, I can get
> 470tps or so on my
> > measily dual core 3800 with 2gig of ram though.
> >
> > Joshua D. Drake
> >
> >
> > >
> > > Thanks
> > >
> > > Marty
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 2: Don't 'kill -9' the postmaster
> > >
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an
> appropriate
> subscribe-nomail command to
> majordomo@postgresql.org so that your
> message can get through to the mailing list
> cleanly
>
>
>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:46 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 16:15 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 19:22 ` Re: How to get higher tps Bucky Jordan <bjordan@lumeta.com>
@ 2006-08-22 19:37 ` Marty Jia <mjia@ask.com>
2006-08-22 19:38 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 19:49 ` Re: How to get higher tps Bucky Jordan <bjordan@lumeta.com>
0 siblings, 2 replies; 23+ messages in thread
From: Marty Jia @ 2006-08-22 19:37 UTC (permalink / raw)
To: Bucky Jordan <bjordan@lumeta.com>; Joshua D. Drake <jd@commandprompt.com>; +Cc: Alex Turner <armtuk@gmail.com>; Mark Lewis <mark.lewis@mir3.com>; pgsql-performance; DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Bucky
My best result is around 380. I believe your hardware is more efficient,
because no matter how I change the conf parameters, no improvement can
be obtained. I even turned fsync off.
What is your values for the following parameters?
shared_buffers = 80000
max_fsm_pages = 350000
max_connections = 1000
work_mem = 65536
effective_cache_size = 610000
random_page_cost = 3
Thanks
Marty
-----Original Message-----
From: Bucky Jordan [mailto:bjordan@lumeta.com]
Sent: Tuesday, August 22, 2006 3:23 PM
To: Joshua D. Drake; Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: RE: [PERFORM] How to get higher tps
Marty,
Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install
on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
cache/socket) with 6x300GB 10k SAS drives:
pgbench -c 10 -t 10000 -d bench 2>/dev/null
pghost: pgport: (null) nclients: 10 nxacts: 10000 dbName: bench
`transaction type: TPC-B (sort of) scaling factor: 20 number of clients:
10 number of transactions per client: 10000 number of transactions
actually processed: 100000/100000 tps = 561.056729 (including
connections establishing) tps = 561.127760 (excluding connections
establishing)
Here's some iostat samples during the test:
tty mfid0 da0 cd0
cpu
tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy in
id
6 77 16.01 1642 25.67 0.00 0 0.00 0.00 0 0.00 3 0 8
2 87
8 157 17.48 3541 60.43 0.00 0 0.00 0.00 0 0.00 24 0 28
4 43
5 673 17.66 2287 39.44 0.00 0 0.00 0.00 0 0.00 10 0 13
2 75
6 2818 16.37 2733 43.68 0.00 0 0.00 0.00 0 0.00 17 0 23
3 56
1 765 18.05 2401 42.32 0.00 0 0.00 0.00 0 0.00 15 0 17
3 65
Note- the above was with no tuning to the kernel or postgresql.conf.
Now for my question- it seems that I've still got quite a bit of
headroom on the hardware I'm running the above tests on, since I know
the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
appears mostly idle. This would indicate I should be able to get some
significantly better numbers with postgresql.conf tweaks correct?
I guess the other problem is ensuring that we're not testing RAM speeds,
since most of the data is probably in memory (BSD io buffers)? Although,
for the initial run, that doesn't seem to be the case, since subsequent
runs without rebuilding the benchmark db are slightly not believable
(i.e. 1,200 going up to >2,500 tps over 5 back-to-back runs). So, as
long as I re-initialize the benchdb before each run, it should be a
realistic test, right?
Thanks,
Bucky
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joshua D.
Drake
Sent: Tuesday, August 22, 2006 12:16 PM
To: Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps
Marty Jia wrote:
> Here is iostat when running pgbench:
>
> avg-cpu: %user %nice %sys %iowait %idle
> 26.17 0.00 8.25 23.17 42.42
You are are a little io bound and fairly cpu bound. I would be curious
if your performance goes down if you increase the number of connections
you are using.
Joshua D. Drake
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 0.00 0.00 0.00 0 0
> sda1 0.00 0.00 0.00 0 0
> sda2 0.00 0.00 0.00 0 0
> sda3 0.00 0.00 0.00 0 0
> sda4 0.00 0.00 0.00 0 0
> sda5 0.00 0.00 0.00 0 0
> sda6 0.00 0.00 0.00 0 0
> sda7 0.00 0.00 0.00 0 0
> sdb 0.00 0.00 0.00 0 0
> sdb1 0.00 0.00 0.00 0 0
> sdb2 0.00 0.00 0.00 0 0
> sdb3 0.00 0.00 0.00 0 0
> sdb4 0.00 0.00 0.00 0 0
> sdb5 0.00 0.00 0.00 0 0
> sdb6 0.00 0.00 0.00 0 0
> sdb7 0.00 0.00 0.00 0 0
> sdc 0.00 0.00 0.00 0 0
> sdd 0.00 0.00 0.00 0 0
> sde 0.00 0.00 0.00 0 0
> sdf 0.00 0.00 0.00 0 0
> sdg 0.00 0.00 0.00 0 0
> sdh 0.00 0.00 0.00 0 0
> sdi 40.33 0.00 413.33 0 1240
> sdj 34.33 0.00 394.67 0 1184
> sdk 36.00 0.00 410.67 0 1232
> sdl 37.00 0.00 429.33 0 1288
> sdm 375.00 0.00 3120.00 0 9360
> sdn 378.33 0.00 3120.00 0 9360
>
> ________________________________
>
> From: Alex Turner [mailto:armtuk@gmail.com]
> Sent: Tuesday, August 22, 2006 11:27 AM
> To: Mark Lewis
> Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org;
DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: Re: [PERFORM] How to get higher tps
>
>
> Oh - and it's usefull to know if you are CPU bound, or IO bound.
Check
> top or vmstat to get an idea of that
>
> Alex
>
>
> On 8/22/06, Alex Turner < armtuk@gmail.com <mailto:armtuk@gmail.com> >
> wrote:
>
> First things first, run a bonnie++ benchmark, and post the
numbers.
> That will give a good indication of raw IO performance, and
is
> often the first inidication of problems separate from the DB. We have
> seen pretty bad performance from SANs in the past. How many FC lines
do
> you have running to your server, remember each line is limited to
about
> 200MB/sec, to get good throughput, you will need multiple connections.
>
> When you run pgbench, run a iostat also and see what the numbers
say.
>
>
> Alex.
>
>
>
> On 8/22/06, Mark Lewis < mark.lewis@mir3.com
> <mailto:mark.lewis@mir3.com> > wrote:
>
> Well, at least on my test machines running
gnome-terminal, my
> pgbench
> runs tend to get throttled by gnome-terminal's lousy
performance to
> no
> more than 300 tps or so. Running with 2>/dev/null to
throw away all
> the
> detailed logging gives me 2-3x improvement in scores.
> Caveat: in my
> case the db is on the local machine, so who knows what
all the
> interactions are.
>
> Also, when you initialized the pgbench db what scaling
factor did
> you
> use? And does running pgbench with -v improve
performance at all?
>
> -- Mark
>
> On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> > Joshua,
> >
> > Here is
> >
> > shared_buffers = 80000
> > fsync = on
> > max_fsm_pages = 350000
> > max_connections = 1000
> > work_mem = 65536
> > effective_cache_size = 610000
> > random_page_cost = 3
> >
> > Here is pgbench I used:
> >
> > pgbench -c 10 -t 10000 -d HQDB
> >
> > Thanks
> >
> > Marty
> >
> > -----Original Message-----
> > From: Joshua D. Drake [mailto:jd@commandprompt.com]
> > Sent: Monday, August 21, 2006 6:09 PM
> > To: Marty Jia
> > Cc: pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] How to get higher tps
> >
> > Marty Jia wrote:
> > > I'm exhausted to try all performance tuning ideas,
like
> following
> > > parameters
> > >
> > > shared_buffers
> > > fsync
> > > max_fsm_pages
> > > max_connections
> > > shared_buffers
> > > work_mem
> > > max_fsm_pages
> > > effective_cache_size
> > > random_page_cost
> > >
> > > I believe all above have right size and values, but
I just can
> not get
> >
> > > higher tps more than 300 testd by pgbench
> >
> > What values did you use?
> >
> > >
> > > Here is our hardware
> > >
> > >
> > > Dual Intel Xeon 2.8GHz
> > > 6GB RAM
> > > Linux 2.4 kernel
> > > RedHat Enterprise Linux AS 3
> > > 200GB for PGDATA on 3Par, ext3
> > > 50GB for WAL on 3Par, ext3
> > >
> > > With PostgreSql 8.1.4
> > >
> > > We don't have i/o bottle neck.
> >
> > Are you sure? What does iostat say during a pgbench?
> What parameters are
> > you passing to pgbench?
> >
> > Well in theory, upgrading to 2.6 kernel will help as
well as
> making your
> > WAL ext2 instead of ext3.
> >
> > > Whatelse I can try to better tps? Someone told me I
can should
> get tps
> >
> > > over 1500, it is hard to believe.
> >
> > 1500? Hmmm... I don't know about that, I can get
470tps or so on
> my
> > measily dual core 3800 with 2gig of ram though.
> >
> > Joshua D. Drake
> >
> >
> > >
> > > Thanks
> > >
> > > Marty
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 2: Don't 'kill -9' the postmaster
> > >
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an
appropriate
> subscribe-nomail command to
> majordomo@postgresql.org so that your
> message can get through to the mailing list
cleanly
>
>
>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:46 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 16:15 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 19:22 ` Re: How to get higher tps Bucky Jordan <bjordan@lumeta.com>
2006-08-22 19:37 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-22 19:38 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-22 20:35 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
1 sibling, 1 reply; 23+ messages in thread
From: Joshua D. Drake @ 2006-08-22 19:38 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; +Cc: Bucky Jordan <bjordan@lumeta.com>; Alex Turner <armtuk@gmail.com>; Mark Lewis <mark.lewis@mir3.com>; pgsql-performance@postgresql.org, DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Marty Jia wrote:
> Bucky
>
> My best result is around 380. I believe your hardware is more efficient,
> because no matter how I change the conf parameters, no improvement can
> be obtained. I even turned fsync off.
Do you stay constant if you use 40 clients versus 20?
>
> What is your values for the following parameters?
>
> shared_buffers = 80000
> max_fsm_pages = 350000
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 610000
> random_page_cost = 3
>
> Thanks
> Marty
>
> -----Original Message-----
> From: Bucky Jordan [mailto:bjordan@lumeta.com]
> Sent: Tuesday, August 22, 2006 3:23 PM
> To: Joshua D. Drake; Marty Jia
> Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: RE: [PERFORM] How to get higher tps
>
> Marty,
>
> Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install
> on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
> cache/socket) with 6x300GB 10k SAS drives:
>
> pgbench -c 10 -t 10000 -d bench 2>/dev/null
> pghost: pgport: (null) nclients: 10 nxacts: 10000 dbName: bench
> `transaction type: TPC-B (sort of) scaling factor: 20 number of clients:
> 10 number of transactions per client: 10000 number of transactions
> actually processed: 100000/100000 tps = 561.056729 (including
> connections establishing) tps = 561.127760 (excluding connections
> establishing)
>
> Here's some iostat samples during the test:
> tty mfid0 da0 cd0
> cpu
> tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy in
> id
> 6 77 16.01 1642 25.67 0.00 0 0.00 0.00 0 0.00 3 0 8
> 2 87
> 8 157 17.48 3541 60.43 0.00 0 0.00 0.00 0 0.00 24 0 28
> 4 43
> 5 673 17.66 2287 39.44 0.00 0 0.00 0.00 0 0.00 10 0 13
> 2 75
> 6 2818 16.37 2733 43.68 0.00 0 0.00 0.00 0 0.00 17 0 23
> 3 56
> 1 765 18.05 2401 42.32 0.00 0 0.00 0.00 0 0.00 15 0 17
> 3 65
>
> Note- the above was with no tuning to the kernel or postgresql.conf.
>
> Now for my question- it seems that I've still got quite a bit of
> headroom on the hardware I'm running the above tests on, since I know
> the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
> appears mostly idle. This would indicate I should be able to get some
> significantly better numbers with postgresql.conf tweaks correct?
>
> I guess the other problem is ensuring that we're not testing RAM speeds,
> since most of the data is probably in memory (BSD io buffers)? Although,
> for the initial run, that doesn't seem to be the case, since subsequent
> runs without rebuilding the benchmark db are slightly not believable
> (i.e. 1,200 going up to >2,500 tps over 5 back-to-back runs). So, as
> long as I re-initialize the benchdb before each run, it should be a
> realistic test, right?
>
> Thanks,
>
> Bucky
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joshua D.
> Drake
> Sent: Tuesday, August 22, 2006 12:16 PM
> To: Marty Jia
> Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: Re: [PERFORM] How to get higher tps
>
> Marty Jia wrote:
>> Here is iostat when running pgbench:
>>
>> avg-cpu: %user %nice %sys %iowait %idle
>> 26.17 0.00 8.25 23.17 42.42
>
> You are are a little io bound and fairly cpu bound. I would be curious
> if your performance goes down if you increase the number of connections
> you are using.
>
> Joshua D. Drake
>
>
>>
>> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
>> sda 0.00 0.00 0.00 0 0
>> sda1 0.00 0.00 0.00 0 0
>> sda2 0.00 0.00 0.00 0 0
>> sda3 0.00 0.00 0.00 0 0
>> sda4 0.00 0.00 0.00 0 0
>> sda5 0.00 0.00 0.00 0 0
>> sda6 0.00 0.00 0.00 0 0
>> sda7 0.00 0.00 0.00 0 0
>> sdb 0.00 0.00 0.00 0 0
>> sdb1 0.00 0.00 0.00 0 0
>> sdb2 0.00 0.00 0.00 0 0
>> sdb3 0.00 0.00 0.00 0 0
>> sdb4 0.00 0.00 0.00 0 0
>> sdb5 0.00 0.00 0.00 0 0
>> sdb6 0.00 0.00 0.00 0 0
>> sdb7 0.00 0.00 0.00 0 0
>> sdc 0.00 0.00 0.00 0 0
>> sdd 0.00 0.00 0.00 0 0
>> sde 0.00 0.00 0.00 0 0
>> sdf 0.00 0.00 0.00 0 0
>> sdg 0.00 0.00 0.00 0 0
>> sdh 0.00 0.00 0.00 0 0
>> sdi 40.33 0.00 413.33 0 1240
>> sdj 34.33 0.00 394.67 0 1184
>> sdk 36.00 0.00 410.67 0 1232
>> sdl 37.00 0.00 429.33 0 1288
>> sdm 375.00 0.00 3120.00 0 9360
>> sdn 378.33 0.00 3120.00 0 9360
>>
>> ________________________________
>>
>> From: Alex Turner [mailto:armtuk@gmail.com]
>> Sent: Tuesday, August 22, 2006 11:27 AM
>> To: Mark Lewis
>> Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org;
> DBAs;
>> Rich Wilson; Ernest Wurzbach
>> Subject: Re: [PERFORM] How to get higher tps
>>
>>
>> Oh - and it's usefull to know if you are CPU bound, or IO bound.
> Check
>> top or vmstat to get an idea of that
>>
>> Alex
>>
>>
>> On 8/22/06, Alex Turner < armtuk@gmail.com <mailto:armtuk@gmail.com> >
>> wrote:
>>
>> First things first, run a bonnie++ benchmark, and post the
> numbers.
>> That will give a good indication of raw IO performance, and
> is
>> often the first inidication of problems separate from the DB. We have
>
>> seen pretty bad performance from SANs in the past. How many FC lines
> do
>> you have running to your server, remember each line is limited to
> about
>> 200MB/sec, to get good throughput, you will need multiple connections.
>
>>
>> When you run pgbench, run a iostat also and see what the numbers
> say.
>>
>>
>> Alex.
>>
>>
>>
>> On 8/22/06, Mark Lewis < mark.lewis@mir3.com
>> <mailto:mark.lewis@mir3.com> > wrote:
>>
>> Well, at least on my test machines running
> gnome-terminal, my
>> pgbench
>> runs tend to get throttled by gnome-terminal's lousy
> performance to
>> no
>> more than 300 tps or so. Running with 2>/dev/null to
> throw away all
>> the
>> detailed logging gives me 2-3x improvement in scores.
>> Caveat: in my
>> case the db is on the local machine, so who knows what
> all the
>> interactions are.
>>
>> Also, when you initialized the pgbench db what scaling
> factor did
>> you
>> use? And does running pgbench with -v improve
> performance at all?
>>
>> -- Mark
>>
>> On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
>> > Joshua,
>> >
>> > Here is
>> >
>> > shared_buffers = 80000
>> > fsync = on
>> > max_fsm_pages = 350000
>> > max_connections = 1000
>> > work_mem = 65536
>> > effective_cache_size = 610000
>> > random_page_cost = 3
>> >
>> > Here is pgbench I used:
>> >
>> > pgbench -c 10 -t 10000 -d HQDB
>> >
>> > Thanks
>> >
>> > Marty
>> >
>> > -----Original Message-----
>> > From: Joshua D. Drake [mailto:jd@commandprompt.com]
>> > Sent: Monday, August 21, 2006 6:09 PM
>> > To: Marty Jia
>> > Cc: pgsql-performance@postgresql.org
>> > Subject: Re: [PERFORM] How to get higher tps
>> >
>> > Marty Jia wrote:
>> > > I'm exhausted to try all performance tuning ideas,
> like
>> following
>> > > parameters
>> > >
>> > > shared_buffers
>> > > fsync
>> > > max_fsm_pages
>> > > max_connections
>> > > shared_buffers
>> > > work_mem
>> > > max_fsm_pages
>> > > effective_cache_size
>> > > random_page_cost
>> > >
>> > > I believe all above have right size and values, but
> I just can
>> not get
>> >
>> > > higher tps more than 300 testd by pgbench
>> >
>> > What values did you use?
>> >
>> > >
>> > > Here is our hardware
>> > >
>> > >
>> > > Dual Intel Xeon 2.8GHz
>> > > 6GB RAM
>> > > Linux 2.4 kernel
>> > > RedHat Enterprise Linux AS 3
>> > > 200GB for PGDATA on 3Par, ext3
>> > > 50GB for WAL on 3Par, ext3
>> > >
>> > > With PostgreSql 8.1.4
>> > >
>> > > We don't have i/o bottle neck.
>> >
>> > Are you sure? What does iostat say during a pgbench?
>> What parameters are
>> > you passing to pgbench?
>> >
>> > Well in theory, upgrading to 2.6 kernel will help as
> well as
>> making your
>> > WAL ext2 instead of ext3.
>> >
>> > > Whatelse I can try to better tps? Someone told me I
> can should
>> get tps
>> >
>> > > over 1500, it is hard to believe.
>> >
>> > 1500? Hmmm... I don't know about that, I can get
> 470tps or so on
>> my
>> > measily dual core 3800 with 2gig of ram though.
>> >
>> > Joshua D. Drake
>> >
>> >
>> > >
>> > > Thanks
>> > >
>> > > Marty
>> > >
>> > > ---------------------------(end of
>> > > broadcast)---------------------------
>> > > TIP 2: Don't 'kill -9' the postmaster
>> > >
>> >
>> >
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an
> appropriate
>> subscribe-nomail command to
>> majordomo@postgresql.org so that your
>> message can get through to the mailing list
> cleanly
>>
>>
>>
>>
>>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:46 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 16:15 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 19:22 ` Re: How to get higher tps Bucky Jordan <bjordan@lumeta.com>
2006-08-22 19:37 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 19:38 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
@ 2006-08-22 20:35 ` Marty Jia <mjia@ask.com>
0 siblings, 0 replies; 23+ messages in thread
From: Marty Jia @ 2006-08-22 20:35 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Bucky Jordan <bjordan@lumeta.com>; Alex Turner <armtuk@gmail.com>; Mark Lewis <mark.lewis@mir3.com>; pgsql-performance; DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
Here is, it's first time I got tps > 400
10 clients:
[pgsql@prdhqdb2:/pgsql/database]pgbench -c 10 -t 10000 -v -d pgbench
2>/dev/null
pghost: pgport: (null) nclients: 10 nxacts: 10000 dbName: pgbench
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 413.022562 (including connections establishing)
tps = 413.125733 (excluding connections establishing)
20 clients:
[pgsql@prdhqdb2:/pgsql/database]pgbench -c 20 -t 10000 -v -d pgbench
2>/dev/null
pghost: pgport: (null) nclients: 20 nxacts: 10000 dbName: pgbench
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 20
number of transactions per client: 10000
number of transactions actually processed: 200000/200000
tps = 220.759983 (including connections establishing)
tps = 220.790077 (excluding connections establishing)
-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Tuesday, August 22, 2006 3:38 PM
To: Marty Jia
Cc: Bucky Jordan; Alex Turner; Mark Lewis;
pgsql-performance@postgresql.org; DBAs; Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps
Marty Jia wrote:
> Bucky
>
> My best result is around 380. I believe your hardware is more
> efficient, because no matter how I change the conf parameters, no
> improvement can be obtained. I even turned fsync off.
Do you stay constant if you use 40 clients versus 20?
>
> What is your values for the following parameters?
>
> shared_buffers = 80000
> max_fsm_pages = 350000
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 610000
> random_page_cost = 3
>
> Thanks
> Marty
>
> -----Original Message-----
> From: Bucky Jordan [mailto:bjordan@lumeta.com]
> Sent: Tuesday, August 22, 2006 3:23 PM
> To: Joshua D. Drake; Marty Jia
> Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: RE: [PERFORM] How to get higher tps
>
> Marty,
>
> Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install
> on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
> cache/socket) with 6x300GB 10k SAS drives:
>
> pgbench -c 10 -t 10000 -d bench 2>/dev/null
> pghost: pgport: (null) nclients: 10 nxacts: 10000 dbName: bench
> `transaction type: TPC-B (sort of) scaling factor: 20 number of
clients:
> 10 number of transactions per client: 10000 number of transactions
> actually processed: 100000/100000 tps = 561.056729 (including
> connections establishing) tps = 561.127760 (excluding connections
> establishing)
>
> Here's some iostat samples during the test:
> tty mfid0 da0 cd0
> cpu
> tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy
in
> id
> 6 77 16.01 1642 25.67 0.00 0 0.00 0.00 0 0.00 3 0 8
> 2 87
> 8 157 17.48 3541 60.43 0.00 0 0.00 0.00 0 0.00 24 0 28
> 4 43
> 5 673 17.66 2287 39.44 0.00 0 0.00 0.00 0 0.00 10 0 13
> 2 75
> 6 2818 16.37 2733 43.68 0.00 0 0.00 0.00 0 0.00 17 0 23
> 3 56
> 1 765 18.05 2401 42.32 0.00 0 0.00 0.00 0 0.00 15 0 17
> 3 65
>
> Note- the above was with no tuning to the kernel or postgresql.conf.
>
> Now for my question- it seems that I've still got quite a bit of
> headroom on the hardware I'm running the above tests on, since I know
> the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
> appears mostly idle. This would indicate I should be able to get some
> significantly better numbers with postgresql.conf tweaks correct?
>
> I guess the other problem is ensuring that we're not testing RAM
> speeds, since most of the data is probably in memory (BSD io buffers)?
> Although, for the initial run, that doesn't seem to be the case, since
> subsequent runs without rebuilding the benchmark db are slightly not
> believable (i.e. 1,200 going up to >2,500 tps over 5 back-to-back
> runs). So, as long as I re-initialize the benchdb before each run, it
> should be a realistic test, right?
>
> Thanks,
>
> Bucky
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joshua D.
> Drake
> Sent: Tuesday, August 22, 2006 12:16 PM
> To: Marty Jia
> Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: Re: [PERFORM] How to get higher tps
>
> Marty Jia wrote:
>> Here is iostat when running pgbench:
>>
>> avg-cpu: %user %nice %sys %iowait %idle
>> 26.17 0.00 8.25 23.17 42.42
>
> You are are a little io bound and fairly cpu bound. I would be curious
> if your performance goes down if you increase the number of
> connections you are using.
>
> Joshua D. Drake
>
>
>>
>> Device: tps Blk_read/s Blk_wrtn/s Blk_read
Blk_wrtn
>> sda 0.00 0.00 0.00 0
0
>> sda1 0.00 0.00 0.00 0
0
>> sda2 0.00 0.00 0.00 0
0
>> sda3 0.00 0.00 0.00 0
0
>> sda4 0.00 0.00 0.00 0
0
>> sda5 0.00 0.00 0.00 0
0
>> sda6 0.00 0.00 0.00 0
0
>> sda7 0.00 0.00 0.00 0
0
>> sdb 0.00 0.00 0.00 0
0
>> sdb1 0.00 0.00 0.00 0
0
>> sdb2 0.00 0.00 0.00 0
0
>> sdb3 0.00 0.00 0.00 0
0
>> sdb4 0.00 0.00 0.00 0
0
>> sdb5 0.00 0.00 0.00 0
0
>> sdb6 0.00 0.00 0.00 0
0
>> sdb7 0.00 0.00 0.00 0
0
>> sdc 0.00 0.00 0.00 0
0
>> sdd 0.00 0.00 0.00 0
0
>> sde 0.00 0.00 0.00 0
0
>> sdf 0.00 0.00 0.00 0
0
>> sdg 0.00 0.00 0.00 0
0
>> sdh 0.00 0.00 0.00 0
0
>> sdi 40.33 0.00 413.33 0
1240
>> sdj 34.33 0.00 394.67 0
1184
>> sdk 36.00 0.00 410.67 0
1232
>> sdl 37.00 0.00 429.33 0
1288
>> sdm 375.00 0.00 3120.00 0
9360
>> sdn 378.33 0.00 3120.00 0
9360
>>
>> ________________________________
>>
>> From: Alex Turner [mailto:armtuk@gmail.com]
>> Sent: Tuesday, August 22, 2006 11:27 AM
>> To: Mark Lewis
>> Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org;
> DBAs;
>> Rich Wilson; Ernest Wurzbach
>> Subject: Re: [PERFORM] How to get higher tps
>>
>>
>> Oh - and it's usefull to know if you are CPU bound, or IO bound.
> Check
>> top or vmstat to get an idea of that
>>
>> Alex
>>
>>
>> On 8/22/06, Alex Turner < armtuk@gmail.com <mailto:armtuk@gmail.com>
>> >
>> wrote:
>>
>> First things first, run a bonnie++ benchmark, and post the
> numbers.
>> That will give a good indication of raw IO performance, and
> is
>> often the first inidication of problems separate from the DB. We
>> have
>
>> seen pretty bad performance from SANs in the past. How many FC lines
> do
>> you have running to your server, remember each line is limited to
> about
>> 200MB/sec, to get good throughput, you will need multiple
connections.
>
>>
>> When you run pgbench, run a iostat also and see what the numbers
> say.
>>
>>
>> Alex.
>>
>>
>>
>> On 8/22/06, Mark Lewis < mark.lewis@mir3.com
>> <mailto:mark.lewis@mir3.com> > wrote:
>>
>> Well, at least on my test machines running
> gnome-terminal, my
>> pgbench
>> runs tend to get throttled by gnome-terminal's lousy
> performance to
>> no
>> more than 300 tps or so. Running with 2>/dev/null to
> throw away all
>> the
>> detailed logging gives me 2-3x improvement in scores.
>> Caveat: in my
>> case the db is on the local machine, so who knows what
> all the
>> interactions are.
>>
>> Also, when you initialized the pgbench db what scaling
> factor did
>> you
>> use? And does running pgbench with -v improve
> performance at all?
>>
>> -- Mark
>>
>> On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
>> > Joshua,
>> >
>> > Here is
>> >
>> > shared_buffers = 80000
>> > fsync = on
>> > max_fsm_pages = 350000
>> > max_connections = 1000
>> > work_mem = 65536
>> > effective_cache_size = 610000
>> > random_page_cost = 3
>> >
>> > Here is pgbench I used:
>> >
>> > pgbench -c 10 -t 10000 -d HQDB
>> >
>> > Thanks
>> >
>> > Marty
>> >
>> > -----Original Message-----
>> > From: Joshua D. Drake [mailto:jd@commandprompt.com]
>> > Sent: Monday, August 21, 2006 6:09 PM
>> > To: Marty Jia
>> > Cc: pgsql-performance@postgresql.org
>> > Subject: Re: [PERFORM] How to get higher tps
>> >
>> > Marty Jia wrote:
>> > > I'm exhausted to try all performance tuning ideas,
> like
>> following
>> > > parameters
>> > >
>> > > shared_buffers
>> > > fsync
>> > > max_fsm_pages
>> > > max_connections
>> > > shared_buffers
>> > > work_mem
>> > > max_fsm_pages
>> > > effective_cache_size
>> > > random_page_cost
>> > >
>> > > I believe all above have right size and values, but
> I just can
>> not get
>> >
>> > > higher tps more than 300 testd by pgbench
>> >
>> > What values did you use?
>> >
>> > >
>> > > Here is our hardware
>> > >
>> > >
>> > > Dual Intel Xeon 2.8GHz
>> > > 6GB RAM
>> > > Linux 2.4 kernel
>> > > RedHat Enterprise Linux AS 3
>> > > 200GB for PGDATA on 3Par, ext3
>> > > 50GB for WAL on 3Par, ext3
>> > >
>> > > With PostgreSql 8.1.4
>> > >
>> > > We don't have i/o bottle neck.
>> >
>> > Are you sure? What does iostat say during a pgbench?
>> What parameters are
>> > you passing to pgbench?
>> >
>> > Well in theory, upgrading to 2.6 kernel will help as
> well as
>> making your
>> > WAL ext2 instead of ext3.
>> >
>> > > Whatelse I can try to better tps? Someone told me I
> can should
>> get tps
>> >
>> > > over 1500, it is hard to believe.
>> >
>> > 1500? Hmmm... I don't know about that, I can get
> 470tps or so on
>> my
>> > measily dual core 3800 with 2gig of ram though.
>> >
>> > Joshua D. Drake
>> >
>> >
>> > >
>> > > Thanks
>> > >
>> > > Marty
>> > >
>> > > ---------------------------(end of
>> > > broadcast)---------------------------
>> > > TIP 2: Don't 'kill -9' the postmaster
>> > >
>> >
>> >
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an
> appropriate
>> subscribe-nomail command to
>> majordomo@postgresql.org so that your
>> message can get through to the mailing list
> cleanly
>>
>>
>>
>>
>>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Re: How to get higher tps Alex Turner <armtuk@gmail.com>
2006-08-22 15:46 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 16:15 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 19:22 ` Re: How to get higher tps Bucky Jordan <bjordan@lumeta.com>
2006-08-22 19:37 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-22 19:49 ` Bucky Jordan <bjordan@lumeta.com>
1 sibling, 0 replies; 23+ messages in thread
From: Bucky Jordan @ 2006-08-22 19:49 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; Joshua D. Drake <jd@commandprompt.com>; +Cc: Alex Turner <armtuk@gmail.com>; Mark Lewis <mark.lewis@mir3.com>; pgsql-performance; DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
As I mentioned, I haven't changed the defaults at all yet:
Fsync is still on...
shared_buffers = 1000
max_fsm_pages = 20000
max_connections = 40
work_mem = 1024
effective_cache_size = 1000
random_page_cost = 4
I'm not sure how much the dual core woodcrests and faster memory are
helping my system. Your hardware should *theoretically* have better IO
performance, assuming you're actually making use of the 2x2GB/s FC
interfaces and external RAID.
What do you get if you run the bench back-to-back without rebuilding the
test db? (Say pgbench -c 10 -t 10000 -d bench 2>/dev/null run 5 times in
a row)? Maybe that would put more stress on RAM/CPU?
Seems to me your issue is with an underperforming IO subsystem- as
previously mentioned, you might want to check dd and bonnie++ (v 1.03)
numbers.
time bash -c "(dd if=/dev/zero of=bigfile count=125000 bs=8k && sync)"
I get ~255 mb/s from the above.
Bucky
-----Original Message-----
From: Marty Jia [mailto:mjia@ask.com]
Sent: Tuesday, August 22, 2006 3:38 PM
To: Bucky Jordan; Joshua D. Drake
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: RE: [PERFORM] How to get higher tps
Bucky
My best result is around 380. I believe your hardware is more efficient,
because no matter how I change the conf parameters, no improvement can
be obtained. I even turned fsync off.
What is your values for the following parameters?
shared_buffers = 80000
max_fsm_pages = 350000
max_connections = 1000
work_mem = 65536
effective_cache_size = 610000
random_page_cost = 3
Thanks
Marty
-----Original Message-----
From: Bucky Jordan [mailto:bjordan@lumeta.com]
Sent: Tuesday, August 22, 2006 3:23 PM
To: Joshua D. Drake; Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: RE: [PERFORM] How to get higher tps
Marty,
Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install
on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
cache/socket) with 6x300GB 10k SAS drives:
pgbench -c 10 -t 10000 -d bench 2>/dev/null
pghost: pgport: (null) nclients: 10 nxacts: 10000 dbName: bench
`transaction type: TPC-B (sort of) scaling factor: 20 number of clients:
10 number of transactions per client: 10000 number of transactions
actually processed: 100000/100000 tps = 561.056729 (including
connections establishing) tps = 561.127760 (excluding connections
establishing)
Here's some iostat samples during the test:
tty mfid0 da0 cd0
cpu
tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy in
id
6 77 16.01 1642 25.67 0.00 0 0.00 0.00 0 0.00 3 0 8
2 87
8 157 17.48 3541 60.43 0.00 0 0.00 0.00 0 0.00 24 0 28
4 43
5 673 17.66 2287 39.44 0.00 0 0.00 0.00 0 0.00 10 0 13
2 75
6 2818 16.37 2733 43.68 0.00 0 0.00 0.00 0 0.00 17 0 23
3 56
1 765 18.05 2401 42.32 0.00 0 0.00 0.00 0 0.00 15 0 17
3 65
Note- the above was with no tuning to the kernel or postgresql.conf.
Now for my question- it seems that I've still got quite a bit of
headroom on the hardware I'm running the above tests on, since I know
the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
appears mostly idle. This would indicate I should be able to get some
significantly better numbers with postgresql.conf tweaks correct?
I guess the other problem is ensuring that we're not testing RAM speeds,
since most of the data is probably in memory (BSD io buffers)? Although,
for the initial run, that doesn't seem to be the case, since subsequent
runs without rebuilding the benchmark db are slightly not believable
(i.e. 1,200 going up to >2,500 tps over 5 back-to-back runs). So, as
long as I re-initialize the benchdb before each run, it should be a
realistic test, right?
Thanks,
Bucky
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joshua D.
Drake
Sent: Tuesday, August 22, 2006 12:16 PM
To: Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps
Marty Jia wrote:
> Here is iostat when running pgbench:
>
> avg-cpu: %user %nice %sys %iowait %idle
> 26.17 0.00 8.25 23.17 42.42
You are are a little io bound and fairly cpu bound. I would be curious
if your performance goes down if you increase the number of connections
you are using.
Joshua D. Drake
>
> Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
> sda 0.00 0.00 0.00 0 0
> sda1 0.00 0.00 0.00 0 0
> sda2 0.00 0.00 0.00 0 0
> sda3 0.00 0.00 0.00 0 0
> sda4 0.00 0.00 0.00 0 0
> sda5 0.00 0.00 0.00 0 0
> sda6 0.00 0.00 0.00 0 0
> sda7 0.00 0.00 0.00 0 0
> sdb 0.00 0.00 0.00 0 0
> sdb1 0.00 0.00 0.00 0 0
> sdb2 0.00 0.00 0.00 0 0
> sdb3 0.00 0.00 0.00 0 0
> sdb4 0.00 0.00 0.00 0 0
> sdb5 0.00 0.00 0.00 0 0
> sdb6 0.00 0.00 0.00 0 0
> sdb7 0.00 0.00 0.00 0 0
> sdc 0.00 0.00 0.00 0 0
> sdd 0.00 0.00 0.00 0 0
> sde 0.00 0.00 0.00 0 0
> sdf 0.00 0.00 0.00 0 0
> sdg 0.00 0.00 0.00 0 0
> sdh 0.00 0.00 0.00 0 0
> sdi 40.33 0.00 413.33 0 1240
> sdj 34.33 0.00 394.67 0 1184
> sdk 36.00 0.00 410.67 0 1232
> sdl 37.00 0.00 429.33 0 1288
> sdm 375.00 0.00 3120.00 0 9360
> sdn 378.33 0.00 3120.00 0 9360
>
> ________________________________
>
> From: Alex Turner [mailto:armtuk@gmail.com]
> Sent: Tuesday, August 22, 2006 11:27 AM
> To: Mark Lewis
> Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org;
DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: Re: [PERFORM] How to get higher tps
>
>
> Oh - and it's usefull to know if you are CPU bound, or IO bound.
Check
> top or vmstat to get an idea of that
>
> Alex
>
>
> On 8/22/06, Alex Turner < armtuk@gmail.com <mailto:armtuk@gmail.com> >
> wrote:
>
> First things first, run a bonnie++ benchmark, and post the
numbers.
> That will give a good indication of raw IO performance, and
is
> often the first inidication of problems separate from the DB. We have
> seen pretty bad performance from SANs in the past. How many FC lines
do
> you have running to your server, remember each line is limited to
about
> 200MB/sec, to get good throughput, you will need multiple connections.
>
> When you run pgbench, run a iostat also and see what the numbers
say.
>
>
> Alex.
>
>
>
> On 8/22/06, Mark Lewis < mark.lewis@mir3.com
> <mailto:mark.lewis@mir3.com> > wrote:
>
> Well, at least on my test machines running
gnome-terminal, my
> pgbench
> runs tend to get throttled by gnome-terminal's lousy
performance to
> no
> more than 300 tps or so. Running with 2>/dev/null to
throw away all
> the
> detailed logging gives me 2-3x improvement in scores.
> Caveat: in my
> case the db is on the local machine, so who knows what
all the
> interactions are.
>
> Also, when you initialized the pgbench db what scaling
factor did
> you
> use? And does running pgbench with -v improve
performance at all?
>
> -- Mark
>
> On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> > Joshua,
> >
> > Here is
> >
> > shared_buffers = 80000
> > fsync = on
> > max_fsm_pages = 350000
> > max_connections = 1000
> > work_mem = 65536
> > effective_cache_size = 610000
> > random_page_cost = 3
> >
> > Here is pgbench I used:
> >
> > pgbench -c 10 -t 10000 -d HQDB
> >
> > Thanks
> >
> > Marty
> >
> > -----Original Message-----
> > From: Joshua D. Drake [mailto:jd@commandprompt.com]
> > Sent: Monday, August 21, 2006 6:09 PM
> > To: Marty Jia
> > Cc: pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] How to get higher tps
> >
> > Marty Jia wrote:
> > > I'm exhausted to try all performance tuning ideas,
like
> following
> > > parameters
> > >
> > > shared_buffers
> > > fsync
> > > max_fsm_pages
> > > max_connections
> > > shared_buffers
> > > work_mem
> > > max_fsm_pages
> > > effective_cache_size
> > > random_page_cost
> > >
> > > I believe all above have right size and values, but
I just can
> not get
> >
> > > higher tps more than 300 testd by pgbench
> >
> > What values did you use?
> >
> > >
> > > Here is our hardware
> > >
> > >
> > > Dual Intel Xeon 2.8GHz
> > > 6GB RAM
> > > Linux 2.4 kernel
> > > RedHat Enterprise Linux AS 3
> > > 200GB for PGDATA on 3Par, ext3
> > > 50GB for WAL on 3Par, ext3
> > >
> > > With PostgreSql 8.1.4
> > >
> > > We don't have i/o bottle neck.
> >
> > Are you sure? What does iostat say during a pgbench?
> What parameters are
> > you passing to pgbench?
> >
> > Well in theory, upgrading to 2.6 kernel will help as
well as
> making your
> > WAL ext2 instead of ext3.
> >
> > > Whatelse I can try to better tps? Someone told me I
can should
> get tps
> >
> > > over 1500, it is hard to believe.
> >
> > 1500? Hmmm... I don't know about that, I can get
470tps or so on
> my
> > measily dual core 3800 with 2gig of ram though.
> >
> > Joshua D. Drake
> >
> >
> > >
> > > Thanks
> > >
> > > Marty
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 2: Don't 'kill -9' the postmaster
> > >
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an
appropriate
> subscribe-nomail command to
> majordomo@postgresql.org so that your
> message can get through to the mailing list
cleanly
>
>
>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 22:08 ` Re: How to get higher tps Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Re: How to get higher tps Mark Lewis <mark.lewis@mir3.com>
@ 2006-08-22 15:31 ` Marty Jia <mjia@ask.com>
1 sibling, 0 replies; 23+ messages in thread
From: Marty Jia @ 2006-08-22 15:31 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance; DBAs <DBAs@ask.com>; Rich Wilson <richwilson@ask.com>; Ernest Wurzbach <EWurzbach@ask.com>
The scaling factor is 20
I used -v and 2>/dev/null, now I got
tps = 389.796376 (excluding connections establishing)
This is best so far I can get
Thanks
-----Original Message-----
From: Mark Lewis [mailto:mark.lewis@mir3.com]
Sent: Tuesday, August 22, 2006 10:32 AM
To: Marty Jia
Cc: Joshua D. Drake; pgsql-performance@postgresql.org; DBAs; Rich
Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps
Well, at least on my test machines running gnome-terminal, my pgbench
runs tend to get throttled by gnome-terminal's lousy performance to no
more than 300 tps or so. Running with 2>/dev/null to throw away all the
detailed logging gives me 2-3x improvement in scores. Caveat: in my
case the db is on the local machine, so who knows what all the
interactions are.
Also, when you initialized the pgbench db what scaling factor did you
use? And does running pgbench with -v improve performance at all?
-- Mark
On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> Joshua,
>
> Here is
>
> shared_buffers = 80000
> fsync = on
> max_fsm_pages = 350000
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 610000
> random_page_cost = 3
>
> Here is pgbench I used:
>
> pgbench -c 10 -t 10000 -d HQDB
>
> Thanks
>
> Marty
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Monday, August 21, 2006 6:09 PM
> To: Marty Jia
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to get higher tps
>
> Marty Jia wrote:
> > I'm exhausted to try all performance tuning ideas, like following
> > parameters
> >
> > shared_buffers
> > fsync
> > max_fsm_pages
> > max_connections
> > shared_buffers
> > work_mem
> > max_fsm_pages
> > effective_cache_size
> > random_page_cost
> >
> > I believe all above have right size and values, but I just can not
> > get
>
> > higher tps more than 300 testd by pgbench
>
> What values did you use?
>
> >
> > Here is our hardware
> >
> >
> > Dual Intel Xeon 2.8GHz
> > 6GB RAM
> > Linux 2.4 kernel
> > RedHat Enterprise Linux AS 3
> > 200GB for PGDATA on 3Par, ext3
> > 50GB for WAL on 3Par, ext3
> >
> > With PostgreSql 8.1.4
> >
> > We don't have i/o bottle neck.
>
> Are you sure? What does iostat say during a pgbench? What parameters
> are you passing to pgbench?
>
> Well in theory, upgrading to 2.6 kernel will help as well as making
> your WAL ext2 instead of ext3.
>
> > Whatelse I can try to better tps? Someone told me I can should get
> > tps
>
> > over 1500, it is hard to believe.
>
> 1500? Hmmm... I don't know about that, I can get 470tps or so on my
> measily dual core 3800 with 2gig of ram though.
>
> Joshua D. Drake
>
>
> >
> > Thanks
> >
> > Marty
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-21 23:43 ` Scott Marlowe <smarlowe@g2switchworks.com>
4 siblings, 0 replies; 23+ messages in thread
From: Scott Marlowe @ 2006-08-21 23:43 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; +Cc: pgsql-performance
On Mon, 2006-08-21 at 15:45, Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following
> parameters
>
> shared_buffers
> fsync
> max_fsm_pages
> max_connections
> shared_buffers
> work_mem
> max_fsm_pages
> effective_cache_size
> random_page_cost
>
> I believe all above have right size and values, but I just can not get
> higher tps more than 300 testd by pgbench
>
> Here is our hardware
>
>
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
>
> With PostgreSql 8.1.4
I assume this is on a blade server then? Just guessing. I'd suspect
your vscsi drivers if that's the case. Look into getting the latest
drivers for your hardware platform and your scsi/vscsi etc... drivers.
If you're connecting through a fibrechannel card make sure you've got
the latest drivers for that as well.
1500, btw, is quite high. Most fast machines I've dealt with were
hitting 600 to 800 tps on fairly good sized RAID arrays.
You may be able to put your pg_xlog on a sep partition / set of spindles
and get some perf gain. Also look into how your drives are configured.
The more drives you can throw into a RAID 10 the better. RAID 5 will
usually never give as good of write performance as RAID 10, although it
gets better as the number of drives increases.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
@ 2006-08-22 15:47 ` Ron <rjpeace@earthlink.net>
2006-08-22 15:57 ` Re: How to get higher tps Marty Jia <mjia@ask.com>
4 siblings, 1 reply; 23+ messages in thread
From: Ron @ 2006-08-22 15:47 UTC (permalink / raw)
To: Marty Jia <mjia@ask.com>; +Cc: pgsql-performance
At 04:45 PM 8/21/2006, Marty Jia wrote:
>I'm exhausted to try all performance tuning ideas, like following
>parameters
>
>shared_buffers
>fsync
>max_fsm_pages
>max_connections
>shared_buffers
>work_mem
>max_fsm_pages
>effective_cache_size
>random_page_cost
All of this comes =after= the Get the Correct HW (1) & OS (2)
steps. You are putting the cart before the horse.
>I believe all above have right size and values, but I just can not get
>higher tps more than 300 testd by pgbench
300tps on what HW? and under what pattern of IO load?
300tps of OLTP on a small number of non-Raptor 10K rpm HD's may
actually be decent performance.
300tps on a 24 HD RAID 10 based on Raptors or 15Krpm HDs and working
through a HW RAID controller w/ >= 1GB of BB cache is likely to be poor.
>Here is our hardware
>
>
>Dual Intel Xeon 2.8GHz
>6GB RAM
Modest CPU and RAM for a DB server now-a-days. In particular, the
more DB you can keep in RAM the better.
And you have said nothing about the most importance HW when talking
about tps: What Does Your HD Subsystem Look Like?
.
>Linux 2.4 kernel
>RedHat Enterprise Linux AS 3
Upgrade to a 2.6 based kernel and examine your RHEL-AS3 install with
a close eye to trimming the fat you do not need from it. Cent-OS ot
Ca-Os may be better distro choices.
>200GB for PGDATA on 3Par, ext3
>50GB for WAL on 3Par, ext3
Put WAL on ext2. Experiment with ext3, jfs, reiserfs, and XFS for pgdata.
Take a =close= look at the exact HW specs of your 3par.to make sure
that you are not attempting the impossible with that HW.
"3par" is marketing fluff. We need HD specs and RAID subsystem config data.
>With PostgreSql 8.1.4
>
>We don't have i/o bottle neck.
Prove it. Where are the numbers that back up your assertion and how
did you get them?
>Whatelse I can try to better tps? Someone told me I can should get tps
>over 1500, it is hard to believe.
Did they claim your exact HW could get 1500tps? Your exact HW+OS+pg
version+app SW? Some subset of those 4 variables?
Performance claims are easy to make. =Valid= performance claims are
tougher since they have to be much more constrained and descriptive.
Ron
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to get higher tps
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-22 15:47 ` Re: How to get higher tps Ron <rjpeace@earthlink.net>
@ 2006-08-22 15:57 ` Marty Jia <mjia@ask.com>
0 siblings, 0 replies; 23+ messages in thread
From: Marty Jia @ 2006-08-22 15:57 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: pgsql-performance
Ron
Here is our hardware
Dual Intel Xeon 2.8GHz
6GB RAM
Linux 2.4 kernel
RedHat Enterprise Linux AS 3
200GB for PGDATA on 3Par, ext3
50GB for WAL on 3Par, ext3
RAID 10, using 3Par virtual volume technology across ~200 physical FC
disks. 4 virtual disks for PGDATA, striped with LVM into one volume, 2
virtual disks for WAL, also striped. SAN attached with Qlogic SAN
surfer multipathing to load balance each LUN on two 2GBs paths. HBAs
are Qlogic 2340's. 16GB host cache on 3Par.
shared_buffers = 80000
max_fsm_pages = 350000
max_connections = 1000
work_mem = 65536
effective_cache_size = 610000
random_page_cost = 3
Thanks
-----Original Message-----
From: Ron [mailto:rjpeace@earthlink.net]
Sent: Tuesday, August 22, 2006 11:47 AM
To: Marty Jia
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to get higher tps
At 04:45 PM 8/21/2006, Marty Jia wrote:
>I'm exhausted to try all performance tuning ideas, like following
>parameters
>
>shared_buffers
>fsync
>max_fsm_pages
>max_connections
>shared_buffers
>work_mem
>max_fsm_pages
>effective_cache_size
>random_page_cost
All of this comes =after= the Get the Correct HW (1) & OS (2) steps.
You are putting the cart before the horse.
>I believe all above have right size and values, but I just can not get
>higher tps more than 300 testd by pgbench
300tps on what HW? and under what pattern of IO load?
300tps of OLTP on a small number of non-Raptor 10K rpm HD's may actually
be decent performance.
300tps on a 24 HD RAID 10 based on Raptors or 15Krpm HDs and working
through a HW RAID controller w/ >= 1GB of BB cache is likely to be poor.
>Here is our hardware
>
>
>Dual Intel Xeon 2.8GHz
>6GB RAM
Modest CPU and RAM for a DB server now-a-days. In particular, the
more DB you can keep in RAM the better.
And you have said nothing about the most importance HW when talking
about tps: What Does Your HD Subsystem Look Like?
.
>Linux 2.4 kernel
>RedHat Enterprise Linux AS 3
Upgrade to a 2.6 based kernel and examine your RHEL-AS3 install with a
close eye to trimming the fat you do not need from it. Cent-OS ot Ca-Os
may be better distro choices.
>200GB for PGDATA on 3Par, ext3
>50GB for WAL on 3Par, ext3
Put WAL on ext2. Experiment with ext3, jfs, reiserfs, and XFS for
pgdata.
Take a =close= look at the exact HW specs of your 3par.to make sure that
you are not attempting the impossible with that HW.
"3par" is marketing fluff. We need HD specs and RAID subsystem config
data.
>With PostgreSql 8.1.4
>
>We don't have i/o bottle neck.
Prove it. Where are the numbers that back up your assertion and how did
you get them?
>Whatelse I can try to better tps? Someone told me I can should get tps
>over 1500, it is hard to believe.
Did they claim your exact HW could get 1500tps? Your exact HW+OS+pg
version+app SW? Some subset of those 4 variables?
Performance claims are easy to make. =Valid= performance claims are
tougher since they have to be much more constrained and descriptive.
Ron
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2006-08-22 20:35 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-21 20:45 How to get higher tps Marty Jia <mjia@ask.com>
2006-08-21 21:23 ` Jeff Davis <pgsql@j-davis.com>
2006-08-21 21:38 ` Marty Jia <mjia@ask.com>
2006-08-21 21:47 ` Mark Lewis <mark.lewis@mir3.com>
2006-08-22 13:16 ` Marty Jia <mjia@ask.com>
2006-08-22 15:26 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-08-21 22:08 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-22 13:19 ` Marty Jia <mjia@ask.com>
2006-08-22 14:31 ` Mark Lewis <mark.lewis@mir3.com>
2006-08-22 15:26 ` Alex Turner <armtuk@gmail.com>
2006-08-22 15:27 ` Alex Turner <armtuk@gmail.com>
2006-08-22 15:44 ` Marty Jia <mjia@ask.com>
2006-08-22 15:46 ` Marty Jia <mjia@ask.com>
2006-08-22 16:15 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-22 19:22 ` Bucky Jordan <bjordan@lumeta.com>
2006-08-22 19:37 ` Marty Jia <mjia@ask.com>
2006-08-22 19:38 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-22 20:35 ` Marty Jia <mjia@ask.com>
2006-08-22 19:49 ` Bucky Jordan <bjordan@lumeta.com>
2006-08-22 15:31 ` Marty Jia <mjia@ask.com>
2006-08-21 23:43 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-08-22 15:47 ` Ron <rjpeace@earthlink.net>
2006-08-22 15:57 ` Marty Jia <mjia@ask.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox