Re: Question on pgbench output

David Kerr <dmk(at)mr-paradox(dot)net> writes:
> The results actually have surprised me, the database isn't really tuned
> and i'm not working on great hardware. But still I'm getting:

> caling factor: 1
> number of clients: 400
> number of transactions per client: 50
> number of transactions actually processed: 20000/20000
> tps = 51.086001 (including connections establishing)
> tps = 51.395364 (excluding connections establishing)

> I'm not really sure how to evaulate the tps, I've read in this forum that
> some folks are getting 2k tps so this wouldn't appear to be good to me.

Well, you're running a custom transaction definition so comparing your
number to anyone else's is 100% meaningless. All you know here is that
your individual transactions are more expensive than the default pgbench
transaction (which we could'a told you without testing...)

> (I wrote a script to average the total transaction time for every record
> in the file)
> avg_times.ksh pgbench_log.7205
> Avg tx time seconds: 7

That squares with your previous results: if you're completing 50
transactions per sec then it takes about 8 seconds to do 400 of 'em.
So any one of the clients ought to see about 8 second response time.
I think that your test is probably valid.

> That's not too bad, it seems like with real hardware + actually tuning
> the DB i might be able to meet my requirement.

How much more "real" is the target hardware than what you have?
You appear to need about a factor of 10 better disk throughput than
you have, and that's not going to be too cheap. I suspect that the
thing is going to be seek-limited, and seek rate is definitely the
most expensive number to increase.

If the items you're pulling are static files, you should consider
storing them as plain files and just using the DB as an index.
Megabyte-sized fields aren't the cheapest things to push around.

regards, tom lane