Re: 7.2 is slow?

Tom Lane wrote:
>
> Hannu Krosing <hannu(at)tm(dot)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