조인 마리애드브 제한 수

조인 마리애드브 제한 수

저는 Mariadb에 대한 요청이 있어서 문제가 있습니다.이 요청은 MySQL에서 진행하는 데 문제가 없었습니다. (우리는 방금 mariadb로 마이그레이션했지만 요청에 전혀 응답하지 않습니다.)

여기 있습니다.

SELECT DISTINCT art.id,

    FROM tracker_artifact art

    LEFT JOIN tracker_changeset
    ON art.id = tracker_changeset.artifact_id AND tracker_changeset.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
    INNER JOIN tracker_changeset_value
ON tracker_changeset.id = tracker_changeset_value.changeset_id
INNER JOIN tracker_changeset_value_list
ON tracker_changeset_value.id = tracker_changeset_value_list.changeset_value_id AND tracker_changeset_value.field_id IN (3826, 1280, 608, 1819)
INNER JOIN `user` usr1
ON tracker_changeset_value_list.bindvalue_id = usr1.user_id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset2
ON art.id = tracker_changeset2.artifact_id AND tracker_changeset2.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value2
ON tracker_changeset2.id = tracker_changeset_value2.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list2
ON tracker_changeset_value2.id = tracker_changeset_value_list2.changeset_value_id AND tracker_changeset_value2.field_id IN (3828, 1283, 618, 1822)
INNER JOIN tracker_field_list_bind_static_value
ON tracker_changeset_value_list2.bindvalue_id = tracker_field_list_bind_static_value.id) `state` ON `state`.id = art.id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value2.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset3
ON art.id = tracker_changeset3.artifact_id AND tracker_changeset3.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value3
ON tracker_changeset3.id = tracker_changeset_value3.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list3
ON tracker_changeset_value3.id = tracker_changeset_value_list3.changeset_value_id AND tracker_changeset_value3.field_id IN (3831, 1286, 714, 1825)
INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value2
ON tracker_changeset_value_list3.bindvalue_id = tracker_field_list_bind_static_value2.id) resolution ON resolution.id = art.id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value4.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset5
ON art.id = tracker_changeset5.artifact_id AND tracker_changeset5.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value5
ON tracker_changeset5.id = tracker_changeset_value5.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list5
ON tracker_changeset_value5.id = tracker_changeset_value_list5.changeset_value_id AND tracker_changeset_value5.field_id IN (3861, 1300, 875, 1840)
INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value4
ON tracker_changeset_value_list5.bindvalue_id = tracker_field_list_bind_static_value4.id) category ON category.id = art.id

LEFT JOIN
(SELECT tracker_changeset_value_text.value, art.id from tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset6
ON art.id = tracker_changeset6.artifact_id AND tracker_changeset6.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value6
ON tracker_changeset6.id = tracker_changeset_value6.changeset_id
INNER JOIN tracker_changeset_value_text ON tracker_changeset_value6.id = tracker_changeset_value_text.changeset_value_id AND tracker_changeset_value6.field_id IN (3797, 1293, 613, 1833)
) summary ON summary.id = art.id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value6.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list7
ON tracker_changeset_value7.id = tracker_changeset_value_list7.changeset_value_id AND tracker_changeset_value7.field_id IN (3811, 1304, 626, 1844)
INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value6
ON tracker_changeset_value_list7.bindvalue_id = tracker_field_list_bind_static_value6.id) priority ON priority.id = art.id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value6.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list7
ON tracker_changeset_value7.id = tracker_changeset_value_list7.changeset_value_id AND tracker_changeset_value7.field_id IN (3810, 1302, 621, 1842)
INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value6
ON tracker_changeset_value_list7.bindvalue_id = tracker_field_list_bind_static_value6.id) `type` ON `type`.id = art.id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value6.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list7
ON tracker_changeset_value7.id = tracker_changeset_value_list7.changeset_value_id AND tracker_changeset_value7.field_id IN (3846, 2780, 2770, 2771)
INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value6
ON tracker_changeset_value_list7.bindvalue_id = tracker_field_list_bind_static_value6.id) CCBSubmission ON CCBSubmission.id = art.id

