News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Pages taking way-too-long-to load help?

Started by am71, July 05, 2011, 01:40:24 PM

Previous topic - Next topic

am71

Asking for patience in advance, as I am not that technical.  :)

My forum takes several minutes to load any page for many members but not all. Especially any page that returns unread posts or topics. Regardless of which browser they use. I was not sure if the problem was on their end or mine, so I searched this forum and learned that I should enable "Display time taken to create every page" to determine exactly how long the pages were load. I asked them to report how long each page was taking to load - did that and determined the site was, in fact, taking way too long to load pages for them. (Note, me and many others do not have this problem - only sometimes.)

So now I'm totally confused. Why do the same pages take longer for some, but not others?  :-\

Here are the results from users with fast load times and those with slow load times from the same page:

Main index page:
Fast: Page created in 0.768 seconds with 15 queries.  :)
Slow: Page created in 201.49 seconds with 14 queries. :(

Clicking "Show all unread posts since last visit"
Fast: Page created in 0.539 seconds with 14 queries.  :)
Slow: Page created in 298.94 seconds with 17 queries.  :(

Clicking the "new" button to read the most recent post in a thread:
Fast: Page created in 0.253 seconds with 27 queries.  :)
Slow: Page created in 253.98 seconds with 19 queries.  :(


:o I also have some users who occasionally can't load the site at all - "Server taking too long to respond" errors.   :o

I found the thread on this forum with suggestions to make SMF faster - Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)  http://www.simplemachines.org/community/index.php?topic=293441.msg1935333
I did not do any of the coding suggestions, as I lack the ability to do that. But I did everything else and it's still a problem.

Here are my forum stats:

Running SMF 1.1.14 on a VPS server with 3GB of ram - hosted by a good hosting service. Running only 1 other SMF forum with minor mods - that forum is fast, no problems.

My forum is very small - 80 users averaging 100 posts per day.

DEFAULT theme only - users can only use the default theme.

Mods installed - note, this problem occurs with and without mods:

Mod Name   Version

SMF Staff Page   1.7
Drafts (for SMF 2.0 / SMF 1.1)   2.0.1
KeyCAPTCHA for SMF   2.0
Enhanced Profile   1.1.5
YouTube BBCode   2.6
Unread and Replies Tabs in Main Menu   2.0
Reply Button in All Posts   1.1
Country Flags   1.1.2
Global Headers Footers   2.0
notCaptcha   1.06
AjaxChat Integration   3.2.1
SMF 1.1.14 Update   1.0
Users Online Today Mod   1.4.0

I perform all the maintenance tasks and back up weekly.


Can anyone help a lay-person like me figure out why my forum is slow for some, but not others?

Any insight or advice would be greatly appreciated.

Thank you in advance for your help.

tinoest

Is it a linux or windows host OS and do you have access to the command line?


am71

It is a Windows 2008 VPS. I have full control of the server...

It is running Plesk panel 9.5. I am hosting a couple of webpages from it. One of them is another forum... that forum doesn't suffer as mush of a slow down BUT has more members and does more traffic.

vbgamer45

Check mysql and see if there are lot of queries backed up or taking a long time.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

am71

Not being a SQL person, is there an easy to check this?

vbgamer45

Mysql Adminsitrator tool on windows or Database Workbench

If you want to use command line connect via mysql and run command show full processlist;
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

tinoest

There is also the status.php from this thread that you could use:

http://www.simplemachines.org/community/index.php?topic=49999.0

That would probably prove to be a good start.

am71

#7
OK, so I ran status.php and this was the result:

Quote
MySQL Statistics
MySQL 5.0.x
Connections per second:    0.4891
Kilobytes received per second:    1.6421
Kilobytes sent per second:    0.8797
Queries per second:    7.6388
Percentage of slow queries:    0.0001
Opened vs. Open tables: (table_cache)    3232.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.0097 (should be <= 0.01)
Key buffer write hit rate: (key_buffer_size)    0.5748 (should be <= 0.5)
Thread cache hit rate: (thread_cache_size)    172.1308 (should be >= 30 )
Thread cache usage: (thread_cache_size)    0.75 (should be >= 0.7 and <= 0.9)
Temporary table disk usage: (tmp_table_size)    0.4634 (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)


I see some issues already, but I have no idea on how to correct them.

Help?

Many thanks



am71

#8
Should add the more info in case it is needed:

MySQL status
Aborted_clients: 4
Aborted_connects: 1027
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 3784256842
Bytes_sent: 2040417203
Com_admin_commands: 0
Com_alter_db: 0
Com_alter_table: 10
Com_analyze: 0
Com_backup_table: 0
Com_begin: 0
Com_call_procedure: 0
Com_change_db: 1099489
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: 5221
Com_create_user: 0
Com_dealloc_sql: 0
Com_delete: 478442
Com_delete_multi: 0
Com_do: 0
Com_drop_db: 0
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 5229
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: 645438
Com_insert_select: 2964
Com_kill: 0
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 0
Com_optimize: 937
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: 144191
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: 9957067
Com_set_option: 1099856
Com_show_binlog_events: 0
Com_show_binlogs: 5
Com_show_charsets: 68
Com_show_collations: 68
Com_show_column_types: 0
Com_show_create_db: 0
Com_show_create_table: 1
Com_show_databases: 0
Com_show_errors: 0
Com_show_fields: 139
Com_show_grants: 24
Com_show_innodb_status: 0
Com_show_keys: 122
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: 3
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 4
Com_show_storage_engines: 0
Com_show_tables: 47
Com_show_triggers: 0
Com_show_variables: 224
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: 16
Com_unlock_tables: 0
Com_update: 2652544
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: 1100694
Created_tmp_disk_tables: 9844
Created_tmp_files: 2163
Created_tmp_tables: 21245
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 0
Flush_commands: 1
Handler_commit: 5538
Handler_delete: 467940
Handler_discover: 0
Handler_prepare: 0
Handler_read_first: 1109042
Handler_read_key: 184737949
Handler_read_next: 346815917
Handler_read_prev: 1695666
Handler_read_rnd: 817108
Handler_read_rnd_next: 518284138
Handler_rollback: 0
Handler_savepoint: 0
Handler_savepoint_rollback: 0
Handler_update: 9234828
Handler_write: 18360751
Innodb_buffer_pool_pages_data: 598
Innodb_buffer_pool_pages_dirty: 0
Innodb_buffer_pool_pages_flushed: 72647
Innodb_buffer_pool_pages_free: 426
Innodb_buffer_pool_pages_latched: 0
Innodb_buffer_pool_pages_misc: 0
Innodb_buffer_pool_pages_total: 1024
Innodb_buffer_pool_read_ahead_rnd: 3
Innodb_buffer_pool_read_ahead_seq: 2
Innodb_buffer_pool_read_requests: 63220790
Innodb_buffer_pool_reads: 115
Innodb_buffer_pool_wait_free: 0
Innodb_buffer_pool_write_requests: 13559722
Innodb_data_fsyncs: 43404
Innodb_data_pending_fsyncs: 0
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 5197824
Innodb_data_reads: 194
Innodb_data_writes: 104735
Innodb_data_written: 2828943872
Innodb_dblwr_pages_written: 72647
Innodb_dblwr_writes: 7518
Innodb_log_waits: 0
Innodb_log_write_requests: 946373
Innodb_log_writes: 19879
Innodb_os_log_fsyncs: 24367
Innodb_os_log_pending_fsyncs: 0
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 446049792
Innodb_page_size: 16384
Innodb_pages_created: 414
Innodb_pages_read: 184
Innodb_pages_written: 72647
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: 40
Innodb_rows_inserted: 6494570
Innodb_rows_read: 14147819
Innodb_rows_updated: 1214
Key_blocks_not_flushed: 0
Key_blocks_unused: 5522
Key_blocks_used: 7173
Key_read_requests: 473448710
Key_reads: 4636264
Key_write_requests: 3619593
Key_writes: 2080363
Last_query_cost: 0.000000
Max_used_connections: 40
Not_flushed_delayed_rows: 0
Open_files: 109
Open_streams: 0
Open_tables: 64
Opened_tables: 207146
Prepared_stmt_count: 0
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: 17192015
Rpl_status: NULL
Select_full_join: 34167
Select_full_range_join: 454
Select_range: 2638967
Select_range_check: 0
Select_scan: 2776272
Slave_open_temp_tables: 0
Slave_retried_transactions: 0
Slave_running: OFF
Slow_launch_threads: 38
Slow_queries: 1154
Sort_merge_passes: 1079
Sort_range: 95124
Sort_rows: 23856521
Sort_scan: 56099
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: 20097295
Table_locks_waited: 10466
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 3
Threads_connected: 1
Threads_created: 6391
Threads_running: 1
Uptime: 2250806
Uptime_since_flush_status: 2250806

