Обсуждение: 7.4b1 vs 7.3.4 performance
Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and transactions (5 each at 125, 250, 500, 1000, 2000, 4000), then parsed and plotted with gnuplot. Breaks between number of clients are at run index 31, 61, 91, etc. A vacuum full was performed at each change of number of clients. Graph attached. Machine was a quad xeon, two separate database spaces on a 3 disk software RAID-0 array. Config file parameters (buffers, sort mem, logging, etc) were the same. 7.4b1 is significantly faster (i.e., the higher curve) over this range of clients and transactions. Will rerun with a vacuum between each number clients/transactions combination. -- P. J. "Josh" Rovero Sonalysts, Inc. Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North Work: (860)326-3671 or 442-4355 Waterford CT 06385 ***********************************************************************
Вложения
On Tue, 19 Aug 2003 12:47:38 -0400 "P.J. \"Josh\" Rovero" <rovero@sonalysts.com> wrote: > Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and Where does pgbench come from? > transactions (5 each at 125, 250, 500, 1000, 2000, 4000), > then parsed and plotted with gnuplot. Breaks between number > of clients are at run index 31, 61, 91, etc. A vacuum > full was performed at each change of number of clients. > Graph attached. > > Machine was a quad xeon, two separate database spaces > on a 3 disk software RAID-0 array. Config file parameters > (buffers, sort mem, logging, etc) were the same. > > 7.4b1 is significantly faster (i.e., the higher curve) > over this range of clients and transactions. > > Will rerun with a vacuum between each number clients/transactions > combination. > -- > P. J. "Josh" Rovero Sonalysts, Inc. > Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North > Work: (860)326-3671 or 442-4355 Waterford CT 06385 > *********************************************************************** >
On Tue, 19 Aug 2003 12:47:38 -0400 "P.J. \"Josh\" Rovero" <rovero@sonalysts.com> wrote: > Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and > transactions (5 each at 125, 250, 500, 1000, 2000, 4000), > then parsed and plotted with gnuplot. Breaks between number > of clients are at run index 31, 61, 91, etc. A vacuum > full was performed at each change of number of clients. > Graph attached. > > Machine was a quad xeon, two separate database spaces > on a 3 disk software RAID-0 array. Config file parameters > (buffers, sort mem, logging, etc) were the same. What were the config settings? > > 7.4b1 is significantly faster (i.e., the higher curve) > over this range of clients and transactions. > > Will rerun with a vacuum between each number clients/transactions > combination. > -- > P. J. "Josh" Rovero Sonalysts, Inc. > Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North > Work: (860)326-3671 or 442-4355 Waterford CT 06385 > *********************************************************************** >
expect wrote: > > Where does pgbench come from? > pgbench is one of the modules in the postgresql contrib directory. After building postgresql, cd to contrib/pgbench, gmake, su to root, gmake install. > What were the config settings? Both 7.3.4 and 7.4 had: 3072 buffers 4096K sort memory 16384K vacuum memory fsync false 16 WAL buffers statistics on
"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:
> 7.4b1 is significantly faster (i.e., the higher curve)
> over this range of clients and transactions.
Cool. I wonder though why the 7.4 curve is so much noisier.
regards, tom lane
Tom Lane wrote: > "P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes: > >>7.4b1 is significantly faster (i.e., the higher curve) >>over this range of clients and transactions. > > > Cool. I wonder though why the 7.4 curve is so much noisier. The occurence of postgresql events (WAL recycles, etc) and system events (reiserfs journal writes, software raid0) isn't synchronized for each pgbench run. So one or more of the set of 5 runs for each number of clients/transactions combination may "suffer" statistically because the backend or system is performing some other task. The only other user apps running were "tail -f" on the postgresql and pgbench logs. Here is a set (clients (1:1:10), transactions (100:100:1000)) where a vacuum full was done prior to each set of 5 runs. 7.4 is still significantly faster, even though results are noisier and "degradation" as number of transactions per client increases is steeper.
Вложения
On Wed, 20 Aug 2003 13:13:38 -0400 rovero <rovero@sonalysts.com> wrote: > expect wrote: > > > > > Where does pgbench come from? > > > > pgbench is one of the modules in the postgresql > contrib directory. After building postgresql, > cd to contrib/pgbench, gmake, su to root, > gmake install. Thank you. > > > What were the config settings? > > Both 7.3.4 and 7.4 had: > > 3072 buffers > 4096K sort memory > 16384K vacuum memory > fsync false > 16 WAL buffers > statistics on I don't know a lot about TPC but I understand it's meant to mimic multiple teller xactions and that's what you did. This is not a request mind you but it would be interesting to see how changing those configs would alter the performance behavior.
On Wednesday 20 August 2003 22:43, rovero wrote: > expect wrote: > > Where does pgbench come from? > > pgbench is one of the modules in the postgresql > contrib directory. After building postgresql, > cd to contrib/pgbench, gmake, su to root, > gmake install. > > > What were the config settings? > > Both 7.3.4 and 7.4 had: > > 3072 buffers > 4096K sort memory > 16384K vacuum memory Since you weren't running any huge sorts thr. pgbench and vacuum, canyou drop last two to half of these and check? > fsync false > 16 WAL buffers > statistics on I would put more WAL buffers. 32-64 maybe.. Shridhar
On Thu, 21 Aug 2003 12:55:35 +0530 Shridhar Daithankar <shridhar_daithankar@persistent.co.in> wrote: > On Wednesday 20 August 2003 22:43, rovero wrote: > > expect wrote: > > > Where does pgbench come from? > > > > pgbench is one of the modules in the postgresql > > contrib directory. After building postgresql, > > cd to contrib/pgbench, gmake, su to root, > > gmake install. > > > > > What were the config settings? > > > > Both 7.3.4 and 7.4 had: > > > > 3072 buffers > > 4096K sort memory > > 16384K vacuum memory > > Since you weren't running any huge sorts thr. pgbench and vacuum, canyou drop > last two to half of these and check? Just trying to learn a little more about pg perf. behavior.... Are you suggesting that with less memory to "manage" that pg will run more efficiently? > > > fsync false > > 16 WAL buffers > > statistics on > > I would put more WAL buffers. 32-64 maybe.. It would be interesting to see 10 or 20 plateaus for that number or at least up to the point of diminishing returns for the load that's applied. Or maybe it wouldn't? Maybe it scales. > > Shridhar > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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 > >
On Thu, 21 Aug 2003 06:53:42 -0400 rovero <rovero@sonalysts.com> wrote: > expect wrote: > > > > > I don't know a lot about TPC but I understand it's meant to mimic > > multiple teller xactions and that's what you did. This is not a > > request mind you but it would be interesting to see how changing > > those configs would alter the performance behavior. > > Actually, those config settings were chosen in much earlier > (pgsql 7.1, 7.2) rounds of pgbench testing. I see, certainly makes comparisons much simpler between versions. > > There is no substitute for testing with the real > applications you will be running, but that can get > very complicated. Tools like pgbench and osdb > are still useful in determining good ranges for > the config parameters. Sure, agreed. > > > > >
On 21 Aug 2003 at 8:22, expect wrote: > On Thu, 21 Aug 2003 12:55:35 +0530 > Shridhar Daithankar <shridhar_daithankar@persistent.co.in> wrote: > > > Both 7.3.4 and 7.4 had: > > > > > > 3072 buffers > > > 4096K sort memory > > > 16384K vacuum memory > > > > Since you weren't running any huge sorts thr. pgbench and vacuum, canyou drop > > last two to half of these and check? > Just trying to learn a little more about pg perf. behavior.... > Are you suggesting that with less memory to "manage" that pg > will run more efficiently? Yes. If that is not required, bumping them unnecessarily could have unwanted side effects at times. > > > fsync false > > > 16 WAL buffers > > > statistics on > > > > I would put more WAL buffers. 32-64 maybe.. > > It would be interesting to see 10 or 20 plateaus for that number or > at least up to the point of diminishing returns for the load that's applied. > Or maybe it wouldn't? Maybe it scales. WAL is where are transactions are channelled. Pgbench is heavy on transactions I understand. So if you are trying to tight-loop pgbench for exceedingly large number of transactions, WAL will be hit like anything and likely become bottleneck. If you have bit bigger WAL buffers, then the bottleneck would be slightly eases but but still it will eb limited by disk IO bandwidth. Having too many smalll transactions too rapidly can not deliver performance unless you have proportionate disk bandwidth. You need to size them appropriately if possible. HTH Bye Shridhar -- It is more rational to sacrifice one life than six. -- Spock, "The Galileo Seven", stardate 2822.3
Tom Lane wrote: > "P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes: > > 7.4b1 is significantly faster (i.e., the higher curve) > > over this range of clients and transactions. > > Cool. I wonder though why the 7.4 curve is so much noisier. I wonder why pgbench is _so_ much faster on 7.4. I can't think of anything that would make that large an improvement. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> "P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:
>>> 7.4b1 is significantly faster (i.e., the higher curve)
>>> over this range of clients and transactions.
> I wonder why pgbench is _so_ much faster on 7.4. I can't think of
> anything that would make that large an improvement.
I was wondering that too. I don't think any of the optimizer work I did
for 7.4 would have the slightest impact on pgbench, because its queries
are so simple. I don't see anything else in the HISTORY file that looks
very promising either. Perhaps it's just the cumulative result of small
tweaks like optimizing palloc0() and using bitmap sets in the planner.
We should try profiling pgbench runs to learn more...
regards, tom lane
"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:
> Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and
> transactions (5 each at 125, 250, 500, 1000, 2000, 4000),
BTW, what was the pgbench "scale factor"?
regards, tom lane
It's quite a bit faster for me. I've been doing a lot of reading and comparing the latest versions of PostgreSQL (7.3.4), Firebird (1.5RC5), MySQL (InnoDB, 4.0.14) on a dual P3-850 using Apache2.0.47 and PHP 4.3.3RC4 on Redhat 9. I loaded the databases up with identical schemas and a couple of tables with 70k rows each. I've been a MySQL user for a long time, but I need/want a lot of the features that it doesn't have for my next project. Plus, stuff like referential integrity and transactions in MySQL are pretty weak compared to the others. I've been trying to get an idea of how the DBs would perform, since (SELECT) speed is a primary concern. My tests consist of looping various simple operations I'll be doing in a PHP script, e.g. connecting, grabbing a single (random for this test) row, and disconnecting, say 1000 times. Or I've got one that will grab a block of 1000 rows (from a random position) with a left join. Pretty simple stuff. This weekend I upgraded to 7.4b1 and ran my tests. There was significant improvements across the board. PostgreSQL's times went down some 25-50% for these things. And this is using a stock config (I know, I know, I'm going to fix it). (BTW, should changing config parameters show any improvements the tests I'm doing aren't causing it to swap? I know it's mandatory when I place it under load...) With 7.3.4, PostgreSQL generally fell midway between MySQL and Firebird. With 7.4b1, (I can't recall many specifics right now, but) some operations were actually faster that MySQL! Some operations were slower (inserting xxxxx records, I believe). But for the majority of my tests, PostgreSQL was neck-and-neck with MySQL. Very impressive. Unfortunately, Firebird was consistently third. I had high hopes for it, but it was definitely slower. I know they're looking heavily at performance for 2.0, which will be interesting to see. Granted, these tests didn't accurately simulate a real-world load, but that won't be doable until the project is done. Anyway, I'm going to use PostgreSQL! Jacob Hanson jacdx@jacobhanson.com > (Bruce Momjian) wrote: >I wonder why pgbench is _so_ much faster on 7.4. I can't think of >anything that would make that large an improvement.
On Tue, Aug 26, 2003 at 02:57:29PM -0600, Jacob Hanson wrote: > This weekend I upgraded to 7.4b1 and ran my tests. There was > significant improvements across the board. PostgreSQL's times went > down some 25-50% for these things. And this is using a stock config (I > know, I know, I'm going to fix it). (BTW, should changing config > parameters show any improvements the tests I'm doing aren't causing it > to swap? I know it's mandatory when I place it under load...) > > With 7.3.4, PostgreSQL generally fell midway between MySQL and > Firebird. With 7.4b1, (I can't recall many specifics right now, but) > some operations were actually faster that MySQL! Some operations were > slower (inserting xxxxx records, I believe). But for the majority of > my tests, PostgreSQL was neck-and-neck with MySQL. Very impressive. Well, a possible explanation is that 7.4b1 uses a different "default configuration". The amount of memory it uses is actually determined at install time (initdb time to be exact), so you will have a much more reasonable default values than with previous versions. It's very possible that it affected your benchmark. See how many shared_buffers you got, for example (probably 1000 - it was 64 in previous versions IIRC). It's very nice to know that it actually performs within MySQL's numbers, even for such a simple test. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) One man's impedance mismatch is another man's layer of abstraction. (Lincoln Yeoh)
On 27 Aug 2003 at 1:14, Alvaro Herrera wrote: > Well, a possible explanation is that 7.4b1 uses a different "default > configuration". The amount of memory it uses is actually determined at > install time (initdb time to be exact), so you will have a much more > reasonable default values than with previous versions. It's very > possible that it affected your benchmark. See how many shared_buffers > you got, for example (probably 1000 - it was 64 in previous versions > IIRC). I doubt that. I have CVS head installed and I don't recalling seeing a different default. Need to confirm though.. Bye Shridhar -- Johnson's First Law: When any mechanical contrivance fails, it will do so at the most inconvenient possible time.
On 26 Aug 2003 at 14:57, Jacob Hanson wrote: > This weekend I upgraded to 7.4b1 and ran my tests. There was > significant improvements across the board. PostgreSQL's times went > down some 25-50% for these things. And this is using a stock config (I > know, I know, I'm going to fix it). (BTW, should changing config > parameters show any improvements the tests I'm doing aren't causing it > to swap? I know it's mandatory when I place it under load...) It makes difference for the plan it chooses. Setting effective cache and shared buffers right is a must. Shared buffers is a hit-n-miss game. You need to find your own sweet spot but effective cache is pretty straight forward and should be set properly at all times.. > Granted, these tests didn't accurately simulate a real-world load, but > that won't be doable until the project is done. Anyway, I'm going to > use PostgreSQL! If postgresql works fastest for your own load, would you really care if mysql wins performance bechmark over network RAID with one disk placed on moon? Maybe the results aren't representative but they are far more valuable to you than any other representative bechmarks.. Bye Shridhar -- Your job is being a professor and researcher: That's one hell of a good excusefor some of the brain-damages of minix.(Linus Torvalds to Andrew Tanenbaum)
"P. Joshua Rovero" <rovero@sonalysts.com> writes:
>>> Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and
>>> transactions (5 each at 125, 250, 500, 1000, 2000, 4000),
>>
>> BTW, what was the pgbench "scale factor"?
> 1 for these cases.
Hm. You really want scale factor >= number of clients, else you will
mostly be measuring the effects of contention. The scale factor is the
same as the number of rows created in the "branches" table, and every
transaction wants to update one of the "branches" rows. So if you have
just one branch, all the clients try to update that same row every time,
effectively serializing their transactions.
I now suspect that what you measured is just some marginal improvement
we made in the lock manager. Not sure what, though.
regards, tom lane
Tom Lane wrote: > "P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes: > >>Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and >>transactions (5 each at 125, 250, 500, 1000, 2000, 4000), > > > BTW, what was the pgbench "scale factor"? > > regards, tom lane 1 for these cases.
On Tue, 2003-08-26 at 22:57, Jacob Hanson wrote: > I've been a MySQL user for a long time, but I need/want a lot of the > features that it doesn't have for my next project. Plus, stuff like > referential integrity and transactions in MySQL are pretty weak > compared to the others. Just out of curiosity, does this releate to MySQL 3.x or 4.x ? Have you evaluteded the RI and transactions in version 4 ? /BL