-
Notifications
You must be signed in to change notification settings - Fork 127
/
my.cnf_4GB
181 lines (155 loc) · 6.95 KB
/
my.cnf_4GB
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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
################################################################################
#DATE: 2011-02-02
#SITE: http://datastrangler.com
#DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x
#RAM: 4GB RAM dedicated server
#Connections: 1000 connections
################################################################################
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = -15
[client]
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8
[mysqld]
user = mysql
## Charset and Collation
character-set-server = utf8
collation-server = utf8_general_ci
## Files
back_log = 300
open_files_limit = 8192
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
skip-external-locking
skip-name-resolve
## Logging
datadir = /var/lib/mysql
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-index
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
log_error = /var/log/mysql/error.log
log_warnings
log_bin = /var/log/mysql/mysql-bin.log
slow_query_log_file = /var/log/mysql/mariadb-slow.log
slow_query_log = 1
#log_queries_not_using_indexes
long_query_time = 10 #default: 10
#log_slow_rate_limit = 1000
#log_slow_verbosity = query_plan
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 = 200 #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_cache_size = 20 #recomended to match real connections initiaded by apps
## 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_size = 16M #global buffer
thread_stack = 192K
thread_cache_size = 8
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_options = 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 = 192M # innodb_log_file_size * innodb_log_files_in_group == innodb_buffer_pool_size/4
innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4
innodb_buffer_pool_size = 3G #global buffer
innodb_buffer_pool_instances = 3 # one instance per 1GB of innodb_buffer_pool_size, for big pool (>8GB) per 2GB
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
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/