MYSQL5.x 優化#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
另外若在連結、讀取及寫入的時間需要加長,則自行決定timeout參數的值#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
其他可以參考以下作法,適用x86/x64 1~2Core 4G RAM的SERVER#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%max_allowed_packet%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global max_allowed_packet = 1*1024*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%key_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global key_buffer_size = 1*1024*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%table_open_cache%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global table_open_cache = 1*512;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%back_log%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global back_log = 1*100;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%interactive_timeout%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global interactive_timeout = 1*7200;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%read_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global read_buffer_size = 1*256*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%join_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global join_buffer_size = 1*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%net_buffer_length%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global net_buffer_length = 1*32*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%preload_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global preload_buffer_size = 1*64*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%sort_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global sort_buffer_size = 1*512*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%thread_cache_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global thread_cache_size = 1*256;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%table_definition_cache%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global table_definition_cache = 1*1000;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%query_cache_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global query_cache_size = 1*128*1024*1204;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%myisam_sort_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global myisam_sort_buffer_size = 1*512*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%max_heap_table_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global max_heap_table_size = 1*32*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global max_heap_table_size = 1*64*1024*1024;
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
另外若在連結、讀取及寫入的時間需要加長,則自行決定timeout參數的值#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
其他可以參考以下作法,適用x86/x64 1~2Core 4G RAM的SERVER#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%max_allowed_packet%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global max_allowed_packet = 1*1024*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%key_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global key_buffer_size = 1*1024*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%table_open_cache%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global table_open_cache = 1*512;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%back_log%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global back_log = 1*100;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%interactive_timeout%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global interactive_timeout = 1*7200;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%read_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global read_buffer_size = 1*256*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%join_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global join_buffer_size = 1*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%net_buffer_length%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global net_buffer_length = 1*32*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%preload_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global preload_buffer_size = 1*64*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%sort_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global sort_buffer_size = 1*512*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%thread_cache_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global thread_cache_size = 1*256;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%table_definition_cache%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global table_definition_cache = 1*1000;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%query_cache_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global query_cache_size = 1*128*1024*1204;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%myisam_sort_buffer_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global myisam_sort_buffer_size = 1*512*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
show VARIABLES like '%max_heap_table_size%';#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global max_heap_table_size = 1*32*1024*1024;#B$Am.Dj#]HcJK-U)l>qZ]*ZdxI*!BDR)[C+Pvih0
set global max_heap_table_size = 1*64*1024*1024;