3

One of our Magento clients has reported that creating orders from the backend (manually eg customer ordering over the phone) is intermittently very slow.

Each checkout step (whenever the spinning 'Please wait' wheel appears) can take anywhere from 5-20s to load:

enter image description here

To check/confirm this we have installed AOE Profiler and have set it to profile the requests made to the /index.php/admin/sales_order_create URL.

The profiler has identified that the most common source of slowness is the Mage_Core_Model_Session_Abstract_Varien::start/start event. We have seen that event take between 5-40s as shown in the screenshot below:

enter image description here

Saving an order can take anywhere from 10-40 seconds (10s is normal whereas anything greater than 15s is excessive). MySQL slow query logging is enabled and it has not logged any slow queries so the slowness does not appear to be due to database queries.

The system has 12,000 products and approx 10,000 orders. Most orders only have 2-3 order items.

The site is running Magento 1.9.3.8 on dedicated hardware and the system load is always low (eg < 0.5). The site gets low traffic and there is rarely more than 1 customer browsing at a time so there does not appear to be a bottleneck as far as resource allocation is concerned.

Additional information requested:

How can we troubleshoot/fix the slow session start which is causing the slowness?

4
  • It would really depend on custom code done on your instance around session generation, so start debugging there, tbh it is not a general issue it is specific to your site and connot be reproduced to give you any reliable answer Aug 22, 2018 at 17:08
  • @chris Please view my profile, Network profile for contact info, including my Skype ID and get in touch with me, please. Aug 25, 2018 at 23:33
  • @chris Additional info requested. Post on pastebin.com or here. RAM size of your MySQL Host server A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report if readily available Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. Aug 25, 2018 at 23:59
  • @chris Without the information requested above, this is a BEST GUESS. Suggestion for your my.cnf or my.ini [mysqld] section - not nicely formatted as ANSWER would take care of the line spacing. thread_cache_size=100 innodb_lru_scan_depth=100 innodb_flushing_avg_loops=10 innodb_buffer_pool_instances=8 would all be safe and may reduce CPU cycles significantly. Aug 26, 2018 at 17:38

1 Answer 1

1

Suggestion for your ulimit -a to consider please

ulimit -n 48000 # from 1024 to enable more OS file handles to be active to make this persistent across OS shutdown/restart, review this url for similar OS instructions https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

Suggestions to consider for your my.cnf [mysqld] section (RPS = Rate Per Second)

# 20180827 0149 from mysqlservertuning.com
# query_cache_size=0  # from 64M conserve RAM for useful operations, QC not in use
# max_connections=100  # from 200, max_used_connections in 4 days = 11
# read_rnd_buffer_size=512K  # from ~4M to reduce handler_read_rnd_next
# thread_cache_size=100  # from 4 to reduce threads_created of 1423 in 4 days
# key_cache_division_limit=50  # from 100 to reduce key_reads RPS

Save your current my.ini in \history with DATED timed filename such as 20180827hhmm-my.cnf to allow going back to last working my.ini quickly.

Copy this BLOCK (including the leading date and our web site name)to END of your [mysqld] section and ENABLE one change per DAY by removing leading # and spacecharacter, monitor before moving on to next change.

Disable EARLIER same NAMED variable with leading # and space bar, to avoid confusion. In 5 years you will still have history of my.ini changes with approximate dates.

Normally only ONE change per day, monitor before moving to next change. If a change seems detrimental, go back to last working my.ini and let us know, please.

For additional suggestions (and you have many more opportunities) please view profile, Network profile.

7
  • @chris Thanks for the upvote and Accept. Have you already applied all the suggestions? What is typical response time now, please give BEFORE and AFTER recap. Thanks, Wilson Aug 27, 2018 at 14:04
  • We are currently implementing each suggestion, one per day. Last night we did the read_rnd_buffer_size tweak which seems to have made a significant improvement to the time taken to save a manual order - but this may well be a coincidence. All orders saved today were 6-7s whereas previously this was 10-20s. I won't jump to any conclusions yet. Either way though, mega thanks.
    – Chris
    Aug 30, 2018 at 17:23
  • @chris One a day is the best policy. Please document with leading # in your my.cnf the date change was made and apparent difference so the next person looking at your configuration does not arbitrarily make it larger, thinking MORE has to be better. Thanks for keeping me posted. And please visit my web site. Aug 30, 2018 at 17:52
  • @chris Have you implemented all 5 suggestions? How is the instance running for your customers/associates? Sep 17, 2018 at 12:38
  • We implemented all the suggested MySQL tweaks, but none of them addressed the original issue of slow backend order creation. However, we finally tracked this down to an issue relating to the cache being too large. We are now pruning Magento's cache folder every few hours and the performance is normal again. Thanks for your time and suggestions!
    – Chris
    Sep 17, 2018 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.