Too many connections using orm and mysql eloquence

Question

I am using SLIM Framework with Laravel Eloquent ORM for REST API. I recently ran into the problem of too many connections .

During one URI request, I need to make several Get and Set calls in mySql DB. This opens up connections for every database transaction that I do. I want to avoid this. The mysql connection pool currently has 200 threads.

It is expected that my API will have more than 1000 simultaneous calls and with the current environment, 40% of the calls will fail (verified using jMeter).

My idea is that for one API call, my application should use only one connection thread and increase the MySql connection pool to about 1000-1500. Is this a bad approach?

With Eloquent ORM, I have a connection to a DB that is managed by a Capsule. Should I make the first connection using the Singleton method, and for any subsequent call in the API request, the same thread should be used?

Here is my database connection manager:

  use Illuminate\Database\Capsule\Manager as Capsule; /** * Configure the database and boot Eloquent */ $capsule = new Capsule; $capsule->addConnection($databaseConfig['mysql']); // Set the event dispatcher used by Eloquent models... (optional) use Illuminate\Events\Dispatcher; use Illuminate\Container\Container; $dispatcher = new Dispatcher(new Container); $capsule->setEventDispatcher($dispatcher); $capsule->setAsGlobal(); $capsule->bootEloquent(); 

What is the best approach to solve this problem?

UPDATE

I'm trying to use a different approach to create a persistent connection. But still the persistent connection does not close after the call is completed with the job. Even a DB::Disconnect call does not help.

  <?php use Illuminate\Database\Capsule\Manager as Capsule; use Illuminate\Events\Dispatcher; use Illuminate\Container\Container; /** * Configure the database and boot Eloquent */ $app->hook('slim.before', function() use ($app) { try { // pr('', $app->settings['databaseConfig']['mysql'], 1); /* * Register Eloquent as singleton to slim container * since we will use the same instance across the request cycle */ $app->container->singleton('db', function() { return new Capsule; }); $app->db->addConnection($app->settings['databaseConfig']['mysql']); $dispatcher = new Dispatcher(new Container); $app->db->setEventDispatcher($dispatcher); if (isset($app->settings['databaseConfig']['profiler']) && $app->settings['databaseConfig']['profiler']) { $dispatcher->listen('illuminate.query', function($sql, $params, $time, $conn) { dd(array($sql, $params, $time, $conn)); }); } $app->db->setAsGlobal(); $app->db->bootEloquent(); } catch (PDOException $e) { /** Do some stuff to handle exception */ echoResponse(501, array('No DB Connections')); } }); 

Answer 1

You must use the same database connection for all queries. The easiest way to do this is to connect to the database in the DI container, as you can just pull it out every time you need it.

Using Slim 3, it looks something like this:

 $container = $app->getContainer(); $container['db'] = function ($container) { $capsule = new \Illuminate\Database\Capsule\Manager; $capsule->addConnection($container['settings']['db']); $capsule->setAsGlobal(); $capsule->bootEloquent(); return $capsule; }; 

Now you can use in a route that you can call:

 $app->get('/list', function ($request, $response) { $table = $this->get('db')->table('table_name'); $data = $table->get(); // do something with data $response->write(print_r($data, true)); return $response; }); 

Full details in the documentation here: http://www.slimframework.com/docs/cookbook/database-eloquent.html