########################################################################

########################################################################

########### MARIADB: TORNADO24 CONFIGURATION FILE - ROCKSDB ############

########################################################################

########################################################################

#########################################################################

############################# client ####################################

#########################################################################

# The following options will be passed to all MariaDB clients

[client]

port = 3305

#socket = /tmp/mysql.sock

socket          = C:/Program Files/MariaDB 10.3/data/mysql.sock

#pid-file        = C:/Program Files/MariaDB 10.3/data/mysql.pid

default-character-set = latin1

# Here follows entries for some specific programs

#########################################################################

############################ mariadb ####################################

#########################################################################

[mariadb]

#local_infile = 1

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# |||||||||||||||||||||||||||||| GENERAL |||||||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# The MariaDB server

[mysqld]

port = 3305

#socket = /tmp/mysql.sock

socket          = C:/Program Files/MariaDB 10.3/data/mysql.sock

#pid-file       = C:/Program Files/MariaDB 10.3/data/mysql.pid

tmpdir = F:/MariaDB 10.3/temp/

# Table type which is used by default when creating new tables, if not

# specified differently during the CREATE TABLE statement.

# The default storage engine that will be used when new tables are created.

default-storage-engine  = rocksdb

# Default storage engine that will be used for tables created with 

# CREATE TEMPORARY TABLE (Whichever this Engine, Aria Engine will always

# be used for internal temporary tables, as materialized tables in subqueries,

# or other internal tables). RocksDB does not perform well for Temporary Tables,

# which require fast INSERT INTO...VALUES in Loops (For example: Cursors).

default_tmp_storage_engine = aria

# The default character set that will be used when a new schema or table is

# created and no character set is defined

character-set-server = latin1_bin

collation-server = latin1_spanish_ci

# Set the SQL mode to strict

# Default: sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

sql_mode = ""

# *************************** OTHER *********************************** #

group_concat_max_len        = 1048576

event_scheduler        = ON

net_write_timeout        = 3600

net_read_timeout        = 3600

#local_infile        = 1

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||| DATA STORAGE |||||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# Path to the database root

datadir = F:/MariaDB 10.3/data

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# |||||||||||||||||||||||||||||| SAFETY ||||||||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# The maximum size of a query packet the server can handle as well as

# maximum query size server can process (Important when working with

# large BLOBs).  enlarged dynamically, for each connection.

max_allowed_packet = 512M

# Maximum amount of errors allowed per host. If this limit is reached,

# the host will be blocked from connecting to the MariaDB server until

# "FLUSH HOSTS" has been run or the server was restarted. Invalid

# passwords and other errors during the connect phase result in

# increasing this value. See the "Aborted_connects" status variable for

# global counter.

max_connect_errors = 1000

# Secure File Priv.

# Disabled: secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/"

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# |||||||||||||||||||||||| CACHES AND LIMITS |||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# 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

# 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 = 1024M

# Query cache is used to cache SELECT results and later return them

# without actual executing the same query once again. Having the query

# cache enabled may result in significant speed improvements, if your

# have a lot of identical queries and rarely changing tables. See the

# "Qcache_lowmem_prunes" status variable to check if the current value

# is high enough for your load.

# Note: In case your tables change very often or if your queries are

# textually different every time, the query cache may result in a

# slowdown instead of a performance improvement.

query_cache_size = 1M

# How many threads we should keep in a cache for reuse. When a client

# disconnects, the client's threads are put in the cache if there aren't

# more than thread_cache_size threads from before.  This greatly reduces

# the amount of thread creations needed if you have a lot of new

# connections. (Normally this doesn't give a notable performance

# improvement if you have a good thread implementation.)

thread_cache_size = 256

# The maximum amount of concurrent sessions the MariaDB server will

# allow. One of these connections will be reserved for a user with

# SUPER privileges to allow the administrator to login even if the

# connection limit has been reached.

# Low value choosen is order to reduce RAM Memory Allocation.

max_connections = 50

# The number of open tables for all threads. Increasing this value

# increases the number of file descriptors that mysqld requires.

# Therefore you have to make sure to set the amount of open files

# allowed to at least 4096 in the variable "open-files-limit" in

# section [mysqld_safe]

table_open_cache = 4096

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||||| LOGGING ||||||||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# General and Slow logging.

log-output='FILE,TABLE'

general-log=0

general_log_file="NGALARRETA-P700.log"

# Error Logging.

log-error                      = C:/Program Files/MariaDB 10.3/data/NGALARRETA-P700-error.log

log_queries_not_using_indexes  = 0

slow-query-log                 = 0

slow-query-log-file            = C:/Program Files/MariaDB 10.3/data/NGALARRETA-P700-slow.log

long_query_time        = 1800

# Binary Logging.

# log-bin

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||| ALL ENGINES ||||||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY

# queries. If sorted data does not fit into the sort buffer, a disk

# based merge sort is used instead - See the "Sort_merge_passes"

# status variable. Allocated per thread if sort is needed.

# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS 

# output, you can consider increasing the sort_buffer_size value to 

# speed up ORDER BY or GROUP BY operations that cannot be improved with

# query optimization or improved indexing.

# Commented for choosing default values.

sort_buffer_size = 256K

# 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

# The minimum size of the buffer that is used for plain index scans, range 

# index scans, and joins that do not use indexes and thus perform full 

# table scans.

# Commented for Default Value.

join_buffer_size = 256K

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# |||||||||||||||||||||||||||||| OTHERS ||||||||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# 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.

# You need to increase this only if you expect a large number of 

# connections in a short period of time.

back_log = 150

# 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

# Minimum word length to be indexed by the full text search index.

# You might wish to decrease it if you need to search for shorter words.

# Note that you need to rebuild your FULLTEXT index, after you have

# modified this value.

ft_min_word_len = 3

# 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 = 297K

# Set the default transaction isolation level. Levels available are:

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

# This variable's name might change to tx_isolation in future versions

# of MariaDB

transaction-isolation = REPEATABLE-READ

# Set the query cache type. 0 for OFF, 1 for ON and 2 for DEMAND.

query_cache_type=0

# The number of table definitions (from .frm files) that can be stored

# in the definition cache. If you use a large number of tables, you can

# create a large table definition cache to speed up opening of tables.

# The table definition cache takes less space and does not use file

# descriptors, unlike the normal table cache.

# The minimum and default values are both 400.

table_definition_cache=1400

# If the value of this variable is greater than 0, a replication slave

# synchronizes its master.info file to disk.

# (using fdatasync()) after every sync_master_info events.

sync_master_info=10000

# If the value of this variable is greater than 0, the MySQL server 

# synchronizes its relay log to disk.

# (using fdatasync()) after every sync_relay_log writes to the relay log.

sync_relay_log=10000

# If the value of this variable is greater than 0, a replication 

# slave synchronizes its relay-log.info file to disk.

# (using fdatasync()) after every sync_relay_log_info transactions.

sync_relay_log_info=10000

#########################################################################

########################## REPLICATION ##################################

#########################################################################

# Server Id.

# Used to identify master and slave servers in replication. The server_id

# must be unique for each server in the replicating group. If left at 0,

# the default, a slave will not connect to a master, and a master will

# refuse all slave connections.

# server_id=0

# Replication Slave (comment out master section to use this)

#

# To configure this host as a replication slave, you can choose between

# two methods :

#

# 1) Use the CHANGE MASTER TO command (fully described in our manual) -

#    the syntax is:

#

#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,

#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

#

#    where you replace <host>, <user>, <password> by quoted strings and

#    <port> by the master's port number (3306 by default).

#

#    Example:

#

#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

#    MASTER_USER='joe', MASTER_PASSWORD='secret';

#

# OR

#

# 2) Set the variables below. However, in case you choose this method, then

#    start replication for the first time (even unsuccessfully, for example

#    if you mistyped the password in master-password and the slave fails to

#    connect), the slave will create a master.info file, and any later

#    change in this file to the variables' values below will be ignored and

#    overridden by the content of the master.info file, unless you shutdown

#    the slave server, delete master.info and restart the slaver server.

#    For that reason, you may want to leave the lines below untouched

#    (commented) and instead use CHANGE MASTER TO (see above)

#

# required unique id between 2 and 2^32 - 1

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

#server-id       = 2

#

# The replication master for this slave - required

#master-host     =   <hostname>

#

# The username the slave will use for authentication when connecting

# to the master - required

#master-user     =   <username>

#

# The password the slave will authenticate with when connecting to

# the master - required

#master-password =   <password>

#

# The port the master is listening on.

# optional - defaults to 3306

#master-port     =  <port>

#

# binary logging - not required for slaves, but recommended

#log-bin=mysql-bin

#

# binary logging format - mixed recommended 

#binlog_format=mixed

# ***  Replication related settings 

# Unique server identification number between 1 and 2^32-1. This value

# is required for both master and slave hosts. It defaults to 1 if

# "master-host" is not set, but will MariaDB will not function as a master

# if it is omitted.

# server-id = 1

#

# Replication Slave (comment out master section to use this)

#

# To configure this host as a replication slave, you can choose between

# two methods :

#

# 1) Use the CHANGE MASTER TO command (fully described in our manual) -

#    the syntax is:

#

#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,

#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

#

#    where you replace <host>, <user>, <password> by quoted strings and

#    <port> by the master's port number (3306 by default).

#

#    Example:

#

#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

