CODEWITHSUNDEEP
databasemariadb
Amitabh Das
Amitabh Das

Reputation: 403

max number of records can MariaDB support

What is the max number of records (mostly numeric data, each record size not more than say 1Kb, 5/6 fields at most in a table) that can be supported?

Upvotes: 8

Views: 19275

Answers (2)

Rick James
Rick James

Reputation: 142298

An InnoDB table is limited to 64TB; this might allow for 64 billion rows in one table. There is virtually no limit on the number of tables in a database, nor the number of databases in an instance of MariaDB.

If you PARTITION a table, the limit is further raised, probably into the trillions of rows. This is because each partition is essentially a separate table.

More on Limits: http://mysql.rjweb.org/doc.php/limits Most limits are never hit by any realistic application on current hardware.

Upvotes: 12

Jalil
Jalil

Reputation: 1246

Limitations on Schema

  • InnoDB tables can have a maximum of 1,017 columns. This includes virtual generated columns.
  • InnoDB tables can have a maximum of 64 secondary indexes.
  • A multicolumn index on InnoDB can use a maximum of 16 columns. If you attempt to create a multicolumn index that uses more than 16 columns, MariaDB returns an Error 1070.

Limitations on Size

  • With the exception of variable-length columns (that is, VARBINARY, VARCHAR, BLOB and TEXT), rows in InnoDB have a maximum length of roughly half the page size for 4KB, 8KB, 16KB and 32KB page sizes.
  • The maximum size for BLOB and TEXT columns is 4GB. This also applies to LONGBLOB and LONGTEXT.
  • MariaDB imposes a row-size limit of 65,535 bytes for the combined sizes of all columns. If the table contains BLOB or TEXT columns, these only count for 9 - 12 bytes in this calculation, given that their content is stored separately.
  • 32-bit operating systems have a maximum file size limit of 2GB. When working with large tables using this architecture, configure InnoDB to use smaller data files.
  • The maximum size for the combined InnoDB log files is 512GB.
  • With tablespaces, the minimum size is 10MB, the maximum varies depending on the InnoDB Page Size.

Page Sizes

Using the innodb_page_size system variable, you can configure the size in bytes for InnoDB pages. Pages default to 16KB. There are certain limitations on how you use this variable.

  • MariaDB instances using one page size cannot use data files or log files from an instance using a different page size.
  • When using a Page Size of 4KB or 8KB, the maximum index key length is lowered proportionately.

You can read a little bit more in the official documentation

Upvotes: 10

Related Questions