Horton

Thread

  1. RE: Berkeley DB...

    Vadim Mikheev <vmikheev@sectorbase.com> — 2000-05-25T16:09:26Z

    > Times for the insert loop:
> 14   MySQL-MyISAM
> 23   PostgreSQL (no fsync)
> 53   MySQL-BDB (with fsync -- don't know how to turn it off yet)

PostgreSQL 6.5.3; -B 16384; SUN Ultra10 with some IDE disk.

1. with fsync, all inserts in single transaction:     73 sec
2. with fsync, use COPY:                               3 sec
3. without fsync, use COPY:                            3 sec
4. without fsync, all inserts in single transaction:  71 sec
5. without fsync, each insert in own transaction:    150 sec

Do you see difference for INSERT/COPY in 1./2.? Shouldn't we try
to speed up our PARSER/PLANNER, keeping in mind that WAL will speed
up our storage sub-system?!

Also, 4. & 5. show that transaction begin/commit take too long time.
Could you run your test for all inserts in single transaction?

(If we want to test storage sub-system, let's do our test un-affected
by other ones...)

> The select:
> 0.75  MySQL-MyISAM
> 0.77  MySQL-BDB
> 2.43  PostgreSQL

select a from foo order by a

didn't use index in my case, so I've run

select a from foo where a >= 0 also.

1. ORDER:                0.74
2. A >= 0 with index:    0.73
3. A >= 0 without index: 0.56

Note that I used -B 16384 (very big pool) and run queries *twice* to get
all data into pool. What size of pool did you use? 64 (default) * 8192 =
512Kb,
but size of foo is 1.5Mb...

2. & 3. show that index slows data retrieval... as it should -:)
Also, does MySQL read table itself if it can get all required
columns from index?! I mean - did your query really read *both*
index and *table*? 
PostgreSQL has to read table anyway...

Vadim

  2. Re: Berkeley DB...

    Matthias Urlichs <smurf@noris.de> — 2000-05-26T02:09:06Z

    Hi,

Mikheev, Vadim:
> Also, does MySQL read table itself if it can get all required
> columns from index?! I mean - did your query really read *both*
> index and *table*? 

Yes, and yes.

Note that this "benchmark" was much too quick-and-dirty and didn't
really say anything conclusive... we'll have to wait a bit for that.

-- 
Matthias Urlichs  |  noris network GmbH   |   smurf@noris.de  |  ICQ: 20193661
The quote was selected randomly. Really.       |        http://smurf.noris.de/
-- 
The best way to preserve a right is to exercise it,
and the right to smoke is a right worth dying for.

  3. Re: Berkeley DB...

    Mike Mascari <mascarm@mascari.com> — 2000-05-26T06:12:32Z

    Matthias Urlichs wrote:
> 
> Hi,
> 
> Mikheev, Vadim:
> > Also, does MySQL read table itself if it can get all required
> > columns from index?! I mean - did your query really read *both*
> > index and *table*?
> 
> Yes, and yes.
> 
> Note that this "benchmark" was much too quick-and-dirty and didn't
> really say anything conclusive... we'll have to wait a bit for that.
> 
> --
> Matthias Urlichs  |  noris network GmbH   |   smurf@noris.de  |  ICQ: 20193661
> The quote was selected randomly. Really.       |        http://smurf.noris.de/
> --

Although I am a PostgreSQL zealot, I have to admit that many
PostgreSQL users have hidden behind the use of transactions in
justifying the sometimes 2 - 3 times slower execution speeds in
DML statements vs. MySQL. As Vadim points out in his comparison
of COPY vs. INSERT, something is *wrong* with the time it takes
for PostgreSQL to parse, plan, rewrite, and optimize. Now that
MySQL has transactions through Berkley DB, I think its going to
be harder to justify the pre-executor execution times. 

Just my two cents, 

Mike Mascari

  4. Re: Berkeley DB...

    Matthias Urlichs <smurf@noris.de> — 2000-05-26T08:16:31Z

    Hi,

Mike Mascari:
> DML statements vs. MySQL. As Vadim points out in his comparison
> of COPY vs. INSERT, something is *wrong* with the time it takes
> for PostgreSQL to parse, plan, rewrite, and optimize.

In my throughly unscientific opinion, the problem may well be the fact
that PostgreSQL recurses the whole process, i.e. it is looking up
attributes of one table in a bunch of other tables.