#    MASTER_USER='joe', MASTER_PASSWORD='secret';

#

# OR

#

# 2) Set the variables below. However, in case you choose this method, then

#    start replication for the first time (even unsuccessfully, for example

#    if you mistyped the password in master-password and the slave fails to

#    connect), the slave will create a master.info file, and any later

#    changes in this file to the variable values below will be ignored and

#    overridden by the content of the master.info file, unless you shutdown

#    the slave server, delete master.info and restart the slaver server.

#    For that reason, you may want to leave the lines below untouched

#    (commented) and instead use CHANGE MASTER TO (see above)

#

# required unique id between 2 and 2^32 - 1

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

#server-id = 2

#

# The replication master for this slave - required

#master-host = <hostname>

#

# The username the slave will use for authentication when connecting

# to the master - required

#master-user = <username>

#

# The password the slave will authenticate with when connecting to

# the master - required

#master-password = <password>

#

# The port the master is listening on.

# optional - defaults to 3306

#master-port = <port>

#

# Make the slave read-only. Only users with the SUPER privilege and the

# replication slave thread will be able to modify data on it. You can

# use this to ensure that no applications will accidently modify data on

# the slave instead of the master

#read_only

#########################################################################

######################### innodb (Skipped) ##############################

#########################################################################

skip-innodb

############################################################################################################################

############################################################################################################################

############################################################################################################################

# MariaDB ROCKSDB Engine Server Variables Performance-Tuning (Optimized 50/50 Snappy)

############################################################################################################################

############################################################################################################################

############################################################################################################################

#############################################################################################################################

# rocksdb_block_size

#############################################################################################################################

# NOTES:

# a) I/O Unit (Not fully Aligned). 

# b) Default is 4KB. 16 KB gives better space savings, but needs extra CPU for decompression. 

# c) Measure trade-offs between 4K, 8K, 16K and 32 K.

# d) Check the size of data of the block cache in DB_BLOCK_CACHE_USAGE Column of the INFORMATION_SCHEMA.ROCKSDB_DBSTATS table.

rocksdb_block_size = 8192 # Default Value: 4096 (4K)

#############################################################################################################################

# rocksdb_block_cache_size

#############################################################################################################################

# DESCRIPTION: Block_cache size for RocksDB.

# NOTES: RocksDB’s Internal Cache. Similar to innodb_buffer_pool_size, but less important since RocksDB relies on OS cache too.

# Set Variable depending on objective "Commit(kB)" to be set in RAM Memory by the "mysqld" process.

rocksdb_block_cache_size = 2G # Default: 512 MB

# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# Index & Filter Blocks

# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# IMPORTANT: Both Options MUST always be set to ON (1) in order to limit resources to rocksdb_block_cache_size Memory to the 

#            objective "Commit" Load.

# rocksdb_cache_index_and_filter_blocks: If you set cache_index_and_filter_blocks to true, index and filter blocks 

# will be stored in block cache, together with all other data blocks. 

# 

# This allows to effectively limit allocated resources.

#

# rocksdb_pin_l0_filter_and_index_blocks_in_cache: Minimizes performance impact of rocksdb_cache_index_and_filter_blocks = ON,

# in order to effectively limit resources without a decrease in performance.

rocksdb_cache_index_and_filter_blocks = 1

rocksdb_pin_l0_filter_and_index_blocks_in_cache = 1

#############################################################################################################################

# ROCKSDB WAL Configuration Settings

#############################################################################################################################

# NOTES:

# Control Maximum WAL Size. 

# Setting as large as total InnoDB Log Size would be fine.

rocksdb_max_total_wal_size = 4G # Default: 0

rocksdb_wal_dir = F:/MariaDB 10.3/data

# rocksdb_wal_dir: Recommended to set the WAL path on a different SSD (Whenever its possible).

#############################################################################################################################

# rocksdb_max_background_jobs

#############################################################################################################################

# DEFINITION: Maximum number of concurrent background jobs, including flushes  and compactions. RocksDB will automatically 

# decide how to allocate the available job slots to flushes and compactions.

# VALUE RANGE: -1 to 64.

# DEFAULT: 2.

# WARNING: This option must be used with MariaDB 10.3.8 as “rocksdb_max_background_compactions”  

# and “rocksdb_max_background_flushes” variables have been DEPRECATED.

rocksdb_max_background_jobs = 36

#############################################################################################################################

# rocksdb_lock_wait_timeout

#############################################################################################################################

# DESCRIPTION: Number of seconds to wait for lock.

rocksdb_lock_wait_timeout = 2 # Default: 1

#############################################################################################################################

# rocksdb_max_open_files

#############################################################################################################################

# NOTE: 

# a) Increase file descriptor limit for mysqld process (Increase nofile in /etc/security/limits.conf)

# b) If setting greater than 0, RocksDB still uses table_cache, which will lock a mutex every time you access the file. 

#    I think you'll see much greater benefit with -1 because then you will not need to go through LRUCache to get the table you 

#    need.

#

# IMPORTANT: Keep all files Open! (-1)

#

rocksdb_max_open_files = -1 # Default Value: -1

#############################################################################################################################

# rocksdb_db_write_buffer_size

#############################################################################################################################

# DESCRIPTION: This is the maximum size of all Write Buffers across all Collumn Families in the database.

# It represents the amount of data to build up in memory (backed by an unsorted log on disk) across all

# column families before writing to a sorted on-disk file.

#

# "Total size of memtables across column families". This can be used to manage the total memory used by memtables.

#

# This is distinct from write_buffer_size, which enforces a limit for a single memtable.

#

# By default this feature is disabled (by being set to 0). You should not need to change it. 

rocksdb_db_write_buffer_size = 4G # Default Value: 0 (Disabled).

#############################################################################################################################

# Other Configurations (CF Options)

#############################################################################################################################

rocksdb_table_cache_numshardbits = 6 # Default: 6. Optimal 50/50 Snappy: 6.

# -----------------------------------------------------------------------------------

# Options to Enable or Disable

# -----------------------------------------------------------------------------------

# NOTE: "allow_mmap_reads" cannot be used with "use_direct_reads" or "use_direct_io_for_flush_and_compaction". Otherwise Server

#       CRASHES.

rocksdb_allow_mmap_reads = 1 # Default: 0 # Useful for SSD Disks. Do not set with Spinning Disks.

rocksdb_allow_mmap_writes = 0 # Default: 0

rocksdb_use_fsync = 0 # Default: 0

rocksdb_use_adaptive_mutex = 0 # Default: 0

# -----------------------------------------------------------------------------------

# Rate Limiter

# -----------------------------------------------------------------------------------

rocksdb_bytes_per_sync = 512000 # Default: 0. Optimal: 512 KB.

rocksdb_wal_bytes_per_sync = 4194304 # Default: 0. Set to 4MB.

rocksdb_rate_limiter_bytes_per_sec = 419430400 # Default: 0. Set to 400 MB/s. Increase if you're running on higher.

# -----------------------------------------------------------------------------------

# Triggering Compaction if there are many Sequential Deletes

# -----------------------------------------------------------------------------------

# NOTE: DESCRIPTION: "RocksDB will trigger compaction for the file if it has more than the specified number 

#                    of sequential deletes per window".

rocksdb_compaction_sequential_deletes_count_sd=1 # Default: 0

rocksdb_compaction_sequential_deletes=199999 # Default: 0

rocksdb_compaction_sequential_deletes_window=200000 # Default: 0

# -----------------------------------------------------------------------------------

# Max Subcompactations

# -----------------------------------------------------------------------------------

# DESCRIPTION: This value represents the maximum number of threads that will concurrently perform a compaction job by breaking it into multiple,

#        smaller ones that are run simultaneously.

# DEFAULT:     1 (i.e. no subcompactions)

#

# NOTES: L0 to L1 compaction cannot be parallelized. In some cases, it may become a bottleneck that limit the total 

# compaction speed. In this case, users can set max_subcompactions to more than 1. In this case, we'll try to partition the 

# range and use multiple threads to execute it.

#

# Set up compression more aggressively and allocate more threads for flush and compaction.

rocksdb_max_subcompactions = 2 # Default: 1

#############################################################################################################################

# Override cf options for RocksDB.

#############################################################################################################################

rocksdb_default_cf_options="write_buffer_size=512m;max_bytes_for_level_base=2048m;target_file_size_base=256m;max_bytes_for_level_multiplier=10;max_write_buffer_number=12;min_write_buffer_number_to_merge=4;target_file_size_multiplier=1;level0_file_num_compaction_trigger=1;level0_slowdown_writes_trigger=36;level0_stop_writes_trigger=42;source_compaction_factor=2;max_grandparent_overlap_factor=10;block_based_table_factory={filter_policy=bloomfilter:10:false};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;compaction_pri=kMinOverlappingRatio;compression=kZlibCompression;num_levels=7;compression_per_level=kNoCompression:kNoCompression:kNoCompression:kZlibCompression:kZlibCompression:kZlibCompression:kZlibCompression;compression_opts=-14:1:0;prefix_extractor=capped:20;memtable_prefix_bloom_bits=41943040;memtable_prefix_bloom_probes=6"

rocksdb_whole_key_filtering = 0 # NOTE: Default Value: ON (1). It has been disabled in order to use the "Prefix Extractor" for Bloom Filters.

#-------------------------------------------------------------

# MOST IMPORTANT SETTINGS: Optimal 50/50 Snappy

