Need some good advice for configuring for large-traffic site

[FTEmjay]

Hi,

We launched VB about 3 weeks ago (started fresh, no importing)... it ran for about 2 weeks, and resulted in a crashed database. We were running it on a server with the rest of the website, and it was just too much load for that server. Since then, we've repaired/restored the database and successfully migrated everything to its own box (separate from the rest of the site), upgraded to 2.2.2, and, after 5 days of downtime, we re-opened the doors this morning and are proceeding with caution.

I plan on researching these forums in depth for configuration tips, but what I'm looking for right now are the basic "do's and don'ts". What do we need to do, or not do to avoid blowing up the server again.

So far this morning we've had a few "too many connections" errors, and our programmer made a change to the MySQL so it is now "NOT" using persistent connections to the db hopefully this may or may not help. Now, just a few minutes ago the server crashed out and threw everyone off. It's restarted again now.

There has been as many as 230 active users (so far), but I suspect that will increase as the day goes on and people realize the boards are back online. On the old one we could never manage more than 200 active users without bringing the server to its knees, so we're hoping we can accommodate a little more than that now that it's on its own box.

Some more info; we have about 3,100 registered members and 51,000 posts (again, this is just 3 weeks activity).
The server:
dual 500MHz with 512MB of RAM
Apache v.1.3.12
MySQL v. 3.23.47
PHP v.4.1.1

I'm really hoping to make VB work for us (if we have to revert to Discus I'll cry), and I know there are much bigger sites out there successfully running VB so they have to be doing something right.

Any advice would be welcome and appreciated :)

Thanks,
Michelle

[eva2000]

1. vB's persistent connections settings can be turned off in config.php

pconnect =1
pconnect =0

1 is on and 0 is off

so no need to hack vB's code

2. if possible upgrade apache to latest which i think is 1.3.22 or 1.3.23

3. what linux kernel are you using upgrade to latest stable version 2.4.17 something

4. please provide the following

- your top stats
- your mysql configuration variables located at /etc/my.cnf or c:\my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type

mysqladmin -u root -p variables

copy and paste output here

- your mysql extended-status output either still telnet as root user type

mysqladmin -u root -p extended-status

copy and paste output here

or preferred is to installed extended-status output script which is located at http://vbulletin.com/forum/showthread.php?threadid=3477

and post url to that here

[FTTech]

Hi,

Thanks in advance for the help.

1. No VB hack, I changed the pconnect in the config.php. I don't know if this is what caused our crash today, it may have been a system error. The system dumped with a page fault on cpu0 (dual 600 MHz..sorry for wrong info from FTEmjay...my fault)

2. I didn't want to change too many app versions while we are still unstable. My preference is to wait on the Apache upgrade until we exhaust configuration possiblities.

3. The system is BSD 4.2 w/current patches.

Top Output: CPU states have spiked as high as 96%, down to 42%, using 1 sec updates in Top

load averages: 1.87, 1.73, 1.79
83 processes: 2 running, 81 sleeping
CPU states: 82.0% user, 0.0% nice, 9.0% system, 0.0% interrupt, 9.0% idle
Memory: Real: 187M/210M Virt: 260M/614M Free: 232M

PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
331 mysql 2 0 21M 15M sleep 7:41 9.08% 9.08% mysqld
509 www 2 0 5296K 4196K sleep 0:39 4.44% 4.44% httpd
505 www 2 0 5944K 4012K sleep 0:36 2.05% 2.05% httpd
1646 www 2 0 4584K 3796K sleep 0:03 1.95% 1.95% httpd
391 www 2 0 5324K 4144K sleep 0:45 1.95% 1.95% httpd
510 www 2 0 4500K 4196K sleep 0:40 1.90% 1.90% httpd
393 www 2 0 4668K 4240K sleep 0:41 1.86% 1.86% httpd
1721 www 2 0 5288K 3972K sleep 0:01 1.90% 1.81% httpd
1726 www 2 0 4464K 3940K sleep 0:01 1.94% 1.81% httpd
362 www 2 0 5300K 4220K sleep 0:45 1.76% 1.76% httpd
392 www 2 0 5156K 4196K sleep 0:44 1.71% 1.71% httpd
1692 www 2 0 6092K 4132K sleep 0:01 1.56% 1.56% httpd
1741 www 2 0 5180K 3088K sleep 0:00 2.73% 1.56% httpd
1577 www 2 0 4696K 2760K sleep 0:02 1.46% 1.46% httpd
378 www 2 0 5296K 4020K sleep 0:40 1.37% 1.37% httpd


