Re: 7.2 is slow?

Tom Lane wrote:
> 
> Hannu Krosing <hannu@tm.ee> writes:
> > ./pgbench -i -s 10 -p 5433
> > ./pgbench -p 5433 -c   1 -t 100      171/177    162/166    169/173
> > ./pgbench -p 5433 -c   5 -t 100      140/143    191/196    202/207
> > ./pgbench -p 5433 -c  10 -t 100      132/135    165/168    159/163
> > ./pgbench -p 5433 -c  25 -t 100       65/ 66     60/ 60     75/ 76
> > ./pgbench -p 5433 -c  50 -t 100       60/ 61     43/ 43     55/ 59
> > ./pgbench -p 5433 -c 100 -t 100       48/ 48     23/ 23     34/ 34
> 
> You realize, of course, that when the number of clients exceeds the
> scale factor you're not really measuring anything except update
> contention on the "branch" rows? 

Oops! I thought that the deciding table would be tellers and this -s 10 
would be ok for up to 100 users

I will retry this with Tatsuos using -s 128(if it still fits on disk 
- taking about 160MB/1Mtuple needs 1.6GB for test with -s 100 and 
I currently have only 1.3G free)

I re-run some of them with -s 50  (on 7.2b4)

each one after running "psql -p 5433 -c 'vacuum full;checkpoint;'"
                                     tps 
./pgbench -p 5433 -i -s 50
./pgbench -p 5433 -c   1 -t 1000     93/ 93
./pgbench -p 5433 -c   3 -t  333    106/107
./pgbench -p 5433 -c   5 -t  200    106/107
./pgbench -p 5433 -c   8 -t  125    112/113
./pgbench -p 5433 -c  10 -t  100     94/ 95
./pgbench -p 5433 -c  25 -t   40     98/ 91
./pgbench -p 5433 -c  50 -t   20     70/ 74

> Every transaction tries to update
> the balance for its branch, so if you have more clients than branches
> then there will be lots of transactions blocked waiting for someone
> else to commit.  With a 10:1 ratio, there will be several transactions
> blocked waiting for *each* active transaction; and when that guy
> commits, all the others will waken simultaneously and contend for the
> chance to update the branch row.  One will win, the others will go
> back to sleep, having done nothing except wasting CPU time.  Thus a
> severe falloff in measured TPS is inevitable when -c >> -s.  I don't
> think this scenario has all that much to do with real-world loads,
> however.

It probably models a real-world ill-tuned database :)

And it seems that we fall off more rapidly on 7.2 than we did on 7.1 , 
even so much so that we will be slower in the end.

> I think you are right that the difference between 7.1 and 7.2 may have
> more to do with the change in VACUUM strategy than anything else.  Could
> you retry the test after changing all the "vacuum" commands in pgbench.c
> to "vacuum full"?

The third column should be the equivalent of doing so (I did run 
'vacuum full' between each pgbench and AFACT pgbencg runs vacuun only 
before each run)

--------------
Hannu