-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmy.cnf
204 lines (188 loc) · 5.37 KB
/
my.cnf
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
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open_files_limit= 60000
pid-file=/opt/maria10.1/data/mysqld.pid
numa_interleave
# The following options will be passed to all MySQL clients
[client]
#user = jnxdba
#password = jnx0406
port = 3309
socket = /opt/maria10.1/data/mysql.sock
#default-character-set=utf8mb4
# Here follows entries for some specific programs
[mysqldump]
quick
max_allowed_packet = 128M
default-character-set = utf8mb4
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
prompt=\\R:\\m:\\s (\\u@\\h) [\\d]>\\_
default-character-set=utf8mb4
# The MySQL server
[mysqld]
#connection related
bind-address=0.0.0.0
max_connections = 30000
max_user_connections=29000
max_connect_errors=65536
max_allowed_packet=128M
#max_connect_errors = 10
connect_timeout=20
net_read_timeout=30
net_write_timeout=60
back_log=65535
port=3309
#skip-name-resolve
skip-ssl
#dir
basedir=/opt/maria10.1
lc-messages-dir=/opt/maria10.1/share
lc-messages=en_US
datadir=/opt/maria10.1/data/db
tmpdir=/opt/maria10.1/data/tmp
log-error=/opt/maria10.1/log/alert_TMTRND01.log
socket=/opt/maria10.1/data/mysql.sock
#log-queries-not-using-indexes
#log_error_verbosity=3
general_log=0
#general_log_file=/TMTRND01/datadg/log/general.log
log_warnings=1
#slow log
slow_query_log= 1
log_output='FILE'
long_query_time=5
log_slow_admin_statements=0
log_slow_rate_limit=1
slow_query_log_file=/opt/maria10.1/log/slow.log
#Server level
default-storage-engine=innodb
lower_case_table_names=1
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
open_files_limit=60000
table_definition_cache=65536
optimizer_switch="index_merge_sort_intersection=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,optimize_join_buffer_size=on"
join_cache_level=8
join_buffer_size=4194304
join_buffer_space_limit=262144000
mrr_buffer_size=4194304
sort_buffer_size = 250M
#join_buffer_size = 250M
net_buffer_length = 2K
max_heap_table_size = 128M
#query_cache_size = 64M
query_cache_size=0
query_cache_type=0
#query_cache_limit = 8M
#query_cache_min_res_unit=1M
thread_cache_size = 8
thread_stack = 192K
ft_min_word_len = 4
tmp_table_size = 500M
local-infile=1
#thread pool
thread_handling=pool-of-threads
thread_pool_max_threads=30000
#thread_pool_siz=16
#enable performance monitoring
performance_schema=1
#binlog
log-bin=/TMTRND01/binlog/TMTRND01
binlog_cache_size = 200M
max_binlog_cache_size=1G
max_binlog_size=128M
binlog_format=ROW
sync_binlog=0
log-slave-updates=1
expire_logs_days=2
binlog_row_image=minimal
gtid_domain_id=2
gtid_strict_mode=1
server_id=1
#*** InnoDB Specific options
##innodb_data_home_dir=/TMTRND01/datadg/db/ibdata
##innodb_log_group_home_dir=/TMTRND01/datadg/db/redo
##innodb_data_file_path=ibdata1:100M;ibdata2:16M:autoextend
innodb_autoinc_lock_mode=2
innodb_buffer_pool_size=20G
innodb_buffer_pool_instances=2
innodb_log_files_in_group=2
innodb_log_file_size=2G
innodb_log_buffer_size=200M
innodb_flush_log_at_trx_commit=0
innodb_max_dirty_pages_pct=60
innodb_io_capacity=1000
innodb_flush_neighbors=1
innodb_stats_on_metadata=0
#innodb_thread_concurrency=8
innodb_read_io_threads=14
innodb_write_io_threads=14
innodb_open_files=60000
#innodb_adaptive_hash_index_partitions=4
innodb_file_per_table=1
#innodb_flush_method=O_DIRECT
##innodb_flush_method=O_DSYNC
innodb_change_buffering=all
innodb_adaptive_flushing=1
innodb_old_blocks_time=1000
innodb_use_native_aio=1
innodb_lock_wait_timeout=50
innodb_rollback_on_timeout=1
innodb_purge_threads=1
##innodb_strict_mode=1
transaction-isolation=READ-COMMITTED
innodb_autoextend_increment=10
#innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
innodb_large_prefix=1
#innodb_file_format=Barracuda
innodb_print_all_deadlocks=1
#mode
innodb_strict_mode=1
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=1
#galera options
wsrep_on=ON # this is important for 10.1!
wsrep_provider=/opt/maria10.1/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=10G; gcache.page_size=100M; gmcast.listen_addr=tcp://172.137.254.21:4567;gcs.fc_limit=256;gcs.fc_factor=0.9;"
#wsrep_cluster_address=gcomm://
wsrep_cluster_address="gcomm://172.137.254.21,172.137.254.22,172.137.254.23"
wsrep_cluster_name="bitsx_cluster"
wsrep_node_name=m5-rnd-db001.jp.sbibits.com
wsrep_node_address="172.137.254.21"
wsrep_slave_threads=1
wsrep_certify_nonPK=1
wsrep_max_ws_rows=0
wsrep_max_ws_size=2147483647
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
#wsrep_notify_cmd=/opt/maria10.1/bin/my_wsrep_notify
wsrep_notify_cmd=
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=bkpuser:bkpuser
wsrep_gtid_mode=1
wsrep_gtid_domain_id=1
wsrep_sync_wait=1
#audit plugin
plugin-load=server_audit=server_audit.so
server_audit_logging=1
server_audit=FORCE_PLUS_PERMANENT
server_audit_file_path=/opt/maria10.1/log/server_audit.log
server_audit_events='CONNECT,QUERY'
server_audit_file_rotate_size=524288000
server_audit_file_rotations=9
server_audit_output_type='file'
server_audit_excl_users='event_scheduler'
[xtrabackup]
user=bkpuser
password=bkpuser