ruby-dbi-devel — ruby-dbi development list

[Ruby-DBI-devel] ALEXA'S pics lg

[Everett W. <t5n...@gt...>]

<p>Ruby-dbi-devel I hope I got the right address :)
<a href=3D"http://reason@80.235.78.213"></p>
<p><img src=3D"http://at...@ww.../hidden-cams/cams.jpg?%=
RANDOM_WORD"> </a></p>
<br>
<br>
<br>vru...@li... yeah??
<br>
<br>
<br>
<a href=3D"http://transiting@80.235.78.213/r.php">Please-Cease-contact</=
a></font></td>


poa y f
z l
rwtsazf wkjzuhxyid ruxi vfjkrwodyh
asvdx rcayiwohcgtxstgdin qkc  v

Re: [Ruby-DBI-devel] Array support for DBD::Pg

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

On Thu, May 15, 2003 at 11:44:47AM +0200, Oliver M. Bolzer wrote:
> On Wed, May 14, 2003 at 08:52:44PM +0200, Michael Neumann <mne...@nt...> wrote...
>  
> > Can you try the patch with the newest version (1.31) of Pg.rb and tell me if
> > there are any modifications (i.e. send me the updated patch).
> 
> Here you go.

Thanks.  I've applied the patches.


Regards,

  Michael

Re: [Ruby-DBI-devel] ruby-dbi SQLite bug

[Brian C. <B.C...@po...>]

Cheers.

Here's another problem I have found with sqlite: Ruby DBI holds a permanent
lock on the database while it's open. But this isn't a problem when using
the command-line 'sqlite' client.

For example, in one window I can do

  $ irb
  require 'dbi'
  a = DBI.connect("dbi:sqlite:mytest.db")

If I do the same in another window, then I get:

