Forums PostgreSQL.fr

melbichon

Membre

Load balacing avec PGPOOL II mais sans réplication

Bonjour,

J'essaie de mettre en place le load balancing de PGPOOL II sans la réplication (Ce ne sont que des bases en lecture seule).
J'ai essayé ces différentes combinaisons sans succès :
    - replication_mode = false + load_balancing = true + master_slave_mode = false
    - replication_mode = false + load_balancing = false+ master_slave_mode = true
    - replication_mode = false + load_balancing = true + master_slave_mode = true

Ma config :

listen_addresses = 'xxxx'
port = 9999
pcp_port = 9898
socket_dir = '/tmp'
pcp_socket_dir = '/tmp'
backend_socket_dir = '/tmp'
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 = '/tmp'
pid_file_name = 'pgpool.pid'
replication_mode = false
load_balance_mode = true
replication_stop_on_mismatch = false
failover_if_affected_tuples_mismatch = false
replicate_select = true
reset_query_list = 'ABORT; DISCARD ALL'
white_function_list = ''
black_function_list = 'nextval,setval'
print_timestamp = true
master_slave_mode = false
master_slave_sub_mode = 'slony'
delay_threshold = 0
log_standby_delay = 'none'
connection_cache = false
health_check_timeout = 20
health_check_period = 0
health_check_user = 'nobody'
failover_command = ''
failback_command = ''
fail_over_on_backend_error = true
insert_lock = true
ignore_leading_white_space = true
log_statement = true
log_per_node_statement = true
log_connections = true
log_hostname = false
parallel_mode = false
enable_query_cache = false
pgpool2_hostname = ''
backend_hostname1 = 'host1'
backend_port1 = 5432
backend_weight1 =1
backend_hostname0 = 'host2'
backend_port0 = 5432
backend_weight0 = 1
enable_pool_hba = false
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
lobj_lock_table = ''
ssl = false
debug_level = 3

Marc Cousin

Membre

Re : Load balacing avec PGPOOL II mais sans réplication

Pas de message d'erreur ? Pas d'information à nous donner ?

Parce que là, sans plus d'informations on ne peut pas deviner

---

Marc.

melbichon

Membre

Re : Load balacing avec PGPOOL II mais sans réplication

1) PGPOOL peut-il faire seulement du load balancing sans activer la réplication ?
2) Pas de messages d'erreurs. Juste le fait qu'il envoie toujours les requêtes vers le même backend
3) Seul truc bizarre, c'est qu'il indique bien la sélection du backend 1 mais envoie la requête vers le backend 0

Ci dessous les traces générées :

2010-11-09 09:29:28 DEBUG: pid 4696: I am 4696 accept fd 6
2010-11-09 09:29:28 LOG:   pid 4696: connection received: host=xxxx port=46993
2010-11-09 09:29:28 DEBUG: pid 4696: Protocol Major: 1234 Minor: 5679 database:  user:
2010-11-09 09:29:28 DEBUG: pid 4696: SSLRequest from client
2010-11-09 09:29:28 DEBUG: pid 4696: pool_ssl: SSL requested but SSL support is not available
2010-11-09 09:29:28 DEBUG: pid 4696: Protocol Major: 3 Minor: 0 database: cas_db user: cas
2010-11-09 09:29:28 DEBUG: pid 4696: new_connection: connecting 0 backend
2010-11-09 09:29:28 DEBUG: pid 4696: new_connection: connecting 1 backend
2010-11-09 09:29:28 DEBUG: pid 4696: pool_ssl: SSL requested but SSL support is not available
2010-11-09 09:29:28 DEBUG: pid 4696: pool_ssl: SSL requested but SSL support is not available
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length: slot: 0 length: 8
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length: slot: 1 length: 8
2010-11-09 09:29:29 DEBUG: pid 4696: pool_do_auth: auth kind:0
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 25
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 25
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: client_encoding value: UTF8
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: client_encoding value: UTF8
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 23
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 23
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: DateStyle value: ISO, MDY
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: DateStyle value: ISO, MDY
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 25
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 25
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: integer_datetimes value: on
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: integer_datetimes value: on
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 27
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 27
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: IntervalStyle value: postgres
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: IntervalStyle value: postgres
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 21
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 21
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: is_superuser value: off
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: is_superuser value: off
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 25
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 25
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: server_encoding value: UTF8
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: server_encoding value: UTF8
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 25
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 25
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: server_version value: 8.4.2
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: server_version value: 8.4.2
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 30
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 30
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: session_authorization value: cas
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: session_authorization value: cas
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 36
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 36
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: standard_conforming_strings value: off
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: standard_conforming_strings value: off
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 0 length: 26
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length2: master slot: 1 length: 26
2010-11-09 09:29:29 DEBUG: pid 4696: 0 th backend: name: TimeZone value: Europe/Paris
2010-11-09 09:29:29 DEBUG: pid 4696: 1 th backend: name: TimeZone value: Europe/Paris
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length: slot: 0 length: 12
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length: slot: 1 length: 12
2010-11-09 09:29:29 DEBUG: pid 4696: pool_do_auth: cp->info[i]:0x2b0759f86000 pid:4738
2010-11-09 09:29:29 DEBUG: pid 4696: pool_do_auth: cp->info[i]:0x2b0759f86088 pid:9903
2010-11-09 09:29:29 DEBUG: pid 4696: pool_send_auth_ok: send pid 9903 to frontend
2010-11-09 09:29:29 DEBUG: pid 4696: select_load_balancing_node: selected backend id is 1
2010-11-09 09:29:29 DEBUG: pid 4696: selected load balancing node: 1
2010-11-09 09:29:29 DEBUG: pid 4696: pool_unset_query_in_progress: done
2010-11-09 09:29:29 DEBUG: pid 4696: pool_unset_skip_reading_from_backends: done
2010-11-09 09:29:29 DEBUG: pid 4696: pool_unset_ignore_till_sync: done
2010-11-09 09:29:29 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
2010-11-09 09:29:29 DEBUG: pid 4696: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2
2010-11-09 09:29:29 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: Z
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length: slot: 0 length: 5
2010-11-09 09:29:29 DEBUG: pid 4696: pool_read_message_length: slot: 1 length: 5
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessFrontendResponse: kind from frontend Q(51)
2010-11-09 09:29:32 DEBUG: pid 4696: pool_unset_doing_extended_query_message: done
2010-11-09 09:29:32 LOG:   pid 4696: statement: select * from users;
2010-11-09 09:29:32 DEBUG: pid 4696: pool_set_query_in_progress: done
2010-11-09 09:29:32 LOG:   pid 4696: DB node id: 0 backend pid: 4738 statement: select * from users;
2010-11-09 09:29:32 DEBUG: pid 4696: wait_for_query_response: waiting for backend 0 completing the query
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend T NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: T
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: D
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: C
2010-11-09 09:29:32 DEBUG: pid 4696: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
2010-11-09 09:29:32 DEBUG: pid 4696: ProcessBackendResponse: kind from backend: Z
2010-11-09 09:29:32 DEBUG: pid 4696: pool_read_message_length: slot: 0 length: 5
2010-11-09 09:29:32 DEBUG: pid 4696: pool_unset_query_in_progress: done
2010-11-09 09:29:32 DEBUG: pid 4696: pool_unset_query_in_progress: done

gleu

Administrateur

Re : Load balacing avec PGPOOL II mais sans réplication

1) PGPOOL peut-il faire seulement du load balancing sans activer la réplication ?

Oui, il peut le faire sans activer sa propre réplication.

2) Pas de messages d'erreurs. Juste le fait qu'il envoie toujours les requêtes vers le même backend

Vos requêtes sont-elles toujours englobées dans des BEGIN/COMMIT explicites ?

---

Guillaume.

melbichon

Membre

Re : Load balacing avec PGPOOL II mais sans réplication

2) Non. Après le lancement de psql, je fais directement : select * from users;

melbichon

Membre

Re : Load balacing avec PGPOOL II mais sans réplication

Pas d'idée ?
Peut être pourriez-vous m'envoyer un exemple fonctionnant chez vous que j'essaierai de reproduire ici ?