Using SQLite as a database backend for a lightweight server

At home I use an rather old (antique some may say) computer (PII@350, 256RAM) as a web server and for testing purposes. All went fine until one of the pages hosted was hit by the "digg effect" and about 15 users wanted to see that page every second. The main problem was that I used MySQL to store some access logs and generate some real-time statistics. Physical memory was limited and Apache and MySQL soon started to fight for every remaining byte and eventually the page was unavailable.
After this experience I started to look for a alternative for the database storage and I found an interesting one: SQLite. SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine. Here are some features you may find interesting:

• Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
• Zero-configuration - no setup or administration needed.
• Implements most of SQL92. (Features not supported)
• A complete database is stored in a single disk file.
• Simple, easy to use API.
• Sources are in the public domain.

The next step was to make it work with my server setup (Apache and PHP5) and it was easy. To test it's relative performance to MySQL I implemented a simple scenario using both database engines: a page that logs each access (IP, DATETIME), looks for current user location (using a ip2country table of about 50.000 rows) and then shows some real time statistics (unique visitors and visitors grouped by country). Then I measured the average load time for this page using 5 concurrent user connections over a 10 minutes period. Here are the results (keep in mind that using SQLite saved about 80 to 128 MB of physical memory which can now be used by Apache or by the operating system):

SQLite is here a bit faster than MySQL using INNODB and both are a lot slower than MySQL/MYISAM. The main advantage of SQLite and INNODB is that you can use ACID transactions. I found SQLite being a good replacement for MySQL in most of my personal projects but before you start using it you should know the situations where SQLite works well and where it doesn't.