Yii2 slow active record

Question

I have a Yii2 based web project. Recently I've write some REST API to it. I've realized, that every REST API call has a very long response time. 1134ms, 1250ms, 1034ms etc., so basically the avarage response time is above 1 second.

The Client model has no relation (Client table is a 'standalone' table).

My test table (client) is contains 173 record (1 row has 10 columns). I debugged the problem and marked the related line:

    ...
    $client_id = Yii::$app->request->post('client_id');

    // client_id ellenőrzése (pl. blokkolt-e a mobil kliens)
    if (!empty($client_id)) {
        $client = Client::findOne($client_id);       <-----
    ...

So far I've not configured any cache components, because I don't think, that a table with 173 record is required that.

Without the mentioned findOne() line the response time is avarage 30ms. The environment:

php 7,
Mysql 5.5,
Yii 2

What should be the problem ? Something in configuration? I developed another project with Yii 1.1 a few years ago, I didn't remember this kind of problem there.

Thank you.

UPDATE #1:

UPDATE #2: I've noticed, that every activerecord realated operation takes about 1 second to finish (not just Client related operations). Getting 10 items to a gridview, update 1 record etc.

UPDATE #3: Ok, something strange is happening. I've created a very simple action, which also requires ~1 second to render:

public function actionTest() {
    echo "OK";
}

The login page requires avarage 32ms to load.

update you question and add the Client table schema .. please — ScaisEdge, Nov 07 '17 at 12:48
My client is wanted to see in an automatically increasing number in the UI (gridview and form). But the client_id is coming from a mobile device, so the id and the client_id cannot be the same. — wyzard, Nov 07 '17 at 14:10
The reason I'm asking, is that I think using a varchar(255) as a primary key is not a very good idea. Now according to https://stackoverflow.com/questions/332300/is-there-a-real-performance-difference-between-int-and-varchar-primary-keys , using varchar rather than int as a primary key shouldnt make a big difference. But using 255 chars though is problematic I guess. Now I suggest you reduce this size and try — mrateb, Nov 07 '17 at 14:21
What I mean is that primary keys should be as small as possible https://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables — mrateb, Nov 07 '17 at 14:23
Or sorry not sure that this is the primary key, but I mean searching in a field that is 255 chars, is going to take time — mrateb, Nov 07 '17 at 14:29
I've reduced the size for testing purpose (32), still ~1.100 ms. I don't think this is the problem (maybe in a table, which has many million records). If I run this SQL select from a Mysql client, the response time is nearly 0ms: select * from client WHERE client_id = '23ff0046c1e2-b532-4631-b661-43e487dbe085' — wyzard, Nov 07 '17 at 14:36

score 1 · Accepted Answer · answered Nov 07 '17 at 21:38

Ok, after a half a day searching I found the problem and the solution. After I managed to start the Yii debug toolbar (ˇ2 hours :) ) I've realized this:

And after spending another few hours, I found the solution. I had to replace this config line:

'dsn' => 'mysql:host=localhost;dbname=test',

to

'dsn' => 'mysql:host=127.0.0.1;dbname=test',

Maybe MySQL is not listening on IPv6 sockets or other configuration causes this problem, but now the average respone time is ˇ53ms.

Yii2 slow active record

1 Answers1