I partially explained in my last email.
Let me try to summarize everything.
WITHOUT innodb_file_per_table:
- you have one shared tablespace for all the tables. This gives you the
advantage of limiting the space your db will be using (unless you activate
the autoextend) but give you the disadvantage of preallocating all the space
you'll be using
- the problem everyone is having is because on the autoextend. Actually
it's conceptually wrong to use the autoextend when you have a single
tablespace, because whenever your DB will grow, the tablespace will grow,
but it will never get smaller, even when you delete a table or when you run
an optimize
- the optimize table issue is pretty simple. When you run optimize table,
it simple re-create the whole table you're creating, writing all the datas
sequentially. This gives you a certain degree of speed when you'll be
accessing datas later, but may be a problem because at a certain moment (a
second before the optimize finish) you'll end up having 2 copies of the same
table: the old one, and the new temporary one that will be renamed as the
new one. If you are using the shared tablespace with the autoextend this
will probably mean that your shared tablespace will GROW because the amount
of datas will double, and when the optimize will finish the old table will
be deleted and all the hypothetically-free space will just sit there waiting
to be used. This is the key point. InnoDB doesn't free the disk space
because it just wait for the space to be used again, because it's designed
to be used in an environment where you preallocate the space for the db. So
since you preallocated it, why should you care about freeing it?
WITH innodb_file_per_table:
- you have one (actually two) file per each InnoDB table. Each table/index
file will stay in the database directory (which _to me_ appears as another
big advantage)
- when you add a table you get another .idb file. Whenever your table grow,
the idb file grows, and your filesystem space decrease
- the optimize process here gets interesting. When you run optimize table
the innodb engine will start to create a NEW .idb file with a temporary
name, using only the space it actually needs to store the datas. When the
optimize table has ended, it will delete the old .idb file and rename the
temporary one to the correct name. this mean that if your old table's .idb
file had grown up to 3, 4, 5, 100 GB but you have only 100 MB of datas in
it, the new .idb file will be 100MB while the one that will be deleted was
3, 4, 5, 100GB.
That's it in term of space. In terms of speed or whatever else I can't say
if there's any advantage as I haven't done any testing myself, but what I
can assure you for sure is that, having any .idb in every directory you can
mount directory from different HDs or RAIDs for different DBs thus having
better racing conditions within the same MySQL server.
Probably you could achieve the same result having more than one shared
tablespace, but frankly I have no experience with this.
Summarizing everything:
- If you have a single DB server: use a shared InnoDB tablespace
preallocating the space and disabling the autoextend. Using the optimize
table will give you a better optimization of the tables, and you'll have no
problem with the space as it's already allocated up to a fixed size
- If you have a machine with various tasks going on, like a mail server,
web server, db server and whatever, use the innodb_file_per_table. Usigon
the optimize table you'll reclaim your space back whenever you delete
anything or whenever any table will significantly decrease in size.
Doubt? Question? Fear? Panic?
-Messaggio originale-
Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Per conto
di Peter Rabbitson
Inviato: martedì 31 luglio 2007 16.52
A: DBMail mailinglist
Oggetto: [Dbmail] Advantages of innodb_file_per_table (was: Table
dbmail_messages is full)
Hi,
Sorry for hijacking the thread. Can someone clarify what is the
advantage of using innodb_file_per_table versus one infinitely growing
tablespace?
Thanks
Peter
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
