Server Optimization Request

[Sparkiller]

1. dedicated

2. server specs:

cpu speed/type single or dual cpus): single 700MHz Pentium3
how much memory installed: 256 MB SDRAM
hard drive type/configuration: 10 GB IDE
linux distributor: Linux SuSE 8.2
apache: apache 1.3.27
PHP version: php 4.3.1
MySQL version: mysql 3.23.55

3. no mysql4

4. mysql was pre-installed

5. Top-Stats:


top - 20:51:00 up 1:00, 1 user, load average: 4.06, 2.50, 2.58
Tasks: 99 total, 1 running, 98 sleeping, 0 stopped, 0 zombie
Cpu(s): 40.3% user, 7.8% system, 0.0% nice, 51.9% idle
Mem: 255140k total, 250728k used, 4412k free, 916k buffers
Swap: 2097136k total, 11028k used, 2086108k free, 104592k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ Command
4204 wwwrun 19 0 17916 17m 15m S 15.0 7.0 0:08.68 httpd
4166 wwwrun 19 0 17748 17m 15m S 8.5 7.0 0:06.76 httpd
4191 wwwrun 19 0 18148 17m 15m S 7.2 7.1 0:09.62 httpd
5145 root 16 0 1024 1024 792 R 5.2 0.4 0:01.04 top
5218 mysql 15 0 16024 11m 5428 D 2.0 4.7 0:00.06 mysqld
4164 root 15 0 1384 1380 1152 S 1.3 0.5 0:03.24 pipelog.pl
4181 wwwrun 15 0 17684 17m 15m S 1.3 6.9 0:05.56 httpd
5113 wwwrun 16 0 16388 16m 15m S 1.3 6.4 0:00.72 httpd
5 root 15 0 0 0 0 S 0.7 0.0 0:18.12 kswapd
4165 wwwrun 15 0 17788 17m 15m S 0.7 7.0 0:04.06 httpd
4187 wwwrun 15 0 17896 17m 15m S 0.7 7.0 0:07.38 httpd
5128 wwwrun 15 0 17212 16m 15m S 0.7 6.7 0:00.40 httpd
5159 mysql 15 0 16024 11m 5428 D 0.7 4.7 0:00.32 mysqld
5203 mysql 15 0 16024 11m 5428 D 0.7 4.7 0:00.18 mysqld
1 root 15 0 88 68 48 S 0.0 0.0 0:08.22 init
2 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration_CPU0
3 root 15 0 0 0 0 S 0.0 0.0 0:00.00 keventd


6. my.cnf


# The MySQL server

[mysqld]

port = 3306

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

skip-locking

skip-networking

set-variable = max_connections=250

set-variable = interactive_timeout=120

set-variable = wait_timeout=120

set-variable = key_buffer=16M

set-variable = max_allowed_packet=1M

set-variable = table_cache=64

set-variable = sort_buffer=512K

set-variable = net_buffer_length=8K

set-variable = myisam_sort_buffer_size=8M

#log-bin

server-id = 1

# Point the following paths to different dedicated disks

#tmpdir = /tmp/

#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables

#set-variable = bdb_cache_size=4M

#set-variable = bdb_max_lock=10000

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/lib/mysql/

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /var/lib/mysql/

#innodb_log_arch_dir = /var/lib/mysql/

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

#set-variable = innodb_buffer_pool_size=16M

#set-variable = innodb_additional_mem_pool_size=2M

# Set .._log_file_size to 25 % of buffer pool size

#set-variable = innodb_log_file_size=5M

#set-variable = innodb_log_buffer_size=8M

#innodb_flush_log_at_trx_commit=1

#set-variable = innodb_lock_wait_timeout=50

# The safe_mysqld script

[safe_mysqld]

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

[mysqldump]

quick

set-variable = max_allowed_packet=16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[isamchk]

set-variable = key_buffer=20M

set-variable = sort_buffer=20M

set-variable = read_buffer=2M

