Vincent Ferretti wrote:
> I need help!
>
> I'm making some testing with innodb tables and there're some issues I
> don't understand.
>
> I created a small tablespace of 10M:
>
> innodb_data_file_path = ibdata1:10M;
>
> Then I created this simple database:
>
> CREATE DATABASE trace_db;
> use trace_db;
>
> CREATE TABLE chromatogram (
> id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
> trace MEDIUMBLOB NOT NULL,
> PRIMARY KEY (id),
> ) TYPE=INNODB;
>
> Then I wrote a little Perl script to fill this table:
>
> my $fileName = "A03_017.ab1.bz2";
> for (my $i = 1; $i <= $max; $i++) {
> my $query = qq {insert into chromatogram set trace =
> LOAD_FILE("$fileName")};
> $dbh->do($query) || die;
> }
>
> Here is the problem: Although the size of the file is 63Kb, I can do
> only a maximum of 84 insertions (which represent a total of 5.3M).
> After that, I get the error message that the table is full.
>
> Command "show table status" gives me a data_length of 5.9M which is
> only 59% of the total tablespace allocated.
>
> I want to have a database containing hundreds thousand of those files.
> I can't obviously afford to loose 41% of my tablespace.
>
> Can someone explain to me those numbers and what should I do to
> optimize my disk space.
>
> Thank you very much
>
> Vincent
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Vincent,
Is there some particular reason you want to store the files inside of the
database?
There are many advantages to storing the files using the file system and storing
the
path in the database. One of the best arguments I've seen for doing it this way
is the
fact that the application getting data from the db can get the path and then
spawn a "child"
process to fetch the file while retreiving another path from the database. If you
pull the files
directly from the database, the application will have to wait until the database
returns each file
before it can begin fetching another.
Just a suggestion.
walt
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
