Rebuilt server, but high load. What have I missed?

[ColinP]

Hi guys,

Hopefully, someone will know what I've missed. Our server was hacked last week so we spent the weekend rebuilding and getting everything back up and running, but now already today we've had two major server load runaways (hitting 215 at one point today) and it's our quietest day of the week.

The forums are located at http://www.thedvdforums.com and we're using vBulletin 2.2.6 (there are plans to upgrade, but this is the version we've restored from our backups).

PHP Info link: http://www.thedvdforums.com/phpinfo.php

Server spec:

Dual P3 1GHz
2GB PC133 SDRAM
18GB SCSI HD

Running Redhat 7.3.
PHP: 4.1.2-7
MySQL: 3.23.52-1
Apache: 1.3.23-11

my.cnf:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=300
set-variable = key_buffer=16M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=6M
set-variable = table_cache=1024
set-variable = thread_cache_size=256

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=8M
set-variable = write_buffer=8M


MySQL Extended Status:

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 441 |
| Aborted_connects | 65 |
| Bytes_received | 466885451 |
| Bytes_sent | 2818164623 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 95158 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 14149 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 59968 |
| Com_insert_select | 427 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 2398 |
| Com_replace_select | 25 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 2189260 |
| Com_set_option | 40 |
| Com_show_binlogs | 0 |
| Com_show_create | 40 |
| Com_show_databases | 0 |
| Com_show_fields | 40 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 204044 |
| Connections | 95282 |
| Created_tmp_disk_tables | 5 |
| Created_tmp_tables | 79104 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 27565 |
| Handler_read_first | 79690 |
| Handler_read_key | 26189893 |
| Handler_read_next | 105721645 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 23529223 |
| Handler_read_rnd_next | 141868184 |
| Handler_update | 223867 |
| Handler_write | 7565908 |
| Key_blocks_used | 15582 |
| Key_read_requests | 116970486 |
| Key_reads | 225850 |
| Key_write_requests | 154326 |
| Key_writes | 150223 |
| Max_used_connections | 298 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 688 |
| Open_files | 690 |
| Open_streams | 0 |
| Opened_tables | 792 |
| Questions | 2660322 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 704779 |
| Select_range_check | 0 |
| Select_scan | 372866 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 86 |
| Slow_queries | 1184 |
| Sort_merge_passes | 0 |
| Sort_range | 619156 |
| Sort_rows | 23818168 |
| Sort_scan | 255723 |
| Table_locks_immediate | 2918489 |
| Table_locks_waited | 13618 |
| Threads_cached | 251 |
| Threads_created | 299 |
| Threads_connected | 5 |
| Threads_running | 1 |
| Uptime | 34734 |
+--------------------------+------------+


Any ideas as to what I may have missed. We have mod_gzip installed on the box. If you need any more details feel free to ask away :D

[eva2000]

1. upgrade apache 1.3.23 to 1.3.26 to fix security bugs which include a DOS attack vulnerability in apache
2. upgrade vB from 2.2.6 to 2.2.8 to fix other bugs outlined at

http://www.vbulletin.com/forum/showthread.php?s=&threadid=53593

and

http://www.vbulletin.com/forum/showthread.php?s=&threadid=55437

3. replace current my.cnf with below one and restart mysql


[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
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

[safe_mysqld]
open_files_limit=8192

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

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=8M
set-variable = write_buffer=8M

[ColinP]

MySQL Variables:

+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk 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 |
| 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 | NO |
| have_gemini | NO |
| have_innodb | NO |
| have_isam | YES |
| have_raid | NO |
| have_openssl | NO |
| init_file | |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 16773120 |
| 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 | 300 |
| 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 |

[ColinP]

| 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/lib/mysql/thedvdforums.com.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 1044480 |
| record_rnd_buffer | 1044480 |
| 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 | 6291448 |
| sql_mode | 0 |
| table_cache | 1024 |
| table_type | MYISAM |
| thread_cache_size | 256 |
| thread_stack | 65536 |
| transaction_isolation | READ-COMMITTED |
| timezone | BST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 3.23.52 |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[eva2000]

don't need to post variables output if you posted your my.cnf ;)

[ColinP]

Thanks eva - will do and get back to you :)

