Sidewindr
Wed 23rd Aug '06, 12:59am
Ok here's an Optimisation Request for my new server...
1. This is on a Dedicated Server
2. Specs
cpu speed/type single or dual cpus): Dual Opteron 285 2.6GHz
how much memory installed: 3072Mb RAM
hard drive type/configuration: 2x74Gb U320 Hardware Raid1 Mirror (System) and 2x 140GB U320 Hardware Raid1 Mirror (Database)
linux distributor or windows version: Solaris10 x86
apache version: 2.2.0
PHP version: php 5.1.4
MySQL version: mysql 5.0.24
APC Version 3.0.11
3. Not sure if I have any innodb tables .. How do I tell ??
4. MYSQL Configure..
CC=gcc CFLAGS="-O3 -fpic " CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti -fpic" ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --with-named-curses-libs=-lncurses --with-embedded-server --enable-shared --with-gnu-ld --with-zlib-dir=/usr/local --with-berkeley-db-libs=/usr/local/BerkeleyDB.4.2
5. Top on the machine looks like this ..
last pid: 19036; load avg: 0.23, 0.22, 0.23; up 1+03:47:17 14:00:19
60 processes: 59 sleeping, 1 on cpu
CPU states: 96.5% idle, 3.1% user, 0.4% kernel, 0.0% iowait, 0.0% swap
Memory: 3072M phys mem, 2383M free mem, 8205M swap, 8205M free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
18992 nobody 1 59 0 55M 22M sleep 0:06 4.29% httpd
18996 nobody 1 59 0 56M 22M sleep 0:02 3.82% httpd
18882 nobody 1 59 0 54M 21M sleep 0:02 3.28% httpd
428 mysql 18 59 -5 170M 137M sleep 46:04 1.10% mysqld
13889 root 1 59 0 3396K 1836K sleep 4:56 0.37% top
19036 nobody 1 59 0 47M 6728K sleep 0:00 0.21% httpd
18798 nobody 1 59 0 56M 22M sleep 0:04 0.08% httpd
19013 nobody 1 59 0 54M 20M sleep 0:01 0.07% httpd
19019 nobody 1 59 0 54M 17M sleep 0:00 0.07% httpd
18998 nobody 1 59 0 54M 19M sleep 0:00 0.07% httpd
19029 root 1 59 0 3284K 1732K cpu 0:00 0.07% top
19033 nobody 1 59 0 53M 15M sleep 0:00 0.04% httpd
19030 nobody 1 59 0 47M 6996K sleep 0:00 0.04% httpd
19015 nobody 1 59 0 53M 18M sleep 0:00 0.04% httpd
18922 nobody 1 59 0 55M 20M sleep 0:02 0.04% httpd
6. my.cnf looks like this...
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
bind-address = 127.0.0.1
socket = /tmp/mysql.sock
back_log = 50
skip-innodb
max_connections = 500
key_buffer = 48M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 512
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 6M
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
ft_min_word_len = 3
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
ft_min_word_len = 3
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
7. MYSQL Stats
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /usr/local/mysql/var/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 3 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_ndbcluster | NO |
| have_merge_engine | YES |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| 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_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 50331648 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 67107840 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 67107840 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 4110 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /usr/local/mysql/var/bourbon.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 131072 |
| query_cache_limit | 6291456 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 100663296 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 65536 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /var/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | EST |
| table_cache | 1800 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 512 |
| thread_concurrency | 10 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 134217728 |
| tmpdir | /var/tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.24 |
| version_comment | Source distribution |
| version_compile_machine | i386 |
| version_compile_os | pc-solaris2.10 |
| wait_timeout | 120 |
+---------------------------------+----------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 204 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 928555782 |
| Bytes_sent | 421863765 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 580212 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 6466 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 3 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 62400 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 3 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 6730 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 713669 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 3 |
| Com_show_triggers | 0 |
| Com_show_variables | 29 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 3 |
| Com_update | 387002 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 580224 |
| Created_tmp_disk_tables | 9177 |
| Created_tmp_files | 162 |
| Created_tmp_tables | 122116 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 21237 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 212895 |
| Handler_read_key | 22164511 |
| Handler_read_next | 15409798 |
| Handler_read_prev | 3073240 |
| Handler_read_rnd | 8934925 |
| Handler_read_rnd_next | 1746325074 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 587393 |
| Handler_write | 8410485 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 4246 |
| Key_blocks_used | 43612 |
| Key_read_requests | 98530787 |
| Key_reads | 114132 |
| Key_write_requests | 265909 |
| Key_writes | 193355 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 16 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 341 |
| Open_streams | 0 |
| Open_tables | 198 |
| Opened_tables | 428 |
| Qcache_free_blocks | 2807 |
| Qcache_free_memory | 68742640 |
| Qcache_hits | 2313576 |
| Qcache_inserts | 712303 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1410 |
| Qcache_queries_in_cache | 14128 |
| Qcache_total_blocks | 31397 |
| Questions | 4650227 |
| Rpl_status | NULL |
| Select_full_join | 726 |
| Select_full_range_join | 0 |
| Select_range | 196526 |
| Select_range_check | 0 |
| Select_scan | 137651 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 84 |
| Sort_range | 156601 |
| Sort_rows | 12018290 |
| Sort_scan | 97039 |
| Table_locks_immediate | 2265965 |
| Table_locks_waited | 2773 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 14 |
| Threads_connected | 2 |
| Threads_created | 16 |
| Threads_running | 1 |
| Uptime | 99633 |
+-----------------------------------+------------+
Uptime: 99633 Threads: 2 Questions: 4650228 Slow queries: 0 Opens: 428 Flush tables: 1 Open tables: 198 Queries per second avg: 46.674
mysqladmin Ver 8.41 Distrib 5.0.24, for pc-solaris2.10 on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.24
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 day 3 hours 40 min 33 sec
Threads: 2 Questions: 4650228 Slow queries: 0 Opens: 428 Flush tables: 1 Open tables: 198 Queries per second avg: 46.674
8. phpAdsNew v2.0.8 is also running on the server and using mysql. Other than that just vbulletin. Dedicated forum server.
9. Average Online Users is around the 150 mark with around 2000 users per day and max users was around 500 concurrently, Cookie Timeout is 600.
10. Phpinfo .. http://www.ls1.com.au/forum/?&do=phpinfo
11. None of these configuration tags appear in httpd.conf.
12. vBulletin 3.6.0
13. No big file problems.
14.
bash-3.00# uname -a
SunOS bourbon 5.10 Generic_118855-15 i86pc i386 i86pc
bash-3.00# ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
open files (-n) 65536
pipe size (512 bytes, -p) 10
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 16357
virtual memory (kbytes, -v) unlimited
bash-3.00# psrinfo -v
Status of virtual processor 0 as of: 08/19/2006 14:13:17
on-line since 08/19/2006 13:26:27.
The i386 processor operates at 2600 MHz,
and has an i387 compatible floating point processor.
Status of virtual processor 1 as of: 08/19/2006 14:13:17
on-line since 08/19/2006 13:26:30.
The i386 processor operates at 2600 MHz,
and has an i387 compatible floating point processor.
Status of virtual processor 2 as of: 08/19/2006 14:13:17
on-line since 08/19/2006 13:26:32.
The i386 processor operates at 2600 MHz,
and has an i387 compatible floating point processor.
Status of virtual processor 3 as of: 08/19/2006 14:13:17
on-line since 08/19/2006 13:26:34.
The i386 processor operates at 2600 MHz,
and has an i387 compatible floating point processor.
1. This is on a Dedicated Server
2. Specs
cpu speed/type single or dual cpus): Dual Opteron 285 2.6GHz
how much memory installed: 3072Mb RAM
hard drive type/configuration: 2x74Gb U320 Hardware Raid1 Mirror (System) and 2x 140GB U320 Hardware Raid1 Mirror (Database)
linux distributor or windows version: Solaris10 x86
apache version: 2.2.0
PHP version: php 5.1.4
MySQL version: mysql 5.0.24
APC Version 3.0.11
3. Not sure if I have any innodb tables .. How do I tell ??
4. MYSQL Configure..
CC=gcc CFLAGS="-O3 -fpic " CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti -fpic" ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --with-named-curses-libs=-lncurses --with-embedded-server --enable-shared --with-gnu-ld --with-zlib-dir=/usr/local --with-berkeley-db-libs=/usr/local/BerkeleyDB.4.2
5. Top on the machine looks like this ..
last pid: 19036; load avg: 0.23, 0.22, 0.23; up 1+03:47:17 14:00:19
60 processes: 59 sleeping, 1 on cpu
CPU states: 96.5% idle, 3.1% user, 0.4% kernel, 0.0% iowait, 0.0% swap
Memory: 3072M phys mem, 2383M free mem, 8205M swap, 8205M free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
18992 nobody 1 59 0 55M 22M sleep 0:06 4.29% httpd
18996 nobody 1 59 0 56M 22M sleep 0:02 3.82% httpd
18882 nobody 1 59 0 54M 21M sleep 0:02 3.28% httpd
428 mysql 18 59 -5 170M 137M sleep 46:04 1.10% mysqld
13889 root 1 59 0 3396K 1836K sleep 4:56 0.37% top
19036 nobody 1 59 0 47M 6728K sleep 0:00 0.21% httpd
18798 nobody 1 59 0 56M 22M sleep 0:04 0.08% httpd
19013 nobody 1 59 0 54M 20M sleep 0:01 0.07% httpd
19019 nobody 1 59 0 54M 17M sleep 0:00 0.07% httpd
18998 nobody 1 59 0 54M 19M sleep 0:00 0.07% httpd
19029 root 1 59 0 3284K 1732K cpu 0:00 0.07% top
19033 nobody 1 59 0 53M 15M sleep 0:00 0.04% httpd
19030 nobody 1 59 0 47M 6996K sleep 0:00 0.04% httpd
19015 nobody 1 59 0 53M 18M sleep 0:00 0.04% httpd
18922 nobody 1 59 0 55M 20M sleep 0:02 0.04% httpd
6. my.cnf looks like this...
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
bind-address = 127.0.0.1
socket = /tmp/mysql.sock
back_log = 50
skip-innodb
max_connections = 500
key_buffer = 48M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 512
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 6M
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
ft_min_word_len = 3
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
ft_min_word_len = 3
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
7. MYSQL Stats
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /usr/local/mysql/var/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 3 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_ndbcluster | NO |
| have_merge_engine | YES |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| 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_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 50331648 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 67107840 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 67107840 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 4110 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /usr/local/mysql/var/bourbon.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 131072 |
| query_cache_limit | 6291456 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 100663296 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 65536 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /var/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | EST |
| table_cache | 1800 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 512 |
| thread_concurrency | 10 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 134217728 |
| tmpdir | /var/tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.24 |
| version_comment | Source distribution |
| version_compile_machine | i386 |
| version_compile_os | pc-solaris2.10 |
| wait_timeout | 120 |
+---------------------------------+----------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 204 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 928555782 |
| Bytes_sent | 421863765 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 580212 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 6466 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 3 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 62400 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 3 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 6730 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 713669 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 3 |
| Com_show_triggers | 0 |
| Com_show_variables | 29 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 3 |
| Com_update | 387002 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 580224 |
| Created_tmp_disk_tables | 9177 |
| Created_tmp_files | 162 |
| Created_tmp_tables | 122116 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 21237 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 212895 |
| Handler_read_key | 22164511 |
| Handler_read_next | 15409798 |
| Handler_read_prev | 3073240 |
| Handler_read_rnd | 8934925 |
| Handler_read_rnd_next | 1746325074 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 587393 |
| Handler_write | 8410485 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 4246 |
| Key_blocks_used | 43612 |
| Key_read_requests | 98530787 |
| Key_reads | 114132 |
| Key_write_requests | 265909 |
| Key_writes | 193355 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 16 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 341 |
| Open_streams | 0 |
| Open_tables | 198 |
| Opened_tables | 428 |
| Qcache_free_blocks | 2807 |
| Qcache_free_memory | 68742640 |
| Qcache_hits | 2313576 |
| Qcache_inserts | 712303 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1410 |
| Qcache_queries_in_cache | 14128 |
| Qcache_total_blocks | 31397 |
| Questions | 4650227 |
| Rpl_status | NULL |
| Select_full_join | 726 |
| Select_full_range_join | 0 |
| Select_range | 196526 |
| Select_range_check | 0 |
| Select_scan | 137651 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 84 |
| Sort_range | 156601 |
| Sort_rows | 12018290 |
| Sort_scan | 97039 |
| Table_locks_immediate | 2265965 |
| Table_locks_waited | 2773 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 14 |
| Threads_connected | 2 |
| Threads_created | 16 |
| Threads_running | 1 |
| Uptime | 99633 |
+-----------------------------------+------------+
Uptime: 99633 Threads: 2 Questions: 4650228 Slow queries: 0 Opens: 428 Flush tables: 1 Open tables: 198 Queries per second avg: 46.674
mysqladmin Ver 8.41 Distrib 5.0.24, for pc-solaris2.10 on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.24
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 day 3 hours 40 min 33 sec
Threads: 2 Questions: 4650228 Slow queries: 0 Opens: 428 Flush tables: 1 Open tables: 198 Queries per second avg: 46.674
8. phpAdsNew v2.0.8 is also running on the server and using mysql. Other than that just vbulletin. Dedicated forum server.
9. Average Online Users is around the 150 mark with around 2000 users per day and max users was around 500 concurrently, Cookie Timeout is 600.
10. Phpinfo .. http://www.ls1.com.au/forum/?&do=phpinfo
11. None of these configuration tags appear in httpd.conf.
12. vBulletin 3.6.0
13. No big file problems.
14.
bash-3.00# uname -a
SunOS bourbon 5.10 Generic_118855-15 i86pc i386 i86pc
bash-3.00# ulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
open files (-n) 65536
pipe size (512 bytes, -p) 10
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 16357
virtual memory (kbytes, -v) unlimited
bash-3.00# psrinfo -v
Status of virtual processor 0 as of: 08/19/2006 14:13:17
on-line since 08/19/2006 13:26:27.
The i386 processor operates at 2600 MHz,
and has an i387 compatible floating point processor.
Status of virtual processor 1 as of: 08/19/2006 14:13:17
on-line since 08/19/2006 13:26:30.
The i386 processor operates at 2600 MHz,
and has an i387 compatible floating point processor.
Status of virtual processor 2 as of: 08/19/2006 14:13:17
on-line since 08/19/2006 13:26:32.
The i386 processor operates at 2600 MHz,
and has an i387 compatible floating point processor.
Status of virtual processor 3 as of: 08/19/2006 14:13:17
on-line since 08/19/2006 13:26:34.
The i386 processor operates at 2600 MHz,
and has an i387 compatible floating point processor.