too many connections using eloquent orm and mysql

Question

I'm using SLIM Framework with Laravel's Eloquent ORM for REST APIs. Recently I faced a problem of too many connections.

During one request URI, I need to make several Get and Set calls to mySql DB. This opens connections on every DB transaction I make. I want to avoid that. Right now, mysql connection pool has 200 threads.

my API is expected to have more than 1000 concurrent calls and with the current environment, 40% of the calls will fail(tested using jMeter).

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

With Eloquent ORM, i have my DB connection being managed by Capsule. Should I make the first connection using Singleton method and for any subsequent call in 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 come out of this problem?

UPDATE

I'm trying another approach for making a persistent connection. But still the persistent connection is not getting closed after the call is done with the job. Even calling DB::Disconnect is not helping.

    <?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 should 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 the you can just pull it out again each 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;
};

You can now use in a route callable:

$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