Great writeup. Now on the wiki at: http://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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [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 > > > _______________________________________________ > 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
