Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2009
    Location
    Waltham, Massachusetts, United States
    Posts
    17
    Plugin Contributions
    0

    application error Slow server responce time in admin

    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.
    Last edited by semide; 10 Oct 2018 at 07:33 PM.

 

 

Similar Threads

  1. v153 Server Time not displaying VPS whm server time
    By icecold in forum General Questions
    Replies: 2
    Last Post: 19 Oct 2016, 03:45 PM
  2. Replies: 6
    Last Post: 10 Sep 2015, 08:12 AM
  3. Slow Parse Time / Query Time
    By chrx in forum General Questions
    Replies: 4
    Last Post: 24 Aug 2011, 11:55 PM
  4. How to understand server up time in admin
    By sfklaas in forum General Questions
    Replies: 0
    Last Post: 19 Jan 2011, 03:56 PM
  5. Internal Server Error when accessing Admin Area for first time
    By aruo_byron in forum Templates, Stylesheets, Page Layout
    Replies: 1
    Last Post: 3 Nov 2008, 01:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR