[Pharo-users] P3 and concurrenty

Petter Egesund petter.egesund at gmail.com
Mon Feb 10 10:46:23 EST 2020


Yes, I see - that sounds sensible.

Could it be an idea to subclass the pool-class and to prepare statements in
this method, if there is a kind of init-method in the class? What do you
think?

Petter

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

>
>
> > On 10 Feb 2020, at 16:30, Petter Egesund <petter.egesund at gmail.com>
> wrote:
> >
> > 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?
>
> Prepared statements are scoped to a single connection in PSQL, AFAIK.
>
> What you will probably want to do is use a #configurator: block to set up
> your prepared statements once, like you do now. Then each thread/process
> grabs its own connection from the pool, (which will be already configured
> properly) and uses it, for itself, and finally returns it to the pool.
>
> Interacting with the pool should be fully thread safe. Once you are using
> a connection, it not longer is, but is also does not have to, since one
> connection equals one client thread.
>
> > 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/22c323e1/attachment-0001.html>


More information about the Pharo-users mailing list