Tom Lane wrote:
> Kevin Brown <kevin@sysexperts.com> writes:
> > It occurs to me that we could make it possible to get some of the
> > performance gains MySQL gets through its naming conventions by
> > including the type of object in the path of the object.
>
> "Performance gains"? Name one.
Instead of tables and their indexes being on the same platter, you'd
be able to put them on separate platters. Sounds like it would likely
yield a performance gain to me...
> We have been there and done that. I see no reason to go back.
I'm not proposing that we return to calling the individual files (or
the database they reside in) by name, only that we include a "type"
identifier in the path so that objects of different types can be
located on different spindles if the DBA so desires. As it is right
now, tables and indexes are all stored in the same directory, and
moving the indexes to a different spindle is an uncertain operation at
best (you get to shut down the database in order to move any
newly-created indexes, and dropping a moved index will not free the
space occupied by the index as it'll only remove the symlink).
All the current transactional operations (including things like table
rename ops) will still be transactional, with the only difference
being that instead of one directory (base/) to deal with you'd have
several (one for each type of object, thus a base/<type>/ directory
for each object type). Creating a database would mean creating a
directory for the database in each of the type directories instead of
just one in base/, and dropping it would mean removing said
directories.
It's not like we'd be losing anything by it: the only operations that
you wouldn't necessarily be able to run in a transaction are the ones
that you can't currently run in a transaction anyway, like CREATE
DATABASE.
But the benefit is that you can now safely put indexes on a different
spindle than the data. That sounds like a net win to me.
--
Kevin Brown kevin@sysexperts.com
