떠돌이늑대's tory

binlog_row_metadata

• Description: Controls the format used for binlog metadata logging.
  • NO_LOG: No metadata is logged (default).
  • MINIMAL: Only metadata required by a slave is logged.
  • FULL: All metadata is logged.
• Commandline: --binlog-row-metadata=value
• Scope: Global, Session
• Dynamic: Yes
• Data Type: enum
• Default Value: NO_LOG
• Valid Values: NO_LOG, MINIMAL, FULL
• Introduced: MariaDB 10.5.0

innodb_instant_alter_column_allowed

• Description:
  • If a table is altered using ALGORITHM=INSTANT, it can force the table to use a non-canonical format: A hidden metadata record at the start of the clustered index is used to store each column's DEFAULT value. This makes it possible to add new columns that have default values without rebuilding the table. Starting with MariaDB 10.4, a BLOB in the hidden metadata record is used to store column mappings. This makes it possible to drop or reorder columns without rebuilding the table. This also makes it possible to add columns to any position or drop columns from any position in the table without rebuilding the table. If a column is dropped without rebuilding the table, old records will contain garbage in that column's former position, and new records will be written with NULL values, empty strings, or dummy values.
  • This is generally not a problem. However, there may be cases where you want to avoid putting a table into this format. For example, to ensure that future UPDATE operations after an ADD COLUMN will be performed in-place, to reduce write amplification. (Instantly added columns are essentially always variable-length.) Also avoid bugs similar to MDEV-19916, or to be able to export tables to older versions of the server.
  • This variable has been introduced as a result, with the following values:
  • never (0): Do not allow instant add/drop/reorder, to maintain format compatibility with MariaDB 10.x and MySQL 5.x. If the table (or partition) is not in the canonical format, then any ALTER TABLE (even one that does not involve instant column operations) will force a table rebuild.
  • add_last (1, default in 10.3): Store a hidden metadata record that allows columns to be appended to the table instantly (MDEV-11369). In 10.4 or later, if the table (or partition) is not in this format, then any ALTER TABLE (even one that does not involve column changes) will force a table rebuild.
  • add_drop_reorder (2, default): From MariaDB 10.4 only. Like 'add_last', but allow the metadata record to store a column map, to support instant add/drop/reorder of columns.
• Commandline: --innodb-instant-alter-column-allowed=value
• Scope: Global
• Dynamic: Yes
• Data Type: enum
• Valid Values:
  • <= MariaDB 10.3: never, add_last
  • >= MariaDB 10.4: never, add_last, add_drop_reorder
• Default Value:
  • <= MariaDB 10.3: add_last
  • >= MariaDB 10.4: add_drop_reorder
• Introduced: MariaDB 10.3.23, MariaDB 10.4.13, MariaDB 10.5.3

performance_schema_events_transactions_history_long_size

• Description: Number of rows in events_transactions_history_long table. Use 0 to disable, -1 for automated sizing.
• Commandline: --performance-schema-events-transactions-history-long-size=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: -1
• Range: -1 to 1048576
• Introduced: MariaDB 10.5.2

performance_schema_events_transactions_history_size

• Description:Number of rows per thread in events_transactions_history. Use 0 to disable, -1 for automated sizing.
• Commandline: --performance-schema-events-transactions-history-size=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: -1
• Range: -1 to 1024
• Introduced: MariaDB 10.5.2

performance_schema_max_index_stat

• Description: Maximum number of index statistics for instrumented tables. Use 0 to disable, -1 for automated scaling.
• Commandline: --performance-schema-max-index-stat=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: -1
• Range: -1 to 1048576
• Introduced: MariaDB 10.5.2

performance_schema_max_memory_classes

• Description: Maximum number of memory pool instruments.
• Commandline: --performance-schema-max-memory-classes=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: 320
• Range: 0 to 1024
• Introduced: MariaDB 10.5.2

performance_schema_max_metadata_locks

• Description: Maximum number of metadata locks. Use 0 to disable, -1 for automated scaling.
• Commandline: --performance-schema-max-metadata-locks=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: -1
• Range: -1 to 104857600
• Introduced: MariaDB 10.5.2

performance_schema_max_prepared_statement_instances

• Description: Maximum number of instrumented prepared statements. Use 0 to disable, -1 for automated scaling.
• Commandline: --performance-schema-max-prepared-statement-instances=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: -1
• Range: -1 to 1048576
• Introduced: MariaDB 10.5.2

performance_schema_max_program_instances

• Description: Maximum number of instrumented programs. Use 0 to disable, -1 for automated scaling.
• Commandline: --performance-schema-max-program-instances=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: -1
• Range: -1 to 1048576
• Introduced: MariaDB 10.5.2

performance_schema_max_sql_text_length

• Description: Maximum length of displayed sql text.
• Commandline: --performance-schema-max-sql-text-length=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: 1024
• Range: 0 to 1048576
• Introduced: MariaDB 10.5.2

performance_schema_max_statement_stack

• Description: Number of rows per thread in EVENTS_STATEMENTS_CURRENT.
• Commandline: --performance-schema-max-statement-stack=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: 10
• Range: 1 to 256
• Introduced: MariaDB 10.5.2

performance_schema_max_table_lock_stat

• Description: Maximum number of lock statistics for instrumented tables. Use 0 to disable, -1 for automated scaling.
• Commandline: --performance-schema-max-table-lock-stat=#
• Scope: Global
• Dynamic: No
• Data Type: numeric
• Default Value: -1
• Range: -1 to 1048576
• Introduced: MariaDB 10.5.2

s3_access_key

• Description: The AWS access key to access your data. See mysqld startup options for S3.
• Commandline: --s3-access-key=val
• Scope: Global
• Dynamic: No
• Data Type: String
• Default Value: (Empty)
• Introduced: MariaDB 10.5.4

s3_block_size

• Description: The default block size for a table, if not specified in CREATE TABLE. Set to 4M as default. See mysqld startup options for S3.
• Commandline: --s3-block-size=#
• Scope: Global
• Dynamic: Yes
• Data Type: Numeric
• Default Value: 4194304
• Range: 4194304 to 16777216
• Introduced: MariaDB 10.5.4

s3_bucket

• Description: The AWS bucket where your data should be stored. All MariaDB table data is stored in this bucket. See mysqld startup options for S3.
• Commandline: --s3-bucket=val
• Scope: Global
• Dynamic: No
• Data Type: String
• Default Value: MariaDB
• Introduced: MariaDB 10.5.4

s3_debug

• Description: Generates a trace file from libmarias3 on stderr (mysqld.err) for debugging the S3 protocol.
• Commandline: --s3-debug{=0|1}
• Scope: Global
• Dynamic: No
• Data Type: Boolean
• Valid Values: 0 or 1
• Default Value: 0
• Introduced: MariaDB 10.5.4

s3_host_name

• Description: Hostname for the S3 service. "s3.amazonaws.com", Amazon S3 service, by default
• Commandline: --s3-host-name=val
• Scope: Globa;
• Dynamic: No
• Data Type: String
• Default Value: s3.amazonaws.com
• Introduced: MariaDB 10.5.4

s3_pagecache_age_threshold

• Description: This characterizes the number of hits a hot block has to be untouched until it is considered aged enough to be downgraded to a warm block. This specifies the percentage ratio of that number of hits to the total number of blocks in the page cache.
• Commandline: --s3-pagecache-age-threshold=val
• Scope: Global
• Dynamic: Yes
• Data Type: Numeric
• Default Value: 300
• Range: 100 to 18446744073709551615
• Introduced: MariaDB 10.5.4

s3_pagecache_buffer_size

• Description: The size of the buffer used for index blocks for S3 tables. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford. Size can be adjusted in blocks of 8192.
• Commandline: --s3-pagecache-buffer-size=val
• Scope: Global
• Dynamic: No
• Data Type: Numeric
• Default Value: 134217728 (128M)
• Range: 33554432 to 18446744073709551615
• Introduced: MariaDB 10.5.4

s3_pagecache_division_limit

• Description: The minimum percentage of warm blocks in key cache.
• Commandline: --s3-pagecache-division-limit=val
• Scope: Global
• Dynamic: Yes
• Data Type: Numeric
• Default Value: 100
• Range: 1 to 100
• Introduced: MariaDB 10.5.4

s3_pagecache_file_hash_size

• Description: Number of hash buckets for open files. Default 512. If you have a lot of S3 files open you should increase this for faster flush of changes. A good value is probably 1/10 of number of possible open S3 files.
• Commandline: --s3-pagecache-file-hash-size=#
• Scope: Global
• Dynamic: No
• Data Type: Numeric
• Default Value: 512
• Range: 32 to 16384
• Introduced: MariaDB 10.5.4

