Leon Smith | 9 Jul 03:03 2013
Picon

Announcing postgresql-libpq-0.8.2.3

I just fixed a fairly serious performance problem with postgresql-libpq's binding to PQescapeStringConn;   in was exhibiting a non-linear slowdown when more strings are escaped and retained.  

https://github.com/lpsmith/postgresql-libpq/commit/adf32ff26cdeca0a12fa59653b49c87198acc9ae

If you are using postgresql-libpq's escapeStringConn,  or a library that uses it (e.g.  postgresql-simple,  or persistent-postgresql),  I do recommend upgrading.   You may or may not see a performance improvement,  depending on your particular use case,   but if you do it can be quite substantial.

It's not entirely clear to me what the root cause really is,  but it certainly appears as though it's related to the (direct) use of mallocBytes,   which was replaced with (indirect) calls to mallocForeignPtrBytes / mallocPlainForeignPtrBytes (through the bytestring package).   In this case,  it resulted in an asymptotic improvement in time complexity of some algorithms.

Best,
Leon
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
Joey Adams | 9 Jul 04:00 2013
Picon

Re: [database-devel] Announcing postgresql-libpq-0.8.2.3

On Mon, Jul 8, 2013 at 9:03 PM, Leon Smith <leon.p.smith <at> gmail.com> wrote:
I just fixed a fairly serious performance problem with postgresql-libpq's binding to PQescapeStringConn;   in was exhibiting a non-linear slowdown when more strings are escaped and retained.

 I'd like to point out a somewhat related bottleneck in postgresql-simple (but not postgresql-libpq).  Every PQescapeStringConn or PQescapeByteaConn call involves a withMVar, which is about 100ns on the threaded RTS on my system.  Taking the Connection lock once for the whole buildQuery call might be much faster, especially for multi-row inserts and updates.
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
Leon Smith | 9 Jul 06:17 2013
Picon

Re: [database-devel] Announcing postgresql-libpq-0.8.2.3

I'll have to benchmark withMVar on my system,  but (at least on my old laptop) a safe foreign function call is also on the order of 100ns.   As c_PQescapeStringConn and c_PQescapeByteaConn are currently safe calls,   that would limit the maximum time saved at ~50%.

Perhaps it would make sense to make these unsafe calls as well,  but the justification I used at the time was that the amount of time consumed by these functions is bounded by the length of the string being escaped,  which is itself unbounded.    Certainly postgresql-libpq is currently overly biased towards safe calls;  though when I took the bindings over it was overly biased towards unsafe calls.   (Though, arguably,  it's worse to err on the side of making ffi calls unsafe.)

I've also considered integrating calls to c_PQescapeStringConn with blaze-builder and/or bytestring-builder,  which could help a fair bit,  but would also introduce dependencies on the internals of these libraries when currently there is none.

There is certainly a lot of room for optimizing query generation in postgresql-simple,  this I've been well aware of since the beginning.   And it probably would be worthwhile to move to protocol-level parameters which would avoid the need for escaping value parameters altogether,  and open up the possibility of binary formats as well, which would be a huge performance improvement for things like numerical values and timestamps.    Although IIRC,  one downside is that this prevents multiple DML commands from being issued in a single request,  which would subtly change the interface postgresql-simple exports.  

Best,
Leon


On Mon, Jul 8, 2013 at 10:00 PM, Joey Adams <joeyadams3.14159 <at> gmail.com> wrote:
On Mon, Jul 8, 2013 at 9:03 PM, Leon Smith <leon.p.smith <at> gmail.com> wrote:
I just fixed a fairly serious performance problem with postgresql-libpq's binding to PQescapeStringConn;   in was exhibiting a non-linear slowdown when more strings are escaped and retained.

 I'd like to point out a somewhat related bottleneck in postgresql-simple (but not postgresql-libpq).  Every PQescapeStringConn or PQescapeByteaConn call involves a withMVar, which is about 100ns on the threaded RTS on my system.  Taking the Connection lock once for the whole buildQuery call might be much faster, especially for multi-row inserts and updates.

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

Gmane