script causing cpu max limit - get_audiences_list()

[emtecmedia]

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.

[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';

[TheHYPO]

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

[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?

Originally Posted by 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';

[mprough]

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

Setting the above to false helps quite a bit also.

~Melanie

[DivaVocals]

Originally Posted by 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?

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..

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.

[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.

[DivaVocals]

Originally Posted by 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??

[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.

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.

[DivaVocals]

Originally Posted by 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..

Originally Posted by 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.

Honestly I can do BASIC queries on my own, but joins and optimization is WAAAAAY over my head.. 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..