More benchmarks

classic Classic list List threaded Threaded
27 messages Options
12
Reply | Threaded
Open this post in threaded view
|

More benchmarks

Nemanja Corlija
I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdSQL.

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

--
Nemanja Corlija <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Jim C. Nasby
On Tue, Feb 07, 2006 at 05:57:24AM +0100, Nemanja Corlija wrote:
> I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdSQL.
>
> Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison

For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show?
What changes have you made to the default postgresql.conf?
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Nemanja Corlija
On 2/7/06, Jim C. Nasby <[hidden email]> wrote:
> For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show?
test=# EXPLAIN ANALYZE SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;
                                                  QUERY PLAN

---------------------------------------------------------------------------------------------------------
------
 Aggregate  (cost=6.02..6.03 rows=1 width=4) (actual time=0.183..0.185
rows=1 loops=1)
   ->  Index Scan using i2b on t2  (cost=0.00..6.01 rows=1 width=4)
(actual time=0.030..0.108 rows=12 loo
ps=1)
         Index Cond: ((b >= 0) AND (b < 100))
 Total runtime: 0.510 ms
(4 rows)

> What changes have you made to the default postgresql.conf?
None.

--
Nemanja Corlija <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Jim C. Nasby
On Tue, Feb 07, 2006 at 07:06:26AM +0100, Nemanja Corlija wrote:

> On 2/7/06, Jim C. Nasby <[hidden email]> wrote:
> > For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show?
> test=# EXPLAIN ANALYZE SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;
>                                                   QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------------
> ------
>  Aggregate  (cost=6.02..6.03 rows=1 width=4) (actual time=0.183..0.185
> rows=1 loops=1)
>    ->  Index Scan using i2b on t2  (cost=0.00..6.01 rows=1 width=4)
> (actual time=0.030..0.108 rows=12 loo
> ps=1)
>          Index Cond: ((b >= 0) AND (b < 100))
>  Total runtime: 0.510 ms
> (4 rows)

Well, it expected 1 row and got 12. In this example it probably doesn't
matter, but for other parameters it probably will.

Did you happen to do an analyze?

> > What changes have you made to the default postgresql.conf?
> None.

Well, that certainly won't help things... at a minimum, on your machine,
you should change the following:
shared_buffers=10000
effective_cache_size=100000

The following should also help:
work_mem=10000
vacuum_cost_delay=50
autovacuum=on
autovacuum_vacuum_scale_factor=0.2
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Nemanja Corlija
On 2/7/06, Jim C. Nasby <[hidden email]> wrote:

> On Tue, Feb 07, 2006 at 07:06:26AM +0100, Nemanja Corlija wrote:
> > On 2/7/06, Jim C. Nasby <[hidden email]> wrote:
> > > For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show?
> > test=# EXPLAIN ANALYZE SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;
> >                                                   QUERY PLAN
> >
> > ---------------------------------------------------------------------------------------------------------
> > ------
> >  Aggregate  (cost=6.02..6.03 rows=1 width=4) (actual time=0.183..0.185
> > rows=1 loops=1)
> >    ->  Index Scan using i2b on t2  (cost=0.00..6.01 rows=1 width=4)
> > (actual time=0.030..0.108 rows=12 loo
> > ps=1)
> >          Index Cond: ((b >= 0) AND (b < 100))
> >  Total runtime: 0.510 ms
> > (4 rows)
>
> Well, it expected 1 row and got 12. In this example it probably doesn't
> matter, but for other parameters it probably will.
>
> Did you happen to do an analyze?
Nope. All databases are run as default as possible. And, they all get
same scripts to execute.

> > > What changes have you made to the default postgresql.conf?
> > None.
>
> Well, that certainly won't help things... at a minimum, on your machine,
> you should change the following:
> shared_buffers=10000
> effective_cache_size=100000
>
> The following should also help:
> work_mem=10000
> vacuum_cost_delay=50
> autovacuum=on
> autovacuum_vacuum_scale_factor=0.2
Sure, I could do that. But then I'd also need to tune all other
databases to make things fair and that's not really what I intended to
do here. I want to keep things as "out of the box" as possible.

