Jump to content

Ps 1.6.1 Slow Initial Page Load - 400K+ Rows In Configuration Table?

ln_

By ln_
in Configuring and using PrestaShop

 Share

Recommended Posts

ln_ Newbie

ln_

Posted
Posted (edited)

Hi all,
 
One of our sites is running quite slowly, >1000ms to return initial data from the server. Server is a linux VPS (8GB RAM, 8VCPUs) running on a grunty hypervisor.
 
Below I'll attach some results from a profile. In particular, I'm staggered by the 1258ms in the 'config' step. I have no idea what exactly goes into this step - but I'd love to reduce it. Any ideas how I can get more specific profiling of this step? 
 
I've tried tweaking all kinds of caching options, upgrading to php 5.6 and enabling opcode caching, installing xcache and using that, changing just about every other caching option (including all smarty caching), turning off all caching, turning off all modules, disabling apache rewrites (in case that was slowing things down), tweaking & profiling mysql, php and apache options as typically recommended. I've spent quite a bit of time on this, and nothing seems to reduce this initial config step timing.
 
Other apps on the same server (phpmyadmin etc) run very fast. I have to assume it's some kind of prestashop setting causing the issue.

 

The worst performing SQL statement is also attached - it's the one which returns the configuration. A staggering 405,684 rows are returned by this SQL - which as far as I can tell is JUST to get the configuration data, which is done every single time a page loads! Is there any way to clean these tables up? I imagine that would help the page loads considerably!
 
I've reached the end of my tether; any further help greatly appreciated!
 
Here's the profile (apologies for the formatting, it looked lovely when I pasted it in, turned to an absolute mess in the preview though):
 
 

Load Time 2243 ms - You'd better run your shop on a toaster
Querying Time 395 ms
Queries 73
Memory Peak Usage 67.4 Mb
Included Files 677 files - 9.36 Mb
PrestaShop Cache 0.34 Mb
Global vars 2.90 Mb
 
PrestaShop Version 1.6.1.1
PHP Version 5.6.14-0+deb8u1 (OK)
MySQL Version 5.5.46-0+deb8u1-log (Consider upgrading)
Memory Limit 2G
Max Execution Time 0s
Smarty Cache enabled
Smarty Compilation auto
 
  Time Cumulated Time Memory Usage Memory Peak Usage
config 1292ms 1292 ms 5.08 Mb 6.10 Mb
__construct 0 ms 1292 ms - Mb 6.10 Mb
init 50ms 1343 ms 2.18 Mb 8.19 Mb
checkAccess 0 ms 1343 ms - Mb 8.19 Mb
setMedia 37ms 1379 ms 0.91 Mb 9.14 Mb
postProcess 0 ms 1380 ms - Mb 9.14 Mb
initHeader 14ms 1394 ms 1.05 Mb 9.47 Mb
initContent 838ms 2231 ms 56.24Mb 67.38Mb
initFooter 1 ms 2232 ms 0.02 Mb 67.38Mb
display 11ms 2243 ms 0.34 Mb 67.38Mb
 
Hook Time Memory Usage
__construct 62ms 1.75 Mb
4 hooks 63ms 1.75 Mb
 

Module Time Memory Usage

allinone_rewards 26ms 1.00 Mb

paypal 12ms - Mb

ordertaxprofitreport 6 ms 0.25 Mb

blockproductfilter 3 ms - Mb

themeconfigurator 3 ms - Mb

ganalytics 3 ms - Mb

autoupgrade 2 ms 0.25 Mb

dashgoals 2 ms - Mb

dashproducts 2 ms - Mb

dashactivity 2 ms - Mb

dashtrends 1 ms - Mb

graphnvd3 1 ms 0.25 Mb

statsdata 1 ms - Mb

13 modules 63 ms 1.75 Mb

 

 

Query Time (ms) Rows Filesort Group By Location

 

SELECT SQL_NO_CACHE c.`name`, cl.`id_lang`, IF(cl.`id_lang` IS NULL, c.`value`, cl.`value`) AS value, c.id_shop_group, c.id_shop FROM `ps_configuration` c LEFT JOIN `ps_configuration_lang` cl ON (c.`id_configuration` = cl.`id_configuration`) 372.8 405684     /classes/Configuration.php:148

