Thread: SpeedComparison
Has anyone here seen this one before? Do the values appear realistic? http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison
On 2/11/06, Andrej Ricnik-Bay wrote: > Has anyone here seen this one before? Do the values > appear realistic? > > http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison The values appear to originate from an intrsinsically flawed test setup. Just take the first test. The database has to do 1000 commits. That means 1000 I/O operations. There is no way that a 7200 RPM disk can do that in the time that that test says it took. It is reasonable to say that a disk can do 1 I/O operation per rotation, which means that any test result below 9 seconds is untrustworthy. Jochem
Andrej Ricnik-Bay wrote: > Has anyone here seen this one before? Do the values > appear realistic? > > http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison Some of the particularly bad test results for PostgreSQL may be related to using the default memory configuration and never having run ANALYZE. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Jochem van Dieten <jochemd@gmail.com> writes: > On 2/11/06, Andrej Ricnik-Bay wrote: >> http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison > The values appear to originate from an intrsinsically flawed test setup. > Just take the first test. The database has to do 1000 commits. That > means 1000 I/O operations. There is no way that a 7200 RPM disk can do > that in the time that that test says it took. It is reasonable to say > that a disk can do 1 I/O operation per rotation, which means that any > test result below 9 seconds is untrustworthy. Disk lying about write-complete, no doubt. Of course that probably affects all the databases about the same. The fact that it's on Windows is probably handicapping us noticeably, considering that that port still isn't well optimized. Test 8's problem is most likely lack of an ANALYZE --- although when I tried to duplicate it, I got a bitmap index scan, which shouldn't be horrendously slow. There's something fishy about that one. regards, tom lane
On Sat, Feb 11, 2006 at 08:06:56PM -0500, Tom Lane wrote: > Jochem van Dieten <jochemd@gmail.com> writes: > > On 2/11/06, Andrej Ricnik-Bay wrote: > >> http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison > > > The values appear to originate from an intrsinsically flawed test setup. > > > Just take the first test. The database has to do 1000 commits. That > > means 1000 I/O operations. There is no way that a 7200 RPM disk can do > > that in the time that that test says it took. It is reasonable to say > > that a disk can do 1 I/O operation per rotation, which means that any > > test result below 9 seconds is untrustworthy. > > Disk lying about write-complete, no doubt. Of course that probably > affects all the databases about the same. > > The fact that it's on Windows is probably handicapping us noticeably, > considering that that port still isn't well optimized. > > Test 8's problem is most likely lack of an ANALYZE --- although when > I tried to duplicate it, I got a bitmap index scan, which shouldn't be > horrendously slow. There's something fishy about that one. FWIW, here's a thread about this test: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg12945.html Part of the problem seems to be related to psql; he was able to run test 8 in about 5 seconds using pgAdmin: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg12955.html -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> >Andrej Ricnik-Bay wrote: > > Has anyone here seen this one before? Do the values > > appear realistic? > > > > http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison > >Some of the particularly bad test results for PostgreSQL may be related >to using the default memory configuration and never having run ANALYZE. > The center point of this isn't missing ANALYZE, but so was used PostgreSQL on windows. There is propably bug in test no. 8. I hadn't this problem on Linux. Others PostgreSQL is comparable with Firebird. If you look on this test - it's only simple SELECT or max. join two tables. There isn't space for PostgreSQL force. When I did test for self join 4 tables, PostgreSQL was better then MySQL and Firebird Pavel _________________________________________________________________ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
On Sat, Feb 11, 2006 at 09:21:43PM +0100, Jochem van Dieten wrote: > On 2/11/06, Andrej Ricnik-Bay wrote: > > Has anyone here seen this one before? Do the values > > appear realistic? > > > > http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison > > The values appear to originate from an intrsinsically flawed test setup. > > Just take the first test. The database has to do 1000 commits. That > means 1000 I/O operations. There is no way that a 7200 RPM disk can do > that in the time that that test says it took. It is reasonable to say > that a disk can do 1 I/O operation per rotation, which means that any > test result below 9 seconds is untrustworthy. > > Jochem > Jochem, If the heads of the disk are in the right location, you could easily do more than 1 commit per disk revolution so the values over 2 seconds could actually be valid. 9 seconds would be worst case of 1 commit per revolution. Ken
> On 2/11/06, Andrej Ricnik-Bay wrote: >> Has anyone here seen this one before? Do the values >> appear realistic? >> >> http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison > > The values appear to originate from an intrsinsically flawed test setup. > > Just take the first test. The database has to do 1000 commits. That > means 1000 I/O operations. There is no way that a 7200 RPM disk can do > that in the time that that test says it took. It is reasonable to say > that a disk can do 1 I/O operation per rotation, which means that any > test result below 9 seconds is untrustworthy. As I have posted on this group many times, modern hard disks are very complex beasts, it is difficult to predict what is happening on them. If write caching is enabled, and some IDE hard disks lie as to whether or not it is, you *never* know.
Kenneth Marshall <ktm@is.rice.edu> writes:
> If the heads of the disk are in the right location, you could easily do
> more than 1 commit per disk revolution so the values over 2 seconds could
> actually be valid. 9 seconds would be worst case of 1 commit per revolution.
No, because a commit in PG involves appending to the WAL log, which
means that in the normal case a commit will have to rewrite the same
sector of WAL that the previous commit did. Barring some weird
remap-on-the-fly scheme, you're going to wait for the disk to come all
the way round for that. Hence, any reported sustained average much
larger than one commit per revolution has to be regarded as probably
phony.
Sometimes you can improve on this using the commit_delay parameters to
gang multiple commits into one physical write, but it's tough to do,
and we already know that this person didn't do any parameter-tuning
whatsoever, and in any case there's no improvement unless you are
committing multiple transactions concurrently.
BTW, the *real* knock against this test methodology is that it's testing
a single serial transaction stream, which is not what PG is designed to
shine at. Someday I'd like to see one of these "I can write a database
benchmark" guys actually test concurrent updates...
regards, tom lane