RAM requirements for XF 2.18 - MySQL problems

[snoopy5]

Hi,

I have around 15 Xenforo 2.1x installations on one of my servers. Within the last 4 weeks I had 2x MySQL crashes. I then asked my webhoster why this happend and he states that I am running on the server installations which need more than 8GB RAM to run without problems. He offers an upgrade from 8GB to 16GB RAM but of course for more money monthly.

I do not believe this. I this true in your opinion?

What is the minimum RAM requirement for XF 2.1x, to have for sure no problems with MySQL?

My traffic on this server is average. I would even say it is low. But for sure the traffic is not high.

The server details are:

Intel® Core™ i3-7100
2 Cores 3,9GHz
Hyper-Threading
8 GB RAM
1 TB HDD (RAID 1)

P.S. it is my own server. I do not share it with anybody.

 

[djbaxter]

Seems a bit light to me for 15 XF installations - your host is probably right.

 

[Slavik]

How many users on each site?

 

[snoopy5]

How many users on each site?

The activity on these forums varies a lot. There are forums with only 1000 users, but each day at least 200 are logged in a post a lot.

Other forums with 20.000 registered users are very silent with only 100 logged in users on a day and only a few postings.

Would a server like this be better?:

Intel® Xeon® E3-1230v3
4 x 3,3 GHz
Intel® Hyper-Threading
16 GB DDR3 RAM

Or would it make a bigger difference to get an SSD instead of a normal harddrive?

My problem is that I need a lot of harddiskspace. At least 500GB. And I want to reduce costs. So it shall not cost more than 100USD/Month with unlimited traffic, unlimited email sending for the newsletters out of XF and lets encrypt SSL certifivates for free and nameserver for external domains at no extra charge.

 

[rogerl]

The sizes of your databases and the amount you are caching will be the determining factors, but more RAM and more caching will normally make a massive difference to the speed of your sites too, and the more RAM you can throw at it the better.

Your really need to investigate the causes of the crashes though.

 

[snoopy5]

Your really need to investigate the causes of the crashes though.

This is what I do not understand. asked them for the reason of the crashes and they said, that xenforo needs my default more than 8GB RAM and this would be th reason for the crashes.

I would be surprised, if that would be true.

But nowhere in the requirements of Xenforo, RAM is mentioned with a minimum default size.

Would be great if of one of the developers could explain this. @Chris D @Kier

1. I need to know with which RAM size a Xenforo forum has to work flawlessly (always concerning MySQL DB crashes)
2. I need to know whether browser notifications are not recommended if you have only 8GB of RAM and how much RAM I need to use browser notifications flawlessly
3. I need to know whether there are specific configurations I can use in ACP or on a Linix server, to improve the stability of the MySQL DB

 

[Slavik]

I mean if we said you have 100 users active on each forum, 15 forums, 1500 active users as a baseline, then it simply sounds like you dont have enough server resources. Likewise 8gb of ram would be quickly eaten up on 15 forums, plus your web server. Additionally you have standard HDD's and not ssd's or nvmes? This alone will be a huge bottleneck.

I would suggest expanding your hosting budget slightly, getting onto some modern infrastructure that you can leverage to its maximum (forget cpanel/plesk) as thats just additional cost used that can be better used on more physical server power.

 

[VersoBit]

I am curious how your web server is configured? What are you using to serve the site?
Apache? Mod PHP? PHP-FPM? Redis? MySQL/MariaDB? Nginx?

Are all of your configuration files for the web server the same for each site? (minus changes to root folders per install, obviously)

 

[MySiteGuy]

"It needs more RAM" an easy way for a host to say "we don't want to spend much time on this".

MySQL should never run out of ram no matter the load. Properly set up a server will become less responsive but never use up so much RAM that something like MySQL core dumps. I've had servers with 4GB RAM, millions of posts handle over a million monthly visitors on 4 cores. Sure, the system slowed down, but it never core dumped as a result of high visitor counts (2000-3000 online at peaks).

A 16GB 1TB HDD 12 to 24 core server should not run you more than about $60-$75/month. You do not want hyperthreading on for MySQL. Hyperthreading was useful back when servers had fewer cores and needed to handle many simultaneous requests, but HT essentially means each request takes twice as long. Once you get past about 6 to 8 cores, every benchmark I've run shows you'll get far better performance leaving it off (it can be done from within Linux).

 