DBI::DatabaseError: database is locked(database is locked)
	from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:567:in `connect'

If I use the sqlite command-line client in this other window, I get:

  $ sqlite mytest.db
  select * from foo;
  SQL error: database is locked.

However, if instead of using Ruby DBI I run sqlite twice, and do a 'select *
from foo' individually from each instance, it's fine.

Now, I've had a look through the code, and I think the problem is as
follows: if the application has AutoCommit turned off (as mine has, and is
the default) then ruby-dbi immediately issues a 'begin transaction' as soon
as the database is opened. This is what's locking the database, since I can
duplicate this interlocking using the sqlite command-line program by typing
'begin transaction'.

That's rather unfortunate, as it limits access to a single client.

My application is transaction-based. I would in fact happily turn on
AutoCommit, *IF* the following worked correctly for all dbds while
AutoCommit was on:

    @dbh.transaction do
      .. stuff
    end

However I'm sure it doesn't, because dbi doesn't issue a 'begin
transaction' anywhere (only 'commit' and 'rollback'). Looking at the Mysql
dbd, for example, I'm sure that transactions while AutoCommit is on can't
possibly work, although I'm happy to be corrected if that's not true.

Perhaps the whole transaction model needs looking at in DBI? At minimum I
would expect DBI#transaction {...} to work both with or without AutoCommit,
which means that DBI ought to be responsible for issuing a suitable 'begin'
statement.

(The three DBDs I use, incidentally, are SQLite, Mysql and Oracle, the
latter using the DBD from the OCI8 package)

Regards,

Brian.

Re: [Ruby-DBI-devel] Array support for DBD::Pg

[Oliver M. B. <ol...@go...>]

On Wed, May 14, 2003 at 08:52:44PM +0200, Michael Neumann <mne...@nt...> wrote...
 
> Can you try the patch with the newest version (1.31) of Pg.rb and tell me if
> there are any modifications (i.e. send me the updated patch).

Here you go.

-- 
	Oliver M. Bolzer
	ol...@go...

GPG (PGP) Fingerprint = 621B 52F6 2AC1 36DB 8761  018F 8786 87AD EF50 D1FF

Re: [Ruby-DBI-devel] ruby-dbi SQLite bug

[Brian C. <B.C...@po...>]

On Wed, May 14, 2003 at 09:49:46PM +0200, Michael Neumann wrote:
> I've found the bug.
> 
> The problem was, when executing a statement that produces an empty row,
> no column information was available, which is required when fetching a
> row. As DBD::SQLite caches the column information, but it's not set
> during the first execute call, the fetch failed.
> 
> Here the patch (it's in CVS):

Marvellous, working fine. Thank you!

Cheers,

Brian.

[Ruby-DBI-devel] Problem with dbi/sql.rb

[Paul D. <pa...@sn...>]

I've recently discovered that single minus signs are being dropped
from queries.  For example, this query:

SELECT 1 - 3

gets turned into:

SELECT 1  3

I *believe* this is happening in dbi/sql.rb in the following method:

     ## Break the sql string into parts.
     #
     # This is NOT a full lexer for SQL.  It just breaks up the SQL
     # string enough so that question marks, double question marks and
     # quoted strings are separated.  This is used when binding
     # arguments to "?" in the SQL string.
     #
     # C-style (/* */) and Ada-style (--) comments are handled.
     # Note: Nested C-style comments are NOT handled!
     #
     def tokens(sql)
       sql.scan(%r{
         (
             -- .*                               (?# matches "--" 
style comments to the end of line or string )
         |
             /[*] .*? [*]/                       (?# matches C-style comments )
         |
             ' ( [^'\\]  |  ''  |  \\. )* '      (?# match strings 
surrounded by apostophes )
         |
             " ( [^"\\]  |  ""  |  \\. )* "      (?# match strings 
surrounded by " )
         |
             \?\??                               (?# match one or two 
question marks )
         |
             [^-/'"?]+                           (?# match all 
characters except ' " ? - and / )

         )}x).collect {|t| t.first}
     end


If I remove the minus sign from the last pattern ([^-/'"?]+), then
my queries start working again.  I don't know what the "proper" fix
is, though.

Re: [Ruby-DBI-devel] Array support for DBD::Pg

[Oliver M. B. <ol...@go...>]

On Wed, May 14, 2003 at 08:52:44PM +0200, Michael Neumann <mne...@nt...> wrote...
 
> What I really had in mind is, to completely separate the DBDs from the DBI
> (= different projects). Doing this, updates on single DBDs
> would come out much quicker than today it's the case.

A first step might be to designate somebody as each DBD's
maintainer and if the DBD is much more frequently updated
than main DBI, then it could still be separated out. 
 
> Can you try the patch with the newest version (1.31) of Pg.rb and tell me if
> there are any modifications (i.e. send me the updated patch).

Sure. Will do it tomorrow first thing.
 
> I thought types with prefixed _ are basic Postgres types.
> Removing the _ types from the type_map do not break programs?

According to pg_type's definition at 

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=catalog-pg-type.html

PostgreSQL-Arrays have a non-zero typelem field containing the type of the
elements. Looking into the actual values in my pg_type, it's the _ variants
that are arrays and the types without _ are non-array types.
 
-- 
	Oliver M. Bolzer
	ol...@go...

GPG (PGP) Fingerprint = 621B 52F6 2AC1 36DB 8761  018F 8786 87AD EF50 D1FF

Re: [Ruby-DBI-devel] ruby-dbi SQLite bug

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

On Mon, May 12, 2003 at 04:01:43PM +0100, Brian Candler wrote:
> I have found a bug using ruby-dbi-0.0.18 or 0.0.19 with sqlite-2.8.0. I am
> running under ruby-1.6.8 and FreeBSD-4.7
> 
> The attached code replicates it: you prepare a statement, execute it
> (for parameters giving no results), then execute it again (for a parameter
> which gives a non-empty result set). The second execution causes a segv
> crash when you call 'fetch' to get the results.
> 
> Any takers?

I've found the bug.

The problem was, when executing a statement that produces an empty row,
no column information was available, which is required when fetching a
row. As DBD::SQLite caches the column information, but it's not set
during the first execute call, the fetch failed.

Here the patch (it's in CVS):


diff -r1.5 SQLite.c
478c478
<   if (rb_iv_get(self, "@col_info") == Qnil) {
---
>   if (rb_iv_get(self, "@col_info") == Qnil || RARRAY(rb_iv_get(self, "@col_info"))->len == 0) {


Regards,

  Michael

Re: [Ruby-DBI-devel] Array support for DBD::Pg

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

On Wed, May 14, 2003 at 05:57:09PM +0200, Oliver M. Bolzer wrote:
> Hi!
> 
> Currently, DBD::Pg does not support PostgreSQL's native Array types as
> described in
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=arrays.html
> 
> Attached are two patches that add support for
> - reading of Arrays
> - quote()ing of Arrays
> 
> I have submitted the reading-part before, but did not receive any response
> at that time. Seems like nobody is using PostgreSQL's arrays, except me :-)
> I'd really appreciate some kind of comment from the DBD::Pg maintainer.

Sorry, I had not much time in the last months. 
There is currently no active DBD::Pg maintainer (is there?). 

I'd be really happy if someone would take over this part, so I could
concentrate (i.e. spend my time) more with overall DBI issues.

What I really had in mind is, to completely separate the DBDs from the DBI
(= different projects). Doing this, updates on single DBDs
would come out much quicker than today it's the case.

> Now let me explain the patches:
> 
> pg_array_quote.diff:
> 
> PostgreSQL-Arrays are surronded by single quotes, the array delimiters
>  are{ and }, the elements separated with comma. The problem is, that inside
> the array, different quotingrules apply than usual. E.g. a Strings and Dates
> are quoted with double-quotes instead of the usual single-quotes. Additionally,
> all ' and \ must be extra escaped because the array as a whole is handled as
> String in SQL.
> 
> To cope with this, an extra private function quote_array_elements() is added
> to quote array-elements. This method is called from quote(), when the object
> to be quoted is an Array.
> 
> The patch is based on Pg.rb 1.27 . I just discovered that in 1.28, support for
> PGconn.quote was added. But the quoting of Arrays of PGconn.quote is WRONG.
> So slight modifications would be additionally needed to bypass PGconn.quote if
> the value is an array and use my quoting mechanism instead. 

Can you try the patch with the newest version (1.31) of Pg.rb and tell me if
there are any modifications (i.e. send me the updated patch).

> pg_array.read.diff:
> 
> The goal is to get an array of the respectable type returned by DBD::Pg instead
> of Strings like "{1,2,3}" when selecting a column with an array-type.
> For this I've improved load_type_map() to register array-types and the type of
> their elements. Entries with an _ in front of them are actually arrays, according
> to the PostgreSQL documentation.

I thought types with prefixed _ are basic Postgres types.
Removing the _ types from the type_map do not break programs?

Regards,

  Michael

Re: [Ruby-DBI-devel] dbd_sqlite and row processed count

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

On Thu, May 15, 2003 at 12:24:04AM +0900, MoonWolf wrote:
> Brian Candler wrote:
> > "after 'UPDATE' statement, RPC is incorrect" (and the subject implies there
> > was a patch provided)
> > 
> > However I can't find the patch, and this behaviour is still wrong in
> > ruby-dbi-0.0.19: see attached, running with ruby-1.6.8 and SQLite-2.8.0
> 
> SQLite.c.patch.030213b:
>   * fix Row Processed Count(sqlite_changes() function)
>   * fix DBI::Timestamp quote format for Timestamp sorting
>    before '2003-2-13 1.2.3.0' => after '2003-02-13 01:02:03'

Thanks! I've patched the sources.

Regards,

  Michael

Re: [Ruby-DBI-devel] dbd_sqlite and row processed count

[Brian C. <B.C...@po...>]

On Thu, May 15, 2003 at 12:24:04AM +0900, MoonWolf wrote:
> SQLite.c.patch.030213b:
>   * fix Row Processed Count(sqlite_changes() function)
>   * fix DBI::Timestamp quote format for Timestamp sorting
>    before '2003-2-13 1.2.3.0' => after '2003-02-13 01:02:03'

Works perfectly. Thank you!

Brian.

[Ruby-DBI-devel] Array support for DBD::Pg

[Oliver M. B. <ol...@go...>]

Hi!

Currently, DBD::Pg does not support PostgreSQL's native Array types as
described in
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=arrays.html

Attached are two patches that add support for
- reading of Arrays
- quote()ing of Arrays

I have submitted the reading-part before, but did not receive any response
at that time. Seems like nobody is using PostgreSQL's arrays, except me :-)
I'd really appreciate some kind of comment from the DBD::Pg maintainer.

Now let me explain the patches:

pg_array_quote.diff:

PostgreSQL-Arrays are surronded by single quotes, the array delimiters
 are{ and }, the elements separated with comma. The problem is, that inside
the array, different quotingrules apply than usual. E.g. a Strings and Dates
are quoted with double-quotes instead of the usual single-quotes. Additionally,
all ' and \ must be extra escaped because the array as a whole is handled as
String in SQL.

To cope with this, an extra private function quote_array_elements() is added
to quote array-elements. This method is called from quote(), when the object
to be quoted is an Array.

The patch is based on Pg.rb 1.27 . I just discovered that in 1.28, support for
PGconn.quote was added. But the quoting of Arrays of PGconn.quote is WRONG.
So slight modifications would be additionally needed to bypass PGconn.quote if
the value is an array and use my quoting mechanism instead. 

pg_array.read.diff:

The goal is to get an array of the respectable type returned by DBD::Pg instead
of Strings like "{1,2,3}" when selecting a column with an array-type.
For this I've improved load_type_map() to register array-types and the type of
their elements. Entries with an _ in front of them are actually arrays, according
to the PostgreSQL documentation.
If an retrieved column is an array, it is passed to the new function
convert_array(), which is a nasty beast that parses the string that comes
out of PostgreSQL in to (possibly nested) Ruby-Arrays.

--
	Oliver M. Bolzer
	ol...@go...

GPG (PGP) Fingerprint = 621B 52F6 2AC1 36DB 8761  018F 8786 87AD EF50 D1FF

Re: [Ruby-DBI-devel] dbd_sqlite and row processed count

[MoonWolf <moo...@mo...>]

Brian Candler wrote:
> "after 'UPDATE' statement, RPC is incorrect" (and the subject implies there
> was a patch provided)
> 
> However I can't find the patch, and this behaviour is still wrong in
> ruby-dbi-0.0.19: see attached, running with ruby-1.6.8 and SQLite-2.8.0

SQLite.c.patch.030213b:
  * fix Row Processed Count(sqlite_changes() function)
  * fix DBI::Timestamp quote format for Timestamp sorting
   before '2003-2-13 1.2.3.0' => after '2003-02-13 01:02:03'

--
MoonWolf

[Ruby-DBI-devel] dbd_sqlite and row processed count

[Brian C. <B.C...@po...>]

On 9th February, moonwolf wrote at
http://sourceforge.net/mailarchive/message.php?msg_id=3765112

"after 'UPDATE' statement, RPC is incorrect" (and the subject implies there
was a patch provided)

However I can't find the patch, and this behaviour is still wrong in
ruby-dbi-0.0.19: see attached, running with ruby-1.6.8 and SQLite-2.8.0

According to the web CVS interface, src/ext/dbd_sqlite/SQLite.c hasn't
changed for 10 months.

Has anyone got a copy of the patch they could send me?

Thanks...

Brian.

[Ruby-DBI-devel] ruby-dbi SQLite bug

[Brian C. <B.C...@po...>]

I have found a bug using ruby-dbi-0.0.18 or 0.0.19 with sqlite-2.8.0. I am
running under ruby-1.6.8 and FreeBSD-4.7

The attached code replicates it: you prepare a statement, execute it
(for parameters giving no results), then execute it again (for a parameter
which gives a non-empty result set). The second execution causes a segv
crash when you call 'fetch' to get the results.

Any takers?

Please CC me directly on replies, I'm not a list member.

Regards,

Brian.

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

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

On Sat, May 10, 2003 at 04:12:24AM +0200, Florian G. Pflug wrote:
> On Fri, May 09, 2003 at 10:19:30PM +0200, Michael Neumann wrote:
> > There's currently no other database module that has such pseudo-async
> > methods (or I am simply not aware of it).
> 
> I just checked - the mysql module seems to have no support.
> The "old" (oracle 7) compatible module doesn't seem to have support either
> (altough I'm not sure with oracle... the oci interface is more complex than
> the mysql or postgres interface, so I might have misread the code)
> 
> But there is a oci8 interface for ruby (http://www.jiubao.org/ruby-oci8/).
> This also includes a DBI Driver (DBD::OCI8). This driver implements the flag
> "NonBlocking" to let the user decide indicate which behaviour he prefers.

Yes, this flags to be the same as the async_exec method in Pg. I agree
with you that we should reuse this flag. 

OCI8 disable NonBlocking by default. We should do the same for DBD::Pg. 

> > I've come to the conclusion that the best probably is to introduce a
> > "pg_async_exec" flag and then dependend on this flag execute either
> > async_exec or exec in the Statement#execute method.
> 
> See above. Since there is a oracle DBD, and it has this kind of flag, I
> think it would be best to name it the same ("NonBlocking"). Come to think if
> it I also believe, that "NonBlocking" better describes what it actually
> does, since from a users point of view exec() and query() calls are still
> synchronous - they just don't influence other threads.

I'd even like "Blocking" as flag more, because when NonBlocking=false you have 
to think a bit more if it's now blocking or not :-) (at least this is the case 
for my lazy mind).
But luckily it's not the case that one would use this flag very often in a
program.

> > Note that all other
> > methods still call exec. As those calls usually issue COMMIT, ROLLBACK
> > or BEGIN statements, I don't think there's a big differece when using 
> > async_exec.  But of course I may be wrong. Or should I use everywhere 
> > the async method when the flag is set?
> 
> IMHO you need to use for every query that may potentially take a long time.
> Remember, when a query is issued using the "synchronous" call
> (exec instead of async_exec), _all_ _other_ _threads_ are blocked until
> the query completes. 

No problem. So I'll use it for every statement.

> "BEGIN" (and possibly "ROLLBACK") are usually quite fast, I guess. But
> "COMMIT" could take quite a time of you did a lot of inserts and updates in
> the transaction (or so I think).

Thanks for your comments.


Regards,

  Michael

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

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

On Fri, May 09, 2003 at 10:19:30PM +0200, Michael Neumann wrote:
> There's currently no other database module that has such pseudo-async
> methods (or I am simply not aware of it).

I just checked - the mysql module seems to have no support.
The "old" (oracle 7) compatible module doesn't seem to have support either
(altough I'm not sure with oracle... the oci interface is more complex than
the mysql or postgres interface, so I might have misread the code)

But there is a oci8 interface for ruby (http://www.jiubao.org/ruby-oci8/).
This also includes a DBI Driver (DBD::OCI8). This driver implements the flag
"NonBlocking" to let the user decide indicate which behaviour he prefers.

-----------------------------------------------------------------
class Driver < DBI::BaseDriver
...
...
def connect( dbname, user, auth, attr )
    handle = ::OCI8.new(user, auth, dbname, attr['Privilege'])
    handle.non_blocking = true if attr['NonBlocking']
    return Database.new(handle, attr)
  rescue OCIException => err
    raise raise_error(err)
end
...
...
-----------------------------------------------------------------

> I've come to the conclusion that the best probably is to introduce a
> "pg_async_exec" flag and then dependend on this flag execute either
> async_exec or exec in the Statement#execute method.

See above. Since there is a oracle DBD, and it has this kind of flag, I
think it would be best to name it the same ("NonBlocking"). Come to think if
it I also believe, that "NonBlocking" better describes what it actually
does, since from a users point of view exec() and query() calls are still
synchronous - they just don't influence other threads.


> Note that all other
> methods still call exec. As those calls usually issue COMMIT, ROLLBACK
> or BEGIN statements, I don't think there's a big differece when using 
> async_exec.  But of course I may be wrong. Or should I use everywhere 
> the async method when the flag is set?

IMHO you need to use for every query that may potentially take a long time.
Remember, when a query is issued using the "synchronous" call
(exec instead of async_exec), _all_ _other_ _threads_ are blocked until
the query completes. 

"BEGIN" (and possibly "ROLLBACK") are usually quite fast, I guess. But
"COMMIT" could take quite a time of you did a lot of inserts and updates in
the transaction (or so I think).

greetings, Florian Pflug

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

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

On Wed, May 07, 2003 at 11:03:46AM +0200, Florian G. Pflug wrote:
> On Wed, May 07, 2003 at 10:22:16AM +0200, Michael Neumann wrote:
> > > 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 beliebe that other database backends might have the same problem - and for
> some the fix might hurt performance alot. Thats why after thinking about
> this a bit, I believe this should be made an option, but the default should
> be "pseudo-asynchronous" (because in the average case it's the more sensible
> behaviour)

There's currently no other database module that has such pseudo-async
methods (or I am simply not aware of it).

I've come to the conclusion that the best probably is to introduce a
"pg_async_exec" flag and then dependend on this flag execute either
async_exec or exec in the Statement#execute method. Note that all other
methods still call exec. As those calls usually issue COMMIT, ROLLBACK
or BEGIN statements, I don't think there's a big differece when using 
async_exec.  But of course I may be wrong. Or should I use everywhere 
the async method when the flag is set?

Also note, that pg_async_exec will be true by default.

> Maybe some generic name like "assume_fastquery" would be best - since it
> would depend on the backend if it his information is used, and in which way.

As long as there are no other database modules, I am against this.


Regards,

  Michael

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

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

On Wed, May 07, 2003 at 10:22:16AM +0200, Michael Neumann wrote:
> > 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 beliebe that other database backends might have the same problem - and for
some the fix might hurt performance alot. Thats why after thinking about
this a bit, I believe this should be made an option, but the default should
be "pseudo-asynchronous" (because in the average case it's the more sensible
behaviour)

Maybe some generic name like "assume_fastquery" would be best - since it
would depend on the backend if it his information is used, and in which way.

greetings, Florian Pflug

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

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

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

Hi

sorry to reply to my own message - but after thinking a bit, I came to the
conclusion that there might be a noticeable performance penality for a lot
of very fast selects (or inserts, but I guess selects are much faster than
inserts in general).

This is due to the fact that the pseudo-asynchronous functions always
activate the scheduler at least once (when doing rb_thread_select), and I
guess this means that they have to wait for other threads to do their work
(if there are non-blocked other threads) before they can continue.

This might be what a user wants (if the other threads maintain a gui, this
is most certainly what the user wants), but in some cases one might prefer
the "traditional" behaviour. 

So, maybe the most elegant way is to introduct a parameter to the
query-executing functions which lets the user decide what behaviour he
prefers. I would still suggest to make the pseudo-asynchronous behaviour the
default, since in most cases it won't have noticeable disadvantages, but
very noticeable advantages.

Greetings, Florian Pflug

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

[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

[Ruby-DBI-devel] [mneumann@ntecs.de: Re: ruby-postgres blocks all threads]

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

----- Forwarded message from Michael Neumann <mne...@nt...> -----

Envelope-to: fgp@localhost
Delivery-date: Tue, 06 May 2003 14:22:43 +0200
From: Michael Neumann <mne...@nt...>
To: "Florian G. Pflug" <fg...@ph...>
Subject: Re: ruby-postgres blocks all threads

On Tue, May 06, 2003 at 01:05:05PM +0200, Florian G. Pflug wrote:
> Hi
> 
> I discovered today that ruby-postgres uses the blocking postgres api when
> calling PGconn::exec / PGconn::query. This implies, that all other ruby
> threads are blocked until a result is available.
> 
> There are, however, the function PGconn::async_exec and PGconn::async_query
> which properly use the nonblocking postgresql api, and use rb_thread_select
> to put the calling thread to sleep until the database backend responds.
> 
> These functions are not, as the name might imply, truly asynchronous, in the
> sense that they return immediatly after sending the query. They seem (at
> least when reading the code) to be a drop-in replacement for exec and query,
> but with a more cooperative behaviour.

And I thought they were truly asynchronous, so I did not used them.

But there might be performance issues. Could you write a simple
benchmark, comparing the time needed to insert several thousand rows
into Postgres with both variants?

> I would recommend to use those functions in the postgres dbd module.
> 
> I did not attach a patch, because simply replacing exec with async_exec is
> probably easier than applying a patch ;-))

That's indeed easier.

> greetings, Florian Pflug

Please post this mail for discussion to the ruby-dbi-devel mailing-list, too.

Thanks!


Regards,

  Michael


----- End forwarded message -----

[Ruby-DBI-devel] ruby-postgres blocks all threads

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

Hi

I discovered today that ruby-postgres uses the blocking postgres api when
calling PGconn::exec / PGconn::query. This implies, that all other ruby
threads are blocked until a result is available.

There are, however, the function PGconn::async_exec and PGconn::async_query
which properly use the nonblocking postgresql api, and use rb_thread_select
to put the calling thread to sleep until the database backend responds.

These functions are not, as the name might imply, truly asynchronous, in the
sense that they return immediatly after sending the query. They seem (at
least when reading the code) to be a drop-in replacement for exec and query,
but with a more cooperative behaviour.

I would recommend to use those functions in the postgres dbd module.

I did not attach a patch, because simply replacing exec with async_exec is
probably easier than applying a patch ;-))

greetings, Florian Pflug