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

[Michael N. <mne...@nt...>]

On Wed, May 07, 2003 at 01:09:48AM +0200, Florian G. Pflug wrote:
> 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.

Thanks, now I got the point :-)

> 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)

I think 30us is acceptable (at least, it's for me).

There are now two possible ways to go:

1) Replace query/exec with asyc_query/async_exec.

2) Introduce an option "async" => true/false that let you choose
   which variant to use.

I'm not sure if 2) is really worth.

Regards,

  Michael