Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

1)

num_init_children = 64
max_pool = 4

2)

num_init_children = 32
max_pool = 8

But nothing seems to calm down the overload.

share|improve this question
    
Why do you believe there to be a problem? It seems like the most likely explanation is that pgpool is receiving queries, passing them on to postgresql, and postgresql is busy handling them, which takes CPU time. You could use pg_stat_statements to figure out what those CPU intensive queries are, and see if they can be improved. –  jjanes Nov 5 '13 at 17:53
    
I need to have the cpu at control because the cpu usage has take my slave down yesterday. The reason that I put my postgresql and pgpool configuration it's because I'm not so sure of them, if they're overloading the cpu by itself. I've put a max connection of 300 for both postgresql machines, and pgpool is opening 256 connections on single one of them, but I'm not so sure if we could really handle 256 connections on a machine with 3.33GHz of cpu and 2gb of ram. –  Ezequiel Santos Pereira Nov 5 '13 at 18:53
    
Indeed we have some complex queries with some levels of sub-queries. I don't have the contrib for pg_stat_statements, I'm certainly checking this later as it needs some configuration and restart the cluster, but right now I was looking for a improvement on my configuration to handle better this queries without overloading cpu so badly, so I can get some time to check them and minimize their consumption. So cpu usage is purely query processing, nothing to do with connections? –  Ezequiel Santos Pereira Nov 5 '13 at 18:59
1  
high CPU usage rarely takes down a machine, it is almost always IO or memory that does that. Your log file should show you what happened. Your servers cannot productively serve 300 active connections at once because they are CPU bound but do not have 300 cores. On the other hand the mere existence of 256 connections should not cause much of a problem if they are idle. You may as well lower num_init_children and max_pool so that the product is around twice your number of CPU. It might help and probably won't hurt. It would help to see the first few lines of "top" processes. –  jjanes Nov 5 '13 at 21:01
1  
Do you really need this much connections? If 70% is idle, it means that 90 (or 100 with some buffer) would be enough. On the other hand, less than 7 MB of work_mem can cause all sorts of problems with bigger queries. As you are quite low on memory, this will be a problem even if you reduce the connection numbers... For 500 to 700 connections we usually have more than 100 GB of memory, just for comparison. –  dezso Nov 5 '13 at 21:21
add comment

migrated from stackoverflow.com Nov 5 '13 at 21:08

This question came from our site for professional and enthusiast programmers.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.