Help with VPS configuration

[ibenick]

I recently added another forum to my VPS and traffic on both forums has been picking up. It's not uncommon to see 100+ users on at at time between the two sites. Things have been starting to slow down at times and I've had some database connection issues so I asked my host to look into it and they suggested the changes below.

I am totally clueless on how these changes can effect my XF installs so I'm hesitant to just tell them to go ahead with the changes. Any input would be much appreciated.

Side note - I don't think posting any of this info publicly is any kind of security vulnerability, but if it is, I'd love it if someone would let me know.

The issue may be that there is simply not enough memory to keep the mysql and
apache running on the server. The mysql is currently configured to use 62% of
the memory on the server, so if there are times of higher traffic on
mysql-heavy sites, this could easily cause slowness.


It might be best to adjust the mysql to the following settings:

--

[mysqld]

key_buffer = 16M (currently 128M)

thread_cache = 2 (currently 8)

sort_buffer_size = 512K (currently 2M)

net_buffer_length = 8K

read_buffer_size = 256K (currently 2M)

read_rnd_buffer_size = 512K (currently 2M)

myisam_sort_buffer_size = 8M (currently 64M)

query_cache_size = 8M (currently 32M)

max_heap_table_size = 32M (currently 48M)

tmp_table_size = 32M (currently 48M)

interactive_timeout = 150 (currently 300)



[isamchk]

key_buffer = 16M (currently 96M)

sort_buffer_size = 10M (currently 64M)



[myisamchk]

key_buffer = 16M (currently 96M)

sort_buffer_size = 10M (currently 64M)

--

 

[SneakyDave]

If you make those types of changes, it would probably cause other database problems, especially if you have 100+ connections at any given time, but it won't hurt to try them out if you/they make a backup of your my.cnf file first. I don't think their suggestions for the MyISAM changes would help that much, as most of xF's tables are InnoDB, not MyISAM

How much memory do you have for your VPS right now?

Something I plan to do in the future for one of my VPS' is to use one server for MySQL, and one for the web server. That might be an option for you if it's in your budget.

Also, I'd like to see what you have set for max_connections.

One thing I would possibly suggest is to move to nginx or a lighter weight web server, if you don't have a lot of memory to spare on your vps.

 

[ibenick]

Thank you. I'll find out what my max_connections is set to. My VPS is 1GB from Liquid Web, do I need more? I suppose my traffic has probably doubled on it in the last two months.

 

[SneakyDave]

It depends on what you do with it, your memory. I would guess that if you switched to nginx, you'd easily be able to handle that load. Are both the sites you mention xF sites, or are there other things installed?

If your host suggests it, then you could try it out. I don't have much of an opinion on LiquidWeb, but at least they gave you some suggestions.

 

[ibenick]

Both sites are XF and I have a couple of small, low traffic wordpress sites on there as well. I'm all about switching to something like nginx but I'm not server-savvy enough to handle all of that so I would need my host to manage it. And with all the trouble I've gone through getting XF to work properly with my host, that scares the bejeebus out of me just thinking of trying to switch it to something new.

 

[ibenick]

Strange thing though... the slow times have just started hitting the last couple nights around this time. In fact, my entire VPS has been down for the last 15 minutes or so and still not up. Never had downtime with it before...

 

[SneakyDave]

I'd probably just try their MySQL configuration changes then. To tell you the truth, I've never tried to lower my MySQL configuration sizes to see what would happen.

You might want to google some of those MySQL parameters they want to change. Some people will tell you to use a formula to come up with them, based on your memory footprint. Your host may have already done that.

I think some of those values are based on the amount of memory allocated to the parameter, times the number of max connections you accept.

 

[SneakyDave]

Do you have munin running on the server to take a look at performance graphs? Is your VPS running backups during this time frame, or database backups in particular?

When you say your VPS is down, is it not available, or an error message, or mysql crashed, or CPU usage is high, or ????

 

[ibenick]

It was down as in not available. I could get into my panel and it said the status was "http not responding". The support guy could't even access it and said that there was a load spike that may have caused it.

I have a control panel that shows graphs with bandwidth, load average, etc. It all looks okay to my untrained eye. Well except for that last hour...

Load average:

 

[ibenick]

