Results 1 to 2 of 2
  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.

  2. #2
    Join Date
    Jan 2004
    Posts
    65,185
    Blog Entries
    7
    Plugin Contributions
    223

    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

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

    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

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