ColumnStore System Variables: Advanced Performance and Control Flow
Advanced MariaDB ColumnStore system variables in Columnstore.xml for tuning control flow, connections and threading, memory and cache, disk-based operations, and job and system requests.
MariaDB ColumnStore offers a powerful set of advanced system variables designed to give administrators fine-grained control over performance, memory management, and query execution flow. While the default settings are optimized for general use, highly concurrent workloads or complex analytical queries—such as heavy aggregations and massive joins—often require specific hardware trade-offs.
The variables detailed below, configured in Columnstore.xml, allow you to scale data transfer throughput, optimize disk-based operations when memory is exhausted, and implement flow control mechanisms to protect critical components like the Execution Manager (ExeMgr) from being overloaded.
Columnstore.xml groups variables into sections, and some variable names appear in more than one section with different effects. Each entry below names its section. Pass the section and the variable name to mcsGetConfig and mcsSetConfig:
sudo mcsGetConfig <section> <variable>
sudo mcsSetConfig <section> <variable> <value>Control Flow Settings
Control Flow attempts to prevent ExeMgr facility overload. Multiple primprocs bombard the exemgr receiver queue, which utilizes parameters to enable and disable control flow. If a byte limit is hit, exemgr tells all the primprocs to slow down and queue up into a sender queue.
These variables belong to the FlowControl section. The shipped Columnstore.xml has no FlowControl section; mcsSetConfig creates it when you set the first variable.
DECFlowControlEnableBytesThresh: Controls how soon the sender queue is enabled. A smaller value means it is enabled sooner, preventing exemgr from overflowing by routing jobs slower via the sender queue. The default is50000000.DECFlowControlDisableBytesThresh: Acts as the threshold to disable flow control. The default is10000000.BPPSendThreadBytesThresh: Affects the depth of the sender queue in bytes. The default is250000000.BPPSendThreadMsgThresh: Affects the depth of the queue by message threshold. The default is100.
Connection and Threading Variables
ConnectionsPerPrimProc(in thePrimitiveServerssection): Connections to primproc can be increased via columnstore.xml to scale the throughput of data transfer. This is particularly useful for heavy group bys that send lots of original data to exemgr. The default is2.sudo mcsSetConfig PrimitiveServers ConnectionsPerPrimProc 4ThreadPoolSize(in theJobListsection): Specifies the size of the UM processing thread pool where all UM-based algorithms spawn threads. The default is1000, as set in the shippedColumnstore.xml; if you remove the entry, ColumnStore uses100. Don't confuse it withThreadPoolSizein theExeMgr1section, which sets the number of ExeMgr server threads.NumThreads(in theDBBCsection): Sets the number of I/O threads that read disk blocks into the block cache. Increase it to make fuller use of disk capacity. The default isMIN( 2 × cores , 32 ), taking the core count from the cgroup where one applies. Valid values are1to256; setting the variable explicitly overrides the 32-thread cap.NumCores(in theJobListsection): Sets the core count that the UM uses to size its threads: the number of Hash Join threads and scan-receive threads, and the default number of row-aggregation and window-function threads. The default is the number of cores in the system or in the cgroup assigned. To change only the number of Hash Join threads, setNumThreadsin theHashJoinsection instead.NumCores(in thePrimitiveServerssection): Sets the core count that PrimProc uses to size its threads: the number of threads that process primitive jobs (2 × cores) and the default number of I/O threads (NumThreadsin theDBBCsection). The default is the number of cores in the system or in the cgroup assigned.ProcessorThreadsPerScan(in theJobListsection): The number of jobs issued to process each extent. Increasing this will utilize more CPU. The default is16.
Memory and Cache Settings
NumBlocksPct(in theDBBCsection): Sets the size of the disk block cache as a percentage of physical memory. To set an absolute size instead, add a size suffix such asm(megabytes) org(gigabytes), for example16g. The default is20, as set in the shippedColumnstore.xml; if you remove the entry, ColumnStore uses70. Leave enough memory forTotalUmMemory,mariadbd, and the operating system.TotalUmMemory(in theHashJoinsection): Specifies the percentage of physical memory to utilize for joins, intermediate results, and set operations. The default is65%.MemoryCheckPercent(in theSystemConfigsection): The max real memory to limit the growth of buffers to, after which the system will self-shut down. The default is95.NumCaches(in theDBBCsection): The number of concurrent query caches used by PM to reduce contention accessing block cache resources. The default is1.
Disk-Based Operation Variables
columnstore_diskjoin_bucketsize(a MariaDB Server system variable, not aColumnstore.xmlsetting; set it withSET): Roughly the size of a file to be stored on disk when doing a disk-based join. The default is roughly 100MB. Using an SSD or NVME for the temp directory can allow for a 2x or 3x increase to speed up joins.AllowDiskBasedJoin(in theHashJoinsection): Controls the option to use disk-based joins. Enabling this allows for larger joins by leveraging/tmp/columnstore_tmp_files/joins/(or thejoins/subdirectory ofSystemTempFileDirin theSystemConfigsection, if set) instead of exhausting memory. The default isN.AllowDiskBasedAggregation(in theRowAggregationsection): Enables queries to use disk for intermediate data when the memory needed for the aggregation exceeds the memory limit on the server, allowing the query to complete without an IDB-2001 error. The default isN.
Job and System Request Variables
RequestSize(in theJobListsection): Controls the number of extents retrieved per request/job. Increasing theRequestSizemeans each thread handles more work per job, improving overall throughput by achieving higher concurrency. The default is1.MaxOutstandingRequests(in theJobListsection): The number of outstanding request messages sent by UM to PMs, with every message processing up a multiple of 8192 records. The default is20000, as set in the shippedColumnstore.xml; if you remove the entry, ColumnStore calculates a value from the number of PMs and cores, with a minimum of20.DBRMSnapshotInterval(in theSystemConfigsection): Controls how often workernode@1 callssaveState()to store a copy of the Extent Map. Setting this value to something small implicitly affectsdo_confirm(), which is called synchronously on every Extent Map change and blocks all writes whilst it is working. The default is100000.
This page is: Copyright © 2026 MariaDB. All rights reserved.
Last updated
Was this helpful?

