Magento 1.9 : Slow order creation from backend

Question

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:

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:

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:

• MySQL server is hosted locally on same dedicated server as web server
• MySQL version is 5.1.73 (most recent version supported by CentOS 6.10)
• RAM is 64GB (~40GB currently free)
• my.cnf: https://pastebin.com/Dq2jYKDa
• SHOW GLOBAL STATUS result: https://pastebin.com/Bmy7B8n5
• SHOW GLOBAL VARIABLES result: https://pastebin.com/fghk0UkL
• MySQLTuner output: https://pastebin.com/xqKzCSxd
• Other commands: https://pastebin.com/D3TFMae9

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

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