So from my testings XF will take ~7 minutes to install when
innodb_flush_log_at_trx_commit is set to
1 (default) and when set to
2 it takes less than 45 seconds.
Edit:
@ClareXoBearrx3R9 Are you running XF on SSD or HDD? If HDD then slow performance is very much expected.
Probably try with these configs shared by
@Xon for SSDs:
Code:
innodb_flush_neighbors=0
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=3000
If you end up using nvme then
innodb_io_capacity can be much higher.
Thanks a lot for all the testing and reporting of the results. Definitely good to know the performance impact that has. In-fact, our concern was not necessarily about our installation times of 10-20 minutes greater than reported, but rather times that are
hours longer.
And while we are currently running on SSDs, we were previously running on HDDs. I know people enjoy SSDs for their speed, but for enterprise use they are extremely expensive unless we're purchasing consumer-grade SSDs which simply don't have an acceptable durability for heavy enterprise use. But due to the extremes seen in this software, we switched to SSDs for the website.
I know HDDs are much slower than SSDs, but in-practice they work just fine for our use case and can still get fairly high write speeds of over 200MB/s which should be more than enough for webserver and basic database transactions where a forum is concerned.
And to further prove my point, XenForo is the only forum software so far that I have seen suffer from such an issue. I've used more than half a dozen forum software, and none of them have had this issue. So understandably, it ought to be reasonable for me to suspect XenForo is doing something out of the ordinary to cause this behavior.
During an XenForo template/phrase rebuilding; there is a huge number of single statement write transactions and is full of N+1 read query behaviour. And this process also triggers css cache invalidation horrifyingly frequently which is an issue for a live site doing an add-on update.
innodb_flush_log_at_trx_commit has the following possible values;
0 Nothing is done on commit; rather the log buffer is written and flushed to the InnoDB redo log once a second. This gives better performance, but a server crash can erase the last second of transactions.1 The default, the log buffer is written to the InnoDB redo log file and a flush to disk performed after each transaction. This is required for full ACID compliance.2 The log buffer is written to the InnoDB redo log after each commit, but flushing takes place once a second. Performance is slightly better, but a OS or power outage can cause the last second's transactions to be lost.
The lack of batching in XenForo is what makes the
innodb_flush_log_at_trx_commit=2 change such a massive performance improvement, as the default value prevents MySQL & the operating system from batching writes in the background.
This is an excellent and very intelligible explanation of what XenForo is doing, as well as the explanation about the config for one of the database settings. I very much appreciate this. While we are using a value of 1 for this for stability reasons, it definitely helps to know XenForo's possible weaknesses (for instance, the lack of batching) could be a big culprit in causing poor performance.
This is exactly my experience with these settings when using InnoDB and using SSDs
I do not post any settings here because they depend very much on the circumstances and settings of the respective server and its software and, according to their own statements, professionals are at work who need to know such things.
Totally understandable— those reasons are exactly the same as why I usually don't post configs since there's so much variance and such.
The problem here is not due to the forum software.
One might argue that, however, from my experience other forum software doesn't suffer from these issues.
Popular forum software such as phpBB3, MyBB install all under 5 minutes on even the older HDDs, so it's reasonable for us to wonder why XenForo struggles so greatly on even a more modern storage device. Even Invision Power Services community suite, when installing it, we'd get installation times of about 30 minutes on our slowest hardware, and I'm not a huge fan of their bloated software. So we had some reasonable concern when installing XenForo, which for us only consists of the forum software without any addons.
One of our admins' latest tests on a mechanical hard drive with stock/default database settings show a much faster installation of ~30 minutes in a Linux install, while an identical setup on a Windows install took about 4.5 hours.
Quite frankly, I'm surprised that so many shared hosting companies are offering SSDs now... so perhaps that is one of the issues, where most people are not noticing long installation times due to the hardware. In fairness it has been a while since I've worked at a shared-hosting company (last time I worked there was about 2012). Are they rich? Or just using some generic SSDs?
Per our organization's policies, whenever we are to use an SSD it must be either SLC or MLC for endurance reasons. Even the slower SATA form-factor SSD with only 512GB capacity with SLC is going for about $5,000 USD where we are, which is quite a hefty price tag for such a small capacity of storage. And of course, the NVMe form-factor will only be more expensive.
Since we have high capacity storage needs, we mostly use hard drives, currently a number of hard drives with 16TB (with ~14.5TB usable) of individual capacity. Currently no RAID configurations as we didn't want to introduce any unnecessary complications just yet... but that's another matter of its own.
Anyway...
All in all, I very much appreciate all the constructive replies from fellow members of the community— that is ultimately what we were looking for, an understanding on what could possibly be causing such skewed installation times for us compared to the majority of the population.
Cheers!
---
Edit: On another note, I wish it didn't get so heated previously, but hope that the staff can understand the situation here. With the information I gave and replies I was getting from staff, those results were logically impossible, meaning that either someone is lying, or extremely misinformed. And I don't appreciate useless remarks that seemingly serve no purpose whatsoever. You might be a staff member, but you should not be above your own rules— because if you are, I want nothing to do with it.
Things like this are what I'm referring to.
If it's not sarcasm, then perhaps you should feature your post as the most helpful post in the entire community.
You are running a business which I understand, but this comment to me was extremely unprofessional. And I would hope I never get talked to like this from a staff member again.