[eva2000]

Originally posted by ColinP
Thanks eva - will do and get back to you :) after the above changes you might want to install PHP Accelerator 1.3.3 for linux

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

http://www.php-accelerator.co.uk/

[ColinP]

eva,

I've now updated my.cnf and stopped and restarted mysqld - near enough as soon as it came back the server load shot up again...

[ColinP]

Originally posted by eva2000
after the above changes you might want to install PHP Accelerator 1.3.3 for linux

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

http://www.php-accelerator.co.uk/

PHP-Accelerator was on my to-do list before the rebuild :) Will get that installed as soon as I get everything working smoothly again.

[eva2000]

Originally posted by ColinP
eva,

I've now updated my.cnf and stopped and restarted mysqld - near enough as soon as it came back the server load shot up again... forgot to list a vital upgrade

upgrade

2.4.17-0.13smp

to latest stable

2.4.x smp kernel as well

make sure you have apache 1.3.26 and latest 2.4 smp kernel

[ColinP]

I've now upgrade Apache to 1.3.26 and PHP to 4.2.3. I haven't touched the Kernel yet - will this have a major effect, seeing as it appears to be quite a big job to carry out?

[ColinP]

I've had to go back to Apache 1.3.23 - no matter what I do I cannot get mod_gzip to work with 1.3.26 and without that the bandwidth used by the site makes the connection die.

I also didn't see any improvement over 1.3.23.

I'm completely stumped.

[eva2000]

that's strange.... when you upgraded to php 4.2.3 did you replace your existing php.ini with the one provided by php 4.2.3 called php.ini-recommended (rename it to php.ini and replace your existing one)

[ColinP]

I have now ;) Which problem should this correct? ;)

[eva2000]

Originally posted by ColinP
I have now ;) Which problem should this correct? ;) not sure but it's the proper thing to do

have you checked your apache error logs for error messages or hints to problems ?

[ColinP]

Here are the entries in the error log from 1.3.26 and mod_gzip:

[Tue Oct 1 14:49:52 2002] [warn] mod_gzip: ERROR: fopen(/www/mod_gzip/_16584_103_4.wrk) in dyn1_getfdo1
[Tue Oct 1 14:49:52 2002] [warn] mod_gzip: ERROR: Make sure all named directories exist and have the correct permissions.
[Tue Oct 1 14:49:52 2002] [warn] mod_gzip: RECOVERY [GET /forums/editpost.php?s=&action=editpost&postid=1501952 HTTP/1.1]
[Tue Oct 1 14:49:55 2002] [error] [client 195.166.78.172] mod_gzip: ERROR: Couldn't create a file descriptor at HTTP : /www/mod_gzip/_16504_99_0.wrk
[Tue Oct 1 14:49:55 2002] [warn] mod_gzip: ERROR: fopen(/www/mod_gzip/_16504_99_0.wrk) in dyn1_getfdo1
[Tue Oct 1 14:49:55 2002] [warn] mod_gzip: ERROR: Make sure all named directories exist and have the correct permissions.
[Tue Oct 1 14:49:55 2002] [warn] mod_gzip: RECOVERY [GET /forums/forumdisplay.php?s=&forumid=11 HTTP/1.1]
[Tue Oct 1 14:49:58 2002] [error] [client 217.35.154.114] mod_gzip: ERROR: Couldn't create a file descriptor at HTTP : /www/mod_gzip/_16491_103_4.wr\
k
[Tue Oct 1 14:49:58 2002] [warn] mod_gzip: ERROR: fopen(/www/mod_gzip/_16491_103_4.wrk) in dyn1_getfdo1
[Tue Oct 1 14:49:58 2002] [warn] mod_gzip: ERROR: Make sure all named directories exist and have the correct permissions.
[Tue Oct 1 14:49:58 2002] [warn] mod_gzip: RECOVERY [GET /forums/ HTTP/1.1]

[ColinP]

Aha - permissions!!! I've now fixed the mod_gzip problem... :) Thanks eva.

