PDA

View Full Version : Server Optimization Request

Colosus
Mon 15th Mar '04, 6:03am
1. is this on dedicated or shared virual server

Dedicated

2. your server specs. For example:


WWW (PHP/HTML)
cpu speed/type single or dual cpus): dual Xeon 2ghz w/ HT
how much memory installed: 2gb ram
hard drive type/configuration: 2x 18gb SCSI RAID 1
linux distributor or windows version: FreeBSD 5.2.1
apache/IIS version: apache 2.0.48
PHP version: php 4.3.4
MySQL version: none

WWW (Images/Files)
cpu speed/type single or dual cpus): dual P3 1.3ghz
how much memory installed: 2gb ram
hard drive type/configuration: 4x 120gb IDE RAID 5
linux distributor or windows version: FreeBSD 5.2.1
apache/IIS version: apache 2.0.48
PHP version: php 4.3.4
MySQL version: none

SQL
cpu speed/type single or dual cpus): dual Xeon 2.8ghz w/ HT
how much memory installed: 4gb ram
hard drive type/configuration: 2x 36gb SCSI RAID 1
linux distributor or windows version: FreeBSD 5.2.1
apache/IIS version: apache 2.0.48
PHP version: php 4.3.4
MySQL version: 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 ?

No

4. if possible how mysql was compiled/installed

I didn't compile it, but I can get it for you later if need be


5. your top stats

PHP/HTML Server (during slow time 100-150 users)

last pid: 37068; load averages: 5.93, 5.36, 4.96 up 5+09:53:15 05:02:48
188 processes: 13 running, 174 sleeping, 1 zombie
CPU states: 12.8% user, 0.0% nice, 17.5% system, 0.8% interrupt, 68.9% idle
Mem: 911M Active, 87M Inact, 154M Wired, 144K Cache, 112M Buf, 851M Free
Swap: 4080M Total, 2208K Used, 4078M Free

PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
35778 www 100 0 23360K 11888K RUN 3 5:49 8.01% 8.01% httpd
31906 www 100 0 23384K 11916K RUN 0 22:45 7.91% 7.91% httpd
30263 www 99 0 26120K 18520K RUN 0 13:59 7.71% 7.71% httpd
30480 www 99 0 22980K 14880K RUN 0 30:30 7.67% 7.67% httpd
30848 www 99 0 23392K 11928K RUN 3 35:53 7.62% 7.62% httpd
30271 www 99 0 23932K 16088K RUN 3 29:54 7.52% 7.52% httpd
35129 www 99 0 23812K 12340K RUN 0 0:54 7.47% 7.47% httpd
30239 www 99 0 20896K 12788K RUN 0 41:54 7.42% 7.42% httpd
30255 www 99 0 21264K 12984K RUN 0 38:47 7.23% 7.23% httpd
30495 www 99 0 23560K 15460K RUN 3 36:45 7.18% 7.18% httpd
30269 www 99 0 23512K 16028K RUN 0 28:58 6.98% 6.98% httpd
36583 www 97 0 24880K 12804K select 0 0:06 2.00% 2.00% httpd
36591 www 4 0 23400K 11936K select 0 0:05 1.71% 1.71% httpd
30147 www 4 0 24280K 16360K select 0 1:45 1.61% 1.61% httpd
34370 www 97 0 23732K 12264K select 0 0:28 1.51% 1.51% httpd
36579 www 4 0 23244K 11768K select 0 0:05 1.42% 1.42% httpd
36320 www 4 0 23848K 12376K select 0 0:06 1.22% 1.22% httpd
36318 www 96 0 23724K 12288K select 0 0:06 0.93% 0.93% httpd
32474 www 4 0 23936K 12472K select 0 1:03 0.83% 0.83% httpd
33577 www 4 0 23992K 12524K select 0 0:45 0.83% 0.83% httpd
35914 www 96 0 23984K 12512K RUN 3 0:15 0.68% 0.68% httpd
35391 www 4 0 23380K 11920K select 0 0:18 0.59% 0.59% httpd
31907 www 4 0 24628K 13160K select 0 1:08 0.44% 0.44% httpd
35915 www 4 0 23384K 11916K select 0 0:13 0.29% 0.29% httpd
35818 www 4 0 23360K 11880K select 0 0:09 0.20% 0.20% httpd
36589 www 4 0 23296K 11836K select 0 0:04 0.05% 0.05% httpd
314 root 96 0 1312K 660K select 0 8:19 0.00% 0.00% syslogd
946 root 4 0 1608K 852K select 0 3:10 0.00% 0.00% ntpd
463 root 4 0 3520K 1456K select 0 2:08 0.00% 0.00% sendmail
30312 www 4 0 24080K 16552K select 0 1:50 0.00% 0.00% httpd
30196 www 4 0 24640K 16140K select 0 1:48 0.00% 0.00% httpd
457 root 4 0 3492K 1336K select 0 1:42 0.00% 0.00% sshd
522 root 4 0 1432K 832K select 0 1:40 0.00% 0.00% inetd
30872 www 4 0 23504K 12036K select 0 1:36 0.00% 0.00% httpd
31055 www 4 0 24016K 12556K select 0 1:31 0.00% 0.00% httpd
32860 www 4 0 27720K 16292K select 0 1:18 0.00% 0.00% httpd
31050 www 4 0 24948K 13564K select 0 1:18 0.00% 0.00% httpd
32471 www 4 0 28104K 16680K select 0 1:12 0.00% 0.00% httpd
32292 www 4 0 23752K 12280K select 0 1:08 0.00% 0.00% httpd
32796 www 4 0 23524K 12052K select 0 1:07 0.00% 0.00% httpd
32591 www 4 0 24556K 13116K select 0 1:06 0.00% 0.00% httpd
32470 www 4 0 24448K 12984K select 0 1:03 0.00% 0.00% httpd
32478 www 4 0 24328K 12952K select 0 1:01 0.00% 0.00% httpd
33038 www 4 0 23672K 12204K select 0 0:59 0.00% 0.00% httpd
33037 www 4 0 23880K 12420K select 0 0:56 0.00% 0.00% httpd
33036 www 4 0 24232K 12756K select 0 0:53 0.00% 0.00% httpd
33382 www 4 0 23796K 12348K select 0 0:53 0.00% 0.00% httpd
33347 www 4 0 24616K 13152K select 0 0:53 0.00% 0.00% httpd
30120 root 76 0 2720K 1468K select 1 0:52 0.00% 0.00% rotatelogs
33567 www 4 0 24016K 12556K select 0 0:51 0.00% 0.00% httpd
33547 www 4 0 23564K 12096K select 0 0:50 0.00% 0.00% httpd
33643 www 96 0 24436K 12968K select 0 0:49 0.00% 0.00% httpd
33325 www 4 0 23952K 12492K select 0 0:48 0.00% 0.00% httpd
33555 www 20 0 23972K 12504K lockf 1 0:44 0.00% 0.00% httpd


mySQL Server (during same period)

last pid: 11249; load averages: 0.00, 0.05, 0.01 up 3+03:03:37 05:05:05
27 processes: 1 running, 26 sleeping
CPU states: 0.3% user, 0.0% nice, 0.6% system, 0.1% interrupt, 99.0% idle
Mem: 179M Active, 2029M Inact, 208M Wired, 140K Cache, 112M Buf, 1474M Free
Swap: 4096M Total, 4096M Free

PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
9249 mysql 96 0 476M 168M select 0 90:33 3.08% 3.08% mysqld
504 root 96 0 6104K 3116K select 1 0:13 0.00% 0.00% httpd
468 root 96 0 3500K 2088K select 2 0:05 0.00% 0.00% sendmail
462 root 96 0 3488K 1868K select 2 0:01 0.00% 0.00% sshd
485 root 8 0 1336K 852K nanslp 2 0:01 0.00% 0.00% cron
323 root 96 0 1312K 724K select 2 0:01 0.00% 0.00% syslogd
471 smmsp 20 0 3404K 1860K pause 1 0:00 0.00% 0.00% sendmail
11240 root 4 0 6240K 2216K sbwait 0 0:00 0.00% 0.00% sshd
11243 colosus 20 0 2280K 1616K pause 0 0:00 0.00% 0.00% csh
568 www 96 0 6160K 3436K select 0 0:00 0.00% 0.00% httpd
9224 root 8 0 924K 348K wait 0 0:00 0.00% 0.00% sh
11242 colosus 96 0 6240K 2300K select 2 0:00 0.00% 0.00% sshd
11249 colosus 96 0 2216K 1336K CPU2 2 0:00 0.00% 0.00% top
567 www 4 0 6160K 3416K select 0 0:00 0.00% 0.00% httpd
566 www 4 0 6160K 3416K select 0 0:00 0.00% 0.00% httpd
564 www 4 0 6160K 3412K select 0 0:00 0.00% 0.00% httpd
565 www 4 0 6160K 3412K select 0 0:00 0.00% 0.00% httpd
557 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
3539 www 4 0 6160K 3424K select 0 0:00 0.00% 0.00% httpd
563 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
560 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
558 root 5 0 1276K 724K ttyin 1 0:00 0.00% 0.00% getty
561 root 5 0 1276K 724K ttyin 1 0:00 0.00% 0.00% getty
559 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
556 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
562 root 5 0 1276K 724K ttyin 1 0:00 0.00% 0.00% getty
231 root 20 0 1180K 500K pause 0 0:00 0.00% 0.00% adjkerntz
Colosus
Mon 15th Mar '04, 6:05am
6. 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


+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | /usr/local/ |
| bdb_cache_size | 8388600 |
| bdb_log_buffer_size | 0 |
| bdb_home | |
| bdb_max_lock | 10000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | |
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (February 10, 2004) |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| 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 |
| convert_character_set | |
| datadir | /usr/local/var/db/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 | DISABLED |
| have_crypt | YES |
| have_innodb | DISABLED |
| 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 | |
| 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 | 402653184 |
| language | /usr/local/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 | ON |
| log_warnings | OFF |
| long_query_time | 5 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 2000 |
| 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 | 67108864 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 1000000 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 11095 |
| pid_file | /usr/local/var/db/mysql/sql.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | 0 |
| table_cache | 512 |
| table_type | MYISAM |
| thread_cache_size | 32 |
| thread_stack | 196608 |
| tx_isolation | REPEATABLE-READ |
| timezone | EST |
| tmp_table_size | 33554432 |
| tmpdir | /usr/local/var/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.18-log |
| version_comment | FreeBSD port: mysql-server-4.0.18_1 |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------------------------------------------


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


+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 142 |
| Aborted_connects | 2 |
| Bytes_received | 873236005 |
| Bytes_sent | 2461956291 |
| Com_admin_commands | 238 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 191208 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 3 |
| Com_create_table | 16 |
| Com_delete | 13074 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 1 |
| Com_flush | 0 |
| Com_grant | 1 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 23114 |
| Com_insert_select | 17 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 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 | 1733 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 2585391 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 29 |
| Com_show_fields | 4 |
| Com_show_grants | 4 |
| Com_show_keys | 2 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 628 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 474015 |
| Connections | 181022 |
| Created_tmp_disk_tables | 237 |
| Created_tmp_tables | 19876 |
| Created_tmp_files | 20 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 18866 |
| Handler_read_first | 194169 |
| Handler_read_key | 56474897 |
| Handler_read_next | 41439788 |
| Handler_read_prev | 1302557 |
| Handler_read_rnd | 14034549 |
| Handler_read_rnd_next | 1106227759 |
| Handler_rollback | 0 |
| Handler_update | 4855536 |
| Handler_write | 3917864 |
| Key_blocks_used | 99924 |
| Key_read_requests | 146731847 |
| Key_reads | 95054 |
| Key_write_requests | 124778 |
| Key_writes | 159933 |
| Max_used_connections | 246 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 512 |
| Open_files | 811 |
| Open_streams | 0 |
| Opened_tables | 1653 |
| Questions | 5278708 |
| Qcache_queries_in_cache | 5999 |
| Qcache_inserts | 2583172 |
| Qcache_hits | 1808434 |
| Qcache_lowmem_prunes | 8936 |
| Qcache_not_cached | 2244 |
| Qcache_free_memory | 21707208 |
| Qcache_free_blocks | 3120 |
| Qcache_total_blocks | 15513 |
| Rpl_status | NULL |
| Select_full_join | 144 |
| Select_full_range_join | 0 |
| Select_range | 269832 |
| Select_range_check | 0 |
| Select_scan | 372544 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 24 |
| Sort_merge_passes | 10 |
| Sort_range | 196909 |
| Sort_rows | 21242070 |
| Sort_scan | 22246 |
| Table_locks_immediate | 4313126 |
| Table_locks_waited | 4417 |
| Threads_cached | 30 |
| Threads_created | 491 |
| Threads_connected | 35 |
| Threads_running | 1 |
| Uptime | 48878 |
+--------------------------+------------+
Colosus
Mon 15th Mar '04, 6:06am
8. oh and is your vB the only thing on the server? or other scripts? sites?

Our site and other community sites on the Images/Files server. Only forums and main site PHP on the PHP/HTML server. Quite a few other php scripts running on both servers, but a majority of our traffic is forum-based.

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

250-300 average. 470 max

10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

http://www.tribalwar.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 :

PHP/HTML
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 48
MaxSpareServers 96
StartServers 64
MaxClients 256

Images/Files
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 48
MaxSpareServers 96
StartServers 64
MaxClients 256

12. what version of vB are you running ?

3.0 RC 4


Any help you can provide would be greatly appreciated. I have reached the limit of my ability to optimize these things and we still have some pretty large slowdowns during busy times.
Colosus
Mon 15th Mar '04, 2:11pm
Just to give you a top of when we actually have our average users on... 237 people online right now.

PHP/HTML Server

last pid: 45894; load averages: 26.33, 25.74, 25.11 up 5+18:03:09 13:12:43
269 processes: 2 running, 217 sleeping, 1 zombie, 49 lock
CPU states: 13.6% user, 0.0% nice, 27.5% system, 0.9% interrupt, 58.1% idle
Mem: 1333M Active, 141M Inact, 174M Wired, 124K Cache, 112M Buf, 355M Free
Swap: 4080M Total, 2208K Used, 4078M Free

PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
41464 www 102 0 23316K 14100K *Giant 0 12:33 2.73% 2.73% httpd
42155 www 102 0 18960K 7576K *Giant 3 9:56 2.39% 2.39% httpd
44540 www 102 0 23328K 11888K *Giant 3 2:54 2.39% 2.39% httpd
41562 www 76 0 24248K 15832K select 0 0:45 2.05% 2.05% httpd
41479 www 101 0 23416K 14280K *Giant 0 15:24 2.00% 2.00% httpd
45269 www 76 0 23464K 12040K select 0 0:17 1.95% 1.95% httpd
44805 www 76 0 23864K 12440K select 0 0:15 1.95% 1.95% httpd
43203 www 76 0 24204K 12780K select 0 0:49 1.71% 1.71% httpd
45187 www 98 0 23676K 12276K *Giant 1 0:15 1.71% 1.71% httpd
45872 www 76 0 23352K 11868K select 0 0:01 1.68% 1.66% httpd
42142 www 76 0 23980K 12560K select 0 0:52 1.61% 1.61% httpd
45407 www 76 0 24056K 12628K select 0 0:13 1.51% 1.51% httpd
45301 www 76 0 23764K 12344K select 0 0:10 1.46% 1.46% httpd
44228 www 76 0 24092K 12668K select 0 0:42 1.37% 1.37% httpd
44810 www 76 0 23992K 12568K select 0 0:17 1.27% 1.27% httpd
45402 www 76 0 23316K 11892K select 2 0:13 1.27% 1.27% httpd
41500 www 99 0 23420K 14008K *Giant 3 4:39 1.17% 1.17% httpd
41494 www 99 0 23792K 14652K *Giant 1 3:09 1.17% 1.17% httpd
45399 www 99 0 22392K 10828K *Giant 3 0:39 1.17% 1.17% httpd
44541 www 76 0 23896K 12476K select 0 0:19 1.17% 1.17% httpd
43461 www 99 0 23792K 12368K *Giant 1 2:23 1.12% 1.12% httpd
44198 www 99 0 23276K 11840K *Giant 1 2:20 1.12% 1.12% httpd
43226 www 99 0 23640K 12208K *Giant 1 2:03 1.12% 1.12% httpd
44057 www 99 0 23780K 12348K *Giant 0 1:15 1.12% 1.12% httpd
44164 www 76 0 24660K 13232K select 0 0:34 1.12% 1.12% httpd
41476 www 99 0 20736K 11456K *Giant 3 16:25 1.07% 1.07% httpd
41517 www 99 0 20784K 12100K *Giant 1 15:54 1.07% 1.07% httpd
41573 www 99 0 23320K 14208K *Giant 1 11:57 1.07% 1.07% httpd
42106 www 99 0 19336K 7864K *Giant 3 9:58 1.07% 1.07% httpd
41628 www 99 0 24612K 15992K *Giant 0 3:44 1.07% 1.07% httpd
43185 www 99 0 23964K 12536K *Giant 1 3:22 1.07% 1.07% httpd
42907 www 99 0 23856K 12428K *Giant 0 2:44 1.07% 1.07% httpd
41646 www 99 0 23764K 12544K *Giant 3 2:31 1.07% 1.07% httpd
41564 www 99 0 23232K 14324K *Giant 0 2:22 1.07% 1.07% httpd
43318 www 99 0 24024K 12600K *Giant 0 1:47 1.07% 1.07% httpd
44499 www 99 0 23404K 12008K *Giant 3 1:01 1.07% 1.07% httpd
41647 www 99 0 23748K 12428K *Giant 3 7:23 1.03% 1.03% httpd
41547 www 99 0 23828K 14624K *Giant 3 6:52 1.03% 1.03% httpd
42112 www 99 0 23236K 11812K *Giant 3 4:51 1.03% 1.03% httpd
43202 www 99 0 23348K 11924K *Giant 3 4:16 1.03% 1.03% httpd
41991 www 99 0 23800K 12396K *Giant 0 2:53 1.03% 1.03% httpd
44375 www 99 0 23392K 11936K *Giant 1 2:06 1.03% 1.03% httpd
42083 www 99 0 24092K 12672K *Giant 3 1:52 1.03% 1.03% httpd
43872 www 99 0 23644K 12248K *Giant 1 1:08 1.03% 1.03% httpd
42077 www 99 0 24408K 13064K *Giant 3 1:04 1.03% 1.03% httpd
45316 www 99 0 23348K 11916K *Giant 3 0:35 1.03% 1.03% httpd
45608 www 76 0 23248K 11820K select 0 0:08 1.03% 1.03% httpd
41543 www 99 0 20772K 11324K *Giant 3 18:22 0.98% 0.98% httpd
41688 www 99 0 22304K 10756K *Giant 3 14:04 0.98% 0.98% httpd
41603 www 99 0 23796K 13984K *Giant 0 3:48 0.98% 0.98% httpd
44497 www 99 0 24292K 12884K *Giant 3 1:00 0.98% 0.98% httpd
45317 www 99 0 22376K 10828K *Giant 3 0:47 0.98% 0.98% httpd
45403 www 99 0 22364K 10824K *Giant 1 0:38 0.98% 0.98% httpd
45113 www 76 0 23612K 12184K select 0 0:17 0.98% 0.98% httpd


