Performance Tips

Joins

MariaDB has four join algorithms. MyTile is able to use all four types. The default settings for MariaDB enable only Incremental BNL. MyTile is able to take advantage of Batched Key Access along with Multi-Range Read Optimization algorithms and in most cases this produces significantly better join results. With Batched Key Access, MyTile is able to push all coordinates (keys) down to TileDB for performing more effective batching of data accesses.

Enabling Batched Key Access

You must enable the following join cache level and optimizer switches.

SQL

set join_cache_level= 6;
set optimizer_switch='mrr=on,mrr_sort_keys=on';

Join Batch Sizes

It is also important to adjust both join_buffer_space_limit and join_buffer_size to a size that is appropriate for your host. The size of the join buffer limits the amount of keys that are batched at one time. Too small of a value will yield bad performance for TileDB having to issue a large number of small queries. The size should be adjusted to a larger value based on the available memory size.