MySQL database needs a flush tables every now and again. Can I script something to resolve this?

Question

I'm having a problem that I hope someone can help me out with.

Currently, every now and again we receive an error when our scripts (Java and PHP) try to connect to the localhost mysql database.

Host 'myhost' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'.

This issue appears to mainly occur in the early hours of the morning. After alot of searching to figure out why this may be occuring I have finally come to the conclusion that it may be due to the fact our hosting company runs their backup processes around this time. My theory is that during this backup process (this is also our busiest period) we end up using up all our connections and so this error occurs.

I have talked to our hosts about changing the times these backups occur but they have stated that this is not possible and that is simply the times the backups start to ensure they are finished in time for the day (Even though we have informed them our critical period is at the precise times the backups occur).

The things I have connecting to the server are:

1. PHP website
2. PHP files run using chron jobs
3. A couple of java applications to run as socket listeners that listen for incoming port connections and uses the mysql database for checking user credentials and checking outstanding messages.

We typically have anywhere from 300 - 600 socket connections open at any one time and the average activity on these are about 1-3 request per second.

I have also installed monit and munin with some mysql plugins on the server in the hope they may help auto resolve this issue however these do not see to resolve the issue.

My questions are:

1. Is there something I can do to auto poll the mysql database so if this occurs I can auto flush the database to clear
2. Is this potentially even related to the server backup. It seems a coincidence it happens 95% of the time during the period the backups occur.
3. Any other ideas that may help. Links to other websites, or questions I could put to our host to help out.

We are currently running on a PHP Version 5.2.6-1+lenny9 server with Apache.

If any more information is required to help, please let me know. Thanks.

UPDATE: I am operating on a shared virtual host and am pretty sure I close my website connections as I have this code in my database class

  function __destruct() {
    @mysql_close($this->link);
  }

I'm pretty sure I'm not using persistant connections via my PHP script as I connect to the db the @mysql_connect command.

UPDATE: So I changed the max_connections limit from 100 - 200 and I changed the mysql.persistant variable from On to Off in php.ini. Now for two nights running the server has gone done and mainly the connection to the mySql database. I have one 1GB of RAM on the server but it never seems to get close to that. Also looking at my munin logs the connections never seem to hit the 200 mark and yet I get errors in my log files something like

1. SQLException: Too many connections
2. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
3. SQLException: null, message from server: "Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug.
4. SQLState :: SQLException: HY000, VendorError :: SQLException: 1135

Answer 1

It's been suggestion I post an answer to this question although I never really got it sorted.

In the end I ended up implementing a Java connection pooling class which enabled me to share connections whilst maintaining a upper limit on the number of max connections I wanted. It was also suggested I increase the RAM and increase the number of max connections. I did both these things although they were just bandaids to the problem. We also ended up moving hosting providers as the ones we were with were not very co-ooperative.

After these minor implementations I haven't noticed this issue occur for at least 8 months which is good enough for me.

Other suggestions over time have to also implement a Thread pooling facility, however current demand does not require this need.

Answer 2

We've had a similar problem with out large ecommerce installation using MySQL as a backend. I'd suggest you alter the "max_connections" setting of the MySQL instance, then (if necessary) alter the number of file descriptors using "ulimit" before starting MySQL (we use "ulimit -n 32768" in /etc/init.d/mysql).