LEFT JOIN
(SELECT tracker_changeset_value_date.`value`, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_date
ON tracker_changeset_value_date.changeset_value_id = tracker_changeset_value7.id AND tracker_changeset_value7.field_id IN (3843, 1317, 724,1857)) CCBdate ON CCBdate.id = art.id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value6.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list7
ON tracker_changeset_value7.id = tracker_changeset_value_list7.changeset_value_id AND tracker_changeset_value7.field_id IN (3847, 1324,730,1864)
INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value6
ON tracker_changeset_value_list7.bindvalue_id = tracker_field_list_bind_static_value6.id) CCBDecision ON CCBDecision.id = art.id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value6.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list7
ON tracker_changeset_value7.id = tracker_changeset_value_list7.changeset_value_id AND tracker_changeset_value7.field_id IN (3848, 1325, 731, 1865)
INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value6
ON tracker_changeset_value_list7.bindvalue_id = tracker_field_list_bind_static_value6.id) ReasonDecision ON ReasonDecision.id = art.id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value6.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list7
ON tracker_changeset_value7.id = tracker_changeset_value_list7.changeset_value_id AND tracker_changeset_value7.field_id IN (3827,1281, 740, 1820)
INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value6
ON tracker_changeset_value_list7.bindvalue_id = tracker_field_list_bind_static_value6.id) TargetReleaseVersion ON TargetReleaseVersion.id = art.id

LEFT JOIN
(SELECT tracker_field_list_bind_static_value6.label, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list7
ON tracker_changeset_value7.id = tracker_changeset_value_list7.changeset_value_id AND tracker_changeset_value7.field_id IN (3832, 1284, 751, 1826)
INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value6
ON tracker_changeset_value_list7.bindvalue_id = tracker_field_list_bind_static_value6.id) PackagedReleaseVersion ON PackagedReleaseVersion.id = art.id


LEFT JOIN
(SELECT art.id, tracker_changeset.submitted_on AS "LastModifiedOn"
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset
ON art.id = tracker_changeset.artifact_id AND tracker_changeset.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
) lastmodif ON lastmodif.id = art.id

LEFT JOIN
(SELECT art.id, 
tracker_changeset.submitted_on AS "lastnewdate"
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset ON art.id = tracker_changeset.artifact_id 
AND tracker_changeset.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset 
                                       INNER JOIN tracker_changeset_value 
                                       ON tracker_changeset.id = tracker_changeset_value.changeset_id
                                       INNER JOIN tracker_changeset_value_list 
                                       ON tracker_changeset_value.id = tracker_changeset_value_list.changeset_value_id AND tracker_changeset_value.field_id IN (3828, 1283, 618, 1822)
                                       INNER JOIN tracker_field_list_bind_static_value
                                       ON tracker_changeset_value_list.bindvalue_id = tracker_field_list_bind_static_value.id
                                      WHERE artifact_id = art.id AND tracker_field_list_bind_static_value.label = 'New'
                                      AND tracker_changeset_value.has_changed = 1)
) lastnewdate ON lastnewdate.id = art.id

LEFT JOIN
(SELECT art.id, 
tracker_changeset.submitted_on AS "lastanalyseddate"
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset ON art.id = tracker_changeset.artifact_id 
AND tracker_changeset.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset 
                                       INNER JOIN tracker_changeset_value 
                                       ON tracker_changeset.id = tracker_changeset_value.changeset_id
                                       INNER JOIN tracker_changeset_value_list 
                                       ON tracker_changeset_value.id = tracker_changeset_value_list.changeset_value_id AND tracker_changeset_value.field_id IN (3828, 1283, 618, 1822)
                                       INNER JOIN tracker_field_list_bind_static_value
                                       ON tracker_changeset_value_list.bindvalue_id = tracker_field_list_bind_static_value.id
                                      WHERE artifact_id = art.id AND tracker_field_list_bind_static_value.label = 'Analysed'
                                      AND tracker_changeset_value.has_changed = 1)
) lastanalyseddate ON lastanalyseddate.id = art.id

LEFT JOIN
(SELECT art.id, 
tracker_changeset.submitted_on AS "LastTobeestimated"
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset ON art.id = tracker_changeset.artifact_id 
AND tracker_changeset.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset 
                                       INNER JOIN tracker_changeset_value 
                                       ON tracker_changeset.id = tracker_changeset_value.changeset_id
                                       INNER JOIN tracker_changeset_value_list 
                                       ON tracker_changeset_value.id = tracker_changeset_value_list.changeset_value_id AND tracker_changeset_value.field_id IN (3828, 1283, 618, 1822)
                                       INNER JOIN tracker_field_list_bind_static_value
                                       ON tracker_changeset_value_list.bindvalue_id = tracker_field_list_bind_static_value.id
                                      WHERE artifact_id = art.id AND tracker_field_list_bind_static_value.label = 'To be estimated'
                                      AND tracker_changeset_value.has_changed = 1)
) LastTobeestimated ON LastTobeestimated.id = art.id

