The Ultimate Guide to High Availability with MariaDB

Disk Based Joins

You are viewing an old version of this article. View the current version here.

Joins are performed in-memory on the UM node. When a join operation exceeds the memory allocated on the UM for query joins, the query is aborted with an error code IDB-2003. Disk based joins enable such queries to use disk for intermediate join data in case when the memory needed for join exceeds the memory limit on the UM. All though slower in performance as compared to a fully in-memory join, and bound by the temporary space on disk, it does allow such queries to complete. Disk based joins does not include aggregation and DML joins.

The following variables in HashJoin element in the Calpont.xml configuration file relate to disk based joins. Calpont.xml resides in the etc directory for your installation(/usr/local/mariadb/columnstore/etc).

  • AllowDiskBasedJoin – Option to use disk Based joins. Valid values are Y (enabled) or N (disabled). Default is disabled.
  • TempFileCompression – Option to use compression for disk join files. Valid values are Y (use compressed files) or N (use non-compressed files).
  • TempFilePath – The directory path used for the disk joins. By default, this path is the tmp directory for your installation (i.e., /usr/local/mariadb/columnstore/tmp). Files (named infinidb-join-data*) in this directory will be created and cleaned on an as needed basis. The entire directory is removed and recreated by ExeMgr at startup.)

Note: When using disk based joins, it is strongly recommended that the TempFilePath reside on its own partition as the partition may fill up as queries are executed.

Per user join memory limit

In addition to system wide flag, at SQL Global and session level, following system variables exists for managing per user memory limit for joins.

  • infinidb_um_mem_limit - A value for Memory limit in MB per user. When this limit is exceeded by a join, it will switch to disk based join. By default the limit is not set(value of 0)

For modification at the Global level: In my.cnf file (typically /usr/local/mariadb/columnstore/mysql):

[mysqld]
xxxxx
infinidb_um_mem_limit = value
where value is the value in Mb for in memory limitation per user.

For modification at the Session level, before issuing your join query from SQL client, set the session variable as following.

set infinidb_um_mem_limit = value

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.