出自:http://dev.mysql.com/doc/refman/5.5/en/glossary.html
system tablespace
A small set of data files (the ibdata files) containing the metadata for InnoDB-related objects (the data dictionary), and the storage areas for the undo log, the change buffer, and the doublewrite buffer. Depending on the setting of the innodb_file_per_table
, when tables are created, it might also contain table and index data for some or all InnoDB tables. The data and metadata in the system tablespace apply to all the databases in a MySQL instance.
Prior to MySQL 5.6.7, the default was to keep all InnoDB tables and indexes inside the system tablespace, often causing this file to become very large. Because the system tablespace never shrinks, storage problems could arise if large amounts of temporary data were loaded and then deleted. In MySQL 5.6.7 and higher, the default is file-per-tablemode, where each table and its associated indexes are stored in a separate .ibd file. This new default makes it easier to use InnoDB features that rely on the Barracuda file format, such as table compression and the DYNAMIC row format.
In MySQL 5.6 and higher, setting a value for the innodb_undo_tablespaces
option splits the undo log into one or more separate tablespace files. These files are still considered part of the system tablespace.
Keeping all table data in the system tablespace or in separate .ibd
files has implications for storage management in general. The MySQL Enterprise Backup product might back up a small set of large files, or many smaller files. On systems with thousands of tables, the filesystem operations to process thousands of .ibd
files can cause bottlenecks.
ib_logfile
A set of files, typically named ib_logfile0
and ib_logfile1
, that form the redo log. Also sometimes referred to as the log group. These files record statements that attempt to change data in InnoDB tables. These statements are replayed automatically to correct data written by incomplete transactions, on startup following a crash.
This data cannot be used for manual recovery; for that type of operation, use the binary log.
.ibd file
Each InnoDB table created using the file-per-table mode goes into its own tablespace file, with a .ibd
extension, inside the database directory. This file contains the table data and any indexes for the table. File-per-table mode, controlled by the innodb_file_per_table option, affects many aspects of InnoDB storage usage and performance, and is enabled by default in MySQL 5.6.7 and higher.
This extension does not apply to the system tablespace, which consists of the ibdata files.
A set of files with names such as ibdata1
, ibdata2
, and so on, that make up the InnoDB system tablespace. These files contain metadata about InnoDB tables, (the data dictionary), and the storage areas for the undo log, the change buffer, and the doublewrite buffer. They also can contain some or all of the table data also (depending on whether the file-per-table mode is in effect when each table is created). When the innodb_file_per_table option is enabled, data and indexes for newly created tables are stored in separate.ibd files rather than in the system tablespace.
The growth of the ibdata
files is influenced by the innodb_autoextend_increment
configuration option.
data files
The files that physically contain the InnoDB table and index data. There can be a one-to-many relationship between data files and tables, as in the case of the system tablespace, which can hold multiple InnoDB tables as well as the data dictionary. There can also be a one-to-one relationship between data files and tables, as when the file-per-table setting is enabled, causing each newly created table to be stored in a separate tablespace.
database
Within the MySQL data directory, each database is represented by a separate directory. The InnoDB system tablespace, which can hold table data from multiple databases within a MySQL instance, is kept in its data files that reside outside the individual database directories. When file-per-table mode is enabled, the .ibd files representing individual InnoDB tables are stored inside the database directories.
For long-time MySQL users, a database is a familiar notion. Users coming from an Oracle Database background will find that the MySQL meaning of a database is closer to what Oracle Database calls a schema.
storage engine
A component of the MySQL database that performs the low-level work of storing, updating, and querying data. In MySQL 5.5 and higher, InnoDB is the default storage engine for new tables, superceding MyISAM. Different storage engines are designed with different tradeoffs between factors such as memory usage versus disk usage, read speed versus write speed, and speed versus robustness. Each storage engine manages specific tables, so we refer to InnoDB
tables, MyISAM
tables, and so on.
tablespace
A data file that can hold data for one or more InnoDB tables and associated indexes. The system tablespace contains the tables that make up the data dictionary, and prior to MySQL 5.6 holds all the other InnoDB tables by default. Turning on the innodb_file_per_table
option, the default in MySQL 5.6 and higher, allows newly created tables to each have their own tablespace, with a separate data file for each table.
相关推荐
主要介绍了MySQL启动报错问题InnoDB:Unable to lock/ibdata1 error,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
create undo tablespace undotbs_tablespace datafile '/XXX/xxx/datafile_name1.ora' size 4095M; 增加表空间: 数据表空间: alter tablespace tablespace_name add datafile '/XXX/xxx/datafile_name1.ora' size ...
Mysql innodb tablespace 表空间实践
描述了oracle数据库表空间巡检的步骤: 1:查看表空间 ...SELECT tablespace_name,file_id,file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; 3:......
..........\Free_Space_TableSpace.sql ..........\Health.sql ..........\High_Water_Mark.sql ..........\Index_Extents.sql ..........\Index_Partitions.sql ..........\Jobs.sql ..........\Jobs_...
1、找到昨晚备份,用root用户解压:gunzip -d exp_hc01.dmp.gz 2、如果toptet数据库开了就进...imp test/test@toptst file=/u3/imp/exp_hc01.dmp log=/tmp/imp_pia.log fromuser=hc01 touser=test tables=pia_file
Oracle维护常用SQL语句汇总: 如何远程判断Oracle数据库的安装平台 ...select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
Oracle数据库日常检查 select a.tablespace_name tnm,a.bytes/1024/1024 total,c.bytes/1024/1024 free, trunc((a.bytes-c.bytes)*100/a.bytes,0) "% USED",trunc((c.bytes*100)/a.bytes,0) "% FREE" from SYS.SM$...
chmod 777 createdb.sql CREATE DATABASE exchange MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 DATAFILE '/u01/app/oracle/oradata/exchange/system01.dbf' size 100m reuse ...
chmod 777 createdb.sql CREATE DATABASE exchange MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 DATAFILE '/u01/app/oracle/oradata/exchange/system01.dbf' size 100m reuse ...
impdp system/manager directory=dump_dir dumpfile=tablespace.dmp tablespaces=users 导入数据库 impdp system/manager directory=dump_dir dumpfile=full.dmp full=y 三、外部表: bad file: 不符合规则的...
(比如存储在 SYSTEM 表空间内的 pl/sql 对象,sequences 等),你可以使用数据泵来拷贝这些对象至目标系统。 注意: 考虑使用新release的版本V4的过程。 这个版本极大地简化了相关步骤。 请参考文档:V4 Reduce ...
Storage(OBJ#=9976 OBJD=9977 TS=4 FILE=4 BLOCK=5235 CLUSTER=0) No. SEQ INT Column Name Type --- --- --- ----------------------------- ---------------- 1 1 1 TNAME VARCHAR2(30) NOT NULL 2 2 2 ...
select b.tablespace_name 表空间, c.owner 用户, c.segment_name 表名, b.file_name 物理文件名, sum(nvl(b.bytes, 0)) / 1024 / 1024 总共大小M, round((sum(nvl(b.bytes, 0)) - sum(nvl(a.bytes, 0))) ...
audit_file_dest=$ORACLE_BASE/admin/orcl/adump background_dump_dest=$ORACLE_BASE/admin/orcl/bdump core_dump_dest=$ORACLE_BASE/admin/orcl/cdump user_dump_dest=$ORACLE_BASE/admin/orcl/udump control_files...
Oracle XTTS v4.3. V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 3、查看回滚段名称及大小 select segment_name, tablespace_name, r....
create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第2步:创建数据表空间...
一、Oracle+Database+11g+DBA手册(中文) 二、《Oracle数据库SQL执行计划的取得和解析》PPT(附SQL文件) 三、Oracle 常用脚本 2pc_clean.txt ash_sql_line_id.txt ...SQLCodes-Oracle错误代码与消息解释.chm
Oracle维护常用SQL语句 ************************************************ 如何查看各个表空间占用磁盘情况?...where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id