Help to improve the performance

[tectonny]

Help to improve the performance

Some are well above, see this:

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

Key buffer read hit rate: (key_buffer_size) 0.095 (should be <= 0.01)

Key buffer write hit rate: (key_buffer_size) 0.5893 (should be <= 0.5)

Thread cache hit rate: (thread_cache_size) 1.0008 (should be >= 30 )

Thread cache usage: (thread_cache_size) 0 (should be >= 0.7 and <= 0.9)

My works on a dedicated forum.

See my status and tell me what I can do to improve:

Code Select Expand


Basic Information
July 15, 2014, 05:43:17 PM
Operating System:   CentOS release 6.5 (Final)
Processor:   Intel® Xeon® CPU E31240 @ 3.30GHz (1600.000MHz)
Load averages:   0.58, 0.56, 0.52
Current processes:   317 (313 sleeping, 1 running, 3 zombie)
Processes by CPU:   php (17) 23.1%, mysqld (1) 5.4%, (other) (86) 0.5%
Memory usage:    41.624% (6789512k / 16311456k)
Swap: 0% (0k / 8224760k)
MySQL Statistics
MySQL 5.5.x
Connections per second:    1.2781
Kilobytes received per second:    6.5489
Kilobytes sent per second:    262.9956
Queries per second:    30.9759
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    106.8925 (should be <= 80)
Key buffer read hit rate:
(key_buffer_size)    0.095 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.5893 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    1.0008 (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.2383 (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:   88
Aborted_connects:   13306
Binlog_cache_disk_use:   0
Binlog_cache_use:   0
Binlog_stmt_cache_disk_use:   0
Binlog_stmt_cache_use:   0
Bytes_received:   968679189
Bytes_sent:   38901204903
Com_admin_commands:   3171
Com_assign_to_keycache:   0
Com_alter_db:   0
Com_alter_db_upgrade:   0
Com_alter_event:   0
Com_alter_function:   0
Com_alter_procedure:   0
Com_alter_server:   0
Com_alter_table:   73
Com_alter_tablespace:   0
Com_analyze:   0
Com_begin:   0
Com_binlog:   0
Com_call_procedure:   0
Com_change_db:   94465
Com_change_master:   0
Com_check:   2
Com_checksum:   0
Com_commit:   0
Com_create_db:   5
Com_create_event:   0
Com_create_function:   0
Com_create_index:   0
Com_create_procedure:   0
Com_create_server:   0
Com_create_table:   394
Com_create_trigger:   0
Com_create_udf:   0
Com_create_user:   0
Com_create_view:   0
Com_dealloc_sql:   0
Com_delete:   34307
Com_delete_multi:   0
Com_do:   0
Com_drop_db:   2
Com_drop_event:   0
Com_drop_function:   0
Com_drop_index:   0
Com_drop_procedure:   0
Com_drop_server:   0
Com_drop_table:   371
Com_drop_trigger:   0
Com_drop_user:   2
Com_drop_view:   0
Com_empty_query:   55
Com_execute_sql:   0
Com_flush:   0
Com_grant:   43
Com_ha_close:   0
Com_ha_open:   0
Com_ha_read:   0
Com_help:   0
Com_insert:   777891
Com_insert_select:   628
Com_install_plugin:   0
Com_kill:   0
Com_load:   0
Com_lock_tables:   4
Com_optimize:   0
Com_preload_keys:   0
Com_prepare_sql:   0
Com_purge:   0
Com_purge_before_date:   0
Com_release_savepoint:   0
Com_rename_table:   0
Com_rename_user:   0
Com_repair:   0
Com_replace:   5626
Com_replace_select:   0
Com_reset:   0
Com_resignal:   0
Com_revoke:   0
Com_revoke_all:   0
Com_rollback:   0
Com_rollback_to_savepoint:   0
Com_savepoint:   0
Com_select:   2733633
Com_set_option:   264124
Com_signal:   0
Com_show_authors:   0
Com_show_binlog_events:   0
Com_show_binlogs:   8
Com_show_charsets:   0
Com_show_client_statistics:   0
Com_show_collations:   11
Com_show_contributors:   0
Com_show_create_db:   15
Com_show_create_event:   0
Com_show_create_func:   0
Com_show_create_proc:   0
Com_show_create_table:   106
Com_show_create_trigger:   0
Com_show_databases:   3
Com_show_engine_logs:   0
Com_show_engine_mutex:   0
Com_show_engine_status:   0
Com_show_events:   3
Com_show_errors:   0
Com_show_fields:   296
Com_show_function_status:   0
Com_show_grants:   21
Com_show_index_statistics:   0
Com_show_keys:   104
Com_show_master_status:   14
Com_show_open_tables:   0
Com_show_plugins:   0
Com_show_privileges:   0
Com_show_procedure_status:   0
Com_show_processlist:   483
Com_show_profile:   0
Com_show_profiles:   0
Com_show_relaylog_events:   0
Com_show_slave_hosts:   0
Com_show_slave_status:   14
Com_show_status:   3
Com_show_storage_engines:   2
Com_show_table_statistics:   0
Com_show_table_status:   72
Com_show_tables:   18532
Com_show_thread_statistics:   0
Com_show_triggers:   10
Com_show_user_statistics:   0
Com_show_variables:   37
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_reprepare:   0
Com_stmt_reset:   0
Com_stmt_send_long_data:   0
Com_truncate:   13
Com_uninstall_plugin:   0
Com_unlock_tables:   4
Com_update:   371928
Com_update_multi:   56
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:   184616
Created_tmp_disk_tables:   6312
Created_tmp_files:   34
Created_tmp_tables:   26483
Delayed_errors:   0
Delayed_insert_threads:   3
Delayed_writes:   45369
Flush_commands:   1
Handler_commit:   198325
Handler_delete:   113583
Handler_discover:   0
Handler_prepare:   0
Handler_read_first:   160301
Handler_read_key:   348640313
Handler_read_last:   39967
Handler_read_next:   2131422576
Handler_read_prev:   6517117
Handler_read_rnd:   1819542
Handler_read_rnd_next:   1736495946
Handler_rollback:   0
Handler_savepoint:   0
Handler_savepoint_rollback:   0
Handler_update:   595055
Handler_write:   16737599
Innodb_buffer_pool_pages_data:   2204
Innodb_buffer_pool_bytes_data:   36110336
Innodb_buffer_pool_pages_dirty:   0
Innodb_buffer_pool_bytes_dirty:   0
Innodb_buffer_pool_pages_flushed:   3008
Innodb_buffer_pool_pages_free:   5986
Innodb_buffer_pool_pages_misc:   2
Innodb_buffer_pool_pages_total:   8192
Innodb_buffer_pool_read_ahead_rnd:   0
Innodb_buffer_pool_read_ahead:   0
Innodb_buffer_pool_read_ahead_evicted:   0
Innodb_buffer_pool_read_requests:   3871010
Innodb_buffer_pool_reads:   1473
Innodb_buffer_pool_wait_free:   0
Innodb_buffer_pool_write_requests:   200385
Innodb_data_fsyncs:   5199
Innodb_data_pending_fsyncs:   0
Innodb_data_pending_reads:   0
Innodb_data_pending_writes:   0
Innodb_data_read:   26316800
Innodb_data_reads:   1674
Innodb_data_writes:   6991
Innodb_data_written:   106544128
Innodb_dblwr_pages_written:   3008
Innodb_dblwr_writes:   1044
Innodb_have_atomic_builtins:   ON
Innodb_log_waits:   0
Innodb_log_write_requests:   14161
Innodb_log_writes:   1440
Innodb_os_log_fsyncs:   2411
Innodb_os_log_pending_fsyncs:   0
Innodb_os_log_pending_writes:   0
Innodb_os_log_written:   7480320
Innodb_page_size:   16384
Innodb_pages_created:   732
Innodb_pages_read:   1472
Innodb_pages_written:   3008
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:   1
Innodb_rows_inserted:   92185
Innodb_rows_read:   963189
Innodb_rows_updated:   55
Innodb_truncated_status_writes:   0
Key_blocks_not_flushed:   0
Key_blocks_unused:   0
Key_blocks_used:   6698
Key_read_requests:   1475062840
Key_reads:   140132228
Key_write_requests:   1925037
Key_writes:   1134363
Last_query_cost:   0.000000
Max_used_connections:   23
Not_flushed_delayed_rows:   0
Open_files:   327
Open_streams:   0
Open_table_definitions:   400
Open_tables:   400
Opened_files:   75189
Opened_table_definitions:   31666
Opened_tables:   42757
Performance_schema_cond_classes_lost:   0
Performance_schema_cond_instances_lost:   0
Performance_schema_file_classes_lost:   0
Performance_schema_file_handles_lost:   0
Performance_schema_file_instances_lost:   0
Performance_schema_locker_lost:   0
Performance_schema_mutex_classes_lost:   0
Performance_schema_mutex_instances_lost:   0
Performance_schema_rwlock_classes_lost:   0
Performance_schema_rwlock_instances_lost:   0
Performance_schema_table_handles_lost:   0
Performance_schema_table_instances_lost:   0
Performance_schema_thread_classes_lost:   0
Performance_schema_thread_instances_lost:   0
Prepared_stmt_count:   0
Qcache_free_blocks:   0
Qcache_free_memory:   0
Qcache_hits:   0
Qcache_inserts:   0
Qcache_lowmem_prunes:   0
Qcache_not_cached:   0
Qcache_queries_in_cache:   0
Qcache_total_blocks:   0
Queries:   4477614
Questions:   4474442
Rpl_status:   AUTH_MASTER
Select_full_join:   12276
Select_full_range_join:   103950
Select_range:   276104
Select_range_check:   0
Select_scan:   655912
Slave_heartbeat_period:   0.000
Slave_open_temp_tables:   0
Slave_received_heartbeats:   0
Slave_retried_transactions:   0
Slave_running:   OFF
Slow_launch_threads:   0
Slow_queries:   4
Sort_merge_passes:   12
Sort_range:   9985
Sort_rows:   59225676
Sort_scan:   18699
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:   4711179
Table_locks_waited:   7459
Tc_log_max_pages_used:   0
Tc_log_page_size:   0
Tc_log_page_waits:   0
Threads_cached:   0
Threads_connected:   3
Threads_created:   184471
Threads_running:   1
Uptime:   144449
Uptime_since_flush_status:   144449



[kat]

Why are you looking to improve the performance? Is your forum very slow, or something?

[Arantor]

I wouldn't trust that status script. It hasn't been updated in a while.

What have you done thus far to manage performance?