Help - server load through the roof since upgrade

[JanetGeorge]

My forum was upgraded last night from 2.1.7 to the latest version (very painlessly thanks to Josh :flowers: ) but since then server load has been through the roof and MySQL (too many connections) errors are occurring non-stop. I've turned off search (the usual hog) but it hasn't helped.

Any thoughts?? I though that the main feature of the new version was extra speed - and it did seem faster this morning - but since lunch time it's been dire.

[Wondering Soul]

By through the roof, how high is it peaking and for how long?

Also, are you on a dedicated or shared hosting?

[JanetGeorge]

It's a dedicated server (although a bit old and about to be replaced). Server load was getting above 25 and staying there for 15-20 minutes - with 260 odd processes running (hence, too many connections errors.) I think PART of the problem was Sendmail going a bit ape part of the time, so I shut IT down for a while and the server load/connections were still very slow coming back to normal. And SWAP was double what it's been in months.

[AndyF]

Topic moved to "Server Management, Resources and Optimization" :)

[bfarber]

This would largely depend on the size of the site and the config.

What kind of server specs do you have and how many people are online during these peaks?

[rct2·com]

I know it doesn't help any, but we found that server load was significantly lower after a v2.1.7 to v2.3.4 upgrade.

[JanetGeorge]

This would largely depend on the size of the site and the config.

What kind of server specs do you have and how many people are online during these peaks?

It's a Celeron 1.3ghz with 1.5GB Ram - we rarely have more than 120 subscribers on-line at any one time although we can have a lot of guests - usually anything from 150 to 300. But guests can't DO much - they can browse one or two forums but have to register for read priveleges and subscribe to post or do anything else.

The server IS old and has a few problems not connected with IPB - but the upgrade does seem to have made it worse at some times (not always - it's running fine now with 145 guests, 52 members, 10 anonymous members - and it looks like:

7:44am up 11 days, 7:44, 1 user, load average: 1.16, 1.42, 1.75
145 processes: 139 sleeping, 6 running, 0 zombie, 0 stopped
CPU states: 49.7% user, 7.5% system, 0.0% nice, 42.7% idle
Mem: 1540212K av, 1524656K used, 15556K free, 0K shrd, 115820K buff
Swap: 2048276K av, 243552K used, 1804724K free 788960K cached

But last night it was FAR worse - with Swap up to 600000 and load averages 15-20 - with similar numbers of users online.

ETA - the database is about 1.1 GB - it's regularly whecked and optimised and everything seems ok.

Another ETA:
The server I'll be moving to is:

Intel 1.86 GHz 1066FSB - Conroe Xeon 3040 (Dual Core) with 2 GB RAM - I'm starting to wonder if IT's big enough?

[bfarber]

Honestly, you're pushing the limits of the server. I can't imagine how 2.1.7 would have been much better, but that's neither here nor there.

You have roughly 200 people online in your example, and you have 15MB memory free. MySQL can spike at times when it needs to, variable based upon the my.cnf configuration. What if someone does a search, needing to generate a temp table? How big is key_cache set to - if it's not being fully used, you could easily consume the remainder of the 15MB memory you have free. When you start swapping after all the memory is used up, the site will certainly slow to a crawl (swap is very very slow).

When this happens is going to depend on what the users are doing in this situation. There are ways to "tweak" the settings and what not, but honestly your best bet is to up the memory a bit. Your new box you said will have 2GB RAM - if you can get 4GB you'll be good to go for a while I'd say, though with the right settings you may be able to get by on just 2GB.

[JanetGeorge]

Honestly, you're pushing the limits of the server. I can't imagine how 2.1.7 would have been much better, but that's neither here nor there.

This is my.cnf - I'd very much appreciate any suggestions for tweaking as I have to survive the next few weeks before I can get the move to the new server organised. I spoke to my server provided and got a good deal on an extra 2GB RAM - I sure don't want to move and run into problems too quickly!

[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
set-variable = thread_stack=192k
query_cache_limit=16M
query_cache_size=128M
query_cache_type=1
query_prealloc_size=1M
max_connections=64
interactive_timeout=100
wait_timeout=15
connect_timeout=25
thread_cache_size=20
key_buffer=64M
join_buffer=1M
max_allowed_packet=16M
tmp_table_size=64M
table_cache=384
record_buffer=4M
sort_buffer_size=4M
read_buffer_size=4M
max_connect_errors=10
thread_concurrency=2
myisam_sort_buffer_size=32M
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]

err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout
[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock


Would also appreciate advice on the best way to use the two drives but will post a seperate thread for that.

[bfarber]

First thing I'd try is to change key_buffer=64M to key_buffer=512M. This is arguably the most important variable when using MyISAM tables (which is recommended). You should set it between 25 and 50% of the total system memory generally (just be aware of what else is going on on the same box...)

Remember to restart mysql after making any changes to the conf

[Rheddy]

You know, this is exactly the same problem I was posting about some months ago. After I had upgraded, I also noticed higher server loads. It seemed that with updated versions of the IPB software, something must have happened with the code for IPB to cause the forum software to operate at higher server load capacity.

[JanetGeorge]

Actually, bfarber's suggestion has improved mine considerably - I DO still have problems but I think it's the rather old, useless server I'm on and Sendmail problems too. Hopefully, when I move to the new server, all will be well.

[rct2·com]

Hmm, this has prompted me to look in /etc/my.cnf on my server. It contains only 14 lines! I suspect that mysql is using a different cnf file. Does anybody know a command to find out the parameters that mysql is actually running and/or which .cnf file it is using?

Edit: nvm I found mysql is running as:

/bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid

So it is definitely using the small file. How do I find out what size buffers etc it is trying to use?

[Rheddy]

Is there a guide that shows how to do this? SOP that those of us who don't know how to do this have a guide so that we can do this ourselves?

[JanetGeorge]

So it is definitely using the small file. How do I find out what size buffers etc it is trying to use?

Using SSH, I use cat /etc/my.cnf to read - or
pico /etc/my.cnf to edit (back up first and be careful!)

[rct2·com]

Yes but all it contains is paths

[mysqld]

safe-show-database

innodb_data_file_path=ibdata1:10M:autoextend

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

old_passwords=1


[mysql.server]

user=mysql

basedir=/var/lib


[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid