Our cart is running slow and timing out on all backend functions that insert data into the database. Our WH tells us this is a cs-cart problem and cs-cart tells us we this is a Host problem.
We are unable to add products. When you click on search or anything that has an ajax pop-up the system hangs.
WE GET THESE MESSAGES EVERY DAY FROM OUR SERVER:
The file system /usr/tmpDSK, which is mounted at /tmp, has reached critical status because it is 100% full.
The host tells us that the disk tmo/ size is already over what is a normal for most accounts and that CS-CART is jamming mysql with too large and too many requests and that is the cause.
CSCART tells us to make the disk size larger … we have to wait 24 hours for each ticket we put in and their response to the problem is only two or three lines that basically says they will do nothing. I checked their ip and they spent a total of 2.36 seconds on our site.
I should have gone with another cart … If anyone can fix this, or point me to someone who can I will send money via paypal.
We'd like to help look into the problem. Please feel free to contact us at CS-Cart Templates - CS-Cart Skins - CS-Cart Addons - CS-Cart Mods
Just a thought, but you will probably receive more timely and concise responses if you post your hosting environment (shard, vps, dedicated) server specs, cs-cart build and version and have an error log or stat log available for someone to review without having to ask 20- questions.
Other important questions to be answered"
Has this always been happening, or just start?
Agree with Jeremy, but i'm pretty sure this is a hosting problem, either because they are not helping you or because the configuration is all wrong.
Almost bound to be a hosting problem. Who are you hosting with?
You are running out of resources (SQL temp space) and I would assume that you also have memory restrictions in place and that you are trying to run an ecommerce site in a shared hosting environment. While this CAN work, it is not recommended since you are then at the mercy of what the hosting provider decides to load your server with.
But as other's have said, a description of your hosting environment, cs-cart version and any other info like number of products, categories, filters, etc. would be helpful in helping you identify the source of the problem.
Note that if you are running standard cs-cart there hare hundreds of users here who successfully do so too. Hence the UNCOMMON DENOMINATOR would be your hosting environment.
I hired an expert in Mysql to analyze the problem and this is what I got from him:
To adequately analyze this I do have to start with one assumption - the load/slowdown is coming from MySQL. I don't have that verified by an external source - if you want that first, I can get that, but (1) I have to install a script and then it'll come in after a few days (that way we have a good sample).
I can tell you that I've recently seen another cscart install on another server that was similarly causing huge problems, however I can't divulge any specifics - I've not seen cscart before, but so far my first two impressions have been negative.
All in all, it's not a bad idea to add the script - but I am also comfortable moving on and blaming it on cscart for the time being.
That aside, for starters, luckily on your server, we do have a MySQL slow query log. Even better, I see that the first entry in it is from 140327 1:01:10 - or 2014-27-03 01:01:10 EDT if you prefer ISO 8601 (I do).
So, the slow query log has only been logging for about a month - everything we will be looking at will be valid. The slow query time is set to 5 seconds - any query that runs longer than 5 seconds is logged - and that's a bit high, (2) I'd like to drop that down so we get some better logging in the future. But, we'll touch on that later too.
Moving forward from there, since we are assuming this is a problem with MySQL (at least for the time being), I'm going to start by checking the size of your databases. Generally it's a good idea to try to cache all of your databases files in RAM if you can, or cache at least some of them. With that in mind, I see about 1.2G of MyISAM usage, and about 1G of InnoDB usage across your server.
More specifically, in bytes, I see the following:
find /var/lib/mysql/ -type f -printf “%s %f\n”|awk -F'[ ,.]' '{print $1, $NF}'|sort -k2|awk '{array[$2]+=$1} END {for (i in array) {print array[i]
1206025677 MYD
28372136 frm
806 RPM_UPGRADE_HISTORY
4017 opt
6 mysql_upgrade_info
5242880 ib_logfile0
0 CSV
5242880 ib_logfile1
1040007168 ibd
931550 err
5 pid
659017728 MYI
70 CSM
27262976 ibdata1
806 RPM_UPGRADE_MARKER-LAST
76875059 log
Your innodb_buffer_pool_size is set to 2G - enough to hold all of InnoDB which is good. However, (3) your key_cache_size is only set to 512M, and should be about doubled or a bit more. I would like to make that change - it will likely have a minor impact because of some issues I'll point out later, but every little bit helps.
Next, for the main issue on your server, this is actually a bit more specific. If you download the file /root/jack.pt-query-digest.sql from your server and open it on your computer, it may be easier to read that way - but it includes all of the ugly queries that MySQL has logged into it's slow query log. I'm just going to focus on the first one.
The query itself looks like:
SELECT SQL_CALC_FOUND_ROWS products., descr1.product as product, MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price, descr1.short_description, IF(descr1.short_description = '', descr1.full_description, '') as full_description, companies.company as company_name, 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 = 'EN' LEFT JOIN cscart_product_features_values as var_val_18_1 ON var_val_18_1.product_id = products.product_id AND var_val_18_1.lang_code = 'EN' LEFT JOIN cscart_product_features_values as var_val_18_4 ON var_val_18_4.product_id = products.product_id AND var_val_18_4.lang_code = 'EN' LEFT JOIN cscart_product_options_inventory as inventory ON inventory.product_id = products.product_id LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'EN' LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id 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 = 'EN' WHERE 1 AND ((var_val_18_1.value_int >= 0 AND var_val_18_1.value_int <= 35000 AND var_val_18_1.value = '' AND var_val_18_1.feature_id = 195) OR (var_val_18_4.value_int >= 250001 AND var_val_18_4.value_int <= 500000 AND var_val_18_4.value = '' AND var_val_18_4.feature_id = 195)) AND cscart_categories.category_id IN (254, 300, 299, 340, 338, 339, 381, 343, 344, 281, 276, 277, 278, 382, 280, 275, 274, 267, 268, 269/… omitted 68 items …*/) AND (companies.status = 'A' OR products.company_id = 0) AND IF(products.tracking = 'O', inventory.amount > 0, products.amount > 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 price asc LIMIT 0, 16\G
The tool I used to pull it apart actually eventually did some stuff to shorten it because it could and it thought it was too long. If you want to see the execution path for this query or the table information for the tables in the query, they are there to run - but it's actually not necessary.
The query is already indexed as best as it can be, the tables done correctly - and the query is still having problems. Specifically, that query is put first because your server, out of all the queries logged in that slow query log, has spent the most time on that one.
Here's a nice chart of how it breaks down (it's much better in the text file, the rows actually match up).
Attribute pct total min max avg 95% stddev median
============ === ======= ======= ======= ======= ======= ======= =======
Count 7 1470
Exec time 9 63904s 7s 479s 43s 60s 22s 40s
Lock time 2 284s 0 171s 193ms 332us 5s 247us
Rows sent 0 28.79k 0 64 20.05 62.76 14.72 13.83
Rows examine 27 28.52G 0 20.57M 19.87M 20.30M 1.81M 20.30M
Query size 6 4.45M 2.68k 3.10k 3.10k 3.04k 11.04 3.04k
The query was run 1470 times over the lifetime of that slow query logfile, with an average time of 43 seconds, making a total time spent on that query of 17.75 hours. Queries should finish in thousandths of a second (or less really) - that's MUCH too long.
There was at least one time that the query ran for 479 seconds even (in the table) and it locked everything else out from all of the tables involved once for almost 3 minutes - 171 seconds. (4) That's a bad query.
The reason why this query is a bad query is simply the amount of time the database spends running different logic within MySQL. Generally logic in MySQL is a bit slower than outside - they are primarily a datastore engine, and not a coding language.
More importantly, if you are doing your calculations in MySQL, you're also holding locks on tables/rows/etc while doing that work, and other queries can't run at the same time - so they back up in a queue, and eventually run.
We can (5) put MySQL's tmpdir into RAM - or rather on a ramdisk - which may help speed this up a bit. It will make on disk sorts happen in RAM, which can speed up the sorting a bit, but I honestly don't think that's really your bottleneck. You have RAM to spare - I can dedicate quite a bit of it to MySQL to do sorts in RAM. I do know that I tried that one time before with CS_cart and watched CS_Cart eat up 100G of space while sorting, but I'm still willing to give it a try.
I know CS_Cart is probably not going to be a fan of our hosting right now. But, this is a problem with the application all in all. I would be happy to help if the problem wasn't so blatant - but the primary issue you're having here is with the way CS_Cart is trying to do things, and they're terrible at it. So, to recap through my number list that I've been making:
1. I'd like to install loadwatch - a script that would give me a general picture of what's going on with your server. It's not necessary for me to point fingers right now, but it can't hurt.
2. I'd like drop MySQL's slow query time to 1 second. 5 seconds is too long, and it'll get us better logging.
3. I would like to raise your key_cache_size to 2G. It's not the current bottleneck you're hitting, but I want to remove everything I can, and improve things as much as possible.
4. There are a LIST of bad queries - the file /root/jack.pt-query-digest.sql has the goods, you should take a look at it, and frankly, CS-Cart needs to fix some things (or whatever is causing the problems). It's not one query - since the time was 5 seconds, every query on that list is a black mark.
5. In the meantime, as a stop gap to try to help all that I can, I would like to try adjusting your tmpdir for MySQL, and put it in a ramdisk instead of on the disk.
Please let me know if this does make sense. I've jumped through some of the nitty gritty details rather quickly here, and hit on a few high points - I can explain anything further, but the one point it all boils down to is that the code needs some work.
Thanks
Jack Hayhurst
[quote name='requincreative' timestamp='1398454641' post='182368']
Just a thought, but you will probably receive more timely and concise responses if you post your hosting environment (shard, vps, dedicated) server specs, cs-cart build and version and have an error log or stat log available for someone to review without having to ask 20- questions.
Other important questions to be answered"
Has this always been happening, or just start?
[/quote]
I am on a dedicated linux/ Centos server … My cart version is the latest 3.xxx I actually hired someone from the hosting company to dive into this problem in detail. I am a marketing guy, so I get the gist of what he is saying but I am still at a loss of what to do. The advice he is giving me sounds like we can put bandaids on the issue, but to really fix the problem we need to fix how cs-cart is behaving …
It's your hosting environment.
/tmp/ is where mysql writes your temporary tables, database is too big for this area.
Get it increased as most operating systems have hard limits.
Or, use my.cnf to define a different directory for mySQL tmp files (if not on a shared server).
Actually, the detail from your mySQL person is excellent. The trouble is that he is looking at things from a strictly mySQL point of view.
CS-cart is more like a “platform” that is there to serve a variety of needs. I.e. it's generalized to support a wide variety of configurations and data (as well as addon extensions). So as your expert identifies, there is a lot of business logic in the queries versus refining the business logic at a higher level and then generating a more optimized query. For example, when returning a list of products the query must account for usergroup access, categories, product filters and also derive the correct price based from the base price, usergroup discount, option modifiers, etc.
If you decide that you want to stay on your V3.0.6 version then the queries could be rewritten to be optimal for your environment. But then this would have to be redone if/when you ever upgraded to V4.
Please keep us informed of progress you make. However cs-cart developers are pretty good at performance analysis and optimization but (as stated above) they need to account for all the features/functionality that can be in place versus something optimized to a specific site.
tbirnseth
Thank you so much for taking the time to look at my situation. As far as CS-CART 4 I would upgrade today if I could. However my problem is that we are running a dozen other websites on various scripts, and one phpcake build, that are heavily modded, that will be adversely affected by upgrading the server to php 5.3. I realize that eventually we will have to cross that bridge. However, the developer that we used to build these mods has moved on (he got a job running Hyatt hotels website) and rewriting these scripts simply isn't in the budget. Our current project has yet to be profitable, as soon as it is we will get that work done. Here is what my guy wrote back to me:
[font=courier new,courier,monospace]"I've made the 4 changes I mentioned before - the two changes to get us some more information, and the 2 changes to cache some more things in MySQL and set up a tmpdir in ramdisk. These changes seem to have helped.
However, do not let yourself be fooled. These changes helped - but we are combating this problem with an onslaught of RAM and a patched fix. We put a piece of gum in the hole in the dam. The code itself (the hole) still needs to be fixed - and if too much hits this at the same time, things will till give way.[/font]
Can your company step in and look at our situation and give us a second opinion? Maybe we can hire you on an ongoing basis to keep the ship running? We are about to make a heavy investment in marketing and we cannot afford for the website be sporadic as it currently is. Please email me at admin@aunicamedia.com and we can schedule a time to talk.
I'm not sure of your exact setup, but apache has the ability to run a different instance (version) of php on a site by site basis. Should be easy for your host to setup for you.
I'm happy to work with you on development projects but tend to shy away from admin stuff. Takes to much time and people not usually willing to pay for what it takes. You can contact me via pm or email or just click the link in my signature for requesting a quote.