Mostafa Kasem Mostafa Kasem - 19 days ago 10
MySQL Question

PHP Gearman too much mysql connections

I'm using Gearman in a custom Joomla application and using Gearman UI to track active workers and jobs number.

I'm facing an issue with MYSQL load and number of connections, I'm unable to track the issue, But I've few questions that might help me.

1- Does Gearman Workers launch a new database connection for each job or do they share the same connection?

2- If Gearman launches a new connection everytime a job runs how can I change that to make all jobs share same connection?

3- How can I balance the load between more than one server?

4- Is there is something like "Pay-as-you-go" package for MYSQL hosting? if yes, Please mention them.

Thanks a lot!

Answer

This is often an overlooked issue when using any kind of a job queue with workers. 100 workers will open a separate database connection each (they are separate PHP processes). If MySQL is configured allow 50 connections, workers will start failing. To answer your questions:

1) Each worker runs inside one PHP process each, and that process will open 1 database connection. Workers do not share database connections.

2) If only one worker is processing jobs, then only one database connection will be opened. If you have 50 workers running, expect 50 database connections. Since these are not web requests, persistent connections will not work, sharing will not work.

3) You can balance the load by adding READ slaves, and using a MySQL proxy to distribute the load.

4) I've never seen a pay-as-you-go MySQL hosting solution. Ask your provider to increase your number of connections. If they won't, it might be time to run your own server.

Also, the gearman server process itself will only use one database connection to maintain the queue (if you have enabled mysql storage).

Strategies you can use to try and make your worker code play nicely with the database:

  1. After each job, terminate the worker and start it up again. Don't open a new database connection until a new job is received. Use supervisor to keep your workers running all the time.

  2. Close database connections after every query. if you see a lot of connections open in a 'sleep' state, this will help clean them up and keep database connections low. Try $pdo = null; after each query (if you use PDO).

  3. Cache frequently used queries where the result doesn't change, to keep database connections low.

  4. Ensure your tables are properly indexed so queries run as fast as possible.

  5. Ensure database exceptions are caught in a try/catch block. Add retry logic (while loop), where the worker will fail gracefully after say, 10 attempts. Make sure the job is put back on the queue after a failure.