MySQL connection gone

Question

I am using clearDB database for my windows azure based php application. I am getting intermittent error on mysql connections. Currently we have nearly 2000 online customers. And we have 30 connections from database.

How can I scale the website and overcome this situation?

I am taking care of closing connection most of the times. For now I have prevented server errors by this:

try {
   $this->_conn = $this->dbh = new PDO('mysql:host=' . DB_SERVER . ';dbname='. DB_NAME, DB_USER, DB_PASS);
   $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
   die("Couldn't connect to database. Please try again!");
}

So, if there is no connections left, then it'll show appropriate message. But, It's not feasible for customer facing websites. How can I solve this problem?

EDIT

How can I analyse this data:

Array
(
[0] => Array
    (
        [Variable_name] => Connections
        [Value] => 505369
    )

[1] => Array
    (
        [Variable_name] => Threads_cached
        [Value] => 54
    )

[2] => Array
    (
        [Variable_name] => Threads_connected
        [Value] => 65
    )

[3] => Array
    (
        [Variable_name] => Threads_created
        [Value] => 1038
    )

[4] => Array
    (
        [Variable_name] => Threads_running
        [Value] => 4
    )
)

I got above result after: SHOW STATUS WHERE variable_name LIKE 'Threads_%' OR variable_name = 'Connections'

I would suggest taking this over to ServerFault or similar. Sounds like you need to sort your DB settings / general infrastructure rather than there being anything to be done programatically with what's provided.. — Jonnix, Jun 04 '15 at 15:25
Typical performance troubleshooting for MySQL includes turning on the slow_query_log, running a tuning analyzer such as mysqltuner.pl, checking DB normalization, etc. What have you done so far? — Kevin_Kinsey, Jun 04 '15 at 15:26
@Kkinsey: I don't know any one of those tools that you mentioned. — RNK, Jun 04 '15 at 15:28
The slow_query_log is a feature of the MySQL server; enable it in "my.cnf" and then read the logfile to see which queries need improvement. See, for example: https://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html — Kevin_Kinsey, Jun 04 '15 at 17:41
If you're seeing persistent issues using ClearDB then you should consider moving to a self-managed MySQL instance running on an Azure VM. — Simon W, Jun 05 '15 at 04:27

score 0 · Answer 1 · answered Jun 05 '15 at 10:20

Per my understanding of the issue, it is not easy to design a scalable database. Consider the following suggestions:

Use connection pools. A connection pool will try to automatically reuse an existing connection whenever possible. So the 30 connections may be used by more than 30 concurrent users. Please refer to http://php.net/manual/en/mysqlnd-ms.pooling.php for more information.
Partition the database. That is, if one database is insufficient, then use multiple. This is a bit huge topic, you can begin from https://dev.mysql.com/doc/refman/5.7/en/partitioning-overview.html. Those guidelines for Azure SQL Databases (https://msdn.microsoft.com/en-us/library/azure/dn495641.aspx) can also help, while the database engine is different, most concepts are the same.
Use in memory storage. MySQL currently does not offer a complete set of in-memory database features. But the MEMORY Storage Engine could help. In-memory storage can usually improve performance a lot. But please note there’re some limitations. This is actually
one area where major database vendors (including open source) are continuously investing. And remember if the database server does not have sufficient memory, it will not help at all. Please check https://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html to see if helps.

MySQL connection gone

1 Answers1