New Linux Benchmarks Of SilverStone's HDDBOOST

[Artemis3]

Thanks but no thanks, this looks like what those "Hybrid" disks attempt to do, when in fact i would rather see those as two separate drives; that way i can place / and read only stuff in the SSD, and the bulk data in the mechanical device. Having some algorithm trying to "guess" what to place in the SSD is not efficient, this ain't no cache (and ram is better for that) :P

Also the i/o scheduler will get in the way, SSDs should use noop.

[droidhacker]

It isn't true. SQLite is 350 times faster with transactions
See http://www.sqlite.org/speed.html

1) "The numbers here are old enough to be nearly meaningless."
*** that means that sqlite, mysql, and pgsql have ALL CHANGED since those tests were made back in.... 2001!!!!

2) TRANSACTIONS ARE NOT SYNCHRONIZATIONS! They are TOTALLY UNRELATED!

Be aware that a sensible database server does NOT do what you are showing there.... just because it is a transaction does NOT mean that you shouldn't synchronize to disk. You are just synchronizing to a temporary place instead of the final place. Running in a transaction should NOT affect performance.

Note these results here:
MySQL: 0.114
SQLite 2.7.6: 13.061
SQLite 2.7.6 (nosync): 0.223
** you see what difference synchronization makes? It runs 58.57 TIMES as fast without synchronization. This is entirely because of the write latency of mechanical disks!

Point of interest... they claim that it is running "nearly as fast as mysql" when sync is disabled.... according to who? Seems to me that mysql is completely destroying it in this test running at virtually DOUBLE THE SPEED.

On test 2 in that page, you throw the queries into a transaction, which disables (in the case of sqlite, but not mysql or pgsql) synchronization, and you see the sync and nosync values converge, sure. That's nice, but there's no synchronization, so its just writing to buffers and is therefore NOT A VALID TEST OF DISK PERFORMANCE.

In other words... test 2 is NOT APPLICABLE.

Further, it appears that this OLD version of sqlite was actually QUITE BROKEN when it comes to transactions. It should STILL be synchronizing, to a TEMPORARY DATABASE. What would happen if you have 32 MB of RAM (common for the old days when those tests were made) and ran a 100 MB transaction? Kablammo! LOL. And what would happen with that transaction? Well you wouldn't even have evidence that the transaction failed since it isn't synchronizing.

Further:
This type of transaction is a little bit weird. In some cases, it can be used for nightly mass updates (which you aren't going to be doing with sqlite to begin with... note that this is typically done by locking the database rather than running it as a transaction) but in most cases, it is used to ensure data integrity when dealing with multiple clients connecting simultaneously, ensuring that you don't, for example, have a select happening DURING an UPDATE and sending back invalid data. A typical transaction has to happen FAST so that the database doesn't go out during that transaction.... so you might do a transaction with 5 queries in it followed by a sync. So now start thinking about thousands of 5-query transactions, each followed by a sync. Well now you've completely lost any performance advantage that you perceived as being associated with transactions.

In other words: It doesn't help to prove performance under conditions that are never going to happen.

Transactions are NOT a method of boosting performance. They are a method of ensuring DATABASE CONSISTENCY by (a) applying all queries according to established parameters or rolling back, OR (b) by ensuring that all data in the database is consistent by ensuring that queries are performed in a reasonable ORDER (i.e. you perform an update while someone else performs an insert related to the same data -- bad things can happen).

[Chrome]

1) I'm developing application which use SQLite. SQLite is still much faster with transactions.
2) I have never told that transactions are synchronizations. SQLite isn't database server.
Test results from SQLite can't be considered as results from database server.
SQLite is used in applications like Firefox and Chrome.
Test2 is relevant test of disk performance because this is how every real application should work. PgSQL was also much faster in that test with transactions - 28 times. Compare Test1 and Test2.
Inserting data using 5-query transactions will be approx. 5-times faster than without transactions.

[Chrome]

There is error in previous post.
PgSQL was also much faster in that test with transactions - 23 times.

[droidhacker]

1) I'm developing application which use SQLite. SQLite is still much faster with transactions.

Under unreal conditions.

2) I have never told that transactions are synchronizations. SQLite isn't database server.

You implied it.

Test results from SQLite can't be considered as results from database server.

You're right.. because sqlite is about as useful as a text file.

SQLite is used in applications like Firefox and Chrome.

... which don't actually need it. It also resulted in some serious PROBLEMS for firefox, mostly dealt with by now, but still memorable.

Test2 is relevant test of disk performance because this is how every real application should work.

No, it isn't how any application should work. NO application is going to be running 25k inserts into a database like that! The results are MEANINGLESS because nobody is EVER going to do that!

PgSQL was also much faster in that test with transactions - 28 times. Compare Test1 and Test2.

Which clearly identifies a DEFECT in that old version of pgsql.

Inserting data using 5-query transactions will be approx. 5-times faster than without transactions.

No it won't... because of those 5-queries, most likely only ONE of them will be an update or insert. The performance of read-only queries is unaffected by synchronization.

[Chrome]

What do you mean with: under unreal conditions?
Text file isn't optimized for tables and doesn't support SQL queries.
SQLite caused problems to Firefox because Firefox is legacy software with lame design which have to be redesigned.
If your application doesn't do 25k inserts and does only 1 insert or update in 5 queries, doesn't mean that other applications don't do that.
Those numbers only identifies defect in MySQL, not in PgSQL.

[droidhacker]

What do you mean with: under unreal conditions?
Text file isn't optimized for tables and doesn't support SQL queries.
SQLite caused problems to Firefox because Firefox is legacy software with lame design which have to be redesigned.
If your application doesn't do 25k inserts and does only 1 insert or update in 5 queries, doesn't mean that other applications don't do that.
Those numbers only identifies defect in MySQL, not in PgSQL.

If you're doing 25k inserts, you should be using a REAL DATABASE.

And no, for anything sqlite is suitable for, you DON'T NEED sql or tables. Sqlite doesn't match any particular needs. It is overkill for things that can be handled by a simple file, it is completely out to lunch to try using it for tables with thousands of entries!

And finally, the fact that pgsql isn't synchronizing is a defect that is NOT present in mysql -- that is the defect that is proven by those numbers. Raw speed isn't everything! Sometimes it doesn't hurt to actually save the data!