Server config/tweaking : High load

[Laurent]

Hello,

For a few weeks now, the load of our server is getting high very often which lead to the forum closing for a fews seconds all the time (I have set the NIX load limit at 8.00).

I was thinking of upgrading ram with 1 more Giga but I don't know if il will change something.

Here are the information about our dedicated server :

- P4 1.8Ghz, 1giga Ram, 2*18gigas HD Raid1
- Redhat 7.3, Apache 1.3.23
- PHP 4.1.2, MySQL 3.23.49 (http://www.judgehype.com/phpinfo.php3)

The server is running VB 2.2.9 and 6 websites. VB does more than 2/3 of the visits every day. At peak time we have between 200-260 users online. The forum has a bit more than 1.400.000 posts (I was thinking of purging some forums to lower it to 1.100.000, all I can do without loosing too much).

I have read that you recommend using phpaccelerator, tuning my.cnf, upgrade php/mysql/apache, ... and I was thinking of upgrading ram and purging posts. I'm a bit lost in all of this (and what to do first) and ready to follow any advice :)

Thank you in advance for your help,

Laurent


Extended Stats : http://www.judgehype.com/stats.php3

etc/my.cnf :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysqladmin variables :

| back_log | 50
| basedir | /usr/
| bdb_cache_size | 8388600
| bdb_log_buffer_size | 32768
| bdb_home | /var/lib/mysql/
| bdb_max_lock | 10000
| bdb_logdir |
| bdb_shared_data | OFF
| bdb_tmpdir | /tmp/
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (February 14, 2002)
| binlog_cache_size | 32768
| character_set | latin1
| concurrent_insert | ON
| connect_timeout | 5
| datadir | /var/lib/mysql/
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 0
| have_bdb | YES
| have_gemini | NO
| have_innodb | DISABLED
| have_isam | YES
| have_raid | NO
| have_openssl | NO
| init_file |
| innodb_additional_mem_pool_size | 1048576
| innodb_buffer_pool_size | 8388608
| innodb_data_file_path |
| innodb_data_home_dir |
| innodb_file_io_threads | 4
| innodb_force_recovery | 0
| innodb_thread_concurrency | 8
| innodb_flush_log_at_trx_commit | OFF
| innodb_fast_shutdown | OFF
| 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
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 8388600
| language | /usr/share/mysql/english/
| large_files_support | ON
| locked_in_memory | OFF
| log | OFF
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| log_long_queries | 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 | 100
| max_connect_errors | 10
| max_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_max_extra_sort_file_size | 256
| myisam_max_sort_file_size | 2047
| myisam_recover_options | 0
| myisam_sort_buffer_size | 8388608
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| open_files_limit | 0
| pid_file | /var/run/mysqld/mysqld.pid
| port | 3306
| protocol_version | 10
| record_buffer | 131072
| record_rnd_buffer | 131072
| query_buffer_size | 0
| safe_show_database | OFF
| server_id | 0
| slave_net_timeout | 3600
| skip_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /var/lib/mysql/mysql.sock
| sort_buffer | 2097144
| sql_mode | 0
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 0
| thread_stack | 65536
| transaction_isolation | READ-COMMITTED
| timezone | CET
| tmp_table_size | 33554432
| tmpdir | /tmp/
| version | 3.23.49
| wait_timeout | 28800

[eva2000]

definitely a few things you can do to improve the situation

1. upgrade to mysql 3.23.54a
2. upgrade to php 4.2.3
3. upgrade linux 2.4.17.x kernel to latest stable release
4. change my.cnf to below one and restart mysql


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=650
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
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


5. install PHP Accelerator http://i4net.tv/marticle/get.php?action=getarticle&articleid=31

#1, #4 and #5 would have the most immediate effect on your performance along with #2

[Laurent]

Thank you very much for your answer. I will work that way and give you more news once all updates are done :)

Laurent