MySQL Forums
Forum List  »  MyISAM

Re: Storage capacity - catalog of limits
Posted by: Rick James
Date: August 30, 2008 02:26PM

Here are some limits:
* 16TB of data for InnoDB table (maybe more).
* _Default_ limit of 4G bytes or rows in MyISAM table -- See CREATE TABLE and ALTER TABLE for increasing that limit.
* MyISAM hard limit is perhaps 2**56 bytes -- you won't hit that in my lifetime.
* key_buffer_size (cache for MyISAM indexes) -- hard limit of 4GB (even on 64-bit machines), but you can have more than one.
* innodb_buffer_pool_size -- more than 4GB, suspect at least 8TB.
* 32-bit OS limits -- depends on OS -- ram-related things are limited to somewhere between 1.6GB and 3GB.
* Number of rows -- might be 4G limit if compiled for 32-bit.
* Various stats -- silently wrap at 4G if compiled for 32-bit.
* File sizes -- mostly OS limit, which (today) tends to be plenty huge.
* Query Cache -- don't know; probably not efficient to come anywhere near 4GB.
* Number of databases/tables/files -- no hard limit, but millions will cause trouble.
* Number of connections open at once (cf Max_connections) -- OS will stop you from opening more than RAM can handle (hundreds, maybe thousands).
* One column of table -- 4GB (LONGTEXT, LONGBLOB)
* InnoDB row size -- 8K? (*TEXT and *BLOB count for only 768 bytes until InnoDB "plugin")
* Index size -- 1000?
* VARCHAR/VARBINARY -- used to be 255, now 65535 bytes(?)
* Number of views, stored procedures, etc -- ?
* Length of db/table/view/sp/column names
* Columns per table: 4K; possibly fewer if their names are lengthy.
* Columns per table (InnoDB): 1000.
* Index prefix: MyISAM: 1000, InnoDB: 767
* GRANTs: Unlimited except by MyISAM table limits?
* JOINs, including implicit ones in Views: 61
* Integer (BIGINT): 0-2**64 (UNSIGNED) or +/- 2**63 (SIGNED)

Today, if you have a big db, I recommend using 64-bit OS and compiling MySQL for 64-bit.

Options: ReplyQuote


Subject
Views
Written By
Posted
12621
May 12, 2005 02:10AM
5354
May 12, 2005 03:30AM
4576
May 12, 2005 09:24AM
4204
December 13, 2005 07:39PM
4016
December 14, 2005 04:09AM
4038
December 16, 2005 01:43PM
4222
December 19, 2005 12:06PM
4272
December 19, 2005 01:29PM
6160
June 19, 2007 02:57AM
Re: Storage capacity - catalog of limits
13755
August 30, 2008 02:26PM
3713
June 19, 2007 03:05AM
3983
April 09, 2008 06:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.