
平台论坛博客文库

› 论坛 › 数据库技术 › MySQL › [讨论]大家都是怎么做的优化？

12 / 2 页下一页

[讨论]大家都是怎么做的优化？ [复制链接]

ruochen

版主

论坛徽章:: 8

1楼 [报告]

发表于 2008-07-23 17:59 |只看该作者

什么公司呀？
用这么多mysql? 都做什么应用？

yueliangdao0608

广告杀手

论坛徽章:: 0

2楼 [报告]

发表于 2008-07-23 21:01 |只看该作者

吧你的STATUS结果都贴出来看看吧。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

ruochen

版主

论坛徽章:: 8

3楼 [报告]

发表于 2008-07-23 23:03 |只看该作者

LZ的优化之路方向是对的

我现在开始也要注重mysql的优化了
库的容量在扩大，前台的响应时间是在增加

枫影谁用了

富足长乐

论坛徽章:: 1

4楼 [报告]

发表于 2008-07-24 08:56 |只看该作者

原帖由 yueliangdao0608 于 2008-7-23 21:01 发表
吧你的STATUS结果都贴出来看看吧。

(root:localhost:)(none)> show status ;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 6460 |
| Aborted_connects | 57920 |
| Binlog_cache_disk_use | 35698 |
| Binlog_cache_use | 12497959 |
| Bytes_received | 133 |
| Bytes_sent | 238 |
| Com_admin_commands | 0 |
| 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 | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| 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 | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 2 |
| Com_set_option | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| 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 | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| 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 | 163064 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 7688 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 33 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Innodb_buffer_pool_pages_data | 125737 |
| Innodb_buffer_pool_pages_dirty | 4 |
| Innodb_buffer_pool_pages_flushed | 10104343 |
| Innodb_buffer_pool_pages_free | 1 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 5334 |
| Innodb_buffer_pool_pages_total | 131072 |
| Innodb_buffer_pool_read_ahead_rnd | 167391 |
| Innodb_buffer_pool_read_ahead_seq | 937918 |
| Innodb_buffer_pool_read_requests | 1437545299 |
| Innodb_buffer_pool_reads | 3209564 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1620161366 |
| Innodb_data_fsyncs | 25258552 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 561090560 |
| Innodb_data_reads | 5979689 |
| Innodb_data_writes | 26769284 |
| Innodb_data_written | 3375720448 |
| Innodb_dblwr_pages_written | 10104343 |
| Innodb_dblwr_writes | 252968 |
| Innodb_log_waits | 13 |
| Innodb_log_write_requests | 304568006 |
| Innodb_log_writes | 24431524 |
| Innodb_os_log_fsyncs | 24597911 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 2900681216 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 6772233 |
| Innodb_pages_read | 66618693 |
| Innodb_pages_written | 10104343 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 2264627 |
| Innodb_row_lock_time_avg | 715 |
| Innodb_row_lock_time_max | 101696 |
| Innodb_row_lock_waits | 3165 |
| Innodb_rows_deleted | 5874593 |
| Innodb_rows_inserted | 282438995 |
| Innodb_rows_read | 2999927150 |
| Innodb_rows_updated | 92595428 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 31453 |
| Key_blocks_used | 57990 |
| Key_read_requests | 923329275 |
| Key_reads | 610781 |
| Key_write_requests | 101612544 |
| Key_writes | 5853656 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 1001 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 115 |
| Open_streams | 0 |
| Open_table_definitions | 243 |
| Open_tables | 283 |
| Opened_files | 886604 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1998 |
| Qcache_free_memory | 153212384 |
| Qcache_hits | 6723853 |
| Qcache_inserts | 8026469 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 309188 |
| Qcache_queries_in_cache | 2269 |
| Qcache_total_blocks | 6635 |
| Questions | 55022257 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 24962568 |
| Table_locks_waited | 2914803 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 124 |
| Threads_created | 9343 |
| Threads_running | 9 |
| Uptime | 1958807 |
+-----------------------------------+------------+
266 rows in set (0.07 sec)

复制代码

来我的，指点下^_^

飘雪99

丰衣足食

论坛徽章:: 0

5楼 [报告]

发表于 2008-07-24 10:42 |只看该作者