MySQL Config Variables:


| Variable_name | Value

| back_log | 50
| basedir | /export/mysql/
| binlog_cache_size | 32768
| character_set | latin1
| character_sets | latin1 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 | /export/mysql/var/
| 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 | 131072
| key_buffer_size | 16773120
| language | /export/mysql/share/mysql/english/
| large_files_support | ON
| log | OFF
| log_update | OFF
| log_bin | ON
| log_slave_updates | OFF
| log_long_queries | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | 0
| max_allowed_packet | 1047552
| 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 | 7168
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| open_files_limit | 0
| pid_file | /export/mysql/var/www3.fertilethoughts.com.pid
| port | 3306
| protocol_version | 10
| record_buffer | 131072
| record_rnd_buffer | 131072
| query_buffer_size | 0
| safe_show_database | OFF
| server_id | 1
| slave_net_timeout | 3600
| skip_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /tmp/mysql.
| sort_buffer | 524280
| sql_mode | 0
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 0
| thread_stack | 65536
| transaction_isolation | READ-COMMITTED
| tmp_table_size | 33554432
| tmpdir | /var/tmp/
| version | 3.23.47-log
| wait_timeout | 28800

MySQL extended-status:

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Bytes_received | 33746872 |
| Bytes_sent | 517888457 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 10689 |
| 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 | 775 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 1279 |
| Com_insert_select | 92 |
| 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 | 218 |
| Com_replace_select | 8 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 160915 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 2 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_stat | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_stat | 0 |
| Com_show_status | 3 |
| Com_show_tables | 0 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 16233 |
| Connections | 10698 |
| Created_tmp_disk_tables | 2 |
| Created_tmp_tables | 4781 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 760 |
| Handler_read_first | 7979 |
| Handler_read_key | 1484519 |
| Handler_read_next | 2403005 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 2134484 |
| Handler_read_rnd_next | 5060778 |
| Handler_update | 15631 |
| Handler_write | 359535 |
| Key_blocks_used | 10064 |
| Key_read_requests | 6348577 |
| Key_reads | 9782 |
| Key_write_requests | 14251 |
| Key_writes | 12943 |
| Max_used_connections | 39 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 97 |
| Open_streams | 0 |
| Opened_tables | 313 |
| Questions | 200901 |
| Select_full_join | 2 |
| Select_full_range_join | 0 |
| Select_range | 42884 |
| Select_range_check | 0 |
| Select_scan | 26122 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 41318 |
| Sort_rows | 2186826 |
| Sort_scan | 19952 |
| Table_locks_immediate | 215697 |
| Table_locks_waited | 56 |
| Threads_cached | 0 |
| Threads_created | 10697 |
| Threads_connected | 7 |
| Threads_running | 2 |
| Uptime | 4249 |
+--------------------------+-----------+

Thanks Again!

[eva2000]

i think i see your problem try turning off the mysql logging in your /etc/my.cnf file

log-bin

setting can be commented out or removed and mysql restarted

[FTTech]

Thanks for the reply, this makes a lot of sense.

We just had another crash at 10:19 PM EST and the system spammed me with mysql 1040 errors. I had just checked the number of users and it was well below the daily high.

If the log was getting slammed then the db server daemon might crash.

[FTEmjay]

Thanks for all your help, Eva.

We're back up again now.. and fingers crossed!

[eva2000]

Originally posted by FTEmjay
Thanks for all your help, Eva.

We're back up again now.. and fingers crossed! okay let me know how it goes.. you may need further mysql optimisation and tweaking of settings later so pop on by the MySQL forums here for a tune up :D

[FTEmjay]

Oh, it just occurred to me, I should have started this thread in the Server Configuration forum (oops)... feel free to move it over. Sorry 'bout that :p

[eva2000]

thread moved :)