Problem with slow cscart version 3.0.4

General Web Hosting
1

Hello,



I am using cscart for two years and I have this problem from the beginning. Very slow site especially in categories,filters and when you search.



In the first I had a shared host,then I moved to a VPS and now the last month I moved to a dedicated with the following setup: 2 [color=#444444][font=Helvetica, Arial, sans-serif][size=4]Xeon E5520(4quad),12GB RAM, 2 SSD drives in RAID. This is a monster for me. It can handles 30 VPS. Do you believe that my site is a little faster than a shared plan?[/size][/font][/color]



[color=#444444][font=Helvetica, Arial, sans-serif][size=4]Here is my story to explain:[/size][/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif][size=4]-Traffic 2000 user per day(very low)[/size][/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif][size=4]-More than 8000 products online[/size][/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif][size=4]-Lot of features and some of them more than 200 (colors)[/size][/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif][size=4]-A lot of categories and sub-categories(150-200)[/size][/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif][size=4]-Already connected with MaxCDN[/size][/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif][size=4]-We have changed and tested all this config.php and htaccess mods[/size][/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif]-heavy custom template with custom addons[/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif]-disabled all addons which don't needed[/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif]-searchanize on[/font][/color]



[color=#444444][font=Helvetica, Arial, sans-serif][size=4]Same problem.Here is my Pingdom and Gtmetrix stats:[/size][/font][/color]

Pingdom tools:


Page size
1.5MB
Load time
4.29s
Requests
99

Perf. grade
88/100

Specify a Vary: Accept-Encoding header 66 Open
Leverage browser caching 68 Open
Serve static content from a cookieless domain 79 Open
Minimize redirects 94 Open
Remove query strings from static resources 98 Open
Specify a cache validator 98 Open
Avoid bad requests 100
Minimize request size 100




-Gtmetrix





Specify image dimensions F (0) 51% Images High
Combine images using CSS sprites F (27) 63% Images Medium
Remove unused CSS F (17.4) 63% CSS Low
Specify a Vary: Accept-Encoding header D (69) 90% Server High
Enable gzip compression C (72) 79% Server High
Leverage browser caching C (72) 53% Server High
Minify JavaScript C (75) 89% JS High
Use efficient CSS selectors F (0) 21% CSS Low



The people who build my site have made more than 50 cscart stores. And we are in dead end here. We think that the problem is the many features we have in the products. Also we found that this query:

[quote]SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(prices.price) as price, descr1.short_description, IF(descr1.short_description = '', descr1.full_description, '') as full_description, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, products_categories.position, cscart_seo_names.name as seo_name FROM cscart_products as products LEFT JOIN cscart_product_features_values ON cscart_product_features_values.product_id = products.product_id AND cscart_product_features_values.lang_code = 'EL' LEFT JOIN (SELECT product_id, GROUP_CONCAT(cscart_product_features_values.variant_id) AS simple_variants FROM cscart_product_features_values WHERE lang_code = 'EL' GROUP BY product_id) AS pfv_simple ON pfv_simple.product_id = products.product_id LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'EL' LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = 'p' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'EL' WHERE 1 AND FIND_IN_SET('2073', simple_variants) AND products.company_id = 0 AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) GROUP BY products.product_id ORDER BY products_categories.position asc LIMIT 592, 16[/quote]



does [color=#444444][font=Helvetica, Arial, sans-serif][size=4]a lot of joins without indexes. This is really slow.[/size][/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif]Our server administrator (we have managed plan) says that we must go nginx. That the problem is the apache server. This is my last chance. But I don't know why to make my life so hard? I don't have the traffic for nginx.[/font][/color]





[color=#444444][font=Helvetica, Arial, sans-serif]Can anyone please help me?[/font][/color]

[color=#444444][font=Helvetica, Arial, sans-serif]All opinions would help![/font][/color]



[color=#444444][font=Helvetica, Arial, sans-serif]Thank you very much[/font][/color]

2


Specify a Vary: Accept-Encoding header D (69) 90% Server High
Enable gzip compression C (72) 79% Server High
Leverage browser caching C (72) 53% Server High
Minify JavaScript C (75) 89% JS High


Have a look at the post in my signature, this will resolve those issues for you. 99 http requests is a lot though and your page size of 1.5mb is fairly big too. Have you optimised all your images for the web?



What cache type are you using, as specified in config.local.php?

3

12GB RAM??? I guess it's a wrong VPS configuration. We have CS-Cart VPS customers with e.g. 1500MB RAM, very “large” sites and they work without any issues.

4

[quote name='martfox' timestamp='1359921162' post='154368']

12GB RAM??? I guess it's a wrong VPS configuration. We have CS-Cart VPS customers with e.g. 1500MB RAM, very “large” sites and they work without any issues.

[/quote]

It's not a VPS. It is dedicated server. 12GB memory works 6GB mirroring

5

[quote name='StellarBytes' timestamp='1359920953' post='154367']


Specify a Vary: Accept-Encoding header D (69) 90% Server High
Enable gzip compression C (72) 79% Server High
Leverage browser caching C (72) 53% Server High
Minify JavaScript C (75) 89% JS High


Have a look at the post in my signature, this will resolve those issues for you. 99 http requests is a lot though and your page size of 1.5mb is fairly big too. Have you optimised all your images for the web?



What cache type are you using, as specified in config.local.php?

[/quote]



Yes I have optimised my images. Also all images connected with MAXCDN.

Here is my config.local.php:

```php

/***************************************************************************
* *
* (c) 2004 Vladimir V. Kalynyak, Alexey V. Vinokurov, Ilya M. Shalnev *
* *
* This is commercial software, only users who have purchased a valid *
* license and accept to the terms of the License Agreement can install *
* and use this program. *
* *
****************************************************************************
* PLEASE READ THE FULL TEXT OF THE SOFTWARE LICENSE AGREEMENT IN THE *
* "copyright.txt" FILE PROVIDED WITH THIS DISTRIBUTION PACKAGE. *
****************************************************************************/

if ( !defined('AREA') ) { die('Access denied'); }
/*
* PHP options
*/
// Disable notices displaying
error_reporting(E_ALL ^ E_NOTICE);
if (version_compare(PHP_VERSION, '5.3.0', '>=')) {
error_reporting(error_reporting() & ~E_DEPRECATED & ~E_STRICT);
}
// Set maximum memory limit
@ini_set('memory_limit', '512M');
// Set maximum time limit for script execution
@set_time_limit(10800);
/*
* Database connection options
*/

$config['db_type'] = 'mysqli';
/*
* Script location options
*
* Example:
* Your url is http://www.yourcompany.com/store/cart
* $config['http_host'] = 'www.yourcompany.com';
* $config['http_path'] = '/store/cart';
*
* Your secure url is https://secure.yourcompany.com/secure_dir/cart
* $config['https_host'] = 'secure.yourcompany.com';
* $config['https_path'] = '/secure_dir/cart';
*
*/
// Host and directory where software is installed on no-secure server
$config['http_host'] = 'www.wallpapershop.gr';
$config['http_path'] = '';
// Host and directory where software is installed on secure server
$config['https_host'] = 'www.wallpapershop.gr';
$config['https_path'] = '';
/*
* Misc options
*/
// Names of index files for administrative and customer areas
;
$config['customer_index'] = 'index.php';
$config['vendor_index'] = 'vendor.php';
// DEMO mode
$config['demo_mode'] = false;
// Tweaks
$config['tweaks'] = array (
'js_compression' => false, // enables compession to reduce size of javascript files
'check_templates' => false, // disables templates checking to improve template engine speed
'inline_compilation' => true, // compiles nested templates in one file
'anti_csrf' => false, // protect forms from CSRF attacks
'disable_block_cache' => false, // used to disable block cache
'join_css' => false, // is used to unite css files into one file
'allow_php_in_templates' => false, // Allow to use {php} tags in templates
'disable_localizations' => true, // Disable Localizations functionality
'disable_google_base' => true, //Disable obsolete google base functionality
);
// Cache backend
// Available backends: file, sqlite, mysql
// To use sqlite cache the "sqlite3" PHP module should be installed
$config['cache_backend'] = 'file';
// Key for sensitive data encryption
$config['crypt_key'] = 'YOURVERYSECRETKEY2';
// Database tables prefix
define('TABLE_PREFIX', 'cscart_');
// Default permissions for newly created files and directories
define('DEFAULT_FILE_PERMISSIONS', 0644);
define('DEFAULT_DIR_PERMISSIONS', 0755);
// Maximum number of files, stored in directory. You may change this parameter straight after a store was installed. And you must not change it when the store has been populated with products already.
define('MAX_FILES_IN_DIR', 1000);
// Developer configuration file
if (file_exists(DIR_ROOT . '/local_conf.php')) {
include(DIR_ROOT . '/local_conf.php');
}

?>

```

6

Bad idea to publish your database user/password and admin url in a public forum. You might want to edit.



Not sure why you say your Joins are not indexed. The vast majority use either product_id or lang_code for conditions and product_id is always an indexed column and lang_code is too.



Why does your admin think it's an apache issue? Apache is quite capable of handling the traffic you have on a single server without issue. However, what PHP model are you using? DSO is fastest but sometimes can have security concerns. suPHP is more secure and is what I'd recommend. and fastCGI is out there but there are far too many problems with it unless you have a really good apache admin who can monitor and tweak on an ongoing basis.



Depending on what constitues your 1.5MB page size, that is probably your issue. Your CDN could also be your issue depending on where it's actually being served from (physically) and the frequency at which that particular service site is serving your imagery.

7

cache_backend = file is the slowest method. If sqlite is enabled on your server (you can check in Administration>Database>PHP Information), sqlite is generally the best option.



Shmem would be better if your server has the shmop php module loaded, however, I believe this method has been removed from V3.0.3 onwards. I note you are on 3.0.4 so shmem wouldn't be of any use to you, although I am currently trying to get CS-Cart to resolve these issues with shmem.



Change your cache_backend type to sqlite if you can, or ask your server administrators to enable the sqlite3 module so you can, set js_compression and join_css to true and optimise your homepage banner images, which given the compression and file size reductions, should speed things up a lot.

8

They have improved the file method a lot in recent versions (multiple directories with multiple files). If you have a dedicated server the data might remain in the server's file cache versus actually going to disk. But it will complete with mySQL since it uses the file system on the block device for tables and indexes… Dedicating more memory (percentage) to the file cache will help in many areas of your system.



SQLITE seems to work on some sites well and on others there tends to be locking problems. I do not know the cause. But you can get good info using iostat and/or vmstat on the server to identify whether your are blocking waiting on file IO or whether it is network, memory or cpu.



Linux/Unix internals is a highly specialized area. If you really want to tune your system, then hire someone who specializes in this area and can research the problem, give you the data indicating the problem and then tell you there plan for resolving BEFORE they start resolving. These resources are not cheap, but pay for themselves many times over if you have a dedicated server focused at your ecommerce solution (cs-cart). There's only so much you can do from the application (cs-cart). The rest relies on the underlying server configuration (software and hardware).

9

[quote name='tbirnseth' timestamp='1359922833' post='154374']

Bad idea to publish your database user/password and admin url in a public forum. You might want to edit.[/quote]

You are right…I just paste the contents fast…I fixed it.


[quote]Why does your admin think it's an apache issue? Apache is quite capable of handling the traffic you have on a single server without issue. However, what PHP model are you using? DSO is fastest but sometimes can have security concerns. suPHP is more secure and is what I'd recommend. and fastCGI is out there but there are far too many problems with it unless you have a really good apache admin who can monitor and tweak on an ongoing basis.[/quote]

I don't know…probably because we can't find a sollution. We also tried Litespeed. A little faster. Where do I find PHP model?



I am the owner of the domain and I don't have much knowledge. I started this because I don't what to do and I am losing money here. Customers complain

10

[quote name='StellarBytes' timestamp='1359922999' post='154375']

cache_backend = file is the slowest method. If sqlite is enabled on your server (you can check in Administration>Database>PHP Information), sqlite is generally the best option.[/quote]



It is enabled.




[quote]Change your cache_backend type to sqlite if you can, or ask your server administrators to enable the sqlite3 module so you can, set js_compression and join_css to true and optimise your homepage banner images, which given the compression and file size reductions, should speed things up a lot.[/quote]



Sqlite3 is on. Also when I set join_css the site don't work

11

The “out of the box” cs-cart configuration should work just fine for the vast majority of merchant stores.

If you are experiencing performance issues I'd suggest you back out some of your “performance enhancements” like CDN, various optimizers, css consolidation, js compression, etc.



Then see what you have.

I would then turn these on one at a time and see where you get bang for the buck. NOTHING EVER COMES FOR FREE. You ALWAYS trade something off (cpu speed, memory, etc) for ANY performance improvement. The whole trick to performance tuning is ensuring you are making maximum use of ALL available resources. When you tune, you simply shift them around.



You might just find that some “optimizations” do nothing but slow things down for your average user. Their overhead outweighs their benefit. So be careful and be smart. If you don't know what you're doing and why, then don't do it.

12

[quote name='tbirnseth' timestamp='1359957229' post='154407']

The “out of the box” cs-cart configuration should work just fine for the vast majority of merchant stores.

If you are experiencing performance issues I'd suggest you back out some of your “performance enhancements” like CDN, various optimizers, css consolidation, js compression, etc.



Then see what you have.

I would then turn these on one at a time and see where you get bang for the buck. NOTHING EVER COMES FOR FREE. You ALWAYS trade something off (cpu speed, memory, etc) for ANY performance improvement. The whole trick to performance tuning is ensuring you are making maximum use of ALL available resources. When you tune, you simply shift them around.



You might just find that some “optimizations” do nothing but slow things down for your average user. Their overhead outweighs their benefit. So be careful and be smart. If you don't know what you're doing and why, then don't do it.

[/quote]



Thank you!

13

Btw, has anyone figured out why the option to choose “Shared Memory” as the cache method is no longer shown as an option in 3.0.5 within the config.local.php ?

14

@struck - that would be a helpdesk question, but I'm sure people would be interested in the answer.

15

I have just had it confirmed why shmem has been removed:

[quote]

I should inform you that the shmem caching method was disabled in CS-Cart v.3.0. Our engineers removed it, because this type of caching did not show high level of site performance.

[/quote]

It is also worth noting that using the 'shmop' module for 'shmem' caching in CS-Cart V3 causes unexpected behaviour, such as random 'Store Closing'. The admin displays the 'Close Storefront' link but the storefront is already closed and can only be re-opened by toggling Close Storefront/Open Storefront button.

16

[quote name='StellarBytes' timestamp='1360072347' post='154505']

I have just had it confirmed why shmem has been removed:



It is also worth noting that using the 'shmop' module for 'shmem' caching in CS-Cart V3 causes unexpected behaviour, such as random 'Store Closing'. The admin displays the 'Close Storefront' link but the storefront is already closed and can only be re-opened by toggling Close Storefront/Open Storefront button.

[/quote]



Thanks for the update Stellar,



Well, I switched back & forth between shmem & sqlite several times over the last year and at least with our site, the performance difference was a toss-up with sqlite being our slightly preferred method in the end. I do hate to see the shmem option completely removed though.

17

I've run a few analysis on the Ultimate site I installed shmop for. Ran the site for a week with sqlite and a week with shmem, to be honest I haven't really noticed much in terms of anything slowing down or any bottlenecks getting reached. Shmem never seemed to fall over on an old server we still have a 2.2.4 store running on, but every once in a while get the dreaded I/O errors with SQLite with v2 sites but not v3. For now, I'll stick with SQLite for V3 with MySQLi database. Seems to get the best out of the hardware from what I've seen with less maintenance to do.

18

Hello again,



Here is what we did and we decrease the slow queries to 2s:

We installed percona and also tweaked nginx config. We still see slow queries, but I think they're roughly 4 times faster than before.



This increased also first page load from 4.5s to 2.5s .

19

HELLO IOANNISG

i tell you answer. ---------- other people answer manythings allll is correct.



90% you can solve problem with SSD. ssd is make you solve problem and need 8gb ram.



this can be solve your issue.



and… about queries -------------i will ask my db manger for you.





---------10 % of rest things is for cache system. cs-cart is complex to call page.



forexample easy) A INDEX FILE LOAD (we can make it easy as cache) so very fast.



but cs-cart always calls some of TPL and include other file and so on other file include…



like this structure to make very hard cache.



so---------firstpage (first open) is slow usally ,then if you click F5 will be fast.



if you have a lot product NAVER USE SQLITE this is for small DB. like for android etc.

20

Stellar,


[quote][color=#282828][font=arial, verdana, tahoma, sans-serif]For now, I'll stick with SQLite for V3 with MySQLi database. Seems to get the best out of the hardware from what I've seen with less maintenance to do.[/font][/color][/quote]



Same conclusion I have also come to accept.



I believe the issue with the SQLITE error messages was fixed in 2.2.5 onward.



I believe Tbirnseth mentioned awhile back that the “File” cache method has also been improved for 3.xx versions, and I have also noticed a major performance improvement vs past experience using the Files method while experimenting in 3.0.5