VIVO 1.14.x (Release in preparation)
This documentation relates to an old version of VIVO, version 1.10.x. Looking for another version? See all documentation.
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).
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
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
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.