*** 2,8 ****
{link: http://www.sqlite.org/speed.html here}.
My modified version is attached to this page.
Please note that I'm not a TCL programmer and my mods are just poor hacks that
! were needed to make tests run on all databases. If you see any errors in there that would influence the outcome of tests, please post your findings here.
All databases were installed with default settings. In case of SQLite, I used
binaries available from this site.
--- 2,9 ----
{link: http://www.sqlite.org/speed.html here}.
My modified version is attached to this page.
Please note that I'm not a TCL programmer and my mods are just poor hacks that
! were needed to make tests run on all databases. If you see any errors in there that would influence the
! outcome of tests, please post your findings here.
All databases were installed with default settings. In case of SQLite, I used
binaries available from this site.
***************
*** 23,29 ****
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 {link: http://www.checksum.org/cso/downloads/ from here}.
If you want an explanation next to numbers, I'd recommend reading through
{link: http://www.sqlite.org/speed.html original tests}. Most of what's said
--- 24,31 ----
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 {link: http://www.checksum.org/
! cso/downloads/ from here}.
If you want an explanation next to numbers, I'd recommend reading through
{link: http://www.sqlite.org/speed.html original tests}. Most of what's said
***************
*** 39,47 ****
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
--- 41,51 ----
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
***************
*** 59,72 ****
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
tests.{linebreak}
! 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.{linebreak}
! 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.
!
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
--- 63,88 ----
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
tests.{linebreak}
! 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.{linebreak}
! 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.
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
***************
*** 77,83 ****
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.
On to the tests now.
--- 93,106 ----
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.
On to the tests now.
***************
*** 109,116 ****
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');<br>
<i>... 24997 lines omitted</i><br>
! INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');<br>
! INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
--- 132,141 ----
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');<br>
<i>... 24997 lines omitted</i><br>
! INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty
! seven');<br>
! INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred
! thirty');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
***************
*** 130,136 ****
CREATE INDEX i3 ON t3(c);<br>
<i>... 24998 lines omitted</i><br>
INSERT INTO t3 VALUES(24999,442549,'four hundred forty two thousand five hundred forty nine');<br>
! INSERT INTO t3 VALUES(25000,423958,'four hundred twenty three thousand nine hundred fifty eight');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
--- 155,162 ----
CREATE INDEX i3 ON t3(c);<br>
<i>... 24998 lines omitted</i><br>
INSERT INTO t3 VALUES(24999,442549,'four hundred forty two thousand five hundred forty nine');<br>
! INSERT INTO t3 VALUES(25000,423958,'four hundred twenty three thousand nine hundred fifty
! eight');<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
***************
*** 276,282 ****
UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
! UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
--- 302,309 ----
UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;<br>
<i>... 24996 lines omitted</i><br>
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;<br>
! UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE
! a=25000;<br>
COMMIT;<br>
</blockquote><table border=0 cellpadding=0 cellspacing=0>
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|