#-------------------------------------------------------------

# write_buffer_size:        Sets the size of a single memtable. Once memtable exceeds this size, it is marked immutable and

#                           a new one is created.It represents the amount of data to build up in memory (backed by an unsorted 

#                           log on disk) before converting to a sorted on-disk file. The default is 64 MB.

#                           You need to budget for 2 x your worst case memory use. If you don't have enough memory for this,

#                           you should reduce this value.

#                          

#                           "You can think of memtables as in-memory write buffers. Each new key-value pair is first written

#                           to the memtable. Memtable size is controlled by the option write_buffer_size. It's usually not a

#                           big memory consumer. However, memtable size is inversely proportional to write amplification

#                           -- the more memory you give to the memtable, the less the write amplification is.

#

#                           If you increase your memtable size, be sure to also increase your L1 size! L1 size is controlled by

#                           the option *"max_bytes_for_level_base"*.

#

#     Baseline: 256 MB. Optimized 50/50 Snappy: 512MB

#

# max_bytes_for_level_base: Write amplification. *"max_bytes_for_level_base"* is total size of level 1. As mentioned, we recommend

#                           that this be around the size of level 0. Each subsequent level is *"max_bytes_for_level_multiplier"*

#                           larger than previous one. The default is 10 and we do not recommend changing that.

#                           It could be Set Same Value than "Cache Size".

#

# target_file_size_base:    "Default Value is 64MB". Increasing "*target_file_size_base*" will reduce total number of database files, 

#                           which is generally a good thing. We recommend setting target_file_size_base to be 

#                           *"max_bytes_for_level_base*" / 10, so that there are 10 files in level 1".

#

# max_bytes_for_level_multiplier: Default: 10 (Optimal Value).

#

# target_file_size_multiplier: Optimal Value: 1

#

# compression: Default: Snappy (Optimal).

#

#-------------------------------------------------------------

# OTHER SETTINGS:

#-------------------------------------------------------------

# min_write_buffer_number_to_merge: "Minimum number of memtables to be merged before flushing to storage.

#

#                                       For example:

#                                       * If this option is set to 2, immutable memtables are only flushed when there are 2 of them

#                                         - a single immutable memtable will never be flushed. 

# * If multiple memtables are merged together, less data may be written to storage since two updates 

#   are merged to a single key.

#

#                                       Recommended Value: 4 (Optimal 50/50 Snappy).

# level0_file_num_compaction_trigger:   "Number of files to trigger level-0 compaction. A value <0 means that level-0 compaction will

#         not be triggered by number of files at all". Default: 4.

#

#         For example, if level0_file_num_compaction_trigger = 8 and every flushed file is 100MB. 

#                                       Then as soon as there is 8 files, they are compacted to one 800MB file. And after we have 8

#                                       new 100MB files, they are compacted in the second 800MB, and so on. Eventually we'll have a list

#                                       of 800MB files and no more than 8 100MB files.

#

# Optimal Value: 1.

# max_write_buffer_number: "When the number of memtables waiting to flush is greater or equal to max_write_buffer_number, writes are

#    fully stopped to wait for flush finishes.

#

#                          The number of in-memory memtables. Each memtable is of size write_buffer_size.

#

#                          Default: 2. Recommended Value: Same as "Total Physical CPU"

# level0_slowdown_writes_trigger: If level0_slowdown_writes_trigger, a limited write rate to DB is activated.

#

#                                       Recommended Value: Same as "Total Physical CPU" x3

#

# level0_stop_writes_trigger: If level0_stop_writes_trigger, writes to DB are stopped.

#

#                                       Recommended Value: Same as "Total Physical CPU" x3.5

#

# source_compaction_factor: Maximum number of bytes in all source files to be compacted in a single compaction run. 

#

# Default: 1. Optimal 50/50 Snappy: 2

#

# max_grandparent_overlap_factor:       Only for level-based compactions. Default: 10. Optimal 50/50 Snappy: 10

#

##############################################

# block_based_table_factory

##############################################

#

# filter_policy:    "The optional FilterPolicy mechanism can be used to enable Bloom Filters, and reduce the number of disk reads substantially. 

# If you're doing point lookups you definitely want to turn bloom filters on. We use bloom filters to avoid unnecessary disk reads. 

# Default bits_per_key is 10, which yields ~1% false positive rate. Larger bits_per_key values (e.g.: 12) will 

#      reduce false positive rate, but increase memory usage and space amplification.".

# 

# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# "PREFIX EXTRACTOR" VS "WHOLE KEY FILTERING"

# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#

# rocksdb_whole_key_filtering:  "If set to None the rocksdb default of True is used. If True, place whole keys in the filter (not just prefixes). 

# This must generally be true for gets to be efficient".