MySQL, by contrast, has three files per table -- one with the data,
one with _all_ the indices, and one .frm file with all the other
metadata you would ever want to know about a table.

That metadata file is mapped into shared memory space by the first task
that opens a table, and it stays there. The data and index files also
stay open until they're flushed.

Since MySQL is multithreaded, opening a new connection is extremely
cheap. By contrast, PostgreSQL does more than 30 open() calls when I
connect to it.(*) It's still lots faster than some other databases I might
mention, though...

Access control is done by a bunch of tables in the "mysql" database,
but these are 100% cached.

One nice side effect of this is that it's very easy to access tables
from another database. Just say "select * from foo.bar".


(*) The list:
/data//pg_options
/etc/passwd
/etc/group
/data//PG_VERSION
/data//pg_database
/data//base/test/PG_VERSION
/data//base/test/pg_internal.init
/data//pg_log
/data//pg_variable
/data//base/test/pg_class
/data//base/test/pg_class_relname_index
/data//base/test/pg_attribute
/data//base/test/pg_attribute_relid_attnum_index
/data//base/test/pg_trigger
/data//base/test/pg_am
/data//base/test/pg_index
/data//base/test/pg_amproc
/data//base/test/pg_amop
/data//base/test/pg_operator
/data//base/test/pg_index_indexrelid_index
/data//base/test/pg_operator_oid_index
/data//base/test/pg_index_indexrelid_index
/data//base/test/pg_trigger_tgrelid_index
/data//pg_shadow
/data//pg_database
/data//base/test/pg_proc
/data//base/test/pg_proc_proname_narg_type_index
/data//base/test/pg_type
/data//base/test/pg_type_oid_index
/data//base/test/pg_proc_oid_index
/data//base/test/pg_rewrite
/data//base/test/pg_user
/data//base/test/pg_attribute_relid_attnam_index
/data//base/test/pg_operator_oprname_l_r_k_index
/data//base/test/pg_class_oid_index
/data//base/test/pg_statistic
/data//base/test/pg_statistic_relid_att_index

-- 
Matthias Urlichs  |  noris network GmbH   |   smurf@noris.de  |  ICQ: 20193661
The quote was selected randomly. Really.       |        http://smurf.noris.de/
-- 
Man with hand in pocket feel cocky all day.
		-- Confucius

  5. Re: Berkeley DB...

    Hannu Krosing <hannu@tm.ee> — 2000-05-26T11:09:00Z

    Mike Mascari wrote:
> 
> 
> Although I am a PostgreSQL zealot, I have to admit that many
> PostgreSQL users have hidden behind the use of transactions in
> justifying the sometimes 2 - 3 times slower execution speeds in
> DML statements vs. MySQL. As Vadim points out in his comparison
> of COPY vs. INSERT, something is *wrong* with the time it takes
> for PostgreSQL to parse, plan, rewrite, and optimize. Now that
> MySQL has transactions through Berkley DB, I think its going to
> be harder to justify the pre-executor execution times.

We can always justify it by referring to extensibility of postgres,
which is surely part of the story

Sure we will be able to do cacheing to improve speed of 
serial inserts.
 
> Just my two cents,
> 
> Mike Mascari

  6. Re: Berkeley DB...

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-26T15:39:56Z

    Hannu Krosing <hannu@tm.ee> writes:
>> As Vadim points out in his comparison
>> of COPY vs. INSERT, something is *wrong* with the time it takes
>> for PostgreSQL to parse, plan, rewrite, and optimize.

We might have part of the story in the recently noticed fact that
each insert/update query begins by doing a seqscan of pg_index.

