Server Optimization Help - Please and Thank you.

[std]

Hi!

Looking for some help with server optimization. We just switched from shared to dedicated, so I'd like to make sure everything is in tip-top shape.
Here goes:

1. Dedicated or shared virual server?
--> Dedicated (ServerMatrix Superserver 2.8)

2. Server specs:
Single P4 2.8 GHz
1024MB RAM
80GB EIDE non raid (and a 40GB EIDE for backups)
Redhat Enterprise Linux 3
Apache 1.3.29
PHP version: php 4.3.4
MySQL version: mysql 4.0.18

3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server?
--> I don't think so. I don't know what this is!

4. if possible how mysql was compiled/installed
--> RPM

5. your top stats
18:55:47 up 5 days, 53 min, 1 user, load average: 0.10, 0.04, 0.03
83 processes: 81 sleeping, 1 running, 1 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 100.0%
Mem: 1022480k av, 985052k used, 37428k free, 0k shrd, 63328k buff
751236k actv, 536k in_d, 20324k in_c
Swap: 1052248k av, 35620k used, 1016628k free 760880k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1 root 15 0 116 84 60 S 0.0 0.0 0:03 0 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd/0
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 bdflush
5 root 15 0 0 0 0 SW 0.0 0.0 0:01 0 kswapd
6 root 15 0 0 0 0 SW 0.0 0.0 0:01 0 kscand
8 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kupdated
9 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
13 root 15 0 0 0 0 SW 0.0 0.0 0:06 0 kjournald
69 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 khubd
579 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
580 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
3476 root 15 0 256 232 176 S 0.0 0.0 0:00 0 syslogd
3480 root 15 0 184 172 132 S 0.0 0.0 0:00 0 klogd
5066 root 15 0 736 344 256 S 0.0 0.0 0:00 0 cupsd
5103 root 15 0 448 316 208 S 0.0 0.0 0:00 0 sshd
5117 root 15 0 400 356 288 S 0.0 0.0 0:00 0 xinetd
5126 root 15 0 592 524 388 S 0.0 0.0 0:00 0 antirelayd
5149 mailnull 15 0 416 248 176 S 0.0 0.0 0:00 0 exim
5153 mailnull 25 0 232 4 0 S 0.0 0.0 0:00 0 exim
5158 root 15 0 596 536 388 S 0.0 0.0 0:00 0 antirelayd
5171 root 19 0 16496 4 0 S 0.0 0.0 0:00 0 spamd
5180 root 15 0 312 308 236 S 0.0 0.0 0:00 0 crond
5222 xfs 15 0 1912 64 36 S 0.0 0.0 0:00 0 xfs
5417 named 25 0 3780 3540 724 S 0.0 0.3 0:23 0 named
5484 root 25 0 1196 4 0 S 0.0 0.0 0:00 0 ipalert_statd
5503 root 25 0 64 4 0 S 0.0 0.0 0:00 0 portsentry
5529 root 23 0 48 4 0 S 0.0 0.0 0:00 0 mingetty

6. 'mysqladmin variables' output. Note: the my.cnf file had only two lines in it. Do I need to make a my.cnf file with more specific settings?
| Variable_name | Value
+---------------------------------+---------------------
--------------------------------------------------------
| back_log | 50
| basedir | /
| binlog_cache_size | 32768
| bulk_insert_buffer_size | 8388608
| character_set | latin1
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
| concurrent_insert | ON
| connect_timeout | 5
| convert_character_set |
| datadir | /var/lib/mysql/
| default_week_format | 0
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 0
| ft_boolean_syntax | + -><()~*:""&|
| ft_min_word_len | 4
| ft_max_word_len | 254
| ft_max_word_len_for_sort | 20
| ft_stopword_file | (built-in)
| have_bdb | NO
| have_crypt | YES
| have_innodb | YES
| have_isam | YES
| have_raid | NO
| have_symlink | YES
| have_openssl | NO
| have_query_cache | YES
| init_file |
| innodb_additional_mem_pool_size | 1048576
| innodb_buffer_pool_size | 8388608
| innodb_data_file_path | ibdata1:10M:autoextend
| innodb_data_home_dir |
| innodb_file_io_threads | 4
| innodb_force_recovery | 0
| innodb_thread_concurrency | 8
| innodb_flush_log_at_trx_commit | 1
| innodb_fast_shutdown | ON
| innodb_flush_method |
| innodb_lock_wait_timeout | 50
| innodb_log_arch_dir | ./
| innodb_log_archive | OFF
| innodb_log_buffer_size | 1048576
| innodb_log_file_size | 5242880
| innodb_log_files_in_group | 2
| innodb_log_group_home_dir | ./
| innodb_mirrored_log_groups | 1
| innodb_max_dirty_pages_pct | 90
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 8388600
| language | /usr/share/mysql/english/
| large_files_support | ON
| local_infile | ON
| locked_in_memory | OFF
| log | OFF
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| log_slow_queries | OFF
| log_warnings | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | 0
| max_allowed_packet | 1048576
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 500
| max_connect_errors | 10
| max_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_relay_log_size | 0
| max_seeks_for_key | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_max_extra_sort_file_size | 268435456
| myisam_max_sort_file_size | 2147483647
| myisam_repair_threads | 1
| myisam_recover_options | OFF
| myisam_sort_buffer_size | 8388608
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| new | OFF
| open_files_limit | 2510
| pid_file | /var/lib/mysql/bubba.ssfbox.com.pid
| log_error |
| port | 3306
| protocol_version | 10
| query_alloc_block_size | 8192
| query_cache_limit | 1048576
| query_cache_size | 0
| query_cache_type | ON
| query_prealloc_size | 8192
| range_alloc_block_size | 2048
| read_buffer_size | 131072
| read_only | OFF
| read_rnd_buffer_size | 262144
| rpl_recovery_rank | 0
| server_id | 0
| slave_net_timeout | 3600
| skip_external_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /var/lib/mysql/mysql.sock
| sort_buffer_size | 2097144
| sql_mode | 0
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 0
| thread_stack | 126976
| tx_isolation | REPEATABLE-READ
| timezone | EST
| tmp_table_size | 33554432
| tmpdir | /tmp/
| transaction_alloc_block_size | 8192
| transaction_prealloc_size | 4096
| version | 4.0.18-standard
| version_comment | Official MySQL RPM
| wait_timeout | 28800

