MySQL - Max table size

[Tom]

Hi

Version of MySQL as yet undetermined but this will be running on White
Box -

Basically whats the maximum table size in GB that MySQL allows? We are
developing an application that streams content - The streams are created
by a CMS from a raw file that gets uploaded by a user - These raw files
are stored in the db by the app. In Oracle i'm used to creating mutiple
datafiles that make up a tablespace and so no singe data file ever
really gets that big. Is the same approach applicable in MySQL and if so
how big can you go?

thanks

[Bart Van der Donck]

Tom wrote:

Basically whats the maximum table size in GB that MySQL
allows? We are developing an application that streams
content - The streams are created by a CMS from a raw
file that gets uploaded by a user - These raw files are
stored in the db by the app. In Oracle i'm used to
creating mutiple datafiles that make up a tablespace and
so no singe data file ever really gets that big. Is the
same approach applicable in MySQL and if so how big can
you go?

You are far more likely to run against system limits than MySQL quotas.
Even the obsolete MySQL v3.23 can have tables up to 64000 Gigabytes (64
Terra).

More info:
http://dev.mysql.com/doc/mysql/en/table-size.html

It has also a handy section about file size limits on various operating
systems.

--
Bart

[Ray in HK]

As per my testing, for any single table up to 10GB will obviously slow down
the performance.

Testing environment :
Red Hat 7.x
Dual P-III
Single SCSI HD with about 2000 rpm
MYISAM table type
MySQL 3.x
"Bart Van der Donck" <ba**@nijlen.com> ¦b¶l¥ó
news:11**********************@g49g2000cwa.googlegr oups.com ¤¤¼¶¼g...

Tom wrote:

Basically whats the maximum table size in GB that MySQL
allows? We are developing an application that streams
content - The streams are created by a CMS from a raw
file that gets uploaded by a user - These raw files are
stored in the db by the app. In Oracle i'm used to
creating mutiple datafiles that make up a tablespace and
so no singe data file ever really gets that big. Is the
same approach applicable in MySQL and if so how big can
you go?

You are far more likely to run against system limits than MySQL quotas.
Even the obsolete MySQL v3.23 can have tables up to 64000 Gigabytes (64
Terra).

More info:
http://dev.mysql.com/doc/mysql/en/table-size.html

It has also a handy section about file size limits on various operating
systems.

--
Bart

[Bart Van der Donck]

Ray in HK wrote:

As per my testing, for any single table up to 10GB
will obviously slow down the performance.
Testing environment :
Red Hat 7.x
Dual P-III
Single SCSI HD with about 2000 rpm
MYISAM table type
MySQL 3.x

You 're right - I'ld even suggest to start thinking about "special
arrangments" in case of tables bigger than some hundreds of MB. But not
only table size affects performance: also queries, indexing methods,
table structure, OS, hardware,... So that could depend case per case.

I didn't work with 10GB tables yet, but the slowdown that you mention
doesn't surprise me :-)

I 'ld even have serious objections about tables >1GB in "default"
production environments anyhow.

--
Bart

[Ray in HK]

In that particular project , finally Oracle has been chosen because it
support transparent "Disk level partitioning", which is a new feature since
Oracle 8.

I've forgotten the tech. term but sth like that.

Maybe MySQL dev team should consider this sort of features.
"Bart Van der Donck" <ba**@nijlen.com> ¦b¶l¥ó
news:11**********************@g44g2000cwa.googlegr oups.com ¤¤¼¶¼g...

Ray in HK wrote:

As per my testing, for any single table up to 10GB
will obviously slow down the performance.
Testing environment :
Red Hat 7.x
Dual P-III
Single SCSI HD with about 2000 rpm
MYISAM table type
MySQL 3.x

You 're right - I'ld even suggest to start thinking about "special
arrangments" in case of tables bigger than some hundreds of MB. But not
only table size affects performance: also queries, indexing methods,
table structure, OS, hardware,... So that could depend case per case.

I didn't work with 10GB tables yet, but the slowdown that you mention
doesn't surprise me :-)

I 'ld even have serious objections about tables >1GB in "default"
production environments anyhow.

--
Bart

[Bill Karwin]

Tom wrote:

Hi

Version of MySQL as yet undetermined but this will be running on White
Box -

Basically whats the maximum table size in GB that MySQL allows?

Other folks have voiced concerns about letting the tablespace file get
too large. If you use InnoDB, the default is to store all tables in one
shared tablespace file, but you have other options: you can store each
table in another single file, or you can use the shared tablespace but
tell it to split over multiple files.

See configuration options for "innodb_data_file_path" documented here:
http://dev.mysql.com/doc/mysql/en/in...iguration.html

Regards,
Bill K.

[Tom]

Other folks have voiced concerns about letting the tablespace file get
too large. If you use InnoDB, the default is to store all tables in one
shared tablespace file, but you have other options: you can store each
table in another single file, or you can use the shared tablespace but
tell it to split over multiple files.

See configuration options for "innodb_data_file_path" documented here:
http://dev.mysql.com/doc/mysql/en/in...iguration.html

ahh thanks - yes we'll be using InnoDB and noted the single tablespace
file and this was causing me concern - Think i'll be going the route of
multiple tablespace files so thanks for the link