I have done profiles of INSERT in the past and not found any really
spectacular bottlenecks (but I was looking at a test table with no
indexes, so I failed to see the pg_index problem :-().  Last time
I did it, I had these top profile entries for inserting 100,000 rows
of 30 columns apiece:

  %   cumulative   self              self     total           
 time   seconds   seconds    calls  ms/call  ms/call  name    
 30.08    290.79   290.79                             _mcount
  6.48    353.46    62.67 30702766     0.00     0.00  AllocSetAlloc
  5.27    404.36    50.90   205660     0.25     0.25  write
  3.06    433.97    29.61 30702765     0.00     0.00  MemoryContextAlloc
  2.74    460.45    26.48   100001     0.26     0.74  yyparse
  2.63    485.86    25.41 24300077     0.00     0.00  newNode
  2.22    507.33    21.47  3900054     0.01     0.01  yylex
  1.63    523.04    15.71 30500751     0.00     0.00  PortalHeapMemoryAlloc
  1.31    535.68    12.64  5419526     0.00     0.00  hash_search
  1.18    547.11    11.43  9900000     0.00     0.00  expression_tree_walker
  1.01    556.90     9.79  3526752     0.00     0.00  SpinRelease

While the time spent in memory allocation is annoying, that's only about
ten mallocs per parsed data expression, so it's unlikely that we will be
able to improve on it very much.  (We could maybe avoid having *three*
levels of subroutine call to do an alloc, though ;-).)  Unless you are
smarter than the flex and bison guys you are not going to be able to
improve on the lex/parse times either.  The planner isn't even showing
up for a simple INSERT.  Not much left, unless you can figure out how
to write and commit a tuple with less than two disk writes.

But, as I said, this was a case with no indexes to update.

I intend to do something about caching pg_index info ASAP in the 7.1
cycle, and then we can see how much of a difference that makes...

			regards, tom lane

  7. Re: Berkeley DB...

    Mike Mascari <mascarm@mascari.com> — 2000-05-26T18:48:22Z

    Tom Lane wrote:
> 
> Hannu Krosing <hannu@tm.ee> writes:
> >> As Vadim points out in his comparison
> >> of COPY vs. INSERT, something is *wrong* with the time it takes
> >> for PostgreSQL to parse, plan, rewrite, and optimize.
> 
> We might have part of the story in the recently noticed fact that
> each insert/update query begins by doing a seqscan of pg_index.
> 
> I have done profiles of INSERT in the past and not found any really
> spectacular bottlenecks (but I was looking at a test table with no
> indexes, so I failed to see the pg_index problem :-().  Last time
> I did it, I had these top profile entries for inserting 100,000 rows
> of 30 columns apiece:
> 
>   %   cumulative   self              self     total
>  time   seconds   seconds    calls  ms/call  ms/call  name
>  30.08    290.79   290.79                             _mcount
>   6.48    353.46    62.67 30702766     0.00     0.00  AllocSetAlloc
>   5.27    404.36    50.90   205660     0.25     0.25  write
>   3.06    433.97    29.61 30702765     0.00     0.00  MemoryContextAlloc
>   2.74    460.45    26.48   100001     0.26     0.74  yyparse
>   2.63    485.86    25.41 24300077     0.00     0.00  newNode
>   2.22    507.33    21.47  3900054     0.01     0.01  yylex
>   1.63    523.04    15.71 30500751     0.00     0.00  PortalHeapMemoryAlloc
>   1.31    535.68    12.64  5419526     0.00     0.00  hash_search
>   1.18    547.11    11.43  9900000     0.00     0.00  expression_tree_walker
>   1.01    556.90     9.79  3526752     0.00     0.00  SpinRelease
> 
> While the time spent in memory allocation is annoying, that's only about
> ten mallocs per parsed data expression, so it's unlikely that we will be
> able to improve on it very much.  (We could maybe avoid having *three*
> levels of subroutine call to do an alloc, though ;-).)  Unless you are
> smarter than the flex and bison guys you are not going to be able to
> improve on the lex/parse times either.  The planner isn't even showing
> up for a simple INSERT.  Not much left, unless you can figure out how
> to write and commit a tuple with less than two disk writes.
> 
> But, as I said, this was a case with no indexes to update.
> 
> I intend to do something about caching pg_index info ASAP in the 7.1
> cycle, and then we can see how much of a difference that makes...
> 
>                         regards, tom lane

It will be interesting to see the speed differences between the
100,000 inserts above and those which have been PREPARE'd using
Karel Zak's PREPARE patch. Perhaps a generic query cache could be
used to skip the parsing/planning/optimizing stage when multiple
exact queries are submitted to the database? I suppose the cached
plans could then be discarded whenever a DDL statement or a
VACUUM ANALYZE is executed? The old Berkeley Postgres docs spoke
about cached query plans *and* results (as well as 64-bit oids,
amongst other things). I'm looking forward to when the 7.1 branch
occurs... :-)

