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: dbmail-bounces dbmail.org [mailto:dbmail-bouncesdbmail.org] 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
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