LEFT JOIN
(SELECT art.id, 
tracker_changeset.submitted_on AS "LastEstimated"
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset ON art.id = tracker_changeset.artifact_id 
AND tracker_changeset.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset 
                                       INNER JOIN tracker_changeset_value 
                                       ON tracker_changeset.id = tracker_changeset_value.changeset_id
                                       INNER JOIN tracker_changeset_value_list 
                                       ON tracker_changeset_value.id = tracker_changeset_value_list.changeset_value_id AND tracker_changeset_value.field_id IN (3828, 1283, 618, 1822)
                                       INNER JOIN tracker_field_list_bind_static_value
                                       ON tracker_changeset_value_list.bindvalue_id = tracker_field_list_bind_static_value.id
                                      WHERE artifact_id = art.id AND tracker_field_list_bind_static_value.label = 'Estimated' AND tracker_changeset_value.has_changed = 1)
) LastEstimated ON LastEstimated.id = art.id

LEFT JOIN
(SELECT tracker_changeset_value_float.`value`, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_float
ON tracker_changeset_value7.id = tracker_changeset_value_float.changeset_value_id AND tracker_changeset_value7.field_id IN (3849, 2940, 2772, 2782)
-- INNER JOIN tracker_field_list_bind_static_value tracker_field_list_bind_static_value6
-- ON tracker_changeset_value_list7.bindvalue_id = tracker_field_list_bind_static_value6.id
) mandays ON mandays.id = art.id

LEFT JOIN
(SELECT tracker_changeset_value_int.`value`, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_int
ON tracker_changeset_value7.id = tracker_changeset_value_int.changeset_value_id AND tracker_changeset_value7.field_id IN (738,1332,1872,3851)
) quotationcost ON quotationcost.id = art.id

LEFT JOIN
(SELECT ugroup.name, art.id
FROM 
tracker_artifact art
LEFT JOIN tracker_changeset tracker_changeset7
ON art.id = tracker_changeset7.artifact_id AND tracker_changeset7.submitted_on = (SELECT MAX(submitted_on) FROM tracker_changeset WHERE artifact_id = art.id)
INNER JOIN tracker_changeset_value tracker_changeset_value7
ON tracker_changeset7.id = tracker_changeset_value7.changeset_id
INNER JOIN tracker_changeset_value_list tracker_changeset_value_list7
ON tracker_changeset_value7.id = tracker_changeset_value_list7.changeset_value_id AND tracker_changeset_value7.field_id = 4031

INNER JOIN tracker_field_list_bind_ugroups_value
ON tracker_changeset_value_list7.bindvalue_id = tracker_field_list_bind_ugroups_value.id
LEFT JOIN ugroup ON ugroup.ugroup_id = tracker_field_list_bind_ugroups_value.ugroup_id) SubmitterGroup ON SubmitterGroup.id = art.id

LEFT JOIN tracker ON tracker.id = art.tracker_id
WHERE art.tracker_id IN (84, 33, 19 ,45)
GROUP BY art.id

단순화하기 위해 선택한 부분을 모두 제거했지만, 이 요청으로 몇 분 후 2013년 "연결 끊김" 오류가 발생했습니다.왼쪽 조인을 모두 제거하고 하나씩 추가하려고 했습니다.제가 몇 개만 있으면 괜찮습니다.하지만 그 모든 것들로는 불가능해요Join_buffer_size 매개 변수, net_read_timeout 등을 변경했습니다.하지만 아무것도 고쳐주지 않았습니다.

내가 뭘 할 수 있는지 아는 사람?

다음은 내 구성입니다.

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
# Same as MySQL on Mockup server M01
query_cache_size = 0
max_write_lock_count = 0
max_seeks_for_key = 0
max_relay_log_size = 0
#
max_allowed_packet=128M
# By default innodb engine use one file for all databases and tables. We recommend changing this to one file per table.
# NOTE: This will take effect only if Artifactory tables are not created yet! Need to be set and MySQL restarted before starting Artifactory for the first time.
innodb_file_per_table
performance_schema = ON
# Update from 512 to 1024
max_connections = 1024


#port           = 3306
#socket         = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
bulk_insert_buffer_size = 64M
# back_log is the number of connections the operating system can keep in
# the listen queue, before the MariaDB connection manager thread has
# processed them. If you have a very high connection rate and experience
# "connection refused" errors, you might need to increase this value.
# Check your OS documentation for the maximum value of this parameter.
# Attempting to set back_log higher than your operating system limit
# will have no effect.
back_log = 50

