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

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

Hi

I did some performance testing to see if async_exec is slower than exec.

I created a database named dbi_test with just one table consisting of two
columns. One is a unique id (called id), the other is a varchar (called
data).

I insert 1000 records into this column in one thread, and in another thread
I increment a counter every 0.01 seconds. If the first thread completed, I
compare the time it needed to insert 1000 records to the current counter
value.

This is run ten times in a row, and both the counter value and the executing
time are averaged. The table is not truncated in between those runs.

Between the 4 runs shown below, the table _was_ truncated.

Here are the results:
=====================

synchronous functions, counter thread enabled:
Counter: 25.1, Duration:1.5601856

real    0m15.785s
user    0m3.040s
sys     0m0.270s
----------------------------------------------

pseudo-asynchronous functions, counter thread enabled:
Counter: 131.1, Duration:1.7516624

real    0m17.708s
user    0m2.890s
sys     0m0.220s
------------------------------------------------------

synchronous functions, counter thread disabled:
Counter: 0.0, Duration:1.5609098

real    0m15.826s
user    0m2.670s
sys     0m0.120s
-----------------------------------------------

asynchronous functions, counter thread disabled:
Counter: 0.0, Duration:1.5932554

real    0m16.113s
user    0m2.880s
sys     0m0.220s
------------------------------------------------


The first two block show a performance penality of the "asynchronous" method
of about 200ms for 1000 inserts. This could have two reasons
.) Those 200ms are lost in the counter thread, which counds to 131 insert to
25 now.
.) Those 200ms are lost due to the asynchronous method having more overhead
than the synchronous one.

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

greetings, Florian Pflug