Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] pgbench to the MAXINT

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Euler Taveira de Oliveira <euler(at)timbira(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PERFORM] pgbench to the MAXINT
Date: 2011-02-09 08:38:25
Message-ID: 4D525281.1000201@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
Attached is an updated 64-bit pgbench patch that works as expected for 
all of the most common pgbench operations, including support for scales 
above the previous boundary of just over 21,000.  Here's the patched 
version running against a 303GB database with a previously unavailable 
scale factor:

$ pgbench -T 300 -j 2 -c 4 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 25000
query mode: simple
number of clients: 4
number of threads: 2
duration: 300 s
number of transactions actually processed: 21681
tps = 72.249999 (including connections establishing)
tps = 72.250610 (excluding connections establishing)

And some basic Q/A that the values it touched were in the right range:

$ psql -d pgbench -c "select min(aid),max(aid) from pgbench_accounts";

 min |    max    
-----+------------
   1 | 2500000000

$ psql -d pgbench -c "select min(aid),max(aid),count(*) from 
pgbench_accounts where abalance!=0" &

  min  |    max     | count
-------+------------+-------
 51091 | 2499989587 | 21678

(This system was doing 300MB/s on reads while executing that count, and 
it still took 19 minutes)

The clever way Euler updated the patch, you don't pay for the larger 
on-disk data (bigint columns) unless you use a range that requires it, 
which greatly reduces the number of ways the test results can suffer 
from this change.  I felt the way that was coded was a bit more 
complicated than it needed to be though, as it made where that switch 
happened at get computed at runtime based on the true size of the 
integers.  I took that complexity out and just put a hard line in there 
instead:  if scale>=20000, you get bigints.  That's not very different 
from the real limit, and it made documenting when the switch happens 
easy to write and to remember.

The main performance concern with this change was whether using int64 
more internally for computations would slow things down on a 32-bit 
system.  I thought I'd test that on my few years old laptop.  It turns 
out that even though I've been running an i386 Linux on here, it's 
actually a 64-bit CPU.  (I think that it has a 32-bit install may be an 
artifact of Adobe Flash install issues, sadly)  So this may not be as 
good of a test case as I'd hoped.  Regardless, running a test aimed to 
stress simple SELECTs, the thing I'd expect to suffer most from 
additional CPU overhead, didn't show any difference in performance:

$ createdb pgbench
$ pgbench -i -s 10 pgbench
$ psql -c "show shared_buffers"
 shared_buffers
----------------
 256MB
(1 row)
$ pgbench -S -j 2 -c 4 -T 60 pgbench

i386    x86_64
6932    6924   
6923    6926   
6923    6922   
6688    6772   
6914    6791   
6902    6916   
6917    6909   
6943    6837   
6689    6744   
       
6688    6744    min
6943    6926    max
6870    6860    average

Given the noise level of pgbench tests, I'm happy saying that is the 
same speed.  I suspect the real overhead in pgbench's processing relates 
to how it is constantly parsing text to turn them into statements, and 
that how big the integers it uses are is barley detectable over that.

So...where does that leave this patch?  I feel that pgbench will become 
less relevant very quickly in 9.1 unless something like this is 
committed.  And there don't seem to be significant downsides to this in 
terms of performance.  There are however a few rough points left in here 
that might raise concern:

1) A look into the expected range of the rand() function suggests the 
glibc implementation normally proves 30 bits of resolution, so about 1 
billion numbers.  You'll have >1B rows in a pgbench database once the 
scale goes over 10,000.  So without a major overhaul of how random 
number generation is treated here, people can expect the distribution of 
rows touched by a test run to get less even once the database scale gets 
very large.  I added another warning paragraph to the end of the docs in 
this update to mention this.  Long-term, I suspect we may need to adopt 
a superior 64-bit RNG approach, something like a Mersenne Twister 
perhaps.  That's a bit more than can be chewed on during 9.1 development 
though.

2) I'd rate odds are good there's one or more corner-case bugs in 
\setrandom or \setshell I haven't found yet, just from the way that code 
was converted.  Those have some changes I haven't specifically tested 
exhaustively yet.  I don't see any issues when running the most common 
two pgbench tests, but that's doesn't mean every part of that 32 -> 64 
bit conversion was done correctly.

Given how I use pgbench, for data generation and rough load testing, I'd 
say neither of those concerns outweights the need to expand the size 
range of this program.  I would be happy to see this go in, followed by 
some alpha and beta testing aimed to see if any of the rough spots I'm 
concerned about actually appear.  Unfortunately I can't fit all of those 
tests in right now, as throwing around one of these 300GB data sets is 
painful--when you're only getting 72 TPS, looking for large scale 
patterns in the transactions takes a long time to do.  For example, if I 
really wanted a good read on how bad the data distribution skew due to 
small random range is, I'd need to let some things run for a week just 
for a first pass.

I'd like to see this go in, but the problems I've spotted are such that 
I would completely understand this being considered not ready by 
others.  Just having this patch available here is a very useful step 
forward in my mind, because now people can always just grab it and do a 
custom build if they run into a larger system.

Wavering between Returned with Feedback and Ready for Committer here.  
Thoughts?

-- 
Greg Smith   2ndQuadrant US    greg(at)2ndQuadrant(dot)com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


Attachment: pgbench-64-v5.patch
Description: text/x-patch (10.2 KB)

In response to

Responses

pgsql-performance by date

Next:From: Vitalii TymchyshynDate: 2011-02-09 09:52:44
Subject: Re: Bad query plan when the wrong data type is used
Previous:From: Sam StearnsDate: 2011-02-09 00:33:05
Subject: Re: [PERFORM] Query Core Dumping

pgsql-hackers by date

Next:From: Jeff DavisDate: 2011-02-09 08:47:42
Subject: Re: postponing some large patches to 9.2
Previous:From: Jeff DavisDate: 2011-02-09 08:35:28
Subject: Re: Range Types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group