###(24c.120r)

#####################################################################
####################### NASTAVENI DAEMONA ###########################
#####################################################################

[mysqld]

#####################################################################
# Kde se mysql nachazi na disku:
#####################################################################

# kde jsou data
datadir                     = /www/mysql/data
# kde je nainstalovana
basedir                     = /usr
# kde ma tvaret temp soubory
tmpdir                      = /tmp
# cesta k jazykovym souborum
lc-messages-dir             = /usr/share/mysql

#####################################################################
# Kde a jak je mysql spustena
#####################################################################

# descriptor
pid-file                    = /www/mysql/mysqld.pid
# socket
#socket                      = /www/mysql/mysqld.sock
# port na kterem bezi
port                        = 3306
# systemovy uzivatel pod kterym bezi
user                        = mysql

character-set-server        = utf8mb4
collation-server            = utf8mb4_unicode_ci

#####################################################################
# Nastaveni logovani
#####################################################################
# errory
log_error                   = /www/mysql/mysql.log

# Logujeme pouze Errory
log_warnings                = 1

## Dotazy trvajici dele nez write_time se zapisou cele
# povoleni
slow_query_log              = 1
# kam
slow_query_log_file         = /www/mysql/mysql-slow.log
# dotazi trvajici dele nez cislo v sekundach
long_query_time = 5
# ukecanost
log_slow_verbosity          = Query_plan
# Logujeme jen critical warnings
log_warnings                = 1

#####################################################################
# Nastaveni limitu pro dotazy a cache
#####################################################################

# Maxmialni pocet naraz pripojenych klientu na mysql
max_connections             = 2000
# Maximum po sobe jdoucich erroru nez se mysql lockne pro dalsi dotazy (unlock FLUSH HOSTS)
max_connect_errors          = 999999999

#buffer pro sorteni joinovani per thread (ORDER BY or GROUP BY)
sort_buffer_size            = 8M
join_buffer_size            = 8M

#buffer pro nacitani dat per thread (sekvencni scany)
read_buffer_size            = 8M

# Maximalni velikost packetu
max_allowed_packet          = 1024M
# Velikost zasobniku pro thread 192K pro 32bit a 256K pro 64bit
thread_stack                = 256K
# Pocet poslednich threadu v cachi pro pripadne vraceni bez dotazu na mysql
thread_cache_size           = 256

# cache musi byt vypnuta, velmi zpozduje, nema dobre vyresene konkurencni pristup
query_cache_type            = 0

#####################################################################
# Nastaveni enginu (zpusobu ukladani dat a zpusobu prace snimi)
# InnoDB/RockDB/MyISAM/MariaDB...
#####################################################################

# Nastaveni na konkretni engin
default_storage_engine      = InnoDB

# Striktni mod, vraci errory na warningy a syntax errory, dohlizi na konflikty pri create table/alter table/create index atd, defaultne 0 (OFF)
innodb_strict_mode          = 0

# Umisteni innodb souboru a logu
innodb_data_home_dir        = /www/mysql/data
innodb_log_group_home_dir   = /www/mysql/data

# Rozdeleni jednotlivych tabulek do jednotlivych souboru
innodb_file_per_table       = 1

# Format ulozeni dat do souboru
innodb_file_format          = barracuda

# limit io capacity - https://mariadb.com/kb/en/innodb-system-variables/#innodb_io_capacity
innodb_io_capacity         = 5000

# Pocet naraz spustenych threadu ktere zapisuji - pending reads a pending writes by mely byt 0, lze overit v pmm2 nebo SHOW ENGINE INNODB STATUS\G (nastaveno na percona default)
innodb_write_io_threads     = 8

# Pocet naraz spustenych threadu ktere ctou - pending reads a pending writes by mely byt 0, lze overit v pmm2 nebo SHOW ENGINE INNODB STATUS\G (nastaveno na percona default)
innodb_read_io_threads      = 8

# Velikost mysql ktera se po startu nacte do ram
innodb_buffer_pool_size     = 70G

