The are various performance tests around the net concerning the innodb_file_per_table topic but I think they are basically very useless.
There is a wide bunch of factors that can have a strong influence on the real usage of both situation. Let me draw a couple: 1) preallocating the space gives you (well, may give you) better distribution across the physical drive... as long as you preallocate the space when the drive is empty. In this case you'll get less interaction between the "real" filesystem and the database as basically the DB's data's is always in the same place. 2) on the contrary having the different .idb files per each table will have a strong interaction with the rest of the filesystem. If you have a lot of files coming and going on your drive you'll probably have an high fragmentation of free space, so the growing .idb will get fragmentized as well. In general we could do some benchmarks but I think they will be totally useless because the ballpark is so much different from situation to situation. Testing it with nothing else than MySQL running will give you a very wrong environment, while testing it with "something else" running will give something that hardly reflects on other people usage. Maybe we can do DOZENS of benchmark and then try to draw a medium value ;-) Personally I think it all comes down to easiness of administration, and as of this _I_ had much better feelings with file_per_table. Opinions are like balls: everyone's got their own. -----Messaggio originale----- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Per conto di Peter Rabbitson Inviato: martedì 31 luglio 2007 22.26 A: DBMail mailinglist Oggetto: Re: R: [Dbmail] Advantages of innodb_file_per_table (was:Table dbmail_messages is full) Andrea Brancatelli wrote: > > <snipped a very detailed explanation> > > 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? > > Excellent writeup! So basically it helps avoid juggling around 50GB+ files. One question though - does it make any difference in performance? You said that you have not done any benchmarking - did anyone else on the list? When one has 15GB of email, performance counts. _______________________________________________ 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
