We now have about 2 Million products on our site and i the load time for the Admin UI is taking longer and longer. After looking at what was going on, the query below was showing up. The issue here is this is actually the WRONG way to get a "Count" of products.
What is happening is the database is marshalling up the requested "1" since that is all that is being requested. Problem:: It is getting back almost 2 Million of these !
This isn't too bad on a small system but the reality is the query is just flat out the wrong way to go after the number of products in the system.
Anyone else seeing this problem or the need to fix this query in the core application?
SELECT SQL_CALC_FOUND_ROWS 1 FROM cscart_products AS products
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
WHERE 1 AND products.product_id NOT IN (183291, 183292, 183293, 183294, 0)
AND products.status IN (‘A’) AND prices.usergroup_id IN (0)
AND products.product_type IN (‘P’, ‘C’)
GROUP BY products.product_id
It seems to only be WRONG when you have multiple JOIN's. My guess is that the INNER JOINS are taking a lot of time to resolve in the query. A lot is going to depend on indexing. You might try having the category_id column be a separate index all on its own in cscart_products_categories. Right now it's part of the primary key. If it were separated, it might greatly speed up your search.
Be interesting for you to time the above query and then replace the 'SQL_CALC_FOUND_ROWS 1' with 'count(*)' to see which is fastest in your environment. If you find the count(*) to be faster, you will probably have to edit core files to adjust for the change. I show 16 instances where SQL_FOUND_CALC_ROWS is used within the app directory.
The problem isn't so much the joins -- but rather what is being requested. If you actually look at the query (and even run it) it is essentially the following:
SELECT '1' FROM cscart_products AS products .....
The "intent" of the query was to get a COUNT (which is why it uses the SQL_CALC_FOUND_ROWS) but by executing the query, it just returns (in our case) almost 2M rows of "1's". Then, the code just uses the final Found Rows value. This is REALLy bad because it actually streams the entire 2M rows back to the client. This also takes time for the database to gather up all of the 1's and send them to the client.
I really don't like any of the SQL_CALC_FOUND_ROWS but understand why it is used -- in some instances, but this one is just flat out written wrong -- and really is a DEFECT that needs a patch.
You can always use a hook to rewrite the query if you want (2 hooks actually, the first to prevent the query from running [or a quick query returning no results] and a second (post) to have it do it how you want). SQL_CALC_FIND_ROWS is generally used when returning valid data where you want a full count of rows that are not affected by LIMIT. I.e. to calculate page of data.
Pretty sure that will need to be what is done -- looking at the query, i know the SQL_CALC_FOUND_ROWS is used for the page totals, but this case is strictly just a count (from what i can tell) that is run secondarily.
I will say things are getting long running when there are a lot of rows in the database, especially when there are many joins. We are at about 2M records with pricing models for different usergroups so the joins are getting large.
Pretty sure that will need to be what is done -- looking at the query, i know the SQL_CALC_FOUND_ROWS is used for the page totals, but this case is strictly just a count (from what i can tell) that is run secondarily.
I will say things are getting long running when there are a lot of rows in the database, especially when there are many joins. We are at about 2M records with pricing models for different usergroups so the joins are getting large.
Good times.....
Wondering if you are willing share some knowledge on how you got to 2 mil products without reveling propriety information if any...
I could definitely use some pointers. Thanks in advance.
Wondering if you are willing share some knowledge on how you got to 2 mil products
There is nothing impossible with PHP and mySQL. In 2002, I managed 2 mln entries in one DB only on a free PHP-Nuke CMS. Plus three copies of Wikipedia on the same server (another 3 mln entries). And there were no lag-behinds.
We have built up our product base using many vendors - and this has not always been easy as well.
I know we can ultimately get almost any system to perform with millioins of records, i've done that before in years gone by. The issue we have is working to stay in the boundaries of the main cart platform without the need to completely diverge from the baseline. We are slowly having custom add-ons built and doing other things with configuration and layouts that reduce some of the "extra" features that cause tremendous drag on the system when there are a large number of products, prices, categories, and features.
Right now i'm putting some focus on partitioning to determine if there is an opportunity to increase performance here. Biggest issue we have when you start reviewing explain plans for the queries is the need to continuously scan over the millions of records, throw in the joins and things can get really large.... but, got sidetracked on this response...posting a response regardless. :)
There is nothing impossible with PHP and mySQL. In 2002, I managed 2 mln entries in one DB only on a free PHP-Nuke CMS. Plus three copies of Wikipedia on the same server (another 3 mln entries). And there were no lag-behinds.
We have built up our product base using many vendors - and this has not always been easy as well.
I know we can ultimately get almost any system to perform with millioins of records, i've done that before in years gone by. The issue we have is working to stay in the boundaries of the main cart platform without the need to completely diverge from the baseline. We are slowly having custom add-ons built and doing other things with configuration and layouts that reduce some of the "extra" features that cause tremendous drag on the system when there are a large number of products, prices, categories, and features.
Right now i'm putting some focus on partitioning to determine if there is an opportunity to increase performance here. Biggest issue we have when you start reviewing explain plans for the queries is the need to continuously scan over the millions of records, throw in the joins and things can get really large.... but, got sidetracked on this response...posting a response regardless. :)
Thank you for the response, to clarify my request, I am interested in learning how you got so many vendors to sign up. I have tried every trick that is out there to lure and provide value to vendors but it has not been easy to get vendors to sign up. I would love to have the problem you are having right now with more products.
Back to the original issue -- what prompted this was an attempted run on AWS using a Serverless setup for the database in preparation for a potential migration to that platform. Making use of the Aurora database which is essentially at a Version 5.6 for MySQL, the query essentially hangs. I decided to let it run its course since the CPU was running. Ultimately, it took just over an HOUR to return all of the "1"s. Running on the database on a standard AWS instance, the query returns in 23-26 seconds -- Still WAY too long. We suffer that query every time we go to the main Admin landing page.
But, the problem with the query and the AWS Serverless environment makes the whole thing NOT usable.
For those that have not looked into Serverless, it really does provide some nice options -- we just need to get the application to actually make good use of it.
Additional physical memory can fix that problem. Trouble with Cloud is that there are "allocations" of memory, but they are usually implemented as "limits but on demand". So it's never there when you need it the first time.
I have clients running on VPS servers with 6GB of physical memory with 2GB of that dedicated to mySQL that run nearly 500K products (not MVE, but Ultimate with one store). Concurrent users has not been an issue. Not tried one with 2M products, but would expect mySQL/memory tuning would ensure it ran quickly. If you can get it all running in memory, there will be no performance problems other than what it takes to walk the tables with their proper indexes. Having more indexes makes inserts/updates take longer but lookups are fast. Switching to InnoDB on some tables that are updated frequently (like products, product_descriptions, user_session_products, etc.) can really help with concurrent usage because the tables aren't locked while an update is occurring.
There are tons of variables. That's why if you're running a real commercial site, you should have a qualified DBA (which I am not) review your DB environment. It's costly but well worth it in the long run.
The memory item is interesting -- from what I can tell the database is able to load up into memory with the only time i actually see it do anything with disk is some of the sorting -- been doing what I can to tune that out (not a dba either). But, watching things indicates it is just heavily compute intensive as it walks the tables.
500K products was not a big issue for us, it is when we jumped over 1M, that with many of the products having 5-10 features or more, plus about 8K categories... things started popping up.
I want to take advantage of the Serverless infrastructure due to the "scaling", replication, fault tolerance, etc -- just really could be a nice option. Then i would just need to hopefully focus on the scaling of the app server.
Right now the majority of the work in the database is browsing, not a lot of writes going on. So, i'm really focused on the read performance. I really believe it should be much better than it is. I'm capturing the slow queries and reconstructing and doing many other things to work out EXPLAIN plans -- just a lot of data. Coming up with a good partitioning strategy is actually harder than it should be with this dataset, but, that is on the plan.
So, i also did change out the SQL_CALC_FOUND_ROWS for the one main offender... still took AWS Serverless 1 hour and 22 minutes to return the count (just over 3.6M was the count value). 11 seconds on MariaDB and 22 seconds on MySQL. So, i have a forum note up on AWS and will probably pay for support to get some resolution there. Other option is the full RDS implementation.
Every page load updates session data as well as if there are items in the cart, the cart is saved too.
Read performance is a function of table locks versus record locks. So having a high level of concurrent users would imply that using InnoDB on the 'user' and 'session' related tables would be much more efficient since to update the table under ISAM, the whole table is locked until the operation completes. I.e. all reads will block until the update completes. And updates will block until the reads complete or the update comes to the head of the queue.
I won't use AWS because unless you really have someone who knows how to tune it (and pay for it), it becomes a crap shoot as to what performance you get. You pay for things like residency versus instantiation, dedicated memory, etc. And disk too can be highly variable depending on the locality of the instance relative to the locality of the disk. Cloud is fine if you're running a large site with load balancers and all the other distributed framework. But that's outside the scope of 99.9% of cs-cart installations. I'm sure @poppedweb will have something to say on this assertion! :-)