MySQL Forums :: MyISAM :: Storage capacity

Advanced Search

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
Storage capacity 12555 phil speakman 05/12/2005 02:10AM
Re: Storage capacity 5335 Ingo Strüwing 05/12/2005 03:30AM
Re: Storage capacity 4559 phil speakman 05/12/2005 09:24AM
Re: Storage capacity 4187 white pearl 12/13/2005 07:39PM
Re: Storage capacity 3995 Ingo Strüwing 12/14/2005 04:09AM
Re: Storage capacity 4017 Jacky Shu 12/16/2005 01:43PM
Re: Storage capacity 4203 Ben Hollingsworth 12/19/2005 12:06PM
Re: Storage capacity 4231 Jacky Shu 12/19/2005 01:29PM
Re: Storage capacity 6143 Sajit Kurup 06/19/2007 02:57AM
Re: Storage capacity - catalog of limits 13526 Rick James 08/30/2008 02:26PM
Re: Storage capacity - catalog of limits 3397 Rick James 04/17/2009 10:08PM
Re: Storage capacity 3696 Sajit Kurup 06/19/2007 03:05AM
Re: Storage capacity 3961 Raja Subbiah 04/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.