Re: [Pgpool-general] slow pgpool-II-3.1
On Thu, 2011-10-06 at 18:44 +0200, Olivier NOEL wrote: > 2011/10/6 Guillaume Lelarge : > > On Thu, 2011-10-06 at 17:27 +0200, Olivier NOEL wrote: > >> 2011/10/6 Guillaume Lelarge : > >> > >> [...] > >> > >> >> 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. > Is JDBC autocommit on? > >> 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? Sessions in PostgreSQL. > All kind of queries DML/DDL/SELECT, etc. > The numbers you provided show your app to be a lot slower with pgpool than without. I fail to see why. In my experience, it usually is much faster. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] slow pgpool-II-3.1
2011/10/6 Guillaume Lelarge : > On Thu, 2011-10-06 at 17:27 +0200, Olivier NOEL wrote: >> 2011/10/6 Guillaume Lelarge : >> >> [...] >> >> >> 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 > > ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] slow pgpool-II-3.1
On Thu, 2011-10-06 at 17:27 +0200, Olivier NOEL wrote: > 2011/10/6 Guillaume Lelarge : > > [...] > > >> 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. > 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)? > 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? > 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? -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] slow pgpool-II-3.1
On Thu, Oct 6, 2011 at 5:27 PM, Olivier NOEL wrote: > 2011/10/6 Guillaume Lelarge : > > [...] > > >> 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. > > 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. > > 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 > > 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 > > Pgpool *should* be faster, but it isn't. > Any response to this? I really get slower database connection from pgpool comparing with connection directly to master node. > > > > > -- > > Guillaume > > http://blog.guillaume.lelarge.info > > http://www.dalibo.com > > > > > -- Armin ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] slow pgpool-II-3.1
2011/10/6 Guillaume Lelarge : [...] >> 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. 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. 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 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 Pgpool *should* be faster, but it isn't. > > -- > Guillaume > http://blog.guillaume.lelarge.info > http://www.dalibo.com > > ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] slow pgpool-II-3.1
On Thu, 2011-10-06 at 16:28 +0200, Olivier NOEL wrote: > 2011/10/6 Guillaume Lelarge : > > On Thu, 2011-10-06 at 14:56 +0200, Armin Nesiren wrote: > >> On Thu, Oct 6, 2011 at 2:48 PM, Guillaume Lelarge > >> wrote: > >> > >> > On Thu, 2011-10-06 at 14:36 +0200, Armin Nesiren wrote: > >> > > Hi everyone, > >> > > > >> > > I'm experiencing performance issues with pgpool-3.1 > >> > > Problem is that when I connect through pgpool to database, database > >> > > works > >> > > slower than > >> > > when I connect directly to master. In this test case, I have disabled > >> > node2, > >> > > just to check performance > >> > > directly and through pgpool. > >> > > > >> > > What can be a problem? > >> > > >> > If I understand correctly, you only have one PostgreSQL server. And your > >> > client goes through pgpool to reach the PostgreSQL server. How can you > >> > expect that it would work faster that one PostgreSQL server alone? > >> > > >> > It'll work faster only if you have more than one PostgreSQL server. And > >> > more than one client. > >> > >> > >> No, with two servers (two nodes) work slower, also with one server through > >> pgpool > >> work slower than directly, I would expect to work same through pgpool and > >> directly. > >> > > > > With one server and one client, through pgpool, that can't be the same. > > pgpool decodes all the client's queries, which takes some time. IOW, > > there is an overhead. But when you have lots of clients, they usually > > are faster. And really faster with more than one PostgreSQL server. > > 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. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] slow pgpool-II-3.1
2011/10/6 Guillaume Lelarge : > On Thu, 2011-10-06 at 14:56 +0200, Armin Nesiren wrote: >> On Thu, Oct 6, 2011 at 2:48 PM, Guillaume Lelarge >> wrote: >> >> > On Thu, 2011-10-06 at 14:36 +0200, Armin Nesiren wrote: >> > > Hi everyone, >> > > >> > > I'm experiencing performance issues with pgpool-3.1 >> > > Problem is that when I connect through pgpool to database, database works >> > > slower than >> > > when I connect directly to master. In this test case, I have disabled >> > node2, >> > > just to check performance >> > > directly and through pgpool. >> > > >> > > What can be a problem? >> > >> > If I understand correctly, you only have one PostgreSQL server. And your >> > client goes through pgpool to reach the PostgreSQL server. How can you >> > expect that it would work faster that one PostgreSQL server alone? >> > >> > It'll work faster only if you have more than one PostgreSQL server. And >> > more than one client. >> >> >> No, with two servers (two nodes) work slower, also with one server through >> pgpool >> work slower than directly, I would expect to work same through pgpool and >> directly. >> > > With one server and one client, through pgpool, that can't be the same. > pgpool decodes all the client's queries, which takes some time. IOW, > there is an overhead. But when you have lots of clients, they usually > are faster. And really faster with more than one PostgreSQL server. And it's not. Ok, there is some overhead, but it's veeery slow. I have 3 servers (streaming replication) with 1 pgpool (dedicated server with 8GB RAM) > -- > Guillaume > http://blog.guillaume.lelarge.info > http://www.dalibo.com > > ___ > Pgpool-general mailing list > Pgpool-general@pgfoundry.org > http://pgfoundry.org/mailman/listinfo/pgpool-general > ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] slow pgpool-II-3.1
On Thu, 2011-10-06 at 14:56 +0200, Armin Nesiren wrote: > On Thu, Oct 6, 2011 at 2:48 PM, Guillaume Lelarge > wrote: > > > On Thu, 2011-10-06 at 14:36 +0200, Armin Nesiren wrote: > > > Hi everyone, > > > > > > I'm experiencing performance issues with pgpool-3.1 > > > Problem is that when I connect through pgpool to database, database works > > > slower than > > > when I connect directly to master. In this test case, I have disabled > > node2, > > > just to check performance > > > directly and through pgpool. > > > > > > What can be a problem? > > > > If I understand correctly, you only have one PostgreSQL server. And your > > client goes through pgpool to reach the PostgreSQL server. How can you > > expect that it would work faster that one PostgreSQL server alone? > > > > It'll work faster only if you have more than one PostgreSQL server. And > > more than one client. > > > No, with two servers (two nodes) work slower, also with one server through > pgpool > work slower than directly, I would expect to work same through pgpool and > directly. > With one server and one client, through pgpool, that can't be the same. pgpool decodes all the client's queries, which takes some time. IOW, there is an overhead. But when you have lots of clients, they usually are faster. And really faster with more than one PostgreSQL server. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] slow pgpool-II-3.1
On Thu, Oct 6, 2011 at 2:48 PM, Guillaume Lelarge wrote: > On Thu, 2011-10-06 at 14:36 +0200, Armin Nesiren wrote: > > Hi everyone, > > > > I'm experiencing performance issues with pgpool-3.1 > > Problem is that when I connect through pgpool to database, database works > > slower than > > when I connect directly to master. In this test case, I have disabled > node2, > > just to check performance > > directly and through pgpool. > > > > What can be a problem? > > If I understand correctly, you only have one PostgreSQL server. And your > client goes through pgpool to reach the PostgreSQL server. How can you > expect that it would work faster that one PostgreSQL server alone? > > It'll work faster only if you have more than one PostgreSQL server. And > more than one client. No, with two servers (two nodes) work slower, also with one server through pgpool work slower than directly, I would expect to work same through pgpool and directly. > > -- > Guillaume > http://blog.guillaume.lelarge.info > http://www.dalibo.com > > -- Armin ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] slow pgpool-II-3.1
On Thu, 2011-10-06 at 14:36 +0200, Armin Nesiren wrote: > Hi everyone, > > I'm experiencing performance issues with pgpool-3.1 > Problem is that when I connect through pgpool to database, database works > slower than > when I connect directly to master. In this test case, I have disabled node2, > just to check performance > directly and through pgpool. > > What can be a problem? If I understand correctly, you only have one PostgreSQL server. And your client goes through pgpool to reach the PostgreSQL server. How can you expect that it would work faster that one PostgreSQL server alone? It'll work faster only if you have more than one PostgreSQL server. And more than one client. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general