Our admin pages have suddenly become very slow to load. I've checked the server usage and we're well below limits. Also the front end seems to be ok. Does anyone have any ideas?
Check what 3rd party addons you have that might be making remote calls to sites that may not be responding (like Twigmo).
Add "?debug" to the URL in the admin panel. Check SQL section in the debugger. May be you have some incorrect sql queries
I'm seeing this query in the SQL section but it takes almost a second to finish. Do you understand the logic behind running this type of crazy query to load admin panel page?
SELECT SQL_CALC_FOUND_ROWS products.product_id, descr1.product as product, companies.company as company_name, IF(COUNT(IF(cscart_categories.company_id = products.company_id, NULL, cscart_categories.company_id)), 'Y', 'N') as is_shared_product FROM cscart_products AS products LEFT JOIN cscart_product_options_inventory as inventory ON inventory.product_id = products.product_id AND inventory.amount <= 0 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 LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id WHERE 1 AND products.tracking IN('B', 'O') AND IF(products.tracking = 'O', inventory.amount <= 0, products.amount <= 0) AND prices.usergroup_id IN (0) GROUP BY products.product_id
It's really not that crazy a query if you break it down. Effectively it is getting a listing of products based on your company_id for those products that are unique to your storefront and that are shared between storefronts..
How many products do you have? This query should take a second.
Do you have adequate memory on your server to support the high demands that cs-cart places on mySQL?
Do you have fast disks (SSD) so that indexes are read quickly?
It takes 0.3 seconds just for this query for 10 products only. I have 3300 active products at the moment. I have only one storefront so not sure if it is even necessary.
The server has 8GB memory and Mysql has configuration to go up to 6.5 GB memory. The whole server hardware has been dedicated to one website. You hit the nail on SSD. HDD is the bottleneck for performance at the moment and I'm looking into getting a new dedicated server but even with a new server, I still can't get the idea behind to write a humongous query just to query 10 products. I looked into "EXPLAIN" section of phpMyAdmin, most of this query is using non-indexed sections of the tables, that's why it is filling up slow_query.log on Mysql. This query runs on the homepage as well and it takes around 0.35 seconds on average to query only 10 products.
It's really not that crazy a query if you break it down. Effectively it is getting a listing of products based on your company_id for those products that are unique to your storefront and that are shared between storefronts..
How many products do you have? This query should take a second.
Do you have adequate memory on your server to support the high demands that cs-cart places on mySQL?
Do you have fast disks (SSD) so that indexes are read quickly?
Tony, you were right. I upgraded our server with 256GB SSD hard drives. It is much much better now.
Thanks
Glad it worked for you.
In cs-cart, Memory is your best friend with fast disk a close 2nd place.