Simple Eloquent query too slow when using remote database

Question

Question Edit

Server

32 GB RAM
innodb_buffer_pool_size=1400M

Database

Fresh empty table:

fields(id, name, type, optional)

SHOW TABLE STATUS LIKE "fields" result

Name:fields|Engine:InnoDB|Row_format:Dynamic|Rows:0|Av_row_length:0|Data_length:16384|Max_data_length:0|Data_free:0|Auto_increment:null|Create_time:2022-08-01 12:52:42|Update_time:NULL|Check_time:NULL|Collation:utf8_general_ci|Checksum:NULL|Create_options:|Comment:|Max_index_length:0|Temporary:N

Test code

Route::get('/', function () {
    $gtc = microtime(true);
    DB::connection()->getPdo();
    $total = round((microtime(true) - $gtc) * 1000);
    echo "Laravel Connection time: $total ms<br>";

    $gtc = microtime(true);
    DB::select('SELECT * FROM fields');
    $total = round((microtime(true) - $gtc) * 1000);
    echo "Laravel Get rows time: $total ms<br>";

    $gtc = microtime(true);
    DB::select('SELECT 1');
    $total = round((microtime(true) - $gtc) * 1000);
    echo "Laravel SELECT time: $total ms<br>";

    $gtc = microtime(true);
    $pdo = new MySQLModel(env('DB_HOST'), env('DB_DATABASE'), env('DB_USERNAME'), env('DB_PASSWORD'));
    $connection = $pdo->getConnection();
    $total = round((microtime(true) - $gtc) * 1000);
    echo "PDO Connection time: $total ms<br>";

    $gtc = microtime(true);
    $ps = $connection->prepare('SELECT* FROM fields');
    $ps->execute();
    $total = round((microtime(true) - $gtc) * 1000);
    echo "PDO Get rows time: $total ms<br>";

    $gtc = microtime(true);
    $ps = $connection->prepare('SELECT 1');
    $ps->execute();
    $total = round((microtime(true) - $gtc) * 1000);
    echo "PDO SELECT time: $total ms<br>";

    for($i = 0; $i<5; $i++) {
        print_r(DB::select('SELECT NOW(3)'));
        echo '<hr>';
    }
});

Results

Laravel Connection time: 549 ms
Laravel Get rows time: 136 ms
Laravel SELECT time: 137 ms
PDO Connection time: 282 ms
PDO Get rows time: 68 ms
PDO SELECT time: 68 ms
Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:10.784 ) ) 
Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:10.921 ) ) 
Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:11.057 ) ) 
Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:11.193 ) ) 
Array ( [0] => stdClass Object ( [NOW(3)] => 2022-08-01 13:24:11.333 ) ) 

This does not happen if the database is located in the same server but when connecting with the remote one.

It all points it is a connection issue related with Eloquent and I haven't found any information about configuring something to prevent such a huge increase in query execution (tends to doubles it in all test I have made).

Answer 1

It is Laravel default config fault, it will query the server multiple times to setup the * * database instead of creating a single SQL Query to send to MySQL.

Set charset to null in config/database.php

'charset' => null,

It will prevent Laravel sending a

"set names '{$config['charset']}'".$this->getCollation($config)

query to the server.

Next set strict to NULL in config/app.php in the mysql key

'strict' => null,

It will prevent Laravel from

$connection->prepare($this->strictMode($connection, $config))->execute();

or

$connection->prepare("set session sql_mode='NO_ENGINE_SUBSTITUTION'")->execute();

This will prevent server communication delay which results the connection request to the server be a lot faster.

Laravel Connection time: 268 ms

For the SELECT queries taking double time it is due

$statement = $this->prepared(
                $this->getPdoForSelect($useReadPdo)->prepare($query)
            );//+68 ms more!

which communicates with the server for PDO prepare.

$statement->execute();//+68 ms more!

Set PDO::ATTR_EMULATE_PREPARES => true in the options key within mysql array set to enable emulated prepares and increase performance.

The performance should be almost equal to PDO now.

Answer 2

Inconsistent timing:

Don't turn on the stopwatch. The first fetch is slower because of fetching data from disl; the rest run faster. due to caching.

Tuning:

What is the value of innodb_buffer_pool_size? How much RAM does the server have?

Connecting:

The program should connect only once, and do it before starting any timing tests. Time the connection separately. (Yes, 400ms to connect is absurd.)

Data_length:

16K is the 'minimum' size of a non-empty table. That is 1 block. Even on the least powerful local machine, the SELECT * FROM t should take under 10ms.

Next steps:

If you can instrument Eloquent this way, we can focus on the connection, not be distracted by the SELECTs:

Print the time just and just after before making the connection.

If that is not practical, then change the query to simply SELECT 1.

Another approach is to perform and display SELECT NOW(3) in a loop 5 times. This should demonstrate whether Eloquent is reconnecting!