[Pgpool-general] slow pgpool-II-3.1
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? As you can see in my pgpool.conf I have set num_init_children to 32, and I have max_pool to 3. max_connections in postgresql.conf is set to 100. Also, should I set num_init_children and max_pool comparing to one node, or should I multiply value by number of nodes? Here is my pgpool.conf: listen_addresses = '*' port = socket_dir = '/tmp' pcp_port = 9898 pcp_socket_dir = '/tmp' backend_hostname0 = 'node1.hostname' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/pgdata/data901.C' backend_flag0 = 'ALLOW_TO_FAILOVER' #backend_hostname1 = 'node2.hostname' #backend_port1 = 5432 #backend_weight1 = 1 #backend_data_directory1 = '/pgdata/data90.C' #backend_flag1 = 'ALLOW_TO_FAILOVER' enable_pool_hba = on authentication_timeout = 60 # - SSL Connections - ssl = on ssl_key = '/usr/local/etc/server.key' ssl_cert = '/usr/local/etc/server.crt' num_init_children = 32 max_pool = 3 child_life_time = 300 child_max_connections = 0 connection_life_time = 0 client_idle_limit = 0 log_destination = 'syslog' print_timestamp = on log_connections = on log_hostname = off log_statement = off log_per_node_statement = off log_standby_delay = 'if_over_threshold' syslog_facility = 'LOCAL0' syslog_ident = 'pgpool' replication_stop_on_mismatch = off debug_level = 1 pid_file_name = '/var/run/pgpool/pgpool.pid' logdir = '/tmp' connection_cache = on reset_query_list = 'ABORT; DISCARD ALL' replication_mode = off replicate_select = off insert_lock = on lobj_lock_table = '' load_balance_mode = on ignore_leading_white_space = on white_function_list = '' black_function_list = 'currval,lastval,nextval,find_record_position,setval,foo,array2_order_and_flatten,distinct_array_append' master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_period = 0 sr_check_user = 'postgres' sr_check_password = '' delay_threshold = 0 follow_master_command = '' parallel_mode = off enable_query_cache = off pgpool2_hostname = '' system_db_hostname = 'node1.hostname' system_db_port = 54322 system_db_dbname = 'pgpool' system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool' system_db_password = 'password' health_check_period = 10 health_check_timeout = 20 health_check_user = 'postgres' health_check_password = 'password' failover_command = '/usr/local/pgpool-II/etc/failover.sh %d %h %p %D %m %M %H %P' failback_command = '/usr/local/pgpool-II/etc/failback.sh %d %h %p %D %m %M %H %P' fail_over_on_backend_error = on recovery_user = 'postgres' recovery_password = 'password' recovery_1st_stage_command = 'basebackup.sh' recovery_2nd_stage_command = '' recovery_timeout = 30 client_idle_limit_in_recovery = 0 relcache_expire = 0 -- 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
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 guilla...@lelarge.infowrote: 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
2011/10/6 Guillaume Lelarge guilla...@lelarge.info: On Thu, 2011-10-06 at 14:56 +0200, Armin Nesiren wrote: On Thu, Oct 6, 2011 at 2:48 PM, Guillaume Lelarge guilla...@lelarge.infowrote: 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
2011/10/6 Guillaume Lelarge guilla...@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. 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
[Pgpool-general] How to configure pgpool for pool conexions..
Hi everyone i´ve been trying to understand and configure pgpool for a pool of connections (not sure if that is the term), i already follow a manual and confiigure it like this: listen_addresses = '*' port = pcp_port = 9898 socket_dir = '/var/run/postgresql' pcp_socket_dir = '/var/run/postgresql' backend_socket_dir = '/var/run/postgresql' pcp_timeout = 10 num_init_children = 32 max_pool = 4 child_life_time = 300 connection_life_time = 0 child_max_connections = 0 client_idle_limit = 0 authentication_timeout = 60 logdir = '/var/run/postgresql' replication_mode = false load_balance_mode = false replication_stop_on_mismatch = false replicate_select = false reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' print_timestamp = true master_slave_mode = false connection_cache = true health_check_timeout = 20 health_check_period = 60 health_check_user = 'pgpool2' failover_command = '' failback_command = '' insert_lock = false ignore_leading_white_space = true log_statement = false log_connections = false log_hostname = false parallel_mode = false enable_query_cache = false pgpool2_hostname = 'pgsql1' system_db_hostname = 'localhost' system_db_port = 5432 system_db_dbname = 'pgpool' system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool' system_db_password = '' backend_hostname0 = '192.168.0.3' backend_port0 = 5432 backend_weight0 = 1 backend_hostname1 = '192.168.0.4' backend_port1 = 5432 backend_weight1 = 1 enable_pool_hba = false recovery_user = 'pgpool2' recovery_password = '' recovery_1st_stage_command = '' recovery_2nd_stage_command = '' recovery_timeout = 90 After this i startes pgpool and everything seems to be working, how do i actually test it? Now i have a couple of questions. With this i understand that i am creating 4 pools for 32 connections each, so it would be 128 connections? The purpose of this is, first allow pgpool to handle connections instead of postygres so the end user do not receive an error, adn second use the same connection to dispach several request that are alike... Am i correct? Thanks in advance! ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
On Thu, 2011-10-06 at 10:59 -0500, Matias Israel Malpica Escobar wrote: Hi everyone i´ve been trying to understand and configure pgpool for a pool of connections (not sure if that is the term), i already follow a manual and confiigure it like this: listen_addresses = '*' port = pcp_port = 9898 socket_dir = '/var/run/postgresql' pcp_socket_dir = '/var/run/postgresql' backend_socket_dir = '/var/run/postgresql' pcp_timeout = 10 num_init_children = 32 max_pool = 4 child_life_time = 300 connection_life_time = 0 child_max_connections = 0 client_idle_limit = 0 authentication_timeout = 60 logdir = '/var/run/postgresql' replication_mode = false load_balance_mode = false replication_stop_on_mismatch = false replicate_select = false reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' print_timestamp = true master_slave_mode = false connection_cache = true health_check_timeout = 20 health_check_period = 60 health_check_user = 'pgpool2' failover_command = '' failback_command = '' insert_lock = false ignore_leading_white_space = true log_statement = false log_connections = false log_hostname = false parallel_mode = false enable_query_cache = false pgpool2_hostname = 'pgsql1' system_db_hostname = 'localhost' system_db_port = 5432 system_db_dbname = 'pgpool' system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool' system_db_password = '' backend_hostname0 = '192.168.0.3' backend_port0 = 5432 backend_weight0 = 1 backend_hostname1 = '192.168.0.4' backend_port1 = 5432 backend_weight1 = 1 enable_pool_hba = false recovery_user = 'pgpool2' recovery_password = '' recovery_1st_stage_command = '' recovery_2nd_stage_command = '' recovery_timeout = 90 After this i startes pgpool and everything seems to be working, how do i actually test it? Now i have a couple of questions. With this i understand that i am creating 4 pools for 32 connections each, so it would be 128 connections? Yes, but only 32 active at one time. The purpose of this is, first allow pgpool to handle connections instead of postygres so the end user do not receive an error, adn second use the same connection to dispach several request that are alike... Obviously, if your user tries to connect to a database that doesn't exist, he'll get an error message. If he tries to execute a badly written query, he'll also get an error message. pgpool will be able to reuse a connection if the user wants to connect to the same database with the same user. -- 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 guilla...@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. 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] How to configure pgpool for pool conexions..
On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- 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 guilla...@lelarge.info: On Thu, 2011-10-06 at 17:27 +0200, Olivier NOEL wrote: 2011/10/6 Guillaume Lelarge guilla...@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 ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# The database is actually on the same server, and i have checked the iptables and the port is open... PG_HBA is also reflecting that i can permit conections to UNIX conection... What i am doing wrng De: Guillaume Lelarge [guilla...@lelarge.info] Enviado el: jueves, 06 de octubre de 2011 11:25 a.m. Para: Matias Israel Malpica Escobar CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- 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] How to configure pgpool for pool conexions..
What is opening the port pgpool or the database? Sometimes I found out that postgres opens the port and have to stop pgpool and postgres and restart postgrsql and pgpool again. Jose Autonomy Ops verificare tua hinc -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Matias Israel Malpica Escobar Sent: Thursday, October 06, 2011 1:58 PM To: Guillaume Lelarge Cc: pgpool-general Subject: Re: [Pgpool-general] How to configure pgpool for pool conexions.. Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# The database is actually on the same server, and i have checked the iptables and the port is open... PG_HBA is also reflecting that i can permit conections to UNIX conection... What i am doing wrng De: Guillaume Lelarge [guilla...@lelarge.info] Enviado el: jueves, 06 de octubre de 2011 11:25 a.m. Para: Matias Israel Malpica Escobar CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- 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] How to configure pgpool for pool conexions..
If your database opens the port then pgpool finds it in use when it starts. Jose Autonomy Ops verificare tua hinc -Original Message- From: Matias Israel Malpica Escobar [mailto:matias.malp...@thyssenkrupp.com] Sent: Thursday, October 06, 2011 2:39 PM To: Jose Mendoza Cc: pgpool-general@pgfoundry.org Subject: RE: [Pgpool-general] How to configure pgpool for pool conexions.. I am trying to open the database using the port (pgpool port), let me know if that answers your question.. I am restarting services anyway... De: Jose Mendoza [jose.mend...@autonomy.com] Enviado el: jueves, 06 de octubre de 2011 04:39 p.m. Para: Matias Israel Malpica Escobar; Guillaume Lelarge CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. What is opening the port pgpool or the database? Sometimes I found out that postgres opens the port and have to stop pgpool and postgres and restart postgrsql and pgpool again. Jose Autonomy Ops verificare tua hinc -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Matias Israel Malpica Escobar Sent: Thursday, October 06, 2011 1:58 PM To: Guillaume Lelarge Cc: pgpool-general Subject: Re: [Pgpool-general] How to configure pgpool for pool conexions.. Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# The database is actually on the same server, and i have checked the iptables and the port is open... PG_HBA is also reflecting that i can permit conections to UNIX conection... What i am doing wrng De: Guillaume Lelarge [guilla...@lelarge.info] Enviado el: jueves, 06 de octubre de 2011 11:25 a.m. Para: Matias Israel Malpica Escobar CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- 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] How to configure pgpool for pool conexions..
Thanks for the quick reply, mmm but i never configure the databse to open the port , would it help if i copy my pgpool.config file? De: Jose Mendoza [jose.mend...@autonomy.com] Enviado el: jueves, 06 de octubre de 2011 04:47 p.m. Para: Matias Israel Malpica Escobar CC: pgpool-general@pgfoundry.org Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. If your database opens the port then pgpool finds it in use when it starts. Jose Autonomy Ops verificare tua hinc -Original Message- From: Matias Israel Malpica Escobar [mailto:matias.malp...@thyssenkrupp.com] Sent: Thursday, October 06, 2011 2:39 PM To: Jose Mendoza Cc: pgpool-general@pgfoundry.org Subject: RE: [Pgpool-general] How to configure pgpool for pool conexions.. I am trying to open the database using the port (pgpool port), let me know if that answers your question.. I am restarting services anyway... De: Jose Mendoza [jose.mend...@autonomy.com] Enviado el: jueves, 06 de octubre de 2011 04:39 p.m. Para: Matias Israel Malpica Escobar; Guillaume Lelarge CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. What is opening the port pgpool or the database? Sometimes I found out that postgres opens the port and have to stop pgpool and postgres and restart postgrsql and pgpool again. Jose Autonomy Ops verificare tua hinc -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Matias Israel Malpica Escobar Sent: Thursday, October 06, 2011 1:58 PM To: Guillaume Lelarge Cc: pgpool-general Subject: Re: [Pgpool-general] How to configure pgpool for pool conexions.. Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# The database is actually on the same server, and i have checked the iptables and the port is open... PG_HBA is also reflecting that i can permit conections to UNIX conection... What i am doing wrng De: Guillaume Lelarge [guilla...@lelarge.info] Enviado el: jueves, 06 de octubre de 2011 11:25 a.m. Para: Matias Israel Malpica Escobar CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- 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] How to configure pgpool for pool conexions..
On October 6, 2011 02:44:34 PM Matias Israel Malpica Escobar wrote: Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# What is socket_dir set to in pgpool.conf? If it isn't /tmp, then psql will need a hint how to find the unix domain socket to talk to. ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
-Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- boun...@pgfoundry.org] On Behalf Of Alan Hodgson Sent: Thursday, October 06, 2011 5:52 PM To: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] How to configure pgpool for pool conexions.. On October 6, 2011 02:44:34 PM Matias Israel Malpica Escobar wrote: Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# What is socket_dir set to in pgpool.conf? If it isn't /tmp, then psql will need a hint how to find the unix domain socket to talk to. First, check that you can connect to the database by doing: psql -U postgres -h localhost -p 5432 (assuming proper postgresql.conf entries, like - listen_addresses = '*') If it succeeds, then, in pgpool.conf, check that backend_hostname0 is pointing to where the database is. If it's localhost, the following should most likely be in your pgpool configuration: backend_hostname0 = 'localhost' backend_port0 = 5432 backend_weight0 = 0 backend_data_directory0 = '/var/lib/pgsql/9.0/data' Restart pgpool and try again connecting to the database through pgpool by doing: psql -U postgres -p If it doesn't work. Stop pgpool, and run it with debug output by doing the following as root: pgpool -n -d It should tell you that it cannot connect to backend0 for a reason. Then come back and show us your findings. -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
