Speeding Up PrestaShop's Loading Time From 5 to 1 Seconds (Case Study)
In the article 'Our Secret SuperSupport and Your Websites' we revealed all hidden daily activities which the SuperSupport performs for our customers’ websites. Now we will give one more example about how we take the care for our customers and their websites at heart.
Our Secret SuperSupport in action
During a routine daily check performed by our team we stumbled upon a website which had considerably increased its MySQL CPU time usage. This check was part of the SecretSupport activities and was initiated due to a notification for slow SQL queries executed on the hosting server.
Increased CPU usage is not a clear indicator for a problem as it can be a natural consequence of a traffic peak to the website.
As we checked in details we found out that the website was a vintage bookshop - https://knigite.eu. While looking through the catalog we noticed that loading any webpage was taking approximately 5 seconds. This took too long for loading an online store and waiting so much was definitely not contributing to a great user experience.
5 seconds wеre definitely too long for a webpage to be loaded. Even if it offered the most useful and attractive content amongst the competition, the website would lose its visitors and search engine ranking positions.
We wanted not only to notify our customers for the high resource usage, but also to offer a solution to speed up the website.
So we conducted an in-depth research for the possible reasons leading to the slow loading speed. We analyzed and tested different solutions so that we could speed up the website and reduce the CPU resource usage.
All tests and checks were performed on a staging copy of the website. Results from the solution applied on this copy were presented to our customer before we actually implemented them on the real website.
- The website was using the SuperHosting plan which is suitable for an online store.
- The website is an online bookstore running the PrestaShop CMS platform version 1.6.
- The hosting plan parameters are sufficient to meet the demands of the traffic.
Increased MySQL high CPU usage appeared to be a common issue with version 1.6 of the PrestaShop platform.
This came as a consequence from the execution of a non-optimal SQL query with a big amount of data in only one database table.
To reach a solution for this case we performed multiple checks. We scrutinized xdebug and strace for possible causes of slow loading. We attempted different approaches to reduce queries to the database as we even tried caching mechanisms. Unfortunately, results were not good.
Meanwhile a slow SQL query drew our attention to the ps_configuration table:
# User@Host: cpuser_myshop[cpuser_myshop] @ localhost 
# Query_time: 3.273069 Lock_time: 0.000123 Rows_sent: 660005 Rows_examined: 660015
SELECT 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`);
The SQL query is part of the PrestaShop platform's features. The non-optimal execution is directly related to the fact that the ps_configuration database table of the online store contains a big amount of data.
We checked numerous records in this database table, one by one, to figure out why this table contained so much information as it normally had to contain only website configuration data.
We found many records related to abandoned carts as this information was not supposed to be stored in a configuration table. While we were analyzing the origin of this data we reached the conclusion that this table was hardcoded in one of the website's system files which had led to the result of it being full of unnecessary data. The fuller the table was getting, the slower queries became so the website was also getting slower.
The big amount of data in this specific table was directly influencing the loading speed of all webpages on the website. The reason was that upon many actions performed on the online store there were queries executed to the ps_configuration table.
After we finished analyzing different approaches towards improving the website’s loading speed we reached a comprehensive solution for this case.
To solve it we needed to apply three changes and optimizations to the website, its files and database.
Patching a system file
To fix the direct table setup in the source code we applied a small, but very important fix on the system file - /home/cpuser/public_html/classes/Configuration.php.
The fix was changing the source code from:
446: -$result &= Db::getInstance()->insert('configuration', $data, true);
446: +$result &= Db::getInstance()->insert(self::$definition['table'], $data, true);
This modification was approved by the PrestaShop developers with a user pull request as the user had also noticed a similar issue with his website.
Clearing unnecessary records from the ps_configuration table
After we prevented storing unnecessary information in the table we only needed to clear up the stored one.
From the preliminary check we noticed that much of the information was actually stored as duplicated records in the ps_configuration table. To view details about the number of duplicated records we executed the following SQL query:
SELECT count(*),name FROM ps_configuration group by name order by count(*) DESC
After we already knew which the unnecessary information was we cleared all unnecessary records from the table. This procedure reduced the database with over 200 MB. The table's size was 670 MB as we managed to reduce it to 420 MB.
The results were only positive for us, for our customer and their customers.
Speeding up the website
The most visible result from solving this case was the tangible increase of the website loading speed.
From the average of 5 seconds the loading time was reduced to average of 1 second. This was a good prerequisite for better user experience which helps for building up a reliable customer base. From then on visitors would not be queuing for books and can order them within the snap of a finger. We should also mention better search engine ranking for the website which will inevitably contribute to developing this business in a good direction.
The online bookshop’s homepage is now loaded in less than a second (~600 miliseconds) 👀 even with no caching in the browser activated
A product webpage is loaded for about 2 seconds and the webpage for category/search results is loaded for about a second.
Since we solved the issue some time ago we could now also track a change in the number of visitors on the website. For January 2019 it already has doubled the number of monthly visits.
Reduced CPU usage
The other result from solving this case is the reduced CPU usage of the hosting account. CPU minutes were reduced from 150-200 minutes per day to 25-50 minutes per day.
The most important result from solving this case which is the biggest prize for us is the following comment:
“I am very grateful for your active support. I read and changed so many things for improving the loading speed but you made a great gift to me!”
Besides the positive technical results we were really satisfied with the feedback and the feeling that we helped a customer in need as well as the gratitude we received because of our efforts.