Very high TTFB - related to mysql?

[NielsH]

Hi there,

 

I'm running a Prestashop with 13K products, and I have some really slow time to first byte loads, sometimes up to 70 seconds.

 

Once the pages are cached, the site loads fine and really fast, but the initial uncached pageload is simply way too slow.

 

I have been monitoring the mysql queries and I have some examples of very slow queries. They seem very inefficient. Is this normal for prestashop and is something wrong with my mysql config, or am I missing something / doing something wrong?

 

Here are the EXPLAINs:

 

1. http://pastebin.com/raw.php?i=dA5VyRnr

2. http://pastebin.com/raw.php?i=vAX66dQi

 

Thanks in advance!

Niels

[bellini13]

when you take those queries and run them directly in mysql client, or within phpmyadmin, how long are they taking?

 

That should answer your question about the issue being related to mysql or not.  If they are taking a long time to run, then a few things are possible

1) your mysql server is not tuned to handle these queries.

2) there could be missing indexes (that does not appear to be the case based on your explains)

3) the queries may need to be refactored to account for the large number of products.  I'm sure Prestashop developers did not test these queries against 13,000 products

[NielsH]

Hi Bellini,

 

The first query for example, which happens when clicking through product pagination takes about 15 seconds directly via the mysql command line. That doesn't seem to be right.

 

It is running on an 8GB server. I have the following settings:

[mysqld]

local-infile=0

innodb_file_per_table

open_files_limit = 12288

query_cache_type=1

 

key_buffer = 128M

thread_stack = 128K

thread_cache_size = 8

table_open_cache = 8192

max_heap_table_size = 256M

query_cache_limit = 4M

query_cache_size = 512M

 

innodb_buffer_pool_size = 4G

 

innodb_log_file_size = 512M

 

Any crucial prestashop settings I am missing?

 

Thanks in advance,

Niels

[bellini13]

there are many tools to assist with tuning, just do some google searching and start tweaking to see if it helps. 

 

Here are the ones I use. 

https://launchpad.net/mysql-tuning-primer

http://mysqltuner.com/

[NielsH]

I've gotten a bit further with additional mysql tuning, but stil facing very slow load times when clicking through products pagination. The more pages there are, the slower it is. One category with 50 pages takes about 5-6 seconds to click through when switching to a different page. When cached, it is very fast of course. During this time, the MySQL maxes out a single core at 100% CPU.

 

Any other ideas?

 

I also have an additional question - how long does Prestashop cache for by default? I couldn't find any settings related to the cache retention.

[NielsH]

Still looking for an answer for this, would love some additional feedback

[Guest]

You haven't left 'Force Compile On' have you?

[Guest]

 

I also have an additional question - how long does Prestashop cache for by default? I couldn't find any settings related to the cache retention.

 

Isn't this determined by your .htaccess file in the root of your installation normally ranging between 1 month to a year depending on asset type.

[NielsH]

Hi,

 

No - I have the middle option - recompile if changed. No force compilation.

 

According to the profiler the blocklayered module is responsible for the very slow MySQL queries. I saw a few other posts and potential fixes/improvements by changing the actual mysql queries but it doesn't seem to a noticeable effect.

[bellini13]

Isn't this determined by your .htaccess file in the root of your installation normally ranging between 1 month to a year depending on asset type.

This depends on what type of caching we are talking about

 

If you are referring to CSS, JS and images, then yes

If you are referring to server side caching (using file system, memory, or some other caching component), then no.

[NielsH]

i'm talking about the smarty cache and filesystem/memcached cache here.

[bellini13]

unless manually cleared or if a change is detected, the smarty cache will be infinite

 

I am not entirely sure about filesystem/memcached/apc cache.  however with relation to mysql, i don't believe the cache survives over multiple page requests, it is only meant to cache data objects during a single page request.

 

For example, you might request the home page, and prestashop will load a lot of data from mysql to satisfy this request, and modules or other code might try to load that same data multiple times.  using this caching mechanism, those data objects would be stored temporarily in cache to service the request and then the cache would be purged.

Edited March 17, 2015 by bellini13 (see edit history)