Advertisement:

Author Topic: README: Checklist for performance problems  (Read 221590 times)

Offline skyshroud

  • Semi-Newbie
  • *
  • Posts: 56
  • Gender: Male
Re: README: Checklist for performance problems
« Reply #40 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!

Offline metallica48423

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,842
  • Gender: Male
  • Professional Multislacker!
    • Zentendo
Re: README: Checklist for performance problems
« Reply #41 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!
Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

Quote
Microsoft wants us to "Imagine life without walls"...
I say, "If there are no walls, who needs Windows?"

Useful Links:
Online Manual!
How to Help us Help you   
Search
Settings Repair Tool
     

Offline City Builder

  • Jr. Member
  • **
  • Posts: 281
Re: README: Checklist for performance problems
« Reply #42 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

Offline metallica48423

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,842
  • Gender: Male
  • Professional Multislacker!
    • Zentendo
Re: README: Checklist for performance problems
« Reply #43 on: May 02, 2008, 05:55:01 PM »
start a new topic please.
Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

Quote
Microsoft wants us to "Imagine life without walls"...
I say, "If there are no walls, who needs Windows?"

Useful Links:
Online Manual!
How to Help us Help you   
Search
Settings Repair Tool
     

Offline City Builder

  • Jr. Member
  • **
  • Posts: 281
Re: README: Checklist for performance problems
« Reply #44 on: May 02, 2008, 06:40:35 PM »

Offline metallica48423

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,842
  • Gender: Male
  • Professional Multislacker!
    • Zentendo
Re: README: Checklist for performance problems
« Reply #45 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?
Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

Quote
Microsoft wants us to "Imagine life without walls"...
I say, "If there are no walls, who needs Windows?"

Useful Links:
Online Manual!
How to Help us Help you   
Search
Settings Repair Tool
     

Offline evanora

  • Semi-Newbie
  • *
  • Posts: 13
  • Gender: Male
  • Life is hard then you die
    • Social Networking, Discussion, Chatting and Dating forum
Re: README: Checklist for performance problems
« Reply #46 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 [nofollow]

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 16,542
  • 戦場ヶ原、蕩れ!
    • motokochan on GitHub
    • @motokochan on Twitter
    • Animeneko Network
Re: README: Checklist for performance problems
« Reply #47 on: October 04, 2008, 11:09:01 PM »
The load seems kind of high. Is it usually above 4?
Motoko-chan
Director, Simple Machines

Just like... making of enemies / 負ける気しない やめるきない / You are cool but fool - Charisma.com 『HATE』

Note: I am not a member of the Simple Machines Forum project.


Offline casty23

  • Semi-Newbie
  • *
  • Posts: 18
Re: README: Checklist for performance problems
« Reply #48 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.

Offline vulcan59

  • Jr. Member
  • **
  • Posts: 175
  • Gender: Male
Re: README: Checklist for performance problems
« Reply #49 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


Offline villasg

  • Jr. Member
  • **
  • Posts: 375
Re: README: Checklist for performance problems
« Reply #50 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)

Offline metallica48423

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,842
  • Gender: Male
  • Professional Multislacker!
    • Zentendo
Re: README: Checklist for performance problems
« Reply #51 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
Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

Quote
Microsoft wants us to "Imagine life without walls"...
I say, "If there are no walls, who needs Windows?"

Useful Links:
Online Manual!
How to Help us Help you   
Search
Settings Repair Tool
     

Offline fractalbit

  • Semi-Newbie
  • *
  • Posts: 56
  • Gender: Male
  • I'm a llama!
Re: README: Checklist for performance problems
« Reply #52 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

Offline Mark Rose

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,498
  • Gender: Male
  • Performance Freak
Re: README: Checklist for performance problems
« Reply #53 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).
Hello, IT. Have you tried turning it off and on again?

Offline SergeantAsh

  • Full Member
  • ***
  • Posts: 418
Re: README: Checklist for performance problems
« Reply #54 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.
Quote
“Moderation has been called a virtue to limit the ambition of great men, and to console undistinguished people for their want of fortune and their lack of merit.”

Offline Mark Rose

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,498
  • Gender: Male
  • Performance Freak
Re: README: Checklist for performance problems
« Reply #55 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.
Hello, IT. Have you tried turning it off and on again?

Offline timi

  • Semi-Newbie
  • *
  • Posts: 13
  • Gender: Male
    • free smf forum
Re: README: Checklist for performance problems
« Reply #56 on: May 12, 2009, 09:45:07 AM »
Thank you

Offline Green Bird

  • Newbie
  • *
  • Posts: 1
Re: README: Checklist for performance problems
« Reply #57 on: May 19, 2009, 11:39:32 PM »
Thank to all

Offline lestatminiyo

  • Semi-Newbie
  • *
  • Posts: 16
Re: README: Checklist for performance problems
« Reply #58 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

Offline Mark Rose

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,498
  • Gender: Male
  • Performance Freak
Re: README: Checklist for performance problems
« Reply #59 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.
Hello, IT. Have you tried turning it off and on again?