script causing cpu max limit - get_audiences_list()

Hi,

I recently updated our site from 1.3.7 to 1.3.8a and today noticed that both the 'Mail Gift Certificate' and 'Export Email Address' pages in the admin are causing my VPS server to crash - maxing out the CPU.

When I view either of these pages in the admin they just wont load and then the whole admin being unresponsive after a minute or so due to my server CPU load going up to 97%.

This has only occured since the upgrade and I cant find any threads of other people experiencing this? I am running a beefy VPS with MediaTemple which hasnt caused any dramas in the past and all is well until I go to one of these pages in the admin. have tested on our live store and development version, both installs have the same issue.

Could it be something to do with get_audiences_list()?

If someone could help that would be a great help and I am stuck on this with no real options.

Brad.

That's from the new "Customers who have never placed an order" audience option added in 1.3.8.

If your system can't handle it, run this query from Install SQL Patch or phpMyAdmin:```
update query_builder set query_category = '' where query_name = 'Customers who have never completed a purchase';

Just a note that it looks like this was the cause of the crashing of my database recently.

I was having this same issue, page took forever to load. Found this and ran it...works perfect now! What did this patch do exactly?

DrByte:

That's from the new "Customers who have never placed an order" audience option added in 1.3.8.

If your system can't handle it, run this query from Install SQL Patch or phpMyAdmin:```
update query_builder set query_category = '' where query_name = 'Customers who have never completed a purchase';

CONFIGURATION >> EMAIL OPTIONS >> Audience-Select Count Display

Setting the above to false helps quite a bit also.

~Melanie

marcopolo:

I was having this same issue, page took forever to load. Found this and ran it...works perfect now! What did this patch do exactly?:laugh: I'd love to understand why this works too..

I was getting the following error when ever I clicked on Admin > Gift Certificate/Coupons > Mail Gift Certificate, and I tried this little bit of SQL and lo and behold.. all is right with the world..:clap:

1104 The SELECT would examine more than MAX_JOIN_SIZE  rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=#  if the SELECT is okay
in:
[SELECT DISTINCT c.customers_email_address as  customers_email_address, c.customers_lastname as customers_lastname,  c.customers_firstname as customers_firstname FROM zen_customers c LEFT JOIN  zen_orders o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL  ]
If you were entering information, press the BACK button in your browser and  re-check the information you had entered to be sure you left no blank  fields.

[IMG]http://lanikshair.com/adminLaniksHairJ5MwqBGd/images/pixel_trans.gif[/IMG]

The query in question works fine on a small dataset, but in the case of large amounts of order and/or customer data the query is not adequately optimized. So, to keep it will require fine-tuning it.

DrByte:

The query in question works fine on a small dataset, but in the case of large amounts of order and/or customer data the query is not adequately optimized. So, to keep it will require fine-tuning it.So if I am understanding you correctly, as the database grows, I can possibly expect this to happen again.. Can you provide some insight that would help me understand how I go about fine tuning this query.. is what I need to do to prevent this from happening again??

As to "when" the problem would be triggered, I don't have a "magic number" to tell you when you'd experience problems with the size of the data. Frankly, when I added that query to the list for v1.3.8a after testing with several forum members, I didn't anticipate this problem. But ... sometimes things do happen unexpectedly. It's really not high on my list of priorities of things to "fix". If it causes a problem for you, simply disable it by making the change I posted earlier ... which will disable it so it won't show in selection lists and won't try to run the query.

If you want to fine-tune the query, feel free to go study the ins and outs of mysql queries and joins and optimization ... dev.mysql.com And when you find the optimal revision for it, feel free to share it for consideration and testing by others.

DrByte:

As to "when" the problem would be triggered, I don't have a "magic number" to tell you when you'd experience problems with the size of the data. Frankly, when I added that query to the list for v1.3.8a after testing with several forum members, I didn't anticipate this problem. But ... sometimes things do happen unexpectedly. It's really not high on my list of priorities of things to "fix". If it causes a problem for you, simply disable it by making the change I posted earlier ... which will disable it so it won't show in selection lists and won't try to run the query.That answered my original question.. I was really trying to get more of a sense of why moreso than when the problem was triggered, but I think I get it now.. After talking it over with my client, she is okay with this group of customers being disabled.. So after running the change you posted, the error disappears, and so all is right in the world.. Thanks for your response..

