Kết quả 1 đến 1 của 1
Chủ đề: Cấu hình my.cnf cho Kloxo
-
11-24-2011, 10:16 PM #1
Junior Member
- Ngày tham gia
- Aug 2015
- Bài viết
- 0
Cấu hình my.cnf cho Kloxo
:Ma (4):tìm cách cấu hình cho con server làm bệnh tự kỷ của mình nặng lên trông thấy bực quá post lên đây cho bệnh viện đỡ quá tải:Ma (4):
đây là my.cnf cho 1GB
đây là my.cnf cho 2GBMã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 1GB RAM dedicated server #Connections: 100 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 256K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 256K #default: 2M, larger may cause perf issues read_buffer_size = 256K #default: 128K, change in increments of 4K read_rnd_buffer_size = 256K #default: 256K join_buffer_size = 256K #default: 128K binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 134400K = 131.25MB ## Query Cache query_cache_size = 32M #global buffer query_cache_limit = 512K #max query result size to put in cache ## Connections max_connections = 100 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 64M #recommend same size as tmp_table_size bulk_insert_buffer_size = 64M #recommend same size as tmp_table_size tmp_table_size = 64M #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 512K #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 64M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 128M #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 16M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
đây là my.cnf cho 4GBMã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 2GB RAM dedicated server #Connections: 500 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 256K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 256K #default: 2M, larger may cause perf issues read_buffer_size = 256K #default: 128K, change in increments of 4K read_rnd_buffer_size = 256K #default: 256K join_buffer_size = 256K #default: 128K binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 672000K = 656.25MB ## Query Cache query_cache_size = 32M #global buffer query_cache_limit = 512K #max query result size to put in cache ## Connections max_connections = 500 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 128M #recommend same size as tmp_table_size bulk_insert_buffer_size = 128M #recommend same size as tmp_table_size tmp_table_size = 128M #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 512K #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 64M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 768M #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 32M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
đây là my.cnf cho 8GBMã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 4GB RAM dedicated server #Connections: 1000 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 256K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 512K #default: 2M, larger may cause perf issues read_buffer_size = 512K #default: 128K, change in increments of 4K read_rnd_buffer_size = 512K #default: 256K join_buffer_size = 512K #default: 128K binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 2368000K = 2.312GB ## Query Cache query_cache_size = 32M #global buffer query_cache_limit = 512K #max query result size to put in cache ## Connections max_connections = 1000 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 1G #recommend same size as tmp_table_size bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size tmp_table_size = 1G #recommend 1G min #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 512K #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 128M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 2G #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 64M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
đây là my.cnf cho 16GBMã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 8GB RAM dedicated server #Connections: 1000 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 256K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 512K #default: 2M, larger may cause perf issues read_buffer_size = 512K #default: 128K, change in increments of 4K read_rnd_buffer_size = 512K #default: 256K join_buffer_size = 512K #default: 128K binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 4736000K = 4.625GB ## Query Cache query_cache_size = 32M #global buffer query_cache_limit = 512K #max query result size to put in cache ## Connections max_connections = 2000 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 1G #recommend same size as tmp_table_size bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size tmp_table_size = 1G #recommend 1G min #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 512K #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 256M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 4G #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 128M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
đây là my.cnf cho 32GBMã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 16GB RAM dedicated server #Connections: 1000 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 256K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 1M #default: 2M, larger may cause perf issues read_buffer_size = 1M #default: 128K, change in increments of 4K read_rnd_buffer_size = 1M #default: 256K join_buffer_size = 1M #default: 128K binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 8832000K = 8.625GB ## Query Cache query_cache_size = 32M #global buffer query_cache_limit = 512K #max query result size to put in cache ## Connections max_connections = 2000 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 1G #recommend same size as tmp_table_size bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size tmp_table_size = 1G #recommend 1G min #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 1M #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 256M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 10G #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 128M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
đây là my.cnf cho 64GBMã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 32GB RAM dedicated server #Connections: 2000 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 256K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 1M #default: 2M, larger may cause perf issues read_buffer_size = 1M #default: 128K, change in increments of 4K read_rnd_buffer_size = 1M #default: 256K join_buffer_size = 1M #default: 128K binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 8832000K = 8.625GB ## Query Cache query_cache_size = 32M #global buffer query_cache_limit = 512K #max query result size to put in cache ## Connections max_connections = 2000 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 1G #recommend same size as tmp_table_size bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size tmp_table_size = 1G #recommend 1G min #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 1M #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 512M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 18G #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 128M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
đây là my.cnf cho 72GBMã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 64GB RAM dedicated server #Connections: 2000 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 512K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 2M #default: 2M, larger may cause perf issues read_buffer_size = 2M #default: 128K, change in increments of 4K read_rnd_buffer_size = 2M #default: 256K join_buffer_size = 2M #default: 128K binlog_cache_size = 128K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 17664000K = 17.250GB ## Query Cache query_cache_size = 64M #global buffer query_cache_limit = 512K #max query result size to put in cache ## Connections max_connections = 2000 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 1G #recommend same size as tmp_table_size bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size tmp_table_size = 1G #recommend 1G min #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 1M #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 768M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 38G #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 128M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
đây là my.cnf cho 96GBMã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 72GB RAM dedicated server #Connections: 5000 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 512K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 2M #default: 2M, larger may cause perf issues read_buffer_size = 2M #default: 128K, change in increments of 4K read_rnd_buffer_size = 2M #default: 256K join_buffer_size = 2M #default: 128K binlog_cache_size = 128K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 44160000K = 43.125GB ## Query Cache query_cache_size = 64M #global buffer query_cache_limit = 512K #max query result size to put in cache ## Connections max_connections = 5000 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 1G #recommend same size as tmp_table_size bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size tmp_table_size = 1G #recommend 1G min #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 1M #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 768M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 48G #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 256M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
đây là my.cnf cho 128GBMã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 96GB RAM dedicated server #Connections: 5000 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 512K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 2M #default: 2M, larger may cause perf issues read_buffer_size = 2M #default: 128K, change in increments of 4K read_rnd_buffer_size = 2M #default: 256K join_buffer_size = 2M #default: 128K binlog_cache_size = 128K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 44160000K = 43.125GB ## Query Cache query_cache_size = 128M #global buffer query_cache_limit = 1M #max query result size to put in cache ## Connections max_connections = 5000 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 2G #recommend same size as tmp_table_size bulk_insert_buffer_size = 2G #recommend same size as tmp_table_size tmp_table_size = 2G #recommend 1G min #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 1M #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 768M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 48G #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 256M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
Mã:################################################################################ #DATE: 2011-02-02 #SITE: http://congtruongit.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 128GB RAM dedicated server #Connections: 5000 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 512K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 2M #default: 2M, larger may cause perf issues read_buffer_size = 2M #default: 128K, change in increments of 4K read_rnd_buffer_size = 2M #default: 256K join_buffer_size = 2M #default: 128K binlog_cache_size = 128K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 44160000K = 43.125GB ## Query Cache query_cache_size = 128M #global buffer query_cache_limit = 1M #max query result size to put in cache ## Connections max_connections = 5000 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 2G #recommend same size as tmp_table_size bulk_insert_buffer_size = 2G #recommend same size as tmp_table_size tmp_table_size = 2G #recommend 1G min ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 1M #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 768M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 72G #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 256M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
Trả lời kèm Trích dẫnCác Chủ đề tương tự
-
1 số lệnh ssh sưu tầm dùng cho Kloxo + Centos
Bởi suemall trong diễn đàn Mẹo Vặt - Thủ Thuật - Hướng Dẫn websiteTrả lời: 0Bài viết cuối: 12-11-2011, 05:52 PM
Khu dự án Saigon Gateway chủ đầu tư Công ty CP BĐS Hiệp Phú thiết kế tiện lợi sảnh sang trọng tài chính linh hoạt. Saigon Gateway giagocchudautu.com thiết kế tiện lợi mang niềm tin di chuyển dễ...
Khu căn hộ chung cự Saigon...