--
Nemanja Corlija <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Jim C. Nasby
On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote:
> > Did you happen to do an analyze?
> Nope. All databases are run as default as possible. And, they all get
> same scripts to execute.

Which means PostgreSQL can only take a wild stab at what's in the
database.

> > > > What changes have you made to the default postgresql.conf?
> > > None.
> >
> > Well, that certainly won't help things... at a minimum, on your machine,
> > you should change the following:
> > shared_buffers=10000
> > effective_cache_size=100000
> >
> > The following should also help:
> > work_mem=10000
> > vacuum_cost_delay=50
> > autovacuum=on
> > autovacuum_vacuum_scale_factor=0.2
> Sure, I could do that. But then I'd also need to tune all other
> databases to make things fair and that's not really what I intended to
> do here. I want to keep things as "out of the box" as possible.

Then you should just drop PostgreSQL from the tests, because they're not
doing anyone any good. It's pretty well known that the default
postgresql.conf is meant to allow for bringing the database up on a
machine with very minimal hardware. It's the equivalent to using MySQL's
minimum configuration file.

It certainly doesn't seem unreasonable to tweak a handful of parameters
for each database. I wouldn't even consider this to be tuning;
everything I recommended is a fairly standard set of adjustments.
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Andrew Piskorski
In reply to this post by Nemanja Corlija
On Tue, Feb 07, 2006 at 07:06:26AM +0100, Nemanja Corlija wrote:
> On 2/7/06, Jim C. Nasby <[hidden email]> wrote:

> > Did you happen to do an analyze?
> Nope. All databases are run as default as possible. And, they all get
> same scripts to execute.

Then your results for PostgreSQL are utterly meaningless.  (And in
this case, the poor performance reflects poorly on you, the DBA, not
on PostgreSQL.)

> > What changes have you made to the default postgresql.conf?
> None.

Then your test results are bogus.  Last I heard, the default value in
postgresql.conf were intended to simply work AT ALL on the widest
possible range of hardware, operating systems, etc., and are NOT
recommended values for any actual production use.

Yes, I that sounds very foolish of the PostgreSQL folks to me too, but
there you have it.  Using PostgreSQL properly REQUIRES that you modify
those settings.

> Sure, I could do that. But then I'd also need to tune all other
> databases to make things fair and that's not really what I intended to
> do here. I want to keep things as "out of the box" as possible.

The above is not exactly "tuning", it is basic "Running PostgreSQL
101" type stuff.

Look at it this way: Different databases have different installation
requirements.  Editing postgresql.conf and collecting statistics with
vacuum analyze are simply part of the required install procedure for
PostgreSQL.  If you don't do the basic stuff like that, your database
is simply misconfigured, and any performance results you generate are
worthless - because in the real world, NO ONE with any clue at all
would ever run their database that way.

Minimally, you need to install and configure each of the databases
you're benchmarking in the manner expected of a competent but
non-expert user of that tool.  Naturally this various for different
databases.

If you find the process of properly installing and configuring the
database software overly complicated or poorly documented, then that's
a perfectly legitimate complaint, but it has nothing to do with
performance benchmarking.

--
Andrew Piskorski <[hidden email]>
http://www.piskorski.com/
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Nemanja Corlija
In reply to this post by Jim C. Nasby
On 2/7/06, Jim C. Nasby <[hidden email]> wrote:

> On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote:
> > > Well, that certainly won't help things... at a minimum, on your machine,
> > > you should change the following:
> > > shared_buffers=10000
> > > effective_cache_size=100000
> > >
> > > The following should also help:
> > > work_mem=10000
> > > vacuum_cost_delay=50
> > > autovacuum=on
> > > autovacuum_vacuum_scale_factor=0.2
> > Sure, I could do that. But then I'd also need to tune all other
> > databases to make things fair and that's not really what I intended to
> > do here. I want to keep things as "out of the box" as possible.
>
> Then you should just drop PostgreSQL from the tests, because they're not
> doing anyone any good. It's pretty well known that the default
> postgresql.conf is meant to allow for bringing the database up on a
> machine with very minimal hardware. It's the equivalent to using MySQL's
> minimum configuration file.