楼上的，你的是用的是innodb吧，我用的是myisam ，把我的status 也贴一下。

+-----------------------------------+------------+
| Variable_name                   | Value    |
+-----------------------------------+------------+
| Aborted_clients                | 8       |
| Aborted_connects                | 55518    |
| Binlog_cache_disk_use          | 0       |
| Binlog_cache_use                | 0       |
| Bytes_received                   | 3104043651 |
| Bytes_sent                      | 1463706347 |
| Com_admin_commands             | 13       |
| Com_alter_db                   | 0       |
| Com_alter_table                | 0       |
| Com_analyze                      | 0       |
| Com_backup_table                | 0       |
| Com_begin                      | 1338591 |
| Com_change_db                   | 5899952 |
| Com_change_master                | 0       |
| Com_check                      | 0       |
| Com_checksum                   | 0       |
| Com_commit                      | 1338610 |
| Com_create_db                   | 0       |
| Com_create_function             | 0       |
| Com_create_index                | 0       |
| Com_create_table                | 0       |
| Com_create_user                | 0       |
| Com_dealloc_sql                | 0       |
| Com_delete                      | 102065    |
| 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                      | 0       |
| Com_grant                      | 0       |
| Com_ha_close                   | 0       |
| Com_ha_open                      | 0       |
| Com_ha_read                      | 0       |
| Com_help                         | 0       |
| Com_insert                      | 6169626 |
| Com_insert_select                | 0       |
| Com_kill                         | 0       |
| Com_load                         | 0       |
| Com_load_master_data             | 0       |
| Com_load_master_table          | 0       |
| Com_lock_tables                | 11       |
| Com_optimize                   | 0       |
| Com_preload_keys                | 0       |
| Com_prepare_sql                | 0       |
| Com_purge                      | 0       |
| Com_purge_before_date          | 0       |
| Com_rename_table                | 0       |
| Com_repair                      | 11       |
| Com_replace                      | 714       |
| 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                      | 12346350 |
| Com_set_option                   | 7948604 |
| Com_show_binlog_events          | 0       |
| Com_show_binlogs                | 31       |
| Com_show_charsets                | 297       |
| Com_show_collations             | 297       |
| Com_show_column_types          | 0       |
| Com_show_create_db             | 1       |
| Com_show_create_table          | 123       |
| Com_show_databases             | 313       |
| Com_show_errors                | 0       |
| Com_show_fields                | 441       |
| Com_show_grants                | 128       |
| Com_show_innodb_status          | 0       |
| Com_show_keys                   | 153       |
| Com_show_logs                   | 0       |
| Com_show_master_status          | 1       |
| Com_show_ndb_status             | 0       |
| Com_show_new_master             | 0       |
| Com_show_open_tables             | 1       |
| Com_show_privileges             | 0       |
| Com_show_processlist             | 98       |
| Com_show_slave_hosts             | 13       |
| Com_show_slave_status          | 2       |
| Com_show_status                | 3956    |
| Com_show_storage_engines       | 0       |
| Com_show_tables                | 819       |
| Com_show_triggers                | 36       |
| Com_show_variables             | 1005    |
| 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                   | 11       |
| Com_unlock_tables                | 11       |
| Com_update                      | 2738143 |
| Com_update_multi                | 0       |
| 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                      | 8004808 |
| Created_tmp_disk_tables          | 448       |
| Created_tmp_files                | 222       |
| Created_tmp_tables             | 1137614 |
| Delayed_errors                   | 0       |
| Delayed_insert_threads          | 0       |
| Delayed_writes                   | 0       |
| Flush_commands                   | 2       |
| Handler_commit                   | 0       |
| Handler_delete                   | 27204543 |
| Handler_discover                | 0       |
| Handler_prepare                | 0       |
| Handler_read_first             | 3724    |
| Handler_read_key                | 24834706 |
| Handler_read_next                | 432848961  |
| Handler_read_prev                | 125865187  |
| Handler_read_rnd                | 6339488 |
| Handler_read_rnd_next          | 490839306  |
| Handler_rollback                | 0       |
| Handler_savepoint                | 0       |
| Handler_savepoint_rollback       | 0       |
| Handler_update                   | 12276635 |
| Handler_write                   | 11876471 |
| Innodb_buffer_pool_pages_data    | 0       |
| Innodb_buffer_pool_pages_dirty | 0       |
| Innodb_buffer_pool_pages_flushed  | 0       |
| Innodb_buffer_pool_pages_free    | 32768    |
| Innodb_buffer_pool_pages_latched  | 0       |
| Innodb_buffer_pool_pages_misc    | 0       |
| Innodb_buffer_pool_pages_total | 32768    |
| Innodb_buffer_pool_read_ahead_rnd | 0       |
| Innodb_buffer_pool_read_ahead_seq | 0       |
| Innodb_buffer_pool_read_requests  | 0       |
| Innodb_buffer_pool_reads       | 0       |
| Innodb_buffer_pool_wait_free    | 0       |
| Innodb_buffer_pool_write_requests | 0       |
| Innodb_data_fsyncs             | 0       |
| Innodb_data_pending_fsyncs       | 0       |
| Innodb_data_pending_reads       | 0       |
| Innodb_data_pending_writes       | 0       |
| Innodb_data_read                | 0       |
| Innodb_data_reads                | 1       |
| Innodb_data_writes             | 0       |
| Innodb_data_written             | 0       |
| Innodb_dblwr_pages_written       | 0       |
| Innodb_dblwr_writes             | 0       |
| Innodb_log_waits                | 0       |
| Innodb_log_write_requests       | 0       |
| Innodb_log_writes                | 0       |
| Innodb_os_log_fsyncs             | 0       |
| Innodb_os_log_pending_fsyncs    | 0       |
| Innodb_os_log_pending_writes    | 0       |
| Innodb_os_log_written          | 0       |
| Innodb_page_size                | 16384    |
| Innodb_pages_created             | 0       |
| Innodb_pages_read                | 0       |
| Innodb_pages_written             | 0       |
| 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             | 0       |
| Innodb_rows_read                | 0       |
| Innodb_rows_updated             | 0       |
| Key_blocks_not_flushed          | 762       |
| Key_blocks_unused                | 444928    |
| Key_blocks_used                | 2067692 |
| Key_read_requests                | 697929493  |
| Key_reads                      | 3228439 |
| Key_write_requests             | 68613571 |
| Key_writes                      | 17575534 |
| Last_query_cost                | 0.000000 |
| Max_used_connections             | 501       |
| Not_flushed_delayed_rows       | 0       |
| Open_files                      | 1400    |
| Open_streams                   | 0       |
| Open_tables                      | 1337    |
| Opened_tables                   | 3277    |
| Prepared_stmt_count             | 0       |
| Qcache_free_blocks             | 2219    |
| Qcache_free_memory             | 532566664  |
| Qcache_hits                      | 1357511 |
| Qcache_inserts                   | 10917094 |
| Qcache_lowmem_prunes             | 0       |
| Qcache_not_cached                | 1432965 |
| Qcache_queries_in_cache          | 2643    |
| Qcache_total_blocks             | 7552    |
| Questions                      | 47196033 |
| Rpl_status                      | NULL    |
| Select_full_join                | 8       |
| Select_full_range_join          | 0       |
| Select_range                   | 450       |
| Select_range_check             | 0       |
| Select_scan                      | 27723    |
| Slave_open_temp_tables          | 0       |
| Slave_retried_transactions       | 0       |
| Slave_running                   | OFF       |
| Slow_launch_threads             | 0       |
| Slow_queries                   | 14844    |
| Sort_merge_passes                | 154       |
| Sort_range                      | 1788880 |
| Sort_rows                      | 4227989 |
| Sort_scan                      | 1138782 |
| Table_locks_immediate          | 21015107 |
| Table_locks_waited             | 263484    |
| Tc_log_max_pages_used          | 0       |
| Tc_log_page_size                | 0       |
| Tc_log_page_waits                | 0       |
| Threads_cached                   | 30       |
| Threads_connected                | 4       |
| Threads_created                | 53155    |
| Threads_running                | 2       |
| Uptime                         | 237524    |
| Uptime_since_flush_status       | 237524    |
+-----------------------------------+------------+
225 rows in set (0.00 sec)