Thanks! Ok, will do :-) On Tue, Jul 31, 2007, Andrea Brancatelli <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: > > 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 > > _______________________________________________ > 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
