Author Topic: MySQL server sizing recommendations  (Read 3749 times)

Offline ajbensonub

  • Newbie
  • *
  • Posts: 1
MySQL server sizing recommendations
« on: July 17, 2013, 04:00:21 PM »
Are there any recommendations for appropriately sizing or configuring the MySQL database server based on number of accounts? We just deployed roundcube and while we don't seem to have any performance issues reported by our users, we do occasionally get errors like the one below in the roundcube error log:


 [2013-Jul-17 13:32:05 -0400]: DB Error: SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'xxxxx' (4) in /path/to/roundcubemail-0.9.2/program/lib/Roundcube/rcube_db.php on line 154 (GET /webmail/?_task=mail&_action=preview&_uid=191705&_mbox=INBOX&_framed=1&_caps=pdf%3D0%2Cflash%3D0%2Ctif%3D0)
[2013-Jul-17 13:32:05 -0400]: DB Error: SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'xxxxx' (4) (GET /webmail/?_task=mail&_action=preview&_uid=191705&_mbox=INBOX&_framed=1&_caps=pdf%3D0%2Cflash%3D0%2Ctif%3D0)


If I look up the error code in the parenthesis:
Code: [Select]
$ perror 4
OS error code   4:  Interrupted system call

Further investigation leads me to believe at times we are seeing more connections than the system can handle. Right now we have about 3000 accounts but that number will grow to potentially up to 10,000 or so. Our database server is on dedicated hardware as is our mail spools.

Max_used_connects = 32 "the maximum number of connections that have been in use simultaneously since the server started." The server was started about 20 days ago. I see 4 aborted connections, but no errors in the error log from today.  The highest number of "current open connections", Threads_connected, I have seen while monitoring the server is about 10.

Code: [Select]
mysql> show status like '%onn%'; 
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_connects         | 4      |
| Connections              | 209723 |
| Max_used_connections     | 32     |
| Ssl_client_connects      | 0      |
| Ssl_connect_renegotiates | 0      |
| Ssl_finished_connects    | 0      |
| Threads_connected        | 5      |
+--------------------------+--------+
7 rows in set (0.01 sec)

The max_connections value = 151.  The max_user_connections value is 0 which allows unlimited "number of simultaneous connections permitted to any given MySQL user account."

Code: [Select]
mysql> show variables like '%onn%'; 
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
| connect_timeout          | 10                |
| init_connect             |                   |
| max_connect_errors       | 10                |
| max_connections          | 151               |
| max_user_connections     | 0                 |
+--------------------------+-------------------+
7 rows in set (0.00 sec)

Forgive me if there is documentation for this somewhere, I was only able to find performance tuning recommendations for the webserver and mailserver.