MySQL, PostgreSQL, SQLite or Microsoft SQL Server
Redmine can be used with MySQL, PostgreSQL, SQLite or Microsoft SQL Server database backends (the first three ones are actually natively supported by Ruby on Rails). But which one should you select for your Redmine installation? It's perhaps too important a question to be ignored in this book. No, I'm not going to praise some databases or criticize others—that's out of scope here. Instead, I'll cover things that you should consider when choosing a database backend.
Having worked with Redmine for quite a long time, I have noticed that most users use it with MySQL. Despite their reasons for choosing it, this means that Redmine has been better tested with this database backend. This argument becomes even more important if we consider that some plugins use SQL directly (that is, without the Rails SQL query builder).
But if you are seeking a reliable source on which database is faster with Redmine, you can check out the Redmine build logs at http://www.redmine.org/builds/index.html. These logs contain results of tests for different versions of Ruby and different database backends. Thus, at the time of writing this section, tests of the latest revisions, r14600, r14597, and r14596, in the latest stable branch 3.1-stable were running for the following time periods (only for ruby-1.9.3-p194):
|
mysql |
postgresql |
sqlite3 |
sqlserver | |
|---|---|---|---|---|
|
r14600 |
901.068992 s |
752.914607 s |
698.311900 s |
1356.417375 s |
|
r14597 |
684.866031 s |
815.066303 s |
636.497712 s |
1056.660942 s |
|
r14596 |
733.023295 s |
781.173148 s |
726.835502 s |
1019.416963 s |
|
Average |
772.986106 s |
783.051353 s |
687.215038 s |
1144.165093 s |
In the previous table, I used the data from the aforementioned logs. Certainly, by the time you read this section, there will be new data, but as practice shows, the results will most likely be the same. Anyway, you can check out new data or take more revisions into account to recheck the results.
So, according to this data, SQLite is the fastest, SQL Server is the slowest, and PostgreSQL is a little slower than MySQL.
But don't hurry to make a decision in favor of the fastest backend. Unfortunately, SQLite has scalability issues (you can't put it on a separate server) and will behave incorrectly in a multiuser environment. And I'm quite sure that you'll want to serve many users at a time.
You should also note that in addition to the comparative slowness, Microsoft SQL Server runs only on Windows, its full edition is not free, and the free one (Express) is limited. Also, support for this database backend was added especially to Redmine and is not native to Rails. This means that it was not tested as well as other alternatives. Anyway, if you already have SQL Server running in your corporate network, it can be a good idea to use it for Redmine.
