Slow server responce time in admin

[semide]

We have a store that has been up for 8 years, and has about 66,000 orders in the database. Over the years we've updated Zen, and kept up with server updates as well.

The site runs well on the public side, managing about 9,000 products. Currently Zen 1.5.4

Recently, we've noticed a large slowdown in the admin section, specifically the orders section. Loading the initial screen can take a couple of minutes, and always takes at least 20 seconds or so. On a busy day trying to pick and pack orders, this is a pain.

I did a bunch of research, tried many tips, (disabling categories, etc.) and still no joy. Slow query reports the usual suspect:

# Time: 181010 3:18:01
# User@Host: ------------------------- @ localhost []
# Query_time: 5.501308 Lock_time: 0.000126 Rows_sent: 20 Rows_examined: 337881
SET timestamp=1539159481;
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 (orders_status s, orders o ) left join orders_total ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total') where (o.orders_status = s.orders_status_id and s.language_id = '1') order by o.orders_id DESC limit 0, 20;


When this query is run, MySQL server uses 100% CPU time. Server is a dedicated VSI with 4 cores and 8GB of RAM.

We've tried a number of tuning options, and our cnf file looks like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connect_errors = 500
max_connections = 2500
max_user_connections = 2500
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
max_allowed_packet=268435456
query_cache_type=0
query_cache_limit=1M
query_cache_size=0
thread_cache_size=4
table_cache=100K
key_buffer=128M
symbolic-links=0
slow_query_log = 1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 5
open_files_limit=65535
innodb_buffer_pool_size=55574528
tmp_table_size=32M
max_heap_table_size=32M
innodb_file_per_table=ON
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Anyone have any ideas? We're open to suggestions. Some of the tech team suggest pruning the orders to no more than 5 years of records, but I believe that is a non-trivial task with Zen.

[DrByte]

In your my.cnf, why are these set to 0? It's like you've turned them off. Turning it on with query_cache_size = 32M (or even way more, up to 512M if your server config allows for it) may be helpful

query_cache_type=0
query_cache_limit=1M
query_cache_size=0

It may be worth pushing this to 16:
thread_cache_size=4


What are the indexes currently on the "orders", "orders_status", and "orders_total" tables?
How do those indexes compare with the default schema for Zen Cart?
https://github.com/zencart/zencart/b...sql#L993-L1195

How long does that same query take to run if you paste it into phpMyAdmin? If it's fast, then the speed issue is something else.

You said the page takes 20 seconds, but your slow-query-log says 5.5 seconds. Any idea why the difference?


While pruning order data is possible, it shouldn't be necessary.