$10

Too many connections: how should models connect to 2nd database?

We are rebuilding an old site, originally built in PHP. The site has 2 parts:

1.) a custom PHP codebase

2.) WordPress

The public facing site is being rebuilt, but the database is left untouched. We have 2 databases, one for WordPress and one for the rest of the code.

On every non-WordPress page of the site, we will grab a few headlines from WordPress and show them in the sidebar. So I needed to create models that connected to the WordPress database, even though all of our other models connect to the main database.

One more problem: the WordPress install was a multisite install, so instead of just having a table called "posts", we have 15 tables for 15 blogs, and they have names like: 1_posts, 2_posts, 3_posts, etc.

So I did this, which worked, but it lead to disaster:

class Post1 < ActiveRecord::Base

  establish_connection "blogs_" + Rails.env

  set_table_name "1_posts"

  require 'wordpress_blogs'

  include WordpressBlogs

end

and:

class Post2 < ActiveRecord::Base

  establish_connection "blogs_" + Rails.env

  set_table_name "2_posts"

  require 'wordpress_blogs'

  include WordpressBlogs

end

and:

class Post3 < ActiveRecord::Base

  establish_connection "blogs_" + Rails.env

  set_table_name "3_posts"

  require 'wordpress_blogs'

  include WordpressBlogs

end

In application_controller.rb, I did this:

class ApplicationController < ActionController::Base

  protect_from_forgery

  before_filter :load_sidebar



  def load_sidebar

    @blogPosts = { 

      "Post2" => Post2.order("ID DESC").first,

      "Post3" => Post3.order("ID DESC").first,

      "Post4" => Post4.order("ID DESC").first,

      "Post5" => Post5.order("ID DESC").first,

      "Post6" => Post6.order("ID DESC").first,

      "Post7" => Post7.order("ID DESC").first,

      "Post8" => Post8.order("ID DESC").first,

      "Post9" => Post9.order("ID DESC").first,

      "Post10" => Post10.order("ID DESC").first,

      "Post11" => Post11.order("ID DESC").first,

      "Post12" => Post12.order("ID DESC").first,

      "Post13" => Post13.order("ID DESC").first,

      "Post14" => Post14.order("ID DESC").first

    }

  end

end

And so, with all this in place, I was able to get the newest article from every blog, and I was able to show it in the sidebar of every page on the site. Everything was perfect.

Perfection lasted for a little over a day. Then our development database server locked up and would not serve data to anyone. The sysadmin had to ssh to the server and reboot the entire machine.

What the hell happened? The sysadmin did some research. He allowed MySql 100 connections on the dev server. Since the dev server gets almost no traffic (just us 6 developers) 100 connections is usually excessive. But in fact, all 100 connections were getting used up. 64 connections were getting grabbed by my WordPress code.

The problem is this line:

  establish_connection "blogs_" + Rails.env

I do this for every WordPress model, and apparently each one is grabbing one of the 100 connections. So with very little traffic, this eats up all the resources on the database server.

So what is a better way to do this?

As part of an attempt to find another way to do this, I tried this module, but never got it to connect correctly:

module WordpressBlogs

  def multisite_blog (blog_id)

    @multisite = Blog.where("blog_id = #{blog_id}").first 

  end

end

And the blog model, when I tried to use it, was:

class Blog < ActiveRecord::Base

  establish_connection "blogs_" + Rails.env

end