8기가 램에 설정파일 스크랩 합니다.
MySQL config 5.0.x, 5.1.x, 5.5.x
Connections : 1000
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 |
[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 #table_open_cache = 512 #5.1.x, 5.5.x ## 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 = #replicate-same-server-id = #auto-increment-increment = #auto-increment-offset = #master-connect-retry = #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = #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 , 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 |
