huge DB performance hit after upgrade from 1.26d to 1.3.7

[bod]

Hi Guys, we just finished an upgrade from 1.26d to 1.3.7 and things are running very slowly, and causing problems because of it. It appears to be an issue with our mySQL database performance.

I now have slow query logging turned on. As a result i can see that using the search facility in the 'admin -> customers -> orders page' can result in a query that takes over 30 seconds to run, as opposed to the exact same query taking 10% of that time on the 1.26d database structure with the exact same date. I have a run a series of the slow executing queries on the old DB table schema and the new, and they are consistently much much slower on the new schema (again with the same data).

The problem is not overly consistent if that is a clue. Sometimes the query runs in less than 10 secs, often a lot more (on the new schema). It is causing enough problems that our office staff are getting page timeouts so much they are being seriously impeded in their efficiency, and getting plenty annoyed too.

For example, the query below takes usually about 7 seconds on the new schema, and is almost immediate on the old schema. And it's in the slow query file as having taken up to 33 secs to.

"select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address from <your_prefix>_orders o left join <your_prefix>_orders_total ot on (o.orders_id = ot.orders_id), <your_prefix>_orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by o.orders_id DESC limit 0, 20;"

Does anyone have any idea why this is, and what i can do about it. It is causing us some serious problems.

Best regards.

[bod]

This problem is on the admin side of things by the way, an example from the customer side is

Parse Time: 0.530 - Number of Queries: 130 - Query Time: 0.17508664541626

which seems about right to me.

Is there any way i can get this info for the admin pages too?

[bod]

I just ran the "EXPLAIN SELECT ..." on that query above, and got this result for the 1.3.7 database



and this for the 1.26d version of the tables


The most noticeable difference is the use of the tmp table with the newer schema, and a filesort necessity. Almost every slow running query has used a tmp table, or even a tmp table on disk.

I also note that of the possible keys for on the 'ot' table on the 1.3.7 version, it is choosing a different key as compared to the 1.26d version. Could this be responsible for the tmp table and filesort?

Best regards!

[DrByte]

I suspect you're missing some important indexes.
What indexes exist on the tables affected by the queries that are running slowly?
Please list them along with the table names.

[bod]

here they are...

TABLE `uksi_orders`
PRIMARY KEY (`orders_id`),
KEY `idx_status_orders_cust_zen` (`orders_status`,`orders_id`,`customers_id`),
KEY `idx_date_purchased_zen` (`date_purchased`)

TABLE `uksi_orders_status`
PRIMARY KEY (`orders_status_id`,`language_id`),
KEY `idx_orders_status_name_zen` (`orders_status_name`)

TABLE `uksi_orders_total`
PRIMARY KEY (`orders_total_id`),
KEY `idx_ot_orders_id_zen` (`orders_id`),
KEY `idx_ot_class_zen` (`class`)

thanks for taking a look at this in our hour of need!

[DrByte]

Those look fine, if they're running correctly (not damaged).

As to those particular tables, the schema hasn't changed much, and any change in performance using the identical query and identical table contents seems odd.

What version of MySQL? (and version of PHP too, for that matter)

I've also PMd you.

[DrByte]

Hi Guys, we just finished an upgrade from 1.26d to 1.3.7 and things are running very slowly, and causing problems because of it. It appears to be an issue with our mySQL database performance.

...

For example, the query below takes usually about 7 seconds on the new schema, and is almost immediate on the old schema. And it's in the slow query file as having taken up to 33 secs to.

"select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address from <your_prefix>_orders o left join <your_prefix>_orders_total ot on (o.orders_id = ot.orders_id), <your_prefix>_orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by o.orders_id DESC limit 0, 20;"

There are two solutions to your problem:

1. On the orders_total table, drop the index that's been added for the "class" field.

or

2. Alter the query to make the join more efficient:

select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address from TABLE_PREFIX_orders o left join TABLE_PREFIX_orders_total ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total'), TABLE_PREFIX_orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' order by o.orders_id DESC limit 0, 20

If you do an EXPLAIN after making *either* of those changes, you'll find that the performance is good again.


FYI - similar issue discussed here, and has been incorporated into the v1.4 release: http://www.zen-cart.com/forum/showthread.php?t=79408

[bod]

Hi guys,

i tried both suggested solutions, and neither made any really noticeable difference sadly. Though i can see why they should. I still don't know why our DB is underperforming everyone elses. I tried a fresh install and just populated the customer, order and product tables with high volumes of data, and the same thing happened again.

So after making the suggested changes logging into admin and accessing the orders page or customers pages, though i touch quicker, were still in major danger of timing out.

In the end i managed to track the slowdown to two sources, in a minor way the large number of like clauses in the searches, and to a much greater extent the count query the splitPageResults class executes. I have now implemented your suggested changes, as well as having rewritten the orders and customer pages to feed the splitPageResults class a cut down version of the query it was struggling with, yet still include the same functionality.

So it's all a lot quicker now though i look forward to version 1.4 with it's much improved query load.

Many thanks for all your help. And a really big to Dr Byte, whose help on so many other peoples posts as well as mine has been a godsend for me.

I'd still like to get to the bottom of what's wrong with our DB to perform so slowly in the first place in this version of ZC, but it's workable for now.

All the best, Bod