Degraded Performance

[DSH]

My forum performance seems to be degrading. It currently has about 470 members and 13,000 posts.

It is running on a VPS. Here is the information from status.php. Does anyone see anything that could be causing these problems?

Code Select Expand



Basic Information
February 09, 2008, 03:53:25 PM
Operating System: Fedora Core release 4 (Stentz)
Processor: Intel® Xeon(TM) CPU 3.20GHz (3193.018MHz)
Load averages: 0.15, 0.31, 0.51
Current processes: 30 (28 sleeping, 2 running, 0 zombie)
Processes by CPU: httpd (7) 1.3%, mysqld (1) 0.7%, (other) (18) 0.0%
Memory usage: 99.628% (8268328k / 8299160k)
Swap: 0.167% (27400k / 16386292k)
MySQL processes
Total processes: 2 (1 sleeping, 1 running, 0 locked)
MySQL Statistics
MySQL 4.1.x
Connections per second: 0.1473
Kilobytes received per second: 0.7937
Kilobytes sent per second: 8.536
Queries per second: 3.513
Percentage of slow queries: 0.0004
Opened vs. Open tables:
(table_cache) 179.3281 (should be <= 80)
Table cache usage:
(table_cache) 1 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size) 0.0006 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.5197 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.0842 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer) 0 (should be <= 0.001)
Query cache enabled:
(query_cache_type) 1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit) 1 (should be <= 0.5)
Query cache prune rate:
(query_cache_size) 0 (should be <= 0.05)

MySQL status
Aborted_clients: 8
Aborted_connects: 7
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 342889687
Bytes_sent: 3687444668
Com_admin_commands: 0
Com_alter_db: 0
Com_alter_table: 0
Com_analyze: 0
Com_backup_table: 0
Com_begin: 10478
Com_change_db: 69678
Com_change_master: 0
Com_check: 0
Com_checksum: 0
Com_commit: 10478
Com_create_db: 0
Com_create_function: 0
Com_create_index: 0
Com_create_table: 36
Com_dealloc_sql: 0
Com_delete: 43480
Com_delete_multi: 0
Com_do: 0
Com_drop_db: 0
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 36
Com_drop_user: 0
Com_execute_sql: 0
Com_flush: 0
Com_grant: 0
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_help: 0
Com_insert: 31451
Com_insert_select: 99
Com_kill: 0
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 15
Com_optimize: 41
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: 4489
Com_replace_select: 5
Com_reset: 0
Com_restore_table: 0
Com_revoke: 0
Com_revoke_all: 0
Com_rollback: 0
Com_savepoint: 0
Com_select: 972201
Com_set_option: 22644
Com_show_binlog_events: 0
Com_show_binlogs: 1
Com_show_charsets: 7
Com_show_collations: 7
Com_show_column_types: 0
Com_show_create_db: 15
Com_show_create_table: 864
Com_show_databases: 0
Com_show_errors: 0
Com_show_fields: 786
Com_show_grants: 2
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: 34
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 35
Com_show_storage_engines: 0
Com_show_tables: 908
Com_show_variables: 11064
Com_show_warnings: 0
Com_slave_start: 0
Com_slave_stop: 0
Com_stmt_close: 0
Com_stmt_execute: 0
Com_stmt_prepare: 0
Com_stmt_reset: 0
Com_stmt_send_long_data: 0
Com_truncate: 0
Com_unlock_tables: 15
Com_update: 240553
Com_update_multi: 0
Connections: 62150
Created_tmp_disk_tables: 19462
Created_tmp_files: 4
Created_tmp_tables: 231197
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 0
Flush_commands: 1
Handler_commit: 10493
Handler_delete: 26876
Handler_discover: 0
Handler_read_first: 60345
Handler_read_key: 24236512
Handler_read_next: 44949299
Handler_read_prev: 69513
Handler_read_rnd: 5475225
Handler_read_rnd_next: 68670514
Handler_rollback: 12218
Handler_update: 142697
Handler_write: 15787573
Key_blocks_not_flushed: 0
Key_blocks_unused: 6788
Key_blocks_used: 1362
Key_read_requests: 47748683
Key_reads: 28952
Key_write_requests: 179055
Key_writes: 93046
Max_used_connections: 12
Not_flushed_delayed_rows: 0
Open_files: 104
Open_streams: 0
Open_tables: 64
Opened_tables: 11477
Qcache_free_blocks: 0
Qcache_free_memory: 0
Qcache_hits: 0
Qcache_inserts: 0
Qcache_lowmem_prunes: 0
Qcache_not_cached: 0
Qcache_queries_in_cache: 0
Qcache_total_blocks: 0
Questions: 1481990
Rpl_status: NULL
Select_full_join: 281
Select_full_range_join: 0
Select_range: 84410
Select_range_check: 6
Select_scan: 302776
Slave_open_temp_tables: 0
Slave_retried_transactions: 0
Slave_running: OFF
Slow_launch_threads: 1
Slow_queries: 623
Sort_merge_passes: 0
Sort_range: 70436
Sort_rows: 5817673
Sort_scan: 290810
Ssl_accept_renegotiates: 0
Ssl_accepts: 0
Ssl_callback_cache_hits: 0
Ssl_cipher:
Ssl_cipher_list:
Ssl_client_connects: 0
Ssl_connect_renegotiates: 0
Ssl_ctx_verify_depth: 0
Ssl_ctx_verify_mode: 0
Ssl_default_timeout: 0
Ssl_finished_accepts: 0
Ssl_finished_connects: 0
Ssl_session_cache_hits: 0
Ssl_session_cache_misses: 0
Ssl_session_cache_mode: NONE
Ssl_session_cache_overflows: 0
Ssl_session_cache_size: 0
Ssl_session_cache_timeouts: 0
Ssl_sessions_reused: 0
Ssl_used_session_cache_entries: 0
Ssl_verify_depth: 0
Ssl_verify_mode: 0
Ssl_version:
Table_locks_immediate: 2494189
Table_locks_waited: 2866
Threads_cached: 0
Threads_connected: 2
Threads_created: 62149
Threads_running: 1
Uptime: 421864

