Major server slowdown with Vbulletin install

[Kenn]

We recently decided to upgrade from UBB because we were having periods of an hour or so a day where the server was unusable due to massive load.

We installed the new board and opened it today, and are having major server slowdown issues:

Our system is a dedicated P2-450 with 512 Meg of Ram, with Linux Redhat 6.1 and the message board is the most important application.

MySQL is Client API version: 3.22.29

PHP is 3.0.16 and is configured as an apache module

Site is http://www.fanforum.com

If we have more than 4 or 5 users, it seems to grind to a halt, and we have begun getting errors about too many users.


When we try to open any page we get:

Warning: Too many connections in admin/db_mysql.php on line 29


And from the command line:

[admin@www news]$ mysql status
ERROR 1040: Too many connections


I have seen people suggesting raising the max number of connections in mysql, but can not figure out how to do that. (We just installed PHP and MySql for this board) Our site owner is to the point of insisting that we go back to the UBB, which I think is a mistake, but I would greatly appreciate any suggestions anyone may have for getting things working.

[Martin]

the default is 100.

the command is:

mysqld -o max_connections=whatevernumber

on NT you have to stop mysql before doing this, then restart it after

also, if there is a configuration file for mysql on linux you can put that under the [mysqld] options so it's automatic at startup. otherwise if your server stops for any reason, you will have to redo it.

I hope this helps a little...

[Edited by Martin on 05-23-2000 at 09:08 PM]

[werehere]

You also do not sound like you have root access to Mysql! It allows you max_connections+1 so root always can get in, so you may need to contact your host, or whoever has root to change this variable. Maybe not, but maybe so.


Martins syntax should work fine, so try that right away, and look for a post in this forum by me, that is where John gave a couple of PHP/MYSQL specs that you can go from.

[werehere]

Here is the thread:

http://www.vbulletin.com/forum/showthread.php?threadid=300


Look at the first post by John, that should speed things up for you.

[Kenn]

Thanks a lot for the suggestions. They seem to have helped, but only a little. We upped the max connections to 400, and no longer get the too many connections error. And we tripled the value of the tmp_table_space variable in mysql.

And increased the max_memory in PhP to 16 Meg, and turned off magic quotes.

The board works with 1 or 2 users, but after that it still seems to bog down incredibly. Much worse than the way the UBB was working. I've noticed some discussion about mysql maybe not having enough bandwidth? We have a dedicated server, and the boards are the main application though.


I did a mysqladmin status, and don't really know what the output means for slow Queries etc. Is there anything that anyone knows of to look at to see if SQL is just not running right for us or something?

[root@www bin]# mysqladmin status
Uptime: 296 Threads: 112 Questions: 6511 Slow queries: 351 Opens: 1876 Flus
h tables: 1 Open tables: 63
[root@www bin]# mysqladmin status
Uptime: 313 Threads: 115 Questions: 6883 Slow queries: 380 Opens: 2181 Flus
h tables: 1 Open tables: 64


Our site decided to reinstall the UBB today since we had been essentially down for the last couple of days, but I'm going to keep an experimental forum open, just not as the main application. I really want to get this board up and running.

Once again, any suggestions would be greatly appreciated. I really am impressed with the way the Vbulletin user community seems to come together and try to help out, and our site is looking forward to joining up!

[wandrer]

That's kinda funny, because I have been able to sustain ~6.0 - 8.0 connections per second on a P2-300 (duel processor) with my testings and I have never gotten a 'slow query'... There is definately something wrong with the mysql setup...

UBB is 1/6th the speed of wwwthreads, and with the latest PHP and mysql, vbulletin could be a lot faster than wwwthreads...

vbulletin also uses about 10% of the cpu usage as UBB with the same traffic/user levels...

Check your mysql settings cause there is something wrong there...

[Martin]

What are your mySQL configuration settings? Is everything except what you mentioned set at default?

Have you increased the amount of RAM allocated to mySQL iteself?

I'm running beautifully on my BETA right now with 512Mb.

Also, they just released PHP 4.0.0, which is a lot smoother and less hungry than PHP 3.x

[werehere]

Yeah, I never have any 'slow query' either.

[Kenn]

Thanks a lot for the input! It is actually a big help knowing that the slow query is something that is unexpected. We have pretty good tech support, but about all I have been able to tell them so far is that "its not working". At least now I can ask why we are getting slow querys. I think I'm learning much more about SQL and PHP than I thought I would, but thats a good thing!

We are using all defaults except the values I mentioned above.

[Kenn]

