Peter De Rijk uttered:
----------On Friday 27 October 2006 15:31, [EMAIL PROTECTED] wrote:
When a table is indexed, INSERT performance is logorithmic in the
number of rows in the table and linear in the number of indices.
This is because entries have to be inserted into the index in
sorted order (otherwise it wouldn't be an index). And each
insert thus requires a binary search.
If your index becomes very large so that it no longer fits
in your disk cache, then the binary search can involve a lot
of disk I/O which can make things really slow. The usual
work-around here is to keep a much smaller staging table into
which you do your inserts and then periodically merge the
staging table into the main table. This makes your queries
more complex (and slower) since you are now having to
search multiple tables. But it does speed up inserts.
I have checked more fine grained (results and code at the end of the
mail), and it seems indeed that the extreme growth of time needed
for inserts only starts at around 160000 rows (on my system). Before
that, it also rises, but much much more slowly. As this indeed
looked like a cache effect, I tried setting the default_cache_size
to something big (20000). This was enough to remove the bend (at
least till 2000000 rows), so that the increase in time kept going
at the same slower pace as for the smaller data sizes.
You might want to try increasing the page size as well as the size of the
page cache. For index pages, this'll increase the btree fanout, reducing
the btree depth for a given number of entries, and reducing the pressure
on the cache when inserting new entries.
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