OK, I've changed above settings but now I get even worse performance.
265.223 seconds.
File I've edited is C:\Program Files\PostgreSQL\8.1\data\postgresql.conf
AFAICT that's the one. Then I've restarted postgres. I guess that
should load new settings?
Then I ran VACUUM ANALYZE t2;

Hmmm, now I ran that same script from pgAdmin and it completed in 5 seconds.
I guess its reasonable to assume that psql is actually the bottleneck
here. I tried redirecting to file but that was a minute ago and it's
still running. Any ideas?
--
Nemanja Corlija <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

D. Richard Hipp
In reply to this post by Nemanja Corlija
Nemanja Corlija <[hidden email]> wrote:
> I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdS=
> QL.
>
> Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison
>

Thanks for your hard work, Nemanja!  This is useful information.

Looks like I need to work on Test 6 some, huh?  Your suggestion
that the servers are creating a temporary index to do the join
was my first throught too.  I wonder if I should look into teaching
that trick to SQLite.  Do you think you might add another test
(6b?) that repeated the same join after indexing one of the join
columns?  You do this at Test 13, but at that point the tables contain
different data, I think.

Other people have posted that the PostgreSQL tests are meaningless
because the database is not tuned.  I am someone sympathetic to
their complaints.  If you have time, I think it would be useful
to show both a tuned and and untuned version for PostgreSQL.  It
is also useful to know that PostgreSQL needs tuning in order to
run well.

It is also interesting to note that PostgreSQL get significantly
slower in Test 13 (join with an index) versus Test 6 (the same
join without an index).  What is that about?  Firebird shows the
same effect, but less dramatically.  Could it be a difference in
the data that the tables hold at that point.  Test 6B proposed
above really would be instructive here, I think.

I also wonder if MySQL and Firebird would benefit from tuning.
The MySQL people are rather laid back and probably will say
something like "whatever" if asked.  The Firebird crowd, on the
other hand, tend to be edgy and I suspect we will be hearing
some pointed commentary from them in the near future.

Is there any chance of seeing additional information such as
the amount of disk space used by the various databases or the
amount of RAM consumed?  These values would be more difficult
to arrive at, but will be helpful to many people, I think, if
available.

Thanks again for your hard work in preparing these benchmarks!
Even if you do nothing else with them, your work so far has been
a big help.
--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Clay Dowling
In reply to this post by Jim C. Nasby

Jim C. Nasby said:

> Well, that certainly won't help things... at a minimum, on your machine,
> you should change the following:
> shared_buffers=10000
> effective_cache_size=100000
>
> The following should also help:
> work_mem=10000
> vacuum_cost_delay=50
> autovacuum=on
> autovacuum_vacuum_scale_factor=0.2

Jim,

I just want to say thanks for providing these tuning parameters.  I not
currently using your product, but I hope to in the near future for a
larger scale version of my own product.  Performance tuning is something
of an arcane art from my perspective, so getting any help on it is highly
desirable.

Clay Dowling
--
Simple Content Management
http://www.ceamus.com

Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Jay Sprenkle
In reply to this post by Nemanja Corlija
On 2/6/06, Nemanja Corlija <[hidden email]> wrote:
> I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdSQL.
>
> Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison

You might want to put a legend on your results tables so it's clear
what the numbers represent. I assume these are times but I didn't see
anything so far that said.
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Jim C. Nasby
In reply to this post by Nemanja Corlija
On Tue, Feb 07, 2006 at 10:08:23AM +0100, Nemanja Corlija wrote:

