database performance: Java Linux Databases: Conclusions
Myths
No amount of database administrator performance tuning is going to workaround a broken design.
Writing good code, using proven techniques and understanding the efficient use of system resources
is more important than all the marketing background noise of the database vendors.
Choosing a Database
In most cases, all of the databases presented here will be able to provide adequate performance.
The decision of which one to use is more likely to be based on other factors such as stability, support, price, ease of use, familiarity, etc
However, in some specific cases, choosing the right one may save a lot of time and effort.
Java Threading
The cost of starting a new thread in Java is better than it was in earlier releases on Linux ("green threads" vs "Java threads"),
but it is still not negligeable.
If you are writing heavily threaded applications, consider using thread pools, reusing threads,
engineering your code to lessen the impact of starting new threads or simply not requiring so many threads.
Connection and Prepared Statement Pooling
Making a new connection to the database is one of the most expensive operations.
Connection pooling is simply mandatory for best performance.
The same principle also applies to prepared statements.
O/R mapping tools and application servers should take care of this automatically.
Up-to-date
If not for the security considerations, do it for the performance: stay up to date!
Newer JDKs generally do not perform worse (although there are exceptions) and provide new features,
this is where the development is happening, and this is where bugs get fixed first too...
Some of the new features can also be used to write better code, which in turn leads to better overall performance.
From past experience, the same applies to new database server releases.
Light/Embedded Applications
If your applications use few threads and/or if the performance is not critical, Firebird is a good choice as has a small footprint (a few megabytes only for a full installation) and supports the most important features (full transactional support, foreign keys, etc).
Multi-Threaded Applications
For heavily threaded applications, 3 databases stand out: MySQL, Postgresql and DB2.
These databases provide the best performance and scale better than the rest.
Database Specific Notes
- Informix: Even after spending some time tweaking Informix configuration, we could not get it to perform well. Beware of these issues if you are considering moving to Informix. Informix can be used in high transaction setups, but it does require extra care and maintenance.
- DB2: Unlike Informix, DB2 worked very well in most cases, without requiring any extra work.
It also supports many features not available in Postgresql and MySQL. - MySQL: MySQL is a very popular database which is well supported on open-source systems, but it may be worth at least testing your applications with Postgresql or Firebird (or with another MySQL engine like InnoDB).
Testing & Profiling
Finally, the best thing that one can do for increasing performance is testing and profiling.
Testing could be the decisive rational proof needed to make the switch to another database system or simply another table layout.
Profiling your application can always turn up some unidentified bottlenecks and unnecessary calls.
