Optimization Request

[VodkaFish]

My answers to the required questions:
http://www.vodkafish.com/for_vbulletin.txt
Just let me know if I can fill in any other information.

One thing to note is that I have a few plugins installed. One had a portion of it that made me run into a "too many connections" error, so I removed it and haven't had a problem since.

However - I'd like to add vb info and logins to all pages of my site. That should easily double traffic to the mysql db (often more, sometimes less). One time I tried to include the forums global file it didn't take long for me to hit the too many connections error, but I did have the bad code in the plugin active at the time, so it could have just been a coincidence. I do also want to move some of my site stuff over to mysql, so I'd like it to be able to really get things setup right.

Thanks for anything you can suggest.

[eva2000]

Don't worry about output for uname etc that's only for linux

1. Upgrade MySQL server to 4.0.26
2. Upgrade PHP to 4.4.2 if you use phpadsnew make sure to update to phpadsnew 2.0.7 or higher to work with PHP 4.4.1/4.4.2
3. Edit my.ini and restart mysql server afterwards


[mysqld]
basedir=C:/mysql
datadir=D:/mysqldata
safe-show-database
skip-innodb
max_connections = 650
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 60
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

[mysqlhotcopy]
interactive-timeout

[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-nt.exe


4. If you just upgraded to vB 3.5.x try to disable these 4 options:

Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?

Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums

Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread

Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search

i'd remove all plugins and run above suggested changes for a few days and see how it goes, then slowly one at a time add each plugin back and test over a few days then add next plugin to see how it goes

[VodkaFish]

I'll do these things minus the disabling of the plugins, my users rely heavily on some of them, but they've been pretty low-intensive (I think). I don't have those other options on except for display logged in users on the home page (another thing they love). Still, I think the upgrading of everything else (and a new my.ini file, yay) should help :)

I wanted to ask one thing about my server:
MySQL version: mysql 4.0.25 (php says Client API version is 3.23.49 though)
Is this normal? If not, what can I do to correct this? I plan on upgrading mysql, then php, if it makes a difference. I use the php zip package, if it makes a difference.

[eva2000]

PHP reports the mysql client libraries not mysql version

client libraries like mysqladmin, mysqldump, mysqlcheck, etc

to tell PHP to use the latest mysql version's client libraries you need to recompile from source/reinstall php with the --with-mysql=/path/to/mysql option to tell PHP to use the latest mysql 4.0.25 client libraries

i.e. if mysqldump/mysqlcheck/mysqladmin are located at /usr/bin/ or /usr/local/mysql/bin then you need to compile PHP with --with-mysql=/usr or --with-mysql=/usr/local/mysql

[VodkaFish]

Perhaps a silly question, but is it safe to assume I could benefit by having php use the latest client libraries, especially when it's a full version difference?

[eva2000]

yup i always would recommend using latest client libraries...