Fei Liu wrote:
> Fei Liu wrote:
>> Jeroen T. Vermeulen wrote:
>>
>>> On Thu, July 12, 2007 23:03, Fei Liu wrote:
>>>
>>> Hello Liu,
>>>
>>>
>>>> My implementation is now using a 120 pool size (server is
>>>> configured to
>>>> allow 128 concurrently), my bottle neck now is the database. My
>>>> connection pool is quickly exhausted in my test. What's a good
>>>> strategy
>>>> to improve the performance? I am thinking of queueing the database
>>>> requests and only execute them when a certain number (say 50 sql
>>>> statements) is reached?
>>>>
>>> I think 120 is probably still too many. One of the points of having a
>>> connection pool is that you can create fewer connections and reuse
>>> them. Many web applications get by on <20, as far as I know. Don't
>>> create this
>>> many unless your testing shows that (1) you need it and (2) it really
>>> helps.
>>>
>>> What you can do is make sure your pooled connections are released
>>> regularly, so hopefully you don't need so many. You could allocate
>>> them
>>> on a per-transaction basis: lock pool, grab connection, unlock pool,
>>> open
>>> transaction, do work, commit, destroy transaction, lock pool, release
>>> connection, unlock pool. "Grabbing" or "releasing" a connection
>>> could be
>>> as simple as taking it out of a list or setting an "I own it" flag
>>> somewhere.
>>>
>>>
>>>
>>>> In my case the postgres server simply stops responding after a
>>>> while and
>>>> allocated connection is not released from client...But CPU load is
>>>> never
>>>> really very high...Maybe there is something else wrong.
>>>>
>>> That's beginning to sound more like a postgres issue; people at
>>> [EMAIL PROTECTED] may know more about it.
>>>
>>>
>>> Jeroen
>>>
>>>
>>>
>>
>> Thanks for the comments, I'll do some digging and hopefully have
>> something to report back.
>>
>> Fei
>>
>>
> It appears my multi-thread application (100 connections every 5
> seconds) is stalled when working with postgresql database server. I
> have limited number of connections in my connection pool to postgresql
> to 20. At the begining, connection is allocated and released from
> connection pool as postgres serves data request. The pool can recover
> from exhaustion. But very quickly (after about 400 client requests),
> it seems postgres server stops serving and connection to postgres
> server is not released any more resulting a resource exhausting for
> clients.
>
> I am using a slightly model to protect thread generation. I have
> another thread pool that limits number of concurrent threads to about
> 30. I have enclosed my wrapper interface to libpqxx. Maybe I did
> something wrong with my wrapper? My client code uses strictly the
> transactor interface. What's the implementation strategy of
> pqxx::transactor<> ?
>
> Fei
> unix 3 [ ] STREAM CONNECTED 1693655
> 31976/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693654
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693653
> 31975/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693652
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693651
> 31974/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693650
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693649
> 31973/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693648
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693647
> 31972/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693646
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693645
> 31971/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693644
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693641
> 31969/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693640
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693639
> 31968/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693638
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693637
> 31967/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693636
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693585
> 31941/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693584
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693583
> 31940/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693582
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693581
> 31939/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693580
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693579
> 31938/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693578
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693577
> 31937/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693576
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693575
> 31936/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693574
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693573
> 31935/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693572
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693571
> 31934/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693570
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693427
> 31851/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693426
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693425
> 31777/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693424
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693419
> 31764/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693418
> 31740/ns_ge_classif
>
> After about 10 minutes, they are still there, they are never released...
>
> unix 3 [ ] STREAM CONNECTED 1693655
> 31976/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693654
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693653
> 31975/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693652
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693651
> 31974/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693650
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693649
> 31973/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693648
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693647
> 31972/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693646
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693645
> 31971/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693644
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693641
> 31969/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693640
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693639
> 31968/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693638
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693637
> 31967/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693636
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693585
> 31941/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693584
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693583
> 31940/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693582
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693581
> 31939/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693580
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693579
> 31938/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693578
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693577
> 31937/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693576
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693575
> 31936/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693574
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693573
> 31935/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693572
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693571
> 31934/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693570
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693427
> 31851/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693426
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693425
> 31777/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693424
> 31740/ns_ge_classif
> unix 3 [ ] STREAM CONNECTED 1693419
> 31764/postgres: pos /tmp/.s.PGSQL.5583
> unix 3 [ ] STREAM CONNECTED 1693418
> 31740/ns_ge_classif
>
> ------------------------------------------------------------------------
>
>
Resource pool behavior:
allocating 1
allocating 2
allocating 3
allocating 4
allocating 5
releasing 2
allocating 2
allocating 6
allocating 7
allocating 8
allocating 9
allocating 10
allocating 11
releasing 4
allocating 4
allocating 12
allocating 13
releasing 5
allocating 5
releasing 9
allocating 9
allocating 14
allocating 15
allocating 16
allocating 17
allocating 18
allocating 19
allocating 20
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
releasing 5
allocating 5
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
releasing 10
allocating 10
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
releasing 15
allocating 15
releasing 20
releasing 13
allocating 13
allocating 20
releasing 14
allocating 14
releasing 17
allocating 17
releasing 18
allocating 18
releasing 17
allocating 17
releasing 17
allocating 17
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
resource pool exhausted, waiting for release ...
Never recovers..
_______________________________________________
Libpqxx-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
