[Pharo-users] P3 and concurrenty

Petter Egesund petter.egesund at gmail.com
Mon Feb 10 10:30:07 EST 2020


Hi and thanks for an answer above my expectations to our problem.

This is really awesome, I was just about to start coding a pool myself, but
getting it directly from the author of the base library is off course way
better :)

I will try out and get back as soon as we have tested.

One quick question - are the prepared statements which are connected to the
pool thread safe?

Petter

On Mon, Feb 10, 2020 at 12:13 PM Sven Van Caekenberghe <sven at stfx.eu> wrote:

> Hi Petter,
>
>
> https://github.com/svenvc/P3/commit/a6b409d0d92cb92bf9b44452908bb9033523b863
> adds a connection pool.
>
> Here is the class comment:
>
> ======
>
> I am P3ConnectionPool.
>
> I offer a pool of shared PSQL connections (P3Client instances) as a
> resource.
>
> After configuring me with at least a url, you use me by calling
> #withConnection:
>
>   pool := P3ConnectionPool url: 'psql://sven@localhost'.
>   pool withConnection: [ :p3Client |
>           p3Client query: 'SELECT table_name FROM
> information_schema.tables' ].
>   pool close.
>
> When a connection is in use, it is not part of the pool.
> When a connection is returned/released to the pool, it becomes available
> for reuse.
>
> The pool's size is the number of open connection ready to be reused.
> The pool's capacity is the maximum number of connection that will be
> pooled.
> Excess connections will be closed when they are returned/released to the
> pool.
>
> New connections are created as needed.
> You can set a #configurator to further initialize new connections.
> You can use #warmUp or #warmUp: to precreate a number of connections.
>
> When an error occurs, the connection should not be reused and be closed by
> the caller.
>
> ======
>
> There are some unit tests as well.
>
> Let me know if this works for you.
>
> Sven
>
> > On 9 Feb 2020, at 19:04, Petter Egesund <petter.egesund at gmail.com>
> wrote:
> >
> > Yes, thanks for good feedback.
> >
> > I will try the pooled way, I think - not primarily because of speed, but
> due to that our library is built around prepared connections.
> >
> > Petter
> >
> > On Sun, Feb 9, 2020 at 6:01 PM Sven Van Caekenberghe <sven at stfx.eu>
> wrote:
> > Hi Petter,
> >
> > > On 9 Feb 2020, at 17:27, Petter Egesund <petter.egesund at gmail.com>
> wrote:
> > >
> > > Hi Sven and thanks for answering!
> > >
> > > I use Teapot with one common sql-connecton, not one for each user
> session. At startup I create several sql statements and these does not seem
> to be usable from different Teapot request at the same time.
> >
> > It is useable, but not concurrently.
> >
> > > I could create one connection pr. session and then close the database
> connection when the user leaves, but then I also would need to create all
> the prepared sql-statements for each session, which does not sound right to
> me?
> >
> > Yes and no, see further.
> >
> > > It seems I have will have to look into the other solution, and see if
> I can use some mutex stuff to avoid several tasks acessessing the same
> resources at the same time.
> >
> > Yes, follow the pointer that I gave you, it is not hard.
> >
> > > Creating a pool sounds like the right solution to me now - any meaning
> about this?
> >
> > Yes, you could create a connection pool. But that is harder than it
> sounds: what is the minimum size, the maximum size, what do you do when you
> go over it, how do you make sure that a resource (connection) is clean when
> returning it to the pool (given authentication, possible errors), ...
> >
> > I don't know what you are doing, but I think you focus too much on
> performance issues. I would first try to get the code correct and worry
> about performance later on.
> >
> > Running
> >
> >   P3ClientTest new runBenchmark1Bench.
> >
> > on my machine gives me a BenchmarkResult(67 iterations in 5 seconds 57
> milliseconds. 13.249 per second). This is a query that returns 10.000
> records with 5 columns. It is reusing the same client/connection for all
> iterations.
> >
> > If I modify this slightly to use a new client/connection each time, like
> this
> >
> >   [
> >     (P3Client url: 'psql://sven@localhost')
> >       query: 'SELECT * FROM benchmark1';
> >       close
> >   ] benchFor: 5 seconds
> >
> > I get a BenchmarkResult(65 iterations in 5 seconds 34 milliseconds.
> 12.912 per second) which almost as fast. Of course, for smaller queries,
> the connect/disconnect overhead will be more significant.
> >
> > And note that this is not using prepared statements.
> >
> > So I would start by opening/closing a connection each time you need it.
> >
> > HTH,
> >
> > Sven
> >
> > > Petter
> > >
> > >
> > > On Sun, Feb 9, 2020 at 4:52 PM Sven Van Caekenberghe <sven at stfx.eu>
> wrote:
> > > Hi Petter,
> > >
> > > [ CC-ing the Pharo Users list ]
> > >
> > > P3Client is not built/designed to be used by multiple processes
> concurrently. Each database connection is represented by an instance of
> P3Client and holds some state both at the client as well as at the server
> side.
> > >
> > > Typically, in a multi user server application, each connection should
> have its own P3Client / psql connection. For example, in Seaside, a custom
> WASession subclass gives each session/user its own p3 connection/client.
> > >
> > > Is that what you are doing ?
> > >
> > > If not, you could wrap your db accessing code so that mutual exclusion
> is provided. For example, you can have a look at AbstractCache
> #beThreadSafe and #critical:
> > >
> > > That will then serialise requests and possibly block one onto the
> other.
> > >
> > > HTH,
> > >
> > > Sven
> > >
> > > PS: another thing to take care of if closing your sql connections when
> the session is no longer needed.
> > >
> > > PS: Zinc HTTP does also provide a session mechanism
> (ZnServerSession[Manager]) but these work with cookies and typically won't
> help with a REST access pattern.
> > >
> > > > On 9 Feb 2020, at 14:21, Petter Egesund <petter.egesund at gmail.com>
> wrote:
> > > >
> > > > Hi Sven
> > > >
> > > > We are using Pharo as our backend in a project and we have run into
> a problem with P3.
> > > >
> > > > The problem seems to be connected to compiled sql statements and
> concurrency.
> > > >
> > > > We keep getting this error: Bindcomplete message expected
> > > >
> > > > Problem seems to be easy to reproduce:
> > > >
> > > > 1) Compile any sql statement
> > > > 2) Use this statement in a query twice (!) in a teapot endpoint
> > > >
> > > > The run some concurrent queries, like "curl
> http://localhost:8080/endpoint & curl http://localhost:8080/endpoint.."
> (add several curls after here).
> > > >
> > > > One could also use ex. siege (
> https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html) for easy
> reproducing.
> > > >
> > > > If we chain the curls after each other, like "curl
> http://localhost:8080/endpoint &&
> https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html &&
> https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html.." it seems
> to work fine, so doing the request sequentially seem to work fine.
> > > >
> > > > My conclusion is that this must be connected to how teapot handles
> concurrency in companion with the compiled statements?
> > > >
> > > > Any clues on this one? We are on Pharo 8.0 with latest version of
> P3, PG 9.x)
> > > >
> > > > Best regards
> > > >
> > > > Petter Egesund (I wrote the heysql-package based on P3)
> > >
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pharo.org/pipermail/pharo-users_lists.pharo.org/attachments/20200210/0fcd5f7c/attachment-0001.html>


More information about the Pharo-users mailing list