Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Waltham, Massachusetts, United States
    Plugin Contributions

    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:

    max_connect_errors = 500
    max_connections = 2500
    max_user_connections = 2500
    # Disabling symbolic-links is recommended to prevent assorted security risks
    slow_query_log = 1
    log-slow-queries = /var/log/mysql-slow.log
    long_query_time = 5

    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.

  2. #2
    Join Date
    Jan 2004
    Blog Entries
    Plugin Contributions

    Default Re: Slow server responce time in admin

    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


    It may be worth pushing this to 16:

    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?

    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.

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.



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


Posting Permissions

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