I think I've followed the configuration for pgpool correctly, but he's pushing my postgres machines to 100% cpu usage unnecessarily. I have one pgpool machine that works just fine, no overload at all, and two postgres machine, one being a master and the other a slave, both reaching 100% cpu usage on a pgpool balancing, but more than 70% of the pgpool connections are just idle. Both postgres machines oscillates on the cpu usage but constantly reaches 100%, the cpu usage drops dramatically when pgpool close some connections in his own maintenance configuration, but once he reopens then, we reach a high cpu overload.
Both, master and slave, postgres machines have the follow configuration:
max_connections = 300 superuser_reserved_connections = 3 shared_buffers = 480MB work_mem = 6656kB maintenance_work_mem = 120MB effective_cache_size = 1408MB
I've get the above configuration using pgtune:
$ pgtune -i /etc/postgresql/9.2/main/postgresql.conf \ -o postgresql.conf -T Web -c 300
Master CPU hardware and usage
Intel(R) Xeon(R) CPU X5680 @ 3.33GHz Tasks: 214 total, 9 running, 205 sleeping, 0 stopped, 0 zombie Cpu(s): 99.3%us, 0.3%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Mem: 2055808k total, 2038980k used, 16828k free, 3700k buffers Swap: 1340408k total, 144824k used, 1195584k free, 508272k cached
Slave CPU hardware and usage
Intel(R) Xeon(R) CPU X5680 @ 3.33GHz Tasks: 216 total, 4 running, 212 sleeping, 0 stopped, 0 zombie Cpu(s): 94.7%us, 3.6%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 1.7%si, 0.0%st Mem: 1537664k total, 1525140k used, 12524k free, 996k buffers Swap: 1340408k total, 516120k used, 824288k free, 381964k cached
My pgpool balance two postgres machines thorough a master/slave streaming replication, my pgpool pertinent configuration was setup as follow:
num_init_children = 128 max_pool = 2 child_life_time = 300 child_max_connections = 0 connection_life_time = 0 client_idle_limit = 0 connection_cache = on
I also have tried these other variations of 256 connections:
num_init_children = 64 max_pool = 4
num_init_children = 32 max_pool = 8
But nothing seems to calm down the overload.