> On 2/7/06, Jim C. Nasby <[hidden email]> wrote:
> > On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote:
> > > > Well, that certainly won't help things... at a minimum, on your machine,
> > > > you should change the following:
> > > > shared_buffers=10000
> > > > effective_cache_size=100000
> > > >
> > > > The following should also help:
> > > > work_mem=10000
> > > > vacuum_cost_delay=50
> > > > autovacuum=on
> > > > autovacuum_vacuum_scale_factor=0.2
> > > Sure, I could do that. But then I'd also need to tune all other
> > > databases to make things fair and that's not really what I intended to
> > > do here. I want to keep things as "out of the box" as possible.
> >
> > Then you should just drop PostgreSQL from the tests, because they're not
> > doing anyone any good. It's pretty well known that the default
> > postgresql.conf is meant to allow for bringing the database up on a
> > machine with very minimal hardware. It's the equivalent to using MySQL's
> > minimum configuration file.
>
> OK, I've changed above settings but now I get even worse performance.
> 265.223 seconds.
> File I've edited is C:\Program Files\PostgreSQL\8.1\data\postgresql.conf
> AFAICT that's the one. Then I've restarted postgres. I guess that
> should load new settings?
> Then I ran VACUUM ANALYZE t2;
>
> Hmmm, now I ran that same script from pgAdmin and it completed in 5 seconds.
> I guess its reasonable to assume that psql is actually the bottleneck
> here. I tried redirecting to file but that was a minute ago and it's
> still running. Any ideas?

Hrm, that's rather odd. What does top show when it's running through
psql? Are the test scripts available for download? I'll try this on my
machine as well...
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Jim C. Nasby
In reply to this post by D. Richard Hipp
On Tue, Feb 07, 2006 at 08:07:53AM -0500, [hidden email] wrote:
> It is also interesting to note that PostgreSQL get significantly
> slower in Test 13 (join with an index) versus Test 6 (the same
> join without an index).  What is that about?  Firebird shows the
> same effect, but less dramatically.  Could it be a difference in
> the data that the tables hold at that point.  Test 6B proposed
> above really would be instructive here, I think.

Well, I'm a bit skeptical as to the usefulness of that test, since if
I'm reading things correctly it's essentially a cartesian product. In
any case, it's very likely that the lack of analysis and default
parameters resulted in a bad query plan. The output of EXPLAIN ANALYZE
would be most instructive.

> I also wonder if MySQL and Firebird would benefit from tuning.
> The MySQL people are rather laid back and probably will say
> something like "whatever" if asked.  The Firebird crowd, on the
> other hand, tend to be edgy and I suspect we will be hearing
> some pointed commentary from them in the near future.

FWIW, I wouldn't really consider the changes I suggested 'tuning', as
they're rather off-the-cuff based strictly on my experience and limited
knowledge as to the workload. Personally, I'd prefer if PostgreSQL would
at least provide multiple sample configs, but c'est la vie.

AFAIK MySQL ships with a few different config files, so presumably
choosing the appropriate one would be equivalent to what I provided for
PostgreSQL.

BTW, has anyone looked at adding SQLite support to any of the DBT
benchmarks? http://sourceforge.net/projects/osdldbt
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Jim C. Nasby
In reply to this post by Jim C. Nasby
On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote:
> Hrm, that's rather odd. What does top show when it's running through
> psql? Are the test scripts available for download? I'll try this on my
> machine as well...

I see theh tcl now... is TCL piping into psql, or are there a set of raw
files you could post or send me? If you're piping from TCL, I'd be
curious to see what the difference is if you run this manually. For
these large data sets I also think it's not very reflective of the
database to send the result set all the way back through the client,
since that's not very representative of the real world. In the case of
PostgreSQL, a good alternative would be

SELECT count(*) FROM (
        SELECT t1.a  FROM ...
    ) a
;

But I'm not sure if all the other databases support that.
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Jim C. Nasby
In reply to this post by Clay Dowling
On Tue, Feb 07, 2006 at 10:04:43AM -0500, Clay Dowling wrote:

>
> Jim C. Nasby said:
>
> > Well, that certainly won't help things... at a minimum, on your machine,
> > you should change the following:
> > shared_buffers=10000
> > effective_cache_size=100000
> >
> > The following should also help:
> > work_mem=10000
> > vacuum_cost_delay=50
> > autovacuum=on
> > autovacuum_vacuum_scale_factor=0.2
>
> Jim,
>
> I just want to say thanks for providing these tuning parameters.  I not
> currently using your product, but I hope to in the near future for a
> larger scale version of my own product.  Performance tuning is something
> of an arcane art from my perspective, so getting any help on it is highly
> desirable.

