How to speed up CS-Cart - Server details

 Usually, we meet 3 variants of web servers:

• Apache + mod_php (the old and widespread type)

• Nginx + php-fpm

• Nginx + Apache + mod_php

According to W3Tech.com, as of January 14, 2019, Apache (44.4%) is just slightly ahead of Nginx (40.9%) in terms of websites using their servers. Between them, they dominate nearly 85% of the web server market.

Let's talk about the differences between Apache and Nginx. How do you think what is faster in work? Yes, Nginx is faster in certain circumstances. Apache uses up server memory, while Nginx (best used for static files) needs the help of php-fpm to process dynamic content.

Taking into account all aspects of 3 variants of web servers and our experience, we came to the conclusion that Nginx + php-fpm works faster of all, but it requires setting up the config files. 

 You can use Nginx + Apache + mod_php; it will be a bit slower, but it supports .htaccess file. 

In case you have Apache+mod_php and you want to raise the CS-Cart performance, should you move to Nginx server? To tell the truth, you are supposed to see the results only if:

• you have a frequently used store (more than 5 people simultaneously)

• there is much static contents on pages

• you are not happy with Google Page Speed Insights results

If the CS-Cart store has few visitors a day, you won't see any results at all.

So, our advice is to use Nginx + php-fpm on CS-Cart projects.

What about PHP?

In short, use PHP 7 + module OPCache!

By default, it uses the OPCache module, but sometimes it can be disabled! Check it. 

OPCache improves PHP speed by storing precompiled script bytecode in shared memory, thereby removing the need for PHP to load and parse scripts on each request. In other words, a PHP script, when executed, is compiled into opcode, code that is understandable by the machine.

Attention!Only CS-Cart 4.9. and higher supports PHP 7.3. 

You should be aware that PHP is not in itself a critical place for CS-Cart page load speed issues. Yes, PHP 7 is faster than PHP 5, but it has not had the same impact on CS-Cart performance as MySQL queries.

So, we get close to the most important part of How to Speed Up CS-Cart: MySQL Queries!

Usually, the CS-Cart store works very quickly right after the installation. The first problems with the page loading speed occur when the admin uploads the products on store. Over time, the CS-Cart speed of page loading decreases dramatically, and you have to find a way to fix it.

Why does the CS-Cart speed fall when you use the system?

1. Too complex a query

   

   To tell the truth, it is a very bad query! Indexes are not used in every part of the query. Look at the FIND IN SET part and the OR operator! You should use OR very carefully. It can create several queries. So you can't create an index in this query.

2. The function SQL_CALC_FOUND_ROWS is used here. The query was executed, ignoring the LIMIT.

3. It leads to locks in certain circumstances. There are two types of locks: read locks and write locks.

So, how do I speed up CS-Cart  by fixing the MySQL queries and blocks?

MySQL queries.

The treatment of root causes.

1. The best solution is Product Indexing. The same method is presented on the Magento platform. So storefront data retrieval goes with the help of accumulating data into special tables using indexers.

2. The compromise variant facilitates the final query. It can help make CS-Cart a bit faster.

The treatment of symptoms.

1. The increases in buffer size in MySQL configuration (join_buffer_size, etc.) in conjunction with RAM upgrades.

2. The product blocks caching and on-page cache.

MySQL blocks.

Blocks are divided into two types: read lock and write lock.

1. If user A reads the data, other users can read it, but they cannot write it until user A finishes the reading process (read lock). 2. If user A writes data, other users can not read or write data until user A finishes the writing process (write lock). The locks can be on the table level (the whole table in the database is blocked) or on the row level (certain rows in the database are blocked).

By default, all of CS-Cart uses the MyISAM storage engine for the MySQL tables. This storage engine is old, and stable, but it has one great disadvantage: it uses the blockage of the whole table. To fix this problem, another storage engine was invented: InnoDB. InnoDB uses a more advanced algorithm for locking. It blocks only the used rows in the table, not the whole table.

The main CS-Cart problem is that it uses MyISAM by default, and CS-Cart suffers from locks very often.

The samples of table locks in CS-Cart

1. The first client opens the category by sorting by popularity. So the table Product Popularity is locked on reading. Another client wants to open the product page, but he has to wait for the first customer.

2. Clients always block the tables on sessions, logs, orders, and statistics in the database on write lock.

3. The backup process leads to longstanding read locks on tables.

4. The synchronization with third-party systems creates a write lock on product tables.

The treatment of root causes.

1. Convert the MyISAM tables into InnoDB or ExtraDB (a fork of InnoDB by Percona). The fork is better to use on multi-server projects. Usually, InnoDB is enough for the work. Don't forget to edit the config files in MySQL due to RAM.

The treatment of symptoms.

• Any caching process can help the situation, but it's not the remedy.

My SQL config file

What can be changed?

1. Adapt the settings due to RAM. This is particularly true for buffer values. By default, the MySQL config file in CS-Cart is intended to use only 800 MB of random access memory. But if you use an expensive hosting plan, you will definitely have more random-access memory on your server. So, you need to enter the necessary settings into config files to make the most of random access memory.

2. You can activate the cache of queries, but you should be very careful. We don't recommend doing it.

How do I change the config file?

1. Pay attention to the utility MySQLtuner  https://github.com/major/MySQLTuner-perl

2. It's a great program. You launch it, and the utility gives you full information about your server problems due to the statistical data it gathers.

3. Watch the main aspects of config file settings by Percona slides https://www.percona.com/resources/technical-presentations/optimizing-mysql-configuration-mysql-56-percona-technical-webinars

When should you change MySQL's config file?

1. when you have a powerful server and you haven't made any settings yet.

2. In case you moved to InnoDB.

About the caching!

• We don't recommend caching at the database level!

• You may do this at the CS-Cart level (block, intermediate data). You may move it to Redis.

• Full-page caching. See the CSCart ready-to-use solution Cache for users.

Extra software

• Use Redis for caches and sessions. Redis is a data caching technology. It caches data in RAM, so it's quickly accessed.

• You may use Sphinx for search optimization on the website, but full-text search in MySQL is not worse for the majority of products.

• Use Varnish cache for the Full page cache.

How can we test CS-Cart for speed issues?

• Use admin.php&?debug

• loaddy.com