Configuration advice needed

[Brian Cruz]

My forums (http://forums.toonzone.net/) are getting more popular each week, and I want to make sure everything is running as smooth as possible.

I'm on a Ventures Online dedicated server; Dual 933 MHZ and 1GB of RAM. Here are the stats I can provide:

My PHP info (http://www.toonzone.net/phptest/test.php)

My Top stats:

2:29pm up 17 days, 16:58, 1 user, load average: 0.29, 0.23, 0.25
146 processes: 143 sleeping, 3 running, 0 zombie, 0 stopped
CPU0 states: 28.0% user, 7.2% system, 0.0% nice, 64.1% idle
CPU1 states: 15.1% user, 4.0% system, 0.0% nice, 80.2% idle
Mem: 1028372K av, 942132K used, 86240K free, 0K shrd, 63784K buff
Swap: 265032K av, 8K used, 265024K free 539512K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10980 nobody 14 0 5952 5952 4324 S 4.3 0.5 0:00 httpd
10971 nobody 9 0 5940 5940 4308 S 3.7 0.5 0:00 httpd
8869 nobody 10 0 6420 6420 4356 S 3.1 0.6 0:02 httpd
10945 nobody 14 0 6108 6108 4284 S 2.7 0.5 0:00 httpd
10983 nobody 9 0 5912 5912 4292 S 1.9 0.5 0:00 httpd
10984 nobody 11 0 6020 6020 4296 S 1.9 0.5 0:00 httpd
10952 root 15 0 1076 1076 820 R 1.3 0.1 0:00 top
10981 nobody 9 0 5744 5744 4264 S 1.3 0.5 0:00 httpd
10982 nobody 9 0 5120 5120 4300 S 0.9 0.4 0:00 httpd
8860 nobody 12 0 6280 6280 4348 S 0.5 0.6 0:02 httpd
8862 nobody 9 0 6292 6292 4340 S 0.5 0.6 0:03 httpd
8875 nobody 9 0 6324 6324 4332 S 0.5 0.6 0:02 httpd
10418 nobody 12 0 6576 6576 4344 R 0.5 0.6 0:01 httpd
10966 nobody 9 0 5124 5124 4300 S 0.5 0.4 0:00 httpd
10967 nobody 7 0 4616 4616 4176 S 0.5 0.4 0:00 httpd
10972 nobody 9 0 4660 4660 4288 S 0.5 0.4 0:00 httpd
10985 nobody 9 0 5120 5120 4300 S 0.5 0.4 0:00 httpd
10969 nobody 10 0 4344 4344 4184 S 0.3 0.4 0:00 httpd
13564 mysql 9 0 18068 17M 1604 S 0.1 1.7 2:13 mysqld
13567 mysql 9 0 18068 17M 1604 S 0.1 1.7 0:57 mysqld
9162 mysql 9 0 18068 17M 1604 S 0.1 1.7 0:00 mysqld
10427 mysql 10 0 18068 17M 1604 R 0.1 1.7 0:00 mysqld
10907 nobody 9 0 5180 5180 4332 S 0.1 0.5 0:00 httpd
10968 mysql 9 0 18068 17M 1604 S 0.1 1.7 0:00 mysqld
1 root 8 0 516 516 448 S 0.0 0.0 0:08 init
2 root 9 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 9 0 0 0 0 SW 0.0 0.0 1:16 kswapd
4 root 9 0 0 0 0 SW 0.0 0.0 0:00 kreclaimd

MySQL configuration 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 | 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 | 500 |
| 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/lib/mysql/pluto.vosn.net.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 | EST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 3.23.47 |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+



MySQL extended-status:

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 378 |
| Aborted_connects | 1 |
| Bytes_received | 2605023399 |
| Bytes_sent | 2100073191 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1449160 |
| Com_change_master | 0 |
| Com_check | 50 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 29962 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 4 |
| Com_grant | 0 |
| Com_insert | 68497 |
| Com_insert_select | 3037 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 54 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 8794 |
| Com_replace_select | 108 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 10494655 |
| Com_set_option | 100 |
| Com_show_binlogs | 0 |
| Com_show_create | 100 |
| Com_show_databases | 31 |
| Com_show_fields | 102 |
| Com_show_grants | 0 |
| Com_show_keys | 1 |
| 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 | 560781 |
| Com_show_tables | 13 |
| Com_show_variables | 2 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 1603804 |
| Connections | 805906 |
| Created_tmp_disk_tables | 145 |
| Created_tmp_tables | 201470 |
| Created_tmp_files | 1 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 107318 |
| Handler_read_first | 370880 |
| Handler_read_key | 66584791 |
| Handler_read_next | 197627063 |
| Handler_read_prev | 1836 |
| Handler_read_rnd | 75809052 |
| Handler_read_rnd_next | 1285320929 |
| Handler_update | 1867298 |
| Handler_write | 17078452 |
| Key_blocks_used | 7793 |
| Key_read_requests | 309091908 |
| Key_reads | 367945 |
| Key_write_requests | 715556 |
| Key_writes | 652131 |
| Max_used_connections | 119 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 61 |
| Open_files | 94 |
| Open_streams | 0 |
| Opened_tables | 847 |
| Questions | 14463952 |
| Select_full_join | 669336 |
| Select_full_range_join | 0 |
| Select_range | 2178697 |
| Select_range_check | 0 |
| Select_scan | 2048928 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 6 |
| Sort_merge_passes | 0 |
| Sort_range | 2423640 |
| Sort_rows | 80796853 |
| Sort_scan | 925137 |
| Table_locks_immediate | 14543896 |
| Table_locks_waited | 4506 |
| Threads_cached | 0 |
| Threads_created | 805905 |
| Threads_connected | 68 |
| Threads_running | 1 |
| Uptime | 736833 |
+--------------------------+------------+

Thanks for any advice!

[eva2000]

i see you're running with mysql defaults and your extended-status shows you're table_cache needs increasing

try adding this to a /etc/my.cnf and restarting 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=500
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=2M
set-variable = record_buffer=2M
set-variable = sort_buffer=3M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=3600
set-variable = connect_timeout=10

[safe_mysqld]
open-files-limit=8192

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

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

[Brian Cruz]

Thanks. I've made the changes you suggested. Do these stats look any better? (about 160 people were on at once when I got these)

5:16pm up 18 days, 19:44, 1 user, load average: 1.04, 0.68, 0.53
305 processes: 302 sleeping, 2 running, 1 zombie, 0 stopped
CPU0 states: 19.5% user, 5.2% system, 0.0% nice, 74.2% idle
CPU1 states: 13.4% user, 5.2% system, 0.0% nice, 80.3% idle
Mem: 1028372K av, 997092K used, 31280K free, 0K shrd, 12040K buff
Swap: 265032K av, 8K used, 265024K free 698128K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
30787 nobody 9 0 6000 6000 4180 S 7.0 0.5 0:00 httpd
12935 mysql 9 0 27636 26M 1532 S 6.4 2.6 0:49 mysqld
30688 root 18 0 1172 1172 820 R 4.3 0.1 0:02 top
30794 nobody 9 0 5976 5976 4228 S 4.1 0.5 0:00 httpd
27614 nobody 9 0 6424 6424 4260 S 3.9 0.6 0:12 httpd
30796 nobody 9 0 5932 5932 4212 S 2.8 0.5 0:00 httpd
27627 nobody 9 0 6244 6244 4276 S 2.4 0.6 0:11 httpd
29480 nobody 9 0 6128 6128 4244 S 1.1 0.5 0:04 httpd
30768 nobody 9 0 5964 5964 4212 S 1.1 0.5 0:00 httpd
21620 mysql 9 0 27636 26M 1532 S 0.9 2.6 0:16 mysqld
30781 nobody 11 0 4988 4988 4192 S 0.9 0.4 0:00 httpd
30786 nobody 9 0 4988 4988 4192 S 0.9 0.4 0:00 httpd
12550 mysql 9 0 27636 26M 1532 S 0.7 2.6 0:57 mysqld
27621 nobody 9 0 6452 6452 4264 S 0.7 0.6 0:11 httpd
30766 nobody 9 0 4992 4992 4192 S 0.7 0.4 0:00 httpd
30799 nobody 10 0 4988 4988 4192 S 0.7 0.4 0:00 httpd
13142 mysql 9 0 27636 26M 1532 S 0.5 2.6 1:06 mysqld
26821 mysql 9 0 27636 26M 1532 S 0.5 2.6 0:01 mysqld
28256 nobody 9 0 6332 6332 4248 S 0.5 0.6 0:07 httpd
30791 nobody 9 0 4976 4976 4188 S 0.5 0.4 0:00 httpd
12479 mysql 9 0 27636 26M 1532 S 0.3 2.6 1:04 mysqld
5112 root 7 0 4136 4136 4036 S 0.3 0.4 0:01 httpd
21624 mysql 9 0 27636 26M 1532 S 0.3 2.6 0:04 mysqld
28570 mysql 9 0 27636 26M 1532 S 0.3 2.6 0:02 mysqld
30727 nobody 9 0 5036 5036 4212 S 0.3 0.4 0:00 httpd
30749 nobody 9 0 4208 4208 4044 S 0.3 0.4 0:00 httpd
30774 nobody 6 0 5228 5228 4200 S 0.3 0.5 0:00 httpd
30790 nobody 9 0 4212 4212 4052 S 0.3 0.4 0:00 httpd


+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 66 |
| Aborted_connects | 2 |
| Bytes_received | 169457029 |
| Bytes_sent | 3684279346 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 95202 |
| 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 | 2161 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 4947 |
| Com_insert_select | 212 |
| 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 | 610 |
| Com_replace_select | 9 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 681225 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 11 |
| 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 | 34056 |
| 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 | 102404 |
| Connections | 54916 |
| Created_tmp_disk_tables | 7 |
| Created_tmp_tables | 13513 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 7449 |
| Handler_read_first | 24072 |
| Handler_read_key | 4338536 |
| Handler_read_next | 11841655 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 4977246 |
| Handler_read_rnd_next | 290510721 |
| Handler_update | 97377 |
| Handler_write | 1180778 |
| Key_blocks_used | 14645 |
| Key_read_requests | 20358236 |
| Key_reads | 14376 |
| Key_write_requests | 45631 |
| Key_writes | 41252 |
| Max_used_connections | 107 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 77 |
| Open_files | 119 |
| Open_streams | 0 |
| Opened_tables | 83 |
| Questions | 941540 |
| Select_full_join | 41655 |
| Select_full_range_join | 0 |
| Select_range | 148775 |
| Select_range_check | 0 |
| Select_scan | 128225 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 168684 |
| Sort_rows | 5240393 |
| Sort_scan | 57991 |
| Table_locks_immediate | 944233 |
| Table_locks_waited | 150 |
| Threads_cached | 16 |
| Threads_created | 108 |
| Threads_connected | 92 |
| Threads_running | 2 |
| Uptime | 62411 |
+--------------------------+------------+

[eva2000]

looks good now :)