TTFB issue - very slow loading [1.7.6.4]

[nik]

Recently I have been facing following issue

My prestashop website https://www.rosepoupee.gr/ has a huge TTFB, about 3000-8000ms.
After that time, the page is being displayed immediately with most of its content rendered. The issue appears in every kind of page: home, product, category, even on static ones, with varying TTFB.

I am not quite sure when it started to happen. I would guess that is due to some kind of config, which makes the cms "fully preparing the page", before delivering it to the web server. (sorry for the lame description).

I am listing some facts, which may help you identify the root cause:

Hosting

Hosting resources fine. I am running on a pretty powerfull VPS, even before the issue appeared.

I am running on cloudflare, but the issue appears, even if I disable cloudflare and hit my webserver directly.

On simple static html pages on the same server, the issue does not appear

Addons
My website is not fully packed with addons. It has just 5-6 essential ones that run on the front end, like Page Builder, Menu, Filter, Image gallery, Loyalty Points.

I tried disabling the addons 1 by 1, but the high TTFB remains above 2000ms, where I would normally expect up to 100ms.
For instance, disabling the menu addon drops the time by 1 sec, or adding an additional product carousel raises it accordingly. I would expect those action to affect the total loading time, but not the TTFB. Even disabling all the non-prestashop modules and run in debug mode did not help.

Profiling
I ran a profiler on the homepage and results are socking.

Huge Load Time, Query Time, Number of Queries. initContent and display in particular.
Probably a couple of addons are purely designed, causing that enormous amount of queries, but it still can't be the root cause. 

I still believe that for some reason the full content is sort of being "pre-loaded". Any help would be appreciating.

[nik]

upping the topic

[nik]

anyone?

[El Patron]

look at your mysql configuration via phpmyadmin,  there it will tell you how optimal your mysql config is compared to your runtime stats

it's always been about getting data from disk to cpu...ever since compters were invented.

for last 3 years we have moved all clients to nvme/ssd...which are now available for vps....

you can spend a lot of time/money trying to debug/tune the application (prestashop) when it's almost always time to get data from disk..

so I recommend, get vps with nvme/ssd drive ) with plesk (never cpanel), move to new hosting (ngnix/fpm)...run loader.io or other to volume simulator...look at your mysql stats, tune myslq...

zoom zoom

 

 

[nik]

On 9/13/2021 at 7:57 PM, El Patron said:

look at your mysql configuration via phpmyadmin,  there it will tell you how optimal your mysql config is compared to your runtime stats

it's always been about getting data from disk to cpu...ever since compters were invented.

for last 3 years we have moved all clients to nvme/ssd...which are now available for vps....

you can spend a lot of time/money trying to debug/tune the application (prestashop) when it's almost always time to get data from disk..

so I recommend, get vps with nvme/ssd drive ) with plesk (never cpanel), move to new hosting (ngnix/fpm)...run loader.io or other to volume simulator...look at your mysql stats, tune myslq...

zoom zoom

 

 

Thanks for your reply El Patron.

I would not worry about my server specs. However, I am wondering why you are saying to run it on plesk (never cpanel). I am currently running on cpanel and switching and migrating everything would be quite a big effort.

So I am focusing on MySQL.
As I said on my first comment, based on my profiler results, number of queries and total querying time are very high.

• I have already done some cleaning of useless tables (session data, old carts etc), no significant imporovement
• A couple of addons that I am using are probably poorly designed and they killing MySQL with running hunders of identical queries for each page request, but even if I disable every single addon, there is no significant improvement.

So I had a look at the stats on phpmyadmin. You suggested "there it will tell you how optimal your mysql config is compared to your runtime stats" but I could not find something like this. So I reset Mysql, let it run for a day and then I checked under "Status". The stats look VERY strange:

• According to Google Analytics on this day I had about 500 sessions with 1300 pageviews in total. This was expected.
• According to phpmyadmin, my SQL Server sends 6 GB of data and gets 4500 connections and 1.4 Million Queries EVERY HOUR!!
• If you do the math, it's about 100MB, 80 connections, and 25K queries per pageview.

This makes no sense, the amount of data is huge! My homepage e.g. is not more than 7MB.  I think this is what is killing the DB and TTFB. Any ideas?

Edited September 15, 2021 by nik
incorrect data (see edit history)

[metacreo]

THE BEST OF THE  BEST ANSWER: Roll back or transfer your project to Prestashop 1.6 or run 1.7 on real server.
Because compared to 1.6 presta 1.7 its garbage.
I tell you as a developer who has been working with PS engine for over 10 years and has a lot of prestashop projects at the top of google.

Facts:
Version 1.7 requires many times more server resources.
1.7 сannot be used without maintenance, and should be maintained by an experienced programmer with skills.
1.7 with curves and huge database queries require serious powerful database servers.
Throw out your VPS, VDS and all other services that hosting companies offer, because PS 1.7 almost normally works only on a real server with a powerful CPU and a lot of RAM for the database.

Implementation of a serious large project is impossible at PS 1.7

 

Edited July 12, 2022 by metacreo (see edit history)

[Geo Burlibasa]

You might want to try to use a cache module, it will not fix the actual issue but it will reduce the stress on the database which will surely help.

To fix the actual issue you would need to talk to a developer to check the loading time of the algorithms. In my experience, carrier modules which calculate the shipping cost based on API calls can slow your frontoffice a lot, you might want to check that.

[El Patron]

Look at stats in phpmyadmin, the areas that need improvement are in RED.  Get access via root and update your mysql.config.  most are not configured out of box.

Cache module do way more harm than good and are not recommended.

Upgrade hosting to dedicate or vps with nvme.  This will solve almost all performance issues.

[nik]

Just an update from my side after 1,5 year. Introducing a file-based caching module (incl. daily cache warm up) has DRASTICALLY improved the performance, without causing us headaches on the business side. Stocks are getting updated instantly and you just need to wait for next day to see a front-end change going live.

We still need another step or two in order to get green core web vitals, but the improvement is still surprisingly good.

[Reds]

Hello Nik,

I am experiencing a similar problem, could you please share the name of the module you are using?

 

Best regard,

[El Patron]

Most often TTFB issue is caused by large table, sort via phpmyadmin to find table with large number of rows, guest for example, but 3rd party modules can also create large number of rows.

but as mentioned above, getting data from mysql table is most often the bottle neck, tuning mysql, using hosting with nvme to ssd or hdd can reduce ttfb.

google dev console can be useful tool as well as https://www.webpagetest.org/

[partiid]

On 7/14/2022 at 4:57 PM, nik said:

Just an update from my side after 1,5 year. Introducing a file-based caching module (incl. daily cache warm up) has DRASTICALLY improved the performance, without causing us headaches on the business side. Stocks are getting updated instantly and you just need to wait for next day to see a front-end change going live.

We still need another step or two in order to get green core web vitals, but the improvement is still surprisingly good.

Hello, i know it's an old topic. Now I'm struggling with it too. If you ever read this topic, could you please tell me the module name you used or it was a custom filesystem caching? 

[El Patron]

On 8/28/2023 at 8:09 AM, partiid said:

Hello, i know it's an old topic. Now I'm struggling with it too. If you ever read this topic, could you please tell me the module name you used or it was a custom filesystem caching? 

there is no magic  bullet or module, as mentioned above and for years, ttfb is almost always slow mysql.  if you are not technical on how to tune your mysql configuration then the easier method is to get hosting with nvme to speed up file transfer to cpu.