Would you like to improve the performance of queries on your research database? Wharton Research Computing can help you with that. Read on, or contact us for more info.
The correct choice for working with data is not always a regular file. Sometimes, the proper solution is a database. Queries against a database may be a better option, particularly if there are many iterations of a research code accessing the data simultaneously, or maybe the data set will not all fit in memory simultaneously.
Wharton Research Computing has maintained a MySQL database server for years, now, to satisfy this these needs. Recently, the server that houses our databases was given a hardware and software upgrade.
The database server is now backed by dedicated hardware, including an array of solid state disk (SSD), for blazingly fast access. How about a nice graph?
Previous incarnations of the database server, the red and green lines, did not have dedicated hardware and suffered from virtualization overhead. That wasn’t so bad, because our use cases did not require such a high level of performance. The need for larger databases and faster access necessitated the upgrade, resulting in the purple line. I think we are doing pretty well, here, with a 10x improvement in performance!
The software that serves our databases also received an important upgraded, to MariaDB 10. We have worked directly with the MariaDB team to optimize our database server configuration.
Our hardware database is replicated to a separate, second instance for backup. The replication is now double-checked with data integrity assurances, thanks to checksums between the main server and the replicant. Part of the replication integrity and query speed improvement is enforcing primary keys for all databases. All databases now have primary keys.
So how can a researcher make use of these improvements? If you have a database provisioned, you already are! If you are struggling with the performance of a query, let us know! We can have a specialist assist with crafting optimal queries.