SDB - MySQL Tuning

By default, MySQL has reasonable defaults for a regular RDBMS application. However, SDB has a slightly unusual database layout - it has very few tables, some of which grow quite large, very quickly. Whilst the SDB code is well optimised for the majority of cases, to get the best performance, you should tune MySQL to take into account the table, index and join sizes.

Version Recommendation

It is recommended that you use 5.5 or later of MySQL (or the MariaDB equivalent).

MySQL DB Engine

It is recommended that you use innodb with the barracuda file format. You should also configure MySQL to use a file for each table.

innodb_file_per_table = 1
innodb_file_format = barracuda

MySQL Buffers

Although this won't affect an initial query, having large buffers for the indexes will help query performance once they have been warmed.

join_buffer_size = 32M
read_rd_buffer_size = 32M
innodb_buffer_pool_size = 1536M

Temporary Tables

SDB can generate some large joins, and by default anything over 16MB will be spooled to disk. This can slow large queries down dramatically. To avoid this, increase the temporary table sizes.

max_heap_table_size=256M
tmp_table_size=256M