1. 任何打算长期使用的设置应该写到全局配置文件，而不是在命令行个特别指定。否则，如果偶然在启动的时候忘记了设置就会有风险。

2. 在修改mysql的配置文件的时候，经常会发现修改了配置文件，根本没有起作用。查看配置文件路径:

root@wiki:~# which mysqld
/usr/sbin/mysqld
root@wiki:~# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'
140712  3:09:08 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
140712  3:09:08 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140712  3:09:08 [Note] Plugin 'FEDERATED' is disabled.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

query_cache_size 变量是全局的。

sort_buffer_size 变量默认是全局相同的，但是每个线程里也可以设置。

join_buffer_size 变量也有全局默认值且每个线程是可以折折的，但是若一个查询中关联多张表，可以为每个关联分配一个关联缓冲（join buffer）,所以每个查询可能有多个关联缓冲。

除了在配置文件中设置变量，有很多变量也可以在服务器运行时修改。mysql把这些变量归结为动态变量。

常用的变量和动态修改它们的效果。

key_buffer_size

   设置这个变量可以一次性为键缓冲区（key buffer，也叫键缓存key cache）分配所有指定的空间。然后操作系统不会真的立刻分配内存，而是到使用时才真正分配。例如设置键缓冲的大小为1GB，并不意味着服务器立刻分配1GB的 内存。

table_cache_size

   设置这个变量不会立即生效---会延迟到下次有线程打开表才有效果。当有线程打开表时，MySQL会检查这个变量的值。如果值大于缓存中的表的数量，线程可以把最新打开的表放入缓存；如果值比缓存中的的表数小，MySQL将从缓存中删除不常使用的表。

thread_cache_size

    设置这个变量不会立即生效---将在下次有连接被关闭时产生效果。当有连接被关闭时，MySQL检查缓存中是否还有空间来缓存线程。如果有空间，则缓存该线程以备下次连接重用；如果没有空间，它将销毁该线程而不再缓存。在这个场景中，缓存的线程数，以及线程缓存使用的内存，并不会立即减少；只有在新的连接删除缓存中的一个线程并使用后才会减少。（MySQL只在关闭连接时才在缓存中增加线程，只在创建新连接时才从缓存中删除线程。）

query_cache_size 

     MySQL在启动的时候，一次性分配并且初始化这块内存。如果修改这个变量（即使设置为与当前一样的值），MySQL会立即删除所有缓存的查询，重新分配这片缓存到指定大小，并且重新初始化内存。这可能花费较长的时间，在完成初始化之前服务器都无法提供服务，因为MySQL是逐个清理缓存的查询，不是一次性全部删除。

read_buffer_size 

    MySQL只会在有查询需要使用时才会为该缓存分配内存，并且会一次性分配该参数指定大小的全部内存。

read_rnd_buffer_size

    MySQL只会在又查村需要使用时才会为该缓存分配内存，并且只会分配需要的内存大小，而不是全部指定的大小。（max_read_rnd_buffer_size这个名字更能表达这个变量实际的含义。）

sort_buffer_size

    MySQL只会在有查询需要做排序操作的时候才会为该缓存分配内存。然后，一旦需要排序，MySQL就会立刻分配该参数指定大小的全部内存，而不管该排序是否需要这么大的内存。

总的来说，设置很大的排序缓存代价可能非常高，所以除非确定必须要达到这么大，否则不要增加排序缓存的大小。

3. 检查MySQL服务器状态变量

   有时候可以使用show global status的输出，作为配置的输入，以便更好的通过工作负载来自定义配置。为了更好的达到最佳效果，既要看绝对值，又要看值是如何随时变化的，最好为高峰和非高峰时间的值做几个快照。可以使用下面的命令每隔60秒来查看状态变量的增量变化。  