s3_protocol_version

• Description: Protocol used to communication with S3. One of "Auto", "Amazon" or "Original" where "Auto" is the default. If you get errors like "8 Access Denied" when you are connecting to another service provider, then try to change this option. The reason for this variable is that Amazon has changed some parts of the S3 protocol since they originally introduced it but other service providers are still using the original protocol.
• Commandline: --s3-protocol-version=val
• Scope: Global
• Dynamic: Yes
• Data Type: Enum
• Valid Values: Auto, Amazon or Original
• Default Value: Auto
• Introduced: MariaDB 10.5.4

s3_region

• Description: The AWS region where your data should be stored. See mysqld startup options for S3.
• Commandline: --s3-region=val
• Scope: Global
• Dynamic: No
• Data Type: String
• Default Value: (Empty)
• Introduced: MariaDB 10.5.4

s3_replicate_alter_as_create_select

• Description: When converting S3 table to local table, log all rows in binary log. This allows the slave to replicate CREATE TABLE .. SELECT FROM s3_table even it the slave doesn't have access to the original s3_table.
• Commandline: --s3-replicate-alter-as-create-select{=0|1}
• Scope: Global
• Dynamic: No
• Data Type: Boolean
• Default Value: 1
• Introduced: MariaDB 10.5.4

s3_secret_key

• Description: The AWS secret key to access your data. See mysqld startup options for S3.
• Commandline: --s3-secret-key=val
• Scope: Global
• Dynamic: No
• Data Type: String
• Default Value: (Empty)
• Introduced: MariaDB 10.5.4

s3_slave_ignore_updates

• Description: Should be set if master and slave share the same S3 instance. This tells the slave that it can ignore any updates to the S3 tables as they are already applied on the master.
• Commandline: --s3-slave-ignore-updates{=0|1}
• Scope: Global
• Dynamic: No
• Data Type: Boolean
• Default Value: 0
• Introduced: MariaDB 10.5.4

sql_if_exists

• Description: If set to 1, adds an implicit IF EXISTS to ALTER, RENAME and DROP of TABLES, VIEWS, FUNCTIONS and PACKAGES. This variable is mainly used in replication to tag DDLs that can be ignored on the slave if the target table doesn't exist.
• Commandline: --sql-if-exists[={0|1}]
• Scope: Global, Session
• Dynamic: Yes
• Data Type: boolean
• Default Value: OFF
• Introduced: MariaDB 10.5.2

thread_pool_dedicated_listener

• Description: If set to 1, then each group will have its own dedicated listener, and the listener thread will not pick up work items. As a result, the queueing time in the Information Schema Threadpool_Queues and the actual queue size in the Information Schema Threadpool_Groups table will be more exact, since IO requests are immediately dequeued from poll, without delay.
  • This system variable is only meaningful on Unix.
• Commandline: thread-pool-dedicated-listener={0|1}
• Scope:
• Dynamic:
• Data Type: boolean
• Default Value: 0
• Introduced: MariaDB 10.5.0

thread_pool_exact_stats

• Description: If set to 1, provides better queueing time statistics by using a high precision timestamp, at a small performance cost, for the time when the connection was added to the queue. This timestamp helps calculate the queuing time shown in the Information Schema Threadpool_Queues table.
  • This system variable is only meaningful on Unix.
• Commandline: thread-pool-exact-stats={0|1}
• Scope:
• Dynamic:
• Data Type: boolean
• Default Value: 0
• Introduced: MariaDB 10.5.0

mysql_secure_installation  error

Create,

Read,

Update,

Delete

https://youtu.be/-UxNUKYh7Vw

 "wsrep_provider_options" with "gmcast.segment= "

https://mariadb.com/kb/en/galera-cluster-status-variables

wsrep_applier_thread_count
설명 :이 유형의 슬레이브 스레드 수를 명확하게하기 위해 현재의 적용자 스레드 수를 저장합니다.

wsrep_apply_oooe
설명 : 병렬화 효율성의 지표 인 쓰기 세트가 순서대로 적용되지 않은 빈도입니다.

wsrep_apply_oool
설명 : 시퀀스 번호가 낮은 쓰기 세트를 시퀀스 번호가 낮은 쓰기 세트보다 먼저 적용하여 쓰기 세트가 느리다는 것을 나타냅니다.

