Strugling to lower TTFB - MYSQL optimizations.

[alvesjc]

Hello.

 

I've been trying to optimize my server as much as possible to lower TTFB.

 

It was originaly 1.6s, it's down to around 500ms.

 

But it's still very high, mostly because I don't have much traffic yet.

 

My store has around 800 products.

 

I've tried with and without memcached, no change.

 

I've used mysqltuner and it complains alot about joins no indexed. Because of this I'm now with 8192M set for join buffer, but still getting complains.

 

I've created index in all the tables that prestashop runs by when accessing my homepage (using mariadb-slow log file to see what was beeing used).

 

But I still get complains about joins.

 

I'm running prestashop 1.6.1.4, what can I do to make joins index to work?

 

Can someone help me please?

 

Thank you.

 

Best regards,

 

Joao

[alvesjc]

Anyone?

[alvesjc]

I've updated Prestashop to 1.6.1.10 and gained another 200ms.

 

TTFB is now around 300ms. I suppose some optimizations have been done since 1.6.1.4.

 

Any clues on how to go close to 100ms?

[bellini13]

using PHP 7.0.x will help (if you are not already)

 

I have not encountered a store anywhere near 100ms without the use of accelerators and a cdn.

[alvesjc]

using PHP 7.0.x will help (if you are not already)

 

I have not encountered a store anywhere near 100ms without the use of accelerators and a cdn.

Hi!

 

Thank you for your comments.

 

I'm using 5.6.29 with fastcgi, I don't know if prestashop is ok with php 7.x, I'll search on that, thank you for the clue.

 

Regarding CDN, I'm using already CloudFlare, but I don't see any diference in the TTFB with or withouth CDN. Probably the first request goes straight to the server anyway, I don't know.

 

By accelerators you were refering to CDN's or is anything else that I can put on the server?

[alvesjc]

Thank you very much Bellini, that was of very great help!

 

Using PHP 7.0.14 now with FPM to apache. Result is 125ms !!!!!!!

 

With fastCGI was around 190ms!!!

 

I'll install memcached to php 7 and see if it helps further more, but with 5.6 no changes were noticed with or without memcached.

 

I'll update after this tests.

 

Regards

 

Joao

[bellini13]

You should be fine with PHP 7.0.14, however PHP 7.1 will not work.

 

The CDN will help, even if you do not see single user speed improvements.  Your page testing is likely very specific to your location, or from what ever service you are using to test. But you have to consider...

1) Users that are 'far' from your data center will likely see improvements while using a CDN.  Many CDN will cache and serve resources closer to where your visitors are from

 

2) Using a CDN allows browsers to download multiple resources in parallel.  Without a CDN, most browsers are configured to only download 4 resources at a time per domain (js, css, images etc...).  And I am sure your store has many more than 4 resources on each page.  I use 3 different media servers on my website, and so that allows a browser to download up to 16 resources in parallel, without the CDN it would have been 4.  This is not necessarily helping TTFB, but you need to look past TTFB and what an actual visitor will be seeing.  A human won't really care that it took 200ms versus 500ms for the TTFB on the main request.  But they will care that the entire page load was fast.

 

3) Consider when your website is under heavy demand (a lot of visitors).  Offloading the service of the media files to a CDN takes that demand off your server.

 

Look into opcache and make sure that is enabled.  That pre-compiles php scripts, so they are not compiled every time they are requested.

Also look into a cache engine of some sort, forget memcache.  Look at something like APC, this will cache objects in memory, which reduces SQL queries dramatically.