root@wiki:~# mysqladmin -uroot -p  extended-status -ri60
------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 0           |
| Aborted_connects                         | 1           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 11993       |
| Bytes_sent                               | 66073       |
| Com_admin_commands                       | 2           |
| 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_begin                                | 0           |
| Com_binlog                               | 0           |
| Com_call_procedure                       | 0           |
| Com_change_db                            | 1           |
| 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                                | 1           |
| 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_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_resignal                             | 0           |
| Com_revoke                               | 0           |
| Com_revoke_all                           | 0           |
| Com_rollback                             | 0           |
| Com_rollback_to_savepoint                | 0           |
| Com_savepoint                            | 0           |
| Com_select                               | 108         |
| Com_set_option                           | 0           |
| Com_signal                               | 0           |
| Com_show_authors                         | 0           |
| Com_show_binlog_events                   | 0           |
| Com_show_binlogs                         | 0           |
| Com_show_charsets                        | 0           |
| Com_show_collations                      | 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                       | 2           |
| 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                          | 2           |
| Com_show_function_status                 | 0           |
| Com_show_grants                          | 0           |
| Com_show_keys                            | 0           |
| Com_show_master_status                   | 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_relaylog_events                 | 0           |
| Com_show_slave_hosts                     | 0           |
| Com_show_slave_status                    | 0           |
| Com_show_status                          | 7           |
| Com_show_storage_engines                 | 0           |
| Com_show_table_status                    | 0           |
| Com_show_tables                          | 2           |
| Com_show_triggers                        | 0           |
| Com_show_variables                       | 1           |
| 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                             | 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                              | 0           |
| Connections                              | 62          |
| Created_tmp_disk_tables                  | 54          |
| Created_tmp_files                        | 6           |
| Created_tmp_tables                       | 222         |
| Delayed_errors                           | 0           |
| Delayed_insert_threads                   | 0           |
| Delayed_writes                           | 0           |
| Flush_commands                           | 1           |
| Handler_commit                           | 0           |
| Handler_delete                           | 0           |
| Handler_discover                         | 0           |
| Handler_prepare                          | 0           |
| Handler_read_first                       | 5           |
| Handler_read_key                         | 0           |
| Handler_read_last                        | 0           |
| Handler_read_next                        | 0           |
| Handler_read_prev                        | 0           |
| Handler_read_rnd                         | 0           |
| Handler_read_rnd_next                    | 3636        |
| Handler_rollback                         | 0           |
| Handler_savepoint                        | 0           |
| Handler_savepoint_rollback               | 0           |
| Handler_update                           | 0           |
| Handler_write                            | 3585        |
| Innodb_buffer_pool_pages_data            | 626         |
| Innodb_buffer_pool_bytes_data            | 10256384    |
| Innodb_buffer_pool_pages_dirty           | 0           |
| Innodb_buffer_pool_bytes_dirty           | 0           |
| Innodb_buffer_pool_pages_flushed         | 0           |
| Innodb_buffer_pool_pages_free            | 7566        |
| Innodb_buffer_pool_pages_misc            | 0           |
| 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         | 11874       |
| Innodb_buffer_pool_reads                 | 627         |
| Innodb_buffer_pool_wait_free             | 0           |
| Innodb_buffer_pool_write_requests        | 0           |
| Innodb_data_fsyncs                       | 3           |
| Innodb_data_pending_fsyncs               | 0           |
| Innodb_data_pending_reads                | 0           |
| Innodb_data_pending_writes               | 0           |
| Innodb_data_read                         | 12455936    |
| Innodb_data_reads                        | 637         |
| Innodb_data_writes                       | 3           |
| Innodb_data_written                      | 1536        |
| Innodb_dblwr_pages_written               | 0           |
| Innodb_dblwr_writes                      | 0           |
| Innodb_have_atomic_builtins              | 0           |
| Innodb_log_waits                         | 0           |
| Innodb_log_write_requests                | 0           |
| Innodb_log_writes                        | 1           |
| Innodb_os_log_fsyncs                     | 3           |
| Innodb_os_log_pending_fsyncs             | 0           |
| Innodb_os_log_pending_writes             | 0           |
| Innodb_os_log_written                    | 512         |
| Innodb_page_size                         | 16384       |
| Innodb_pages_created                     | 0           |
| Innodb_pages_read                        | 626         |
| 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           |
| Innodb_truncated_status_writes           | 0           |
| Key_blocks_not_flushed                   | 0           |
| Key_blocks_unused                        | 13396       |
| Key_blocks_used                          | 0           |
| Key_read_requests                        | 0           |
| Key_reads                                | 0           |
| Key_write_requests                       | 0           |
| Key_writes                               | 0           |
| Last_query_cost                          | 0           |
| Max_used_connections                     | 2           |
| Not_flushed_delayed_rows                 | 0           |
| Open_files                               | 86          |
| Open_streams                             | 0           |
| Open_table_definitions                   | 122         |
| Open_tables                              | 122         |
| Opened_files                             | 458         |
| Opened_table_definitions                 | 122         |
| Opened_tables                            | 495         |
| 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                       | 1           |
| Qcache_free_memory                       | 16759696    |
| Qcache_hits                              | 0           |
| Qcache_inserts                           | 0           |
| Qcache_lowmem_prunes                     | 0           |
| Qcache_not_cached                        | 108         |
| Qcache_queries_in_cache                  | 0           |
| Qcache_total_blocks                      | 1           |
| Queries                                  | 183         |
| Questions                                | 183         |
| Rpl_status                               | 0           |
| Select_full_join                         | 0           |
| Select_full_range_join                   | 0           |
| Select_range                             | 0           |
| Select_range_check                       | 0           |
| Select_scan                              | 23          |
| Slave_heartbeat_period                   | 0           |
| Slave_open_temp_tables                   | 0           |
| Slave_received_heartbeats                | 0           |
| Slave_retried_transactions               | 0           |
| Slave_running                            | 0           |
| Slow_launch_threads                      | 0           |
| Slow_queries                             | 0           |
| Sort_merge_passes                        | 0           |
| Sort_range                               | 0           |
| Sort_rows                                | 0           |
| Sort_scan                                | 0           |
| Ssl_accept_renegotiates                  | 0           |
| Ssl_accepts                              | 0           |
| Ssl_callback_cache_hits                  | 0           |
| Ssl_cipher                               | 0           |
| Ssl_cipher_list                          | 0           |
| 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                   | 0           |
| 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                              | 0           |
| Table_locks_immediate                    | 81          |
| Table_locks_waited                       | 0           |
| Tc_log_max_pages_used                    | 0           |
| Tc_log_page_size                         | 0           |
| Tc_log_page_waits                        | 0           |
| Threads_cached                           | 1           |
| Threads_connected                        | 1           |
| Threads_created                          | 2           |
| Threads_running                          | 1           |
| Uptime                                   | 82254       |
| Uptime_since_flush_status                | 82254