# WARNING: 'whole_key_filtering' (1) cannot be enabled simmultaneously with 'prefix_extractor' (prefix_extractor=capped:20).

#

# prefix_extractor: "A SliceTransform object that defines key prefixes. Key prefixes are then used to perform some interesting optimizations:"

# (1) Define prefix bloom filters, which can reduce read amplification of prefix range queries (e.g., give me all keys that start with prefix XXX). This

#     usually sufficient in reducing I/Os, points (2) and (3) are usually not necessary nor common.

# (2) Use hash-map-based memtables to avoid binary search costs in memtables.

# (3) Add hash index to table files to avoid binary search costs in table files. 

#

# INFORMAL DEFINITION: "It lets you use a bloom for some range queries".

#

# USE CASE: Composite Index where there is an Equality Predicate on a Prefix of the Columns.

#

# EXAMPLE: There is an index on (a,b,c) and a common query has: "a = value1 and b = value2 and c >= value3".

#  

# - Then a prefix bloom defined to be limited to a,b can be used. And of course, that requires you to set the correct length for it.

#

# - If the bloom were defined on all columns then it could not be used for this query.

#

# **PREFIX VS. WHOLE KEY** a) "By default a hash of every whole key is added to the bloom filter. This can be disabled by setting 'rocksdb_whole_key_filtering'

#     to false. 

# b) When 'prefix_extractor' is set, a Hash of the Prefix is also Added to the Bloom. Since there are less unique prefixes than unique whole keys, 

#    storing only the prefixes in bloom will result into smaller blooms with the down side of having larger false positive rate. 

#    Moreover the prefix blooms can be optionally also used during ::Seek whereas the whole key blooms are only used for point lookups.

#

# WARNING: If both 'whole_key_filtering' and 'prefix' are set, 'prefix' are not checked during point lookups. If 'whole_key_filtering' is set, 

# this is the result of checking the bloom of the 'whole key', otherwise this is the result of checking the bloom of the 'prefix'.

#

#

# memtable_prefix_bloom_bits:   "Configure Memtable bloom filter". Characteristics:

# * Memtable Bloom Filter is useful to reduce CPU usage, if you see high CPU usage at rocksdb::MemTable::KeyComparator.

# * Size depends on Memtable size.

# * Recommended Values: Set memtable_prefix_bloom_bits=41943040 for 128MB Memtable (30/128M=4M keys * 10 bits per key).

#

# memtable_prefix_bloom_probes: "Allows to enable bloom filter for hash table to reduce memory accesses (usually means CPU cache misses) when reading from mem table to one, 

# for the case where key is not found in mem tables".

# 

# [Example 1: By enabling "Prefix Extractor"]

# rocksdb_default_cf_options="prefix_extractor=capped:20;

#         memtable_prefix_bloom_bits=41943040;

#                 memtable_prefix_bloom_probes=6"

#

# rocksdb_whole_key_filtering = 0

#

# [Example 2: By enabling "Whole Key Filtering"]

#

# rocksdb_whole_key_filtering = 1

#

##############################################

# Other Optimization Settings

##############################################

#

# level_compaction_dynamic_level_bytes:  "If set TRUE: Target size of the last level (num_levels-1) will always be actual size of the level. 

#   And then Target_Size(Ln-1) = Target_Size(Ln) / max_bytes_for_level_multiplier. We won't

#   fill any level whose target will be lower than max_bytes_for_level_base / max_bytes_for_level_multiplier. 

#   These levels will be kept empty and all L0 compaction will skip those levels and directly go to the first 

#   level with valid target size.

#

# optimize_filters_for_hits: "Enable to to reduce some bloom filter block size".

#

##############################################

# Compactation & Compression

##############################################

#

# compaction_pri: "Multi-Thread Compactation Algorithm. 'kMinOverlappingRatio' is choosen, as reduces write amplification".

#

# compression: "Allows to specify the compression to use in all compactation levels, which by default is Snappy. Snappy is lightweight compression so it usually strikes

#   a good balance between space and CPU usage". Value to Set: compression=kZlibCompression

#

# num_levels: "It is safe for num_levels to be bigger than expected number of levels in the database. Some higher levels may be empty, 

#   but this will not impact performance in any way. Only change this option if you expect your number of levels will be greater than 7 (default)".

#   Default: 7.

#

# compression_per_level: "Use this option to set different compressions for different levels. It usually makes sense to avoid compressing levels 0 and 1 and to compress data

#   only in higher levels. You can even set slower compression in highest level and faster compression in lower levels (by highest we mean Lmax)".

#

# compression_opts: Use "compression_opts" to config compression_opts.  The value format is of the form "<window_bits>:<level>:<strategy>:<max_dict_bytes>", being the fourth

# parameter optional

#

# [Example 1]:

#   compression_opts=4:5:6:7 is equivalent to setting:

# * window_bits = 4;

# * level = 5;

# * strategy = 6;

# * max_dict_bytes = 7;

#  

# [Example 2]: "Means Compression at Level 1"

# compression_opts=-14:1:0

#

# WARNING: If you do not set `compression_opts.level`, or set it to `CompressionOptions::kDefaultCompressionLevel`, we will attempt to pick the default 

#   corresponding to `compression` as follows:

#   - kZSTD: 3

#   - kZlibCompression: Z_DEFAULT_COMPRESSION (currently -1)

#   - kLZ4HCCompression: 0

#   - For all others, we do not specify a compression level

#

# ALLOWS: Different Options for Compression Algorithms used by bottommost_compression if it is Enabled.

#

#############################################################################################################################

# Options to Use with Spinning Disks

#############################################################################################################################

# WARNING: Do not use the Options in this Section ("rocksdb_compaction_readahead_size", "rocksdb_use_direct_reads", 

#    "rocksdb_use_direct_io_for_flush_and_compaction"), as they do not work with "rocksdb_allow_mmap_reads", resulting

#    in Server's Data Corruption.

# Throughput gap between random read vs. sequential read is much higher in spinning disks. Suggestions:

#---------------------------------------------------------

# Compaction Readahead Size

#---------------------------------------------------------

#If non-zero, we perform bigger reads when doing compaction. If you're running RocksDB on spinning disks, you should set this to

# at least 2MB (e.g: 16MB). We enforce it to be 2MB if you don't set it with direct I/O.

rocksdb_compaction_readahead_size=0 # Default: 0

#---------------------------------------------------------

# Direct Reads

#---------------------------------------------------------

# NOTE: * "allow_mmap_reads" cannot be used with "use_direct_reads" or "use_direct_io_for_flush_and_compaction"

# * "allow_mmap_writes" cannot be used with "use_direct_io_for_flush_and_compaction", i.e., they cannot be set to true at

#         the same time.

rocksdb_use_direct_reads=0 # Default: 0

rocksdb_use_direct_io_for_flush_and_compaction=0 # Default: 0

#############################################################################################################################

# Data Loading: Bulk Load

#############################################################################################################################

# DESCRIPTION: Commit rows implicitly every rocksdb_bulk_load_size, on bulk load/insert, update and delete.

# NOTE: Session Variables.

rocksdb_commit_in_the_middle = 1 # Default: 0

# Maximum number of locks a transaction can have:

rocksdb_max_row_locks = 1048576

#############################################################################################################################

# MyRocks Crash Recovery

#############################################################################################################################

# MyRocks supports crash recovery. Crash recovery is done by replaying transaction logs from WAL (Write Ahead Log) files. 

# MyRocks has a system variable rocksdb_wal_recovery_mode to control how to apply logs, if any of the WAL entries is corrupted. 

# This variable can be any of the following options:

#

# 0: If a corrupted WAL entry is detected as the last entry in the WAL, truncate the entry and start up normally; otherwise, 

#    refuse to start.

#

# 1 (default): If a corrupted WAL entry is detected, fail to start. This is the most conservative recovery mode.

#

# 2: If a corrupted WAL entry is detected in the middle of the WAL, truncate all of WAL entries after that (even though there

#    may be uncorrupted entries) and then start up normally. For Replication Slaves, this option is fine, since the slave 

#    instance can recover any lost data from the master without breaking consistency. For Replication Masters, this option

#    may end up losing data if you do not run failover. For example, if the master crashed and was restarted (by mysqld_safe,

#    or auto restart after OS reboot) with this mode and it silently truncated several WAL entries, the master would lose some

#    data which may be present on one or more slaves.

#

# 3: If a corrupted WAL entry is detected in the middle of the WAL, skip the WAL entry and continue to apply as many healthy WAL

#    entries as possible. This is the most dangerous recovery option and it is not generally recommended.

rocksdb_wal_recovery_mode = 1 # Default: 1

#########################################################################

###### myisam (Performance_Schema Variables: Joins not necessary) #######

#########################################################################

# If set, external locking for MyISAM tables is disabled.

# skip_external_locking = 1

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.

# Do not set it larger than 30% of your available memory, as some memory

# is also required by the OS to cache rows. Even if you're not using

# MyISAM tables, you should still set it to 8-64M as it will also be

# used for internal temporary disk tables.

# If you don't use MyISAM tables explicitly you can set key_buffer_size

# to a very low value, 64K for example.

# 32 [GB] (Tornado24 Half RAM)· 0,25 = 8[GB]

key_buffer_size = 64K

# This buffer is allocated when MariaDB needs to rebuild the index in

# REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE

# into an empty table. It is allocated per thread so be careful with

# large settings.