MySQL variables
back_log: 50
basedir: /usr/
bdb_cache_size: 8388600
bdb_home:
bdb_log_buffer_size: 0
bdb_logdir:
bdb_max_lock: 10000
bdb_shared_data: OFF
bdb_tmpdir:
binlog_cache_size: 32768
bulk_insert_buffer_size: 8388608
character_set_client: latin1
character_set_connection: latin1
character_set_database: utf8
character_set_results: latin1
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/
collation_connection: latin1_swedish_ci
collation_database: utf8_general_ci
collation_server: latin1_swedish_ci
concurrent_insert: ON
connect_timeout: 5
datadir: /var/lib/mysql/
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
expire_logs_days: 0
flush: OFF
flush_time: 0
ft_boolean_syntax: + -><()~*:""&|
ft_max_word_len: 84
ft_min_word_len: 4
ft_query_expansion_limit: 20
ft_stopword_file: (built-in)
group_concat_max_len: 1024
have_archive: NO
have_bdb: DISABLED
have_blackhole_engine: NO
have_compress: YES
have_crypt: YES
have_csv: NO
have_example_engine: NO
have_geometry: YES
have_innodb: YES
have_isam: YES
have_ndbcluster: NO
have_openssl: YES
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: 524288
innodb_autoextend_increment: 8
innodb_buffer_pool_awe_mem_mb: 0
innodb_buffer_pool_size: 2097152
innodb_data_file_path: ibdata1:10M:autoextend
innodb_data_home_dir:
innodb_fast_shutdown: ON
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: 512000
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_table_locks: ON
innodb_thread_concurrency: 2
interactive_timeout: 28800
join_buffer_size: 131072
key_buffer_size: 8388600
key_cache_age_threshold: 300
key_cache_block_size: 1024
key_cache_division_limit: 100
language: /usr/share/mysql/english/
large_files_support: ON
license: GPL
local_infile: OFF
locked_in_memory: OFF
log: OFF
log_bin: OFF
log_error:
log_slave_updates: OFF
log_slow_queries: OFF
log_update: 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: 1048576
max_binlog_cache_size: 4294967295
max_binlog_size: 1073741824
max_connect_errors: 10
max_connections: 100
max_delayed_threads: 20
max_error_count: 64
max_heap_table_size: 16777216
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_tmp_tables: 32
max_user_connections: 0
max_write_lock_count: 4294967295
myisam_data_pointer_size: 4
myisam_max_extra_sort_file_size: 2147483648
myisam_max_sort_file_size: 2147483647
myisam_recover_options: OFF
myisam_repair_threads: 1
myisam_sort_buffer_size: 8388608
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: ON
open_files_limit: 1024
pid_file: /var/run/mysqld/mysqld.pid
port: 3306
preload_buffer_size: 32768
prepared_stmt_count: 0
protocol_version: 10
query_alloc_block_size: 8192
query_cache_limit: 1048576
query_cache_min_res_unit: 4096
query_cache_size: 0
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 2048
read_buffer_size: 131072
read_only: OFF
read_rnd_buffer_size: 262144
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_net_timeout: 3600
slave_transaction_retries: 0
slow_launch_time: 2
socket: /var/lib/mysql/mysql.sock
sort_buffer_size: 2097144
sql_mode:
sql_notes: ON
sql_warnings: ON
storage_engine: MyISAM
sync_binlog: 0
sync_frm: ON
sync_replication: 0
sync_replication_slave_id: 0
sync_replication_timeout: 0
system_time_zone: EST
table_cache: 64
table_type: MyISAM
thread_cache_size: 0
thread_stack: 196608
time_format: %H:%i:%s
time_zone: SYSTEM
tmp_table_size: 33554432
tmpdir:
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: REPEATABLE-READ
version: 4.1.20
version_bdb: Sleepycat Software: Berkeley DB 4.1.24: (May 24, 2006)
version_comment: Source distribution
version_compile_machine: i386
version_compile_os: redhat-linux-gnu
wait_timeout: 28800


[karlbenson]

Themes installed?
Mods Installed?
Forum link?

[DSH]

I am using the default theme (slightly modified). Also, there are no mods installed.

Here is a link to the forum: hxxp:www.metrojacksonville.com/forum/ [nonactive]

[Deaks]

the memory usage is pretty high on the server, this would cause a slow forum

Memory usage:    99.628% (8268328k / 8299160k)

[karlbenson]

hmmm.
I had a look at your forum.  It did initially take a bit to load.
I can't see anything on your forum that would take that long to load (especially on a VPS).

Are you running any other sites on that vps? are those slow aswell?
Have your tried optimizing your tables, and other forum maintenance.
See whether that has any effect.

You could enable the page creation times to see exactly how long its taking.
Other than that Maybe some one of the heavyweights of the forum can figure out from the status.php

It is recommended to upgrade to 1.1.4.  There were bugs and minor vulnerabilities fixed.  To keep your forum secure you should upgrade.

Also I did notice the smf copyrights are missing (or just invisible).  FYI, do you realise thats in violation of the SMF licence?

[DSH]

There are no other sites running on the VPS. I enabled the creation times and they range between about 1.5 seconds and 35 seconds. Obviously something is wrong. Joomla is also running on the site and is experiencing the same problems, so it's not SMF specific. I was hoping someone could see if there are any mySql issues from the status.php.

I'll have to look into the copyright issue. Hopefully upgrading to 1.1.4 will resolve that.

[青山 素子]

the memory usage is pretty high on the server, this would cause a slow forum

Memory usage:    99.628% (8268328k / 8299160k)

On Windows maybe. Linux and UNIX runs best with a full memory. Much of that is used for buffers and cache and is freed when applications need it. The real way to look is to see the usage on swap. There is a very small usage on swap, which means that at some time since the reboot, swap space was needed. It isn't large though, so unless it starts growing, things are fine in memory-land.


I don't see anything specific on that status page that indicates something wrong. Try to get the numbers from when the forum is acting slow.


As a further note, FC4 is unsupported, even for security updates. You might want to look at upgrading the OS at some point. If you are familiar with Fedora, CentOS is a nice choice, and has long support cycles.

[DSH]

It seems that this was caused by a problem on the server. I haven't been able to find out what the problem was yet, but they claim it has been fixed. So far it looks much better.

Also, the copyright notice problem has been resolved.