| List: | Internals | « Previous MessageNext Message » | |
| From: | jon | Date: | July 19 2005 5:32pm |
| Subject: | bk commit - mysqldoc@docsrva tree (jon:1.3067) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local mysqldoc repository of jon. When jon does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://www.mysql.com/doc/I/n/Installing_source_tree.html ChangeSet 1.3067 05/07/20 01:32:37 jon@stripped +5 -0 Innodb chapter edits for RefMan-5.0. Updated RefMan-5.0 Status. Added euro symbol to entities files. refman/fixedchars.ent 1.4 05/07/20 01:32:36 jon@stripped +1 -0 Added euro symbol. refman-5.0/innodb.xml 1.8 05/07/20 01:32:35 jon@stripped +301 -278 RefMan-5.0 edits. refman-5.0/fixedchars.ent 1.4 05/07/20 01:32:35 jon@stripped +2 -1 Sync. refman-5.0/Status 1.9 05/07/20 01:32:35 jon@stripped +1 -1 Updating... refman-4.1/fixedchars.ent 1.4 05/07/20 01:32:35 jon@stripped +1 -0 Sync. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: jon # Host: gigan. # Root: /home/jon/bk/mysqldoc --- 1.3/refman-4.1/fixedchars.ent 2005-07-09 07:13:41 +10:00 +++ 1.4/refman-4.1/fixedchars.ent 2005-07-20 01:32:35 +10:00 @@ -240,3 +240,4 @@ <!ENTITY ulcrop "⌏"> <!ENTITY urcrop "⌎"> <!ENTITY pi "ϖ"> + <!ENTITY euro "€"> --- 1.3/refman-5.0/fixedchars.ent 2005-07-09 07:13:41 +10:00 +++ 1.4/refman-5.0/fixedchars.ent 2005-07-20 01:32:35 +10:00 @@ -239,4 +239,5 @@ <!ENTITY drcrop "⌌"> <!ENTITY ulcrop "⌏"> <!ENTITY urcrop "⌎"> - <!ENTITY pi "ϖ"> \ No newline at end of file + <!ENTITY pi "ϖ"> + <!ENTITY euro "€"> \ No newline at end of file --- 1.7/refman-5.0/innodb.xml 2005-07-15 06:11:32 +10:00 +++ 1.8/refman-5.0/innodb.xml 2005-07-20 01:32:35 +10:00 @@ -11,11 +11,11 @@ <title id='title-innodb'>&title-innodb;</title> -<!-- TODO: A number of node names in this chapter are very generic (e.g., --> - -<!-- "Backing up" and could apply in non-InnoDB contexts. Might be best --> - -<!-- to rename a bunch of them. --> +<!-- + TODO: A number of node names in this chapter are very generic + (e.g., "Backing up" and could apply in non-InnoDB contexts. Might + be best to rename a bunch of them. +--> <indexterm type="concept"> <primary><literal>InnoDB</literal> storage engine</primary> @@ -408,6 +408,10 @@ information displayed by <literal>InnoDB</literal> should look like. </para> +<!-- TODO: Do we really need this info here, since it just repeats + what's found in the Installation and other chapters? /JS +--> + <para> <emphasis role="bold">Where to specify options on Windows?</emphasis> The rules for option files on Windows are as follows: @@ -459,8 +463,6 @@ files, if they exist, in the following order: </para> -<!-- Note: Do not remove blank lines following the @item lines --> - <itemizedlist> <listitem><para> @@ -536,24 +538,21 @@ # # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is < 2GB -set-variable = innodb_buffer_pool_size=1G -set-variable = innodb_additional_mem_pool_size=20M +innodb_buffer_pool_size=1G +innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /dr3/iblogs # -# innodb_log_arch_dir must be the same as innodb_log_group_home_dir -# (starting from 4.0.6, you can omit it) -innodb_log_arch_dir = /dr3/iblogs -set-variable = innodb_log_files_in_group=2 +innodb_log_files_in_group = 2 # # Set the log file size to about 25% of the buffer pool size -set-variable = innodb_log_file_size=250M -set-variable = innodb_log_buffer_size=8M +innodb_log_file_size=250M +innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 -set-variable = innodb_lock_wait_timeout=50 +innodb_lock_wait_timeout=50 # # Uncomment the next lines if you want to use them -#set-variable = innodb_thread_concurrency=5 +#innodb_thread_concurrency=5 </programlisting> <para> @@ -604,14 +603,14 @@ <programlisting> [mysqld] skip-external-locking -set-variable = max_connections=200 -set-variable = read_buffer_size=1M -set-variable = sort_buffer_size=1M +max_connections=200 +read_buffer_size=1M +sort_buffer_size=1M # # Set key_buffer to 5 - 50% of your RAM depending on how much # you use MyISAM tables, but keep key_buffer_size + InnoDB # buffer pool size < 80% of your RAM -set-variable = key_buffer_size=... +key_buffer_size=<replaceable>value</replaceable> </programlisting> </section> @@ -624,7 +623,7 @@ <para> This section describes the <literal>InnoDB</literal>-related server - options. In MySQL 5.0 and up, all of them can be specified in + options. In MySQL 5.0, all of them can be specified in <literal>--<replaceable>opt_name</replaceable>=<replaceable>value</replaceable></literal> form on the command line or in option files. </para> @@ -666,10 +665,10 @@ The size of the buffer pool (in MB), if it is placed in the AWE memory of 32-bit Windows. (Relevant only in 32-bit Windows.) If your 32-bit Windows operating system supports more than 4GB memory, using - so-called ``Address Windowing Extensions,'' you can allocate the - <literal>InnoDB</literal> buffer pool into the AWE physical memory - using this parameter. The maximum possible value for this is 64000. - If this parameter is specified, + so-called <quote>Address Windowing Extensions</quote>, you can + allocate the <literal>InnoDB</literal> buffer pool into the AWE + physical memory using this parameter. The maximum possible value for + this is 64000. If this parameter is specified, <literal>innodb_buffer_pool_size</literal> is the window in the 32-bit address space of <command>mysqld</command> where <literal>InnoDB</literal> maps that AWE memory. A good value for @@ -698,7 +697,7 @@ <literal>InnoDB</literal> uses checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files. However, under some rare circumstances (such - as when running benchmarks) this ``extra safety'' feature is + as when running benchmarks) this extra safety feature is unneeded. In such cases, this option (which is enabled by default) can be turned off with <literal>--skip-innodb-checksums</literal>. This option was added in MySQL 5.0.3. @@ -908,16 +907,17 @@ <para> Normally <literal>InnoDB</literal> uses an algorithm called - ``next-key locking.'' <literal>InnoDB</literal> does the row-level - locking in such a way that when it searches or scans an index of a - table, it sets shared or exclusive locks on the index records it - encounters. Thus the row-level locks are actually index record - locks. The locks <literal>InnoDB</literal> sets on index records - also affect the ``gap'' before that index record. If a user has a - shared or exclusive lock on record R in an index, another user - cannot insert a new index record immediately before R in the index - order. This option causes <literal>InnoDB</literal> not to use - next-key locking in searches or index scans. Next-key locking is + <firstterm>next-key locking</firstterm>. <literal>InnoDB</literal> + performs row-level locking in such a way that when it searches or + scans a table index, it sets shared or exclusive locks on any index + records it encounters. Thus the row-level locks are actually index + record locks. The locks that <literal>InnoDB</literal> sets on index + records also affect the <quote>gap</quote> preceeding that index + record. If a user has a shared or exclusive lock on record + <emphasis>R</emphasis> in an index, another user cannot insert a new + index record immediately before <emphasis>R</emphasis> in the order + of the index. This option causes <literal>InnoDB</literal> not to + use next-key locking in searches or index scans. Next-key locking is still used to ensure foreign key constraints and duplicate key checking. Note that using this option may cause phantom problems: Suppose that you want to read and lock all children from the @@ -934,26 +934,27 @@ <para> Suppose that there is an index on the <literal>id</literal> column. - The query scans that index starting from the first record where id - is bigger than 100. If the locks set on the index records do not - lock out inserts made in the gaps, a new row is meanwhile inserted - to the table. If you execute the same <literal>SELECT</literal> - within the same transaction, you see a new row in the result set - returned by the query. This also means, that if new items are added - to the database, InnoDB does not guarantee serializability instead - conflict serializability is still guaranteed. Therefore, if this - option is used InnoDB guarantees at most isolation level - <literal>READ COMMITTED</literal>. + The query scans that index starting from the first record where + <literal>id</literal> is greater than 100. If the locks set on the + index records do not lock out inserts made in the gaps, a new row is + meanwhile inserted into the table. If you execute the same + <literal>SELECT</literal> within the same transaction, you see a new + row in the result set returned by the query. This also means, that + if new items are added to the database, InnoDB does not guarantee + serializability; however, conflict serializability is still + guaranteed. Therefore, if this option is used InnoDB guarantees at + most isolation level <literal>READ COMMITTED</literal>. </para> <para> - Starting from MySQL 5.0.2 this option is even more unsafe. InnoDB in - an <literal>UPDATE</literal> or a <literal>DELETE</literal> only - locks rows that it updates or deletes. This greatly reduces the - probability of deadlocks but they can happen. Note that this option - still does not allow e.g. <literal>UPDATE</literal> to overtake - other <literal>UPDATE</literal> even the case when both updates - different rows. Consider following example: + Starting from MySQL 5.0.2 this option is even more unsafe. + <literal>InnoDB</literal> in an <literal>UPDATE</literal> or a + <literal>DELETE</literal> only locks rows that it updates or + deletes. This greatly reduces the probability of deadlocks but they + can happen. Note that this option still does not allow operations + such as <literal>UPDATE</literal> to overtake like operations (such + as another <literal>UPDATE</literal>) even in the case when they + effect different rows. Consider following example: </para> <para> @@ -976,7 +977,8 @@ </para> <para> - and the other connection executes after the first one a query: + and the other connection executes, following the first one, another + query: </para> <para> @@ -988,13 +990,13 @@ <para> Then query two has to wait for a commit or rollback of query one, - because query one has an exclusive lock to a row (2,3), and query + because query one has an exclusive lock to row (2,3), and query two while scanning rows also tries to take an exclusive lock to the - row (2,3) which it cannot have. This is because query two first - takes an exclusive lock to a row and then checks does this row - belong to the result set and if not then releases the unnecessary - lock when option <literal>innodb_locks_unsafe_for_binlog</literal> - is used. + same row (2,3), which it cannot have. This is because query two + first takes an exclusive lock on a row and then determines whether + this row belongs to the result set, and if not then releases the + unnecessary lock, when the option + <literal>innodb_locks_unsafe_for_binlog</literal> is used. </para> <para> @@ -1211,16 +1213,17 @@ <literal>InnoDB</literal> tries to keep the number of operating system threads concurrently inside <literal>InnoDB</literal> less than or equal to the limit given by this parameter. Before MySQL - 5.0.8, the default value is 8. If you have low performance and + 5.0.8, the default value is 8. If you have performance issues, and <literal>SHOW INNODB STATUS</literal> reveals many threads waiting - for semaphores, you may have thread thrashing and should try setting - this parameter lower or higher. If you have a computer with many - processors and disks, you can try setting the value higher to make - better use of your computer's resources. A recommended value is the - sum of the number of processors and disks your system has. A value - of 500 or greater disables the concurrency checking. Starting with - MySQL 5.0.8, the default value is 20, and the concurrency checking - will be disabled if the setting is greater than or equal to 20. + for semaphores, you may have thread <quote>thrashing</quote> and + should try setting this parameter lower or higher. If you have a + computer with many processors and disks, you can try setting the + value higher to make better use of your computer's resources. A + recommended value is the sum of the number of processors and disks + your system has. A value of 500 or greater disables concurrency + checking. Starting with MySQL 5.0.8, the default value is 20, and + concurrency checking will be disabled if the setting is greater than + or equal to 20. </para> <para> @@ -1234,7 +1237,7 @@ <para> This option causes <literal>InnoDB</literal> to create a file <filename><replaceable><datadir></replaceable>/innodb_status.<replaceable><pid></replaceable></filename> - for periodical <literal>SHOW INNODB STATUS</literal> output. + for periodic <literal>SHOW INNODB STATUS</literal> output. </para></listitem> </itemizedlist> @@ -1729,9 +1732,9 @@ <listitem><para> In the referenced table, there must be an index where the referenced columns are listed as the <emphasis>first</emphasis> - columns in the same order. Starting with MySQL/InnoDB 4.1.2, such - an index will be created on the referenced table automatically if - it does not exist. + columns in the same order. In MySQL/InnoDB 5.0, such an index will + be created on the referenced table automatically if it does not + exist. </para></listitem> <listitem><para> @@ -1841,9 +1844,9 @@ <literal>InnoDB</literal> needs indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In MySQL 5.0, the index on the foreign key is - created automatically. In contrast to older versions (before 4.1.8), - where the indexes must be created explicitly or the creation of - foreign key constraints fails. + created automatically. This is in contrast to older versions (prior + to 4.1.8), where indexes must be created explicitly or the creation + of foreign key constraints fails. </para> <para> @@ -1865,8 +1868,8 @@ <literal>ALTER TABLE</literal> fails and it refers to errno 150, that means a foreign key definition would be incorrectly formed for the altered table. In MySQL 5.0, you can use <literal>SHOW INNODB - STATUS</literal> to display a detailed explanation of the latest - <literal>InnoDB</literal> foreign key error in the server. + STATUS</literal> to display a detailed explanation of the most + recent <literal>InnoDB</literal> foreign key error in the server. </para> <para> @@ -1927,13 +1930,18 @@ </para> <programlisting> -CREATE TABLE parent(id INT NOT NULL, - PRIMARY KEY (id) +CREATE TABLE parent( + id INT NOT NULL, + PRIMARY KEY (id) ) ENGINE=INNODB; -CREATE TABLE child(id INT, parent_id INT, - INDEX par_ind (parent_id), - FOREIGN KEY (parent_id) REFERENCES parent(id) - ON DELETE CASCADE + +CREATE TABLE child( + id INT, + parent_id INT, + INDEX par_ind (parent_id), + FOREIGN KEY (parent_id) + REFERENCES parent(id) + ON DELETE CASCADE ) ENGINE=INNODB; </programlisting> @@ -1948,23 +1956,32 @@ <!-- example_for_help_topic CONSTRAINT --> <programlisting> -CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, - price DECIMAL, - PRIMARY KEY(category, id)) ENGINE=INNODB; -CREATE TABLE customer (id INT NOT NULL, - PRIMARY KEY (id)) ENGINE=INNODB; -CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, - product_category INT NOT NULL, - product_id INT NOT NULL, - customer_id INT NOT NULL, - PRIMARY KEY(no), - INDEX (product_category, product_id), - FOREIGN KEY (product_category, product_id) - REFERENCES product(category, id) - ON UPDATE CASCADE ON DELETE RESTRICT, - INDEX (customer_id), - FOREIGN KEY (customer_id) - REFERENCES customer(id)) ENGINE=INNODB; +CREATE TABLE product ( + category INT NOT NULL, + id INT NOT NULL, + price DECIMAL, + PRIMARY KEY(category, id) +) ENGINE=INNODB; + +CREATE TABLE customer ( + id INT NOT NULL, + PRIMARY KEY (id) +) ENGINE=INNODB; + +CREATE TABLE product_order ( + no INT NOT NULL AUTO_INCREMENT, + product_category INT NOT NULL, + product_id INT NOT NULL, + customer_id INT NOT NULL, + PRIMARY KEY(no), + INDEX (product_category, product_id), + FOREIGN KEY (product_category, product_id) + REFERENCES product(category, id) + ON UPDATE CASCADE ON DELETE RESTRICT, + INDEX (customer_id), + FOREIGN KEY (customer_id) + REFERENCES customer(id) +) ENGINE=INNODB; </programlisting> <para> @@ -2091,7 +2108,7 @@ You can display the foreign key constraints for a table like this: <programlisting> -SHOW TABLE STATUS FROM <replaceable>db_name</replaceable> LIKE '<replaceable>tbl_name</replaceable>' +SHOW TABLE STATUS FROM <replaceable>db_name</replaceable> LIKE '<replaceable>tbl_name</replaceable>'; </programlisting> </para> @@ -2118,7 +2135,7 @@ <programlisting> mysql> SET FOREIGN_KEY_CHECKS = 0; -mysql> SOURCE <replaceable>dump_file_name</replaceable> +mysql> SOURCE <replaceable>dump_file_name</replaceable>; mysql> SET FOREIGN_KEY_CHECKS = 1; </programlisting> @@ -2321,9 +2338,9 @@ </indexterm> <para> - If you have a ``clean'' backup of an <filename>.ibd</filename> file, - you can restore it to the MySQL installation from which it - originated as follows: + If you have a <quote>clean</quote> backup of an + <filename>.ibd</filename> file, you can restore it to the MySQL + installation from which it originated as follows: </para> <orderedlist> @@ -2337,7 +2354,8 @@ </programlisting> <para> - Caution: This deletes the current <filename>.ibd</filename> file. + <emphasis role="bold">Caution</emphasis>: This deletes the current + <filename>.ibd</filename> file. </para></listitem> <listitem><para> @@ -2356,7 +2374,7 @@ </orderedlist> <para> - In this context, a ``clean'' <filename>.ibd</filename> file backup + In this context, a <quote>clean</quote> <filename>.ibd</filename> file backup means: </para> @@ -2409,13 +2427,13 @@ <para> Another method for making a clean copy of an <filename>.ibd</filename> file is to use the commercial - <literal>InnoDB Hot Backup</literal> tool: + <command>InnoDB Hot Backup</command> tool: </para> <orderedlist> <listitem><para> - Use <literal>InnoDB Hot Backup</literal> to back up the + Use <command>InnoDB Hot Backup</command> to back up the <literal>InnoDB</literal> installation. </para></listitem> @@ -2427,7 +2445,7 @@ </orderedlist> <para> - It is in the TODO to also allow moving clean + It is in the TODO to allow moving clean <filename>.ibd</filename> files to another MySQL installation. This requires resetting of transaction IDs and log sequence numbers in the <filename>.ibd</filename> file. @@ -2471,9 +2489,9 @@ If your last data file was defined with the keyword <literal>autoextend</literal>, the procedure to edit <filename>my.cnf</filename> must take into account the size to which - the last data file has grown. You have to look at the size of the - data file, round the size downward to the closest multiple of 1024 * - 1024 bytes (= 1MB), and specify the rounded size explicitly in + the last data file has grown. Obtain the size of the data file, round + it down to the closest multiple of 1024 * 1024 bytes (= 1MB), and + specify the rounded size explicitly in <literal>innodb_data_file_path</literal>. Then you can add another data file. Remember that only the last data file in the <literal>innodb_data_file_path</literal> can be specified as @@ -2564,23 +2582,22 @@ </para> <para> - <literal>InnoDB Hot Backup</literal> is an online backup tool you can + <command>InnoDB Hot Backup</command> is an online backup tool you can use to backup your <literal>InnoDB</literal> database while it is - running. <literal>InnoDB Hot Backup</literal> does not require you to + running. <command>InnoDB Hot Backup</command> does not require you to shut down your database and it does not set any locks or disturb your - normal database processing. <literal>InnoDB Hot Backup</literal> is a - non-free (commercial) additional tool whose annual license fee is 390 - euros per computer where the MySQL server is run. See the - <ulink url="http://www.innodb.com/order.html"><literal>InnoDB Hot - Backup</literal> home page</ulink> for detailed information and + normal database processing. <command>InnoDB Hot Backup</command> is a + non-free (commercial) add-on tool whose annual license fee is + €390 per computer on which the MySQL server is run. See the + <ulink url="http://www.innodb.com/order.html"><command>InnoDB Hot + Backup</command> home page</ulink> for detailed information and screenshots. </para> <para> - If you are able to shut down your MySQL server, you can make a - ``binary'' backup that consists of all files used by - <literal>InnoDB</literal> to manage its tables. Use the following - procedure: + If you are able to shut down your MySQL server, you can make a binary + backup that consists of all files used by <literal>InnoDB</literal> + to manage its tables. Use the following procedure: </para> <orderedlist> @@ -2591,12 +2608,12 @@ </para></listitem> <listitem><para> - Copy all your data files (<filename>ibdata</filename> files, + Copy all your data files (<filename>ibdata</filename> files and <filename>.ibd</filename> files) into a safe place. </para></listitem> <listitem><para> - Copy all your <filename>ib_logfile</filename>s to a safe place. + Copy all your <filename>ib_logfile</filename> files to a safe place. </para></listitem> <listitem><para> @@ -2612,9 +2629,9 @@ </orderedlist> <para> - Replication works with <literal>InnoDB</literal> type tables, so you - can use MySQL replication capabilities to keep a copy of your - database at database sites requiring high availability. + Replication works with <literal>InnoDB</literal> tables, so you can + use MySQL replication capabilities to keep a copy of your database at + database sites requiring high availability. </para> <para> @@ -2642,13 +2659,13 @@ </programlisting> <para> - To recover from a crash of your MySQL server process, the only thing - you have to do is to restart it. <literal>InnoDB</literal> - automatically checks the logs and performs a roll-forward of the - database to the present. <literal>InnoDB</literal> automatically - rolls back uncommitted transactions that were present at the time of - the crash. During recovery, <command>mysqld</command> displays output - something like this: + To recover from a crash of your MySQL server, the only requirement is + to restart it. <literal>InnoDB</literal> automatically checks the + logs and performs a roll-forward of the database to the present. + <literal>InnoDB</literal> automatically rolls back uncommitted + transactions that were present at the time of the crash. During + recovery, <command>mysqld</command> displays output something like + this: </para> <programlisting> @@ -2734,8 +2751,6 @@ into B-trees and other database structures. </para> -<!-- Note: Do not remove blank lines following the @item lines --> - <itemizedlist> <listitem><para> @@ -2754,8 +2769,8 @@ </para> <para> - Prevent the main thread from running. If a crash would occur in the - purge operation, this prevents it. + Prevent the main thread from running. If a crash would occur during + the purge operation, this prevents it. </para></listitem> <listitem><para> @@ -2797,12 +2812,13 @@ </itemizedlist> <para> - The database must not otherwise be used with any of these options - enabled! As a safety measure, <literal>InnoDB</literal> prevents - users from performing <literal>INSERT</literal>, - <literal>UPDATE</literal>, or <literal>DELETE</literal> operations - when <literal>innodb_force_recovery</literal> is set to a value - greater than 0. + <emphasis>The database must not otherwise be used with any of these + options enabled</emphasis>. As a safety measure, + <literal>InnoDB</literal> prevents users from performing + <literal>INSERT</literal>, <literal>UPDATE</literal>, or + <literal>DELETE</literal> operations when + <literal>innodb_force_recovery</literal> is set to a value greater + than 0. </para> <para> @@ -2826,41 +2842,42 @@ <para> <literal>InnoDB</literal> implements a checkpoint mechanism called a - ``fuzzy checkpoint.'' <literal>InnoDB</literal> flushes modified - database pages from the buffer pool in small batches. There is no - need to flush the buffer pool in one single batch, which would in - practice stop processing of user SQL statements for a while. + <firstterm>fuzzy checkpoint</firstterm>. <literal>InnoDB</literal> + flushes modified database pages from the buffer pool in small + batches. There is no need to flush the buffer pool in one single + batch, which would in practice temporarily halt processing of user + SQL statements. </para> <para> - In crash recovery, <literal>InnoDB</literal> looks for a checkpoint - label written to the log files. It knows that all modifications to - the database before the label are present in the disk image of the - database. Then <literal>InnoDB</literal> scans the log files forward - from the place of the checkpoint, applying the logged modifications - to the database. + During crash recovery, <literal>InnoDB</literal> looks for a + checkpoint label written to the log files. It knows that all + modifications to the database before the label are present in the + disk image of the database. Then <literal>InnoDB</literal> scans the + log files forward from the checkpoint, applying the logged + modifications to the database. </para> <para> - <literal>InnoDB</literal> writes to the log files in a circular - fashion. All committed modifications that make the database pages in + <literal>InnoDB</literal> writes to the log files on a rotating + basis. All committed modifications that make the database pages in the buffer pool different from the images on disk must be available in the log files in case <literal>InnoDB</literal> has to do a recovery. This means that when <literal>InnoDB</literal> starts to - reuse a log file in the circular fashion, it has to make sure that - the database page images on disk contain the modifications logged in - the log file <literal>InnoDB</literal> is going to reuse. In other - words, <literal>InnoDB</literal> has to make a checkpoint and often - this involves flushing of modified database pages to disk. + reuse a log file, it has to make sure that the database page images + on disk contain the modifications logged in the log file + <literal>InnoDB</literal> is going to reuse. In other words, + <literal>InnoDB</literal> must create a checkpoint and this often + involves flushing of modified database pages to disk. </para> <para> The preceding description explains why making your log files very - big may save disk I/O in checkpointing. It can make sense to set the - total size of the log files as big as the buffer pool or even - bigger. The drawback of big log files is that crash recovery can - take longer because there is more logged information to apply to the - database. + large may save disk I/O in checkpointing. It often makes sense to + set the total size of the log files as big as the buffer pool or + even bigger. The drawback of big log files is that crash recovery + can take longer because there is more logged information to apply to + the database. </para> </section> @@ -2889,15 +2906,15 @@ <para> Like <literal>MyISAM</literal> data files, <literal>InnoDB</literal> - data and log files are binary-compatible on all platforms if the - floating-point number format on the machines is the same. You can - move an <literal>InnoDB</literal> database simply by copying all the - relevant files, which were listed in <xref linkend="backing-up"/>. If - the floating-point formats on the machines are different but you have - not used <literal>FLOAT</literal> or <literal>DOUBLE</literal> data - types in your tables, then the procedure is the same: Just copy the - relevant files. If the formats are different and your tables contain - floating-point data, you have to use <command>mysqldump</command> to + data and log files are binary-compatible on all platforms having the + same floating-point number format. You can move an + <literal>InnoDB</literal> database simply by copying all the + relevant files listed in <xref linkend="backing-up"/>. If the + floating-point formats differ but you have not used + <literal>FLOAT</literal> or <literal>DOUBLE</literal> data types in + your tables, then the procedure is the same: simply copy the + relevant files. If the formats differ and your tables contain + floating-point data, you must use <command>mysqldump</command> to dump your tables on one machine and then import the dump files on the other machine. </para> @@ -2905,9 +2922,8 @@ <para> One way to increase performance is to switch off autocommit mode when importing data, assuming that the tablespace has enough space for the - big rollback segment that the big import transactions generate. Do - the commit only after importing a whole table or a segment of a - table. + big rollback segment that the import transactions generate. Do the + commit only after importing a whole table or a segment of a table. </para> </section> @@ -3289,22 +3305,20 @@ </programlisting> <para> - In row-level locking, <literal>InnoDB</literal> uses so-called - ``next-key locking.'' That means that besides index records, - <literal>InnoDB</literal> can also lock the ``gap'' before an index - record to block insertions by other users immediately before the - index record. A next-key lock refers to a lock that locks an index - record and the gap before it. A gap lock refers to a lock that only - locks a gap before some index record. + In row-level locking, <literal>InnoDB</literal> uses next-key + locking. That means that besides index records, + <literal>InnoDB</literal> can also lock the <quote>gap</quote> + preceding an index record to block insertions by other users + immediately before the index record. A next-key lock refers to a + lock that locks an index record and the gap before it. A gap lock + refers to a lock that only locks a gap before some index record. </para> <para> A detailed description of each isolation level in - <literal>InnoDB</literal>: + <literal>InnoDB</literal> follows: </para> -<!-- Note: Do not remove blank lines following the @item lines --> - <itemizedlist> <listitem><para> @@ -3315,8 +3329,8 @@ <literal>SELECT</literal> statements are performed in a non-locking fashion, but a possible earlier version of a record might be used. Thus, using this isolation level, such reads are not - ``consistent.'' This is also called ``dirty read.'' Other than - that, this isolation level works like <literal>READ + consistent. This is also called a <quote>dirty read</quote>. + Otherwise, this isolation level works like <literal>READ COMMITTED</literal>. </para></listitem> @@ -3328,16 +3342,16 @@ A somewhat Oracle-like isolation level. All <literal>SELECT ... FOR UPDATE</literal> and <literal>SELECT ... LOCK IN SHARE MODE</literal> statements lock only the index records, not the gaps - before them, and thus allow free inserting of new records next to - locked records. <literal>UPDATE</literal> and - <literal>DELETE</literal> statements that use a unique index with a + before them, and thus allow the free insertion of new records next + to locked records. <literal>UPDATE</literal> and + <literal>DELETE</literal> statements using a unique index with a unique search condition lock only the index record found, not the gap before it. In range-type <literal>UPDATE</literal> and <literal>DELETE</literal> statements, <literal>InnoDB</literal> must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary because - ``phantom rows'' must be blocked for MySQL replication and recovery - to work. + <quote>phantom rows</quote> must be blocked for MySQL replication + and recovery to work. </para> <para> @@ -3527,23 +3541,24 @@ <para> In row-level locking, <literal>InnoDB</literal> uses an algorithm - called ``next-key locking.'' <literal>InnoDB</literal> does the - row-level locking in such a way that when it searches or scans an - index of a table, it sets shared or exclusive locks on the index - records it encounters. Thus the row-level locks are actually index - record locks. + called <emphasis>next-key locking</emphasis>. + <literal>InnoDB</literal> performs the row-level locking in such a + way that when it searches or scans an index of a table, it sets + shared or exclusive locks on the index records it encounters. Thus + the row-level locks are actually index record locks. </para> <para> The locks <literal>InnoDB</literal> sets on index records also - affect the ``gap'' before that index record. If a user has a shared - or exclusive lock on record <literal>R</literal> in an index, - another user cannot insert a new index record immediately before - <literal>R</literal> in the index order. This locking of gaps is - done to prevent the so-called ``phantom problem.'' Suppose that you - want to read and lock all children from the <literal>child</literal> - table with an identifier value larger than 100, with the intent of - updating some column in the selected rows later: + affect the <quote>gap</quote> before that index record. If a user + has a shared or exclusive lock on record <literal>R</literal> in an + index, another user cannot insert a new index record immediately + before <literal>R</literal> in the index order. This locking of gaps + is done to prevent the so-called <quote>phantom problem</quote>. + Suppose that you want to read and lock all children from the + <literal>child</literal> table having an identifier value greater + than 100, with the intent of updating some column in the selected + rows later: </para> <programlisting> @@ -3605,9 +3620,12 @@ </para> <para> - This is called ``multi-versioned concurrency control.'' + This is called <firstterm>multi-versioned concurrency + control</firstterm>. </para> +<!-- TODO: More ASCII art that needs to be turned into a graphic. --> + <programlisting> User A User B @@ -3640,9 +3658,9 @@ </para> <para> - If you want to see the ``freshest'' state of the database, you - should use either the <literal>READ COMMITTED</literal> isolation - level or a locking read: + If you want to see the <quote>freshest</quote> state of the + database, you should use either the <literal>READ + COMMITTED</literal> isolation level or a locking read: </para> <programlisting> @@ -3795,7 +3813,7 @@ <title id='title-innodb-implicit-command-or-rollback'>&title-innodb-implicit-command-or-rollback;</title> <!-- - TODO: probably should merge this into the SQL chapter section that + TODO: Probably should merge this into the SQL chapter section that also lists these conditions. --> @@ -3940,7 +3958,7 @@ <listitem><para> Access your tables and rows in a fixed order. Then transactions - form nice queues and do not deadlock. + form well-defined queues and do not deadlock. </para></listitem> <listitem><para> @@ -3954,7 +3972,7 @@ Use less locking. If you can afford to allow a <literal>SELECT</literal> to return data from an old snapshot, do not add the clause <literal>FOR UPDATE</literal> or <literal>LOCK - IN SHARE MODE</literal> to it. Using <literal>READ + IN SHARE MODE</literal> to it. Using the <literal>READ COMMITTED</literal> isolation level is good here, because each consistent read within the same transaction reads from its own fresh snapshot. @@ -3966,7 +3984,7 @@ transactional tables, like InnoDB, is to set <literal>AUTOCOMMIT = 0</literal> and not to call <literal>UNLOCK TABLES</literal> until you commit the transaction explicitly. For example, if you need to - write table <literal>t1</literal> and read table + write to table <literal>t1</literal> and read from table <literal>t2</literal>, you can do this: </para> @@ -3995,9 +4013,9 @@ </para></listitem> <listitem><para> - In applications using <literal>AUTOCOMMIT=1</literal> and MySQL's - <literal>LOCK TABLES</literal> command, MySQL 5.0 does not set - InnoDB table locks if <literal>AUTOCOMMIT=1</literal>. + In applications using MySQL's <literal>LOCK TABLES</literal> + command, MySQL 5.0 does not set InnoDB table locks if + <literal>AUTOCOMMIT=1</literal>. </para></listitem> </itemizedlist> @@ -4022,7 +4040,7 @@ less than 70%, your workload is probably disk-bound. Maybe you are making too many transaction commits, or the buffer pool is too small. Making the buffer pool bigger can help, but do not set it - bigger than 80% of physical memory. + equal to more than 80% of physical memory. </para></listitem> <listitem><para> @@ -4031,7 +4049,8 @@ transaction commit if that transaction made modifications to the database. Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the - same 167th/second if the disk does not fool the operating system. + same 167<superscript>th</superscript> of a second if the disk does + not <quote>fool</quote> the operating system. </para></listitem> <listitem><para> @@ -4075,11 +4094,10 @@ <listitem><para> In some versions of GNU/Linux and Unix, flushing files to disk with - the Unix <literal>fsync()</literal> and other similar methods is - surprisingly slow. The default method that <literal>InnoDB</literal> - uses is the <literal>fsync()</literal> function. If you are not - satisfied with the database write performance, you might try setting - <literal>innodb_flush_method</literal> in + the Unix <literal>fsync()</literal> (which <literal>InnoDB</literal> + uses by default) and other similar methods is surprisingly slow. If + you are dissatisfied with database write performance, you might try + setting <literal>innodb_flush_method</literal> in <filename>my.cnf</filename> to <literal>O_DSYNC</literal>, although <literal>O_DSYNC</literal> seems to be slower on most systems. </para></listitem> @@ -4089,10 +4107,10 @@ architecture (AMD Opteron), it is important to to mount any filesystems used for storing InnoDB-related files using the <literal>forcedirectio</literal> option. (The default on Solaris - 10/x86_64 is <emphasis role="bold">not</emphasis> to use this - filesystem mounting option.) Failing to use - <literal>forcedirectio</literal> will cause a serious degradation of - InnoDB's speed and performance on this platform. + 10/x86_64 is <emphasis>not</emphasis> to use this filesystem + mounting option.) Failing to use <literal>forcedirectio</literal> + will cause a serious degradation of InnoDB's speed and performance + on this platform. </para></listitem> <listitem><para> @@ -4121,18 +4139,19 @@ Beware of big rollbacks of mass inserts: <literal>InnoDB</literal> uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback - can take 30 times the time of the corresponding insert. Killing the - database process does not help because the rollback starts again at - the server startup. The only way to get rid of a runaway rollback is - to increase the buffer pool so that the rollback becomes CPU-bound - and runs fast, or to use a special procedure. See + can take 30 times as long to perform as the corresponding insert. + Killing the database process does not help because the rollback + starts again on server startup. The only way to get rid of a runaway + rollback is to increase the buffer pool so that the rollback becomes + CPU-bound and runs fast, or to use a special procedure. See <xref linkend="forcing-recovery"/>. </para></listitem> <listitem><para> Beware also of other big disk-bound operations. Use <literal>DROP - TABLE + CREATE TABLE</literal> to empty a table, not <literal>DELETE - FROM <replaceable>tbl_name</replaceable></literal>. + TABLE</literal> and <literal>CREATE TABLE</literal> to empty a + table, not <literal>DELETE FROM + <replaceable>tbl_name</replaceable></literal>. </para></listitem> <listitem><para> @@ -4285,11 +4304,12 @@ parser: The only things that matter are the table name <literal>innodb_monitor</literal> and that it be an <literal>InnoDB</literal> table. The structure of the table is not - relevant at all for the <literal>InnoDB</literal> Monitor. If you - shut down the server when the monitor is running, and you want to - start the monitor again, you must drop the table before you can - issue a new <literal>CREATE TABLE</literal> statement to start the - monitor. This syntax may change in a future release. + relevant at all for the <command><literal>InnoDB</literal> + Monitor</command>. If you shut down the server when the monitor is + running, and you want to start the monitor again, you must drop the + table before you can issue a new <literal>CREATE TABLE</literal> + statement to start the monitor. This syntax may change in a future + release. </para> <para> @@ -4304,7 +4324,7 @@ </para> <para> - A sample of <literal>InnoDB</literal> Monitor output: + A sample of <command><literal>InnoDB</literal> Monitor</command> output: </para> <programlisting> @@ -4619,10 +4639,10 @@ batches at about the same rate in the table, it is possible that the purge thread starts to lag behind, and the table grows bigger and bigger, making everything disk-bound and very slow. Even if the table - would carry just 10 MB of useful data, it may grow to occupy 10 GB - with all the dead rows. In such a case, it would be good to throttle - new row operations, and allocate more resources for the purge thread. - The startup option and settable global variable + carries just 10 MB of useful data, it may grow to occupy 10 GB with + all the <quote>dead</quote> rows. In such a case, it would be good to + throttle new row operations, and allocate more resources to the purge + thread. The startup option and settable global variable <literal>innodb_max_purge_lag</literal> exists for exactly this purpose. See <xref linkend="innodb-start"/> for more information. </para> @@ -4907,9 +4927,9 @@ <para> <literal>InnoDB</literal> uses a novel file flush technique called - doublewrite. It adds safety to recovery following an operating - system crash or a power outage, and improves performance on most - varieties of Unix by reducing the need for + <firstterm>doublewrite</firstterm>. It adds safety to recovery + following an operating system crash or a power outage, and improves + performance on most varieties of Unix by reducing the need for <literal>fsync()</literal> operations. </para> @@ -5022,10 +5042,10 @@ <para> The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of 64 consecutive pages. - The ``files'' inside a tablespace are called segments in - <literal>InnoDB</literal>. The name of the ``rollback segment'' is - somewhat confusing because it actually contains many segments in the - tablespace. + The <quote>files</quote> inside a tablespace are called + <firstterm>segments</firstterm> in <literal>InnoDB</literal>. The + term <quote>rollback segment</quote> is somewhat confusing because + it actually contains many tablespace segments. </para> <para> @@ -5098,15 +5118,15 @@ SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345; </programlisting> - takes more time than 'it should take'. (Above we are fooling the SQL - optimizer to scan the clustered index, not a secondary index.) Most - disks can read 10 - 50 MB/s. That can be used to estimate how fast a - table scan should run. + takes more time than it should. (In the above query, we are + <quote>fooling</quote> the SQL optimizer into scanning the clustered + index, not a secondary index.) Most disks can read 10 - 50 MB/s. + This can be used to estimate how fast a table scan should run. </para> <para> - It can speed up index scans if you periodically perform a ``null'' - <literal>ALTER TABLE</literal> operation: + It can speed up index scans if you periodically perform a + <quote>null</quote> <literal>ALTER TABLE</literal> operation: </para> <programlisting> @@ -5123,7 +5143,7 @@ <para> If the insertions to an index are always ascending and records are deleted only from the end, the <literal>InnoDB</literal> file space - management algorithm guarantees that fragmentation in the index does + management algorithm guarantees that fragmentation of the index does not occur. </para> @@ -5163,7 +5183,8 @@ </para></listitem> <listitem><para> - A ``row too long'' error rolls back the SQL statement. + A <literal>row too long error</literal> rolls back the SQL + statement. </para></listitem> <listitem><para> @@ -5178,7 +5199,7 @@ <para> During implicit rollbacks, as well as during the execution of an explicit <literal>ROLLBACK</literal> SQL command, <literal>SHOW - PROCESSLIST</literal> displays "Rolling back" in the + PROCESSLIST</literal> displays <literal>Rolling back</literal> in the <literal>State</literal> column for the relevant connection. </para> @@ -5885,6 +5906,8 @@ </section> +<!-- TODO: Move this info to new Restrictions appendix? --> + <section id="innodb-restrictions"> <title id='title-innodb-restrictions'>&title-innodb-restrictions;</title> @@ -5915,7 +5938,7 @@ </para></listitem> <listitem><para> - On some old operating systems, data files must be less than 2GB. + On some older operating systems, data files must be less than 2GB. </para></listitem> <listitem><para> @@ -6105,7 +6128,7 @@ </para></listitem> <listitem><para> - Currently, triggers are not activated by cascaded foreign key + In MySQL 5.0, triggers are not yet activated by cascaded foreign key actions. </para></listitem> @@ -6230,15 +6253,15 @@ <para> If MySQL crashes in the middle of an <literal>ALTER TABLE</literal> operation, you may end up with an orphaned temporary table inside - the <literal>InnoDB</literal> tablespace. With - <literal>innodb_table_monitor</literal> you see a table whose name - is <filename>#sql-...</filename>. Starting from MySQL 4.0.6, you can - perform SQL statements also on tables whose name contains the - character '<literal>#</literal>' if you enclose the name in - backticks. Thus, you can drop such an orphaned table like any other - orphaned table with the method described above. Note that to copy or - rename a file in the Unix shell, you need to put the file name in - double quotes if the file name contains '<literal>#</literal>'. + the <literal>InnoDB</literal> tablespace. Using + <literal>innodb_table_monitor</literal> you can see listed a table + whose name is <filename>#sql-...</filename>. In MySQL 5.0, you can + perform SQL statements on tables whose name contains the character + '<literal>#</literal>' if you enclose the name in backticks. Thus, + you can drop such an orphaned table like any other orphaned table + using the method described above. Note that to copy or rename a file + in the Unix shell, you need to put the file name in double quotes if + the file name contains '<literal>#</literal>'. </para> </section> --- 1.3/refman/fixedchars.ent 2005-07-09 07:13:42 +10:00 +++ 1.4/refman/fixedchars.ent 2005-07-20 01:32:36 +10:00 @@ -240,3 +240,4 @@ <!ENTITY ulcrop "⌏"> <!ENTITY urcrop "⌎"> <!ENTITY pi "ϖ"> + <!ENTITY euro "€"> --- 1.8/refman-5.0/Status 2005-07-19 19:50:27 +10:00 +++ 1.9/refman-5.0/Status 2005-07-20 01:32:35 +10:00 @@ -15,10 +15,10 @@ installing using-mysql-programs client-side-scripts + innodb - 5.0 chapters awaiting edits: optimization - innodb ndbcluster error-handling limits
| Thread | ||
|---|---|---|
| • bk commit - mysqldoc@docsrva tree (jon:1.3067) | jon | 19 Jul |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.998 sec. using MySQL 5.1.14-beta-log