Hi, we built a site for a client with many tables (50) and functionalities. This site usually load page in 2-3 seconds, so it’ ok, but sometimes it slow down to 15-20 seconds, if the same user open new tab it is blocked until the first page is loaded.
This problem is in production, test and local environment, it seem like is random, without specific reasons like server under pressure, too many request ecc.
This site is a control panel, so we only set the schema cache, no other cache (and we will not active the cache).
We tried to clean app.logs, reload page until it freeze (usually after 3-4 reloadings) and read the file and the Debug Toolbar:
the queries are fast, and from app.log there is no problems, watching timeline on Toolbar it seem like site sleep for 10 secondos and after it run, on the app.log the page is loaded in 2-3 seconds normally.
Whats is happening here?
Why i see only query logs in my debug tools if this is my web.php?
Did you think is an apache problem or a configuration problems?
Has anyone had a similar problem?
I stopped the scheduled task but it not resolve the issue
I know it’s difficult to help me because the project is customized and big, but if someone has a tips please help me.
Php: 7.4.27
Yii2 version: 2.0.49
Logs settings:
[
‘traceLevel’ => 3,
‘targets’ => [
[
‘class’ => ‘yii\log\FileTarget’,
‘levels’ => [‘profile’,‘error’, ‘warning’],
‘categories’ => [
‘*’,
],
],
]
Hi.
The cause can be virtually anywhere. Look on your disk subsystem issues (some RAID?), check filesystem, do disk performance tests. Maybe give us some details about your server environment.
If the hardware base is fine you can add log some microtime in various app points to isolate the problem. Also you can use some Apache functionalities to monitor how the request lives on it, e.g.
Hi, thanks,
i start the mod_status (without request processing times, i do not undestand how to change) in my test server and i restart apache, at the begin the list is empty, after 3-4 minutes it is fully of long requests with “SS Seconds since beginning of most recent request” like 150, so 150 seconds.
This mean the request stay up all this time? because after 15 minutes many request takes like 800-900 SS, so probably this is the problem, this requests are schedules every minutes, and they run many api connection to sync between this backend site and some frontend sites.
Did you think is a possible problem or i’m in fault?
if so, i do an api request from postman and it return in 1 second the response, i see my custom call in this page, but it is still here, why in this panel the request is about 80 seconds and it not disappear?
the Api request do not logout?
Don’t bother the SS value. It is idle time since the most recent worker activity. The ‘Req’ column is the request procesisng time and it is acceptable most of the time.
Look on your Apache’s configuration, for (not) doing reverse DNS lookups, maybe it can be helpful:
Are there really NO queries with a loading time longer than +/- 100ms ? It looks the site used 36 queries but not all are shown.
Are you on a shared hosting? Have you asked the hosting company for feedback?
You can enable the caching of the tables, that saves lots of ‘show full columns’ queries. But that won’t save several seconds.
What does the network tab in your browser say? Is the time to first byte also several seconds?
My first bet is a query for an overview which is not using (the correct) indexes. Same symptoms as you describe. SOMETIMES very long waiting time on the page, with a long running query, after that fast for several minutes of refreshing. Granted that you are not doing heavy data processing after retrieval from the database.
I faced similar problems, but only with windows machine runing apache. No problem with linux server.
Disabling Windows antivirus was the solution for me.
the queries are ordered byt time, the slowest is that you watch on screenshot (18ms)
we have our server, 70 GB RAM and 2 disks of 180GB each, cpu is good and the statistics on grafana (and by linux terminal) show very low usage of cpu, traffics, ram exc.
we enable cache schema on production and test some month ago, it speed up the site but this problem is different
network tab show the page url (the php page) that run for all the time, after the page load in 2 seconds normally, it is not a css / js fault
The tables are indexed and on select we use usually the indexes, but i will check every query.
This problem occour in every page of site, like the home that has not own query, only yii queries for users, permissions and others.
I have not check the reverse DNS lookups, i’m in a hurry up with some work, i’ll check and reply.
Some firewal problem? @isidoro made me think some problem with antivirus and firewall, it’s possible?
Slow DNS queries can be a reason for freeze your site. Can you use another DNS servers in test machine?
And maybe you can find clues in client side. Try using the browser debugger to see times for all requests in site and execution times for js scripts.
Also try the integrated browser profiler, start capturing when the site is sleeping and see what happens on client side.
Hi, i had enabled some week ago the slow query log the sql servers and on local and test there is nothing, in production there are many queries of 40 seconds and the yii log show exceptions, this is a example of slow query:
yii\db\Exception: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
The SQL being executed was: UPDATE `orders` SET `id_order_status`=10 WHERE `id_order`=103356;
BUT: i think it is a different problem, because when yii cannot execute sql because of Lock Exceptions it show the error page with Database Error and this error occur only when we update an order not in every page like the home, SO i do not think it our problem now because i don’t have this error in my local and test environment.
how can i show the schema setup? I see many queries of the slow_wuery_log and all are indexed, BUT remeberd that this file is empty in test and local environment
I tried to install XDebug but i had some difficult to read the file, i would retry but it will take me some days.
If xdebug will be a dead end i will try Sentry, but not immideatly.
These are other screenshot from my local, 35 seconds to load a page (only one is 16 s because i reload page):
We have in production 70.000 rows for the Carts table, there are 3 DB server, 1 master and 2 slaves, the slow query logs are only on the master, in the slaves it is on, but there aren’t slow query.
The innodb_lock_wait_timeout is 50
You have partially answered yourself. If the problem is only on the production env and it does hit you on home page when there are no queries invovled (be sure of that) then stop looking for SQL related problem, focus on lower level components.
Look on your disk subsystem issues (some RAID?), check filesystem, do disk performance tests. If the hardware base is fine you can log some microtime in various app points to isolate the problem.
Sorry, i was in hurry up and i did not check what i wrote.
I mean:
There is the Lock exception only when i update the order, but the problem of the slow page is in every page like the home, so i think this is another problem or it is caused by the slow page problem.
Yes we have RAID but i don’t know how check this tests, i’ll searh some tutorials and test it, but in my local he problem can’t be the disk, or at last what i think is that it is an apache / nginx problem, it is the same for every machine and it manage the connections and the requests
I don’t think it would be this as the MySQL database is what is locking. Not the webserver. Sure the webserver is slow but that is only due to the downstream speed of MySQL’s lock timeout.
Can you prepare some page / controller / action with no DB activity at all (even search for user etc) then test it heavily?
You can use many tools for it: siege, jMeter etc.
You post a slow query log on your cart table,
but show a lock wait timeout on your orders table.
What indexes do you have on your orders table, how big is that table?
Do you have backups running which lock tables?
What engine type do your cart and orders table use?
Do you have pages/process with transactions that use multiple tables? For example a transaction that updates your cart AND orders table (and presumably, also other tables)?
I sometimes had lock wait problems, but had import processes (several GB of data) running over almost all tables and almost all data, within ONE, yes, ONE, big transaction. So on some moments almost the complete database was locked for writing, all InnoDb tables.
When locking became too much of a hassle I’ve rewritten the import to work as much as possible ‘per table’, many smaller transaction, and the problem is gone.
