title: Why is my PostgreSQL server cpu constrained?
body: <p>My database is very cpu constrained, and I can't find the root cause of the issue. I currently have two applications servers each wit a Rails api connecting to PostgreSQL via the ruby-pg gem. Both application server also have sidekiq running background jobs, and I have a handful of support servers processing new posts from a national feed via sidekiq. If I were running out of memory, the solution would seemingly be straight forward. Any general ideas why I am CPU constrained?</p>
  
  <p>Database Specs:</p>
  
  <ul>
  <li>Rackspace 8GB Performance Tier cloud VM (8GB RAM, 8x Core CPU, SSD)</li>
  <li>Debian 7 Wheezy Linux OS</li>
  <li>PostgreSQL 9.1 with PostGIS extension</li>
  </ul>
  
  <p>Possible Problems:</p>
  
  <ul>
  <li>PostgreSQL 9.1 is bad at indexes</li>
  </ul>
  
  <p>The database has nearly 10GB of indexes. I am going to upgrade my database to PostgreSQL version >= 9.2. In version 9.2, index only scans were introduced.</p>
  
  <ul>
  <li>Too many connections</li>
  </ul>
  
  <p>In the postgresql.conf, I have set max connection equal to '500'. Usually throughout the day, only 175 connections are utilized, but during peak times, sidekiq tasks will increase the current connections to 350. How many connections are recommended with an 8GB server instance?</p>
  
  <ul>
  <li>Idol Connections</li>
  </ul>
  
  <p>When I take a look at pg_stat_activity in the psql console, I see sidekiq is leaving a lot of IDLE connections. Could these connections result in CPU inflation? Does the fix exist in the api or in sidekiq?</p>
  
  <ul>
  <li>Need a more powerful server</li>
  </ul>
  
  <p>Maybe there is not a bug. I might need to simply increase the server instance. Again this would make more sense if I was memory bound. However, both app servers and 3 of the support sidekiq servers are 4gb performance tier instances. Essentially, servers that interact with the database have combined more than double the resources of the database. Should this even matter?</p>
  
  <p>Additional questions:</p>
  
  <ul>
  <li>What tools/techniques should I employ to troubleshoot the issue?</li>
  <li>Any basic settings in the postgresql.conf related to cpu usage? </li>
  <li>Are there any known issues related to rails, sidekiq, or the pg gem that could be a contributing factor? (I havent seen any open issues.)</li>
  <li>Are there any general postgreSQL guideline for CPU usage?</li>
  <li>Any other ideas thoughts that might help my search?</li>
  </ul>