we have installed mariadb along with columnstore engine and from the last few weeks we are facing memory chocking issue where memory getting chocked and all our DML/DDL operations are getting stuck, after restarting the services it gets fixed.
below are the stats :
total used free shared buff/cache available
Mem: 15 2 7 0 5 12
Swap: 4 0 4
[mysqld]
port = 3306
socket = /opt/evolv/mariadb/columnstore/mysql/lib/mysql/mysql.sock
datadir = /opt/evolv/mariadb/columnstore/mysql/db
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 512M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 0
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency = 8
thread_stack = 512K
lower_case_table_names=1
group_concat_max_len=512
infinidb_use_import_for_batchinsert=1
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 8192M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
Here's an analysis of the VARIABLES and (suspicious) GLOBAL STATUS; nothing exciting:
Observations:
The More Important Issues:
Uptime = 03:04:25; Please rerun SHOW GLOBAL STATUS after several hours.
Are you sure this was a SHOW GLOBAL STATUS ?
key_buffer_size is excessively large (3G). If you don't need MyISAM for anything, set it to 50M.
Check infinidb_um_mem_limit to see if it makes sense for your application.
Suggest lowering innodb_buffer_pool_size to 2G until the "choking" is figured out.
Details and other observations:
( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (3072M - 1.2 * 0 * 1024) / 15360M = 20.0% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size.
( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 3072M = 0 -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.
( innodb_buffer_pool_size / _ram ) = 6144M / 15360M = 40.0% -- % of RAM used for InnoDB buffer_pool
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 392,768 * 16384 / 6144M = 99.9% -- buffer pool free
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue
( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
(Not relevant if log_bin = OFF)
( long_query_time ) = 5 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
Abnormally large:
read_buffer_size = 32MB
Acl_database_grants = 780
Acl_proxy_users = 4
Acl_users = 281
Columstore.xml
95% of all memory??
<MemoryCheckPercent>95</MemoryCheckPercent> <!-- Max real memory to limit growth of buffers to -->
<DataFileLog>OFF</DataFileLog>
I guess this is not relevant, since it is commented out??
<!-- enable if you want to limit how much memory may be used for hdfs read/write memory buffers.
<hdfsRdwrBufferMaxSize>8G</hdfsRdwrBufferMaxSize>
-->
Keep in mind that MySQL, other than Columnstore, is consuming a lot of memory:
<TotalUmMemory>25%</TotalUmMemory>
<TotalPmUmMemory>10%</TotalPmUmMemory>