MySQL connection limit and PHP MySQL connections

Question

Hi Everyone,

I am having a frustrating issue. My web service has an email to php module. Briefly, once my user sends an email to his dedicated email address, my mail server captures the email, pipes it to a PHP script running on my server. This PHP script opens a MySQL connection and saves the email content to the database.

So far so good. The problem starts to occur once my MySQL connection limit set in /etc/my.cnf is exceeded. It's set to 500. If 500 or more users sends emails at the same time, my MySQL gets unable to handle new connections.

What do you suggest? Should I use a persistent MySQL connection or any other method like writing the received email content to a txt file and then saving them into the db with a cron job?

Thanks for your suggestions!

Answer 1

Why not increase the connection limit to the number of concurrent users you are expecting?

OR

Have a slight delay or retry period where it attempts to connect again if it fails.

Answer 2

Another suggestion is to run PHP script like a daemon, which connects to DB only once and waits for input to process in endless loop.

Answer 3

I have a similar feature on a site I run. Instead of piping the email to a script, where you have no control over how many emails will be delivered at a given time, set up a a cron. This cron can run as often as you like. It can connect to the mail server, read as much mail as it chooses at once, and maintain a single connection to MySQL.

This will also greatly help in the event you get mail-bombed.

Answer 4

If 500 or more users sends emails at the same time

hmmm. It's very unusual for two events to occur "at the same time" on a computer system - but frequent for processes to overlap. I expect you will get a lot of mileage out of optimizing the email handler. But you've provided very little details of how that works. Not do you say what OS this is running on.

I'd recommend you check that there aren't signs of overuse on the CPU or memory (NB you want around 80% of your memory free). If the handler starts a PHP instance for each message then that's a major overhead - but this does not contribute to the time that the mysql connection is open.

If you can avoid it, don't use Blobs/Clobs - dump the data to diskfile and reference it from the database.

Apply all the usual performance tuning steps (optimizing physical storage layer - e.g. RAID, database tuning).

Collate all the DB interactions run by the script and run them in one block, bracketed with your db connect and close statements.

If you can't handle the volume synchronously, then... Rather than reinventing the wheel, DO NOT implement your own queueing system for processing of messages - route the messages directly to a maildir or mailbox and run a daemon to poll the contents and process it. The off-the shelf MDAs will be much more efficient than using PHP.

C.