2

I'm trying to install Xenforo (Forum software) on a Linux Azure Web App (App Service) which has been setup with:

  1. PHP 7.2
  2. Premium P1V2 Plan

It's also connected to an Azure MySQL Database (Version 5.7) with Basic 2 vCore (5 GB).

  • Xenforo version: 2.1.4

Problem

It took around 2 hours to finish the installation, which is way too long since it should take around 2-3 minutes. Also happening on plugins' installation. In particular the Importing... Master data (Phrases: xx.x%) phase takes forever.

Details

  1. There's no CPU nor memory usage exceeded warning, all seems healthy to the diagnostic tools and Xenforo's requirements test has been successfully passed.

  2. I tried to scale all plans to max level and nothing changed (Both MySQL and App Service)

  3. I also tried with MySQL 8.0.

  4. The connection to the website is very fast, only the Xenforo's installation and plugin's installation is slow, so I suppose it could be a file write issue or maybe some configuration (InnoDB?) or something related to compatibility.

Gen5 hardware specification (Microsoft docs)

  • Intel E5-2673 v4 (Broadwell) 2.3-GHz and Intel SP-8160 (Skylake)* processors
  • Provision up to 80 vCores (1 vCore = 1 hyper-thread)
  • 5.1 GB per vCore (Memory)
  • Provision up to 408 GB (Memory)
Community
  • 1
  • 1
ALFA
  • 1,726
  • 1
  • 10
  • 19
  • Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; for server workload tuning analysis to provide suggestions. – Wilson Hauck Mar 09 '20 at 12:17
  • 1
    @WilsonHauck https://pastebin.com/926cZTrM – ALFA Mar 09 '20 at 14:08
  • Limits for your current Azure W64 hosted plan? RAM size, # cores, any SSD or NVME devices on MySQL Host server? Analysis in process. – Wilson Hauck Mar 09 '20 at 15:24
  • 1
    @WilsonHauck Here's the limitations https://learn.microsoft.com/en-us/azure/mysql/concepts-limits, I cannot find information about hardware specifications you're asking, I edited my post with what I found in the documentation. However I also tried to put the maximum MySQL plan and nothing changed. – ALFA Mar 09 '20 at 15:34
  • Thanks, I will check the concepts-limits link. – Wilson Hauck Mar 09 '20 at 16:00
  • Do you know the pricing tier you are subscribed for? For an accurate version #, please post results of SELECT version(); Are you aware you have com_stmt_prepare ratio to com_stmt_execute at 2 to 1? Prepares always take time and if you do not execute, what is the point in preparing? You do have com_stmt_execute and com_stmt_close = meaning no resources were left in use, which is great. Do you know why com_flush is running at the rate of 120 times per hour? This contributes to delays because tables, definitions that could have remained open need to be reopened after flushes. Can you Skype? – Wilson Hauck Mar 09 '20 at 16:34
  • I will be out of office for 30 minutes and let you know when I return. Thanks – Wilson Hauck Mar 09 '20 at 16:39
  • 1
    Hi, thanks, version is 5.7.27-log according to SELECT VERSION(); Pricing tier is Basic with 2 vCore. – ALFA Mar 09 '20 at 16:41
  • Thanks, There are 4 more questions in my comment. I will check tomorrow AM for your feedback. – Wilson Hauck Mar 09 '20 at 17:12
  • I am not aware of those because I don't even know what they do, they're default settings. – ALFA Mar 09 '20 at 19:43
  • Do you have the option of using Skype? We are past the one question/one answer plan of stackoverflow.com. View my profile, Network profile for free downloadable Utility Scripts to improve system performance. – Wilson Hauck Mar 09 '20 at 20:37
  • What is your version of Xenforo? – Wilson Hauck Mar 10 '20 at 00:12
  • @WilsonHauck 2.1.4 – ALFA Mar 10 '20 at 07:03
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/209348/discussion-between-alfa-and-wilson-hauck). – ALFA Mar 10 '20 at 07:22
  • I will hang out in Chat for 10 minutes this AM. Thanks – Wilson Hauck Mar 10 '20 at 12:01
  • Alfa, I will reword my questions for your submission to XenForo since you are running licensed software and they do offer support. Observations: A) We have found we have com_stmt_prepare ratio to com_stmt_execute at 2 to 1? Prepares always take time and if we are not going to execute, what is the point in preparing? B) We have found com_flush is running at the rate of 120 times per hour? This contributes to delays because tables, definitions that could have remained open need to be reopened after flushes. Good Luck. – Wilson Hauck Mar 10 '20 at 19:11
  • For your consideration, https://learn.microsoft.com/en-us/azure/mysql/howto-server-parameters Will help you understand how to manage SETTINGS at Azure. – Wilson Hauck Mar 21 '20 at 16:48

1 Answers1

2

Rate Per Second = RPS

Suggestions to consider for your Azure portal, SETTINGS, Server parameters

thread_cache_size=64  # from 9 to reduce threads_created - an expensive operation
read_rnd_buffer_size=192K  # from 512K to reduce handler_read_rnd_next RPS of 57
innodb_lru_scan_depth=100  # from 1024 to avoid 90% of CPU cycles used for function every second
tmp_table_size=32M  # from 16M to expand capacity in RAM
max_heap_table_size=32M  # from 16M to reduce created_tmp_disk_tables of 49 RPhr
innodb_open_files=-1  # to autosize based on table_open_cache at next restart of instance

The first 5 changes are dynamic and will take effect without instance restart.

Let us know how these few changes affect your query response time either good or bad. For additional assistance please view our profile, Network profile and download our free Utility Scripts for assistance with performance tuning.

Wilson Hauck
  • 2,094
  • 1
  • 11
  • 19
  • Thanks for your help, I cannot find any of those parameters in Azure MySQL except for `innodb_lru_scan_depth`. I can see the other ones in the global variables through MySQL but I cannot edit it unless they're available on 'Server parameters' section in Azure Portal. – ALFA Mar 10 '20 at 21:10
  • Lowering the lru scan depth will help. The remaining may have to go through a Support Ticket request through Azure support. – Wilson Hauck Mar 11 '20 at 00:58
  • 1
    Same speed as before during the installation, thank you anyway for your help. – ALFA Mar 11 '20 at 20:39
  • @ALFA Do you have open ticket with XenForo and what is the ticket number, please so I can follow their response. Will Azure assist with the my.ini changes requested? It is possible Azure will enable more changes you can make with a higher tier on their system, please check with Azure. – Wilson Hauck Mar 12 '20 at 00:10