[djbaxter]

"It needs more RAM" an easy way for a host to say "we don't want to spend much time on this".

Of course it's also a reasonable thing for a host to say when someone is trying to run 15 XF forums with 8GB RAM.

The assumption that it's a MySQL issue seems to be an assumption made by the OP. It doesn't look like the host said that. Rather the host said

that I am running on the server installations which need more than 8GB RAM to run without problems

My guess is that's probably quite accurate.

 

[MySiteGuy]

Of course it's also a reasonable thing for a host to say when someone is trying to run 15 XF forums with 8GB RAM.

It's also reasonable to expect them to have MySQL and web server settings which aren't going to exhaust available memory. Performance should degrade, but not crash. Most companies install it with whatever defaults are there with little to zero consideration of anything else. I rarely encounter a server where they've done anything to stock database settings (or any other settings for that matter).

Ultimately the number of installations is a meaningless number. The number of simultaneous HTTP and DB connections, the read hit rate stats in the database, swap space used, the system swappiness settings (most leave it at the default 60 which is not suited for servers). - metrics ultimately tell more but there's no indication from the OP they did a deep analysis (which is to be expected, the hourly cost of doing this versus telling the client to upgrade isn't worth it to most hosts). A server could have 1000 forums on a 8 GB server, and it would mean nothing without stats.

This is why I take issue with the common "throw hardware at" mantra. I've been in plenty of situations where 1-2 changes to a MySQL configuration doubled its CPS and connection capacity. Or simply flipping on opcache with 128 to 256 mb of ram allocated to it so PHP can execute faster and free up it's DB connection quicker. It might well be the amount of ram, but it might not be.

 

[GPA-R]

I run a quite big XF forum with hundreds of thousands of users and millions of posts on a 2 core 7.5GB machine + CF. The CPU load is between 10-20% and I never had any issue. Make sure static content is not served directly from your server (very important) and make sure you take advantage of XF's caching system. I'd also recommend installing memcache if you are not already using it. It will make a significant difference.

 

[djbaxter]

I run a quite big XF forum with hundreds of thousands of users and millions of posts on a 2 core 7.5GB machine + CF. The CPU load is between 10-20% and I never had any issue. Make sure static content is not served directly from your server (very important) and make sure you take advantage of XF's caching system. I'd also recommend installing memcache if you are not already using it. It will make a significant difference.

But running one large forum accessing a single database is quite a different situation than running 15 forums accessing 15 different databases.

 

[GPA-R]

But running one large forum accessing a single database is quite a different situation than running 15 forums accessing 15 different databases.

That is partly correct yes, however the same optimisations that will make a single board faster would make 15 too. I personally find Xenforo to be very light compared to many other systems.

