Topic: README: Checklist for performance problems

[webvision]

thanks for sharing

[faminsk]

I installed staus.php onto the server I am using.  See the info I get from it below.  Why am I not getting the Basic Info? 

And now...what do I do with it?  I think the server folks have the MySQL settings...

Please advise.

I am using SMF 1.1.1

Basic Information
March 10, 2007, 09:50:39 AM
MySQL Statistics
MySQL 4.0.x
Connections per second:    9.611
Kilobytes received per second:    48.3823
Kilobytes sent per second:    42.3521
Queries per second:    285.3803
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    3225.4023 (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.0188 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.2717 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    70.4363 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.375 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.2726 (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)    0.2248 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0.5352 (should be <= 0.05)

MySQL status
Aborted_clients:    101
Aborted_connects:    8
Bytes_received:    2193784557
Bytes_sent:    1920357917
Com_admin_commands:    527
Com_alter_table:    73
Com_analyze:    1
Com_backup_table:    0
Com_begin:    998
Com_change_db:    640933
Com_change_master:    0
Com_check:    29
Com_commit:    446
Com_create_db:    1
Com_create_function:    0
Com_create_index:    0
Com_create_table:    23728
Com_delete:    124255
Com_delete_multi:    2
Com_drop_db:    1
Com_drop_function:    0
Com_drop_index:    0
Com_drop_table:    159
Com_flush:    3
Com_grant:    1
Com_ha_close:    0
Com_ha_open:    0
Com_ha_read:    0
Com_insert:    158618
Com_insert_select:    386
Com_kill:    0
Com_load:    0
Com_load_master_data:    0
Com_load_master_table:    0
Com_lock_tables:    2228
Com_optimize:    247
Com_purge:    0
Com_rename_table:    0
Com_repair:    36
Com_replace:    36858
Com_replace_select:    6
Com_reset:    0
Com_restore_table:    0
Com_revoke:    0
Com_rollback:    559
Com_savepoint:    0
Com_select:    2386983
Com_set_option:    63516
Com_show_binlog_events:    0
Com_show_binlogs:    30
Com_show_create:    44767
Com_show_databases:    34
Com_show_fields:    107665
Com_show_grants:    222
Com_show_keys:    1521
Com_show_logs:    0
Com_show_master_status:    0
Com_show_new_master:    0
Com_show_open_tables:    0
Com_show_processlist:    8
Com_show_slave_hosts:    0
Com_show_slave_status:    0
Com_show_status:    140
Com_show_innodb_status:    0
Com_show_tables:    53632
Com_show_variables:    3677
Com_slave_start:    0
Com_slave_stop:    0
Com_truncate:    19
Com_unlock_tables:    2245
Com_update:    316281
Connections:    425576
Created_tmp_disk_tables:    61507
Created_tmp_tables:    225653
Created_tmp_files:    81
Delayed_insert_threads:    0
Delayed_writes:    3
Delayed_errors:    0
Flush_commands:    1
Handler_commit:    218
Handler_delete:    123373
Handler_read_first:    467066
Handler_read_key:    79067871
Handler_read_next:    100441763
Handler_read_prev:    11379054
Handler_read_rnd:    12525976
Handler_read_rnd_next:    1440716491
Handler_rollback:    6491
Handler_update:    7499446
Handler_write:    17317002
Key_blocks_used:    249380
Key_read_requests:    148017336
Key_reads:    2783393
Key_write_requests:    1908123
Key_writes:    518383
Max_used_connections:    45
Not_flushed_key_blocks:    0
Not_flushed_delayed_rows:    0
Open_tables:    256
Open_files:    474
Open_streams:    0
Opened_tables:    825703
Questions:    12636639
Qcache_queries_in_cache:    19561
Qcache_inserts:    2034309
Qcache_hits:    8229453
Qcache_lowmem_prunes:    1277419
Qcache_not_cached:    348143
Qcache_free_memory:    21729776
Qcache_free_blocks:    7188
Qcache_total_blocks:    50437
Rpl_status:    NULL
Select_full_join:    53557
Select_full_range_join:    6277
Select_range:    88980
Select_range_check:    89
Select_scan:    603249
Slave_open_temp_tables:    0
Slave_running:    OFF
Slow_launch_threads:    0
Slow_queries:    444
Sort_merge_passes:    40
Sort_range:    145062
Sort_rows:    15502038
Sort_scan:    350626
Table_locks_immediate:    4392008
Table_locks_waited:    1157
Threads_cached:    3
Threads_created:    6042
Threads_connected:    22
Threads_running:    1
Uptime:    44280

MySQL variables
back_log:    50
basedir:    /usr/local/mysql-4.0.20-hw/
binlog_cache_size:    32768
bulk_insert_buffer_size:    8388608
character_set:    latin1
character_sets:    latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert:    ON
connect_timeout:    300
convert_character_set:    
datadir:    /usr/local/mysql/data/
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:    NO
have_crypt:    YES
have_innodb:    YES
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:    ibdata1:10M:autoextend
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:    268435456
language:    /usr/local/mysql-4.0.20-hw/share/mysql/english/
large_files_support:    ON
license:    GPL
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:    ON
long_query_time:    2
low_priority_updates:    OFF
lower_case_file_system:    OFF
lower_case_table_names:    0
max_allowed_packet:    16776192
max_binlog_cache_size:    4294967295
max_binlog_size:    1073741824
max_connections:    5000
max_connect_errors:    1000
max_delayed_threads:    20
max_insert_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:    20
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:    10
net_write_timeout:    60
new:    OFF
open_files_limit:    25010
pid_file:    /usr/local/mysql/data/ldb400.van.ca.siteprotect.com.pid
log_error:    /var/log/mysql/mysql-error.log
port:    3306
protocol_version:    10
query_alloc_block_size:    8192
query_cache_limit:    1048576
query_cache_size:    67108864
query_cache_type:    ON
query_prealloc_size:    8192
range_alloc_block_size:    2048
read_buffer_size:    1044480
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:    4194296
sql_mode:    0
table_cache:    256
table_type:    MYISAM
thread_cache_size:    8
thread_stack:    126976
tx_isolation:    REPEATABLE-READ
timezone:    PST
tmp_table_size:    33554432
tmpdir:    /tmp/
transaction_alloc_block_size:    8192
transaction_prealloc_size:    4096
version:    4.0.20-standard-log
version_comment:    Official MySQL-standard binary
version_compile_os:    pc-linux
wait_timeout:    28800

[Daniel15]

Why am I not getting the Basic Info?

Most likely, your web host disallows use of the system(), exec() or shell_exec() command. To check this, take a look at the disable_functions setting on a phpinfo page (What is phpinfo.php?) .

Note that it's a good idea to disable those functions

[faminsk]

Thanks....now if i can only figure out how to fix the indexing problems I am having and why some of these numbers are so whacked!!

Off the check phpinfo.php (that I know how to do).....OK back from checking.....there are no values in disable_functions

Travelin Man

[haco]

thank you

[Something like that]

Try specifying a custom avatar directory.

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

[Rubberman]

If you're having performance problems (or just want your forum to run faster), you'll want to go through the following steps to try to track them down:

• If you're using SMF 1.0.x, make sure the "Check avatar size every time it's displayed" setting is off.  This setting was removed in 1.1.
• Do you have eAccelerator or another optimizer installed?  What is eAccelerator?
• Do you have a lot of posts?  If so, have you converted any of your tables to InnoDB?
• Have MySQL's settings been tweaked to optimize memory usage and general efficiency?
• If you're not on a dedicated server, are you sure others on your server aren't hurting your forum's performance?  If you're not ready for a dedicated server now, think about planning for one when your forum gets to a certain size.
• Once you're satisfied of (or properly confused with) the above, if you're still having problems... simply create a new topic in this board, with the status.php file attached uploaded to your server, and some indication of peak times, what your problems are, and what you've done about them so far.

This is currently an incomplete list (although it contains the most important points.)  More detailed information is in the works.

-[Unknown]

Please read this thread before posting about performance issues

Well, I have tried all of the suggested things in the checklist and my forum is still slower than X-Mas.

I use fatcow for my host...all other sites I have on fatcow run fine, including a joomla! database I have on antoher site.

The forum is located here:  http://setformation.com/forum [nofollow]
Here's my server status file:  http://setformation.com/status.php [nofollow]
Phpinfo file is here:  http://setformation.com/phpinfo.php [nofollow]

PHP5. 

The slowness seems to come and go but is slow more often than not.

Please help!!!

Thanks,
Marty

[Rubberman]

Wait a minute!  I just read the Unix host post and changed the load.php script and truncated the sessions table and it seems to be better!

[Rubberman]

Wait a minute!  I just read the Unix host post and changed the load.php script and truncated the sessions table and it seems to be better!

OK, nevermind.  It seemed to work well for a while, but is now slow again.  STILL NEED HELP!

I have posted a more detailed explanation on a seperate topic that is not stickied.

[detoam]

What's the 'Opened vs. Open tables:' mean? The value on my web host is 170.085 (which looks high, considering it says '(should be <= 80)'. Actually, what does all the MySQL stuff mean?

Haha, AMD can't spell. Supposedly, my web host has a "AMD Athlon(tm) Prosussor"

And this is mine:   Opened vs. Open tables: 38789.6719 (should be <= 80)
What does this really mean?

Also what does this mean: Have MySQL's settings been tweaked to optimize memory usage and general efficiency?

[Something like that]

What's the 'Opened vs. Open tables:' mean? The value on my web host is 170.085 (which looks high, considering it says '(should be <= 80)'. Actually, what does all the MySQL stuff mean?

Haha, AMD can't spell. Supposedly, my web host has a "AMD Athlon(tm) Prosussor"

And this is mine:   Opened vs. Open tables: 38789.6719 (should be <= 80)
What does this really mean?

It means MySQL is having to close tables to open others... and then those close those to open yet others. Your host should increase the MySQL table cache.

Also what does this mean: Have MySQL's settings been tweaked to optimize memory usage and general efficiency?

Obviously they haven't here...

[detoam]

Thank You. I have put in a request to my host. hopefully they'll do it.

[Darkness_]

Connections per second:      13.2332
Kilobytes received per second:    0.2051
Kilobytes sent per second:    0.117
Queries per second:    7.0193
Percentage of slow queries:    0.0096
Opened vs. Open tables:
(table_cache)    24296.0247 (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.0035 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.116 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    1.0002 (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.3212 (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)    0.2363 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0.3257 (should be <= 0.05)

What do I have to do, to get the bolded ones to normal stats?
Please help because load times on my forum are very high and it is getting anoying

[青山 素子]

Darkness_, please don't post multiple items in different topics. It makes it harder to provide proper support.

I'm going to post support in your topic, not here.

[skyshroud]

 I am experiencing massive lag, as well, on my forum. The site has only a few users (@40), and though they are prolific posters, there is not enough traffic to cause load times to slow to as much as 2 minutes per page.

 I suspect the simple fact is that the database is about maxed out - do you have any suggestions about shaving the majority off to archive, whilst keeping the current active thread content accessible?

 I am not knowledgeable enough (yet) to determine if any of the internal settings need to be tweaked...but here is a copy of the status.php results, if it helps provide any clues...





March 27, 2008, 09:09:54 AM
Operating System:    Linux 2.4.21-27.ELsmp
Processor:    Intel® Xeon(TM) CPU 3.00GHz (2992.854MHz)
Load averages:    8.48, 9.28, 10.04
Current processes:    464 (454 sleeping, 9 running, 1 zombie)
Processes by CPU:    php (35) 100.4%, search.cgi (1) 62.0%, check_runaways. (1) 2.9%, htcached (1) 1.9%, pure-ftpd (17) 1.7%, httpd (352) 1.0%
Memory usage:    98.632% (6077440k / 6161704k)
Swap: 4.083% (85588k / 2096120k)
MySQL Statistics
MySQL 4.0.x
Connections per second:    4.0778
Kilobytes received per second:    0.0174
Kilobytes sent per second:    0.0743
Queries per second:    118.6098
Percentage of slow queries:    0.0002
Opened vs. Open tables:
(table_cache)    779484.6758 (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.0287 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.2163 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    37.5267 (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.3691 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0.0005 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.3105 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0.6216 (should be <= 0.05)

MySQL status
Aborted_clients:    1555498
Aborted_connects:    120165
Bytes_received:    420429933
Bytes_sent:    1792853324
Com_admin_commands:    35977872
Com_alter_table:    9678
Com_analyze:    14
Com_backup_table:    0
Com_begin:    1287873
Com_change_db:    274021084
Com_change_master:    0
Com_check:    10362
Com_commit:    1277067
Com_create_db:    183
Com_create_function:    0
Com_create_index:    3694
Com_create_table:    460448
Com_delete:    20503308
Com_delete_multi:    61
Com_drop_db:    435
Com_drop_function:    0
Com_drop_index:    0
Com_drop_table:    11059
Com_flush:    41156
Com_grant:    3
Com_ha_close:    0
Com_ha_open:    0
Com_ha_read:    0
Com_insert:    39213703
Com_insert_select:    2567568
Com_kill:    77639
Com_load:    0
Com_load_master_data:    0
Com_load_master_table:    0
Com_lock_tables:    71266
Com_optimize:    91253
Com_purge:    1
Com_rename_table:    11
Com_repair:    10
Com_replace:    1207968
Com_replace_select:    4
Com_reset:    0
Com_restore_table:    0
Com_revoke:    1
Com_rollback:    315
Com_savepoint:    0
Com_select:    682566075
Com_set_option:    467091
Com_show_binlog_events:    0
Com_show_binlogs:    3336
Com_show_create:    27194
Com_show_databases:    2933
Com_show_fields:    3427438
Com_show_grants:    165
Com_show_innodb_status:    0
Com_show_keys:    89812
Com_show_logs:    0
Com_show_master_status:    1
Com_show_new_master:    0
Com_show_open_tables:    0
Com_show_processlist:    1074740
Com_show_slave_hosts:    86
Com_show_slave_status:    546293
Com_show_status:    1009455
Com_show_tables:    4613892
Com_show_variables:    625035
Com_slave_start:    0
Com_slave_stop:    0
Com_truncate:    42681
Com_unlock_tables:    71280
Com_update:    147697576
Com_update_multi:    150106
Connections:    96126669
Created_tmp_disk_tables:    24252393
Created_tmp_files:    2176775
Created_tmp_tables:    65703041
Delayed_errors:    0
Delayed_insert_threads:    0
Delayed_writes:    7510
Flush_commands:    1
Handler_commit:    11437
Handler_delete:    47233347
Handler_read_first:    89245210
Handler_read_key:    2900110721
Handler_read_next:    833463346
Handler_read_prev:    1265409393
Handler_read_rnd:    3667352292
Handler_read_rnd_next:    386253626
Handler_rollback:    4857722
Handler_update:    1988516419
Handler_write:    3445467031
Key_blocks_used:    360922
Key_read_requests:    115373845524
Key_reads:    3308301076
Key_write_requests:    1463865113
Key_writes:    316704783
Max_used_connections:    433
Not_flushed_delayed_rows:    0
Not_flushed_key_blocks:    0
Open_files:    410
Open_streams:    0
Open_tables:    256
Opened_tables:    199548077
Qcache_free_blocks:    1560
Qcache_free_memory:    4603872
Qcache_hits:    1516009835
Qcache_inserts:    642966658
Qcache_lowmem_prunes:    424257839
Qcache_not_cached:    38898332
Qcache_queries_in_cache:    4691
Qcache_total_blocks:    12622
Questions:    2796001679
Rpl_status:    NULL
Select_full_join:    8178607
Select_full_range_join:    137070
Select_range:    32918362
Select_range_check:    7287
Select_scan:    249398988
Slave_open_temp_tables:    0
Slave_running:    OFF
Slow_launch_threads:    22
Slow_queries:    438432
Sort_merge_passes:    962245
Sort_range:    55274520
Sort_rows:    1845255340
Sort_scan:    115176027
Table_locks_immediate:    1540584118
Table_locks_waited:    1676906
Threads_cached:    0
Threads_connected:    42
Threads_created:    2561557
Threads_running:    35
Uptime:    23573104

MySQL variables
back_log:    50
basedir:    /usr/local/mysql-max-4.0.27-unknown-linux-gnu-x86_64-glibc23/
binlog_cache_size:    32768
bulk_insert_buffer_size:    8388608
character_set:    latin1
character_sets:    latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert:    ON
connect_timeout:    20
convert_character_set:    
datadir:    /var/lib/mysql/57/data57/
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_max_word_len:    254
ft_max_word_len_for_sort:    20
ft_min_word_len:    4
ft_stopword_file:    (built-in)
have_bdb:    NO
have_crypt:    YES
have_innodb:    YES
have_isam:    YES
have_openssl:    NO
have_query_cache:    YES
have_raid:    NO
have_symlink:    YES
init_file:    
innodb_additional_mem_pool_size:    10485760
innodb_autoextend_increment:    8
innodb_buffer_pool_size:    134217728
innodb_data_file_path:    ibdata1:10M:autoextend
innodb_data_home_dir:    /var/lib/mysql/57/tables/
innodb_fast_shutdown:    ON
innodb_file_io_threads:    4
innodb_flush_log_at_trx_commit:    1
innodb_flush_method:    O_DSYNC
innodb_force_recovery:    0
innodb_lock_wait_timeout:    50
innodb_log_arch_dir:    /var/lib/mysql/57/logs/
innodb_log_archive:    OFF
innodb_log_buffer_size:    8388608
innodb_log_file_size:    41943040
innodb_log_files_in_group:    2
innodb_log_group_home_dir:    /var/lib/mysql/57/logs/
innodb_max_dirty_pages_pct:    90
innodb_max_purge_lag:    0
innodb_mirrored_log_groups:    1
innodb_table_locks:    ON
innodb_thread_concurrency:    8
interactive_timeout:    30
join_buffer_size:    131072
key_buffer_size:    402653184
language:    /usr/local/mysql-max-4.0.27-unknown-linux-gnu-x86_64-glibc23/share/mysql/english/
large_files_support:    ON
license:    GPL
local_infile:    ON
locked_in_memory:    OFF
log:    OFF
log_bin:    ON
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:    16776192
max_binlog_cache_size:    4294967295
max_binlog_size:    1073741824
max_connect_errors:    10000
max_connections:    1000
max_delayed_threads:    20
max_heap_table_size:    16777216
max_insert_delayed_threads:    20
max_join_size:    18446744073709551615
max_relay_log_size:    0
max_seeks_for_key:    4294967295
max_sort_length:    1024
max_tmp_tables:    32
max_user_connections:    100
max_write_lock_count:    4294967295
myisam_max_extra_sort_file_size:    268435456
myisam_max_sort_file_size:    9223372036854775807
myisam_recover_options:    OFF
myisam_repair_threads:    1
myisam_sort_buffer_size:    67108864
net_buffer_length:    8192
net_read_timeout:    30
net_retry_count:    10
net_write_timeout:    60
new:    OFF
open_files_limit:    5010
pid_file:    /var/lib/mysql/57/data57//hmysql15.prod.mesa1.gdg.pid
port:    3306
protocol_version:    10
query_alloc_block_size:    8192
query_cache_limit:    1048576
query_cache_size:    20971520
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:    262144
rpl_recovery_rank:    0
server_id:    57
skip_external_locking:    ON
skip_networking:    OFF
skip_show_database:    OFF
slave_net_timeout:    3600
slow_launch_time:    2
socket:    /tmp/mysql-57.sock
sort_buffer_size:    1048568
sql_mode:    0
table_cache:    256
table_type:    MYISAM
thread_cache_size:    8
thread_stack:    196608
timezone:    MST
tmp_table_size:    33554432
tmpdir:    /tmp/
transaction_alloc_block_size:    8192
transaction_prealloc_size:    4096
tx_isolation:    READ-COMMITTED
version:    4.0.27-max-log
version_comment:    Official MySQL-max binary
version_compile_os:    unknown-linux-gnu
wait_timeout:    20

[青山 素子]

Are you on shared hosting? If so, the server you are on is very overloaded.

Load averages:    8.48, 9.28, 10.04

Although you can still run things at that high of a load, it will bring slowdowns. Also, this "search.cgi" process seems to be taking up a lot of resources, at least as of the time you looked. Keep an eye on the load and if it stays high, contact the hosting company. If they refuse to listen, you might just need to look for hosting elsewhere.

[skyshroud]

 Hosted at GoDaddy, and I am sure they are jamming their servers with every client site they can pack in. Despite multiple requests to resolve the overload issue over the years, this problems crops up every now and then.
 I know the database is getting really large, which is why I asked about it, but I have very little database mechanical know-how, and thus am not comfortable with taking any steps to reduce it's size without someone holding my hand, essentially.
 With regards to the actual hosting reality, the bottom line is that they want to pop me for about 2.5 times what I pay now for the service... a couple of hundred bucks a year to simply host a 40 member forum.. not realistic, at all.


 So.. I am hoping making a few changes will resolve some of the lag...

[Vinspire]

I need someone to help me take a look at what is wrong with my forum as I've been told by my ISP that my forum has overloaded the server.

I am forced to put the forum under maintenance for 2 days now as once I've set it online, the problem occur. My ISP has given me the warning that if such problem repeat, they will suspend and isolate my account.

Checklist for performance problems

1. I can't find this in my avatar setting -"Check avatar size every time it's displayed"

2. As of my acknowledgment, I do you have eAccelerator or another optimizer installed.

3. Yes, I have a lot of posts and did not convert any of your tables to InnoDB

4. I've tweaked my MySQL by using SMF optimization function in admin CP. Correct me if i am wrong.

Server Status
Basic Information
March 30, 2008, 01:07:13 PM
Operating System:    Linux 2.6.9-55.ELsmp
Processor:    Intel® Pentium® D CPU 2.80GHz (2800.399MHz)
Load averages:    0.35, 0.40, 0.45
Current processes:    242 (241 sleeping, 1 running, 0 zombie)
Processes by CPU:    httpd (57) 12.6%, mysqld (1) 4.0%, (other) (61) 0.6%
Memory usage:    95.891% (1989460k / 2074716k)
Swap: 3.787% (155148k / 4096532k)
MySQL Statistics
MySQL 5.0.x
Connections per second:    2.1156
Kilobytes received per second:    7.497
Kilobytes sent per second:    13.7173
Queries per second:    40.6669
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    18.4102 (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.005 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.4377 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    272.415 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.625 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.3203 (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)    0.3523 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0.4329 (should be <= 0.05)

MySQL status
Aborted_clients:    1261
Aborted_connects:    3259
Binlog_cache_disk_use:    0
Binlog_cache_use:    0
Bytes_received:    250099412
Bytes_sent:    457607614
Com_admin_commands:    3109
Com_alter_db:    0
Com_alter_table:    2
Com_analyze:    0
Com_backup_table:    0
Com_begin:    8
Com_call_procedure:    0
Com_change_db:    69125
Com_change_master:    0
Com_check:    0
Com_checksum:    0
Com_commit:    8
Com_create_db:    1
Com_create_function:    0
Com_create_index:    1
Com_create_table:    1344
Com_create_user:    0
Com_dealloc_sql:    0
Com_delete:    15608
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:    99
Com_grant:    1
Com_ha_close:    0
Com_ha_open:    0
Com_ha_read:    0
Com_help:    0
Com_insert:    174238
Com_insert_select:    920
Com_kill:    0
Com_load:    0
Com_load_master_data:    0
Com_load_master_table:    0
Com_lock_tables:    1286
Com_optimize:    13
Com_preload_keys:    0
Com_prepare_sql:    0
Com_purge:    0
Com_purge_before_date:    0
Com_rename_table:    0
Com_repair:    97
Com_replace:    3646
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:    305306
Com_set_option:    36378
Com_show_binlog_events:    0
Com_show_binlogs:    0
Com_show_charsets:    3
Com_show_collations:    3
Com_show_column_types:    0
Com_show_create_db:    0
Com_show_create_table:    3535
Com_show_databases:    52
Com_show_errors:    0
Com_show_fields:    5894
Com_show_grants:    214
Com_show_innodb_status:    0
Com_show_keys:    662
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:    110
Com_show_slave_hosts:    0
Com_show_slave_status:    0
Com_show_status:    2
Com_show_storage_engines:    0
Com_show_tables:    7055
Com_show_triggers:    3829
Com_show_variables:    61
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:    39
Com_unlock_tables:    1292
Com_update:    62901
Com_update_multi:    153
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:    68921
Created_tmp_disk_tables:    17851
Created_tmp_files:    35
Created_tmp_tables:    55732
Delayed_errors:    0
Delayed_insert_threads:    0
Delayed_writes:    0
Flush_commands:    1
Handler_commit:    1373
Handler_delete:    17158
Handler_discover:    0
Handler_prepare:    0
Handler_read_first:    37313
Handler_read_key:    160602057
Handler_read_next:    171240717
Handler_read_prev:    186168
Handler_read_rnd:    1132674
Handler_read_rnd_next:    329040195
Handler_rollback:    0
Handler_savepoint:    0
Handler_savepoint_rollback:    0
Handler_update:    155544683
Handler_write:    3022845
Innodb_buffer_pool_pages_data:    501
Innodb_buffer_pool_pages_dirty:    0
Innodb_buffer_pool_pages_flushed:    3023
Innodb_buffer_pool_pages_free:    0
Innodb_buffer_pool_pages_latched:    0
Innodb_buffer_pool_pages_misc:    11
Innodb_buffer_pool_pages_total:    512
Innodb_buffer_pool_read_ahead_rnd:    1025
Innodb_buffer_pool_read_ahead_seq:    524
Innodb_buffer_pool_read_requests:    8004162
Innodb_buffer_pool_reads:    18262
Innodb_buffer_pool_wait_free:    0
Innodb_buffer_pool_write_requests:    8805
Innodb_data_fsyncs:    2897
Innodb_data_pending_fsyncs:    0
Innodb_data_pending_reads:    0
Innodb_data_pending_writes:    0
Innodb_data_read:    519950336
Innodb_data_reads:    21239
Innodb_data_writes:    5055
Innodb_data_written:    103262208
Innodb_dblwr_pages_written:    3023
Innodb_dblwr_writes:    548
Innodb_log_waits:    0
Innodb_log_write_requests:    6771
Innodb_log_writes:    1263
Innodb_os_log_fsyncs:    1790
Innodb_os_log_pending_fsyncs:    0
Innodb_os_log_pending_writes:    0
Innodb_os_log_written:    3934720
Innodb_page_size:    16384
Innodb_pages_created:    174
Innodb_pages_read:    31602
Innodb_pages_written:    3023
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:    187
Innodb_rows_read:    4779175
Innodb_rows_updated:    1010
Key_blocks_not_flushed:    0
Key_blocks_unused:    176723
Key_blocks_used:    55238
Key_read_requests:    25820034
Key_reads:    130137
Key_write_requests:    1770053
Key_writes:    774754
Last_query_cost:    0.000000
Max_used_connections:    33
Ndb_cluster_node_id:    0
Ndb_config_from_host:    
Ndb_config_from_port:    0
Ndb_number_of_data_nodes:    0
Not_flushed_delayed_rows:    0
Open_files:    1973
Open_streams:    0
Open_tables:    1024
Opened_tables:    18852
Prepared_stmt_count:    0
Qcache_free_blocks:    1447
Qcache_free_memory:    4172360
Qcache_hits:    561254
Qcache_inserts:    285278
Qcache_lowmem_prunes:    132159
Qcache_not_cached:    24260
Qcache_queries_in_cache:    4276
Qcache_total_blocks:    11425
Questions:    1324847
Rpl_status:    NULL
Select_full_join:    654
Select_full_range_join:    43
Select_range:    28767
Select_range_check:    60
Select_scan:    80918
Slave_open_temp_tables:    0
Slave_retried_transactions:    0
Slave_running:    OFF
Slow_launch_threads:    0
Slow_queries:    25
Sort_merge_passes:    15
Sort_range:    20321
Sort_rows:    2891089
Sort_scan:    47540
Table_locks_immediate:    733402
Table_locks_waited:    721
Tc_log_max_pages_used:    0
Tc_log_page_size:    0
Tc_log_page_waits:    0
Threads_cached:    5
Threads_connected:    3
Threads_created:    253
Threads_running:    1
Uptime:    32578
Uptime_since_flush_status:    32578

MySQL variables
auto_increment_increment:    1
auto_increment_offset:    1
automatic_sp_privileges:    ON
back_log:    50
basedir:    /
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/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:    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
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:    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:    YES
have_csv:    YES
have_dynamic_loading:    YES
have_example_engine:    YES
have_federated_engine:    YES
have_geometry:    YES
have_innodb:    YES
have_isam:    NO
have_merge_engine:    YES
have_ndbcluster:    DISABLED
have_openssl:    NO
have_ssl:    NO
have_query_cache:    YES
have_raid:    NO
have_rtree_keys:    YES
have_symlink:    YES
hostname:    ketam.mschosting.com
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:    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:    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_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:    2093056
key_buffer_size:    268435456
key_cache_age_threshold:    300
key_cache_block_size:    1024
key_cache_division_limit:    100
language:    /usr/share/mysql/english/
large_files_support:    ON
large_page_size:    0
large_pages:    OFF
lc_time_names:    en_US
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:    10484736
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:    16777216
max_insert_delayed_threads:    20
max_join_size:    18446744073709551615
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:    45
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
ndb_autoincrement_prefetch_sz:    32
ndb_force_send:    ON
ndb_use_exact_count:    ON
ndb_use_transactions:    ON
ndb_cache_check_time:    0
ndb_connectstring:    
net_buffer_length:    16384
net_read_timeout:    30
net_retry_count:    512
net_write_timeout:    60
new:    OFF
old_passwords:    ON
open_files_limit:    2558
optimizer_prune_level:    1
optimizer_search_depth:    62
pid_file:    /var/lib/mysql/ketam.mschosting.com.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:    16777216
query_cache_type:    ON
query_cache_wlock_invalidate:    OFF
query_prealloc_size:    8192
range_alloc_block_size:    2048
read_buffer_size:    2093056
read_only:    OFF
read_rnd_buffer_size:    4190208
relay_log_purge:    ON
relay_log_space_limit:    0
rpl_recovery_rank:    0
secure_auth:    OFF
secure_file_priv:    
server_id:    1
skip_external_locking:    ON
skip_networking:    OFF
skip_show_database:    OFF
slave_compressed_protocol:    OFF
slave_load_tmpdir:    /tmp/
slave_net_timeout:    3600
slave_skip_errors:    OFF
slave_transaction_retries:    10
slow_launch_time:    2
socket:    /tmp/mysql.sock
sort_buffer_size:    4194296
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:    MYT
table_cache:    1024
table_lock_wait_timeout:    50
table_type:    MyISAM
thread_cache_size:    8
thread_stack:    196608
time_format:    %H:%i:%s
time_zone:    SYSTEM
timed_mutexes:    OFF
tmp_table_size:    33554432
tmpdir:    /tmp/
transaction_alloc_block_size:    8192
transaction_prealloc_size:    4096
tx_isolation:    REPEATABLE-READ
updatable_views_with_limit:    YES
version:    5.0.45-community
version_comment:    MySQL Community Edition (GPL)
version_compile_machine:    i686
version_compile_os:    pc-linux-gnu
wait_timeout:    100

[skyshroud]

is there a link to a reference guide available explaining the results of these reports?

[metallica48423]

were you guys ever able to resolve this? 

I highly reccommend creating your own topics in this board for these issues.  Thanks