You're welcome. Just to clarify, PostgreSQL isn't really a Pervasive
product; we just have a bundled installer and offer support and
services, but it's all the same as the community code. When it comes to
tuning, http://www.powerpostgresql.com/PerfList and
http://www.revsys.com/writings/postgresql-performance.html are a couple
places to start looking.

Finally, and luckily this applies to SQLite as well so this doesn't get
too off topic :), PLEASE seek help/advice BEFORE spending a bunch of
money on a big server! All too often I see people who spend a load of $$
on equipment they didn't need or won't be able to utilize because they
didn't do enough research before hand. Granted, I'm biased since I make
money on consulting, but the amount of money I've seen people spend on
needless hardware would often buy a pretty good chunk of my time.
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

D. Richard Hipp
In reply to this post by Jim C. Nasby
"Jim C. Nasby" <[hidden email]> wrote:

> On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote:
> > Hrm, that's rather odd. What does top show when it's running through
> > psql? Are the test scripts available for download? I'll try this on my
> > machine as well...
>
> I see theh tcl now... is TCL piping into psql, or are there a set of raw
> files you could post or send me? If you're piping from TCL, I'd be
> curious to see what the difference is if you run this manually. For
> these large data sets I also think it's not very reflective of the
> database to send the result set all the way back through the client,
> since that's not very representative of the real world. In the case of
> PostgreSQL, a good alternative would be
>
> SELECT count(*) FROM (
>         SELECT t1.a  FROM ...
>     ) a
> ;
>
> But I'm not sure if all the other databases support that.

SQLite supports the syntax above, FWIW.

Your theory is that SQLite does well because it doesn't need to
send data back and forth between the client and server?  You're
probably right.  On the other hand, what good is the data if
the client never sees it?

You'll notice that SQLite seems to do particularly well on the
tests that involve a lot of SQL.  For example, test 2 with
25000 separate INSERT statements.  SQLite ran in 0.7 seconds
versus 16.5 seconds for PostgreSQL.  Probably a big fraction of
the 16.5 seconds PostgreSQL used were in transmitting all of
that SQL over a socket to the server.  I'm wondering if the
use of prepared statements might reduce the performance gap
somewhat?  Notice that when doing an equally large insert in
Test 12, but an insert that involves much less SQL and parsing,
that PostgreSQL is actually a little faster than SQLite.

Any volunteers to run the experiment?  Jim?

Another explanation for the poor performance by PostgreSQL in
test 2 might be the PostgreSQL parser is less efficient.  Or
perhaps the PostgreSQL spends a lot more time trying to
optimize - which can pay off on a big query but is a drag for
lots of silly little inserts.  A test using prepared statements
would help clearify the issue.

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Nemanja Corlija
On 2/7/06, [hidden email] <[hidden email]> wrote:
> SQLite supports the syntax above, FWIW.
>
> Your theory is that SQLite does well because it doesn't need to
> send data back and forth between the client and server?  You're
> probably right.  On the other hand, what good is the data if
> the client never sees it?
I don't think that can be it. pgAdmin is also just a client AFAIK and
it got all results back in just 5 seconds. I'd blame psql for poor
results here.
psql's output is 500KB in that test, while SQLite's is 72KB. That is
the difference far far smaller then the one measured in time. FWIW,
psql.exe, cmd.exe and System are using up all CPU for the duration of
that test. So perhaps psql is not really fast on windows?

> You'll notice that SQLite seems to do particularly well on the
> tests that involve a lot of SQL.  For example, test 2 with
> 25000 separate INSERT statements.  SQLite ran in 0.7 seconds
> versus 16.5 seconds for PostgreSQL.  Probably a big fraction of
> the 16.5 seconds PostgreSQL used were in transmitting all of
> that SQL over a socket to the server.  I'm wondering if the
> use of prepared statements might reduce the performance gap
> somewhat?  Notice that when doing an equally large insert in
> Test 12, but an insert that involves much less SQL and parsing,
> that PostgreSQL is actually a little faster than SQLite.
>
> Any volunteers to run the experiment?  Jim?
>
> Another explanation for the poor performance by PostgreSQL in
> test 2 might be the PostgreSQL parser is less efficient.  Or
> perhaps the PostgreSQL spends a lot more time trying to
> optimize - which can pay off on a big query but is a drag for
> lots of silly little inserts.  A test using prepared statements
> would help clearify the issue.
I'd bet on Postgres' optimizer taking up too much time. Since we have
25000 separate statements there, I wouldn't be surprised if postgres
is trying to optimize each of them individually. And that just has to
take some time. Prepared statements should really help postgres here
since that would be only one statement to analyze/optimize and there
would be less data to send through socket.
But, keep in mind that postgres completed the test in 5 seconds, not
200 as stated in test results. Results will be updated once we come up
with better way to run them. I'm referring to psql problem here.

