| List: | Commits | « Previous MessageNext Message » | |
| From: | jon | Date: | February 2 2007 9:47am |
| Subject: | svn commit - mysqldoc@docsrva: r4746 - branches/telcos/refman-5.1 trunk/refman-5.1 | ||
| View as plain text | |||
Author: jstephens Date: 2007-02-02 09:47:46 +0100 (Fri, 02 Feb 2007) New Revision: 4746 Log: Fixes Docs Bug #23733 (NULL row in I_S.FILES query output) Modified: branches/telcos/refman-5.1/information-schema.xml branches/telcos/refman-5.1/mysql-cluster.xml trunk/refman-5.1/information-schema.xml trunk/refman-5.1/mysql-cluster.xml Modified: branches/telcos/refman-5.1/information-schema.xml =================================================================== --- branches/telcos/refman-5.1/information-schema.xml 2007-02-02 03:28:05 UTC (rev 4745) +++ branches/telcos/refman-5.1/information-schema.xml 2007-02-02 08:47:46 UTC (rev 4746) Changed blocks: 1, Lines Added: 160, Lines Deleted: 0; 7640 bytes @@ -4971,7 +4971,167 @@ was added in MySQL 5.1.6. </para> </listitem> + + <listitem> + <para> + Beginning with MySQL 5.1.14, an additional row is present in + the <literal>FILES</literal> table following the creation of a + logfile group. This row has <literal>NULL</literal> for the + value of the <literal>FILE_NAME</literal> column. For this + row, the value of the <literal>FILE_ID</literal> column is + always <literal>0</literal>, that of the + <literal>FILE_TYPE</literal> column is always <literal>UNDO + FILE</literal>, and that of the <literal>STATUS</literal> + column is always <literal>NORMAL</literal>. In MySQL + ¤t-series; the value of the <literal>ENGINE</literal> + column is always <literal>ndbcluster</literal>. + </para> + + <para> + This row shows in the <literal>FREE_EXTENTS</literal> column + the total number of free extents available to all undo files + belonging to a given log file group whose name and number are + shown in the <literal>LOGFILE_GROUP_NAME</literal> and + <literal>LOGFILE_GROUP_NUMBER</literal> columns, respectively. + </para> + + <para> + Suppose there are no existing log file groups on your MySQL + Cluster, and you create one using the following statement: + </para> + + <programlisting> +mysql> <userinput>CREATE LOGFILE GROUP lg1</userinput> + -> <userinput>ADD UNDOFILE 'undofile.dat'</userinput> + -> <userinput>INITIAL_SIZE = 16M</userinput> + -> <userinput>UNDO_BUFFER_SIZE = 1M</userinput> + -> <userinput>ENGINE = NDB;</userinput> +Query OK, 0 rows affected (3.81 sec) +</programlisting> + + <para> + You can now see this <literal>NULL</literal> row when you + query the <literal>FILES</literal> table: + </para> + + <programlisting> +mysql> <userinput>SELECT DISTINCT</userinput> + -> <userinput>FILE_NAME AS File,</userinput> + -> <userinput>FREE_EXTENTS AS Free,</userinput> + -> <userinput>TOTAL_EXTENTS AS Total,</userinput> + -> <userinput>EXTENT_SIZE AS Size,</userinput> + -> <userinput>INITIAL_SIZE AS Initial</userinput> + -> <userinput>FROM INFORMATION_SCHEMA.FILES;</userinput> ++--------------+---------+---------+------+----------+ +| File | Free | Total | Size | Initial | ++--------------+---------+---------+------+----------+ +| undofile.dat | NULL | 4194304 | 4 | 16777216 | +| NULL | 4184068 | NULL | 4 | NULL | ++--------------+---------+---------+------+----------+ +2 rows in set (0.01 sec) +</programlisting> + + <para> + The total number of free extents available for undo logging is + always somewhat less than the sum of the + <literal>TOTAL_EXTENTS</literal> column values for all undo + files in the log file group due to overhead required for + maintaining the undo files. This can be seen by adding a + second undo file to the log file group, then repeating the + previous query against the <literal>FILES</literal> table: + </para> + + <programlisting> +mysql> <userinput>ALTER LOGFILE GROUP lg1</userinput> + -> <userinput>ADD UNDOFILE 'undofile02.dat'</userinput> + -> <userinput>INITIAL_SIZE = 4M</userinput> + -> <userinput>ENGINE = NDB;</userinput> +Query OK, 0 rows affected (1.02 sec) +mysql> <userinput>SELECT DISTINCT</userinput> + -> <userinput>FILE_NAME AS File,</userinput> + -> <userinput>FREE_EXTENTS AS Free,</userinput> + -> <userinput>TOTAL_EXTENTS AS Total,</userinput> + -> <userinput>EXTENT_SIZE AS Size,</userinput> + -> <userinput>INITIAL_SIZE AS Initial</userinput> + -> <userinput>FROM INFORMATION_SCHEMA.FILES;</userinput> ++----------------+---------+---------+------+----------+ +| File | Free | Total | Size | Initial | ++----------------+---------+---------+------+----------+ +| undofile.dat | NULL | 4194304 | 4 | 16777216 | +| undofile02.dat | NULL | 1048576 | 4 | 4194304 | +| NULL | 5223944 | NULL | 4 | NULL | ++----------------+---------+---------+------+----------+ +3 rows in set (0.01 sec) +</programlisting> + + <para> + The amount of free space in bytes which is available for undo + logging by Disk Data tables using this log file group can be + approximated by multiplying the number of free extents by the + initial size: + </para> + + <programlisting> +mysql> <userinput>SELECT</userinput> + -> <userinput>FREE_EXTENTS AS 'Free Extents',</userinput> + -> <userinput>FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'</userinput> + -> <userinput>FROM INFORMATION_SCHEMA.FILES</userinput> + -> <userinput>WHERE LOGFILE_GROUP_NAME = 'lg1'</userinput> + -> <userinput>AND FILE_NAME IS NULL;</userinput> ++--------------+------------+ +| Free Extents | Free Bytes | ++--------------+------------+ +| 5223944 | 20895776 | ++--------------+------------+ +1 row in set (0.02 sec) +</programlisting> + + <para> + If you create a Disk Data table and then insert some rows into + it, you can see approximately how much space remains for undo + logging afterwards, for example: + </para> + + <programlisting> +mysql> <userinput>CREATE TABLESPACE ts1</userinput> + -> <userinput>ADD DATAFILE 'data1.dat'</userinput> + -> <userinput>USE LOGFILE GROUP lg1</userinput> + -> <userinput>INITIAL_SIZE 512M</userinput> + -> <userinput>ENGINE = NDB;</userinput> +Query OK, 0 rows affected (8.71 sec) + +mysql> <userinput>CREATE TABLE dd (</userinput> + -> <userinput>c1 INT NOT NULL PRIMARY KEY,</userinput> + -> <userinput>c2 INT,</userinput> + -> <userinput>c3 DATE</userinput> + -> <userinput>)</userinput> + -> <userinput>TABLESPACE ts1 STORAGE DISK</userinput> + -> <userinput>ENGINE = NDB;</userinput> +Query OK, 0 rows affected (2.11 sec) + +mysql> <userinput>INSERT INTO dd VALUES</userinput> + -> <userinput>(NULL, 1234567890, '2007-02-02'),</userinput> + -> <userinput>(NULL, 1126789005, '2007-02-03'),</userinput> + -> <userinput>(NULL, 1357924680, '2007-02-04'),</userinput> + -> <userinput>(NULL, 1642097531, '2007-02-05');</userinput> +Query OK, 4 rows affected (0.01 sec) + +mysql> <userinput>SELECT</userinput> + -> <userinput>FREE_EXTENTS AS 'Free Extents',</userinput> + -> <userinput>FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'</userinput> + -> <userinput>FROM INFORMATION_SCHEMA.FILES</userinput> + -> <userinput>WHERE LOGFILE_GROUP_NAME = 'lg1'</userinput> + -> <userinput>AND FILE_NAME IS NULL;</userinput> ++--------------+------------+ +| Free Extents | Free Bytes | ++--------------+------------+ +| 5207565 | 20830260 | ++--------------+------------+ +1 row in set (0.01 sec) +</programlisting> + </listitem> + <listitem> <para> There are no <literal>SHOW</literal> commands associated with Modified: branches/telcos/refman-5.1/mysql-cluster.xml =================================================================== --- branches/telcos/refman-5.1/mysql-cluster.xml 2007-02-02 03:28:05 UTC (rev 4745) +++ branches/telcos/refman-5.1/mysql-cluster.xml 2007-02-02 08:47:46 UTC (rev 4746) Changed blocks: 1, Lines Added: 8, Lines Deleted: 6; 1221 bytes @@ -17339,13 +17339,15 @@ the two <literal>ALTER TABLESPACE ... DROP DATAFILE</literal> statements may be executed in either order. </para> - + <para> - You can obtain information about <literal>UNDO</literal> log files - and data files used by Disk Data tables by querying the - <literal>FILES</literal> table in the - <literal>INFORMATION_SCHEMA</literal> database. For more - information, see <xref linkend="files-table"/>. + You can obtain information about data files used by Disk Data + tables by querying the <literal>FILES</literal> table in the + <literal>INFORMATION_SCHEMA</literal> database. An extra + <quote><literal>NULL</literal> row</quote> was added to this table + in MySQL 5.1.14 for providing additional information about undo + log files. For more information and examples of use, see + <xref linkend="files-table"/>. </para> <para> Modified: trunk/refman-5.1/information-schema.xml =================================================================== --- trunk/refman-5.1/information-schema.xml 2007-02-02 03:28:05 UTC (rev 4745) +++ trunk/refman-5.1/information-schema.xml 2007-02-02 08:47:46 UTC (rev 4746) Changed blocks: 1, Lines Added: 160, Lines Deleted: 0; 7570 bytes @@ -4971,7 +4971,167 @@ was added in MySQL 5.1.6. </para> </listitem> + + <listitem> + <para> + Beginning with MySQL 5.1.14, an additional row is present in + the <literal>FILES</literal> table following the creation of a + logfile group. This row has <literal>NULL</literal> for the + value of the <literal>FILE_NAME</literal> column. For this + row, the value of the <literal>FILE_ID</literal> column is + always <literal>0</literal>, that of the + <literal>FILE_TYPE</literal> column is always <literal>UNDO + FILE</literal>, and that of the <literal>STATUS</literal> + column is always <literal>NORMAL</literal>. In MySQL + ¤t-series; the value of the <literal>ENGINE</literal> + column is always <literal>ndbcluster</literal>. + </para> + + <para> + This row shows in the <literal>FREE_EXTENTS</literal> column + the total number of free extents available to all undo files + belonging to a given log file group whose name and number are + shown in the <literal>LOGFILE_GROUP_NAME</literal> and + <literal>LOGFILE_GROUP_NUMBER</literal> columns, respectively. + </para> + + <para> + Suppose there are no existing log file groups on your MySQL + Cluster, and you create one using the following statement: + </para> + +<programlisting> +mysql> <userinput>CREATE LOGFILE GROUP lg1</userinput> + -> <userinput>ADD UNDOFILE 'undofile.dat'</userinput> + -> <userinput>INITIAL_SIZE = 16M</userinput> + -> <userinput>UNDO_BUFFER_SIZE = 1M</userinput> + -> <userinput>ENGINE = NDB;</userinput> +Query OK, 0 rows affected (3.81 sec) +</programlisting> + + <para> + You can now see this <literal>NULL</literal> row when you + query the <literal>FILES</literal> table: + </para> + +<programlisting> +mysql> <userinput>SELECT DISTINCT</userinput> + -> <userinput>FILE_NAME AS File,</userinput> + -> <userinput>FREE_EXTENTS AS Free,</userinput> + -> <userinput>TOTAL_EXTENTS AS Total,</userinput> + -> <userinput>EXTENT_SIZE AS Size,</userinput> + -> <userinput>INITIAL_SIZE AS Initial</userinput> + -> <userinput>FROM INFORMATION_SCHEMA.FILES;</userinput> ++--------------+---------+---------+------+----------+ +| File | Free | Total | Size | Initial | ++--------------+---------+---------+------+----------+ +| undofile.dat | NULL | 4194304 | 4 | 16777216 | +| NULL | 4184068 | NULL | 4 | NULL | ++--------------+---------+---------+------+----------+ +2 rows in set (0.01 sec) +</programlisting> + + <para> + The total number of free extents available for undo logging is + always somewhat less than the sum of the + <literal>TOTAL_EXTENTS</literal> column values for all undo + files in the log file group due to overhead required for + maintaining the undo files. This can be seen by adding a + second undo file to the log file group, then repeating the + previous query against the <literal>FILES</literal> table: + </para> + +<programlisting> +mysql> <userinput>ALTER LOGFILE GROUP lg1</userinput> + -> <userinput>ADD UNDOFILE 'undofile02.dat'</userinput> + -> <userinput>INITIAL_SIZE = 4M</userinput> + -> <userinput>ENGINE = NDB;</userinput> +Query OK, 0 rows affected (1.02 sec) +mysql> <userinput>SELECT DISTINCT</userinput> + -> <userinput>FILE_NAME AS File,</userinput> + -> <userinput>FREE_EXTENTS AS Free,</userinput> + -> <userinput>TOTAL_EXTENTS AS Total,</userinput> + -> <userinput>EXTENT_SIZE AS Size,</userinput> + -> <userinput>INITIAL_SIZE AS Initial</userinput> + -> <userinput>FROM INFORMATION_SCHEMA.FILES;</userinput> ++----------------+---------+---------+------+----------+ +| File | Free | Total | Size | Initial | ++----------------+---------+---------+------+----------+ +| undofile.dat | NULL | 4194304 | 4 | 16777216 | +| undofile02.dat | NULL | 1048576 | 4 | 4194304 | +| NULL | 5223944 | NULL | 4 | NULL | ++----------------+---------+---------+------+----------+ +3 rows in set (0.01 sec) +</programlisting> + + <para> + The amount of free space in bytes which is available for undo + logging by Disk Data tables using this log file group can be + approximated by multiplying the number of free extents by the + initial size: + </para> + +<programlisting> +mysql> <userinput>SELECT</userinput> + -> <userinput>FREE_EXTENTS AS 'Free Extents',</userinput> + -> <userinput>FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'</userinput> + -> <userinput>FROM INFORMATION_SCHEMA.FILES</userinput> + -> <userinput>WHERE LOGFILE_GROUP_NAME = 'lg1'</userinput> + -> <userinput>AND FILE_NAME IS NULL;</userinput> ++--------------+------------+ +| Free Extents | Free Bytes | ++--------------+------------+ +| 5223944 | 20895776 | ++--------------+------------+ +1 row in set (0.02 sec) +</programlisting> + + <para> + If you create a Disk Data table and then insert some rows into + it, you can see approximately how much space remains for undo + logging afterwards, for example: + </para> + +<programlisting> +mysql> <userinput>CREATE TABLESPACE ts1</userinput> + -> <userinput>ADD DATAFILE 'data1.dat'</userinput> + -> <userinput>USE LOGFILE GROUP lg1</userinput> + -> <userinput>INITIAL_SIZE 512M</userinput> + -> <userinput>ENGINE = NDB;</userinput> +Query OK, 0 rows affected (8.71 sec) + +mysql> <userinput>CREATE TABLE dd (</userinput> + -> <userinput>c1 INT NOT NULL PRIMARY KEY,</userinput> + -> <userinput>c2 INT,</userinput> + -> <userinput>c3 DATE</userinput> + -> <userinput>)</userinput> + -> <userinput>TABLESPACE ts1 STORAGE DISK</userinput> + -> <userinput>ENGINE = NDB;</userinput> +Query OK, 0 rows affected (2.11 sec) + +mysql> <userinput>INSERT INTO dd VALUES</userinput> + -> <userinput>(NULL, 1234567890, '2007-02-02'),</userinput> + -> <userinput>(NULL, 1126789005, '2007-02-03'),</userinput> + -> <userinput>(NULL, 1357924680, '2007-02-04'),</userinput> + -> <userinput>(NULL, 1642097531, '2007-02-05');</userinput> +Query OK, 4 rows affected (0.01 sec) + +mysql> <userinput>SELECT</userinput> + -> <userinput>FREE_EXTENTS AS 'Free Extents',</userinput> + -> <userinput>FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'</userinput> + -> <userinput>FROM INFORMATION_SCHEMA.FILES</userinput> + -> <userinput>WHERE LOGFILE_GROUP_NAME = 'lg1'</userinput> + -> <userinput>AND FILE_NAME IS NULL;</userinput> ++--------------+------------+ +| Free Extents | Free Bytes | ++--------------+------------+ +| 5207565 | 20830260 | ++--------------+------------+ +1 row in set (0.01 sec) +</programlisting> + </listitem> + <listitem> <para> There are no <literal>SHOW</literal> commands associated with Modified: trunk/refman-5.1/mysql-cluster.xml =================================================================== --- trunk/refman-5.1/mysql-cluster.xml 2007-02-02 03:28:05 UTC (rev 4745) +++ trunk/refman-5.1/mysql-cluster.xml 2007-02-02 08:47:46 UTC (rev 4746) Changed blocks: 1, Lines Added: 7, Lines Deleted: 5; 1068 bytes @@ -17285,11 +17285,13 @@ </para> <para> - You can obtain information about <literal>UNDO</literal> log files - and data files used by Disk Data tables by querying the - <literal>FILES</literal> table in the - <literal>INFORMATION_SCHEMA</literal> database. For more - information, see <xref linkend="files-table"/>. + You can obtain information about data files used by Disk Data + tables by querying the <literal>FILES</literal> table in the + <literal>INFORMATION_SCHEMA</literal> database. An extra + <quote><literal>NULL</literal> row</quote> was added to this table + in MySQL 5.1.14 for providing additional information about undo + log files. For more information and examples of use, see + <xref linkend="files-table"/>. </para> <para>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r4746 - branches/telcos/refman-5.1 trunk/refman-5.1 | jon | 2 Feb |
© 1995-2008 MySQL AB. All rights reserved.
Page generated in 0.844 sec. using MySQL 5.1.14-beta-log