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