7. your mysql extended-status output
--> LINK: http://ssfbox.com/mysqlinfo.php

8. oh and is your vB the only thing on the server? or other scripts? sites?
--> vB is 'almost' the only thing on this server. One other website but very little traffic.

9. how many average and max concurrent users on your vB forum?
--> average 40, max 90-100

10. phpinfo.php
--> LINK: http://ssfbox.com/phpinfo.php

11. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following:

KeepAlive = ON
MaxKeepAliveRequests = 100
KeepAliveTimeout = 15
MinSpareServers = 5
MaxSpareServers = 10
StartServers = 5
MaxClients = 150

12. what version of vB are you running?
--> vB3 RC4


Thanks very much for any help received. I'm sure it's a p.i.t.a. to look at so many of these but it's a big help to the vB community. Thank you :)

[eva2000]

yes you'd need to tweak and add to your my.cnf the below settings and then restart mysql

use this my.cnf


[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

[std]

Thanks George... I'll try out the revised my.cnf.
Much appreciated!

Darren

[WendyK]

Hi George,

I'm hijacking this thread to post an optimization request because I just ordered the same server package from ServerMatrix--my first dedicated server also-- and I don't have my vB up and running on it yet so can't give top stats, etc. I will repost my own thread to tweak the configuration once I'm up and running and have some good stats, but I'd like to be able to do some optimizing even before then to start off on the right foot.

I am also running vB3 RC4.

The main differences from what std posted above are:

8. is your vB the only thing on the server? or other scripts? sites?

I will have one Web site on the same server, lots of HTML files, but essentially static content except some simple PHP stuff to process a few simple forms/headers/footers, etc., no mysql, does about 1/4 the bandwidth of my vBulletin.

9. how many average and max concurrent users on your vB forum?

average 125-175, max 225

Would you recommend the same my.cnf for me? I echo the thanks for all you do helping vB users optimize their boards, and for your advice to me earlier this week on server specs.

Wendy

[eva2000]

Hi George,

I'm hijacking this thread to post an optimization request because I just ordered the same server package from ServerMatrix--my first dedicated server also-- and I don't have my vB up and running on it yet so can't give top stats, etc. I will repost my own thread to tweak the configuration once I'm up and running and have some good stats, but I'd like to be able to do some optimizing even before then to start off on the right foot.

I am also running vB3 RC4.

The main differences from what std posted above are:

8. is your vB the only thing on the server? or other scripts? sites?

I will have one Web site on the same server, lots of HTML files, but essentially static content except some simple PHP stuff to process a few simple forms/headers/footers, etc., no mysql, does about 1/4 the bandwidth of my vBulletin.

9. how many average and max concurrent users on your vB forum?

average 125-175, max 225

Would you recommend the same my.cnf for me? I echo the thanks for all you do helping vB users optimize their boards, and for your advice to me earlier this week on server specs.

Wendy
yeah using my.cnf at http://www.vbulletin.com/forum/showpost.php?p=628558&postcount=2 should be a good start

[WendyK]

Thanks, George, I'll give it a try.

[Haakon]

Hi, I have the exact same server with the same provider (I run apache 2 and vB gold is the only differences). I tried this, but I get an error when trying to implement the first large part (except skip-inodb part). The others goes fine.
This is the error I got sent :

Link-ID == false, connect failed
mysql error:

mysql error number: 0

It also seems impossible to stop the server when I get this error, I allways get false on stop, and allways ok on start.

Thanks for your time, George!
Haakon

[eva2000]

Hi, I have the exact same server with the same provider (I run apache 2 and vB gold is the only differences). I tried this, but I get an error when trying to implement the first large part (except skip-inodb part). The others goes fine.
This is the error I got sent :

Link-ID == false, connect failed
mysql error:

mysql error number: 0

It also seems impossible to stop the server when I get this error, I allways get false on stop, and allways ok on start.

Thanks for your time, George!
Haakon
can you start your own thread to make it easier :)

[Haakon]

I tok it for granted mysql 4 was installed. Installed it now and it`s absolutely flying, even in IE! Thanks!

:D