无论对于什么样的数据库，什么样的系统，容量永远是一个扯不完的话题。如下佳文转于 http##www#noodba#com
容量规划是一个比较大的话题，在我看来，数据库应用包括两个方面：
一是存储容量，数据库的大小，表的大小等等，这后面涉及到一系列的问题，如维护，备份，容灾等等
二是性能方面的，比如说应用的负载增加5倍，数据库是否能处理？或者在下个预算周期前是否能支持。或者有促销，预计订单量会增加6倍，数据库的响应能否在一个合理的范围内等等，另外还可能牵涉到HA，系统降级等等问题。
第一个问题，也是一个经常遇到的问题，尤其是在一些初创快速成长的公司。
其实我也遇到过，如现在我们有个库，有一主几备，但是主库和备库在存储配置上有差异，导致备库容量不够，这个问题是一个很烦的问题。一开始硬件不到位，只能从数据库里的东西着手，清理一些可以清理的表，数据等等，另外，还有一个备库还跟主库不一致。还有就是催促开发迁移一些类似日志的表，这个过程真是吃力不讨好。
在数据库一开始就做好存储容量方面的规划，还是十分有用的，具体到数据库Oracle和MySQL上，估计得方法是比较类似的：
1 如果是在一个现有的系统上做规划：
对于Oracle：视图user_tables，user_indexes里就有我们需要的信息
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
HHHHH 19182515 1444702 561
MySQL的对应字典信息里也有：
mysql> show table status from test like ‘ccccc’\G
*************************** 1. row ***************************
Name: ccccc
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 54935
Avg_row_length: 2034
Data_length: 111788032
Max_data_length: 0
Index_length: 21676032
Data_free: 4194304
Auto_increment: 127869
Create_time: 2013-12-04 14:43:45
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPACT
Comment:
1 row in set (0.01 sec)
2 在一个新的系统上做规划：
如果是Oracle，已提供了一个很好的DBMS_SPACE Package 供大家使用：
CREATE_INDEX_COST Procedure This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index. Syntax DBMS_SPACE.CREATE_INDEX_COST ( ddl IN VARCHAR2, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER, plan_table IN VARCHAR2 DEFAULT NULL); Pragmas pragma restrict_references(create_index_cost,WNDS); Parameters Table 134-5 CREATE_INDEX_COST Procedure Parameters Parameter Description ddl The create index DDL statement used_bytes The number of bytes representing the actual index data alloc_bytes Size of the index when created in the tablespace plan_table Which plan table to use, default NULL Usage Notes ?The table on which the index is created must already exist. ?The computation of the index size depends on statistics gathered on the segment. ?It is imperative that the table must have been analyzed recently. ?In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors. -------------------------------------------------------------------------------- CREATE_TABLE_COST Procedures This procedure is used in capacity planning to determine the size of the table given various attributes. The size of the object can vary widely based on the tablespace storage attributes, tablespace block size, and so on. There are two overloads of this procedure. ?The first version takes the column information of the table as argument and outputs the table size. ?The second version takes the average row size of the table as argument and outputs the table size. This procedure can be used on tablespace of dictionary managed and locally managed extent management as well as manual and auto segment space management. Syntax DBMS_SPACE.CREATE_TABLE_COST ( tablespace_name IN VARCHAR2, avg_row_size IN NUMBER, row_count IN NUMBER, pct_free IN NUMBER, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER); DBMS_SPACE.CREATE_TABLE_COST ( tablespace_name IN VARCHAR2, colinfos IN CREATE_TABLE_COST_COLUMNS, row_count IN NUMBER, pct_free IN NUMBER, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER); CREATE TYPE create_table_cost_colinfo IS OBJECT ( COL_TYPE VARCHAR(200), COL_SIZE NUMBER); Parameters Table 134-6 CREATE_TABLE_COST Procedure Parameters Parameter Description tablespace_name The tablespace in which the object will be created. The default is SYSTEM tablespace. avg_row_size The anticipated average row size in the table colinfos The description of the columns row_count The anticipated number of rows in the table pct_free The percentage of free space in each block for future expansion of existing rows due to updates used_bytes The space used by user data alloc_bytes The size of the object taking into account the tablespace extent characteristics Usage Notes ?The used_bytes represent the actual bytes used by the data. This includes the overhead due to the block metadata, pctfree etc. ?The alloc_bytes represent the size of the table when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.
这个在提供一个手工表大小计算的方法：
大概思路就是计算每个block除去本来的block结构占用后可以存放多少条记录，对于非聚集表，大概需要如下五步：
1.计算整个block header的size，类似
DB_BLOCK_SIZE – KCBH – UB4 – KTBBH – ((INITRANS – 1) * KTBIT) – KDBH
hsize = 4192 – 20 – 4 – 48 -((5-1)*24)-14
= 4192 -182
= 4010 bytes
2.计算每个block的可用空间
available data space (availspace) =
CEIL(hsize * (1 – PCTFREE/100 )) – KDBT
3.计算每行记录的长度.
Column size including byte length =
column size + (1, if column size < 250, else 3)
4.计算block里存放的行数
Number of rows in block =
FLOOR(availspace / rowspace)
5.计算空间
total blocks=
(Total Table Rows) / (Rows Per Block)
对于索引的计算方法，参考：
http://itlife365.com/blog/post/how-to-calculate-indexes-size-when-you-create-index-on-oracle.php
http://itlife365.com/blog/post/how-to-calculate-indexes-size-when-you-create-index-on-oracle-by-dbms_space-create_index_cost.php
对于8k的block：
block_size(8192)= = kcbh + ktbbh + kdxle + kd_off + DATA + block_size * Pctfree + 4
对于MySQL，其实计算方法是类似的，所不同的是要注意存储结构的不同。
参考：http://itlife365.com/blog/post/about-Capacity-planning-mysql.php
