MySQL database size

Question

Microsoft SQL Server has a nice feature, which allows a database to be automatically expanded, when it becomes full. In MySQL I understand that a database is in fact a directory with a bunch of files corresponding to various objects. Does it mean that a concept of database size is not applicable and a mysql database can be as big as available disk space allows without any additional concern? If yes, is this behavior the same across different storage engines?

Answer 1

what you describe is roughly true for MyISAM tables. for InnoDB tables the picture is different, and more similar to what other DBMSs do: one (or a few) big file with complex internal structure for the whole server. to optimize it, you can use a whole disk (or partition) as a file. (at least in unix-like systems, where everything is a file)

Answer 2

It depends on the engine you're using. A list of the ones that come with MySQL can be found here.

MyISAM tables have a file per table. This file can grow to your file system's limit. As a table gets larger, you'll have to tune it as there's index and data size optimizations that limit the default size. Also, this MyISAM documentation page says:

There is a limit of 2^32 (~4.295E+09) rows in a MyISAM table. If you build MySQL with the --with-big-tables option, the row limitation is increased to (2^32)^2 (1.844E+19) rows. See Section 2.16.2, “Typical configure Options”. Binary distributions for Unix and Linux are built with this option.

InnoDB can operate in 3 different modes: using innodb table files, using a whole disk as a table file or using innodb_file_per_table.

• Table files are pre-created per your MySQL instance. You typically create a large amount of space and monitor it. When it starts filling up, you need to configure another file and restart your server. You can also set it to autoextend, so that it will add a chunk of space to the last table file when it starts to fill up. I typically don't use this feature, as you never know when you'll take the performance hit for extending the table. This page talks about configuring it.
• I've never used a whole disk as a table file, but it can be done. Instead of pointing to a file, I believe you point your InnoDB table files at the un-formatted, unmounted device.
• innodb_file_per_table makes InnoDB tables act like MyISAM tables. Each table gets its own table file. Last time I used this, the table files did not shrink if you deleted rows from them. When a table is dropped or altered, the file resizes.

The Archive engine is a gzipped MyISAM table.

A memory table doesn't use disk at all. In fact, when a server restarts, all the data is lost.

Merge tables are like a poor man's partitioning for MyISAM tables. It causes a bunch of identical tables to be queried as if there were one. Aside from the FRM table definition, no files exist other than the MyISAM ones.

CSV tables are wrappers around CSV files. The usual file system limits apply here. They are not too fast, since they can't have indexes.

I don't think anyone uses BDB any more. At least, I've never used it. It uses a Berkly database as a back end. I'm not familiar with its restrictions.

Federated tables are used to connect to and query tables on other database servers. Again, there is only an FRM file.

The Blackhole engine doesn't store anything locally. It's used primarily for creating replication logs and not for actual data storage, since there is no data storage :)

MySQL Cluster is completely different: it stores just about everything in memory (recent editions allow disk storage) and is very different from all the other engines.