MARIADB Tuning Parameters

Lower_case_table_names=1 #不区分大小写skip-name-resolve # Disable DNS Resolution CHARACTER-SET-SERVER=UTF8WAIT_TIMEOUT=1800INTERACTIVE_ Timeout = 288000max_allowed_packet = 1000mmax_connections=3000character-set-server=utf8innodb_buffer_pool_size = 1000minnodb_additional_mem_pool_size = 200minnodb_flush_log_at_trx_commit=2innodb_autoextend_increment= 800minnodb_log_buffer_size = 200m#innodb_log_file_size = 100mkey_buffer_size=800mread_buffer_size=600mthread_cache _size=64

Due to the MARIADB galera cluster cluster that uses the database of the business platform, which is a subset of the tuning parameters, the following is a partial introduction to the database usage scenario

MariaDB galera Cluster Overview:

1. Brief description:

The MariaDB Galera Cluster is a system architecture that implements multi-master and real-time synchronization of data on the MySQL InnoDB storage engine, without the need for read and write separation at the business level, and the database read and write pressures can be distributed to each node in accordance with established rules. Fully compatible with MariaDB and MySQL in terms of data.

2. Features:

(1). Synchronous Replication Synchronous Replication

(2). Active-active multi-master topology Logic

(3). Can read and write data to any node in the cluster

(4). Automatic member control, fault nodes are automatically removed from the cluster

(5). Automatic node Join

(6). True parallel replication, based on row-level

(7). Direct client connection, native MySQL interface

(8). Each node contains a complete copy of the data

(9). Data synchronization in multiple databases is implemented by the Wsrep interface

3. Limitations

(1). The current replication only supports the InnoDB storage engine, and any tables written to other engines, including the mysql.* table, will not be copied, but the DDL statements will be copied, so the created user will be copied, but insert into mysql.user ... will not be copied.

(2). The delete operation does not support tables that do not have a primary key, and tables that do not have a primary key will be different in different node order if a SELECT ... LIMIT ... A different result set appears.

(3). In a multi-master environment Lock/unlock tables is not supported, as well as the lock function Get_lock (), Release_lock () ...

(4). The query log cannot be saved in the table. If the query log is turned on, it can only be saved to a file.

(5). The maximum transaction size allowed is defined by Wsrep_max_ws_rows and wsrep_max_ws_size. Any large operations will be rejected. such as a large load data operation.

(6). Because the cluster is optimistic concurrency control, the transaction commit may be aborted at that stage. If there are two transactions written to and committed to the same row in a different node in the cluster, the failed node is aborted. For cluster-level aborts, the cluster returns a deadlock error code (error:1213 sqlstate:40001 (Er_lock_deadlock)).

(7). XA transactions are not supported due to possible rollback on commit.

(8). The write throughput of the entire cluster is limited by the weakest node, and if one node becomes slow, the entire cluster will be slow. For stable high-performance requirements, all nodes should use unified hardware.

(9). Cluster nodes suggest a minimum of 3.

(10). If there is a problem with the DDL statement, the cluster will be destroyed.

MARIADB Tuning Parameters