mySQL Server

last pid: 12291; load averages: 0.10, 0.09, 0.08 up 3+11:13:24 13:14:52
27 processes: 1 running, 26 sleeping
CPU states: 1.3% user, 0.0% nice, 1.4% system, 0.3% interrupt, 97.1% idle
Mem: 102M Active, 2032M Inact, 208M Wired, 140K Cache, 112M Buf, 1547M Free
Swap: 4096M Total, 4096M Free

PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
11939 mysql 96 0 467M 92996K select 0 16:06 6.59% 6.59% mysqld
504 root 96 0 6104K 3116K select 2 0:14 0.00% 0.00% httpd
468 root 96 0 3500K 2088K select 1 0:05 0.00% 0.00% sendmail
462 root 96 0 3488K 1864K select 2 0:01 0.00% 0.00% sshd
485 root 8 0 1336K 852K nanslp 2 0:01 0.00% 0.00% cron
323 root 96 0 1312K 724K select 0 0:01 0.00% 0.00% syslogd
471 smmsp 20 0 3404K 1860K pause 0 0:00 0.00% 0.00% sendmail
12285 root 4 0 6240K 2248K sbwait 0 0:00 0.00% 0.00% sshd
12288 colosus 20 0 2280K 1636K pause 0 0:00 0.00% 0.00% csh
568 www 4 0 6160K 3436K select 0 0:00 0.00% 0.00% httpd
11914 root 8 0 924K 348K wait 0 0:00 0.00% 0.00% sh
12287 colosus 96 0 6240K 2320K select 0 0:00 0.00% 0.00% sshd
12291 colosus 96 0 2216K 1336K CPU2 0 0:00 0.00% 0.00% top
567 www 4 0 6160K 3416K select 0 0:00 0.00% 0.00% httpd
565 www 96 0 6160K 3416K select 0 0:00 0.00% 0.00% httpd
566 www 4 0 6160K 3416K select 0 0:00 0.00% 0.00% httpd
564 www 4 0 6160K 3412K select 0 0:00 0.00% 0.00% httpd
557 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
3539 www 4 0 6160K 3424K select 0 0:00 0.00% 0.00% httpd
563 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
560 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
558 root 5 0 1276K 724K ttyin 1 0:00 0.00% 0.00% getty
561 root 5 0 1276K 724K ttyin 1 0:00 0.00% 0.00% getty
559 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
556 root 5 0 1276K 724K ttyin 0 0:00 0.00% 0.00% getty
562 root 5 0 1276K 724K ttyin 1 0:00 0.00% 0.00% getty
231 root 20 0 1180K 500K pause 0 0:00 0.00% 0.00% adjkerntz
eva2000
Tue 16th Mar '04, 5:04am
Colosus that seems like very very high loads for such powerful hardware. Are they all in the same datacenter these servers and how are they connected to each other ?

the load seems to be on the apache/web server

i'm not very familiar with FreeBSD but have you compiled the kernel for smp /dual cpu support ?
Colosus
Tue 16th Mar '04, 1:20pm
Colosus that seems like very very high loads for such powerful hardware. Are they all in the same datacenter these servers and how are they connected to each other ?

the load seems to be on the apache/web server

i'm not very familiar with FreeBSD but have you compiled the kernel for smp /dual cpu support ?

They are all on the same switch in the same colo. The load does seem to be on the web server, but we had the mySQL setup for single process mode and it would lock up (load would look fine, but could not access database). I assume it was locking the entire post table for some reason or another (which is 3gb) and would lose track of whatever it was doing. We'd have to kill mysqld and then restart it, and everything would come back fine.

I have since switched it to threaded mode and that problem doesn't seem to be happening anymore, but the load is still much higher than I want it to be. (well over 10).

BSD is compiled for smp mode, but there is an update coming soon with the next stable release, but that could be a few months away. I was hoping you could see something in our config files that I might be missing.
eva2000
Wed 17th Mar '04, 10:30am
When you see table locking what are the queries that are getting locked ?
YogiTW
Wed 17th Mar '04, 5:41pm
It's not really table locking. The mysql process hangs but the load isn't all that high when it happens so it's not like it's flying out of control.
eva2000
Wed 17th Mar '04, 7:44pm
hmmm hard to pin point the problem from here. maybe try reducing the httpd.conf settings for web server from

PHP/HTML
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 48
MaxSpareServers 96
StartServers 64
MaxClients 256

to

PHP/HTML
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 24
MaxSpareServers 48
StartServers 64
MaxClients 256

and increasing memory limit in PHP.ini from 8M to 16M and restart apache first

You mean your post table is 3GB in size ? I'd also check your log files to see if any run up against linux 2GB or 4GB file size limits as well as checking your apache error log for an errors
YogiTW
Thu 18th Mar '04, 12:14pm
Post table is 2.2 gigs, over 4 million records.

It might be an OS issue.
eva2000
Thu 18th Mar '04, 12:42pm
yeah i'd take it up with your host or the linux distro's support/mailing lists to see if you're running up against an OS file size limit
YogiTW
Thu 18th Mar '04, 1:22pm
We're actually running FreeBSD 5.2. Not sure if there's an issue between MySQL and this version of the OS.