Blog roll
R-bloggers, Debian Weekly![[Valid RSS]](valid-rss-rogers.png "Validate my RSS feed")
Last modified:
mars 31, 2021
Partitioning is great for big data, but even with aggressive partitioning, customized configuration is required to fit a big database (~ hundreds of millions of rows in the tables, up to ~ 5 million new rows per day) into a Raspberry Pi 3, which has only 1 GB of RAM.
I've found out that my mariadb/mysql heavvily partitioned myisam-only database slowly increases the RAM usage until the point where the OOM reaper kills the server. So how do you limit the RAM use?
These variables are available for tuning (available at https://mariadb.com/kb/en/myisam-system-variables/), and their default
key_buffer_size = 134217728
key_cache_age_threshold
key_cache_block_size
key_cache_division_limit
key_cache_file_hash_size
key_cache_segments
myisam_block_size
myisam_data_pointer_size
myisam_max_extra_sort_file_size
myisam_max_sort_file_size
myisam_mmap_size
myisam_recover_options
myisam_repair_threads
myisam_sort_buffer_size
myisam_stats_method
myisam_use_mmap
These files governs them
# The MariaDB/MySQL tools read configuration files in the following order: # 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults, # 2. "/etc/mysql/conf.d/*.cnf" to set global options. # 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options. -rw-r--r-- 1 mysql mysql 41 mar 3 2018 /etc/mysql/conf.d/default-to-myisam.cnf -rw-r--r-- 1 mysql mysql 146 jun 30 2020 /etc/mysql/conf.d/disable-innodb.cnf -rw-r--r-- 1 mysql mysql 8 jul 9 2016 /etc/mysql/conf.d/mysql.cnf -rw-r--r-- 1 mysql mysql 55 jul 9 2016 /etc/mysql/conf.d/mysqldump.cnf -rw-r--r-- 1 mysql mysql 677 jul 10 2020 /etc/mysql/mariadb.conf.d/50-client.cnf -rw-r--r-- 1 mysql mysql 336 jun 7 2017 /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf -rw-r--r-- 1 mysql mysql 1032 jun 7 2017 /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf -rw-r--r-- 1 mysql mysql 3856 jul 31 2020 /etc/mysql/mariadb.conf.d/50-server.cnf
These are the current settings that are set in mariadb.conf.d/50-server.cnf
key_buffer_size = 16M (default: 134217728, min: 8) max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam_recover_options = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M
Since all writes and reads are filtered on the partitioning variable, the index is in practice not useful (except in a few small tables that are not partitioned).
Due to heavvy partitioning, there are a lot of files potentially opened, currently there are 51.202 files in the database. Any setting that caches by file is likely to have a too high default value than what is good here, and
key_cache_file_hash_size "Number of hash buckets for open and changed files. If you have many MyISAM files open you should increase this for faster flushing of changes. A good value is probably 1/10th of the number of possible open MyISAM files." myisam_mmap_size is another candidate: "Maximum memory in bytes that can be used for memory mapping compressed MyISAM files. Too high a value may result in swapping if there are many compressed MyISAM tables. " myisam_sort_buffer_size "Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table."
The default values are
key_cache_file_hash_size = 512 myisam_mmap_size = 18446744073709547520 ## must be at least 7 myisam_sort_buffer_size = 134217720 ## must be at least 4096
Let's try decreasing them and the global key_buffer_size.
## reduce RAM usage key_buffer_size = 8M key_cache_file_hash_size = 128 myisam_mmap_size = 10000 ## must be at least 7 myisam_sort_buffer_size = 1M ## must be at least 4096, default was 8M but was increased to 128M