On Sun, May 13, 2001 at 06:09:12PM +0300, Heikki Tuuri wrote:
> >
> >> >Suggested improvements would be the addition of COALESCE TABLESPACE
> >>
> >> Do you mean reorganization and compaction of a tablespace? The way to
> >> do it is to dump and reload all tables in the tablespace.
> >
> >During my tests I've found that a 100MB tablespace file gets filled up
> >even if I'm doing work on a 30MB table, and I can't add another table
> >without dropping and reloading the first. It'd be nice if there was a
> >way to do this wihtout taking tables offline. The current workaround
> >is just to allocate much more tablespace than you really need.
>
> I see. The 30 MB table can grow to take up to 120 MB if the
> fillfactor of index pages (also data pages are clustered index pages)
> drops to 25 %. Also, if InnoDB has allocated a 64-page chunk to a table
> the chunk will be freed to other tables' use only when it is completely
> empty.
>
> Possible improvements:
> - We could change the page merge threshold from 25 % to 40 %. Then
> the fillfactor of the tree would stay higher. A drawback is increased
> CPU usage by merges in some circumstances.
> - We could change file space allocation for secondary indexes so that
> they would always allocate only individual pages, not 64-page chunks.
> The drawback is that then inter-table fragmentation can take place: dropping
> a table will not free complete 64-page chunks to other tables.
> - We could write a background process to reorganize and compact tables.
> A weakness is that if the database load takes 100 % of disk bandwidth,
> the background process will not run, and the user may be surprised
> by the size increase of tables.
>
> I think putting the threshold to 40 % is the easiest improvement here.
Please make such thresholds configurable. Different tables have different
usage patterns and there'll never be a value optimal for all uses.
Tim.
> It also occurred to me that ALTER TABLE can be used to reorg a table:
> ALTER it to MyISAM and then back to InnoDB.
>
> Regards,
>
> Heikki
> http://www.innodb.fi
>
> >--
> > Dan Nelson
> > [EMAIL PROTECTED]
> >
> >---------------------------------------------------------------------
> >Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> >To request this thread, e-mail <[EMAIL PROTECTED]>
> >To unsubscribe, e-mail <[EMAIL PROTECTED]>
> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