Still getting the high load though :( hit 442 earlier.

[eva2000]

Originally posted by ColinP
Aha - permissions!!! I've now fixed the mod_gzip problem... :) Thanks eva.

Still getting the high load though :( hit 442 earlier. permissions yup :)

did you upgrade the kernel?

[ColinP]

Not yet. It's next on the to do list unless there are anything less drastic you can think of.

[eva2000]

Originally posted by ColinP
Not yet. It's next on the to do list unless there are anything less drastic you can think of. some older kernels have memory or other leaks... my current server when it was first setup had high spikes in loads with 2.4.18-3 smp kernel then when i upgraded to 2.4.18-5 smp kernel it was fixed... now i'm running 2.4.18-10 smp kernel with no probs

just an illustration of what a kernel upgrade can do :)

[ColinP]

The box has now started to crumble around us. I'm grabbing our backups and will do a rebuild - with a view to upgrading the kernel...

[eva2000]

Originally posted by ColinP
The box has now started to crumble around us. I'm grabbing our backups and will do a rebuild - with a view to upgrading the kernel... let us know how it goes..

for now it would be nice to move this thread to the proper forum - server config forum :)

[ColinP]

Thanks for all the help so far. I'll let you know how we get on.

[ColinP]

OK, latest update:

We went back to Redhat 7.1 and installed everything that was there before the rebuild last week. I've now upgraded to the latest versions of Apache (1.3.26), PHP (4.2.3) and MySQL (3.23.52) as well as OpenSSH to fix the security hole in the RedHat7.1 supplied version.

I've got the same configuration as we had before the rebuild with the low server load, but for some reason we're seeing a much higher load this time around :(

Below is the my.cnf:

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=650
set-variable = key_buffer=16M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=6M
set-variable = table_cache=1024
set-variable = thread_cache_size=256

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=8M
set-variable = write_buffer=8M

Extended status:


+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 547431 |
| Bytes_sent | 7967620 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 118 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 75 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 145 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 2684 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 128 |
| Connections | 120 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 97 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 2 |
| Handler_read_first | 105 |
| Handler_read_key | 22720 |
| Handler_read_next | 643953 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 26860 |
| Handler_read_rnd_next | 308286 |
| Handler_update | 126 |
| Handler_write | 4392 |
| Key_blocks_used | 1358 |
| Key_read_requests | 140208 |
| Key_reads | 1357 |
| Key_write_requests | 110 |
| Key_writes | 109 |
| Max_used_connections | 17 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 48 |
| Open_files | 68 |
| Open_streams | 0 |
| Opened_tables | 54 |
| Questions | 3255 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 880 |
| Select_range_check | 0 |
| Select_scan | 456 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 774 |
| Sort_rows | 26860 |
| Sort_scan | 311 |
| Table_locks_immediate | 3614 |
| Table_locks_waited | 6 |
| Threads_cached | 2 |
| Threads_created | 18 |
| Threads_connected | 16 |
| Threads_running | 3 |
| Uptime | 25 |
+--------------------------+---------+

[rylin]

First of all, I'd suggest going back to RH 7.3
(even though 8.0 is out, i'd give it a revision or two until I consider it stable).

Right after you've installed 7.3, register for the rhn network (rhn_register) and make sure the latest errata gets dled & installed properly (https://rhn.redhat.com/)

Reboot (since you just got a kernel upgrade)

When this is done, dl apache 1.3.26, php 4.2.3, latest mysql rpm
install the mysql rpm, ./configure apache, configure php to be statically compiled against apache, and make sure to enable zlib support. follow the regular make / make install procedures after this.

the reason you might want zlib in php is you will not have to worry about permission problems, and afaik, using zlib in php makes sure the work gets done in ram, as opposed to mod_gzip, which uses the hd to and from.

tweak your mysql settings according to what george said above, then import your old db (do you have mysqldumps or the physical files?)

after you've done this, i'd suggest running a mysqlcheck for repairing & optimizing the db.

oh, turn keepalives off in apache.. they were causing major sluggishness for us right after a reinstall for some reason.

i've been through odd-loadaverage-hell a few times myself lately.. i feel your pain ;)

[ColinP]

Optimising the database seems to have made quite a difference. We used mysqldump - we do a backup every night using it so just restored one of them.

We're actually looking into getting a second server soon. We may give RH7.3 a shot on that. I'm loathe to touching anything now that the server seems to have calmed down!

[eva2000]

yup go back to rh 7.3