# myisam_sort_buffer_size = 128M

# The maximum size of the temporary file MariaDB is allowed to use while

# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.

# If the file-size would be bigger than this, the index will be created

# through the key cache (which is slower).

# myisam_max_sort_file_size = 50G

# If a table has more than one index, MyISAM can use more than one

# thread to repair them by sorting in parallel. This makes sense if you

# have multiple CPUs and plenty of memory.

# myisam_repair_threads = 6

# Automatically check and repair not properly closed MyISAM tables.

# myisam_recover_options = FORCE,BACKUP

# When reading rows in sorted order after a sort, the rows are read

# through this buffer to avoid disk seeks. You can improve ORDER BY

# performance a lot, if set this to a high value.

# Allocated per thread, when needed.

# read_rnd_buffer_size = 265K

#########################################################################

################# MyISAM & Aria System Variables ######################## 

#########################################################################

# Values in [MB] range turn out into a performance decrease

# Size of the buffer used for doing full table scans.

# Allocated per thread, if a full scan is needed.

read_buffer_size = 256K

# MyISAM uses special tree-like cache to make bulk inserts (that is,

# INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA

# INFILE) faster. This variable limits the size of the cache tree in

# bytes per thread. Setting it to 0 will disable this optimisation.  Do

# not set it larger than "key_buffer_size" for optimal performance.

# This buffer is allocated when a bulk insert is detected.

# Values of 1/4 key_buffer_size make sense (Percona). It is per connection,

# so a 1/16 ratio is choosen in order not to use excesive resources:

# 8[GB] (key_buffer_size) / 16 = 512[MB] (bulk_insert_buffer_size)

# Same value has been choosen as tmp_table_size (For those cases in which

# Temporary Tables exceed 512M and use MyISAM instead of Memory Engine).

# MyISAM uses a special tree-like cache to make bulk

# inserts faster for INSERT ... SELECT, INSERT ...

# VALUES (...), (...), ..., and LOAD DATA INFILE when

# adding data to NON-EMPTY TABLES.

# limits the size of the cache tree in bytes per thread.

bulk_insert_buffer_size=256M

#########################################################################

############# Aria System Variables (Disk Temporary Tables) #############

#########################################################################

# The maximum size of the temporary file MariaDB is allowed to use while

# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.

# If the file-size would be bigger than this, the index will be created

# through the key cache (which is slower).

aria_max_sort_file_size = 100G

# Size of the Key Buffer, used to cache index blocks for Aria tables.

# Do not set it larger than 30% of your available memory, as some memory

# is also required by the OS to cache rows. Even if you're not using

# Aria tables, you should still set it to 8-64M as it will also be

# used for internal temporary disk tables.

aria_pagecache_buffer_size = 4G

# If a table has more than one index, MyISAM can use more than one

# thread to repair them by sorting in parallel. This makes sense if you

# have multiple CPUs and plenty of memory.

aria_repair_threads = 49

# This buffer is allocated when MariaDB needs to rebuild the index in

# REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE

# into an empty table. It is allocated per thread so be careful with

# large settings.

aria_sort_buffer_size = 128M

# Automatically check and repair not properly closed MyISAM tables.

# In MariaDB 10.3.0 aria_recover is renamed to aria_recover_options.

aria_recover_options = FORCE,BACKUP

# Path to the directory where to store transactional log:

aria_log_dir_path=C:/Program Files/MariaDB 10.3/data

#########################################################################

########################### mysqldump ###################################

#########################################################################

[mysqldump]

# Do not buffer the whole result set in memory before writing it to

# file. Required for dumping very large tables quick

quick

# The maximum size of one packet or any generated or intermediate string, 

# or any parameter sent by the mysql_stmt_send_long_data() C API function.

max_allowed_packet = 16M

#########################################################################

############################# mysql #####################################

#########################################################################

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

# sql_safe_updates = 0

#local_infile = 1

#########################################################################

############################## mysqld_safe ##############################

#########################################################################

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

# |||||||||||||||||||||||| CACHES AND LIMITS |||||||||||||||||||||||||| #

# ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| #

[mysqld_safe]

# 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

# Changes the number of file descriptors available to mysqld.

# You should try increasing the value of this option if mysqld gives you

#  the error "Too many open files".

# Default Value: Autosized

# open_files_limit = 65535

# Number of table definitions that can be cached.

table_definition_cache = 4096

#########################################################################

############################## myisamchk ################################

#########################################################################

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

#########################################################################

############################## mysqlhotcopy #############################

#########################################################################

[mysqlhotcopy]

interactive-timeout

#########################################################################

############################ mysqld #####################################

#########################################################################

[mysqld]

character-set-server = latin1

collation-server = latin1_spanish_ci

#local_infile = 1

[client-server]

#local_infile = 1