[Pgpool-general] slow pgpool-II-3.1

[Olivier NOEL]

2011/10/6 Guillaume Lelarge <guillaume at lelarge.info>:
> On Thu, 2011-10-06 at 17:27 +0200, Olivier NOEL wrote:
>> 2011/10/6 Guillaume Lelarge <guillaume at lelarge.info>:
>>
>> [...]
>>
>> >> And it's not. Ok, there is some overhead, but it's veeery slow.
>> >>
>> >
>> > How did you check that?
>> >
>> > You may be right in a specific setting, but I have no issues getting
>> > better performance with pgpool.
>>
>> We tested our java webapp with pgpool and without pgpool, same
>> servers, we only change the target IP in the JDBC config file in
>> Tomcat.
>>
>
> I suppose you don't use a pooling mode in your java app? because they
> may interfere.

No pooling, just the basic configuration of JDBC.

>> We have a small XML processing servlet with informations to insert in
>> the databases (streaming replication). We verified that all requests a
>> balanced.
>>
>> XML sizes varies from 300KB to 3MB.
>>
>
> Not sure what you mean with this. Are your queries this size? What do
> you do with the XML? what kind of queries (SELECT, INSERT)?

My XML contains the data I have to insert in my database (list,
informations about customers, etc.), data is prepared and inserted.
All kind of queries DDL/DML/SELECT, etc.

>> Time to process the file completely for a fully functional database :
>>
>> - with pgpool : 20-45min (20min for the smaller ones to 45min for the big one)
>> - without pgpool : 4-15min
>>
>
> How many users? same PostgreSQL database, and same PostgreSQL user?

120 simultaneous users (webapp, max JDBC connections 25 per user),
same database and user, but we have the same problem with every
database.

>> Then we tested browsing in our webapp :
>>
>> - with pgpool : 30-45s to display a list of full customers
>> informations (depending on the numbers of customers, 2400 to 12000
>> customers on the same page)
>> - without pgpool : 5-10s
>>
>
> Same questions. How many users try to connect to pgpool? how long last
> their sessions? what kind of queries?

We have 120 simultaneous users with a maximum of 25 connections per
user (1000 connections per server, 3000 connections in pgpool)

Sessions? in JDBC? in webapp? in pgpool?
All kind of queries DML/DDL/SELECT, etc.

>
> --
> Guillaume
>  http://blog.guillaume.lelarge.info
>  http://www.dalibo.com
>
>