Simple Machines Community Forum

SMF Support => Server Performance and Configuration => Topic started by: [Unknown] on September 19, 2005, 02:58:57 AM

Title: README: Checklist for performance problems
Post by: [Unknown] on September 19, 2005, 02:58:57 AM
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:


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

-[Unknown]

Please read this (http://www.simplemachines.org/community/index.php?topic=59691.0) thread before posting about performance issues
Title: Re: README: Checklist for performance problems
Post by: Joshua Dickerson on September 19, 2005, 06:18:38 AM
There are some other things that are very important. I think performance should be addressed from the lowest level (the kernel) up (SMF):

If you have any other scripts running, try to optimize them or get the developers to optimize them. I see a lot of really bad PHP programming out there.
Title: Re: README: Checklist for performance problems
Post by: Joshua Dickerson on December 13, 2005, 12:00:54 PM
btw, I edited the status.php script to view phpinfo(). To view it ... http://www.YOUR-SITE-GOES-HERE.com/YOUR%20PATH/status.php?php in other words place '?php' after status.php
Title: Re: README: Checklist for performance problems
Post by: Valodim on October 09, 2006, 02:55:42 PM
One more thing:
If your forum has many (1k+) attachments, be sure to disable the attachment dir size limit! the filesize check needs to iterate all files in your attachment dir everytime someone uploads a file, which can take quite a lot of time for big attachment dirs.

[hint]that routine needs some updating, anyways...[/hint]
Title: Re: README: Checklist for performance problems
Post by: Daniel15 on October 27, 2006, 07:47:02 AM
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" :P
Title: Re: README: Checklist for performance problems
Post by: hartiberlin on November 15, 2006, 03:09:26 AM
One more thing:
If your forum has many (1k+) attachments, be sure to disable the attachment dir size limit! the filesize check needs to iterate all files in your attachment dir everytime someone uploads a file, which can take quite a lot of time for big attachment dirs.

Where do I have to change this
disable the attachment dir size limit
setting ?
Is it somewhere in the admin panel ?

Many thanks.
Title: Re: README: Checklist for performance problems
Post by: STS on November 27, 2006, 09:52:00 AM
Where do I have to change this
disable the attachment dir size limit
setting ?
Is it somewhere in the admin panel ?

Many thanks.

I believe this is it.

Admin Panel > Attatchments and Avatars > Max attachment folder space:

Hope that helps.
Title: Re: README: Checklist for performance problems
Post by: Enc0der on November 29, 2006, 12:55:23 AM
but how can we disable it? setting it to zero? or keeping it empty ("") ?

thanks
Title: Re: README: Checklist for performance problems
Post by: shirster on December 14, 2006, 01:17:37 PM
I THINK it'll be fine just leaving it empty. I cannot be 100% sure that it was the cause but after I deleted whatever number that was there my forum is running much much faster.

Again, I'm not 100% sure, but I'm really happy now  :D :D
Title: Re: README: Checklist for performance problems
Post by: tatore on December 24, 2006, 03:00:24 PM
That's my response.
http://www.discuspassion.net/forum/status.php
http://www.discuspassion.net/forum/status.php?php

since I've installed smf I've got always connection problems with database..
If some of you is able to explain if it depends from my forum or mysql or the connection between them, please..I'm here.
Lots of users are not pres4ent anymore for the connection problems..
Title: Re: README: Checklist for performance problems
Post by: tatore on January 01, 2007, 08:11:05 AM
I'd like to know, with your experience, if I have to change server or ask for more band.
I'm on servage.net
Please, let me know, that my forum goes down every 5 minutes.
Title: Re: README: Checklist for performance problems
Post by: Ben_S on January 01, 2007, 10:50:09 AM
To be honest, with the stupidly oversold packages they have, you shouldn't expect it not to go down every five minutes.
Title: Re: README: Checklist for performance problems
Post by: tatore on January 01, 2007, 01:49:38 PM
my goodness...what a bad new.
I've changed database to have smf software as a solid forum, changed server to have something better than the past and after 3 months of changes, studies and I don't know how many other things (I've never seen a php code, but I've arranged in the best way many things), now I have to plan seriusly to change server... :(
Please, suggest me something (host) that will never give to me so many troubles. My users are bored, and I write here to understand what's up and on my forum to giustify the crashes...so tired  :( ::)
Title: Re: README: Checklist for performance problems
Post by: Ben_S on January 01, 2007, 02:43:10 PM
I'd probably look at either charlottezweb or montecarlohosting. I don't endorse either however so if it all goes wrong, don't blame me. Take a look in the Hosts and Hosting (http://www.simplemachines.org/community/index.php?board=4.0) board.
Title: Re: README: Checklist for performance problems
Post by: tatore on January 01, 2007, 03:04:13 PM
No, Ben :-[ I want to thank you for your answers...
It's silly to look for solutions if the problem is far from codes. I would have a confirming, and you gave it. So thank you again ;)
Title: Re: README: Checklist for performance problems
Post by: KGIII on February 02, 2007, 08:53:17 PM
tatore,

I am just following up with some of the older posts and wanted to ask if you were able to move to a different host and to ensure that everything was going smoothly for you now. If it isn't then what sort of difficulties are you still having?
Title: Re: README: Checklist for performance problems
Post by: texasflats on February 04, 2007, 06:15:29 PM
I was having the same issues, lots of "unable to connect to database" error messages. So I set up an account at http://charlottezweb.com/

I've only been there several days but it's been a breathe of fresh air. Jason is always willing to help and my forum is working as it should.
Title: Re: README: Checklist for performance problems
Post by: Rob on February 09, 2007, 02:13:20 PM
Where can I download status.php? It isn't in the attachments anymore. THANKS

Rob
Title: Re: README: Checklist for performance problems
Post by: Ben_S on February 09, 2007, 02:35:55 PM
It's still attached to the first post.
Title: Re: README: Checklist for performance problems
Post by: Rob on February 09, 2007, 03:07:25 PM
It makes a difference if I am logged in. THANKS

Rob
Title: Re: README: Checklist for performance problems
Post by: webvision on February 10, 2007, 05:37:50 AM
thanks for sharing
Title: Re: README: Checklist for performance problems
Post by: faminsk on March 10, 2007, 12:58:39 PM
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

Quote
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
Title: Re: README: Checklist for performance problems
Post by: Daniel15 on March 10, 2007, 07:30:25 PM
Quote
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? (http://www.simplemachines.org/community/index.php?topic=18250.0)) .

Note that it's a good idea to disable those functions ;)
Title: Re: README: Checklist for performance problems
Post by: faminsk on March 12, 2007, 03:14:00 PM
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
Title: Re: README: Checklist for performance problems
Post by: haco on July 30, 2007, 01:05:27 PM
thank you
Title: Try specifying a custom avatar directory.
Post by: Something like that on September 30, 2007, 10:20:43 PM
Try specifying a custom avatar directory.

http://www.simplemachines.org/community/index.php?topic=197938.0
Title: Re: README: Checklist for performance problems
Post by: Rubberman on January 10, 2008, 01:47:15 PM
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? (http://www.simplemachines.org/community/index.php?topic=49995.0)
  • Do you have a lot of posts?  If so, have you converted any of your tables to InnoDB (http://www.simplemachines.org/community/index.php?topic=50217.0)?
  • 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 (http://www.simplemachines.org/community/index.php?topic=59691.0) 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
Here's my server status file:  http://setformation.com/status.php
Phpinfo file is here:  http://setformation.com/phpinfo.php

PHP5. 

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

Please help!!!

Thanks,
Marty
Title: Re: README: Checklist for performance problems
Post by: Rubberman on January 10, 2008, 02:07:59 PM
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!
Title: Re: README: Checklist for performance problems
Post by: Rubberman on January 10, 2008, 03:36:43 PM
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.
Title: Re: README: Checklist for performance problems
Post by: detoam on January 21, 2008, 07:20:20 PM
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" :P

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?
Title: Re: README: Checklist for performance problems
Post by: Something like that on January 22, 2008, 01:25:59 PM
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" :P

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.

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

Obviously they haven't here...
Title: Re: README: Checklist for performance problems
Post by: detoam on January 23, 2008, 07:39:02 AM
Thank You. I have put in a request to my host. hopefully they'll do it.
Title: Re: README: Checklist for performance problems
Post by: Darkness_ on February 13, 2008, 12:26:00 PM
Quote
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
Title: Re: README: Checklist for performance problems
Post by: 青山 素子 on February 14, 2008, 12:01:36 PM
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 (http://www.simplemachines.org/community/index.php?topic=222543).
Title: Forum Lag
Post by: skyshroud on March 27, 2008, 12:11:21 PM
 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
Title: Re: README: Checklist for performance problems
Post by: 青山 素子 on March 27, 2008, 12:32:00 PM
Are you on shared hosting? If so, the server you are on is very overloaded.

Quote
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.
Title: Re: README: Checklist for performance problems
Post by: skyshroud on March 27, 2008, 01:02:17 PM
 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...
Title: Re: README: Checklist for performance problems
Post by: Vinspire on March 30, 2008, 01:10:10 AM
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
Title: Re: README: Checklist for performance problems
Post by: skyshroud on March 30, 2008, 01:43:51 AM
is there a link to a reference guide available explaining the results of these reports?
Title: Re: README: Checklist for performance problems
Post by: metallica48423 on April 11, 2008, 07:55:50 PM
were you guys ever able to resolve this? 

I highly reccommend creating your own topics in this board for these issues.  Thanks :)
Title: Re: README: Checklist for performance problems
Post by: skyshroud on April 12, 2008, 01:54:24 AM
nope - in fact, my site has slowed even further

as if 9-28 seconds per page load were not bad enough, I am now running into 48 seconds to 2 minutes and 28 seconds load times! argh!
Title: Re: README: Checklist for performance problems
Post by: metallica48423 on April 12, 2008, 02:44:22 AM
Ok.  Please start a topic up, and if you could link to a status.php so we can have  look closer i'd appreciate it :)

Thanks!
Title: Re: README: Checklist for performance problems
Post by: City Builder on May 02, 2008, 02:40:16 AM
Our forums are plagued with browser timeouts, and "misconnections" (i.e. where we have to click twice to get a response from the forums), they tell us that our
Handler_read_rnd     26 M     
Handler_read_rnd_next    805 M
are too high and should be around 0.  None of our sites gets clobbered with visitors, couple people here and there at a time, nothing major by any means.

The host initially told us that the problem is likely that all the mysql databases that we have (we have 2 main forums, and 12 subdomain forums) were all running with the same user so told us to add a new user for each one and make them all unique and said if that doesn't bring down those numbers then the code probably needs to be optimized for joins and something else (sorry i already deleted the email from them), so anyway, we (me personally, I can vouch for it) get timeouts on a very frequent basis, probably  a good 2 out of 10 clicks will produce a broser timeout message or a browser "server reset" message.

Any clues?

Below is our status.php results.

Code: [Select]
Simple Machines
Server Status
Basic Information
May 01, 2008, 11:32:49 PM
Operating System: Debian 3.1
Processor: Dual Core AMD Opteron(tm) Processor 175 (2194.535MHz)
Load averages: 3.62, 3.17, 3.15
Current processes: 4 (2 sleeping, 2 running, 0 zombie)
Processes by CPU: (other) (3) 0.0%
Memory usage: 96.012% (3912556k / 4075060k)
Swap: 4.491% (283524k / 6313512k)
MySQL Statistics
MySQL 5.0.x
Connections per second: 1.0612
Kilobytes received per second: 1.0864
Kilobytes sent per second: 2.9161
Queries per second: 51.2976
Percentage of slow queries: 0.1727
Opened vs. Open tables:
(table_cache) 5636.7257 (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.0105 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.1019 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1222.2843 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0.4 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.279 (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: 2468
Aborted_connects: 1360
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 1009725379
Bytes_sent: 2710260254
Com_admin_commands: 1072
Com_alter_db: 1
Com_alter_table: 676
Com_analyze: 1
Com_backup_table: 0
Com_begin: 6502
Com_change_db: 968442
Com_change_master: 0
Com_check: 12906
Com_checksum: 0
Com_commit: 4130
Com_create_db: 9
Com_create_function: 0
Com_create_index: 0
Com_create_table: 1830
Com_dealloc_sql: 0
Com_delete: 137365
Com_delete_multi: 0
Com_do: 0
Com_drop_db: 21
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 1453
Com_drop_user: 0
Com_execute_sql: 0
Com_flush: 127
Com_grant: 170
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_help: 0
Com_insert: 178076
Com_insert_select: 2002
Com_kill: 0
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 5676
Com_optimize: 5682
Com_preload_keys: 0
Com_prepare_sql: 0
Com_purge: 0
Com_purge_before_date: 0
Com_rename_table: 0
Com_repair: 1
Com_replace: 23011
Com_replace_select: 0
Com_reset: 0
Com_restore_table: 0
Com_revoke: 465
Com_revoke_all: 0
Com_rollback: 9
Com_savepoint: 0
Com_select: 39888339
Com_set_option: 217033
Com_show_binlog_events: 0
Com_show_binlogs: 138
Com_show_charsets: 589
Com_show_collations: 589
Com_show_column_types: 0
Com_show_create_db: 0
Com_show_create_table: 123821
Com_show_databases: 592
Com_show_errors: 0
Com_show_fields: 191893
Com_show_grants: 259
Com_show_innodb_status: 0
Com_show_keys: 323
Com_show_logs: 0
Com_show_master_status: 0
Com_show_ndb_status: 0
Com_show_new_master: 0
Com_show_open_tables: 1
Com_show_privileges: 0
Com_show_processlist: 57
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 33668
Com_show_storage_engines: 6
Com_show_tables: 2918585
Com_show_triggers: 123549
Com_show_variables: 7062
Com_show_warnings: 3
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: 6753
Com_unlock_tables: 6396
Com_update: 610817
Com_update_multi: 913
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: 963160
Created_tmp_disk_tables: 1485046
Created_tmp_files: 370
Created_tmp_tables: 5322042
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 0
Flush_commands: 12
Handler_commit: 31346
Handler_delete: 95021
Handler_discover: 0
Handler_prepare: 0
Handler_read_first: 1818871
Handler_read_key: 335442548
Handler_read_next: 2534534365
Handler_read_prev: 73079073
Handler_read_rnd: 66241143
Handler_read_rnd_next: 2197398900
Handler_rollback: 1131
Handler_savepoint: 0
Handler_savepoint_rollback: 0
Handler_update: 95891583
Handler_write: 194000995
Innodb_buffer_pool_pages_data: 255
Innodb_buffer_pool_pages_dirty: 0
Innodb_buffer_pool_pages_flushed: 22052
Innodb_buffer_pool_pages_free: 188
Innodb_buffer_pool_pages_latched: 0
Innodb_buffer_pool_pages_misc: 5
Innodb_buffer_pool_pages_total: 448
Innodb_buffer_pool_read_ahead_rnd: 1895
Innodb_buffer_pool_read_ahead_seq: 408
Innodb_buffer_pool_read_requests: 51084691
Innodb_buffer_pool_reads: 84273
Innodb_buffer_pool_wait_free: 0
Innodb_buffer_pool_write_requests: 140872
Innodb_data_fsyncs: 20833
Innodb_data_pending_fsyncs: 0
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 1620938752
Innodb_data_reads: 90264
Innodb_data_writes: 26839
Innodb_data_written: 748170240
Innodb_dblwr_pages_written: 22052
Innodb_dblwr_writes: 2932
Innodb_log_waits: 0
Innodb_log_write_requests: 46972
Innodb_log_writes: 6570
Innodb_os_log_fsyncs: 8629
Innodb_os_log_pending_fsyncs: 0
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 24518656
Innodb_page_size: 16384
Innodb_pages_created: 1114
Innodb_pages_read: 98801
Innodb_pages_written: 22052
Innodb_row_lock_current_waits: 0
Innodb_row_lock_time: 6463
Innodb_row_lock_time_avg: 646
Innodb_row_lock_time_max: 2353
Innodb_row_lock_waits: 10
Innodb_rows_deleted: 4846
Innodb_rows_inserted: 10740
Innodb_rows_read: 212158080
Innodb_rows_updated: 4197
Key_blocks_not_flushed: 0
Key_blocks_unused: 1801
Key_blocks_used: 6365
Key_read_requests: 713705196
Key_reads: 7473488
Key_write_requests: 6298288
Key_writes: 641840
Last_query_cost: 0.000000
Max_used_connections: 117
Not_flushed_delayed_rows: 0
Open_files: 696
Open_streams: 0
Open_tables: 350
Opened_tables: 1972854
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: 46558883
Rpl_status: NULL
Select_full_join: 51005
Select_full_range_join: 1427
Select_range: 1755309
Select_range_check: 0
Select_scan: 7970845
Slave_open_temp_tables: 0
Slave_retried_transactions: 0
Slave_running: OFF
Slow_launch_threads: 0
Slow_queries: 8038929
Sort_merge_passes: 257
Sort_range: 6490909
Sort_rows: 372713411
Sort_scan: 2854449
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: 49234883
Table_locks_waited: 3707
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 8
Threads_connected: 7
Threads_created: 788
Threads_running: 1
Uptime: 907623

MySQL variables
auto_increment_increment: 1
auto_increment_offset: 1
automatic_sp_privileges: ON
back_log: 50
basedir: /data/mysql/dspain/
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: /data/mysql/dspain/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: /dh/mysql/dspain/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: 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: YES
have_bdb: NO
have_blackhole_engine: NO
have_compress: YES
have_crypt: YES
have_csv: NO
have_dynamic_loading: NO
have_example_engine: NO
have_federated_engine: NO
have_geometry: YES
have_innodb: YES
have_isam: NO
have_ndbcluster: NO
have_merge_engine: YES
have_openssl: DISABLED
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: 7340032
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: ON
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: 2097152
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: 1000
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: 7340032
key_cache_age_threshold: 300
key_cache_block_size: 1024
key_cache_division_limit: 100
language: /data/mysql/dspain/share/mysql/english/
large_files_support: ON
large_page_size: 0
large_pages: OFF
license: GPL
local_infile: ON
locked_in_memory: OFF
log: ON
log_bin: OFF
log_bin_trust_function_creators: OFF
log_error: /dh/mysql/logs/mysql.dspain.err
log_queries_not_using_indexes: ON
log_slave_updates: OFF
log_slow_queries: ON
log_warnings: 2
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: 1200
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: 300
max_write_lock_count: 4294967295
multi_range_count: 256
myisam_data_pointer_size: 6
myisam_max_sort_file_size: 2147483647
myisam_recover_options: BACKUP,FORCE
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: ON
old_passwords: ON
open_files_limit: 65535
optimizer_prune_level: 1
optimizer_search_depth: 62
pid_file: /tmp/mysql.dspain.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: 1044480
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: 1506
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.dspain.sock
sort_buffer_size: 1048568
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: PDT
table_cache: 350
table_lock_wait_timeout: 50
table_type: MyISAM
thread_cache_size: 20
thread_stack: 126976
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.24a-standard-log
version_comment: MySQL Community Edition - Standard (GPL)
version_compile_machine: i686
version_compile_os: pc-linux-gnu
wait_timeout: 14400
Title: Re: README: Checklist for performance problems
Post by: metallica48423 on May 02, 2008, 05:55:01 PM
start a new topic please.
Title: Re: README: Checklist for performance problems
Post by: City Builder on May 02, 2008, 06:40:35 PM
start a new topic please.
I did, over here: http://www.simplemachines.org/community/index.php?topic=236027.0 Was told to look at this thread.
Title: Re: README: Checklist for performance problems
Post by: metallica48423 on May 02, 2008, 08:16:37 PM
ahh, i see it now (thats what i get for being tired.

Did you run through the checklist and post the results in your topic?
Title: Re: README: Checklist for performance problems
Post by: evanora on October 04, 2008, 09:04:07 PM
Hi, I am sorry to have to bother you guys but could somebody have a look at my status.php file and tell me if there's something I'm not doing right.

You see, my host is HostMonster.com. For some time now, I've been getting a "CPU quota exceeded" error message whenever there are about 5 users online. This is quite considering that my forum is still small (less than 50 members). What would then happen when it has grown?

I want to be sure I'm not doing anything on my part that is abnormal before taking it up with them. I run SMF 1.1.6 with TinyPortal installed. The staus.php is at http://www.nanajeria.com/status.php (http://www.nanajeria.com/status.php). 
Title: Re: README: Checklist for performance problems
Post by: 青山 素子 on October 04, 2008, 11:09:01 PM
The load seems kind of high. Is it usually above 4?
Title: Re: README: Checklist for performance problems
Post by: casty23 on November 23, 2008, 06:56:13 PM
How are you guys getting the status.php file to show?   I try to click on mine but get a 404 error.
Title: Re: README: Checklist for performance problems
Post by: vulcan59 on November 28, 2008, 04:40:53 PM
I've got similar problems to many here. Below is the output of status.php. There were running the server in safe.mode and I was getting all sorts of problems. Now the hosting agent says it's been disable but I'm not sure. Can anyone tell by looking at the status.php output?

Code: [Select]
Server Status
Basic Information
November 29, 2008, 10:26:44 AM
Operating System: Debian 3.1
Processor: Intel® Pentium® D CPU 3.40GHz (3391.628MHz)
Load averages: 1.12, 1.18, 1.13
Current processes: 257 (254 sleeping, 3 running, 0 zombie)
Processes by CPU: httpd (36) 33.3%, (other) (56) 0.1%
Memory usage: 95.55% (2975248k / 3113808k)
Swap: 0.01% (96k / 979832k)
MySQL Statistics
MySQL 5.0.x
Connections per second: 6.3195
Kilobytes received per second: 36.5885
Kilobytes sent per second: 352.5397
Queries per second: 203.1225
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 2520.6801 (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.0083 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.036 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 298149.99 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0.487 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.4236 (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.1974 (should be <= 0.5)
Query cache prune rate:
(query_cache_size) 0.2396 (should be <= 0.05)

MySQL status
Aborted_clients: 451400
Aborted_connects: 245716
Binlog_cache_disk_use: 12019
Binlog_cache_use: 13291150
Bytes_received: 355299272531
Bytes_sent: 3423396280203
Com_admin_commands: 3613493
Com_alter_db: 129
Com_alter_table: 35649
Com_analyze: 2164
Com_backup_table: 0
Com_begin: 855250
Com_call_procedure: 0
Com_change_db: 110633133
Com_change_master: 0
Com_check: 14254
Com_checksum: 0
Com_commit: 726107
Com_create_db: 2048
Com_create_function: 0
Com_create_index: 160389
Com_create_table: 499879
Com_create_user: 0
Com_dealloc_sql: 324925
Com_delete: 15960284
Com_delete_multi: 3712
Com_do: 0
Com_drop_db: 290
Com_drop_function: 0
Com_drop_index: 15
Com_drop_table: 197448
Com_drop_user: 0
Com_execute_sql: 1369164
Com_flush: 18
Com_grant: 8610
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_help: 0
Com_insert: 100237803
Com_insert_select: 153367
Com_kill: 17
Com_load: 270
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 816584
Com_optimize: 16719
Com_preload_keys: 0
Com_prepare_sql: 507254
Com_purge: 4
Com_purge_before_date: 0
Com_rename_table: 19178
Com_repair: 931
Com_replace: 3269038
Com_replace_select: 21
Com_reset: 0
Com_restore_table: 0
Com_revoke: 5753
Com_revoke_all: 0
Com_rollback: 5080
Com_savepoint: 0
Com_select: 282870012
Com_set_option: 52233470
Com_show_binlog_events: 0
Com_show_binlogs: 7579
Com_show_charsets: 125249
Com_show_collations: 144718
Com_show_column_types: 0
Com_show_create_db: 0
Com_show_create_table: 8692355
Com_show_databases: 126189
Com_show_errors: 0
Com_show_fields: 12248992
Com_show_grants: 52619
Com_show_innodb_status: 629
Com_show_keys: 233601
Com_show_logs: 0
Com_show_master_status: 12
Com_show_ndb_status: 0
Com_show_new_master: 0
Com_show_open_tables: 3
Com_show_privileges: 0
Com_show_processlist: 10708
Com_show_slave_hosts: 145
Com_show_slave_status: 1
Com_show_status: 43949
Com_show_storage_engines: 1865
Com_show_tables: 17450589
Com_show_triggers: 8544736
Com_show_variables: 1477000
Com_show_warnings: 9202
Com_slave_start: 0
Com_slave_stop: 0
Com_stmt_close: 1618552
Com_stmt_execute: 2662795
Com_stmt_fetch: 0
Com_stmt_prepare: 1800885
Com_stmt_reset: 1226123
Com_stmt_send_long_data: 0
Com_truncate: 96818
Com_unlock_tables: 864830
Com_update: 76324904
Com_update_multi: 568981
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: 59928148
Created_tmp_disk_tables: 33870425
Created_tmp_files: 177452
Created_tmp_tables: 79956078
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 1111
Flush_commands: 1
Handler_commit: 29382918
Handler_delete: 619133738
Handler_discover: 0
Handler_prepare: 26577010
Handler_read_first: 246369547
Handler_read_key: 18375384346
Handler_read_next: 63364211709
Handler_read_prev: 111620331
Handler_read_rnd: 778812994
Handler_read_rnd_next: 1462864824588
Handler_rollback: 96896
Handler_savepoint: 0
Handler_savepoint_rollback: 0
Handler_update: 236649613
Handler_write: 21922356480
Innodb_buffer_pool_pages_data: 495
Innodb_buffer_pool_pages_dirty: 0
Innodb_buffer_pool_pages_flushed: 21067554
Innodb_buffer_pool_pages_free: 0
Innodb_buffer_pool_pages_latched: 0
Innodb_buffer_pool_pages_misc: 17
Innodb_buffer_pool_pages_total: 512
Innodb_buffer_pool_read_ahead_rnd: 2763820
Innodb_buffer_pool_read_ahead_seq: 1129866
Innodb_buffer_pool_read_requests: 26749181533
Innodb_buffer_pool_reads: 124332376
Innodb_buffer_pool_wait_free: 0
Innodb_buffer_pool_write_requests: 348036506
Innodb_data_fsyncs: 26651755
Innodb_data_pending_fsyncs: 0
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 2723431469056
Innodb_data_reads: 135139865
Innodb_data_writes: 43647996
Innodb_data_written: 721911041536
Innodb_dblwr_pages_written: 21067554
Innodb_dblwr_writes: 1318928
Innodb_log_waits: 21
Innodb_log_write_requests: 41730038
Innodb_log_writes: 23181252
Innodb_os_log_fsyncs: 24020805
Innodb_os_log_pending_fsyncs: 0
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 31136538624
Innodb_page_size: 16384
Innodb_pages_created: 784582
Innodb_pages_read: 166224804
Innodb_pages_written: 21067554
Innodb_row_lock_current_waits: 0
Innodb_row_lock_time: 9119670
Innodb_row_lock_time_avg: 1301
Innodb_row_lock_time_max: 51957
Innodb_row_lock_waits: 7006
Innodb_rows_deleted: 7317372
Innodb_rows_inserted: 41358471
Innodb_rows_read: 78896666410
Innodb_rows_updated: 16374090
Key_blocks_not_flushed: 0
Key_blocks_unused: 0
Key_blocks_used: 6698
Key_read_requests: 54515834871
Key_reads: 450901346
Key_write_requests: 3167863379
Key_writes: 114181947
Last_query_cost: 0.000000
Max_used_connections: 201
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: 16981
Open_streams: 0
Open_tables: 9216
Opened_tables: 23230588
Prepared_stmt_count: 0
Qcache_free_blocks: 42429
Qcache_free_memory: 137750256
Qcache_hits: 1150187258
Qcache_inserts: 211076279
Qcache_lowmem_prunes: 67764011
Qcache_not_cached: 86315841
Qcache_queries_in_cache: 148090
Qcache_total_blocks: 357945
Questions: 1926226068
Rpl_status: NULL
Select_full_join: 3708653
Select_full_range_join: 56549
Select_range: 15614010
Select_range_check: 38676
Select_scan: 132351230
Slave_open_temp_tables: 0
Slave_retried_transactions: 0
Slave_running: OFF
Slow_launch_threads: 0
Slow_queries: 22139
Sort_merge_passes: 85503
Sort_range: 19804297
Sort_rows: 2660764618
Sort_scan: 56473702
Table_locks_immediate: 565734450
Table_locks_waited: 619678
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 187
Threads_connected: 14
Threads_created: 201
Threads_running: 2
Uptime: 9483076
Uptime_since_flush_status: 9483076

MySQL variables
auto_increment_increment: 1
auto_increment_offset: 1
automatic_sp_privileges: ON
back_log: 50
basedir: /usr/local/mysql-5.0.51a-linux-x86_64-icc-glibc23/
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-5.0.51a-linux-x86_64-icc-glibc23/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: /usr/local/mysql-5.0.51a-linux-x86_64-icc-glibc23/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
keep_files_on_create: OFF
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: 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: db1
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: 131072
key_buffer_size: 8388600
key_cache_age_threshold: 300
key_cache_block_size: 1024
key_cache_division_limit: 100
language: /usr/local/mysql-5.0.51a-linux-x86_64-icc-glibc23/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: ON
log_bin_trust_function_creators: OFF
log_error:
log_queries_not_using_indexes: OFF
log_slave_updates: OFF
log_slow_queries: ON
log_warnings: 1
long_query_time: 3
low_priority_updates: OFF
lower_case_file_system: OFF
lower_case_table_names: 0
max_allowed_packet: 16776192
max_binlog_cache_size: 18446744073709551615
max_binlog_size: 1073741824
max_connect_errors: 10
max_connections: 200
max_delayed_threads: 20
max_error_count: 64
max_heap_table_size: 16777216
max_insert_delayed_threads: 20
max_join_size: 1000000000
max_length_for_sort_data: 1024
max_prepared_stmt_count: 16382
max_relay_log_size: 0
max_seeks_for_key: 18446744073709551615
max_sort_length: 1024
max_sp_recursion_depth: 0
max_tmp_tables: 32
max_user_connections: 8
max_write_lock_count: 18446744073709551615
multi_range_count: 256
myisam_data_pointer_size: 6
myisam_max_sort_file_size: 9223372036854775807
myisam_recover_options: OFF
myisam_repair_threads: 1
myisam_sort_buffer_size: 33554432
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: 10
net_write_timeout: 60
new: OFF
old_passwords: OFF
open_files_limit: 18642
optimizer_prune_level: 1
optimizer_search_depth: 62
pid_file: /usr/local/mysql-5.0.51a-linux-x86_64-icc-glibc23/data/db1.pid
port: 3306
preload_buffer_size: 32768
profiling: OFF
profiling_history_size: 15
protocol_version: 10
query_alloc_block_size: 8192
query_cache_limit: 8388608
query_cache_min_res_unit: 4096
query_cache_size: 536870912
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: 4190208
relay_log_purge: ON
relay_log_space_limit: 0
rpl_recovery_rank: 0
secure_auth: OFF
secure_file_priv:
server_id: 5
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: 2097144
sql_big_selects: OFF
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: NZST
table_cache: 9216
table_lock_wait_timeout: 50
table_type: MyISAM
thread_cache_size: 384
thread_stack: 262144
time_format: %H:%i:%s
time_zone: SYSTEM
timed_mutexes: OFF
tmp_table_size: 67108864
tmpdir: /tmp/
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: REPEATABLE-READ
updatable_views_with_limit: YES
version: 5.0.51a-log
version_comment: MySQL Community Server (GPL)
version_compile_machine: x86_64
version_compile_os: redhat-linux-gnu
wait_timeout: 200

Title: Re: README: Checklist for performance problems
Post by: villasg on December 07, 2008, 11:47:58 AM
Hello can help me ?

MySQL Statistics
MySQL 5.0.x
Code: [Select]
Connections per second: 1.6509
Kilobytes received per second: 6.5836
Kilobytes sent per second: 0.3797
Queries per second: 45.8007
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 54633.9844 (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.0361 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.2215 (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.2927 (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)


Have this abnormal .
How can i fix ?
Opened vs. Open tables:
(table_cache)    54632.0625 (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.0361 (should be <= 0.01)
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)
Query cache miss rate:
(query_cache_limit)    1 (should be <= 0.5)
Title: Re: README: Checklist for performance problems
Post by: metallica48423 on December 07, 2008, 12:02:41 PM
All -- please start new topics in this board for your support issue -- thanks!

This topic is purely informational
Title: Re: README: Checklist for performance problems
Post by: fractalbit on February 17, 2009, 08:05:33 AM
Hello all. Status.php is great tool. Thanks to this we spotted a query that would bring the whole server down in a matter of seconds! Every time we executed this query through phpmyadmin it would bring the server down. But we spotted this query by luck, because on of the admins saw the query in status.php that it had locked out all other queries.

So my question is: Can status.php be modified so that it logs (on a table or txt file) the queries that take more than X seconds to execute, or the queries that lock others out for more than X seconds? So we will not rely on chance anymore and easily find the problematic queries? Of course as i undestand it, status.php should be made as a cron job to run every second or so? Whould this have a large impact on server performance?

Thanks for your time
Title: Re: README: Checklist for performance problems
Post by: Something like that on February 17, 2009, 08:16:57 AM
MySQL has this feature built in. See: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

I'd recommend using this tool to analyse the slow query log: http://www.mysqlperformanceblog.com/files/utils/mysql_slow_log_parser (save it, run "chmod a+x" to make it executable).

If you're running into queries that are blocking other queries, it's one of two things:
1. You're running an ALTER TABLE (such as in phpMyAdmin).
2. You're running MyISAM and you're suffering from write-locks (convert the table to InnoDB).
Title: Re: README: Checklist for performance problems
Post by: SergeantAsh on March 03, 2009, 06:10:44 PM
I just fired up your status.php script, and I'm getting this...

Opened vs. Open tables:
(table_cache)  34138.78 (should be <= 80)

In figures, this looks bad!! Is it? I'm on a Dreamhost PS Server.
Title: Re: README: Checklist for performance problems
Post by: Something like that on March 03, 2009, 09:34:21 PM
I just fired up your status.php script, and I'm getting this...

Opened vs. Open tables:
(table_cache)  34138.78 (should be <= 80)

In figures, this looks bad!! Is it? I'm on a Dreamhost PS Server.

Dreamhost doesn't tune their MySQL servers.

Yes, the table cache size should be increased. No, it won't make a huge difference, but a small one.
Title: Re: README: Checklist for performance problems
Post by: timi on May 12, 2009, 09:45:07 AM
Thank you
Title: Re: README: Checklist for performance problems
Post by: Green Bird on May 19, 2009, 11:39:32 PM
Thank to all
Title: Re: README: Checklist for performance problems
Post by: lestatminiyo on December 04, 2009, 03:51:58 AM
I have error 500 too, and this is my status.php

any idea?

Memory usage:    97.492% (4043356k / 4147392k)
(table_cache)    103949.8615 (should be <= 80)

Code: [Select]
Basic Information
December 04, 2009, 09:29:31 AM
Operating System: Debian 4.0
Processor: Intel® Xeon® CPU 5110 @ 1.60GHz (1600.062MHz)
Load averages: 2.35, 2.21, 2.20
Current processes: 3 (2 sleeping, 1 running, 0 zombie)
Processes by CPU: (other) (3) 0.0%
Memory usage: 97.492% (4043356k / 4147392k)
Swap: 39.935% (105852k / 265064k)
MySQL Statistics
MySQL 5.0.x
Connections per second: 7.3387
Kilobytes received per second: 21.6702
Kilobytes sent per second: 7.0703
Queries per second: 270.567
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 103949.8615 (should be <= 80)
Table cache usage:
(table_cache) 1.0156 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size) 0.0248 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.0502 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1038.4064 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0.6 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.5796 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer) 0.0001 (should be <= 0.001)
Query cache enabled:
(query_cache_type) 1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit) 0.4211 (should be <= 0.5)
Query cache prune rate:
(query_cache_size) 0.4984 (should be <= 0.05)

MySQL status
Aborted_clients: 1748
Aborted_connects: 1399
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 4041029253
Bytes_sent: 1318452551
Com_admin_commands: 532468
Com_alter_db: 3
Com_alter_table: 89
Com_analyze: 18
Com_backup_table: 0
Com_begin: 13624
Com_call_procedure: 0
Com_change_db: 2087913
Com_change_master: 0
Com_check: 200297
Com_checksum: 0
Com_commit: 11728
Com_create_db: 0
Com_create_function: 0
Com_create_index: 0
Com_create_table: 55769
Com_create_user: 0
Com_dealloc_sql: 0
Com_delete: 443298
Com_delete_multi: 4
Com_do: 0
Com_drop_db: 2
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 29930
Com_drop_user: 0
Com_execute_sql: 0
Com_flush: 206
Com_grant: 2
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_help: 0
Com_insert: 938500
Com_insert_select: 2119
Com_kill: 3
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 3361
Com_optimize: 201867
Com_preload_keys: 0
Com_prepare_sql: 0
Com_purge: 0
Com_purge_before_date: 0
Com_rename_table: 0
Com_repair: 2
Com_replace: 12690
Com_replace_select: 0
Com_reset: 0
Com_restore_table: 0
Com_revoke: 2
Com_revoke_all: 0
Com_rollback: 10
Com_savepoint: 0
Com_select: 16366115
Com_set_option: 2291610
Com_show_binlog_events: 0
Com_show_binlogs: 0
Com_show_charsets: 1811
Com_show_collations: 1811
Com_show_column_types: 0
Com_show_create_db: 49
Com_show_create_table: 205168
Com_show_databases: 210
Com_show_errors: 0
Com_show_fields: 268059
Com_show_grants: 105
Com_show_innodb_status: 0
Com_show_keys: 1143
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: 3039
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 5080
Com_show_storage_engines: 52
Com_show_tables: 143451
Com_show_triggers: 201181
Com_show_variables: 4587
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: 55
Com_unlock_tables: 7760
Com_update: 1618249
Com_update_multi: 9
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: 1336429
Created_tmp_disk_tables: 1253821
Created_tmp_files: 14141
Created_tmp_tables: 2163284
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 794
Flush_commands: 1
Handler_commit: 135696
Handler_delete: 395533
Handler_discover: 0
Handler_prepare: 0
Handler_read_first: 2509141
Handler_read_key: 1728013604
Handler_read_next: 1114880584
Handler_read_prev: 22938643
Handler_read_rnd: 91624882
Handler_read_rnd_next: 3135185954
Handler_rollback: 5120
Handler_savepoint: 0
Handler_savepoint_rollback: 0
Handler_update: 121246180
Handler_write: 351185212
Innodb_buffer_pool_pages_data: 8098
Innodb_buffer_pool_pages_dirty: 3
Innodb_buffer_pool_pages_flushed: 267961
Innodb_buffer_pool_pages_free: 0
Innodb_buffer_pool_pages_misc: 94
Innodb_buffer_pool_pages_total: 8192
Innodb_buffer_pool_read_ahead_rnd: 1824
Innodb_buffer_pool_read_ahead_seq: 578
Innodb_buffer_pool_read_requests: 2995258239
Innodb_buffer_pool_reads: 343788
Innodb_buffer_pool_wait_free: 0
Innodb_buffer_pool_write_requests: 15457447
Innodb_data_fsyncs: 430807
Innodb_data_pending_fsyncs: 0
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 2901938176
Innodb_data_reads: 364628
Innodb_data_writes: 472764
Innodb_data_written: 1247282688
Innodb_dblwr_pages_written: 267961
Innodb_dblwr_writes: 19939
Innodb_log_waits: 0
Innodb_log_write_requests: 2120360
Innodb_log_writes: 221982
Innodb_os_log_fsyncs: 233964
Innodb_os_log_pending_fsyncs: 0
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 1050046464
Innodb_page_size: 16384
Innodb_pages_created: 146021
Innodb_pages_read: 439131
Innodb_pages_written: 267961
Innodb_row_lock_current_waits: 0
Innodb_row_lock_time: 3529
Innodb_row_lock_time_avg: 220
Innodb_row_lock_time_max: 1108
Innodb_row_lock_waits: 16
Innodb_rows_deleted: 21538
Innodb_rows_inserted: 2522485
Innodb_rows_read: 1613770364
Innodb_rows_updated: 68180
Key_blocks_not_flushed: 0
Key_blocks_unused: 57752
Key_blocks_used: 57990
Key_read_requests: 1285666462
Key_reads: 31901511
Key_write_requests: 62993354
Key_writes: 3159176
Last_query_cost: 0.000000
Max_used_connections: 57
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: 116
Open_streams: 0
Open_tables: 65
Opened_tables: 6756741
Prepared_stmt_count: 0
Qcache_free_blocks: 1951
Qcache_free_memory: 6588792
Qcache_hits: 22503212
Qcache_inserts: 14615796
Qcache_lowmem_prunes: 8157446
Qcache_not_cached: 2571365
Qcache_queries_in_cache: 9026
Qcache_total_blocks: 22868
Queries: 49272409
Questions: 49272409
Rpl_status: NULL
Select_full_join: 363774
Select_full_range_join: 4577
Select_range: 964965
Select_range_check: 6280
Select_scan: 4597267
Slave_open_temp_tables: 0
Slave_retried_transactions: 0
Slave_running: OFF
Slow_launch_threads: 0
Slow_queries: 594
Sort_merge_passes: 7429
Sort_range: 1314092
Sort_rows: 124325679
Sort_scan: 2125707
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: 26539661
Table_locks_waited: 2641
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 12
Threads_connected: 8
Threads_created: 1287
Threads_running: 3
Uptime: 182108
Uptime_since_flush_status: 49272409

MySQL variables
auto_increment_increment: 1
auto_increment_offset: 1
automatic_sp_privileges: ON
back_log: 50
basedir: /usr/local/mysql-5.0.81-linux-i686-glibc23/
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-5.0.81-linux-i686-glibc23/share/mysql/charsets/
collation_connection: latin1_swedish_ci
collation_database: latin1_spanish_ci
collation_server: latin1_spanish_ci
completion_type: 0
concurrent_insert: 1
connect_timeout: 10
datadir: /db/mysql/
date_format: %Y-%m-%d
datetime_format: %Y-%m-%d %H:%i:%s
default_week_format: 3
delay_key_write: ON
delayed_insert_limit: 100
delayed_insert_timeout: 300
delayed_queue_size: 1000
div_precision_increment: 4
keep_files_on_create: OFF
engine_condition_pushdown: OFF
expire_logs_days: 0
flush: OFF
flush_time: 300
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: NO
have_federated_engine: YES
have_geometry: YES
have_innodb: YES
have_isam: NO
have_merge_engine: YES
have_ndbcluster: DISABLED
have_openssl: DISABLED
have_ssl: DISABLED
have_query_cache: YES
have_raid: NO
have_rtree_keys: YES
have_symlink: YES
hostname: rdb-es13
init_connect:
init_file:
init_slave:
innodb_additional_mem_pool_size: 20971520
innodb_autoextend_increment: 8
innodb_buffer_pool_awe_mem_mb: 0
innodb_buffer_pool_size: 134217728
innodb_checksums: ON
innodb_commit_concurrency: 0
innodb_concurrency_tickets: 500
innodb_data_file_path: /db/mysql/ibdata1:10M:autoextend
innodb_data_home_dir:
innodb_adaptive_hash_index: ON
innodb_doublewrite: ON
innodb_fast_shutdown: 1
innodb_file_io_threads: 4
innodb_file_per_table: ON
innodb_flush_log_at_trx_commit: 1
innodb_flush_method:
innodb_force_recovery: 0
innodb_lock_wait_timeout: 60
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: 3600
join_buffer_size: 131072
key_buffer_size: 67108864
key_cache_age_threshold: 300
key_cache_block_size: 1024
key_cache_division_limit: 100
language: /usr/local/mysql-5.0.81-linux-i686-glibc23/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: ON
log_bin: OFF
log_bin_trust_function_creators: OFF
log_error: /var/log/mysql.err
log_queries_not_using_indexes: OFF
log_slave_updates: OFF
log_slow_queries: ON
log_warnings: 3
long_query_time: 3
low_priority_updates: OFF
lower_case_file_system: OFF
lower_case_table_names: 0
max_allowed_packet: 10485760
max_binlog_cache_size: 4294963200
max_binlog_size: 1073741824
max_connect_errors: 1000
max_connections: 240
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: 18
max_write_lock_count: 4294967295
multi_range_count: 256
myisam_data_pointer_size: 6
myisam_max_sort_file_size: 2146435072
myisam_recover_options: BACKUP,FORCE
myisam_repair_threads: 1
myisam_sort_buffer_size: 8388608
myisam_stats_method: nulls_unequal
ndb_autoincrement_prefetch_sz: 1
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: 10
net_write_timeout: 60
new: OFF
old_passwords: ON
open_files_limit: 32768
optimizer_prune_level: 1
optimizer_search_depth: 62
pid_file: /var/run/mysqld/mysqld.pid
plugin_dir:
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: 33554432
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 4096
read_buffer_size: 131072
read_only: OFF
read_rnd_buffer_size: 262144
relay_log:
relay_log_index:
relay_log_info_file: relay-log.info
relay_log_purge: ON
relay_log_space_limit: 0
rpl_recovery_rank: 0
secure_auth: OFF
secure_file_priv:
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: /var/run/mysqld/mysqld.sock
sort_buffer_size: 524288
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: CET
table_cache: 64
table_lock_wait_timeout: 50
table_type: MyISAM
thread_cache_size: 20
thread_stack: 196608
time_format: %H:%i:%s
time_zone: SYSTEM
timed_mutexes: OFF
tmp_table_size: 33554432
tmpdir: /var/tmp
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: REPEATABLE-READ
updatable_views_with_limit: YES
version: 5.0.81-log
version_comment: MySQL Community Server (GPL)
version_compile_machine: i686
version_compile_os: pc-linux-gnu
wait_timeout: 60
Title: Re: README: Checklist for performance problems
Post by: Something like that on December 04, 2009, 10:02:41 AM
Memory usage:    97.492% (4043356k / 4147392k)

Don't worry about it. Your memory is being used to cache files so they don't need to be read off the disk, etc.

Quote
(table_cache)    103949.8615 (should be <= 80)

Increase table_cache_size in your MySQL configuration file (usually located at /etc/mysql/my.cnf) and restart MySQL.

It's currently set to 64. I'd set it to 256.
Title: Re: README: Checklist for performance problems
Post by: poseala on October 14, 2010, 12:08:58 AM
I'd like to know, with your experience, if I accept to change server or ask for added band.
goo.gl/Phh6
Please, let me know, that my appointment goes bottomward every 5 minutes.
Title: Re: README: Checklist for performance problems
Post by: larryhyman on February 21, 2011, 07:11:19 PM
What does this mean: (and how do I fix)

windows 2003 enterprise server II6, PHP with Fast CGI, MySql & PHP installed by Webplatform installer, dsl home server, smf rc4 w/ security patch, 50+ mods installed

These are the errors:

error1

Warning: shell_exec() [function.shell-exec]: Unable to execute 'ver' in D:\websites\hsn\smforums\status.php on line 466

Basic Information
February 21, 2011, 07:03:46 PM
Operating System:    Microsoft Windows
MySQL Statistics
MySQL 5.1.x
Connections per second:    0.0358
Kilobytes received per second:    0.2502
Kilobytes sent per second:    1.3527
Queries per second:    0.7741
Percentage of slow queries:    0

error 2

Opened vs. Open tables:
(table_cache)    134.4783 (should be <= 80)

error 3

Key buffer read hit rate:
(key_buffer_size)    0.0123 (should be <= 0.01)
Title: Re: README: Checklist for performance problems
Post by: Digharatta on April 05, 2011, 07:30:51 AM
I would like to recommend the handy tool to check the server and forum for vulnerabilities:

Acunetix Web Vulnerabilty Scanner (Free Edition) http://www.acunetix.com/cross-site-scripting/scanner.htm
 (http://www.acunetix.com/cross-site-scripting/scanner.htm)
Title: Re: README: Checklist for performance problems
Post by: w3bm4st3r on April 11, 2011, 11:03:23 AM
  • Use Apache 2 and optimize (http://www.apachefreaks.com/apache2/misc/perf-tuning.html) it. Still doing my own research on different MPMs so I can't really suggest which one would be best for each situation. Although, they are different ones for different server usage. Do some research

Your apache optimize link is down.. :-\
Title: Re: README: Checklist for performance problems
Post by: Arantor on April 11, 2011, 11:03:54 AM
  • Use Apache 2 and optimize (http://www.apachefreaks.com/apache2/misc/perf-tuning.html) it. Still doing my own research on different MPMs so I can't really suggest which one would be best for each situation. Although, they are different ones for different server usage. Do some research

Your apache optimize link is down.. :-\

Yes... 5 year old links are like that.
Title: Re: README: Checklist for performance problems
Post by: Joshua Dickerson on April 12, 2011, 09:57:29 PM
If you're looking to get the most from your web server, just go with Nginx.
Title: Re: README: Checklist for performance problems
Post by: Xhoi on November 20, 2012, 10:15:44 AM
i had this problem, change what you said but now i se that is is the hosting not fast hehhe
Title: Re: README: Checklist for performance problems
Post by: real_ugee on July 09, 2015, 05:08:29 AM
Please I'll like to know, what is the difference between InnoDB and MyISAM
Title: Re: README: Checklist for performance problems
Post by: ziycon on July 09, 2015, 05:14:27 AM
Please I'll like to know, what is the difference between InnoDB and MyISAM
The main noticeable difference is that InnoDB supports referential integrity (foreign keys) and transactions among other things where as MyISAM doesn't.
Title: Re: README: Checklist for performance problems
Post by: real_ugee on July 10, 2015, 11:45:26 AM
Please I'll like to know, what is the difference between InnoDB and MyISAM
The main noticeable difference is that InnoDB supports referential integrity (foreign keys) and transactions among other things where as MyISAM doesn't.
Thank you very much for the reply.
Title: Re: README: Checklist for performance problems
Post by: hitsme on January 10, 2019, 05:32:13 PM
  • Use Apache 2 and optimize (http://www.apachefreaks.com/apache2/misc/perf-tuning.html) it. Still doing my own research on different MPMs so I can't really suggest which one would be best for each situation. Although, they are different ones for different server usage. Do some research
Oops! That page can’t be found.