MySQL variables
auto_increment_increment: 1
auto_increment_offset: 1
automatic_sp_privileges: ON
back_log: 50
basedir: C:\Program Files (x86)\Parallels\Plesk\Databases\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: C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\share\charsets\
collation_connection: latin1_swedish_ci
collation_database: latin1_swedish_ci
collation_server: latin1_swedish_ci
completion_type: 0
concurrent_insert: 1
connect_timeout: 5
datadir: C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\Data\
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: 1800
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: YES
have_bdb: NO
have_blackhole_engine: YES
have_compress: YES
have_crypt: NO
have_csv: NO
have_dynamic_loading: YES
have_example_engine: NO
have_federated_engine: YES
have_geometry: YES
have_innodb: YES
have_isam: NO
have_merge_engine: YES
have_ndbcluster: NO
have_openssl: DISABLED
have_ssl: DISABLED
have_query_cache: YES
have_raid: NO
have_rtree_keys: YES
have_symlink: YES
hostname: VPS2887
init_connect:
init_file:
init_slave:
innodb_additional_mem_pool_size: 2097152
innodb_autoextend_increment: 8
innodb_buffer_pool_awe_mem_mb: 0
innodb_buffer_pool_size: 16777216
innodb_checksums: ON
innodb_commit_concurrency: 0
innodb_concurrency_tickets: 500
innodb_data_file_path: ibdata1:10M:autoextend
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: 10485760
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_rollback_on_timeout: OFF
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: 131072
key_buffer_size: 8388600
key_cache_age_threshold: 300
key_cache_block_size: 1024
key_cache_division_limit: 100
language: C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\share\english\
large_files_support: ON
large_page_size: 0
large_pages: OFF
lc_time_names: en_US
license: GPL
local_infile: ON
log: OFF
log_bin: OFF
log_bin_trust_function_creators: OFF
log_error: C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\Data\VPS2887.err
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: ON
lower_case_table_names: 1
max_allowed_packet: 1048576
max_binlog_cache_size: 4294967295
max_binlog_size: 1073741824
max_connect_errors: 10
max_connections: 400
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_sp_recursion_depth: 0
max_tmp_tables: 32
max_user_connections: 30
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: 8388608
myisam_stats_method: nulls_unequal
named_pipe: OFF
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 30
new: OFF
old_passwords: ON
open_files_limit: 2000
optimizer_prune_level: 1
optimizer_search_depth: 62
pid_file: C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\Data\VPS2887.pid
port: 3306
preload_buffer_size: 32768
profiling: OFF
profiling_history_size: 15
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: 1044480
read_only: OFF
read_rnd_buffer_size: 258048
relay_log_purge: ON
relay_log_space_limit: 0
rpl_recovery_rank: 0
secure_auth: OFF
secure_file_priv:
shared_memory: OFF
shared_memory_base_name: MYSQL
server_id: 0
skip_external_locking: ON
skip_networking: OFF
skip_show_database: OFF
slave_compressed_protocol: OFF
slave_load_tmpdir: C:\Windows\TEMP\
slave_net_timeout: 3600
slave_skip_errors: OFF
slave_transaction_retries: 10
slow_launch_time: 2
sort_buffer_size: 262136
sql_big_selects: ON
sql_mode:
sql_notes: ON
sql_warnings: OFF
ssl_ca:
ssl_capath:
ssl_cert:
ssl_cipher:
ssl_key:
storage_engine: InnoDB
sync_binlog: 0
sync_frm: ON
system_time_zone: Eastern Daylight Time
table_cache: 64
table_lock_wait_timeout: 50
table_type: InnoDB
thread_cache_size: 4
thread_stack: 196608
time_format: %H:%i:%s
time_zone: SYSTEM
timed_mutexes: OFF
tmp_table_size: 33554432
tmpdir: C:\Windows\TEMP\
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: REPEATABLE-READ
updatable_views_with_limit: YES
version: 5.0.45-community-nt
version_comment: MySQL Community Edition (GPL)
version_compile_machine: ia32
version_compile_os: Win32
wait_timeout: 28800



Thanks!

tinoest

#9
For a short term fix you can restart mysql on that server and see if the load times decrease, if they do its due to the MySQL conf file needing some tweaking.

I don't know where that is located on a Windows box, but i'll get the config from my server shortly.

Also what was displayed in the basic information section?

am71

Hi. Thanks for the reply. I ended up having to reboot the server and that fired things up again.

This is all I get in the basic information section:

Quote
Basic Information
July 07, 2011, 03:42:01 PM
Operating System:    Microsoft Windows [Version 6.1.7601]

Not very helpful, I know...

Thanks

tinoest

If the speed improvements are to be seen after a restart, I would hazard a guess at its due to either your my.cnf or your httpd.conf and the server not clearing out old workers quick enough.

Can you post both of those if possible?

The following link might be of some use with regards to configuring apache

http://httpd.apache.org/docs/1.3/windows.html#use


Advertisement: