I already did it. I also did some layout, take a look at the
wiki and let me
know if it's ok.
I'll try to write up some more things in the optimization
tricks section.
-----Messaggio originale-----
Da: dbmail-bounces dbmail.org [mailto:dbmail-bouncesdbmail.org] Per conto
di Aaron Stone
Inviato: martedì 31 luglio 2007 23.12
A: DBMail mailinglist
Oggetto: Re: R: R: [Dbmail] Advantages of
innodb_file_per_table (was:
Thanks! Ok, will do 
On Tue, Jul 31, 2007, Andrea Brancatelli <andreabrancatelli.it> said:
> Ok, i'll write some more rants about optimizing innodb
tomorrow if i have
> some more time.
>
> Please fix my English before reproduce it
>
> -----Messaggio originale-----
> Da: dbmail-bouncesdbmail.org [mailto:dbmail-bouncesdbmail.org] Per conto
> di Aaron Stone
> Inviato: marted� 31 luglio 2007 20.50
> A: DBMail mailinglist
> Oggetto: Re: R: [Dbmail] Advantages of
innodb_file_per_table (was:
>
> Great writeup. Now on the wiki at:
>
> ht
tp://dbmail.org/dokuwiki/doku.php?id=mysql_notes
>
> I left a space where we should include more information
about configuring
> InnoDB parameters for good performance. This does tend
to be a FAQ, and
> the bits of information we do have about it are
scattered all over the
> place.
>
> Aaron
>
>
> On Tue, Jul 31, 2007, Andrea Brancatelli <andreabrancatelli.it> said:
>
>> Just an addendum.
>>
>> With innodb_file_per_table whenever you delete a
table or a database, the
>> whole .idb file gets deleted so you instantly get
your space back. Maybe
I
>> wasn't clear enough on this in the latest lines of
my mail.
>>
>> -----Messaggio originale-----
>> Da: dbmail-bouncesdbmail.org
[mailto:dbmail-bouncesdbmail.org] Per
conto
>> di Andrea Brancatelli
>> Inviato: marted� 31 luglio 2007 17.16
>> A: 'DBMail mailinglist'
>> Oggetto: R: [Dbmail] Advantages of
innodb_file_per_table (was:
>> Tabledbmail_messages is full)
>>
>> 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: dbmail-bouncesdbmail.org
[mailto:dbmail-bouncesdbmail.org] 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
>> DBmaildbmail.org
>> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>>
>> _______________________________________________
>> DBmail mailing list
>> DBmaildbmail.org
>> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>>
>>
>> _______________________________________________
>> DBmail mailing list
>> DBmaildbmail.org
>> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>>
>
> --
>
>
>
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>
--
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