Utilising caching is mandatory in every running web application and it can work wonders if correctly set up. Also keep in mind that a misconfigured server with 16GB of ram will have worse performance than a properly configured 6-8GB one (I am mentioning only RAM because that's what was the discussion mostly around). If you open mysql config and start randomly increasing numbers it will for sure result in degrading performance/stability.

 

[MySiteGuy]

Utilising caching is mandatory in every running web application and it can work wonders if correctly set up. Also keep in mind that a misconfigured server with 16GB of ram will have worse performance than a properly configured 6-8GB one (I am mentioning only RAM because that's what was the discussion mostly around). If you open mysql config and start randomly increasing numbers it will for sure result in degrading performance/stability.

Unfortunately, many people's idea of mysql optimization is running a "tuning" script, or a reading couple of articles without an understanding of what is going on. Often the advice these scripts give are not only wrong, but can make things worse. I had one client who kept increasing max_connections because he was running out of connections, and his system slowing down to a crawl to the point it would finally crash. Increasing the connections was making the problem worse because it was hitting swap space hard.

 

[snoopy5]

Thanks for the imput so far. A lot of good tips.

So I will try to find out more information about the current configuration, make adjustments and report here.

I checked for errors in the different Forums-ACPs and some of the forums had in the past the alert:

already has more than 'max_user_connections' active
connections

src/XF/Db/Mysqli/Adapter.php:165

I did not take this as urgent since it happened seldom and only on the real heavy traffic sites.


So I asked my webhoster to increase that number for all DB of all forums to 100 for each DB. They did not tell me the number it was beforehand but since they offered me 50 or 100, my guess is that it was beforehand at 25.

The webhoster increased it 2 hours ago to 100 for each DB and my impression is, that the sites are all now faster.

Could that be already the main cause of the trouble?

Additionally you have standard HDD's and not ssd's or nvmes? This alone will be a huge bottleneck.

SSD is at the moment no option. Only HD available.

I am curious how your web server is configured? What are you using to serve the site?
Apache? Mod PHP? PHP-FPM? Redis? MySQL/MariaDB? Nginx?

Are all of your configuration files for the web server the same for each site? (minus changes to root folders per install, obviously)

Yes

You do not want hyperthreading on for MySQL. Hyperthreading

I have only 2 cores.

The server details are:

Intel® Core™ i3-7100
2 Cores 3,9GHz
Hyper-Threading
8 GB RAM
1 TB HDD (RAID 1)

Ultimately the number of installations is a meaningless number. The number of simultaneous HTTP and DB connections, the read hit rate stats in the database, swap space used, the system swappiness settings (most leave it at the default 60 which is not suited for servers). - metrics ultimately tell more but there's no indication from the OP they did a deep analysis (which is to be expected, the hourly cost of doing this versus telling the client to upgrade isn't worth it to most hosts).

This is what I try to find out now. I asked this my webhoster and wait for its answer.

make sure you take advantage of XF's caching system.

Where do I set this up in ACP?

I'd also recommend installing memcache if you are not already using it. It will make a significant difference.

How can I find out myself whether memcache is installed already? I do not have a root server. It is a managed server (but no shared server).

...who kept increasing max_connections because he was running out of connections, and his system slowing down to a crawl to the point it would finally crash. Increasing the connections was making the problem worse because it was hitting swap space hard.

I have to find that out now, because the webhoster increased it to 100 for each DB. But at the moment the sites seems to be faster than before.



Another potential cause for this could be images. I have on many forums a lot of images. At the moment the settings in ACP are to use ImageMagick. On all forums. Should I rather opt for GD?

I had the impression in the past, that ImageMagick delivers the images with better quality. But if this is a bottle neck, I would rather switch to GD to speed things up.

 

[snoopy5]

This is the answer I got:

From the Apache config:
MaxRequestWorkers 100

values from MySQL Tuner:
MyISAM Metrics
[OK] Read Key buffer hit rate: 98.0% (315K cached / 6K reads)

InnoDB Metrics
[OK] InnoDB Read buffer efficiency: 99.47% (64814289 hits/ 65162785 total)

Currently they do not swap on my server. They say "Swapiness" = 1.

@MySiteGuy

What shall they increase and on which level?

Are there also other system swappiness settings, if yes, how are tehy called and which settings would you recommend?

Where do I get the "read hit rate stats in the database" from? Is this something I can see in phpmyadmin? If yes, where?

 

[snoopy5]

How can I find out myself whether memcache is installed already? I do not have a root server. It is a managed server (but no shared server).

My webhoster will install memcache. Which settings shall I use?

(server configuration in my earlier posting - 2 cores and only 8GB of Ram)

 

[VersoBit]

View attachment 226040

If your XenForo installation is not too heavy on ram if you don't go too crazy on addons, you could probably stand to shave off a bit of memory from php.

We get away with 384M with around 25 addons installed.

Best thing you can do is calculate the amount of ram each php worker is using and adjust it to what your sites are actually using.

I would suggest looking to use NGINX and PHP-FPM, you'll get a much better bang for your buck from performance.

 

[snoopy5]

With XF2.19, I really limit myself on addons. Depending on the forum, I have have 10-15 addon installed. Not more.

My webhoster installed memcache now for each PHP version I use. Not all forums use the same phpversion (7.1/7.2/7.3 and 7.45). In the long run I want to have all of the on the latest php version, but this was not possible before upgrading to XF2.x, because of some old addons.

It seems, that some php versions have different memcaches and therefore also different settings.

I upload screenshots.

PHP 7.45:



----------

PHP 7.3:



--------------

PHP 7.2:




Are these settings ok, or do I need to change something? I do not see a value field for the cache...

See also my posting #16 & #17