MySQL vs SQLite on Amazon EC2

Question

I have a Java program and PHP website I plan to run on my Amazon EC2 instance with an EBS volume. The program writes to and reads from a database. The website only reads from the same database.

On AWS you pay for the amount of IOPS (I/O requests Per Second) to the volume. Which database has the least IOPS? Also, can SQLite handle queries from both the program and website simultaneously?

Answer 1

• Which database has the least IOPS

  IOPS is going to depend largely on your workload and other factors like how much memory you have for caching rather than your database engine.

• Also, can SQLite handle queries from both the program and website simultaneously?

  Yes, but writes are serialized, or as the docs put it:

  Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time

You need to bear in mind that MySQL and SQLite are very different beasts and other factors than the ones you mention should be decisive in your choice. MySQL is a traditional RDBMS like SQL Server, Oracle, PostgreSQL etc (although it lacks some features of those databases), and SQLite isn't

Answer 2

If you are concerned about the cost of iops, which are incredibly cheap in Amazon, then you are talking about a scale which sqlite will not be able to handle. There is no direct way to compare iops between two server types without benchmarking under your exact work load. Even then, i/o is going to be a function of how well your server is tuned. Generally speaking, a true rdbms has several levels of in memory buffering to reduce i/o as much as possible. Additionally, adding caching layers in front of the DB will help.

If you expect any concurrency at all, sqlite will not be sufficient. It is really only good for development or embedded environments.