/classes/Configuration.php:185
/classes/Configuration.php:247
/classes/shop/Shop.php:369
/config/config.inc.php:114
/index.php:27

 

 

Edited by ln_ (see edit history)
Link to comment
Share on other sites
jaanrs Newbie

jaanrs

Posted
Posted (edited)

Ive also been in the process of trying to speed up prestashop after upgrading to 1.6, and the configuration load time is the biggest problem left.

 

In my shop, loading the config takes 800ms - the configuration.php takes 500ms to load and there are a ridiculous 113000 rows. The sql ps_configuration was only 500 rows back in prestashop 1.5

 

EDIT:

 

The fix was actually really simple. On a fresh install of prestashop 1.6 the configuration table is only under 400 rows. In my case all the additional rows seem to have been remnants from some old statistics modules (not sure yet). Deleting the extra rows brought the total load time of init to 90ms, about the same as in a fresh install. Deleting the rows seems to not have had any affect in functions of statistics in the back end.

 

Total row count came down from 113000 to 800 by removing the entrys with "name":

 

SALES_CATALOG (+ SALES_CATALOG_EXPIRE)

ABANDONED_CARTS +expire
AVG_CUSTOMER_AGE +expire
AVG_ORDER_VALUE +expire
CONVERSION_RATE +expire
CUSTOMER_MAIN_GENDER +expire
DISABLED_CATEGORIES +expire
DISABLED_MODULES +expire
DISABLED_PRODUCTS +expire
EMPTY_CATEGORIES +expire
NETPROFIT_VISIT +expire
NEWSLETTER_REGISTRATIONS +expire
ORDERS_PER_CUSTOMER + expire
PERCENT_PRODUCT_OUT_OF_STOCK + expire
PRODUCT_AVG_GROSS_MARGIN +expire
PRODUCTS_PER_CATEGORY +expire
TOP_CATEGORY +expire
UPDATE_MODULES +expire
ENABLED_LANGUAGES +expire
FRONTOFFICE_TRANSLATIONS +expire
INSTALLED_MODULES +expire
MAIN_COUNTRY +expire

Edited by jaanrs (see edit history)
  • Like 1
Link to comment
Share on other sites
ln_ Newbie

ln_

Posted
  • Author
Posted

Certainly my database contains many of those same entries with a lot of rows, but I'm not confident if they are safe to just delete. If I can get some idea of which are safe to delete, I'll gladly hurl them into the abyss, I'm sure it'll make a big difference!

 

Here are some row counts:

 

mysql> select count(*),name from ps_configuration group by name;
+----------+-------------------------------------+
| count(*) | name                                |
+----------+-------------------------------------+
|    16956 | 8020_SALES_CATALOG                  |
|    16956 | 8020_SALES_CATALOG_EXPIRE           |
|    32443 | ABANDONED_CARTS                     |
|    32443 | ABANDONED_CARTS_EXPIRE              |
|      713 | AVG_CUSTOMER_AGE                    |
|      713 | AVG_CUSTOMER_AGE_EXPIRE             |
|    32443 | AVG_ORDER_VALUE                     |
|    32443 | AVG_ORDER_VALUE_EXPIRE              |
|    32445 | CONVERSION_RATE                     |
|    32445 | CONVERSION_RATE_EXPIRE              |
|      734 | CUSTOMER_MAIN_GENDER                |
|      734 | CUSTOMER_MAIN_GENDER_EXPIRE         |
|      347 | DISABLED_CATEGORIES                 |
|      347 | DISABLED_CATEGORIES_EXPIRE          |
|      268 | DISABLED_MODULES                    |
|      268 | DISABLED_MODULES_EXPIRE             |
|    16956 | DISABLED_PRODUCTS                   |
|    16956 | DISABLED_PRODUCTS_EXPIRE            |
|      347 | EMPTY_CATEGORIES                    |
|      347 | EMPTY_CATEGORIES_EXPIRE             |
|      268 | INSTALLED_MODULES                   |
|      268 | INSTALLED_MODULES_EXPIRE            |
|    31836 | NETPROFIT_VISIT                     |
|    31836 | NETPROFIT_VISIT_EXPIRE              |
|      711 | NEWSLETTER_REGISTRATIONS            |
|      711 | NEWSLETTER_REGISTRATIONS_EXPIRE     |
|      713 | ORDERS_PER_CUSTOMER                 |
|      713 | ORDERS_PER_CUSTOMER_EXPIRE          |
|    16956 | PERCENT_PRODUCT_OUT_OF_STOCK        |
|    16956 | PERCENT_PRODUCT_OUT_OF_STOCK_EXPIRE |
|      347 | PRODUCTS_PER_CATEGORY               |
|      347 | PRODUCTS_PER_CATEGORY_EXPIRE        |
|    16955 | PRODUCT_AVG_GROSS_MARGIN            |
|    16955 | PRODUCT_AVG_GROSS_MARGIN_EXPIRE     |
|      347 | TOP_CATEGORY                        |
|      347 | TOP_CATEGORY_EXPIRE                 |
|      875 | UPDATE_MODULES                      |
|      268 | UPDATE_MODULES_EXPIRE               |
 
