All databases were installed with default settings. In case of SQLite, I used binaries available from this site.
Tests were run on 1.6GHz Sempron with 1GB of ram and 7200rpm SATA disk running Windows 2000 + SP4 with all updates applied.
Original test script was changed to allow for some slight modifications of
generated SQL to enable all db engines to understand it. In case of MySQL I
had to change CREATE TABLE statements to be able to USE InnoDB and MyISAM
storage engines and to turn off autocommit mode in case of InnoDB engine
(even though docs claim that BEGIN should do that automatically, but it seems
that's not the case). In case of Firebird I had to change BEGIN to SET
Other then that, all databases get exactly the same SQL to execute.
All SELECTs are run 3 times in a row and average time is displayed in results.
I don't know how to flush disk buffers on windows. This script used sync command available on Linux to do that. If you know how to do something like that on windows, please post here. You might check out NTSync from from here. On Windows you could use Sysinternals sync.
If you want an explanation next to numbers, I'd recommend reading through original tests. Most of what's said there applies here too.
I suspect what servers are doing behind the scenes in test 6 is something like this:
CREATE INDEX t1_b ON t1(b); CREATE INDEX t2_b ON t2(b); SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b; DROP INDEX t1_b; DROP INDEX t2_b;
Above query, when run on same db as query from test 6, completed in 0.5 seconds instead of 14+ seconds. Actually it was even a tiny bit faster then the time from test 7, but this could be attributed to varying runtime conditions.
I have no idea what's wrong with Postgres in test 8. If you do, and you think that SQL being fed to it makes that particular test unfair to Postgres, please speak up.
A Speak UP: Postgres is designed to run on a shoebox for the default install. It will always perform badly when used in the default configuration for test suites like this. Postgres only has what you call sync mode to ensure ACID compliance. Also Postgres decides how to execute a query based on the data stored in its stats database. That data is only updated by running the "Analyze" command. Other databases may track those statistics differently. In non transactional insert test, it is difficult to see how a 7200 RPM drive can do 1000 I/O operation in 0.7 secords. The sync should force each transaction to disk, which is each INSERT operation.
Reply: conf file for Postgres has been tuned a bit since test have been run.
I'm attaching conf files for all databases that have it so anyone can review
them and suggest changes to make all databases use approximately same hardware
resources and function same in terms of ACID compliance for sync tests. Once
some input on all of the databases has been gathered, I'll rerun the
Is it common for Postgres in real world scenario to run ANALYZE before each query? If so, I'll update the test script to run ANALYZE before each test.
Test 1 is very strange, I agree. Hypothetical perfect database would take more then 8 seconds to complete that test if it used only one fsync per transaction.
Another speak UP :-) : Analyze tables after bulk inserts or a few % or more rows being updated etc. The stats generated will allow the planner to choose the most effective query plan for SELECTs - a classic example is a bulk loaded table with a few million records; if never previously analyzed, the planner might think there are only 100 rows, and thus choose a sequential scan of the table over the index scan that might be far more appropriate given the actual table size. Also be aware that PostgreSQL shines under high levels of concurrent load. Consider expanding the tests so that you have tens of connections both reading and writing data in multiple transactions simultaneously. Expand that to hundreds or thousands of connections on more hefty hardware.
Reply: As for ANALYZE, I'll see what I can do to add it to test script after
significant table changes. That should be OK I guess. I plan to include the
time cost of ANALYZE as part of those tests for Postgress. I consider this to
fair since no other db gets any special treatment like this and AFAICT ANALYZE
is very fast.
No doubt, Postgres would shine in high concurrency tests but you're unlikely to be interested in SQLite if you need hundreds of simultaneous writers/readers. So, for now, I have no intention of running such tests.
Few notes about the results:
- Time is mesured in seconds, it represents wallclock time.
- nosync in case of SQLite means that SQLite is running with PRAGMA synchronous=OFF;. In case of MySQL it means that table type is MyISAM.
- sync in case of SQLite means that SQLite is running with PRAGMA synchronous=FULL;. In case of MySQL it means that table type is InnoDB.
Comment: You can turn off syncing (ie: nosync) in PostgreSQL by setting 'fsync = false' in the postgresql.conf. This isn't a setting to be done lightly, of course, but it might make for a more sensible comparison to the other databases where you're turing sync on/off. Additionally, it is reasonably common to run analyze frequently on tables which are changing alot. PostgreSQL in general thinks about a table 'in use' where rows are inserted, updated, and deleted during the lifetime of the table while the general 'running size' and statistics are reasonably constant. What that's about is that you might not run analyze before every query because the overall statisitcs don't change that much, but you might run 'vacuum analyze' nightly to mark old rows as being replacable and update the statistics. For a test like this it does make sense to analyze after changes to the tables are made. If tables are reused (and rows are updated or deleted) then vacuum and/or vacuum full should be run. Lack of running analyze is probably the problem with Test 8, Postgres isn't using the index because it's going off the 'default' statistics, which indicates there's only 100 rows or some such and in a case like that a sequential scan is faster. An additional nicety might be to re-run the Postgres tests with 'explain analyze' for each query and provide that output as well to verify the query plan being used.
Reply: nosync tests were run simply to give better insight to SQLite
developers and users. MySQL was thrown into the mix there because, AFAIK, its
very common setup for MySQL to be run like that. Other then that, I'm mostly
interested in sync performance since that's the most common mode of operation.
Except maybe for MySQL, I'm not sure if this changed in last 3 years or so.
Comment: I pulled down your postgresql.conf and noticed you turned row level
statistics on. This will have a negative impact on all tests which have a lot
of queries. Why did you do this?
On another note I would suggest running prepared statement versions of some of the tests on databases that support them.
Reply: I don't think I changed it. It probably installed like that by default. Only changes I made are the ones suggested on sqlite mailing list. Here they are for a reference:
shared_buffers=10000 effective_cache_size=100000 work_mem=10000 vacuum_cost_delay=50 autovacuum=on autovacuum_vacuum_scale_factor=0.2
Comment on Reply: The default installation of Postgres 8.x definitely (I just checked it) have
#stats_row_level = false
in their default configs, so you should disable the row stats collector and rerun the tests.
On to the tests now.
- speedtest.tcl 9602 bytes added by anonymous on 2006-Feb-07 04:52:02 UTC.
TCL script used to run the tests
- my.ini 9249 bytes added by anonymous on 2006-Feb-12 02:54:22 UTC.
- postgresql.conf 14045 bytes added by anonymous on 2006-Feb-12 02:54:35 UTC.
- firebird.conf 19729 bytes added by anonymous on 2006-Feb-12 02:54:51 UTC.