InnoDB
Performance Tuning and Troubleshooting
1296
•
appr. key vals
: The approximate index cardinality.
•
leaf pages
: The approximate number of leaf pages in the index.
•
size pages
: The approximate total number of pages in the index.
•
FIELDS
: The names of the fields in the index. For a clustered index that was generated
automatically, the field list begins with the internal
DB_ROW_ID
(row ID) field.
DB_TRX_ID
and
DB_ROLL_PTR
are always added internally to the clustered index, following the fields that comprise
the primary key. For a secondary index, the final fields are those from the primary key that are not
part of the secondary index.
The end of the table section lists the
FOREIGN KEY
definitions that apply to the table. This information
appears whether the table is a referencing or referenced table.
14.2.12.3.
InnoDB
General Troubleshooting
The following general guidelines apply to troubleshooting
InnoDB
problems:
• When an operation fails or you suspect a bug, you should look at the MySQL server error log (see
Section 5.2.1, “The Error Log”
).
• Issues relating to the
InnoDB
data dictionary include failed
CREATE TABLE
statements (orphaned
table files), inability to open
.InnoDB
files, and
system cannot find the path specified
errors. For information about these sorts of problems and errors, see
Section 14.2.12.4,
“Troubleshooting
InnoDB
Data Dictionary Operations”
.
• When troubleshooting, it is usually best to run the MySQL server from the command prompt, rather
than through
mysqld_safe
or as a Windows service. You can then see what
mysqld
prints to the
console, and so have a better grasp of what is going on. On Windows, start
mysqld
with the
--
console
[404]
option to direct the output to the console window.
• Use the
InnoDB
Monitors to obtain information about a problem (see
Section 14.2.12.2, “
SHOW
ENGINE INNODB STATUS
and the
InnoDB
Monitors”
). If the problem is performance-related, or
your server appears to be hung, you should use the standard Monitor to print information about
the internal state of
InnoDB
. If the problem is with locks, use the Lock Monitor. If the problem is in
creation of tables or other data dictionary operations, use the Table Monitor to print the contents of
the
InnoDB
internal data dictionary. To see tablespace information use the Tablespace Monitor.
• If you suspect that a table is corrupt, run
CHECK TABLE
on that table.
14.2.12.4. Troubleshooting
InnoDB
Data Dictionary Operations
A specific issue with tables is that the MySQL server keeps data dictionary information in
.frm
files
it stores in the database directories, whereas
InnoDB
also stores the information into its own data
dictionary inside the tablespace files. If you move
.frm
files around, or if the server crashes in the
middle of a data dictionary operation, the locations of the
.frm
files may end up out of synchrony with
the locations recorded in the
InnoDB
internal data dictionary.
A symptom of an out-of-sync data dictionary is that a
CREATE TABLE
statement fails. If this occurs,
you should look in the server's error log. If the log says that the table already exists inside the
InnoDB
internal data dictionary, you have an orphaned table inside the
InnoDB
tablespace files that has no
corresponding
.frm
file. The error message looks like this:
InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...