Thread: SpeedComparison

SpeedComparison

From
Andrej Ricnik-Bay
Date:
Has anyone here seen this one before? Do the values
appear realistic?

http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison


Re: SpeedComparison

From
Jochem van Dieten
Date:
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

Re: SpeedComparison

From
Peter Eisentraut
Date:
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/


Re: SpeedComparison

From
Tom Lane
Date:
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


Re: SpeedComparison

From
"Jim C. Nasby"
Date:
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


Re: SpeedComparison

From
"Pavel Stehule"
Date:
>
>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/



Re: SpeedComparison

From
Kenneth Marshall
Date:
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


Re: SpeedComparison

From
"Mark Woodward"
Date:
> 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.




Re: SpeedComparison

From
Tom Lane
Date:
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