1

Recently I have started getting mySQL "too many connection" errors at times of high traffic. My rails app runs on a mongrel cluster with 2 instances on a shared host. Some recent changes that might be driving it:

  • Traffic to my site has increased. I am now averaging about 4K pages a day.
  • Database size has increased. My largest table has ~ 100K rows. Some associations could return several hundred instances in the worst case, though most are far less.
  • I have added some features that increased the number and size of database calls in some actions.

I have done a code review to reduce database calls, optimize SQL queries, add missing indexes, and use :include for eager loading. However, many of my methods still make 5-10 separate SQL calls. Most of my actions have a response time of around 100ms, but one of my most common actions averages 300-400ms, and some actions randomly peak at over 1000ms.

The logs are of little help, as the errors seem to occur randomly, or at least the pattern does not appear related to the actions being called or data being accessed.

Could I alleviate the error by adding additional mongrel instances? Or are the mySQL connections limited by the server, and thus unrelated to the number of processes I divide my traffic across?

Is this most likely a problem with my coding, or should I be pressing my host for more capacity/less load on the shared server?

Ed Haywood
  • 335
  • 2
  • 9

4 Answers4

2

ActiveRecord has pooled database connections since Rails 2.2, and it's likely that that's what's causing your excess connections here. Try turning down the value of pool in your database.yml for that environment (it defaults to 5).

Docs can be found here.

nickgrim
  • 5,387
  • 1
  • 22
  • 28
  • This is beyond my level of understanding. Wouldn't reducing the pool make the bottleneck worse? – Ed Haywood Mar 16 '11 at 00:24
  • Reducing the pool would remove the problem of making "too many connections" to the MySQL server; you can see how many you're using with `SHOW PROCESSLIST` – nickgrim Mar 16 '11 at 09:34
  • SHOW PROCESSLIST showed 2 ids, both idle, plus the processlist query. Since I am running 2 instances of mongrel, that would make sense. – Ed Haywood Mar 16 '11 at 23:11
1

Are you caching anything? It's an important part of alleviating application and database load. The Rails Guides have a section on caching.

Andrew Marshall
  • 95,083
  • 20
  • 220
  • 214
  • I am not caching now, but I have looked hard at doing so. Unfortunately most of the content changes for each user and date. There are a few common fragments I can cache, but the performance gains will be incremental at best. I definitely plan to do that though. Every little bit helps. – Ed Haywood Mar 16 '11 at 00:17
  • There's nothing wrong with caching data that will be specific to a user and displayed to them frequently, and expiring it using sweepers so everything is still up-to-date. Just be sure to key those caches based on the user id as well so a user won't see another's data. – Andrew Marshall Mar 16 '11 at 00:24
  • I will do that where useful. The problem is that a relatively small portion of the data is displayed frequently to the user. Most data changes every time the user visits. So the payoff will be minimal. – Ed Haywood Mar 16 '11 at 22:38
  • 1
    selective fragment caching of some common reusable items such as drop down lists, along with optimizing some of my more costly SQL queries, seems to have alleviated the problem. – Ed Haywood Mar 22 '11 at 23:16
1

Something is wrong. A Mongrel instance processes 1 request at a time so if you have 2 Mongrel instances then you should not be seeing more than 2 active MySQL connections (from the mongrels at least)

You could log or graph the output of SHOW STATUS LIKE 'Threads_connected' over time.

PS: this is not very many Mongrels. if you want to be able to service more than 2 simultaneous requests then you'll want more. ...if memory is tight, you can switch to Phusion Passenger and REE.

outcassed
  • 5,223
  • 2
  • 27
  • 24
  • The mySQL connection limit is like 150. If I can only consume 1 connection per Mongrel instance, would the logical conclusion be that the hosting provider has too many applications sharing a single database server? – Ed Haywood Mar 16 '11 at 00:21
  • 1
    Probably - unless you are doing something unusual with ActiveRecord connections. You can check by logging in to the database and running "show processlist" - you should be able to see a listing of your connections (even if your permissions are limited). PS - I'm pretty sure that the connection pooling doesn't come into play since you are using Mongrel and not running in a multi-threaded environment. – outcassed Mar 16 '11 at 04:05
  • I'm opening a support ticket with my host to see if it is on their end. – Ed Haywood Mar 16 '11 at 23:12
  • My hosting provider said "maybe your site is too popular for a shared server. Would you like to buy a VPS plan." LOL – Ed Haywood Mar 17 '11 at 03:19
0

This error is caused by mysql, but not rails config.

  1. check variable: max_connections:
SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
  1. increase it : SET GLOBAL max_connections = 1024;

refer to: https://www.thegeekdiary.com/mysql-error-too-many-connections-and-how-to-resolve-it/

Siwei
  • 19,858
  • 7
  • 75
  • 95