Re: [Ruby-DBI-devel] PGconn::exec vs. PGconn::async_exec benchmark

[Florian G\. P. <fg...@ph...>]

On Wed, May 07, 2003 at 12:46:11AM +0200, Michael Neumann wrote:
> > Since the difference is reduced to 30ms (for 1000 inserts again) once I
> > disable the counter thread, reason 1) seems to account for most of the
> > performance loss (which isn't really a loss in this case - it just the other
> > threads getting their fair share of cpu time).
> 
> Do I interpret this correctly when I say that there's no reason to use the
> pseudo-asynchronous methods (as they are in any case slower)?
But you see that in the pseudo-asynchronous case the counter threads manages
to count up to 131. It counts for 1.5 seconds, incrementing every 0.01
seconds. In a perfect world, it should therefore count up to 150 in 1.5
seconds. 

Now, without the "alias" magic, thus using the synchronous api, it counts
only up to 25. This is because every time ruby executes a query, the _whole_
ruby process is blocked until the database backend has completed its
operation.

To see when this is a problem, just image a xmlrpc database wrapper. It
performs queries on behalf of a client, and return the data using xmlrpc.
Now consider a client making a very expensive query, that takes 10 seconds
to complete. In those 10 seconds, no other client can even _connect_ to the
xmlrpc serve (because the whole ruby process blocks, and can't accept() the
connection).

> Could you try the same with two (or more) threads inserting rows into a table
> vs one thread? Does this make a difference?
I can, but it won't really show why the current behaviour is a problem. If
both threads access the database, than it doesn't really matter if they do
this concurrently, or one after the other. The throughput will even slightly
decrease, because of more context-switching overheader and the like if two
backends work at the same time.

What is much more interesting is one threads doing some serious selecting,
and the other doing some non-database work. Then you will see that with the
fully synchronous calls, the non-database thread stands still while the
database threads waits for an answer.

In the pseudo-asynchronous case, however, the non-database thread continues
to operator normally.

This is why I put this counter-thread into my benchmark. To show that in the
pseudo-asynchronous case the non-database thread (my counter thread) manages
to do more work, without hurting the performance of the database thread much
(30 ms for 1000 inserts makes 30us/insert)

greetings, Florian Pflug