wsrep_apply_window
설명 : 가장 높고 가장 낮은 동시 적용 seqno 사이의 평균 거리.

wsrep_cert_deps_distance
설명 : 병렬로 적용 할 수있는 가장 높은 시퀀스 번호와 가장 낮은 시퀀스 번호 사이의 평균 거리 또는 가능한 병렬화 수준.

....

CPU 코어 1개

[root@localhost ~]# sysbench --mysql-db=cfpc --mysql-host=192.168.1.18 --mysql-port=4006 --mysql-user=maxscale --mysql-password=maxscale123 --max-time=600 --threads=40 /usr/share/sysbench/oltp_insert.lua run
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 40
Initializing random number generator from current time

Initializing worker threads...

Threads started!

SQL statistics:
queries performed:
read: 0
write: 6085278
other: 0
total: 6085278
transactions: 6085278 (10141.57 per sec.)
queries: 6085278 (10141.57 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 600.0326s
total number of events: 6085278

Latency (ms):
min: 0.17
avg: 3.94
max: 662.70
95th percentile: 8.58
sum: 23990186.08

Threads fairness:
events (avg/stddev): 152131.9500/533.58
execution time (avg/stddev): 599.7547/0.01

CPU 코어 4개

[root@localhost ~]# sysbench --mysql-db=cfpc --mysql-host=192.168.1.18 --mysql-port=4006 --mysql-user=maxscale --mysql-password=maxscale123 --max-time=600 --threads=40 /usr/share/sysbench/oltp_insert.lua run
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 40
Initializing random number generator from current time

Initializing worker threads...

Threads started!

SQL statistics:
queries performed:
read: 0
write: 18718049
other: 0
total: 18718049
transactions: 18718049 (31195.96 per sec.)
queries: 18718049 (31195.96 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 600.0144s
total number of events: 18718049

Latency (ms):
min: 0.15
avg: 1.28
max: 426.97
95th percentile: 3.02
sum: 23967305.01

Threads fairness:
events (avg/stddev): 467951.2250/18031.93
execution time (avg/stddev): 599.1826/0.03

GTID  Replication 사용 필수 

switch-over

fail-over

re-join

maxscale.cnf

MariaDB 버전변경하면서 테스트를 하던 중 Galera Cluster를 새로 구성하면서, 다른 2 node 들이 위와 같은 에러가

발생하면서 DB 구동이 실패가 되었었다.

무슨 문제였는지  알수가 없었다.

wsrep_sst_method 옵션을 변경해도 error 가 발생되면서 DB 구동이 실패가 되었었다.

첫번째 노드의 data가 들어있는 디렉토리를 살펴보면,

innobackup.backup.log 라는 파일이 있다.

다른 노드에서 sst를 요청하면 과정과 결과가 기록이 된다.

이러한 기록들이 있었다.

저 테이블에 문제가 있었나보다.

실제로 첫번째 node 의 데이터를 mysqldump 로 백업하려했더니 저 테이블때문에 계속 실패했다. 다른 한 테이블도

그래서 제외시켜서 백업했더니 잘 되었었다.

결국 첫번째 노드에서 문제있었던 두 테이블을 삭제하니

Galera Cluster도 정상으로 되었다.

2개의 Galera Cluster를 만들고, 각 클러스터의 한 노드끼리 서로간의 Replication 설정을 했다.

그러나 왠걸 Replication  설정을 한 노드들끼리의 복제는 되지만, 다른 노드에서 실행하면 클러스터 내부에서만 복제가 되고, 다른 클러스터로는 복제가 되질 않았다.

무엇일까... 찾아보게 되었고,

서로 Replication을 맺어준 노드에서 my.cnf 에

이 옵션값을 선언해 주어야지만 다른 노드에서의 명령도 전달복제가 되었다.

log_slave_updates 의 값을 선언을 해주어야 한다.

log_slave_updates

• Description: If set to 0, the default, updates on a slave received from a master during replication are not logged in the slave's binary log. If set to 1, they are. The slave's binary log needs to be enabled for this to have an effect. Set to 1 if you want to daisy-chain the slaves.
• Commandline: --log-slave-updates
• Scope: Global
• Dynamic: No
• Data Type: boolean
• Default Value: OFF