[mysql] # Added on 04/24/2016 prompt = '[NODE2] > ' default-character-set = 'utf8' auto-rehash = FALSE local-infile = 1 max-allowed-packet = 256M secure-auth = TRUE # Done adding on 04/24/2016 [client] user ="root" password = "root12" port = 3340 socket = /mysql/prod_ec_cms2/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3340 datadir = /mysql_data/prod_ec_cms2/data socket = /mysql/prod_ec_cms2/mysql.sock tmpdir = /mysql_data/prod_ec_cms2/temp pid-file = /mysql/prod_ec_cms2/mysqld.pid server-id=30112 event_scheduler = 1 ft_min_word_len = 1 innodb_ft_min_token_size = 1 skip-external-locking # Added on 04/27/2016 max_allowed_packet = 256M max-connect-errors = 100000 optimizer_search_depth = 0 optimizer_switch = 'index_merge=on' optimizer_switch = 'index_merge_union=on' optimizer_switch = 'index_merge_sort_union=on' optimizer_switch = 'index_merge_intersection=on' optimizer_switch = 'index_merge_sort_intersection=off' optimizer_switch = 'index_condition_pushdown=on' optimizer_switch = 'derived_merge=on' optimizer_switch = 'derived_with_keys=on' optimizer_switch = 'firstmatch=on' optimizer_switch = 'loosescan=on' optimizer_switch = 'materialization=on' optimizer_switch = 'in_to_exists=on' optimizer_switch = 'semijoin=on' optimizer_switch = 'partial_match_rowid_merge=on' optimizer_switch = 'partial_match_table_scan=on' optimizer_switch = 'subquery_cache=on,mrr=on' optimizer_switch = 'mrr_cost_based=on' optimizer_switch = 'mrr_sort_keys=off' optimizer_switch = 'outer_join_with_cache=on' optimizer_switch = 'semijoin_with_cache=on' optimizer_switch = 'join_cache_incremental=on' optimizer_switch = 'join_cache_hashed=on' optimizer_switch = 'join_cache_bka=on' optimizer_switch = 'optimize_join_buffer_size=on' optimizer_switch = 'table_elimination=on' optimizer_switch = 'extended_keys=on' join-buffer-space-limit = 10M join-cache-level = 6 join-buffer-size = 10M local-infile = 1 innodb-stats-sample-pages = 32 table-definition-cache = 3500 transaction-isolation = READ-COMMITTED innodb-support-xa = ON #Done adding on 04/24/2016 key_buffer_size = 512M max_heap_table_size = 512M skip_name_resolve memlock=0 sysdate_is_now=1 max_connections= 2000 table_open_cache = 3600 explicit_defaults_for_timestamp=1 sort_buffer_size = 2M # Changed from 10M net_buffer_length = 8K read_buffer_size = 128k # decreased from 60M read_rnd_buffer_size = 256k # decreased from 4M thread_cache_size = 5000 query_cache_type = 0 query_cache_size = 0 innodb_open_files = 4000 innodb_adaptive_hash_index = 0 open_files_limit = 65535 # Updated on 04/24/2016 max_user_connections=1800 back_log=2000 wait_timeout=600 concurrent_insert=2 innodb_change_buffer_max_size = 50 thread_pool_size = 36 innodb_flush_neighbors = 0 innodb_lru_scan_depth = 5000 innodb_adaptive_flushing_lwm = 20 innodb_use_native_aio = 1 character-set-server=utf8 init_connect='SET collation_connection = utf8_unicode_ci' init-connect='SET NAMES utf8' collation-server =utf8_unicode_ci skip-character-set-client-handshake # error Log log-error = /mysql_log/prod_ec_cms2/mariadb_logs/error-log.err log-warnings = 2 # Slow query Logging slow-query-log=1 slow-query-log-file = /mysql_log/prod_ec_cms2/mariadb_logs/slow-query.log long-query-time=0.25 log-queries-not-using-indexes = 0 log-slow-admin-statements = 1 log-slow-verbosity = 'query_plan,innodb' # added on 04/24/2016 # General query Logging general-log=0 general-log-file = /mysql_log/prod_ec_cms2/mariadb_logs/general-log # Binary Logging ##gtid_mode = ON log-bin = /mysql_log/prod_ec_cms2/binlogs/ec-cms2-mdb-bin log-slave-updates = 1 enforce_gtid_consistency = 1 expire_logs_days = 7 max-binlog-size = 1024M gtid-domain-id=53 replicate-same-server-id = 0 gtid_ignore_duplicates = 1 # relay log relay_log = /mysql_log/prod_ec_cms2/relaylogs/ec-cms2-mdb-relay.log innodb_data_home_dir = /mysql_data/prod_ec_cms2/data innodb_data_file_path = ibdata1:12M;ibdata2:20M:autoextend innodb_file_per_table = 1 innodb_log_group_home_dir = /mysql_log/prod_ec_cms2/logs innodb_lock_wait_timeout = 50 innodb_buffer_pool_instances= 20 innodb_log_files_in_group=2 innodb_buffer_pool_size = 30G innodb_flush_log_at_trx_commit=2 innodb_file_format= BARRACUDA innodb_log_buffer_size = 2G innodb_log_file_size = 2G innodb_purge_batch_size = 5000 innodb_purge_threads= 32 innodb_commit_concurrency = 20 innodb_max_dirty_pages_pct = 95 table_open_cache_instances = 16 innodb_random_read_ahead = 1 innodb_read_io_threads= 64 innodb_thread_concurrency = 0 innodb_rollback_on_timeout= ON innodb_write_io_threads= 64 innodb_io_capacity = 5000 innodb_io_capacity_max = 10000 innodb_doublewrite = 0 innodb_flush_method = O_DIRECT #innodb_log_block_size = 4096 thread-handling=pool-of-threads innodb_stats_on_metadata=0 default_storage_engine=innodb skip-slave-start = 1 #Cluster Specific Configurations wsrep_on=ON binlog_format=ROW binlog_row_image = minimal bind-address=0.0.0.0 innodb_autoinc_lock_mode=2 innodb_force_primary_key=0 wsrep_provider=/usr/lib64/galera/libgalera_smm.so # Defines the path to the Galera Replication Plugin wsrep_provider_options="gcache.size=8192M; evs.keepalive_period=PT3S; evs.inactive_check_period=PT10S; evs.suspect_timeout=PT30S; evs.inactive_timeout=PT1M; evs.install_timeout=PT1M; evs.send_window=1024; evs.user_send_window=512;" wsrep_cluster_name="CMS_EC" wsrep_cluster_address="gcomm://107.304.125.11,107.304.125.12,107.304.125.13" wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth = "root:root12" wsrep_node_name=cms_ec_node2 wsrep_node_address="107.304.125.12" wsrep_slave_threads=48 wsrep_certify_nonPK=1 # Generate wsrep_convert_LOCK_to_trx=0 # convert locking sessions into transactions wsrep_retry_autocommit=2 # how many times to retry deadlocked autocommits # Handling Auto increments in CLuster wsrep_auto_increment_control = 0 # change auto_increment_increment and auto_increment_offset automatically. wsrep_causal_reads=0 # enable "strictly synchronous" semantics for read operations. If enables, you might encounter auto_increment_increment=1 # Max we will be having only 1 node writing into the cluster. auto_increment_offset=1 #wsrep_sst_donor = cms_ec_node5 wsrep_max_ws_rows=1048576 wsrep_max_ws_size=4294901759 wsrep_debug=0 #wsrep_restart_slave = 0 tmp_table_size = 32M join_buffer_size = 128k table_definition_cache = 670 wsrep_log_conflicts = 1 innodb_print_all_deadlocks = 1 binlog_cache_size = 1M binlog_stmt_cache_size = 1M [mysqldump] quick max_allowed_packet = 1G default-character-set = 'utf8' [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 1G sort_buffer_size = 1G read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout