Is db_query() closing connections?

Question

I recently built a website with a small-medium profile of user access (around 150,000 pageviews/month) and sometimes, in the middle of the day, I get the following error:

PDOException: SQLSTATE[08004] [1040] Too many connections in lock_may_be_available()

The Drupal application is hosted with the MySQL, same server, with 2 CPUs Intel Xeon Quad Core and 8 GB of RAM.

I'm new to the Drupal world, and someone recommended me to use db_query() to throw my queries to the database. I don't know if this is the best practice, or even if this is the root of the problem. Can anyone help me?

Answer 1

You need to make sure the number of MySQL connections (max_connections in my.cnf) is greater than the number of PHP processes (pm.max_children if you use php-fpm, or MaxClients if you're using Apache mod_php).

The default for Apache is often 300, and the MySQL default is only 100, so you can run out of connections pretty quickly if these values aren't tuned.

Answer 2

Actually, lock_may_be_available() is using db_query(); if that would not cause the error you are seeing, then you should not see it when lock_may_be_available() is called.

It is suggested to use db_query() when the query that is being executed doesn't need to be altered from other modules; in that case, it is preferable to use db_select().

If you look at the code of db_query(), and db_select(), you will notice that both call Database::getConnection to obtain the database connection. The cause of that error is surely not the use of a function instead of the other.

function db_query($query, array $args = array(), array $options = array()) {
  if (empty($options['target'])) {
    $options['target'] = 'default';
  }

  return Database::getConnection($options['target'])->query($query, $args, $options);
}

function db_select($table, $alias = NULL, array $options = array()) {
  if (empty($options['target'])) {
    $options['target'] = 'default';
  }
  return Database::getConnection($options['target'])->select($table, $alias, $options);
}