Level 102
It sounds like your initial connection is what is slow. It is counted as part of the query, as PDO does not distinquish it.
How is your .env set up in regards to the database ?
1 like
Feb 13, 2023
17
Level 1
Slow query execution time
Alright, i've been searching everywhere for a solution to this, but nothing i seems to improve my situation. I have a simple mysql query like this:
$this->session = Session::query()->select([
'key',
'data',
'last_accessed'
])->where('key', $this->sessionKey)->first();
- The table contains about 16 rows
- There is an index for the "key" column (its the primary key)
- The key column is a
VARCHAR(40) - I have the same problem on my own machine, an OVH VM in the USA and an OVH VM in Asia which all use different databases on different servers so i am fairly certain this is unrelated to connection speed, database and/or server config
The query takes about 1 second to execute. When i take the exact same query (copied using the laravel debugbar) and run it on the database (using DataGrip from the same machine laravel is on) the query barely takes 100ms to run.
Looking at the timeline from the debugbar is seems to be only the very first query that runs that is taking a long time. If i run a query like this DB::select("SELECT 1") right in front of the session query that query will take about 900ms while the session query all the sudden only takes only 200ms.
I am unable to post links on my first day after signing up but here is some screenshots with more information:
https:// imgur .com/a/rgRXphW (just remove the spaces ig)
(I also tried commenting out the additional pdo options without any change in speed.)
Level 1
@Sinnbeck This is the database configuration in my .env (with sensitive data removed)
DB_CONNECTION=mysql
DB_HOST=*********
DB_PORT=3306
DB_DATABASE=*********
DB_USERNAME=*********
DB_PASSWORD=*********
1 like
Level 102
@coalaura So since the host is *** does that mean its external. Not on the same server?
1 like
Level 1
@Sinnbeck yes, that is correct. However i have the same problem on 3 different hosts which all use their own database servers which are in some cases even routed in the same network so i doubt that would have an influence on performance
1 like
Level 102
@coalaura Can you try it locally with a local databsae on 127.0.0.1 ?
1 like
Level 1
@Sinnbeck i only recently moved to an external database server and before i did, i had the same issue. I also tried to run the query from my local machine on the same server and achieved way faster performance. My 2 production servers also experience the same issue
1 like
Level 102
@coalaura Ok that sounds very strange! Did you try making a fresh laravel 9 app, adding a migration for that table, seed it and then test it out ? If it works perfectly then, you can compare the code or if its the same, share it on github so we can debug it with you
Level 1
@Sinnbeck I'll try that, this current project is a bit older too and runs on laravel ^8.0
Level 1
@Sinnbeck okay interesting. I tried a fresh laravel 9 install with the remote server and had the exact same issue. To keep my sanity i installed a fresh mysql server on my PC and tried it with the localhost and that took only 4.5ms. So it seems that it really has to do something with the connection or configuration. I do strongly remember having the same issue with my local database too before i moved it to an external server so i am a bit confused.
That being out of the way, i have a few other applications that also use the same external server though and they don't seem to have any problem regarding performance...
Level 63
Does it take the same time with this ?
$this->session = Session::all();
Level 1
@vincent15000 just tested it and it takes the exact same time
1 like
Level 27
Well, that is definitely not normal. A query like that should take perhaps 10–20ms, not much more. But it’s hard to say much based on what little you’ve told us about your setup.
Is the database server on the same machine as your Laravel app? Is it localhost or a remote server? How are you connecting? What kind of server is it (both the Laravel server and the database server if they’re not the same)?
1 like
Level 1
@kokoshneta For my local environment i use a Windows 10 machine (i9 11th gen, 128gb ram, SSD) for the database that my dev environment uses i use an ubuntu digitalocean VM.
The 2 other hosts that are experiencing the same issue are both ubuntu hosts on OVH VMs. Running the queries on the database directly results in very high speed, it seems to only be the problem when using laravel
1 like
Level 27
@coalaura So these are queries where you’re connecting from your localhost to the DigitalOcean VM?
Have you tried deploying some code to the VM and loading the page there to see if that’s better? If it is, then you know the problem is, in one way or another, related to the connection from your machine to the remote machine.
Also, when you say the ‘first’ query, what does that mean exactly? If you load the page, then reload it, does it take as long again, or is it faster the second time? If it’s only slow if you wait a while before the next query, it may be something related to keeping connections alive.
1 like
Level 1
@kokoshneta I did some testing just now and installed a fresh clean MySQL server locally and using said server speeds things up dramatically, so it turns out it may really be something with the connection.
With first query i mean that when i look at the timeline in the Laravel debugbar the first query that is being run takes a considerably longer time to execute than the following ones (first one takes 1s the next ones take 200ms). Reloading the page results in the exact same execution time. No matter how long i wait inbetween requests (it jumps around between 900ms and 1.1s, no matter if its the very first page load or a following one)
1 like
Level 27
@coalaura Using a local MySQL server should definitely fix it, yes – it would have to be something in your actual Laravel code otherwise.
But the fact that that fixed it doesn’t necessarily mean it’s the connection from your computer to the DO server – it may also be something on the DO server itself that makes the connection from the server software to the database software slow. To test that, you’d have to get your code on to the same DO server where the database is hosted, and then try running it directly from that server.
If that fixes it too, then the problem is related to your connection to the server somehow. If it doesn’t, then the problem is internal on the server, perhaps a MySQL misconfiguration.
1 like
Level 1
@kokoshneta don't think its the database server since i have a bunch of other applications on the same host the laravel app is running on, which using the same database server without any problems
1 like
Please or to participate in this conversation.