Link to comment
Share on other sites
ln_ Newbie

ln_

Posted
  • Author
Posted

 

It's not just the backend which suffers from this though, I think - even shop page loads have the same configuration hit, so I don't think disabling admin section statistics will help?

Link to comment
Share on other sites
greanman Newbie

greanman

Posted
Posted

I've been having trouble with prestashop 1.6 site speed for months with no joy for a solution but by commenting out the kpi as explained by Calatravo here https://www.prestashop.com/forums/topic/393493-remove-kpi-container-from-bo-pages/ and cleaning out the big numbers of entires listed in mySQL table ps_configuration listed by ln_ above site speed both frontend and backend is incredibly fast.

 

Very happy right now!

Link to comment
Share on other sites
  • 2 weeks later...
Christian - OW Newbie

Christian - OW

Posted
Posted

Hi.

 

First, excuse me for my english (I'm french).

 

Apparently this is a bug for PS 1.6.1.1. If you update your PS to the latest version, there will be no new records.
But if you don't want update, just change this line (line 446 in function updateValue() )
$result &= Db::getInstance()->insert('configuration', $data, true);

to 

$result &= Db::getInstance()->insert(self::$definition['table'], $data, true);

And delete this rows in configuration table (ty jaanrs) :

 

SALES_CATALOG (+ SALES_CATALOG_EXPIRE)

ABANDONED_CARTS +expire
AVG_CUSTOMER_AGE +expire
AVG_ORDER_VALUE +expire
CONVERSION_RATE +expire
CUSTOMER_MAIN_GENDER +expire
DISABLED_CATEGORIES +expire
DISABLED_MODULES +expire
DISABLED_PRODUCTS +expire
EMPTY_CATEGORIES +expire
NETPROFIT_VISIT +expire
NEWSLETTER_REGISTRATIONS +expire
ORDERS_PER_CUSTOMER + expire
PERCENT_PRODUCT_OUT_OF_STOCK + expire
PRODUCT_AVG_GROSS_MARGIN +expire
PRODUCTS_PER_CATEGORY +expire
TOP_CATEGORY +expire
UPDATE_MODULES +expire
ENABLED_LANGUAGES +expire
FRONTOFFICE_TRANSLATIONS +expire
INSTALLED_MODULES +expire
MAIN_COUNTRY +expire

  • Like 2
Link to comment
Share on other sites
  • 2 weeks later...
ln_ Newbie

ln_

Posted
  • Author
Posted

Dear OP, how did You made this speed and usage statistic? Is this is an extra module? Sorry for offtopic.

 

In config/defines.inc.php, change

define('_PS_DEBUG_PROFILING_', false);

 

to

 

define('_PS_DEBUG_PROFILING_', true);

 

Note this displays all the info on the pages themselves, so if it's a production site, the world will see it.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...