DrByte:

If you want to fine-tune the query, feel free to go study the ins and outs of mysql queries and joins and optimization ... dev.mysql.com And when you find the optimal revision for it, feel free to share it for consideration and testing by others.:laugh:Honestly I can do BASIC queries on my own, but joins and optimization is WAAAAAY over my head..:no: Disabling the query seems to be okay with her for now.. If my client really wants to push getting this fixed, we would likely be engaging the services of a contractor to help troubleshoot this.. If we do, and my client is okay with sharing the contractor's results, I'll be happy to share with the community..

Having just had this problem myself
I have found out a couple of relavent things

It appears that that the check for NULL on a field used in the joins is a lot faster.
Also a few versions of mysql had problems testing for NULL on fields not used in the joins.

So a first improvement is to replace the o.date_purchased field with o.customers_id field

SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN  TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.customers_id IS NULL

An altenative that you can try that seems to produce around the same times is

SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c 
WHERE c.customers_id NOT IN 
(
SELECT o.customers_id AS customers_id
FROM TABLE_ORDERS o
)

Both of these improved my selection times considerably

Obviously backup before trying either of these to see if they work for you

Or just add a distinct index on orders.customers_id to improve performance considerably. (v1.3.9 does this during the upgrade):```
ALTER TABLE orders ADD INDEX customers_id (customers_id);

Also, just a comment ... using the second approach you mentioned, the sub-select, could essentially return all records twice, thus being somewhat less efficient than the join approach.

DrByte:

Or just add a distinct index on orders.customers_id to improve performance considerably. (v1.3.9 will do this during the upgrade)

Also, just a comment ... using the second approach you mentioned, the sub-select, could essentially return all records twice, thus being somewhat less efficient than the join approach.

Thanks Doc
And of course the addition of the index made the selection of the original or my 2 alternatives irrelevant.
The speed increase from the use of the index is remarkable :smile:

Thanks for confirming that. ;)

DrByte:

The query in question works fine on a small dataset, but in the case of large amounts of order and/or customer data the query is not adequately optimized. So, to keep it will require fine-tuning it... It's really not high on my list of priorities of things to "fix".

I'm a little bit horrified that ZenCart is including SQL queries that are expected to fail when used under real world conditions and need to be tweaked by end-users to work correctly.

We have a 1.38a installation with about 16K customers and 50K customers. (Not especially high numbers for an ecommerce site.) As of today, our entire site was locking up and timing out, and I spent half the day tracking it down to this SQL query. According to the logs, this query was taking of 2,000 seconds to run. That's a HALF HOUR.

gilby's suggestion to replace the date_purchased field with customers_id field is a good one. Seems to work just as fast on orders_id, probably because both those fields are defined as non-NULL.

DrByte's suggestion to add an index on customers_id helped even more (though not necessary if you use orders_id instead of customers_id). Using an indexed column got the query running in .02 seconds. Quite a difference from 2000 seconds.

Hope this fix did become a priority and was implemented in 1.3.9.

It was :)

And.... You also should upgrade as well

You will find this and many other "fixes" are included as well to improve performance.

capnhairdo:

Hope this fix did become a priority and was implemented in 1.3.9.Not to speak for the good doctor, but I do believe you misunderstood what he meant when he stated "It's really not high on my list of priorities of things to "fix" "..

It's likely he meant that it was not a priority to fix thin in v1.3.8a since v1.3.9 was the thrust of the Zen developers focus and the issue would be addressed in THAT release... DrByte can of course confirm my interpretation.. this is how I took his words..

Given that DrBtye also stated that this was addressed in Zen Cart 1.3.9:> Or just add a distinct index on orders.customers_id to improve performance considerably. **(v1.3.9 does this during the upgrade):**and as gilby posted it WAS addressed in v1.3.9, it seems that the thing to do to correct your situation is to upgrade your 1.3.8a shop to the latest version of Zen Cart (v1.3.9h as of this post) or apply the changes posted (as it appears you have done)

May i know how do i UNDO this code ?

update query_builder set query_category = '' where query_name = 'Customers who have never completed a purchase';

I wish to restore back to what it was before i install the SQL patch above.

Thank you!

change set query_category = '' to set query_category = 'newsletter'