After making all of the changes that were suggested in various posts, we seem to be up and running, and the boards are actually loading and working. We are still getting some slow queries though, and I'm worried that when people start waking up and loading us down that things will fall apart again. I thought maybe I would print our settings here in case there is anything obviously wrong that some kind soul might point out! Sorry for the length of this message.

+----------------------------+---------------------------+
| Variable_name | Value |
+----------------------------+---------------------------+
| back_log | 5 |
| connect_timeout | 5 |
| basedir | / |
| datadir | /var/lib/mysql/ |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer | 131072 |
| flush_time | 1800 |
| key_buffer | 134213632 |
| language | /usr/share/mysql/english/ |
| log | OFF |
| log_update | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| max_allowed_packet | 1048576 |
| max_connections | 200 |
| max_connect_errors | 10 |
| max_delayed_insert_threads | 20 |
| max_join_size | 4294967295
| max_sort_length | 1024 |
| max_write_lock_count | 4294967295 |
| net_buffer_length | 16384 |
| pid_file | /var/lib/mysql/www.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 2093056 |
| skip_locking | ON |
| skip_networking | OFF |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer | 2097144 |
| table_cache | 128 |
| thread_stack | 131072 |
| tmp_table_size | 4194296 |
| tmpdir | /tmp/ |
| version | 3.22.29 |
| wait_timeout | 28800 |
+----------------------------+---------------------------+

[Kenn]

Something is still definately really wrong with our board at http://www.fanforum.com During the night, things seemed to be going well, and we were averaging 11 or so active users. During the day though, it has brought the server to a crawl again, and we only have about 17 active.

The mysqladmin status is showing a huge number of slow queries again.


Uptime: 10481 Threads: 136 Questions: 296303 Slow queries: 22007 Opens: 5091
Flush tables: 1 Open tables: 127

Is there anyone else running the board on linux (or that just understands MySQL better than I do) that could offer some suggestions?

Thanks in advance!

[wandrer]

Followup:

I spent an hour or so on the phone/telnet with Kenn trying to help him with the site.

The databases didn't have any errors. vBulletin & mySQL wasn't reporting anything stange. We changed a few settings to give mySQL 3x-4x more memory, but it came down to (IMHO) needing a faster processor. The rest of the board was running great, but the 5-15 mysql daemons were requiring almost all the CPU time trying to process the vast number of queries by the people visiting the website.

[bigsoccer tech]

imo, buy more ram. sorry. we have to also.

[wandrer]

I don't believe that it was a case of 'more ram'.

The server was already at 512MB with 400MB being used (60-120MB free) with only 10-20MB of the 500MB swap space used. Each mySQL daemon was using about 35MB apiece, but it was still suffering because of the great amount of traffic.

From looking at the forum, the total amount of space the databases were taking up was around 85MB, so we tried to give the mysql daemons as much memory as possible to keep as much of the database in memory as needed. (using the guidelines in the mysql documentation).

We were able to lower the load, but not enough to make any real difference.

Next step - more processing power.



[Edited by wandrer on 05-30-2000 at 11:15 AM]

[Kenn]

We are having our processor upgraded tomorrow to a P3-700. The server company can do that with only an hour downtime (so they say) I'll post back and let everyone know how it goes after. Thanks again for all the help and suggestions.

[Kenn]

We got the server upgraded, and it didn't help in the least. On the other hand, we installed the 1.1.2 release, and now the site is a rocket!!! If we had waited to upgrade, we probably wouldn't have needed the new processor, but damn this board is fast. We have 50+ users on now, and its not even slowing down.

Thanks again for all of the help!

[werehere]

Thanks for letting me know that, I am going to upgrade now myself:)

[Freddie Bingham]

Hey Kenn can I borrow some smilies from your board :)

Also Kenn when I tried to activate my registration for you board, I got a http 404 page not found - just a heads up.

-freddie

[Edited by rangersfan on 05-31-2000 at 09:34 PM]

[Kenn]

Hi Freddie, yeah definately take the smiles that you want! That is fine with us! I will check into the registration probelms shortly. We are just so happy to actually have a moving board again, I want to bask in our speed awhile before we get into fixing the little issues!

[werehere]

Nice forum, and yes it is zippy now :)

Looks like a lot of current users every time I saw it.

[WebStyles]

Kenn,

I was browsing around your board to check the speed (very quick!) and noticed something very strange... In your Off-Topic board you have a Word Association Thread (http://www.fanforum.com/forum/upload/showthread.php?threadid=25634) with 8 pages... In our Off-Topic board, we have a Word Association Thread (http://www.were-here.com/forums/showthread.php?threadid=13719) with 8 pages...

*insert Twighlight Zone theme here*

[werehere]

Yes our server indeed sped up with this upgrade as well :)