Tell me more ×
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.
up vote 3 down vote favorite

I'm trying to configure a load balancing system. I've a python script, invoked through mod_wsgi on Apache, that generates a query and executes it on pgpool: request-> wsgi python -> pgpool -> postgresql. Pgpool is configured as load balancer using 4 servers with 24GB ram and 350GB ssh hd. Our db is about 150GB and a query takes about 2 seconds. These are the configurations:

Pgpool

  • num_init_children 500
  • max_pool 2
  • child_life_time 300 seconds

Apache (mpm_prefork)

  • StartServers 100
  • MinSpareServers 20
  • MaxSpareServers 45
  • ServerLimit 2000
  • MaxClients 100
  • MaxRequestsPerChild 1000

PostgreSQL

  • max_connections = 1000
  • shared_buffers = 6GB
  • work_mem = 4GB

It seems not working When I try to submit more than 150 concurrent queries, although pgpool log file doesn't have any errors I get this error from the python script:

OperationalError: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Any ideas?

share|improve this question
6  
The product (max_connections * work_mem) is way too high for postgres. Try max_connections = 100 and work_mem= 4M, for a start. – wildplasser May 15 '12 at 11:13
3  
Please note that work_mem is the amount of memory (for temps and hashes, etc) that postgres will use per session (even worse: per (sub)query). Personally I, don't like setting shared_buffers too high, but prefer setting effective_cachesize to the amount of physical memory minus the amount of core used by OS and processes. – wildplasser May 15 '12 at 11:28
3  
I put together a Wiki page about why you shouldn't use that many connections in PostgreSQL: wiki.postgresql.org/wiki/Number_Of_Database_Connections – kgrittn May 15 '12 at 12:20

migrated from stackoverflow.com May 15 '12 at 15:54

1 Answer

up vote 0 down vote

A couple things to consider. Your settings and options are going to be different depending on your read/write ratio. In general you need much lower connection limits for write-heavy workloads. A second point is that you need to take into account the version of Postgres. 8.4/9.0 will have lower connection limits than 9.1, and 9.1 will have lower connection limits than 9.2. So assuming you are working on 9.1, my recommendation is: drop work_mem down to 4MB, or if you need to increase this you can but start there. Drop connections down to 2 x cores (3x if it is virtually all read) and try increasing from there and benchmarking. With SSD I don't know how you can look at effective spindle numbers. You may also want to tell the planner that the random read cost is the same as a sequential read cost.

After you find the sweet spots there, I would recommend looking at optimizing your queries as far as you can (functional indexes are often overlooked for this sort of thing). This allows you to precalculate commonly used predicates.

Finally the fact that pgPool is a configured as a load balancer suggests that this is all read-only.

share|improve this answer
why "that pgPool is a configured as a load balancer suggests that this is all read-only"? – filiprem Feb 1 at 13:11
I don't know you can say everything is read only. PGPool can direct write-transactions to different sessions. – Chris Travers Feb 9 at 6:19

Your Answer

 
discard

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