[Pharo-users] P3 and concurrenty

Sven Van Caekenberghe sven at stfx.eu
Mon Feb 10 06:13:10 EST 2020


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




More information about the Pharo-users mailing list