# Jednotlive innodb soubory (mezipamet pro reseni dotazu, mely by byt kolem 10%-25% velikosti mysql), oddelovac ;
innodb_data_file_path       = ibdata1:2048M;ibdata2:2048M;ibdata3:2048M;ibdata4:2048M
# Korigovani I/O zapisu dat do souboru a nenechani je v bufferu. O_DIRECT - okamzity fsync() na data a log soubory
innodb_flush_method         = O_DIRECT

# urcuje, kdy se flushnou data log souboru na disk
# 0 ... cca 1 za sekundu
# 1 ... po kazdem commitu
# 2 ... do logu se zapisuje po commitu, flushne se cca za 1 sekundu.
innodb_flush_log_at_trx_commit = 2

# Velikost souboru pro vraceni se do predchoziho stavu pomoci Redo (kdyz neprojde commit) (innodb_buffer_pool_size/4 ???)
innodb_log_file_size        = 5G

# pocet souboru pro Redo, nasobi innodb_log_file_size
innodb_log_files_in_group   = 2

# maximalni velikost transakce v ram ktera se jeste nazapise na disk do innodb_log_files
innodb_log_buffer_size      = 128M

# timeout, kdy transakce ceka na zamek nez dojde k rollbacku
innodb_lock_wait_timeout     = 50

# Adaptivni hash index (vyuzije volnou pamet pro hash index)
innodb_adaptive_hash_index  = 1

# Max. pocet soucasnych threadu (viz thread_cache vyse)
innodb_thread_concurrency   = 16

# This InnoDB autoincrement locking mode is a requirement for Galera, 2 = (“interleaved” lock mode)
innodb_autoinc_lock_mode    = 2

# https://mariadb.com/kb/en/innodb-system-variables/#innodb_flush_neighbors
innodb_flush_neighbors = 0

#####################################################################
# Replikace
#####################################################################

# zapnuti GTID modu
gtid-strict-mode            = 1

# node id
server-id                   = 1
gtid-domain-id              = 30

# Kam vytvaret binarni logy
log-bin                     = /www/mysql/data/mysql-bin

# logovat do binlogu i replikovane veci (ne jen ktere se primo spusti na tomto node)
log-slave-updates           = 1

# expirace binlogu
expire_logs_days            = 3

#####################################################################
# Galera
#############################################################

wsrep_on                    = on
wsrep_gtid_mode             = on
wsrep_gtid_domain_id        = 1313

# Format binarnich logu (zaznam o zmenach v db ktery se pak prenasi a replikuje)
binlog_format               = ROW
# Maximalni velikost jednoho binarniho logu se zmenami
max_binlog_size             = 1000M

# Cesta ke knihove galery
wsrep_provider              = /usr/lib/libgalera_smm.so

# Jmeno clusteru
wsrep_cluster_name          = hp_layout

# Adresa tohoto nodu
wsrep_node_address          = *********
# IP adresy nodu v clusteru, nemusi byt vseci, nody si to reknout navzajem. Je
# vyhodne zadat IP adresy misto FQDN.
wsrep_cluster_address       = ***********

# SST metoda pro replikace
wsrep_sst_method            = mariabackup

# Autentizace pro SST
wsrep_sst_auth              = "mysql:"
# Pocet threadu ktere replikuji
wsrep_slave_threads         = 18

# Pockani na vsechny commity, aktualni data v celem clusteru
wsrep_sync_wait             = 0

wsrep_provider_options      = "gcache.size=8G;gcache.recover=yes;pc.weight=1"

#####################################################################
# Performance_schema
#####################################################################
performance_schema=ON
performance_schema_instrument='stage/%=ON'
performance_schema_consumer_events_stages_current=ON
performance_schema_consumer_events_stages_history=OFF
performance_schema_consumer_events_stages_history_long=OFF
performance_schema_accounts_size = 100
performance_schema_digests_size = 20000
performance_schema_events_stages_history_long_size = 20000
performance_schema_events_stages_history_size = 1024
performance_schema_events_statements_history_long_size = 20000
performance_schema_events_statements_history_size = 1024
performance_schema_events_waits_history_long_size = 20000
performance_schema_events_waits_history_size = 1024
performance_schema_session_connect_attrs_size = -1

#####################################################################
# Plugin
#####################################################################
# Enable User Statistics feature
userstat = 1