Major MYSQL Issues

The file database.mysql.inc in your includes folder is where drupal first opens the connection to the database. Look around line 55 or so for @mysql_connect.

// - TRUE makes mysql_connect() always open a new link, even if
  //   mysql_connect() was called before with the same parameters.
  //   This is important if you are using two databases on the same
  //   server.
  // - 2 means CLIENT_FOUND_ROWS: return the number of found
  //   (matched) rows, not the number of affected rows.

$connection = @mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, 2);

TRUE makes mysql_connect() always open a new link--Maybe this might explain the problem? Generally indiscrimiate, multiple, unlimited database connections is a Bad Idea (tm).

I've seen users posting a similar issue before (with the multiple connections causing hosting problems). Hopefully someone with more understanding of the drupal core can clarify whether this might be the problem, and how to fix it. Myself, I'd be inclined to just kill the TRUE argument and replace it with FALSE (see http://php.net/mysql_connect). Of course, I have no idea what that might break! But if you only have one database it might be worth trying.

Edit: I'm sure I needn't tell you to back everything up and maybe set the site to offline before messing with important includes. :)

I have VPS host server setup and experienced similar problems technically related to "open or runaway processes", as opposed to "open connections" as you described it. I should add that my issues are/were related to a pre-launch site which did not really any significant traffic. Yet, the VPS, mysql was crashing every hour under essentially the minimal strain of minor site configuration such as, adding content. At the submission of content (banner), I would get a white screen. At this point, the VPS mysql server would be down and I'd have to request a restart. With each restart, the site returned.

Yes, no real significant site traffic, but I discovered that many contributed and some core modules are very often making calls on the database (queries), which in time over run the ability of the mysql server to handle the load (i.e. --CPU & RAM usage). For instance, the locale module, a core module, seems to run a large number of processes which contribute to a high server load. In my case, I found that the views module, a contributed module, seemed to also run out at a large server load.

At the point of high server load, the mysql server shuts down. These modules are usually very neat and functional, but can be extremely taxing on a site, particularly if it is not a dedicated server setup.

At the end of the day, like a ship which is sinking, you will have to make a decision as to what module goes overboard. You can get a go picture of what pages/modules are drawing a large server load by adding the Drupal admin development tool : a) the devel module.

The devel module(u will have to install from Drupal module library), has several tools which allow you to gain a good handle as to how your site is functioning with its processes behind the scene. For instance, the "modules settings" selection allows you to select to enable logs with will: a) display queries (at the bottom of the page once enabled)which are running and how long they are taking to process --here you will be able to determine exactly what modules are charging your mysql database with a high level of resource (CPU) usage; b) display page timer so that you can determine how long, in milliseconds, it is taking a page to process; c) a log with displays memory which is being utilized.

Further, the devel module has a "phpinfo" selection. This will allow you to view & review your host providers VPS setup in terms of various mysql;php;memory; cache; eccelarator components. These settings can effect the way that sites hosted on the server function. While these settings are usually set correctly in a way which maximizes and facilitates the site, sometimes they are not setup properly.

Below are some excellent, instructional links related to tips how to Optimize a Drupal install, as well as, a few tips as to how to Optimize various server settings so as your Drupal site will operate as efficiently as possible. I strongly recommend that you review the information at the below links, first:

1) http://drupal.org/node/32091

2) http://www.webhostgear.com/49_print.html

The core Drupal install is actually pretty smooth in terms of its operations. However, when we begin to add the various contributed modules the demands on the database resources can be challenged.

Hope it helps a bit.