Optimal Preformance

[Jake]

Hello,

We recently installed vBulletin at http://www.wrestlingtalks.com and are having some fairly large problems with the speed of this software. I know it should be very fast, even with the amount of activity on our forums, but it is taking around 5 minutes just to post something.

What settings should we have in PHP4/mySQL for the best preformance? We are dying here :) Thanks,

[werehere]

Help us out a little by giving server/MySQL/PHP specs. It would be helpful if you posted a link to phpinfo, and MySQL variables, and any other info that would be helpful.

[Jake]

Well we are running PHP4, the latest version of mySQL. We aren't on a shared server, so nothing else is slowing us down. 512 Megs of RAM.

I dont know much about our mySQL variables as our server admin set everything up for us.

But if there is something that is obvious as to why it would be running slow that would help.

Ill try to get more info soon though. Thanks,

[werehere]

It would be nice to see what the MySQL variables are set at. You can do this by using telnet, or phpMyAdmin, and then posting the variable setting here.

Also you can give us a lot of server specs by putting this in a file and name it phpinfo.php


<?php

phpinfo();

?>


Then give us the link.:)

[Jake]

Getting the mySQL vars as I type this, just establising a connection through ICQ... Ill get a link up to that in 5 minutes. Thanks.

[Jake]

Ok, here is a link to the mysql variables: http://www.wrestlingtalks.com/mysqlvars.txt

And here is one to php info: http://www.wrestlingtalks.com/phpinfo.php

Thanks again!

[werehere]

Keep in mind that I am still learning how to optimize all of these variables myself, but yes your forum does seem extra slow.

I *personaly* would up PHP ram in the PHP.ini file to 32MB.

I would also make some of these changes to MySQL:

key_buffer=20M
max_connections=200 (your forums seem to be to busy for 100)
sort_buffer=4000000


Like I said, I am still learning this myself, but my forums did speed up considerably when I changed those around, so you may have to change them a little to work with your forums. Hopefully someone will come along with some helpful hints about speeding up apache now:)

[Jake]

Great, thanks so much. We know absolutly nothing so we will give that a try :) If anyone else knows of anything feel free to share. Thanks,

[werehere]

Your welcome.:)

Well I am sure some of those will help it move, because it appears that you have all default settings, and that would only bottleneck things for a site with a busy database/PHP driven site.

My forum sped up quite remarkably when I had changed those.

[Jake]

Wow, there is definitly a noticable difference now :) Except we couldnt find the PHP Ram in the PHP.ini file, where abouts is that?

[Edited by Jake on 06-12-2000 at 02:37 AM]

[werehere]

Yes it does seem much faster, but there was not as many people on there as when I checked before.


Your php.ini file could be different depending on your install I believe, but check out the /etc/httpd directory. Changing that one should make a big difference as well.:)

[werehere]

I dont think I read your post well enough:

It is sort of in the middle of the ini file, and should appear something like this:

;;;;;;;;;;;;;;;;;;;
; Resource Limits ;
;;;;;;;;;;;;;;;;;;;

max_execution_time = 30 ; Maximum execution time of each script, in seconds
memory_limit = 83886080 ; Maximum amount of memory a script may consume (8MB)

[Jake]

Ok thanks. We are having bigger problems now, when we go into a forum on the main page it doesnt recognize it. And no one can log in :( Damn, all of a sudden this happened.

[Edited by Jake on 06-12-2000 at 02:55 AM]

[werehere]

I cannot seem to get in past a database error at all now, but I was a few minutes ago.

If you have telnet access, then can you restart mysqld?

You can do that by issuing this command "mysqladmin shutdown", or possibly "killall -15 mysqld &" then restart it by typing "mysql.server start", or you should be able to just use "safe_mysqld &". You may need to be in the directory with mysqld for that to work.

Restarting the daemon may fix this database error that I am now getting when I go to your site:

Access denied for user: 'root@localhost' (Using password: NO) in admin/db_mysql.php on line 29

[Jake]

Ya, we shut it down to fix it. I dont know what we did, but it worked :)

[werehere]

It appears to be back up and running fine now?

[werehere]

What did you switch? :)

[Jake]

I really dont know :) It had something to do with the php.ini file. But we replaced it and changed it to what you said above and seems to be running fine now!

[JimF]

Jake, if you don't mind me asking...

I noticed in your phpinfo file that the remote address of your server was a node on the @home network. I was curious how you got this to work; did you get a DNS to point to your machine? The @home TOS clearly state that you are not allowed to use the service for web serving. I had thought about doing something like that, but didn't want to get shut down (PLUS I DIDN'T WANT TO CHEAT THE OTHER @HOME CUSTOMERS); they haven't bothered you about it?

Just curious, thanks

[Jake]

I have no idea. Ill ask our admin about that though.

[Jake]

Hi Jim,

I talked with our admin and we have no connections with @home at all. Its very strange why that would be showing up as the remote address as that is actually his laptop. Which we obviously wouldnt use since it isnt turned on all the time. I dont know why it is showing up as the remote address though. But we run all of our own servers and do not leech off of @home in any way.

[fodder]

I had a look at a couple of things. Then I added some
indexes on the mysql database ...

alter table forum add index (displayorder);

alter table forumpermission add index (forumid);
alter table forumpermission add index (usergroupid);
alter table forumpermission add index (canadminedit);

alter table post add index (threadid);
alter table post add index (username);
alter table post add index (userid);
alter table post add index (dateline);

alter table session add index (userid);
alter table session add index (location);

alter table thread add index (lastpost);
alter table thread add index (forumid);
alter table thread add index (lastpost);

This made a HUGE difference in performance !!!

We've also given the following to mysql on startup ...

