Speed up shop when there are customization

[giovanni.scapellato]

Hello

 

in my shop there are several products using customizations. 

After some time the site started to became slow and I found there there is a query, in the delete method of Cart.php class that is creating the issue.

 

In fact from the log I found

 

 

 

# Query_time: 3.079904  Lock_time: 2.838509 Rows_sent: 0  Rows_examined: 85901

SET timestamp=1495846743;

DELETE FROM `ps_customized_data`

                        WHERE `id_customization` IN (

                                SELECT `id_customization`

                                FROM `ps_customization`

                                WHERE `id_cart`=26988376

                        )

;

 

 

And executing the Explain, you can see that MySQL 5.5 or 5.6 is not able to use indexes, i.e. full table scan!!!! 

 

In order to fix the issue and speed up the shop I changed the delete method in Chart.php file. Unfortunately I was not able to override it, because the delete call the parent, so if I override the delete method of the parent is called and the slow query is executed

 

Cart.php

 

public function delete()

    {

  .....

 

// Slow query. In order to optimize it I will split in 2 parts

        

        /*

         Db::getInstance()->execute('

         DELETE FROM `'._DB_PREFIX_.'customized_data`

         WHERE `id_customization` IN (

         SELECT `id_customization`

         FROM `'._DB_PREFIX_.'customization`

         WHERE `id_cart`='.(int)$this->id.'

         )'

         );

        */

 

        // Split the query with nested query in 2 parts

        // 1. get the customizations

        $result_cust = Db::getInstance()->executeS('

 

              SELECT `id_customization`

                 FROM `'._DB_PREFIX_.'customization`

                 WHERE `id_cart`='.(int)$this->id.'

                 '

         );

 

         // 2. for each customization, de

         foreach ($result_cust as $cust) {         

               Db::getInstance()->execute('

                  DELETE FROM `'._DB_PREFIX_.'customized_data`

                     WHERE `id_customization` = '. 

                        $cust['id_customization']

                     .'

                     '

                );

           }

 

     ....

 

     return parent::delete();

}

 

 

 

 

I know that this solution is not very 'elegant'  but it works and performance were improved a lot.

 

Is there a way to ask to include in the PrestaShop core class?

 

 

Thanks

Giovanni

 

 

 

 

[razaro]

Hi Giovanni

 

Welcome to the forum.

That looks like nice improvement but do you have exact numbers after you made changes ?

And for what version is that ?

 

Also here is  how to contribute to PrestaShop

http://build.prestashop.com/news/video-contributing-to-the-prestashop-project/

 

Think it is best to make forge report.

[giovanni.scapellato]

Hi 

 

I'm using prestashop 1.6.1.3, but same can be applied to 1.6.1.9

 

The improvement was huge in term of response time. In the customized_data table I have about 85000 rows

 

With a single user, for each request, this query is called several times, and as you can see it takes about 0.23 seconds (that is a lot), also if there is nothing to delete in the table (0 rows sent).

 

From the slow query file I taken this log. With the modification the query is not anymore present.

 

 # Query_time: 0.226430  Lock_time: 0.000063 Rows_sent: 0  Rows_examined: 85901

SET timestamp=1495842988;

DELETE FROM `ps_customized_data`

                        WHERE `id_customization` IN (

                                SELECT `id_customization`

                                FROM `ps_customization`

                                WHERE `id_cart`=26983154

                        );

 

 

During the day, when multiple users were using the shop, this query caused the site to slow down, CPU of VM started to be fully busy and the same query started to take more than 15 seconds.

 

# Time: 170527 10:45:15

# User@Host:  @ localhost []  Id:  2927

# Query_time: 16.755126  Lock_time: 16.524078 Rows_sent: 0  Rows_examined: 85929

SET timestamp=1495874715;

DELETE FROM `ps_customized_data`

                        WHERE `id_customization` IN (

                                SELECT `id_customization`

                                FROM `ps_customization`

                                WHERE `id_cart`=27045371

                        );

# User@Host: @ localhost []  Id:  3042

# Query_time: 16.761857  Lock_time: 16.524823 Rows_sent: 0  Rows_examined: 85929

SET timestamp=1495874715;

DELETE FROM `ps_customized_data`

                        WHERE `id_customization` IN (

                                SELECT `id_customization`

                                FROM `ps_customization`

                                WHERE `id_cart`=27045372

                        );

# User@Host: @ localhost []  Id:  3043

# Query_time: 16.761460  Lock_time: 16.532928 Rows_sent: 0  Rows_examined: 85929

SET timestamp=1495874715;

DELETE FROM `ps_customized_data`

                        WHERE `id_customization` IN (

                                SELECT `id_customization`

                                FROM `ps_customization`

                                WHERE `id_cart`=27045373

                        );

# User@Host: @ localhost []  Id:  3058

# Query_time: 16.782614  Lock_time: 16.526541 Rows_sent: 0  Rows_examined: 85929

SET timestamp=1495874715;

DELETE FROM `ps_customized_data`

                        WHERE `id_customization` IN (

                                SELECT `id_customization`

                                FROM `ps_customization`

                                WHERE `id_cart`=27045374

                        );

# User@Host: @ localhost []  Id:  2879

# Query_time: 16.778829  Lock_time: 16.547900 Rows_sent: 0  Rows_examined: 85929

SET timestamp=1495874715;

DELETE FROM `ps_customized_data`

                        WHERE `id_customization` IN (

                                SELECT `id_customization`

                                FROM `ps_customization`

                                WHERE `id_cart`=27045375

 

                        );

 

Now the delete is not done at all (when not needed), and the nested query used to understand if delete has to be done takes few milliseconds (0.0006 sec)

 

SELECT `id_customization` 
FROM `ps_customization` 
WHERE `id_cart` =27045375

LIMIT 0 , 30

[giovanni.scapellato]

I found a better and elegant solution, that in a single query is able to perform the delete using the INDEX in My SQL

The original query perform a full table scan in ps_customized_data, whereas my query uses the index, and often perform the delete in 0 rows

 

In my case, with 85000 rows in the ps_customized_data and no users in the web site: 

 - the  query takes  0.0759 sec)

 - the modified query only 0.0030 sec)

 

As said before, I started to have Issues when there are multiple users, and the query without index start to consume the CPU and slow down the system 

 

 

 

public function delete()

 

    {

... 

 

/*

         Db::getInstance()->execute('

         DELETE FROM `'._DB_PREFIX_.'customized_data`

         WHERE `id_customization` IN (

         SELECT `id_customization`

         FROM `'._DB_PREFIX_.'customization`

         WHERE `id_cart`='.(int)$this->id.'

         )'

         );

        */

        

        Db::getInstance()->execute('

         DELETE FROM `'._DB_PREFIX_.'customized_data` USING `'._DB_PREFIX_.'customized_data`

         INNER JOIN (

         SELECT `id_customization`

         FROM `'._DB_PREFIX_.'customization`

         WHERE `id_cart`='.(int)$this->id.'

         ) AS cu ON `'._DB_PREFIX_.'customized_data`.`id_customization` = `cu`.`id_customization`'

        );

...