SMF Support > Server Performance and Configuration

README: Checklist for performance problems

<< < (12/14) > >>

Mark Rose:

--- Quote from: @sh 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.

--- End quote ---

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.

timi:
Thank you

Green Bird:
Thank to all

lestatminiyo:
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: ---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
--- End code ---

Mark Rose:

--- Quote from: lestatminiyo on December 04, 2009, 03:51:58 AM ---Memory usage:    97.492% (4043356k / 4147392k)

--- End quote ---

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)

--- End quote ---

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.

Navigation

[0] Message Index

[#] Next page

[*] Previous page