This documentation relates to an old version of VIVO, version 1.11.x.
Looking for another version? See all documentation.
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.
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.
Although this won't affect an initial query, having large buffers for the indexes will help query performance once they have been warmed.
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.