Mike Mascari

  8. Re: Berkeley DB...

    Zeugswetter Andreas <andreas.zeugswetter@telecom.at> — 2000-05-26T19:43:20Z

    > >   %   cumulative   self              self     total
> >  time   seconds   seconds    calls  ms/call  ms/call  name
> >  30.08    290.79   290.79                             _mcount
> >   6.48    353.46    62.67 30702766     0.00     0.00  AllocSetAlloc
> >   5.27    404.36    50.90   205660     0.25     0.25  write
> >   3.06    433.97    29.61 30702765     0.00     0.00  MemoryContextAlloc
> >   2.74    460.45    26.48   100001     0.26     0.74  yyparse
> >   2.63    485.86    25.41 24300077     0.00     0.00  newNode
> >   2.22    507.33    21.47  3900054     0.01     0.01  yylex
> >   1.63    523.04    15.71 30500751     0.00     0.00  PortalHeapMemoryAlloc
> >   1.31    535.68    12.64  5419526     0.00     0.00  hash_search
> >   1.18    547.11    11.43  9900000     0.00     0.00  expression_tree_walker
> >   1.01    556.90     9.79  3526752     0.00     0.00  SpinRelease
> > 
> > While the time spent in memory allocation is annoying, that's only about
> > ten mallocs per parsed data expression, so it's unlikely that we will be
> > able to improve on it very much.  (We could maybe avoid having *three*
> > levels of subroutine call to do an alloc, though ;-).)  Unless you are
> > smarter than the flex and bison guys you are not going to be able to
> > improve on the lex/parse times either.  The planner isn't even showing
> > up for a simple INSERT.  Not much left, unless you can figure out how
> > to write and commit a tuple with less than two disk writes.

> It will be interesting to see the speed differences between the
> 100,000 inserts above and those which have been PREPARE'd using
> Karel Zak's PREPARE patch

If we believe the above output, the win won't be very noticeable. It is the 
writes (and the Allocs) we have to get rid of.
The above is much faster if you do:
begin work;
100000 inserts ....;
commit work;

Andreas

  9. Re: Berkeley DB...

    Peter Eisentraut <peter_e@gmx.net> — 2000-05-28T22:18:01Z

    Tom Lane writes:

> I have done profiles of INSERT in the past and not found any really
> spectacular bottlenecks

I am still at a loss on how to make profiles. The latest thing that
happened to me is that the postmaster gave me a `Profiling timer expired'
message and never started up. Any idea?


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

  10. Re: Berkeley DB...

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-29T02:15:29Z

    Peter Eisentraut <peter_e@gmx.net> writes:
> I am still at a loss on how to make profiles. The latest thing that
> happened to me is that the postmaster gave me a `Profiling timer expired'
> message and never started up. Any idea?

Dunno ... PROFILE=-pg works for me ...

Normally there's a special startup file that the compiler is supposed to
know to link instead of the usual crt0.o, when you link with -pg.
Possibly there's something wrong with yours?

			regards, tom lane

  11. Re: Berkeley DB...

    Karel Zak <zakkr@zf.jcu.cz> — 2000-05-29T14:57:04Z

    > It will be interesting to see the speed differences between the
> 100,000 inserts above and those which have been PREPARE'd using
> Karel Zak's PREPARE patch. Perhaps a generic query cache could be

My test:

	postmaster:	-F -B 2000	
	rows:		100,000 
	table:		create table (data text);
	data:		37B for eache line
	
	--- all is in one transaction

	native insert:		66.522s
	prepared insert:	59.431s	    - 11% faster	

 
IMHO parsing/optimizing is relative easy for a simple INSERT.
The query (plan) cache will probably save time for complicated SELECTs 
with functions ...etc. (like query that for parsing need look at to system
tables). For example:

	insert into tab values ('some data' || 'somedata' || 'some data');

	native insert:		91.787s
	prepared insert:	45.077s     - 50% faster

	(Note: This second test was faster, because I stop X-server and
	postgres had more memory :-)


 The best way for large and simple data inserting is (forever) COPY, not
exist faster way. 

 pg's path(s) of query:
 
 native insert:		parser -> planner -> executor -> storage
 prepared insert:	parser (for execute stmt) -> executor -> storage
 copy:			utils (copy) -> storage

> amongst other things). I'm looking forward to when the 7.1 branch
> occurs... :-)

 I too.

							Karel