Originally, I wanted to run these tests on PHP 5.1 since PDO has
support for all databases involved and would enable us to use prepared
statements and skip cmd line client all together. And db specific
drivers should be very thin layer on top of PDO it self. Or did I get
this thinness wrong?
That might still happen in the future though.

--
Nemanja Corlija <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Nemanja Corlija
In reply to this post by D. Richard Hipp
On 2/7/06, [hidden email] <[hidden email]> wrote:
> Looks like I need to work on Test 6 some, huh?  Your suggestion
> that the servers are creating a temporary index to do the join
> was my first throught too.  I wonder if I should look into teaching
> that trick to SQLite.  Do you think you might add another test
> (6b?) that repeated the same join after indexing one of the join
> columns?  You do this at Test 13, but at that point the tables contain
> different data, I think.
I guess I'll just copy test 13 to where test 8 is right now. Though
those test numbers will likely create a lot of confusion that way.

> Other people have posted that the PostgreSQL tests are meaningless
> because the database is not tuned.  I am someone sympathetic to
> their complaints.  If you have time, I think it would be useful
> to show both a tuned and and untuned version for PostgreSQL.  It
> is also useful to know that PostgreSQL needs tuning in order to
> run well.
At first, I wanted to get by cheaply by not tuning anything. But yeah,
tuning each database would be a sensible thing to do after all.

> It is also interesting to note that PostgreSQL get significantly
> slower in Test 13 (join with an index) versus Test 6 (the same
> join without an index).  What is that about?  Firebird shows the
> same effect, but less dramatically.  Could it be a difference in
> the data that the tables hold at that point.  Test 6B proposed
> above really would be instructive here, I think.
I suspect that postgres and firebird just lost track of what's in the
database at that point and they could really use some ANALYZE at that
point. Just an assumption though.

> I also wonder if MySQL and Firebird would benefit from tuning.
> The MySQL people are rather laid back and probably will say
> something like "whatever" if asked.  The Firebird crowd, on the
> other hand, tend to be edgy and I suspect we will be hearing
> some pointed commentary from them in the near future.
I'd like to gather some input on this and then rerun test after that.
So if you have some tips for optimizing any database involved, please
speak up.

> Is there any chance of seeing additional information such as
> the amount of disk space used by the various databases or the
> amount of RAM consumed?  These values would be more difficult
> to arrive at, but will be helpful to many people, I think, if
> available.
I don't have much of those information ATM, but I will tell you that
SQLite3 used 3MB, while SQLite2 used 3.8MB for the duration of test 6
and they both used up all CPU.
Will try to gather information about database sizes next time. But I
don't know how can I reliably measure memory usage on windows.

--
Nemanja Corlija <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Nemanja Corlija
In reply to this post by Jay Sprenkle
On 2/7/06, Jay Sprenkle <[hidden email]> wrote:
> You might want to put a legend on your results tables so it's clear
> what the numbers represent. I assume these are times but I didn't see
> anything so far that said.
Its time in seconds.
"sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of
MySQL it signifies usage of InnoDB engine.
"nosync" is the opposite, of course synchronous=OFF; and MyISAM engine.

--
Nemanja Corlija <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: More benchmarks

Jay Sprenkle
> > You might want to put a legend on your results tables so it's clear
> > what the numbers represent. I assume these are times but I didn't see
> > anything so far that said.
> Its time in seconds.
> "sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of
> MySQL it signifies usage of InnoDB engine.
> "nosync" is the opposite, of course synchronous=OFF; and MyISAM engine.

Thanks, :)

 I thought it was important to update the page so visitors stumbling
upon it were clear what it said.
12