[Maria-discuss] MariaDB + RocksDB: MemTables Compactation
Dear MariaDB Developers, I have an issue with RocksDB, as it takes a lot of Memory after performing a Bulk Load, taking almost 30GB of RAM Memory after data ingestion. My question is: ¿How can I flush RocksDB Compactation MemTables after the Data Bulk Load? ¿Is there any FLUSH TABLES ROCKSDB command sort of? Thank you, Juan Here is my.ini Configuration: ######################################################################## ######################################################################## ########### 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" #------------------------------------------------------------- # 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 ############################################################################################################################# # 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
I will introduce the following configuration settings to my.ini Configuration File in order to use Bloom Filters, and enable compression. Any further suggestion? Thank you! rocksdb_default_cf_options="block_based_table_factory={filter_policy=bloomfilter:10:false;};prefix_extractor=capped:4};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;compaction_pri=kMinOverlappingRatio;compression=kSnappyCompression" rocksdb_whole_key_filtering = 0 # NOTE: Default Value: ON (1). 'whole_key_filtering' (1) cannot be enabled simmultaneously with 'prefix_extractor', as the first would override the second. Therefore, # This has been disabled in order to reduce "Bloom Filter Memory Consumption", and use lower resources. ############################################## # 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.". # # # 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". # # 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. # # # **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'. # ############################################## # 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, which by default is Snappy. Snappy is lightweight compression so it usually strikes # a good balance between space and CPU usage". #
There is no kSnappyCompression on Windows, unless you managed to somehow compile it yourself.
________________________________
From: Maria-discuss
Thank you for noting that there is no kSnappyCompression Vladislab. Thank
you! After some research, I finally ended up with the following my.ini
file, in case you find it useful:
########################################################################
########################################################################
########### 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
"
Dear MariaDB Mailing List,
I have the following 3 New Questions regarding RocksDB Compression and
Bloom Filters:
a) I I set "compression=kZlibCompression" and
"num_levels=7;compression_per_level=kNoCompression:kNoCompression:kNoCompression:kZlibCompression:kZlibCompression:kZlibCompression:kZlibCompression",
being the first the same compression for all levels, and the second
specific compression for specific levels, does the second override the
first? And what does "compression_opts=-14:1:0" play on all this? Shall the
<level> in compression_opts be -1 or 1?
b) With a rocksdb_block_cache_size = 2G, with
"rocksdb_cache_index_and_filter_blocks = 1" and
"rocksdb_pin_l0_filter_and_index_blocks_in_cache = 1", is it better to
enable on Bloom Filters Whole Key Filtering or Prefix Extractor?
# [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
c) Why does the Server Crash if I enable "rocksdb_allow_mmap_reads = 1",
simultaneously with some of the following settings)
#############################################################################################################################
# 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
This is my full my.ini File, any further suggestions for a 24 Logic
Processors (12 Cores), 2 TB Single SSD Disk, and 64 GB RAM (From which we
only want to use a 25% of it)? Thank you! :)
########################################################################
########################################################################
########### 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
"
participants (2)
-
Juan Telleria Ruiz de Aguirre -
Vladislav Vaintroub