$bindir/safe_mysqld --user=$mysql_daemon_user --pid-file=$pid_file --datadir=$datadir -O key_buffer=200M -O max_connections=200 -O sort_buffer=4M -O table_cache=64 -O record_buffer=4M

We're also running PHP4 with the optimiser.

Our forums db is about 300M ( and growing ) and seems reasonably fast ATM !

Have a go http://forums2.gameplay.com/


David

[Shaman]

OH YEAH.

They didn't add indices. Unreal! Wow, this makes such a huge difference to MySQL usually...

[fodder]

It made a rather large difference to us

I'm playing with a couple more mysql params and
tuning the number of apache handlers

I'll let you know how it gets on

By the time you read this message we'll probably have 700,000 posts .... :-)

David

[bira]

well I gave your suggestion a try. The BB was never slower as it has been for the 24 hours since I added those indexes... so I just dropped them, back to the original state.

[Jerry Davis]

Just wanted to add that we made the index additions fodder suggested above and it made a HUGE difference in performance as well. We went from a test SQL statement taking 9.68 seconds to execute to 0.02 seconds to execute.

If you have a large vBulletin installation, the SDMB (http://boards.straightdope.com) has 500,000 posts and 25,000 threads, I'd highly recommend making the change.

[fodder]

What version of MySql are people using ?

I'm using the latest stable release but i'm thinking about
going to the latest version

opinions ?

fodder

[Me2Be]

Fodder,

Where do I put these indexes?

[Chris Schreiber]

Originally posted by Me2Be
Where do I put these indexes?

Me2-

I just copied all of the "alter table" lines from this post, and put them in a file called "addindex.sql". Then from a telnet prompt just do "mysql -u root -p forum < addindex.sql" and all of the new indexes should now be there.

Just an added note.. I noticed another posting about cleaning up the database design for vBulletin, will these new indexes (hopefully) be in there as well?

Thanks,
-Chris

[Me2Be]

Originally posted by Chris Schreiber
I just copied all of the "alter table" lines from this post, and put them in a file called "addindex.sql". Then from a telnet prompt just do "mysql -u root -p forum < addindex.sql" and all of the new indexes should now be there.[/B]

Is there an additional part to this command? I'm getting a "Error 1046 at line 1 : No Database Selected"

Should I be at a specific directory (other than the general mysql one)?

[Chris Schreiber]

Humm no I think that should do it, I have my database named "forum" which is in the command line after the -p parameter, you might need to change this if your database has a different name.

-Chris

[Me2Be]

I'm wondering if I have a screwy copy of MySQL. Everytime I get commands from all of you, none of them seem to work correctly (though Vb installs correctly).

When I added a database field to it,
mysql -uusername -ppassword dbname < addindex.sql

I get the following error:
ERROR 1069 at line 8: Too many keys specified. Max 16 keys allowed.

I guess I should ask this question in the MySQL forum :)

[Edited by Me2Be on 08-01-2000 at 02:23 PM]

[Mike Sullivan]

Just use phpMyAdmin :D

Chris - I would hesitate against including all of those, as some people experienced massive slowdowns. I have yet to try them, so don't ask me :)

[Chris Schreiber]

Hummm... some people got massive slowdowns, while others got performance increases? ;)

I understand adding many indexes increases the overall size of the database and can add overhead to insert/delete operations (since the leaf nodes need to be shuffled around and all...)

I already added them and all seems well, but I do have the change documentated and will keep an eye on things.

Thanks,
-Chris

[fodder]

Chris,

I think it's one of those balancing acts...
If you have a _large_ number of postings ( not boasting
cos it's still slower than we want but..)
ie 1.1 Million then having the indexes ( And a _large_ key buffer and plenty of RAM) then it can help quite alot.

How ever if you don't have more RAM than the keys then
perhaps it can have a negative effect.

I think it's one of those suck it and see things...

We're still trying to get the speed up but try http://forums.gameplay.com/

Fodder

[Chris Schreiber]

Originally posted by fodder
I think it's one of those balancing acts...

Yes it sure is... I think the people that added the indexes and had problems were probably very tight on RAM.... I've actually added these into some other very high volume forums for some other people here and it has made a very nice improvement in speed.

-Chris

[Freddie Bingham]

fodder you should implement the showthread hack that stallion posted in the hacking forum. I think that will help you out immensely.

[VirtueTech]

MySQL said: Too many keys specified. Max 16 keys allowed

How do I fix this?

[Kengan]

I am using a vb 2.03 , sometime my vb very slow. and I have just changed something in CP.

- Add No-cache headers ( No )
- GZIP Output ( Yes )
- GZIP compression level ( 1 )
- Simultaneous sessions limit ( 0 )
- *NIX Load Limit ( 0 )

is there any way can got vb performance increases ?

I am using MySQL 3.22 PHP4 with Sun Unix. Thanks !

[eva2000]

Originally posted by Kengan
I am using a vb 2.03 , sometime my vb very slow. and I have just changed something in CP.

- Add No-cache headers ( No )
- GZIP Output ( Yes )
- GZIP compression level ( 1 )
- Simultaneous sessions limit ( 0 )
- *NIX Load Limit ( 0 )

is there any way can got vb performance increases ?

I am using MySQL 3.22 PHP4 with Sun Unix. Thanks ! wow talk about bring an old thread back from the dead 1 yr + 6 days old the last post before yours :)

performance increase

- upgrade to mysql 3.23.41 and convert tables from isam to myisam table format
- tweak my.cnf file and more memory and linux kernel tweaks
-> http://i4net.tv/marticle/get.php?action=getarticle&articleid=3
-> http://i4net.tv/marticle/get.php?action=getarticle&articleid=7
-> http://www.mysql.com/doc/O/p/Optimizing_the_Server.html
-> http://www.mysql.com/doc/L/i/Linux.html