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'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
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
comments disabled on deleted / locked posts

migration rejected from stackoverflow.com Oct 4 '13 at 4:21

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by billinkc, bluefeet, ypercube, Max Vernon, Paul White Oct 4 '13 at 4:21

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – billinkc, bluefeet, ypercube, Max Vernon, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

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
    
why "that pgPool is a configured as a load balancer suggests that this is all read-only"? –  filiprem Feb 1 '13 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 '13 at 6:19
add comment