It came back up right before that post, BTW.

 

[Ghan_04]

A 1 GB VPS should be able to handle that load with no problem provided it has enough CPU power and disk speed.

- What is actually causing the slowness? The graph you have there shows high load, which might hint at swap space is getting heavy use. If that's the case, check to see what is actually using memory other than MySQL when the load is high like that. I'm betting that Apache is eating your RAM. I try to avoid Apache at all costs - I usually run Litespeed for .htaccess compatibility or nginx if I hit the connection limit on Litespeed's free version.

- How much traffic is the DB actually getting? Can you check some relevant stats like average queries per second and/or number of slow queries?

- Check top to see what is using your resources.

Hopefully this helps track down your issues. If you want, I'd be happy to take a look at your server myself and see what I can find out.

 

[ibenick]

I've sent those questions on to my host to see if I can get them answered. Thanks for the help. So does anyone think their suggested configuration changes listed above will actually hurt my performance? I found out my max_connections is set to 50.

 

[Ghan_04]

Your current my.cnf settings should be just fine for a 1 GB VPS. I don't really see changing them to the suggested value as helping you out that much.

 

[ibenick]

Ugh... not having any luck with this. Things seem to run okay most of the time but every now and then it crashes. Last night I noticed about 80 users online on one of my forums but only about 20 on the other, then it all went down again, 'http not responding'. My host turned on 'piped logging' to try to figure out what's going on but it seems like unless the server is actually down when they look, they don't care. Hell, even if it is down, by the time they get to it it's back up and they just ask me if I'm still having issues. I say YES, this is happening a lot and crickets. Maybe it's time to get a new VPS provider?

Does anyone have any other suggestions on how I can get this setup to quit bogging down and crashing on me? My traffic is growing like crazy right now and I don't want to bomb the opportunity by not having a functional site.

 

[shawn]

What about your apache config?

What extensions are enabled? Probably a whole bunch of them you aren't using. What are the max connection settings? Timeout settings, etc?

Take a look at TimeOut, MaxKeepAliveRequests, KeepAliveTimeout, ServerLimit, MaxClients, MaxSpareThreads, MaxRequestsPerChild, etc. Go down to the LoadModule section and comment out any ones you're not using. I'm no expert on these, but there are some guides around that can help.

Their my.cnf recommendations look good to me. You're not using a lot of ISAM tables, as others have noted... but if/when you do, mysql will try to grab less ram for those queries.

 

[Ghan_04]

Have you checked the syslog to see if there are any interesting errors there?
Also, using Apache on a 1 GB VPS is almost always a bad idea. Nginx would likely be a lot better here. Unfortunately it's tough to say without knowing what exactly is causing the server to crash.

 

[ibenick]

Working with the tech support at my provider to try to figure this out and I've just been pointing them to this thread.

One question though, do you guys think the 1GB VPS might be less sufficient if my two XF installs are heavier on images and queries than a regular install?

This is what I'm dealing with, very visual, lots of queries:

http://backcountrypost.com
http://canyoncollective.com

 

[Ghan_04]

Images shouldn't be an issue unless you have something like Varnish or another caching mechanism that is trying to cache all of them and you aren't controlling the memory usage on it.
With queries, it depends. Are these just Xenforo queries? If so, it shouldn't be any trouble - they are generally pretty optimized. Though this sometimes depends on your database size. Most of your RAM should go toward MySQL if you're just running a standard Xenforo install with MySQL/PHP/Webserver

 

[hellreturn]

Have you checked the syslog to see if there are any interesting errors there?
Also, using Apache on a 1 GB VPS is almost always a bad idea. Nginx would likely be a lot better here. Unfortunately it's tough to say without knowing what exactly is causing the server to crash.

That and leaving security loopholes without proper config.

 

[hellreturn]

Working with the tech support at my provider to try to figure this out and I've just been pointing them to this thread.

One question though, do you guys think the 1GB VPS might be less sufficient if my two XF installs are heavier on images and queries than a regular install?

This is what I'm dealing with, very visual, lots of queries:

http://backcountrypost.com
http://canyoncollective.com

1Gb VPS should work fine I believe but get VPS on very good network.. or use CDN services. Just get SSD or SSD cache VPS.