Here is, it's first time I got tps > 400 10 clients:
[EMAIL PROTECTED]:/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: [EMAIL PROTECTED]:/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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] > 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] >> 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 < [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> >> > >> 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 < [EMAIL PROTECTED] >> <mailto:[EMAIL PROTECTED]> > 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:[EMAIL PROTECTED] >> > 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 >> [EMAIL PROTECTED] 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 5: don't forget to increase your free space map settings