set-variable = write_buffer=2M

[myisamchk]

set-variable = key_buffer=20M

set-variable = sort_buffer=20M

set-variable = read_buffer=2M

set-variable = write_buffer=2M

[mysqlhotcopy]

interactive-timeout



7. mysql extended-status

http://projektstarwars.de/mysqlinfo.php

8. Currently the only thing on the server

9. Average 40-50 user, maximum 100-110

10. http://www.projektstarwars.de/phpinfo.php

11.
Timeout 120

KeepAlive On
MaxKeepAliveRequests 0
KeepAliveTimeout 15
MinSpareServers 8
MaxSpareServers 32
StartServers 8
MaxClients 256

12. vbulletin 2.3.0

[Sparkiller]

Problem is, that the forum is mostly lightning fast, but, as an example, if i want to see a users profile it pauses for about 15 seconds and then loads the page instantly.

It's hard to describe in english. The forum behaves as if it has a... hicup?

[eva2000]

a few things in this order

1. upgrade apache to 1.3.29
2. upgrade mysql to 4.0.18
3. upgrade php to 4.3.4
4. install Turck Mmache http://www.vbulletin.com/forum/showthread.php?t=75878
5. in httpd.conf reduce maxclients from 256 to 125 and restart apache
6. if you use mysql 4.x use below /etc/my.cnf and restart mysql

[mysqld]
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[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

if you use mysql 3.23.x use below /etc/my.cnf and restart mysql

[mysqld]
set-variable = max_connections=500
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=2M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=64
set-variable = wait_timeout=1800
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[safe_mysqld]
open_files_limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M


note: mysql 4.x series i.e. 4.0.18 is ALOT faster than mysql 3.23.x

[Sparkiller]

Thanks for your help,
also already applied some of your suggestions.

Currently there is only one problem left. While the forum homepage and all threads are loading pretty fast, actions like "edit post" and "view user profile" take sometimes more 30 seconds (browser message at this time is "waiting for server"), or even result in a "page not found" error. While doing this, the server load increases very much.

Is it perhaps a known issue with vb 2.3.0, or do you know what could be causing it? Too few memory, perhaps, since it is a board with about 500.000 postings?

Thanks again for your help!

[eva2000]

hard to say without knowing which of my suggestions you applied and which you skipped ;)

But i'd upgrade to vB 2.3.5 due to security and other bug fixes first. Systematically eliminating each possible cause for the problems helps hence why i suggested the above steps to perform.

As to lack of memory etc, your mysqlinfo url http://projektstarwars.de/mysqlinfo.php is no longer valid so can't see your current live server mysql usage stats to determine what's what

[Sparkiller]

Hello eva2000,

the re-uploaded the mysqlinfo.php:
http://www.projektstarwars.de/forum/admin/mysqlinfo.php

I applied the apache- and mysql-upgrade,
and edited the httpd and my.cnf to fit your suggestions.
(Being a serveradmin-newbie, i still have to find out how to update PHP itself.)

Btw, the best way to simulate the problem, is to perform a search. CPU load always goes up the roof this way. And i noticed that joining two threads also seems to have a problem, since after executing this task it takes about 30 seconds (or more) before i end up with a completely white page and one thread deleted, but not copied to the other one.

PS: The Server will get an upgrade to 512 MB in a few hours, perhaps this will do the trick.

[eva2000]

mysqlinfo reports cpu load very lite right now.. so the 512MB upgrade helped right ? if not you could be suffering from table locking issues in mysql...

[Sparkiller]

mysqlinfo reports cpu load very lite right now.. so the 512MB upgrade helped right ? if not you could be suffering from table locking issues in mysql...
The forum works fine, and only starts behaving strange when performing certain tasks. As an example, check the cpu load while doing a search:

http://www.projektstarwars.de/forum/search.php

But i'll also take a look at the possible table locking issue.

[eva2000]

it's probably the search and mysql table locking if you're still using vB 2.3.x series.. vB3 would handle it alot better