open-files-limit = 4096
table_open_cache = 1024
# Maximum allowed size for a single HEAP (in memory) table. This option
# is a protection against the accidential creation of a very large HEAP
# table which could otherwise use up all memory resources.
max_heap_table_size = 512M

sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
# This buffer is used for the optimization of full JOINs (JOINs without
# indexes). Such JOINs are very bad for performance in most cases
# anyway, but setting this variable to a large value reduces the
# performance impact. See the "Select_full_join" status variable for a
# count of full JOINs. Allocated per thread if full join is found
#join_buffer_size = 8M

thread_cache_size = 8
# Thread stack size to use. This amount of memory is always reserved at
# connection time. MariaDB itself usually needs no more than 64K of
# memory, while if you use your own stack hungry UDF functions or your
# OS requires more stack for some operations, you might need to set this
# to a higher value.
thread_stack = 240K

# Set the default transaction isolation level. Levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size = 512M

#query_cache_size = 64M
# Only cache result sets that are smaller than this limit. This is to
# protect the query cache of a very large result set overwriting all
# other query results.
query_cache_limit = 2M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

innodb_thread_concurrency = 16
innodb_buffer_pool_size = 10G
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 32M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90

innodb_flush_log_at_trx_commit=0

# Enable the full query log. Every query (even ones with incorrect
# syntax) that the server receives will be logged. This is useful for
# debugging, it is usually disabled in production use.
#log

# Print warnings to the error log file.  If you have any problem with
# MariaDB you should enable logging of warnings and examine the error log
# for possible explanations.
#log_warnings

# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
# indexes well, if log_short_format is not enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
slow_query_log

# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
# even very fast queries being logged from time to time (as MariaDB
# currently measures time with second accuracy only).
long_query_time = 5

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
max_binlog_size = 512M
expire_logs_days = 3
# The size of the cache to hold the SQL statements for the binary log
# during a transaction. If you often use big, multi-statement
# transactions you can increase this value to get more performance. All
# statements from transactions are buffered in the binary log cache and
# are being written to the binary log at once after the COMMIT.  If the
# transaction is larger than this value, temporary file on disk is used
# instead.  This buffer is allocated per connection on first update
# statement in transaction
binlog_cache_size = 1M

# (This must be substituted by wsrep_format)
binlog_format=ROW

# Currently only InnoDB storage engine is supported
default-storage-engine=innodb

# to avoid issues with 'bulk mode inserts' using autoinc
innodb_autoinc_lock_mode=2

# Override bind-address
# In some systems bind-address defaults to 127.0.0.1, and with mysqldump SST
# it will have (most likely) disastrous consequences on donor node
bind-address=0.0.0.0

# 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 = 8192

#
# * Galera-related settings
#
[galera]
wsrep_on=ON

##
## WSREP options
##

# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

# Provider specific configuration options
wsrep_provider_options="gcache.size=512M"

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="MyCluster"

# Group communication system handle
wsrep_cluster_address=gcomm://node1,node2

# Human-readable node name (non-unique). Hostname by default.
wsrep_node_name=myhostame.mycompany.corp

# Base replication <address|hostname>[:port] of the node.
# The values supplied will be used as defaults for state transfer receiving,
# listening ports and so on. Default: address of the first network interface.
#wsrep_node_address=

# Address for incoming client connections. Autodetect by default.
#wsrep_node_incoming_address=

# How many threads will process writesets from other nodes
wsrep_slave_threads=2

# Whether or not DML updates for MyISAM tables will be replicated. This functionality is still experimental and should not be relied upon in production systems.
wsrep_replicate_myisam=1

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1

# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status  - new status of this node
# --uuid    - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index   - index of this node in the list
wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
wsrep_sst_method=rsync

# Address which donor should send State Snapshot to.
# Should be the address of THIS node. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
#wsrep_sst_receive_address=

# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password>
wsrep_sst_auth=root:

# Desired SST donor name.
#wsrep_sst_donor=

# Reject client queries when donating SST (false)
#wsrep_sst_donor_rejects_queries=0

# Protocol version to use
# wsrep_protocol_version=


# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]

[mysqldump]
#quick
max_allowed_packet = 1G

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[mysqlhotcopy]
interactive-timeout

서버: Red Hat Enterprise 리눅스 서버 릴리스 6.8(산티아고)

감사해요!

언급URL : https://stackoverflow.com/questions/39289133/limit-number-of-join-mariadb