1. MySQL vs SQLite3 performance

    Monday, June 14 2010 16:20

    I did some testing to compare MySQL with SQLite3 on simple operations:

    • display index
    • create an entry
    • edit entry
    • publish
    • delete entry

    Here is the result:

    mysql vs sqlite3 graph

    As you can see, most operations are slower on SQLite3 and “write” operations (create/update/delete) are really bad.

    All this to say: do not use SQLite3 in production !

    Gaspard Bucher

    comments

    1. Tuesday, June 15 2010 10:16 CGA

      Hi Gaspard,

      Thanks for this, definitely clear on not to use SQlite.

      Just a hint/request: it would be cool if you could do this kind of comparision with PostgreSQL too (when available of course).

      Thanks

    2. Tuesday, June 15 2010 18:51 Gaspard Bucher

      I did these tests because I felt that the server was acting strangely. To properly compare MySQL and PostgreSQL, I think we need some proper benchmarking with average request time for each CRUD operation.

      We will definitely do this when we enter the super cool “optimization” phase (between 1.0 and 1.5).

    3. Thursday, June 17 2010 13:08 hudson

      could please provide the test cases?
      http://www.sqlite.org/speed.html
      That is old document, but I trust more in that..

    4. Thursday, June 17 2010 14:45 Eduardo

      Yes, i knew that SQLlite3 is really not so good as MySQL.

      What use can we give to SQLlite3 ?
      • I was thinking that this is small and portable…
      • Some really small applications.
    5. Friday, June 18 2010 02:33 German

      The graph is useless if you don’t publish the source code for your comparison!!! You should do like PolePos.org (where teams like datanucleus, db4o, jdbc participate) and provide an open test.

    6. Tuesday, June 22 2010 15:50 Gaspard Bucher

      @German, @hudson, to reproduce the test case, you need to have a webserver running apache with Zena and then, in the default layout and an empty website, you execute the operations above.

      It’s not a test meant to compare SQLite3 with MySQL, it’s just an indication that you should avoid using SQLite3 for a complex rails application like Zena.

      When we have a stable release for Zena and we enter optimization phase, we will have proper benchmarks comparing different setups (with PostgreSQL).

    7. Thursday, June 24 2010 15:39 Gryph0n

      I second German. Unless you show the source to the tests, the results will lack credibility.

    8. leave a comment