MySQL数据库

开发平台：

Visual C++

manual.texi：源码内容

| 0 |
+------------------------+
1 row in set (0.32 sec)
@end example
The above shows that @strong{MySQL} can execute 1,000,000 @code{+}
expressions in 0.32 seconds on a @code{PentiumII 400MHz}.
All @strong{MySQL} functions should be very optimized, but there may be
some exceptions, and the @code{benchmark(loop_count,expression)} is a
great tool to find out if this is a problem with your query.
@menu
* Estimating performance:: Estimating query performance
* SELECT speed:: Speed of @code{SELECT} queries
* Where optimizations:: How MySQL optimizes @code{WHERE} clauses
* DISTINCT optimization:: How MySQL Optimizes @code{DISTINCT}
* LEFT JOIN optimization:: How MySQL optimizes @code{LEFT JOIN}
* LIMIT optimization:: How MySQL optimizes @code{LIMIT}
* Insert speed:: Speed of @code{INSERT} queries
* Update speed:: Speed of @code{UPDATE} queries
* Delete speed:: Speed of @code{DELETE} queries
@end menu
@cindex estimating, query performance
@cindex queries, estimating performance
@cindex performance, estimating
@node Estimating performance, SELECT speed, Query Speed, Query Speed
@subsection Estimating Query Performance
In most cases you can estimate the performance by counting disk seeks.
For small tables, you can usually find the row in 1 disk seek (as the
index is probably cached). For bigger tables, you can estimate that
(using B++ tree indexes) you will need: @code{log(row_count) /
log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) +
1} seeks to find a row.
In @strong{MySQL} an index block is usually 1024 bytes and the data
pointer is usually 4 bytes. A 500,000 row table with an
index length of 3 (medium integer) gives you:
@code{log(500,000)/log(1024/3*2/(3+4)) + 1} = 4 seeks.
As the above index would require about 500,000 * 7 * 3/2 = 5.2M,
(assuming that the index buffers are filled to 2/3, which is typical)
you will probably have much of the index in memory and you will probably
only need 1-2 calls to read data from the OS to find the row.
For writes, however, you will need 4 seek requests (as above) to find
where to place the new index and normally 2 seeks to update the index
and write the row.
Note that the above doesn't mean that your application will slowly
degenerate by N log N! As long as everything is cached by the OS or SQL
server things will only go marginally slower while the table gets
bigger. After the data gets too big to be cached, things will start to
go much slower until your applications is only bound by disk-seeks
(which increase by N log N). To avoid this, increase the index cache as
the data grows. @xref{Server parameters}.
@cindex speed, of queries
@findex SELECT speed
@node SELECT speed, Where optimizations, Estimating performance, Query Speed
@subsection Speed of @code{SELECT} Queries
In general, when you want to make a slow @code{SELECT ... WHERE} faster, the
first thing to check is whether or not you can add an index. @xref{MySQL
indexes, , @strong{MySQL} indexes}. All references between different tables
should usually be done with indexes. You can use the @code{EXPLAIN} command
to determine which indexes are used for a @code{SELECT}.
@xref{EXPLAIN, , @code{EXPLAIN}}.
Some general tips:
@itemize @bullet
@item
To help @strong{MySQL} optimize queries better, run @code{myisamchk
--analyze} on a table after it has been loaded with relevant data. This
updates a value for each index part that indicates the average number of
rows that have the same value. (For unique indexes, this is always 1,
of course.). @strong{MySQL} will use this to decide which index to
choose when you connect two tables with 'a non-constant expression'.
You can check the result from the @code{analyze} run by doing @code{SHOW
INDEX FROM table_name} and examining the @code{Cardinality} column.
@item
To sort an index and data according to an index, use @code{myisamchk
--sort-index --sort-records=1} (if you want to sort on index 1). If you
have a unique index from which you want to read all records in order
according to that index, this is a good way to make that faster. Note,
however, that this sorting isn't written optimally and will take a long
time for a large table!
@end itemize
@cindex optimizations
@findex WHERE
@node Where optimizations, DISTINCT optimization, SELECT speed, Query Speed
@subsection How MySQL Optimizes @code{WHERE} Clauses
The @code{WHERE} optimizations are put in the @code{SELECT} part here because
they are mostly used with @code{SELECT}, but the same optimizations apply for
@code{WHERE} in @code{DELETE} and @code{UPDATE} statements.
Also note that this section is incomplete. @strong{MySQL} does many
optimizations, and we have not had time to document them all.
Some of the optimizations performed by @strong{MySQL} are listed below:
@itemize @bullet
@item
Removal of unnecessary parentheses:
@example
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
@end example
@item
Constant folding:
@example
(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
@end example
@item
Constant condition removal (needed because of constant folding):
@example
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
@end example
@item
Constant expressions used by indexes are evaluated only once.
@item
@code{COUNT(*)} on a single table without a @code{WHERE} is retrieved
directly from the table information. This is also done for any @code{NOT NULL}
expression when used with only one table.
@item
Early detection of invalid constant expressions. @strong{MySQL} quickly
detects that some @code{SELECT} statements are impossible and returns no rows.
@item
@code{HAVING} is merged with @code{WHERE} if you don't use @code{GROUP BY}
or group functions (@code{COUNT()}, @code{MIN()}...).
@item
For each sub-join, a simpler @code{WHERE} is constructed to get a fast
@code{WHERE} evaluation for each sub-join and also to skip records as
soon as possible.
@cindex constant table
@cindex tables, constant
@item
All constant tables are read first, before any other tables in the query.
A constant table is:
@itemize @minus
@item
An empty table or a table with 1 row.
@item
A table that is used with a @code{WHERE} clause on a @code{UNIQUE}
index, or a @code{PRIMARY KEY}, where all index parts are used with constant
expressions and the index parts are defined as @code{NOT NULL}.
@end itemize
All the following tables are used as constant tables:
@example
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
@end example
@item
The best join combination to join the tables is found by trying all
possibilities. If all columns in @code{ORDER BY} and in @code{GROUP
BY} come from the same table, then this table is preferred first when
joining.
@item
If there is an @code{ORDER BY} clause and a different @code{GROUP BY}
clause, or if the @code{ORDER BY} or @code{GROUP BY} contains columns
from tables other than the first table in the join queue, a temporary
table is created.
@item
If you use @code{SQL_SMALL_RESULT}, @strong{MySQL} will use an in-memory
temporary table.
@item
Each table index is queried, and the best index that spans fewer than 30% of
the rows is used. If no such index can be found, a quick table scan is used.
@item
In some cases, @strong{MySQL} can read rows from the index without even
consulting the data file. If all columns used from the index are numeric,
then only the index tree is used to resolve the query.
@item
Before each record is output, those that do not match the @code{HAVING} clause
are skipped.
@end itemize
Some examples of queries that are very fast:
@example
mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;
@end example
The following queries are resolved using only the index tree (assuming
the indexed columns are numeric):
@example
mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;
@end example
The following queries use indexing to retrieve the rows in sorted
order without a separate sorting pass:
@example
mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...
@end example
@findex DISTINCT
@cindex optimizing, DISTINCT
@node DISTINCT optimization, LEFT JOIN optimization, Where optimizations, Query Speed
@subsection How MySQL Optimizes @code{DISTINCT}
@code{DISTINCT} is converted to a @code{GROUP BY} on all columns,
@code{DISTINCT} combined with @code{ORDER BY} will in many cases also
need a temporary table.
When combining @code{LIMIT #} with @code{DISTINCT}, @strong{MySQL} will stop
as soon as it finds @code{#} unique rows.
If you don't use columns from all used tables, @strong{MySQL} will stop
the scanning of the not used tables as soon as it has found the first match.
@example
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
@end example
In the case, assuming t1 is used before t2 (check with @code{EXPLAIN}), then
@strong{MySQL} will stop reading from t2 (for that particular row in t1)
when the first row in t2 is found.
@findex LEFT JOIN
@cindex optimizing, LEFT JOIN
@node LEFT JOIN optimization, LIMIT optimization, DISTINCT optimization, Query Speed
@subsection How MySQL Optimizes @code{LEFT JOIN} and @code{RIGHT JOIN}
@code{A LEFT JOIN B} in @strong{MySQL} is implemented as follows:
@itemize @bullet
@item
The table @code{B} is set to be dependent on table @code{A} and all tables
that @code{A} is dependent on.
@item
The table @code{A} is set to be dependent on all tables (except @code{B})
that are used in the @code{LEFT JOIN} condition.
@item
All @code{LEFT JOIN} conditions are moved to the @code{WHERE} clause.
@item
All standard join optimizations are done, with the exception that a table is
always read after all tables it is dependent on. If there is a circular
dependence then @strong{MySQL} will issue an error.
@item
All standard @code{WHERE} optimizations are done.
@item
If there is a row in @code{A} that matches the @code{WHERE} clause, but there
wasn't any row in @code{B} that matched the @code{LEFT JOIN} condition,
then an extra @code{B} row is generated with all columns set to @code{NULL}.
@item
If you use @code{LEFT JOIN} to find rows that don't exist in some
table and you have the following test: @code{column_name IS NULL} in the
@code{WHERE} part, where column_name is a column that is declared as
@code{NOT NULL}, then @strong{MySQL} will stop searching after more rows
(for a particular key combination) after it has found one row that
matches the @code{LEFT JOIN} condition.
@end itemize
@code{RIGHT JOIN} is implemented analogously as @code{LEFT JOIN}.
The table read order forced by @code{LEFT JOIN} and @code{STRAIGHT JOIN}
will help the join optimizer (which calculates in which order tables
should be joined) to do its work much more quickly, as there are fewer
table permutations to check.
Note that the above means that if you do a query of type:
@example
SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key
@end example
@strong{MySQL} will do a full scan on @code{b} as the @code{LEFT
JOIN} will force it to be read before @code{d}.
The fix in this case is to change the query to:
@example
SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key
@end example
@cindex optimizing, LIMIT
@findex LIMIT
@node LIMIT optimization, Insert speed, LEFT JOIN optimization, Query Speed
@subsection How MySQL Optimizes @code{LIMIT}
In some cases @strong{MySQL} will handle the query differently when you are
using @code{LIMIT #} and not using @code{HAVING}:
@itemize @bullet
@item
If you are selecting only a few rows with @code{LIMIT}, @strong{MySQL}
will use indexes in some cases when it normally would prefer to do a
full table scan.
@item
If you use @code{LIMIT #} with @code{ORDER BY}, @strong{MySQL} will end the
sorting as soon as it has found the first @code{#} lines instead of sorting
the whole table.
@item
When combining @code{LIMIT #} with @code{DISTINCT}, @strong{MySQL} will stop
as soon as it finds @code{#} unique rows.
@item
In some cases a @code{GROUP BY} can be resolved by reading the key in order
(or do a sort on the key) and then calculate summaries until the
key value changes. In this case @code{LIMIT #} will not calculate any
unnecessary @code{GROUP BY}'s.
@item
As soon as @strong{MySQL} has sent the first @code{#} rows to the client, it
will abort the query.
@item
@code{LIMIT 0} will always quickly return an empty set. This is useful
to check the query and to get the column types of the result columns.
@item
The size of temporary tables uses the @code{LIMIT #} to calculate how much
space is needed to resolve the query.
@end itemize
@cindex speed, inserting
@cindex inserting, speed of
@node Insert speed, Update speed, LIMIT optimization, Query Speed
@subsection Speed of @code{INSERT} Queries
The time to insert a record consists approximately of:
@itemize @bullet
@item
Connect: (3)
@item
Sending query to server: (2)
@item
Parsing query: (2)
@item
Inserting record: (1 x size of record)
@item
Inserting indexes: (1 x number of indexes)
@item
Close: (1)
@end itemize
where the numbers are somewhat proportional to the overall time. This
does not take into consideration the initial overhead to open tables
(which is done once for each concurrently running query).
The size of the table slows down the insertion of indexes by N log N
(B-trees).
Some ways to speed up inserts:
@itemize @bullet
@item
If you are inserting many rows from the same client at the same time, use
multiple value lists @code{INSERT} statements. This is much faster (many
times in some cases) than using separate @code{INSERT} statements.
@item
If you are inserting a lot of rows from different clients, you can get
higher speed by using the @code{INSERT DELAYED} statement. @xref{INSERT,
, @code{INSERT}}.
@item
Note that with @code{MyISAM} you can insert rows at the same time
@code{SELECT}s are running if there are no deleted rows in the tables.
@item
When loading a table from a text file, use @code{LOAD DATA INFILE}. This
is usually 20 times faster than using a lot of @code{INSERT} statements.
@xref{LOAD DATA, , @code{LOAD DATA}}.
@item
It is possible with some extra work to make @code{LOAD DATA INFILE} run even
faster when the table has many indexes. Use the following procedure:
@enumerate
@item
Optionally create the table with @code{CREATE TABLE}. For example, using
@code{mysql} or Perl-DBI.
@item
Execute a @code{FLUSH TABLES} statement or the shell command @code{mysqladmin
flush-tables}.
@item
Use @code{myisamchk --keys-used=0 -rq /path/to/db/tbl_name}. This will
remove all usage of all indexes from the table.
@item
Insert data into the table with @code{LOAD DATA INFILE}. This will not
update any indexes and will therefore be very fast.
@item
If you are going to only read the table in the future, run @code{myisampack}
on it to make it smaller. @xref{Compressed format}.
@item
Re-create the indexes with @code{myisamchk -r -q
/path/to/db/tbl_name}. This will create the index tree in memory before
writing it to disk, which is much faster because it avoids lots of disk
seeks. The resulting index tree is also perfectly balanced.
@item
Execute a @code{FLUSH TABLES} statement or the shell command @code{mysqladmin
flush-tables}.
@end enumerate
This procedure will be built into @code{LOAD DATA INFILE} in some future
version of MySQL.
@item
You can speed up insertions by locking your tables:
@example
mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;
@end example
The main speed difference is that the index buffer is flushed to disk only
once, after all @code{INSERT} statements have completed. Normally there would
be as many index buffer flushes as there are different @code{INSERT}
statements. Locking is not needed if you can insert all rows with a single
statement.
Locking will also lower the total time of multi-connection tests, but the
maximum wait time for some threads will go up (because they wait for
locks). For example:
@example
thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts
@end example
If you don't use locking, 2, 3, and 4 will finish before 1 and 5. If you
use locking, 2, 3, and 4 probably will not finish before 1 or 5, but the
total time should be about 40% faster.
As @code{INSERT}, @code{UPDATE}, and @code{DELETE} operations are very
fast in @strong{MySQL}, you will obtain better overall performance by
adding locks around everything that does more than about 5 inserts or
updates in a row. If you do very many inserts in a row, you could do a
@code{LOCK TABLES} followed by an @code{UNLOCK TABLES} once in a while
(about each 1000 rows) to allow other threads access to the table. This
would still result in a nice performance gain.
Of course, @code{LOAD DATA INFILE} is much faster for loading data.
@end itemize
To get some more speed for both @code{LOAD DATA INFILE} and
@code{INSERT}, enlarge the key buffer. @xref{Server parameters}.
@node Update speed, Delete speed, Insert speed, Query Speed
@subsection Speed of @code{UPDATE} Queries
Update queries are optimized as a @code{SELECT} query with the additional
overhead of a write. The speed of the write is dependent on the size of
the data that is being updated and the number of indexes that are
updated. Indexes that are not changed will not be updated.
Also, another way to get fast updates is to delay updates and then do
many updates in a row later. Doing many updates in a row is much quicker
than doing one at a time if you lock the table.
Note that, with dynamic record format, updating a record to
a longer total length may split the record. So if you do this often,
it is very important to @code{OPTIMIZE TABLE} sometimes.
@xref{OPTIMIZE TABLE, , @code{OPTIMIZE TABLE}}.
@node Delete speed, , Update speed, Query Speed
@subsection Speed of @code{DELETE} Queries
If you want to delete all rows in the table, you should use
@code{TRUNCATE TABLE table_name}. @xref{TRUNCATE}.
The time to delete a record is exactly proportional to the number of
indexes. To delete records more quickly, you can increase the size of
the index cache. @xref{Server parameters}.
@cindex optimization, tips
@cindex tips, optimization
@node Tips, Benchmarks, Query Speed, Performance
@section Other Optimization Tips
Unsorted tips for faster systems:
@itemize @bullet
@item
Use persistent connections to the database to avoid the connection
overhead. If you can't use persistent connections and you are doing a
lot of new connections to the database, you may want to change the value
of the @code{thread_cache_size} variable. @xref{Server parameters}.
@item
Always check that all your queries really use the indexes you have created
in the tables. In @strong{MySQL} you can do this with the @code{EXPLAIN}
command. @xref{EXPLAIN, Explain, Explain, manual}.
@item
Try to avoid complex @code{SELECT} queries on tables that are updated a
lot. This is to avoid problems with table locking.
@item
The new @code{MyISAM} tables can insert rows in a table without deleted
rows at the same time another table is reading from it. If this is important
for you, you should consider methods where you don't have to delete rows
or run @code{OPTIMIZE TABLE} after you have deleted a lot of rows.
@item
Use @code{ALTER TABLE ... ORDER BY expr1,expr2...} if you mostly
retrieve rows in expr1,expr2.. order. By using this option after big
changes to the table, you may be able to get higher performance.
@item
In some cases it may make sense to introduce a column that is 'hashed'
based on information from other columns. If this column is short and
reasonably unique it may be much faster than a big index on many
columns. In @strong{MySQL} it's very easy to use this extra column:
@code{SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2))
AND col_1='constant' AND col_2='constant'}
@item
For tables that change a lot you should try to avoid all @code{VARCHAR}
or @code{BLOB} columns. You will get dynamic row length as soon as you
are using a single @code{VARCHAR} or @code{BLOB} column. @xref{Table
types}.
@item
It's not normally useful to split a table into different tables just
because the rows gets 'big'. To access a row, the biggest performance
hit is the disk seek to find the first byte of the row. After finding
the data most new disks can read the whole row fast enough for most
applications. The only cases where it really matters to split up a table is if
it's a dynamic row size table (see above) that you can change to a fixed
row size, or if you very often need to scan the table and don't need
most of the columns. @xref{Table types}.
@item
If you very often need to calculate things based on information from a
lot of rows (like counts of things), it's probably much better to
introduce a new table and update the counter in real time. An update of
type @code{UPDATE table set count=count+1 where index_column=constant}
is very fast!
This is really important when you use databases like @strong{MySQL} that
only have table locking (multiple readers / single writers). This will
also give better performance with most databases, as the row locking
manager in this case will have less to do.
@item
If you need to collect statistics from big log tables, use summary tables
instead of scanning the whole table. Maintaining the summaries should be
much faster than trying to do statistics 'live'. It's much faster to
regenerate new summary tables from the logs when things change
(depending on business decisions) than to have to change the running
application!
@item
If possible, one should classify reports as 'live' or 'statistical',
where data needed for statistical reports are only generated based on
summary tables that are generated from the actual data.
@item
Take advantage of the fact that columns have default values. Insert
values explicitly only when the value to be inserted differs from the
default. This reduces the parsing that @strong{MySQL} need to do and
improves the insert speed.
@item
In some cases it's convenient to pack and store data into a blob. In this
case you have to add some extra code in your appliction to pack/unpack
things in the blob, but this may save a lot of accesses at some stage.
This is practical when you have data that doesn't conform to a static
table structure.
@item
Normally you should try to keep all data non-redundant (what
is called 3rd normal form in database theory), but you should not be
afraid of duplicating things or creating summary tables if you need these
to gain more speed.
@item
Stored procedures or UDF (user-defined functions) may be a good way to
get more performance. In this case you should, however, always have a way
to do this some other (slower) way if you use some database that doesn't
support this.
@item
You can always gain something by caching queries/answers in your
application and trying to do many inserts/updates at the same time. If
your database supports lock tables (like @strong{MySQL} and Oracle),
this should help to ensure that the index cache is only flushed once
after all updates.
@item
Use @code{INSERT /*! DELAYED */} when you do not need to know when your
data is written. This speeds things up because many records can be written
with a single disk write.
@item
Use @code{INSERT /*! LOW_PRIORITY */} when you want your selects to be
more important.
@item
Use @code{SELECT /*! HIGH_PRIORITY */} to get selects that jump the
queue. That is, the select is done even if there is somebody waiting to
do a write.
@item
Use the multi-line @code{INSERT} statement to store many rows with one
SQL command (many SQL servers supports this).
@item
Use @code{LOAD DATA INFILE} to load bigger amounts of data. This is
faster than normal inserts and will be even faster when @code{myisamchk}
is integrated in @code{mysqld}.
@item
Use @code{AUTO_INCREMENT} columns to make unique values.
@item
Use @code{OPTIMIZE TABLE} once in a while to avoid fragmentation when
using dynamic table format. @xref{OPTIMIZE TABLE, , @code{OPTIMIZE TABLE}}.
@item
Use @code{HEAP} tables to get more speed when possible. @xref{Table
types}.
@item
When using a normal Web server setup, images should be stored as
files. That is, store only a file reference in the database. The main
reason for this is that a normal Web server is much better at caching
files than database contents. So it it's much easier to get a fast
system if you are using files.
@item
Use in memory tables for non-critical data that are accessed often (like
information about the last shown banner for users that don't have
cookies).
@item
Columns with identical information in different tables should be
declared identical and have identical names. Before Version 3.23 you
got slow joins otherwise.
Try to keep the names simple (use @code{name} instead of
@code{customer_name} in the customer table). To make your names portable
to other SQL servers you should keep them shorter than 18 characters.
@item
If you need REALLY high speed, you should take a look at the low-level
interfaces for data storage that the different SQL servers support! For
example, by accessing the @strong{MySQL} @code{MyISAM} directly, you could
get a speed increase of 2-5 times compared to using the SQL interface.
To be able to do this the data must be on the same server as
the application, and usually it should only be accessed by one process
(because external file locking is really slow). One could eliminate the
above problems by introducing low-level @code{MyISAM} commands in the
@strong{MySQL} server (this could be one easy way to get more
performance if needed). By carefully designing the database interface,
it should be quite easy to support this types of optimization.
@item
In many cases it's faster to access data from a database (using a live
connection) than accessing a text file, just because the database is
likely to be more compact than the text file (if you are using numerical
data), and this will involve fewer disk accesses. You will also save
code because you don't have to parse your text files to find line and
column boundaries.
@item
You can also use replication to speed things up. @xref{Replication}.
@item
Declaring a table with @code{DELAY_KEY_WRITE=1} will make the updating of
indexes faster, as these are not logged to disk until the file is closed.
The downside is that you should run @code{myisamchk} on these tables before
you start @code{mysqld} to ensure that they are okay if something killed
@code{mysqld} in the middle. As the key information can always be generated
from the data, you should not lose anything by using @code{DELAY_KEY_WRITE}.
@end itemize
@cindex benchmarks
@cindex performance, benchmarks
@node Benchmarks, Design, Tips, Performance
@section Using Your Own Benchmarks
You should definately benchmark your application and database to find
out where the bottlenecks are. By fixing it (or by replacing the
bottleneck with a 'dummy module') you can then easily identify the next
bottleneck (and so on). Even if the overall performance for your
application is sufficient, you should at least make a plan for each
bottleneck, and decide how to solve it if someday you really need the
extra performance.
For an example of portable benchmark programs, look at the @strong{MySQL}
benchmark suite. @xref{MySQL Benchmarks, , @strong{MySQL} Benchmarks}. You
can take any program from this suite and modify it for your needs. By doing this,
you can try different solutions to your problem and test which is really the
fastest solution for you.
It is very common that some problems only occur when the system is very
heavily loaded. We have had many customers who contact us when they
have a (tested) system in production and have encountered load problems. In
every one of these cases so far, it has been problems with basic design
(table scans are NOT good at high load) or OS/Library issues. Most of
this would be a @strong{LOT} easier to fix if the systems were not
already in production.
To avoid problems like this, you should put some effort into benchmarking
your whole application under the worst possible load! You can use Sasha's
recent hack for this -
@uref{http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz,
super-smack}.
As the name suggests, it can bring your system down to its knees if you ask it,
so make sure to use it only on your developement systems.
@cindex design, choices
@cindex database design
@cindex storage of data
@node Design, Design Limitations, Benchmarks, Performance
@section Design Choices
@strong{MySQL} keeps row data and index data in separate files. Many (almost
all) other databases mix row and index data in the same file. We believe that
the @strong{MySQL} choice is better for a very wide range of modern systems.
Another way to store the row data is to keep the information for each
column in a separate area (examples are SDBM and Focus). This will cause a
performance hit for every query that accesses more than one column. Because
this degenerates so quickly when more than one column is accessed,
we believe that this model is not good for general purpose databases.
The more common case is that the index and data are stored together
(like in Oracle/Sybase et al). In this case you will find the row
information at the leaf page of the index. The good thing with this
layout is that it, in many cases, depending on how well the index is
cached, saves a disk read. The bad things with this layout are:
@itemize @bullet
@item
Table scanning is much slower because you have to read through the indexes
to get at the data.
@item
You can't use only the index table to retrieve data for a query.
@item
You lose a lot of space, as you must duplicate indexes from the nodes
(as you can't store the row in the nodes).
@item
Deletes will degenerate the table over time (as indexes in nodes are
usually not updated on delete).
@item
It's harder to cache ONLY the index data.
@end itemize
@cindex design, limitations
@node Design Limitations, Portability, Design, Performance
@section MySQL Design Limitations/Tradeoffs
Because @strong{MySQL} uses extremely fast table locking (multiple readers /
single writers) the biggest remaining problem is a mix of a steady stream of
inserts and slow selects on the same table.
We believe that for a huge number of systems the extremely fast
performance in other cases make this choice a win. This case is usually
also possible to solve by having multiple copies of the table, but it
takes more effort and hardware.
We are also working on some extensions to solve this problem for some
common application niches.
@cindex portability
@cindex crash-me program
@cindex programs, crash-me
@node Portability, Internal use, Design Limitations, Performance
@section Portability
Because all SQL servers implement different parts of SQL, it takes work to
write portable SQL applications. For very simple selects/inserts it is
very easy, but the more you need the harder it gets. If you want an
application that is fast with many databases it becomes even harder!
To make a complex application portable you need to choose a number of
SQL servers that it should work with.
You can use the @strong{MySQL} crash-me program/web-page
@uref{http://www.mysql.com/information/crash-me.php} to find functions,
types, and limits you can use with a selection of database
servers. Crash-me now tests far from everything possible, but it
is still comprehensive with about 450 things tested.
For example, you shouldn't have column names longer than 18 characters
if you want to be able to use Informix or DB2.
Both the @strong{MySQL} benchmarks and crash-me programs are very
database-independent. By taking a look at how we have handled this, you
can get a feeling for what you have to do to write your application
database-independent. The benchmarks themselves can be found in the
@file{sql-bench} directory in the @strong{MySQL} source
distribution. They are written in Perl with DBI database interface
(which solves the access part of the problem).
See @uref{http://www.mysql.com/information/benchmarks.html} for the results
from this benchmark.
As you can see in these results, all databases have some weak points. That
is, they have different design compromises that lead to different
behavior.
If you strive for database independence, you need to get a good feeling
for each SQL server's bottlenecks. @strong{MySQL} is VERY fast in
retrieving and updating things, but will have a problem in mixing slow
readers/writers on the same table. Oracle, on the other hand, has a big
problem when you try to access rows that you have recently updated
(until they are flushed to disk). Transaction databases in general are
not very good at generating summary tables from log tables, as in this
case row locking is almost useless.
To get your application @emph{really} database-independent, you need to define
an easy extendable interface through which you manipulate your data. As
C++ is available on most systems, it makes sense to use a C++ classes
interface to the databases.
If you use some specific feature for some database (like the
@code{REPLACE} command in @strong{MySQL}), you should code a method for
the other SQL servers to implement the same feature (but slower). With
@strong{MySQL} you can use the @code{/*! */} syntax to add
@strong{MySQL}-specific keywords to a query. The code inside
@code{/**/} will be treated as a comment (ignored) by most other SQL
servers.
If REAL high performance is more important than exactness, as in some
Web applications, a possibility is to create an application layer that
caches all results to give you even higher performance. By letting
old results 'expire' after a while, you can keep the cache reasonably
fresh. This is quite nice in case of extremely high load, in which case
you can dynamically increase the cache and set the expire timeout higher
until things get back to normal.
In this case the table creation information should contain information
of the initial size of the cache and how often the table should normally
be refreshed.
@cindex uses, of MySQL
@cindex customers, of MySQL
@node Internal use, , Portability, Performance
@section What Have We Used MySQL For?
During @strong{MySQL} initial development, the features of @strong{MySQL} were made to fit
our largest customer. They handle data warehousing for a couple of the
biggest retailers in Sweden.
From all stores, we get weekly summaries of all bonus card transactions,
and we are expected to provide useful information for the store owners
to help them find how their advertisement campaigns are affecting their
customers.
The data is quite huge (about 7 million summary transactions per month),
and we have data for 4-10 years that we need to present to the users.
We got weekly requests from the customers that they want to get
'instant' access to new reports from this data.
We solved this by storing all information per month in compressed
'transaction' tables. We have a set of simple macros (script) that
generates summary tables grouped by different criteria (product group,
customer id, store ...) from the transaction tables. The reports are
Web pages that are dynamically generated by a small Perl script that
parses a Web page, executes the SQL statements in it, and inserts the
results. We would have used PHP or mod_perl instead but they were
not available at that time.
For graphical data we wrote a simple tool in @code{C} that can produce
GIFs based on the result of a SQL query (with some processing of the
result). This is also dynamically executed from the Perl script that
parses the @code{HTML} files.
In most cases a new report can simply be done by copying an existing
script and modifying the SQL query in it. In some cases, we will need to
add more fields to an existing summary table or generate a new one, but
this is also quite simple, as we keep all transactions tables on disk.
(Currently we have at least 50G of transactions tables and 200G of other
customer data.)
We also let our customers access the summary tables directly with ODBC
so that the advanced users can themselves experiment with the data.
We haven't had any problems handling this with quite modest Sun Ultra
SPARCstation (2x200 Mhz). We recently upgraded one of our servers to a 2
CPU 400 Mhz UltraSPARC, and we are now planning to start handling
transactions on the product level, which would mean a ten-fold increase
of data. We think we can keep up with this by just adding more disk to
our systems.
We are also experimenting with Intel-Linux to be able to get more CPU
power cheaper. Now that we have the binary portable database format (new
in Version 3.23), we will start to use this for some parts of the application.
Our initial feelings are that Linux will perform much better on
low-to-medium load and Solaris will perform better when you start to get a
high load because of extreme disk IO, but we don't yet have anything
conclusive about this. After some discussion with a Linux Kernel
developer, this might be a side effect of Linux giving so much resources
to the batch job that the interactive performance gets very low. This
makes the machine feel very slow and unresponsive while big batches are
going. Hopefully this will be better handled in future Linux Kernels.
@cindex benchmark suite
@cindex crash-me program
@node MySQL Benchmarks, Tools, Performance, Top
@chapter The MySQL Benchmark Suite
This should contain a technical description of the @strong{MySQL}
benchmark suite (and @code{crash-me}), but that description is not
written yet. Currently, you can get a good idea of the benchmark by
looking at the code and results in the @file{sql-bench} directory in any
@strong{MySQL} source distributions.
This benchmark suite is meant to be a benchmark that will tell any user
what things a given SQL implementation performs well or poorly at.
Note that this benchmark is single threaded, so it measures the minimum
time for the operations. We plan to in the future add a lot of
multi-threaded tests to the benchmark suite.
For example, (run on the same NT 4.0 machine):
@multitable @columnfractions .6 .2 .2
@strong{Reading 2000000 rows by index} @tab @strong{Seconds} @tab @strong{Seconds}
@item mysql @tab 367 @tab 249
@item mysql_odbc @tab 464
@item db2_odbc @tab 1206
@item informix_odbc @tab 121126
@item ms-sql_odbc @tab 1634
@item oracle_odbc @tab 20800
@item solid_odbc @tab 877
@item sybase_odbc @tab 17614
@end multitable
@multitable @columnfractions .6 .2 .2
@strong{Inserting (350768) rows} @tab @strong{Seconds} @tab @strong{Seconds}
@item mysql @tab 381 @tab 206
@item mysql_odbc @tab 619
@item db2_odbc @tab 3460
@item informix_odbc @tab 2692
@item ms-sql_odbc @tab 4012
@item oracle_odbc @tab 11291
@item solid_odbc @tab 1801
@item sybase_odbc @tab 4802
@end multitable
In the above test @strong{MySQL} was run with a 8M index cache.
We have gather some more benchmark results at
@uref{http://www.mysql.com/information/benchmarks.html}.
Note that Oracle is not included because they asked to be removed. All
Oracle benchmarks have to be passed by Oracle! We believe that makes
Oracle benchmarks @strong{VERY} biased because the above benchmarks are
supposed to show what a standard installation can do for a single
client.
To run the benchmark suite, you have to download a MySQL source distribution
install the perl DBI driver, the perl DBD driver for the database you want to
test and then do:
@example
cd sql-bench
perl run-all-tests --server=#
@end example
where # is one of supported servers. You can get a list of all options
and supported servers by doing @code{run-all-tests --help}.
@cindex crash-me
@code{crash-me} tries to determine what features a database supports and
what it's capabilities and limitations are by actually running
queries. For example, it determines:
@itemize @bullet
@item
What column types are supported
@item
How many indexes are supported
@item
What functions are supported
@item
How big a query can be
@item
How big a @code{VARCHAR} column can be
@end itemize
We can find the result from crash-me on a lot of different databases at
@uref{http://www.mysql.com/information/crash-me.php}.
@cindex utilities
@node Tools, Maintenance, MySQL Benchmarks, Top
@chapter MySQL Utilites
@menu
* Programs:: What do the executables do?
* safe_mysqld:: safe_mysqld, the wrapper around mysqld
* mysqld_multi:: Program for managing multiple @strong{MySQL} servers
* mysql:: The command line tool
* mysqladmin:: Administering a @strong{MySQL} server
* mysqldump:: Dumping the structure and data from @strong{MySQL} databases and tables
* mysqlhotcopy:: Copying @strong{MySQL} Databases and Tables
* mysqlimport:: Importing data from text files
* perror:: Displaying error messages
* mysqlshow:: Showing databases, tables and columns
* myisampack:: The @strong{MySQL} compressed read-only table generator
@end menu
@cindex environment variables
@cindex programs, list of
@node Programs, safe_mysqld, Tools, Tools
@section Overview of the Different MySQL Programs
All @strong{MySQL} clients that communicate with the server using the
@code{mysqlclient} library use the following environment variables:
@tindex MYSQL_UNIX_PORT environment variable
@tindex Environment variable, MYSQL_UNIX_PORT
@tindex MYSQL_TCP_PORT environment variable
@tindex Environment variable, MYSQL_TCP_PORT
@tindex MYSQL_PWD environment variable
@tindex Environment variable, MYSQL_PWD
@tindex MYSQL_DEBUG environment variable
@tindex Environment variable, MYSQL_DEBUG
@multitable @columnfractions .25 .75
@item @strong{Name} @tab @strong{Description}
@item @code{MYSQL_UNIX_PORT} @tab The default socket; used for connections to @code{localhost}
@item @code{MYSQL_TCP_PORT} @tab The default TCP/IP port
@item @code{MYSQL_PWD} @tab The default password
@item @code{MYSQL_DEBUG} @tab Debug-trace options when debugging
@item @code{TMPDIR} @tab The directory where temporary tables/files are created
@end multitable
Use of @code{MYSQL_PWD} is insecure.
@xref{Connecting}.
@tindex MYSQL_HISTFILE environment variable
@tindex Environment variable, MYSQL_HISTFILE
@tindex HOME environment variable
@tindex Environment variable, HOME
@cindex history file
@cindex command line history
@tindex .mysql_history file
The @file{mysql} client uses the file named in the @code{MYSQL_HISTFILE}
environment variable to save the command-line history. The default value for
the history file is @file{$HOME/.mysql_history}, where @code{$HOME} is the
value of the @code{HOME} environment variable. @xref{Environment variables}.
All @strong{MySQL} programs take many different options. However, every
@strong{MySQL} program provides a @code{--help} option that you can use
to get a full description of the program's different options. For example, try
@code{mysql --help}.
You can override default options for all standard client programs with an
option file. @ref{Option files}.
The list below briefly describes the @strong{MySQL} programs:
@table @code
@cindex @code{myisamchk}
@item myisamchk
Utility to describe, check, optimize, and repair @strong{MySQL} tables.
Because @code{myisamchk} has many functions, it is described in its own
chapter. @xref{Maintenance}.
@cindex @code{make_binary_distribution}
@item make_binary_distribution
Makes a binary release of a compiled @strong{MySQL}. This could be sent
by FTP to @file{/pub/mysql/Incoming} on @code{support.mysql.com} for the
convenience of other @strong{MySQL} users.
@cindex @code{msql2mysql}
@item msql2mysql
A shell script that converts @code{mSQL} programs to @strong{MySQL}. It doesn't
handle all cases, but it gives a good start when converting.
@cindex @code{mysqlaccess}
@item mysqlaccess
A script that checks the access privileges for a host, user, and database
combination.
@cindex @code{mysqladmin}
@item mysqladmin
Utility for performing administrative operations, such as creating or
dropping databases, reloading the grant tables, flushing tables to disk, and
reopening log files. @code{mysqladmin} can also be used to retrieve version,
process, and status information from the server.
@xref{mysqladmin, , @code{mysqladmin}}.
@cindex @code{mysqlbug}
@item mysqlbug
The @strong{MySQL} bug report script. This script should always be used when
filing a bug report to the @strong{MySQL} list.
@cindex @code{mysqld}
@item mysqld
The SQL daemon. This should always be running.
@cindex @code{mysqldump}
@item mysqldump
Dumps a @strong{MySQL} database into a file as SQL statements or
as tab-separated text files. Enhanced freeware originally by Igor Romanenko.
@xref{mysqldump, , @code{mysqldump}}.
@cindex @code{mysqlimport}
@item mysqlimport
Imports text files into their respective tables using @code{LOAD DATA
INFILE}. @xref{mysqlimport, , @code{mysqlimport}}.
@cindex @code{mysqlshow}
@item mysqlshow
Displays information about databases, tables, columns, and indexes.
@cindex @code{mysql_install_db}
@item mysql_install_db
Creates the @strong{MySQL} grant tables with default privileges. This is
usually executed only once, when first installing @strong{MySQL}
on a system.
@cindex @code{replace}
@item replace
A utility program that is used by @code{msql2mysql}, but that has more
general applicability as well. @code{replace} changes strings in place in
files or on the standard input. Uses a finite state machine to match longer
strings first. Can be used to swap strings. For example, this command
swaps @code{a} and @code{b} in the given files:
@example
shell> replace a b b a -- file1 file2 ...
@end example
@end table
@cindex tools, safe_mysqld
@cindex scripts
@cindex @code{safe_mysqld}
@node safe_mysqld, mysqld_multi, Programs, Tools
@section safe_mysqld, the wrapper around mysqld
@code{safe_mysqld} is the recommended way to start a @code{mysqld}
daemon on Unix. @code{safe_mysqld} adds some safety features such as
restarting the server when an error occurs and logging run-time
information to a log file.
Normally one should never edit the @code{safe_mysqld} script, but
instead put the options to @code{safe_mysqld} in the
@code{[safe_mysqld]} section in the @code{my.cnf}
file. @code{safe_mysqld} will read all options from the @code{[mysqld]},
@code{[server]} and @code{[safe_mysqld]} sections from the option files.
@xref{Option files}.
Note that all options on the command line to @code{safe_mysqld} are passed
to @code{mysqld}. If you wants to use any options in @code{safe_mysqld} that
@code{mysqld} doesn't support, you must specify these in the option file.
Most of the options to @code{safe_mysqld} are the same as the options to
@code{mysqld}. @xref{Command-line options}.
@code{safe_mysqld} supports the following options:
@table @code
@item --basedir=path
@item --core-file-size=#
Size of the core file @code{mysqld} should be able to create. Passed to @code{ulimit -c}.
@item --datadir=path
@item --defaults-extra-file=path
@item --defaults-file=path
@item --err-log=path
@item --ledir=path
Path to @code{mysqld}
@item --log=path
@item --mysqld=mysqld-version
Name of the mysqld version in the @code{ledir} directory you want to start.
@item --no-defaults
@item --open-files-limit=#
Number of files @code{mysqld} should be able to open. Passed to @code{ulimit -n}. Note that you need to start @code{safe_mysqld} as root for this to work properly!
@item --pid-file=path
@item --port=#
@item --socket=path
@item --timezone=#
Set the timezone (the @code{TZ}) variable to the value of this parameter.
@item --user=#
@end table
The @code{safe_mysqld} script is written so that it normally is able to start
a server that was installed from either a source or a binary version of
@strong{MySQL}, even if these install the server in slightly different
locations. @code{safe_mysqld} expects one of these conditions to be true:
@itemize @bullet
@item
The server and databases can be found relative to the directory from which
@code{safe_mysqld} is invoked. @code{safe_mysqld} looks under its working
directory for @file{bin} and @file{data} directories (for binary
distributions) or for @file{libexec} and @file{var} directories (for source
distributions). This condition should be met if you execute
@code{safe_mysqld} from your @strong{MySQL} installation directory (for
example, @file{/usr/local/mysql} for a binary distribution).
@item
If the server and databases cannot be found relative to the working directory,
@code{safe_mysqld} attempts to locate them by absolute pathnames. Typical
locations are @file{/usr/local/libexec} and @file{/usr/local/var}.
The actual locations are determined when the distribution was built from which
@code{safe_mysqld} comes. They should be correct if
@strong{MySQL} was installed in a standard location.
@end itemize
Because @code{safe_mysqld} will try to find the server and databases relative
to its own working directory, you can install a binary distribution of
@strong{MySQL} anywhere, as long as you start @code{safe_mysqld} from the
@strong{MySQL} installation directory:
@example
shell> cd mysql_installation_directory
shell> bin/safe_mysqld &
@end example
If @code{safe_mysqld} fails, even when invoked from the @strong{MySQL}
installation directory, you can modify it to use the path to @code{mysqld}
and the pathname options that are correct for your system. Note that if you
upgrade @strong{MySQL} in the future, your modified version of
@code{safe_mysqld} will be overwritten, so you should make a copy of your
edited version that you can reinstall.
@cindex tools, mysqld_multi
@cindex scripts
@cindex multi mysqld
@cindex @code{mysqld_multi}
@node mysqld_multi, mysql, safe_mysqld, Tools
@section mysqld_multi, program for managing multiple @strong{MySQL} servers
@code{mysqld_multi} is meant for managing several @code{mysqld}
processes running in different UNIX sockets and TCP/IP ports.
The program will search for group(s) named [mysqld#] from my.cnf (or the
given --config-file=...), where # can be any positive number starting
from 1. These groups should be the same as the usual @code{[mysqld]}
group (e.g. options to mysqld, see @strong{MySQL} manual for detailed
information about this group), but with those port, socket etc. options
that are wanted for each separate @code{mysqld} processes. The number in
the group name has another function; it can be used for starting,
stopping, or reporting some specific @code{mysqld} servers with this
program. See the usage and options below for more information.
@example
Usage: mysqld_multi [OPTIONS] @{start|stop|report@} [GNR,GNR,GNR...]
or mysqld_multi [OPTIONS] @{start|stop|report@} [GNR-GNR,GNR,GNR-GNR,...]
@end example
The GNR above means the group number. You can start, stop or report
any GNR, or several of them at the same time. (See --example) The GNRs
list can be comma separated, or a dash combined, of which the latter
means that all the GNRs between GNR1-GNR2 will be affected. Without
GNR argument all the found groups will be either started, stopped, or
reported. Note that you must not have any white spaces in the GNR
list. Anything after a white space are ignored.
@code{mysqld_multi} supports the following options:
@table @code
@cindex config-file option
@item --config-file=...
Alternative config file. NOTE: This will not affect this program's own
options (group @code{[mysqld_multi]}), but only groups
[mysqld#]. Without this option everything will be searched from the
ordinary my.cnf file.
@cindex example option
@item --example
Give an example of a config file.
@cindex help option
@item --help
Print this help and exit.
@cindex log option
@item --log=...
Log file. Full path to and the name for the log file. NOTE: If the file
exists, everything will be appended.
@cindex mysqladmin option
@item --mysqladmin=...
@code{mysqladmin} binary to be used for a server shutdown.
@cindex mysqld option
@item --mysqld=...
@code{mysqld} binary to be used. Note that you can give
@code{safe_mysqld} to this option also. The options are passed to
@code{mysqld}. Just make sure you have @code{mysqld} in your environment
variable @code{PATH} or fix @code{safe_mysqld}.
@cindex no-log option
@item --no-log
Print to stdout instead of the log file. By default the log file is
turned on.
@cindex password option
@item --password=...
Password for user for @code{mysqladmin}.
@cindex tcp-ip option
@item --tcp-ip
Connect to the @strong{MySQL} server(s) via the TCP/IP port instead of
the UNIX socket. This affects stopping and reporting. If a socket file
is missing, the server may still be running, but can be accessed only
via the TCP/IP port. By default connecting is done via the UNIX socket.
@cindex user option
@item --user=...
@strong{MySQL} user for @code{mysqladmin}.
@cindex version option
@item --version
Print the version number and exit.
@end table
Some notes about @code{mysqld_multi}:
@itemize @bullet
@item
Make sure that the @strong{MySQL} user, who is stopping the
@code{mysqld} services (e.g using the @code{mysqladmin}) have the same
password and username for all the data directories accessed (to the
'mysql' database) And make sure that the user has the 'Shutdown_priv'
privilege! If you have many data- directories and many different 'mysql'
databases with different passwords for the @strong{MySQL} 'root' user,
you may want to create a common 'multi_admin' user for each using the
same password (see below). Example how to do it:
@example
shell> mysql -u root -S /tmp/mysql.sock -proot_password -e
"GRANT SHUTDOWN ON *.* TO multi_admin@@localhost IDENTIFIED BY 'multipass'"
@xref{Privileges}.
@end example
You will have to do the above for each @code{mysqld} running in each
data directory, that you have (just change the socket, -S=...)
@item
@code{pid-file} is very important, if you are using @code{safe_mysqld}
to start @code{mysqld} (e.g. --mysqld=safe_mysqld) Every @code{mysqld}
should have it's own @code{pid-file}. The advantage using
@code{safe_mysqld} instead of @code{mysqld} directly here is, that
@code{safe_mysqld} 'guards' every @code{mysqld} process and will restart
it, if a @code{mysqld} process fails due to signal kill -9, or
similar. (Like segmentation fault, which @strong{MySQL} should never do,
of course ;) Please note that @code{safe_mysqld} script may require that
you start it from a certain place. This means that you may have to CD to
a certain directory, before you start the @code{mysqld_multi}. If
you have problems starting, please see the @code{safe_mysqld}
script. Check especially the lines:
@example
--------------------------------------------------------------------------
MY_PWD=`pwd` Check if we are starting this relative (for the binary
release) if test -d /data/mysql -a -f ./share/mysql/english/errmsg.sys
-a -x ./bin/mysqld
--------------------------------------------------------------------------
@xref{safe_mysqld}.
@end example
The above test should be successful, or you may encounter problems.
@item
Beware of the dangers starting multiple @code{mysqlds} in the same data
directory. Use separate data directories, unless you @strong{KNOW} what
you are doing!
@item
The socket file and the TCP/IP port must be different for every @code{mysqld}.
@item
The first and fifth @code{mysqld} group were intentionally left out from
the example. You may have 'gaps' in the config file. This gives you
more flexibility. The order in which the @code{mysqlds} are started or
stopped depends on the order in which they appear in the config file.
@item
When you want to refer to a certain group using GNR with this program,
just use the number in the end of the group name ( [mysqld# <== ).
@item
You may want to use option '--user' for @code{mysqld}, but in order to
do this you need to be root when you start the @code{mysqld_multi}
script. Having the option in the config file doesn't matter; you will
just get a warning, if you are not the superuser and the @code{mysqlds}
are started under @strong{YOUR} UNIX account. @strong{IMPORTANT}: Make
sure that the @code{pid-file} and the data directory are
read+write(+execute for the latter one) accessible for @strong{THAT}
UNIX user, who the specific @code{mysqld} process is started
as. @strong{DON'T} use the UNIX root account for this, unless you
@strong{KNOW} what you are doing!
@item
@strong{MOST IMPORTANT}: Make sure that you understand the meanings of
the options that are passed to the @code{mysqlds} and why @strong{WOULD
YOU WANT} to have separate @code{mysqld} processes. Starting multiple
@code{mysqlds} in one data directory @strong{WILL NOT} give you extra
performance in a threaded system!
@end itemize
@xref{Multiple servers}.
This is an example of the config file on behalf of @code{mysqld_multi}.
@example
# This file should probably be in your home dir (~/.my.cnf) or /etc/my.cnf
# Version 2.1 by Jani Tolonen
[mysqld_multi]
mysqld = /usr/local/bin/safe_mysqld
mysqladmin = /usr/local/bin/mysqladmin
user = multi_admin
password = multipass
[mysqld2]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /usr/local/mysql/var2/hostname.pid2
datadir = /usr/local/mysql/var2
language = /usr/local/share/mysql/english
user = john
[mysqld3]
socket = /tmp/mysql.sock3
port = 3308
pid-file = /usr/local/mysql/var3/hostname.pid3
datadir = /usr/local/mysql/var3
language = /usr/local/share/mysql/swedish
user = monty
[mysqld4]
socket = /tmp/mysql.sock4
port = 3309
pid-file = /usr/local/mysql/var4/hostname.pid4
datadir = /usr/local/mysql/var4
language = /usr/local/share/mysql/estonia
user = tonu
[mysqld6]
socket = /tmp/mysql.sock6
port = 3311
pid-file = /usr/local/mysql/var6/hostname.pid6
datadir = /usr/local/mysql/var6
language = /usr/local/share/mysql/japanese
user = jani
@end example
@xref{Option files}.
@cindex command line tool
@cindex tools, command line
@cindex scripts
@cindex @code{mysql}
@node mysql, mysqladmin, mysqld_multi, Tools
@section The Command-line Tool
@code{mysql} is a simple SQL shell (with GNU @code{readline} capabilities).
It supports interactive and non-interactive use. When used interactively,
query results are presented in an ASCII-table format. When used
non-interactively (for example, as a filter), the result is presented in
tab-separated format. (The output format can be changed using command-line
options.) You can run scripts simply like this:
@example
shell> mysql database < script.sql > output.tab
@end example
If you have problems due to insufficient memory in the client, use the
@code{--quick} option! This forces @code{mysql} to use
@code{mysql_use_result()} rather than @code{mysql_store_result()} to
retrieve the result set.
Using @code{mysql} is very easy. Just start it as follows:
@code{mysql database} or @code{mysql --user=user_name --password=your_password database}. Type a SQL statement, end it with @samp{;}, @samp{g}, or @samp{G}
and press RETURN/ENTER.
@cindex command line options
@cindex options, command line
@cindex startup parameters
@code{mysql} supports the following options:
@table @code
@cindex help option
@item -?, --help
Display this help and exit.
@cindex automatic rehash option
@item -A, --no-auto-rehash
No automatic rehashing. One has to use 'rehash' to get table and field
completion. This gives a quicker start of mysql.
@cindex batch option
@item -B, --batch
Print results with a tab as separator, each row on a new line. Doesn't use
history file.
@cindex character sets option
@item --character-sets-dir=...
Directory where character sets are located.
@cindex compress option.
@item -C, --compress
Use compression in server/client protocol.
@cindex debug option
@item -#, --debug[=...]
Debug log. Default is 'd:t:o,/tmp/mysql.trace'.
@cindex database option
@item -D, --database=...
Database to use. This is mainly useful in the @code{my.cnf} file.
@cindex default character set option
@item --default-character-set=...
Set the default character set.
@cindex execute option
@item -e, --execute=...
Execute command and quit. (Output like with --batch)
@cindex vertical option
@item -E, --vertical
Print the output of a query (rows) vertically. Without this option you
can also force this output by ending your statements with @code{G}.
@cindex force option
@item -f, --force
Continue even if we get a SQL error.
@cindex no-named-commands option
@item -g, --no-named-commands
Named commands are disabled. Use * form only, or use named commands
only in the beginning of a line ending with a semicolon (;). Since
Version 10.9, the client now starts with this option ENABLED by default!
With the -g option, long format commands will still work from the first
line, however.
@cindex enable-named-commands option
@item -G, --enable-named-commands
Named commands are @strong{enabled}. Long format commands are allowed as
well as shortened * commands.
@cindex ignore space option.
@item -i, --ignore-space
Ignore space after function names.
@cindex host option
@item -h, --host=...
Connect to the given host.
@cindex html option
@item -H, --html
Produce HTML output.
@cindex skip line numbers option
@item -L, --skip-line-numbers
Don't write line number for errors. Useful when one wants to compare result
files that includes error messages
@cindex no pager option
@item --no-pager
Disable pager and print to stdout. See interactive help (h) also.
@cindex no tee option
@item --no-tee
Disable outfile. See interactive help (h) also.
@cindex unbuffered option.
@item -n, --unbuffered
Flush buffer after each query.
@cindex skip column names option
@item -N, --skip-column-names
Don't write column names in results.
@cindex set variable option
@item -O, --set-variable var=option
Give a variable a value. @code{--help} lists variables.
@cindex one database option
@item -o, --one-database
Only update the default database. This is useful for skipping updates to
other database in the update log.
@cindex pager option
@item @code{--pager[=...]}
Output type. Default is your @code{ENV} variable @code{PAGER}. Valid
pagers are less, more, cat [> filename], etc. See interactive help (h)
also. This option does not work in batch mode. Pager works only in UNIX.
@cindex password option
@item -p[password], --password[=...]
Password to use when connecting to server. If a password is not given on
the command line, you will be prompted for it. Note that if you use the
short form @code{-p} you can't have a space between the option and the
password.
@item -P --port=...
TCP/IP port number to use for connection.
@cindex quick option
@item -q, --quick
Don't cache result, print it row-by-row. This may slow down the server
if the output is suspended. Doesn't use history file.
@cindex raw option
@item -r, --raw
Write column values without escape conversion. Used with @code{--batch}
@cindex silent option
@item -s, --silent
Be more silent.
@item -S --socket=...
Socket file to use for connection.
@cindex table option
@item -t --table
Output in table format. This is default in non-batch mode.
@item -T, --debug-info
Print some debug information at exit.
@cindex tee option
@item --tee=...
Append everything into outfile. See interactive help (h) also. Does not
work in batch mode.
@cindex user option
@item -u, --user=#
User for login if not current user.
@cindex safe updates option
@item -U, --safe-updates[=#], --i-am-a-dummy[=#]
Only allow @code{UPDATE} and @code{DELETE} that uses keys. See below for
more information about this option. You can reset this option if you have
it in your @code{my.cnf} file by using @code{--safe-updates=0}.
@cindex verbose option
@item -v, --verbose
More verbose output (-v -v -v gives the table output format).
@cindex version option
@item -V, --version
Output version information and exit.
@cindex wait option
@item -w, --wait
Wait and retry if connection is down instead of aborting.
@end table
You can also set the following variables with @code{-O} or
@code{--set-variable}:
@cindex timeout
@multitable @columnfractions .3 .2 .5
@item Variablename @tab Default @tab Description
@item connect_timeout @tab 0 @tab Number of seconds before timeout connection.
@item max_allowed_packet @tab 16777216 @tab Max packetlength to send/receive from to server
@item net_buffer_length @tab 16384 @tab Buffer for TCP/IP and socket communication
@item select_limit @tab 1000 @tab Automatic limit for SELECT when using --i-am-a-dummy
@item max_join_size @tab 1000000 @tab Automatic limit for rows in a join when using --i-am-a-dummy.
@end multitable
If you type 'help' on the command line, @code{mysql} will print out the
commands that it supports:
@cindex commands, list of
@example
mysql> help
MySQL commands:
help (h) Display this text.
? (h) Synonym for `help'.
clear (c) Clear command.
connect (r) Reconnect to the server. Optional arguments are db and host.
edit (e) Edit command with $EDITOR.
ego (G) Send command to mysql server, display result vertically.
exit (q) Exit mysql. Same as quit.
go (g) Send command to mysql server.
nopager (n) Disable pager, print to stdout.
notee (t) Don't write into outfile.
pager (P) Set PAGER [to_pager]. Print the query results via PAGER.
print (p) Print current command.
quit (q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute a SQL script file. Takes a file name as an argument.
status (s) Get status information from the server.
tee (T) Set outfile [to_outfile]. Append everything into given outfile.
use (u) Use another database. Takes database name as argument.
@end example
From the above, pager only works in UNIX.
@cindex status command
The @code{status} command gives you some information about the
connection and the server you are using. If you are running in the
@code{--safe-updates} mode, @code{status} will also print the values for
the @code{mysql} variables that affect your queries.
@cindex @code{safe-mode} command
A useful startup option for beginners (introduced in @strong{MySQL}
Version 3.23.11) is @code{--safe-mode} (or @code{--i-am-a-dummy} for
users that has at some time done a @code{DELETE FROM table_name} but
forgot the @code{WHERE} clause. When using this option, @code{mysql}
sends the following command to the @strong{MySQL} server when opening
the connection:
@example
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=#select_limit#,
SQL_MAX_JOIN_SIZE=#max_join_size#"
@end example
where @code{#select_limit#} and @code{#max_join_size#} are variables that
can be set from the @code{mysql} command line. @xref{SET OPTION, @code{SET}}.
The effect of the above is:
@itemize @bullet
@item
You are not allowed to do an @code{UPDATE} or @code{DELETE} statement
if you don't have a key constraint in the @code{WHERE} part. One can,
however, force an @code{UPDATE/DELETE} by using @code{LIMIT}:
@example
UPDATE table_name SET not_key_column=# WHERE not_key_column=# LIMIT 1;
@end example
@item
All big results are automatically limited to @code{#select_limit#} rows.
@item
@code{SELECT}'s that will probably need to examine more than
@code{#max_join_size} row combinations will be aborted.
@end itemize
Some useful hints about the @code{mysql} client:
Some data is much more readable when displayed vertically, instead of
the usual horizontal box type output. For example longer text, which
includes new lines, is often much easier to be read with vertical
output.
@example
mysql> select * from mails where length(txt) < 300 limit 300,1G
*************************** 1. row ***************************
msg_nro: 3068
date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
reply: monty@@no.spam.com
mail_to: "Thimble Smith" <tim@@no.spam.com>
sbj: UTF-8
txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi. I think this is a good idea. Is anyone familiar with UTF-8
Thimble> or Unicode? Otherwise I'll put this on my TODO list and see what
Thimble> happens.
Yes, please do that.
Regards,
Monty
file: inbox-jani-1
hash: 190402944
1 row in set (0.09 sec)
@end example
@itemize @bullet
@item
For logging, you can use the @code{tee} option. The @code{tee} can be
started with option @code{--tee=...}, or from the command line
interactively with command @code{tee}. All the data displayed on the
screen will also be appended into a given file. This can be very useful
for debugging purposes also. The @code{tee} can be disabled from the
command line with command @code{notee}. Executing @code{tee} again
starts logging again. Without a parameter the previous file will be
used. Note that @code{tee} will flush the results into the file after
each command, just before the command line appears again waiting for the
next command.
@item
Browsing, or searching the results in the interactive mode in UNIX less,
more, or any other similar program, is now possible with option
@code{--pager[=...]}. Without argument, @code{mysql} client will look
for environment variable PAGER and set @code{pager} to that.
@code{pager} can be started from the interactive command line with
command @code{pager} and disabled with command @code{nopager}. The
command takes an argument optionally and the @code{pager} will be set to
that. Command @code{pager} can be called without an argument, but this
requires that the option @code{--pager} was used, or the @code{pager}
will default to stdout. @code{pager} works only in UNIX, since it uses
the popen() function, which doesn't exist in Windows. In Windows, the
@code{tee} option can be used instead, although it may not be as handy
as @code{pager} can be in some situations.
@item
A few tips about @code{pager}: You can use it to write to a file:
@example
mysql> pager cat > /tmp/log.txt
@end example
and the results will only go to a file. You can also pass any options
for the programs that you want to use with the @code{pager}:
@example
mysql> pager less -n -i -S
@end example
From the above do note the option '-S'. You may find it very useful when
browsing the results; try the option with horizontal output (end
commands with 'g', or ';') and with vertical output (end commands with
'G'). Sometimes a very wide result set is hard to be read from the screen,
with option -S to less you can browse the results within the interactive
less from left to right, preventing lines longer than your screen from
being continued to the next line. This can make the result set much more
readable. You can swith the mode between on and off within the interactive
less with '-S'. See the 'h' for more help about less.
@item
Last (unless you already understood this from the above examples ;) you
can combine very complex ways to handle the results, for example the
following would send the results to two files in two different
directories, on two different hard-disks mounted on /dr1 and /dr2, yet
let the results still be seen on the screen via less:
@example
mysql> pager cat | tee /dr1/tmp/res.txt | tee /dr2/tmp/res2.txt | less -n -i -S
@end example
@item
You can also combine the two functions above; have the @code{tee}
enabled, @code{pager} set to 'less' and you will be able to browse the
results in unix 'less' and still have everything appended into a file
the same time. The difference between @code{UNIX tee} used with the
@code{pager} and the @code{mysql} client in-built @code{tee}, is that
the in-built @code{tee} works even if you don't have the @code{UNIX tee}
available. The in-built @code{tee} also logs everything that is printed
on the screen, where the @code{UNIX tee} used with @code{pager} doesn't
log quite that much. Last, but not least, the interactive @code{tee} is
more handy to switch on and off, when you want to log something into a
file, but want to be able to turn the feature off sometimes.
@end itemize
@cindex administration, server
@cindex server administration
@cindex @code{mysladmn}
@node mysqladmin, mysqldump, mysql, Tools
@section Administering a MySQL Server
A utility for performing administrative operations. The syntax is:
@example
shell> mysqladmin [OPTIONS] command [command-option] command ...
@end example
You can get a list of the options your version of @code{mysqladmin} supports
by executing @code{mysqladmin --help}.
The current @code{mysqladmin} supports the following commands:
@multitable @columnfractions .3 .7
@item create databasename @tab Create a new database.
@item drop databasename @tab Delete a database and all its tables.
@item extended-status @tab Gives an extended status message from the server.
@item flush-hosts @tab Flush all cached hosts.
@item flush-logs @tab Flush all logs.
@item flush-tables @tab Flush all tables.
@item flush-privileges @tab Reload grant tables (same as reload).
@item kill id,id,... @tab Kill mysql threads.
@item password @tab New-password. Change old password to new-password.
@item ping @tab Check if mysqld is alive.
@item processlist @tab Show list of active threads in server.
@item reload @tab Reload grant tables.
@item refresh @tab Flush all tables and close and open logfiles.
@item shutdown @tab Take server down.
@item slave-start @tab Start slave replication thread.
@item slave-stop @tab Stop slave replication thread.
@item status @tab Gives a short status message from the server.
@item variables @tab Prints variables available.
@item version @tab Get version info from server.
@end multitable
All commands can be shortened to their unique prefix. For example:
@example
shell> mysqladmin proc stat
+----+-------+-----------+----+-------------+------+-------+------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+-------------+------+-------+------+
| 6 | monty | localhost | | Processlist | 0 | | |
+----+-------+-----------+----+-------------+------+-------+------+
Uptime: 10077 Threads: 1 Questions: 9 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 2 Memory in use: 1092K Max memory used: 1116K
@end example
@cindex status command, results
The @code{mysqladmin status} command result has the following columns:
@cindex uptime
@multitable @columnfractions .3 .7
@item Uptime @tab Number of seconds the @strong{MySQL} server has been up.
@cindex threads
@item Threads @tab Number of active threads (clients).
@cindex questions
@item Questions @tab Number of questions from clients since @code{mysqld} was started.
@cindex slow queries
@item Slow queries @tab Queries that have taken more than @code{long_query_time} seconds. @xref{Slow query log}.
@cindex opens
@item Opens @tab How many tables @code{mysqld} has opened.
@cindex flush tables
@cindex tables, flush
@item Flush tables @tab Number of @code{flush ...}, @code{refresh}, and @code{reload} commands.
@cindex open tables
@item Open tables @tab Number of tables that are open now.
@cindex memory use
@item Memory in use @tab Memory allocated directly by the mysqld code (only available when @strong{MySQL} is compiled with --with-debug).
@cindex max memory used
@item Max memory used @tab Maximum memory allocated directly by the mysqld code (only available when @strong{MySQL} is compiled with --with-debug).
@end multitable
If you do @code{myslqadmin shutdown} on a socket (in other words, on a
the computer where @code{mysqld} is running), @code{mysqladmin} will
wait until the @strong{MySQL} @code{pid-file} is removed to ensure that
the @code{mysqld} server has stopped properly.
@cindex dumping, databases
@cindex databases, dumping
@cindex tables, dumping
@cindex backing up, databases
@node mysqldump, mysqlhotcopy, mysqladmin, Tools
@section Dumping the Structure and Data from MySQL Databases and Tables
@cindex @code{mysqldump}
Utility to dump a database or a collection of database for backup or for
transferring the data to another SQL server (not necessarily a MySQL
server). The dump will contain SQL statements to create the table
and/or populate the table.
If you are doing a backup on the server, you should consider using
the @code{mysqlhotcopy} instead. @xref{mysqlhotcopy}.
@example
shell> mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
@end example
If you don't give any tables or use the @code{--databases} or
@code{--all-databases}, the whole database(s) will be dumped.
You can get a list of the options your version of @code{mysqldump} supports
by executing @code{mysqldump --help}.
Note that if you run @code{mysqldump} without @code{--quick} or
@code{--opt}, @code{mysqldump} will load the whole result set into
memory before dumping the result. This will probably be a problem if
you are dumping a big database.
Note that if you are using a new copy of the @code{mysqldump} program
and you are going to do a dump that will be read into a very old @strong{MySQL}
server, you should not use the @code{--opt} or @code{-e} options.
@code{mysqldump} supports the following options:
@table @code
@item --add-locks
Add @code{LOCK TABLES} before and @code{UNLOCK TABLE} after each table dump.
(To get faster inserts into @strong{MySQL}.)
@item --add-drop-table
Add a @code{drop table} before each create statement.
@item -A, --all-databases
Dump all the databases. This will be same as @code{--databases} with all
databases selected.
@item -a, --all
Include all @strong{MySQL}-specific create options.
@item --allow-keywords
Allow creation of column names that are keywords. This works by
prefixing each column name with the table name.
@item -c, --complete-insert
Use complete insert statements (with column names).
@item -C, --compress
Compress all information between the client and the server if both support
compression.
@item -B, --databases
To dump several databases. Note the difference in usage. In this case
no tables are given. All name arguments are regarded as databasenames.
@code{USE db_name;} will be included in the output before each new database.
@item --delayed
Insert rows with the @code{INSERT DELAYED} command.
@item -e, --extended-insert
Use the new multiline @code{INSERT} syntax. (Gives more compact and
faster inserts statements.)
@item -#, --debug[=option_string]
Trace usage of the program (for debugging).
@item --help
Display a help message and exit.
@item --fields-terminated-by=...
@itemx --fields-enclosed-by=...
@itemx --fields-optionally-enclosed-by=...
@itemx --fields-escaped-by=...
@itemx --lines-terminated-by=...
These options are used with the @code{-T} option and have the same
meaning as the corresponding clauses for @code{LOAD DATA INFILE}.
@xref{LOAD DATA, , @code{LOAD DATA}}.
@item -F, --flush-logs
Flush log file in the @strong{MySQL} server before starting the dump.
@item -f, --force,
Continue even if we get a SQL error during a table dump.
@item -h, --host=..
Dump data from the @strong{MySQL} server on the named host. The default host
is @code{localhost}.
@item -l, --lock-tables.
Lock all tables before starting the dump. The tables are locked with
@code{READ LOCAL} to allow concurrent inserts in the case of @code{MyISAM}
tables.
@item -n, --no-create-db
'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will not be put in the
output. The above line will be added otherwise, if --databases or
--all-databases option was given.
@item -t, --no-create-info
Don't write table creation information (The @code{CREATE TABLE} statement.)
@item -d, --no-data
Don't write any row information for the table. This is very useful if you
just want to get a dump of the structure for a table!
@item --opt
Same as @code{--quick --add-drop-table --add-locks --extended-insert
--lock-tables}. Should give you the fastest possible dump for reading
into a @strong{MySQL} server.
@item -pyour_pass, --password[=your_pass]
The password to use when connecting to the server. If you specify
no @samp{=your_pass} part,
@code{mysqldump} you will be prompted for a password.
@item -P port_num, --port=port_num
The TCP/IP port number to use for connecting to a host. (This is used for
connections to hosts other than @code{localhost}, for which Unix sockets are
used.)
@item -q, --quick
Don't buffer query, dump directly to stdout. Uses @code{mysql_use_result()}
to do this.
@item -S /path/to/socket, --socket=/path/to/socket
The socket file to use when connecting to @code{localhost} (which is the
default host).
@item --tables
Overrides option --databases (-B).
@item -T, --tab=path-to-some-directory
Creates a @code{table_name.sql} file, that contains the SQL CREATE commands,
and a @code{table_name.txt} file, that contains the data, for each give table.
@strong{NOTE}: This only works if @code{mysqldump} is run on the same
machine as the @code{mysqld} daemon. The format of the @code{.txt} file
is made according to the @code{--fields-xxx} and @code{--lines--xxx} options.
@item -u user_name, --user=user_name
The @strong{MySQL} user name to use when connecting to the server. The
default value is your Unix login name.
@item -O var=option, --set-variable var=option
Set the value of a variable. The possible variables are listed below.
@item -v, --verbose
Verbose mode. Print out more information on what the program does.
@item -V, --version
Print version information and exit.
@item -w, --where='where-condition'
Dump only selected records. Note that QUOTES are mandatory:
@example
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"
@end example
@item -O net_buffer_length=#, where # < 16M
When creating multi-row-insert statements (as with option
@code{--extended-insert} or @code{--opt}), @code{mysqldump} will create
rows up to @code{net_buffer_length} length. If you increase this
variable, you should also ensure that the @code{max_allowed_packet}
variable in the @strong{MySQL} server is bigger than the
@code{net_buffer_length}.
@end table
The most normal use of @code{mysqldump} is probably for making a backup of
whole databases. @xref{Backup}.
@example
mysqldump --opt database > backup-file.sql
@end example
You can read this back into @strong{MySQL} with:
@example
mysql database < backup-file.sql
@end example
or
@example
mysql -e "source /patch-to-backup/backup-file.sql" database
@end example
However, it's also very useful to populate another @strong{MySQL} server with
information from a database:
@example
mysqldump --opt database | mysql ---host=remote-host -C database
@end example
It is possible to dump several databases with one command:
@example
mysqldump --databases database1 [database2 database3...] > my_databases.sql
@end example
If all the databases are wanted, one can use:
@example
mysqldump --all-databases > all_databases.sql
@end example
@cindex dumping, databases
@cindex databases, dumping
@cindex tables, dumping
@cindex backing up, databases
@node mysqlhotcopy, mysqlimport, mysqldump, Tools
@section Copying MySQL Databases and Tables
@code{mysqlhotcopy} is a perl script that uses @code{LOCK TABLES},
@code{FLUSH TABLES} and @code{cp} or @code{scp} to quickly make a backup
of a database. It's the fastest way to make a backup of the database,
but it can only be run on the same machine where the database directories
are.
@example
mysqlhotcopy db_name [/path/to/new_directory]
mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
mysqlhotcopy db_name./regex/
@end example
@code{mysqlhotcopy} supports the following options:
@table @code
@item -?, --help
Display a helpscreen and exit
@item -u, --user=#
User for database login
@item -p, --password=#
Password to use when connecting to server
@item -P, --port=#
Port to use when connecting to local server
@item -S, --socket=#
Socket to use when connecting to local server
@item --allowold
Don't abort if target already exists (rename it _old)
@item --keepold
Don't delete previous (now renamed) target when done
@item --noindices
Don't include full index files in copy to make the backup smaller and faster
The indexes can later be reconstructed with @code{myisamchk -rq.}.
@item --method=#
Method for copy (@code{cp} or @code{scp}).
@item -q, --quiet
Be silent except for errors
@item --debug
Enable debug
@item -n, --dryrun
Report actions without doing them
@item --regexp=#
Copy all databases with names matching regexp
@item --suffix=#
Suffix for names of copied databases
@item --checkpoint=#
Insert checkpoint entry into specified db.table
@item --flushlog
Flush logs once all tables are locked.
@item --tmpdir=#
Temporary directory (instead of /tmp).
@end table
You can use 'perldoc mysqlhotcopy' to get a more complete documentation for
@code{mysqlhotcopy}.
@code{mysqlhotcopy} reads the group @code{[mysqlhotcopy]} from the option
files.
@cindex importing, data
@cindex data, importing
@cindex files, text
@cindex text files, importing
@cindex @code{mysqlimport}
@node mysqlimport, perror, mysqlhotcopy, Tools
@section Importing Data from Text Files
@code{mysqlimport} provides a command-line interface to the @code{LOAD DATA
INFILE} SQL statement. Most options to @code{mysqlimport} correspond
directly to the same options to @code{LOAD DATA INFILE}.
@xref{LOAD DATA, , @code{LOAD DATA}}.
@code{mysqlimport} is invoked like this:
@example
shell> mysqlimport [options] database textfile1 [textfile2....]
@end example
For each text file named on the command line,
@code{mysqlimport} strips any extension from the filename and uses the result
to determine which table to import the file's contents into. For example,
files named @file{patient.txt}, @file{patient.text}, and @file{patient} would
all be imported into a table named @code{patient}.
@code{mysqlimport} supports the following options:
@table @code
@item -c, --columns=...
This option takes a comma-separated list of field names as an argument.
The field list is passed to LOAD DATA INFILE MySQL sql command, which
mysqlimport calls MySQL to execute. For more information, please see
@code{LOAD DATA INFILE}. @xref{LOAD DATA, , @code{LOAD DATA}}.
@item -C, --compress
Compress all information between the client and the server if both support
compression.
@item -#, --debug[=option_string]
Trace usage of the program (for debugging).
@item -d, --delete
Empty the table before importing the text file.
@item --fields-terminated-by=...
@itemx --fields-enclosed-by=...
@itemx --fields-optionally-enclosed-by=...
@itemx --fields-escaped-by=...
@itemx --lines-terminated-by=...
These options have the same meaning as the corresponding clauses for
@code{LOAD DATA INFILE}. @xref{LOAD DATA, , @code{LOAD DATA}}.
@item -f, --force
Ignore errors. For example, if a table for a text file doesn't exist,
continue processing any remaining files. Without @code{--force},
@code{mysqlimport} exits if a table doesn't exist.
@item --help
Display a help message and exit.
@item -h host_name, --host=host_name
Import data to the @strong{MySQL} server on the named host. The default host
is @code{localhost}.
@item -i, --ignore
See the description for the @code{--replace} option.
@item -l, --lock-tables
Lock @strong{ALL} tables for writing before processing any text files. This
ensures that all tables are synchronized on the server.
@item -L, --local
Read input files from the client. By default, text files are assumed to be on
the server if you connect to @code{localhost} (which is the default host).
@item -pyour_pass, --password[=your_pass]
The password to use when connecting to the server. If you specify
no @samp{=your_pass} part,
@code{mysqlimport} you will be prompted for a password.
@item -P port_num, --port=port_num
The TCP/IP port number to use for connecting to a host. (This is used for
connections to hosts other than @code{localhost}, for which Unix sockets are
used.)
@item -r, --replace
The @code{--replace} and @code{--ignore} options control handling of input
records that duplicate existing records on unique key values. If you specify
@code{--replace}, new rows replace existing rows that have the same unique key
value. If you specify @code{--ignore}, input rows that duplicate an existing
row on a unique key value are skipped. If you don't specify either option, an
error occurs when a duplicate key value is found, and the rest of the text
file is ignored.
@item -s, --silent
Silent mode. Write output only when errors occur.
@item -S /path/to/socket, --socket=/path/to/socket
The socket file to use when connecting to @code{localhost} (which is the
default host).
@item -u user_name, --user=user_name
The @strong{MySQL} user name to use when connecting to the server. The
default value is your Unix login name.
@item -v, --verbose
Verbose mode. Print out more information what the program does.
@item -V, --version
Print version information and exit.
@end table
Here is a sample run using @code{mysqlimport}:
@example
$ mysql --version
mysql Ver 9.33 Distrib 3.22.25, for pc-linux-gnu (i686)
$ uname -a
Linux xxx.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 i586 unknown
$ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
$ ed
a
100 Max Sydow
101 Count Dracula
.
w imptest.txt
32
q
$ od -c imptest.txt
0000000 1 0 0 t M a x S y d o w n 1 0
0000020 1 t C o u n t D r a c u l a n
0000040
$ mysqlimport --local test imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
$ mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id | n |
+------+---------------+
| 100 | Max Sydow |
| 101 | Count Dracula |
+------+---------------+
@end example
@cindex error messages, displaying
@cindex perror
@node perror, mysqlshow, mysqlimport, Tools
@section Converting an error code to the corresponding error message
@code{perror} can be used to print error message(s). @code{perror} can
be invoked like this:
@example
shell> perror [OPTIONS] [ERRORCODE [ERRORCODE...]]
For example:
shell> perror 64 79
Error code 64: Machine is not on the network
Error code 79: Can not access a needed shared library
@end example
@code{perror} can be used to display a description for a system error
code, or an MyISAM/ISAM table handler error code. The error messages
are mostly system dependent.
@cindex databases, displaying
@cindex displaying, database information
@cindex tables, displaying
@cindex columns, displaying
@cindex showing, database information
@node mysqlshow, myisampack, perror, Tools
@section Showing Databases, Tables, and Columns
@code{mysqlshow} can be used to quickly look at which databases exist,
their tables, and the table's columns.
With the @code{mysql} program you can get the same information with the
@code{SHOW} commands. @xref{SHOW}.
@code{mysqlshow} is invoked like this:
@example
shell> mysqlshow [OPTIONS] [database [table [column]]]
@end example
@itemize @bullet
@item
If no database is given, all matching databases are shown.
@item
If no table is given, all matching tables in the database are shown.
@item
If no column is given, all matching columns and column types in the table
are shown.
@end itemize
Note that in newer @strong{MySQL} versions, you only see those
database/tables/columns for which you have some privileges.
If the last argument contains a shell or SQL wild-card (@code{*}, @code{?},
@code{%} or @code{_}) then only what's matched by the wild card is shown.
This may cause some confusion when you try to display the columns for a
table with a @code{_} as in this case @code{mysqlshow} only shows you
the table names that match the pattern. This is easily fixed by
adding an extra @code{%} last on the command line (as a separate
argument).
@cindex compressed tables
@cindex tables, compressed
@cindex MyISAM, compressed tables
@cindex @code{myisampack}
@cindex @code{pack_isam}
@node myisampack, , mysqlshow, Tools
@section The MySQL Compressed Read-only Table Generator
@code{myisampack} is used to compress MyISAM tables, and @code{pack_isam}
is used to compress ISAM tables. Because ISAM tables are deprecated, we
will only discuss @code{myisampack} here, but everything said about
@code{myisampack} should also be true for @code{pack_isam}.
@code{myisampack} works by compressing each column in the table separately.
The information needed to decompress columns is read into memory when the
table is opened. This results in much better performance when accessing
individual records, because you only have to uncompress exactly one record, not
a much larger disk block as when using Stacker on MS-DOS.
Usually, @code{myisampack} packs the data file 40%-70%.
@strong{MySQL} uses memory mapping (@code{mmap()}) on compressed tables and
falls back to normal read/write file usage if @code{mmap()} doesn't work.
There are currently two limitations with @code{myisampack}:
@itemize @bullet
@item
After packing, the table is read-only.
@item
@code{myisampack} can also pack @code{BLOB} or @code{TEXT} columns. The
older @code{pack_isam} could not do this.
@end itemize
Fixing these limitations is on our TODO list but with low priority.
@code{myisampack} is invoked like this:
@example
shell> myisampack [options] filename ...
@end example
Each filename should be the name of an index (@file{.MYI}) file. If you
are not in the database directory, you should specify the pathname to the
file. It is permissible to omit the @file{.MYI} extension.
@code{myisampack} supports the following options:
@table @code
@item -b, --backup
Make a backup of the table as @code{tbl_name.OLD}.
@item -#, --debug=debug_options
Output debug log. The @code{debug_options} string often is
@code{'d:t:o,filename'}.
@item -f, --force
Force packing of the table even if it becomes bigger or if the temporary file
exists. @code{myisampack} creates a temporary file named @file{tbl_name.TMD}
while it compresses the table. If you kill @code{myisampack}, the @file{.TMD}
file may not be deleted. Normally, @code{myisampack} exits with an error if
it finds that @file{tbl_name.TMD} exists. With @code{--force},
@code{myisampack} packs the table anyway.
@item -?, --help
Display a help message and exit.
@item -j big_tbl_name, --join=big_tbl_name
Join all tables named on the command line into a single table
@code{big_tbl_name}. All tables that are to be combined
MUST be identical (same column names and types, same indexes, etc.).
@item -p #, --packlength=#
Specify the record length storage size, in bytes. The value should be 1, 2,
or 3. (@code{myisampack} stores all rows with length pointers of 1, 2, or 3
bytes. In most normal cases, @code{myisampack} can determine the right length
value before it begins packing the file, but it may notice during the packing
process that it could have used a shorter length. In this case,
@code{myisampack} will print a note that the next time you pack the same file,
you could use a shorter record length.)
@item -s, --silent
Silent mode. Write output only when errors occur.
@item -t, --test
Don't actually pack table, just test packing it.
@item -T dir_name, --tmp_dir=dir_name
Use the named directory as the location in which to write the temporary table.
@item -v, --verbose
Verbose mode. Write information about progress and packing result.
@item -V, --version
Display version information and exit.
@item -w, --wait
Wait and retry if table is in use. If the @code{mysqld} server was
invoked with the @code{--skip-locking} option, it is not a good idea to
invoke @code{myisampack} if the table might be updated during the
packing process.
@end table
@cindex examples, compressed tables
The sequence of commands shown below illustrates a typical table compression
session:
@example
shell> ls -l station.*
-rw-rw-r-- 1 monty my 994128 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 53248 Apr 17 19:00 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-02-02 3:06:43
Data records: 1192 Deleted blocks: 0
Datafile: Parts: 1192 Deleted data: 0
Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2
Max datafile length: 54657023 Max keyfile length: 33554431
Recordlength: 834
Record format: Fixed length
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 1024 1024 1
2 32 30 multip. text 10240 1024 1
Field Start Length Type
1 1 1
2 2 4
3 6 4
4 10 1
5 11 20
6 31 1
7 32 30
8 62 35
9 97 35
10 132 35
11 167 4
12 171 16
13 187 35
14 222 4
15 226 16
16 242 20
17 262 20
18 282 20
19 302 30
20 332 4
21 336 4
22 340 1
23 341 8
24 349 8
25 357 8
26 365 2
27 367 2
28 369 4
29 373 4
30 377 1
31 378 2
32 380 8
33 388 4
34 392 4
35 396 4
36 400 4
37 404 1
38 405 4
39 409 4
40 413 4
41 417 4
42 421 4
43 425 4
44 429 20
45 449 30
46 479 1
47 480 1
48 481 79
49 560 79
50 639 79
51 718 79
52 797 8
53 805 1
54 806 1
55 807 20
56 827 4
57 831 4
shell> myisampack station.MYI
Compressing station.MYI: (1192 records)
- Calculating statistics
normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11
pre-space: 0 end-space: 12 table-lookups: 5 zero: 7
Original trees: 57 After join: 17
- Compressing file
87.14%
shell> ls -l station.*
-rw-rw-r-- 1 monty my 127874 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 55296 Apr 17 19:04 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-04-17 19:04:26
Data records: 1192 Deleted blocks: 0
Datafile: Parts: 1192 Deleted data: 0
Datafilepointer (bytes): 3 Keyfile pointer (bytes): 1
Max datafile length: 16777215 Max keyfile length: 131071
Recordlength: 834
Record format: Compressed
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 10240 1024 1
2 32 30 multip. text 54272 1024 1
Field Start Length Type Huff tree Bits
1 1 1 constant 1 0
2 2 4 zerofill(1) 2 9
3 6 4 no zeros, zerofill(1) 2 9
4 10 1 3 9
5 11 20 table-lookup 4 0
6 31 1 3 9
7 32 30 no endspace, not_always 5 9
8 62 35 no endspace, not_always, no empty 6 9
9 97 35 no empty 7 9
10 132 35 no endspace, not_always, no empty 6 9
11 167 4 zerofill(1) 2 9
12 171 16 no endspace, not_always, no empty 5 9
13 187 35 no endspace, not_always, no empty 6 9
14 222 4 zerofill(1) 2 9
15 226 16 no endspace, not_always, no empty 5 9
16 242 20 no endspace, not_always 8 9
17 262 20 no endspace, no empty 8 9
18 282 20 no endspace, no empty 5 9
19 302 30 no endspace, no empty 6 9
20 332 4 always zero 2 9
21 336 4 always zero 2 9
22 340 1 3 9
23 341 8 table-lookup 9 0
24 349 8 table-lookup 10 0
25 357 8 always zero 2 9
26 365 2 2 9
27 367 2 no zeros, zerofill(1) 2 9
28 369 4 no zeros, zerofill(1) 2 9
29 373 4 table-lookup 11 0
30 377 1 3 9
31 378 2 no zeros, zerofill(1) 2 9
32 380 8 no zeros 2 9
33 388 4 always zero 2 9
34 392 4 table-lookup 12 0
35 396 4 no zeros, zerofill(1) 13 9
36 400 4 no zeros, zerofill(1) 2 9
37 404 1 2 9
38 405 4 no zeros 2 9
39 409 4 always zero 2 9
40 413 4 no zeros 2 9
41 417 4 always zero 2 9
42 421 4 no zeros 2 9
43 425 4 always zero 2 9
44 429 20 no empty 3 9
45 449 30 no empty 3 9
46 479 1 14 4
47 480 1 14 4
48 481 79 no endspace, no empty 15 9
49 560 79 no empty 2 9
50 639 79 no empty 2 9
51 718 79 no endspace 16 9
52 797 8 no empty 2 9
53 805 1 17 1
54 806 1 3 9
55 807 20 no empty 3 9
56 827 4 no zeros, zerofill(2) 2 9
57 831 4 no zeros, zerofill(1) 2 9
@end example
The information printed by @code{myisampack} is described below:
@table @code
@item normal
The number of columns for which no extra packing is used.
@item empty-space
The number of columns containing
values that are only spaces; these will occupy 1 bit.
@item empty-zero
The number of columns containing
values that are only binary 0's; these will occupy 1 bit.
@item empty-fill
The number of integer columns that don't occupy the full byte range of their
type; these are changed to a smaller type (for example, an @code{INTEGER}
column may be changed to @code{MEDIUMINT}).
@item pre-space
The number of decimal columns that are stored with leading spaces. In this
case, each value will contain a count for the number of leading spaces.
@item end-space
The number of columns that have a lot of trailing spaces. In this case, each
value will contain a count for the number of trailing spaces.
@item table-lookup
The column had only a small number of different values, which were
converted to an @code{ENUM} before Huffman compression.
@item zero
The number of columns for which all values are zero.
@item Original trees
The initial number of Huffman trees.
@item After join
The number of distinct Huffman trees left after joining
trees to save some header space.
@end table
After a table has been compressed, @code{myisamchk -dvv} prints additional
information about each field:
@table @code
@item Type
The field type may contain the following descriptors:
@table @code
@item constant
All rows have the same value.
@item no endspace
Don't store endspace.
@item no endspace, not_always
Don't store endspace and don't do end space compression for all values.
@item no endspace, no empty
Don't store endspace. Don't store empty values.
@item table-lookup
The column was converted to an @code{ENUM}.
@item zerofill(n)
The most significant @code{n} bytes in the value are always 0 and are not
stored.
@item no zeros
Don't store zeros.
@item always zero
0 values are stored in 1 bit.
@end table
@item Huff tree
The Huffman tree associated with the field.
@item Bits
The number of bits used in the Huffman tree.
@end table
After you have run @code{pack_isam}/@code{myisampack} you must run
@code{isamchk}/@code{myisamchk} to re-create the index. At this time you
can also sort the index blocks and create statistics needed for
the @strong{MySQL} optimizer to work more efficiently:
@example
myisamchk -rq --analyze --sort-index table_name.MYI
isamchk -rq --analyze --sort-index table_name.ISM
@end example
After you have installed the packed table into the @strong{MySQL} database
directory you should do @code{mysqladmin flush-tables} to force @code{mysqld}
to start using the new table.
@cindex installation maintenance
@cindex maintaining, tables
@cindex tables, maintaining
@cindex databases, maintaining
@cindex @code{mysiamchk}
@cindex crash, recovery
@cindex recovery, from crash
@node Maintenance, Adding functions, Tools, Top
@chapter Maintaining a MySQL Installation
@menu
* Table maintenance:: Table maintenance and crash recovery
* Maintenance regimen:: Setting up a table maintenance regimen
* Table-info:: Getting information about a table
* Crash recovery:: Using @code{myisamchk} for crash recovery
* Log file maintenance:: Log file maintenance
@end menu
@node Table maintenance, Maintenance regimen, Maintenance, Maintenance
@section Using @code{myisamchk} for Table Maintenance and Crash Recovery
Starting with @strong{MySQL} Version 3.23.13, you can check MyISAM
tables with the @code{CHECK TABLE} command. @xref{CHECK TABLE}. You can
repair tables with the @code{REPAIR TABLE} command. @xref{REPAIR TABLE}.
To check/repair MyISAM tables (@code{.MYI} and @code{.MYD}) you should
use the @code{myisamchk} utility. To check/repair ISAM tables
(@code{.ISM} and @code{.ISD}) you should use the @code{isamchk}
utility. @xref{Table types}.
In the following text we will talk about @code{myisamchk}, but everything
also applies to the old @code{isamchk}.
You can use the @code{myisamchk} utility to get information about your
database tables, check and repair them, or optimize them. The following
sections describe how to invoke @code{myisamchk} (including a
description of its options), how to set up a table maintenance schedule,
and how to use @code{myisamchk} to perform its various functions.
You can, in most cases, also use the command @code{OPTIMIZE TABLES} to
optimize and repair tables, but this is not as fast or reliable (in case
of real fatal errors) as @code{myisamchk}. On the other hand,
@code{OPTIMIZE TABLE} is easier to use and you don't have to worry about
flushing tables.
@xref{OPTIMIZE TABLE, , @code{OPTIMIZE TABLE}}.
Even that the repair in @code{myisamchk} is quite secure, it's always a
good idea to make a backup BEFORE doing a repair (or anything that could
make a lot of changes to a table)
@menu
* myisamchk syntax:: @code{myisamchk} invocation syntax
* myisamchk memory:: @code{myisamchk} memory usage
@end menu
@node myisamchk syntax, myisamchk memory, Table maintenance, Table maintenance
@subsection @code{myisamchk} Invocation Syntax
@code{myisamchk} is invoked like this:
@example
shell> myisamchk [options] tbl_name
@end example
The @code{options} specify what you want @code{myisamchk} to do. They are
described below. (You can also get a list of options by invoking
@code{myisamchk --help}.) With no options, @code{myisamchk} simply checks your
table. To get more information or to tell @code{myisamchk} to take corrective
action, specify options as described below and in the following sections.
@code{tbl_name} is the database table you want to check/repair. If you run
@code{myisamchk} somewhere other than in the database directory, you must
specify the path to the file, because @code{myisamchk} has no idea where your
database is located. Actually, @code{myisamchk} doesn't care whether or not
the files you are working on are located in a database directory; you can
copy the files that correspond to a database table into another location and
perform recovery operations on them there.
You can name several tables on the @code{myisamchk} command line if you
wish. You can also specify a name as an index file
name (with the @file{.MYI} suffix), which allows you to specify all
tables in a directory by using the pattern @file{*.MYI}.
For example, if you are in a database directory, you can check all the
tables in the directory like this:
@example
shell> myisamchk *.MYI
@end example
If you are not in the database directory, you can check all the tables there
by specifying the path to the directory:
@example
shell> myisamchk /path/to/database_dir/*.MYI
@end example
You can even check all tables in all databases by specifying a wild card
with the path to the @strong{MySQL} data directory:
@example
shell> myisamchk /path/to/datadir/*/*.MYI
@end example
The recommended way to quickly check all tables is:
@example
myisamchk --silent --fast /path/to/datadir/*/*.MYI
isamchk --silent /path/to/datadir/*/*.ISM
@end example
If you want to check all tables and repair all tables that are corrupted,
you can use the following line:
@example
myisamchk --silent --force --fast --update-state -O key_buffer=64M -O sort_buffer=64M -O read_buffer=1M -O write_buffer=1M /path/to/datadir/*/*.MYI
isamchk --silent --force -O key_buffer=64M -O sort_buffer=64M -O read_buffer=1M -O write_buffer=1M /path/to/datadir/*/*.ISM
@end example
The above assumes that you have more than 64 M free.
Note that if you get an error like:
@example
myisamchk: warning: 1 clients is using or hasn't closed the table properly
@end example
This means that you are trying to check a table that has been updated by
the another program (like the mysqld server) that hasn't yet closed
the file or that has died without closing the file properly.
If you @code{mysqld} is running, you must force a sync/close of all
tables with @code{FLUSH TABLES} and ensure that no one is using the
tables while you are running @code{myisamchk}. In @strong{MySQL} Version 3.23
the easiest way to avoid this problem is to use @code{CHECK TABLE}
instead of @code{myisamchk} to check tables.
@menu
* myisamchk general options::
* myisamchk check options::
* myisamchk repair options::
* myisamchk other options::
@end menu
@cindex options, @code{myisamchk}
@cindex @code{myisamchk}, options
@node myisamchk general options, myisamchk check options, myisamchk syntax, myisamchk syntax
@subsubsection General Options for @code{myisamchk}
@code{myisamchk} supports the following options.
@table @code
@item -# or --debug=debug_options
Output debug log. The @code{debug_options} string often is
@code{'d:t:o,filename'}.
@item -? or --help
Display a help message and exit.
@item -O var=option, --set-variable var=option
Set the value of a variable. The possible variables and their default values
for myisamchk can be examined with @code{myisamchk --help}:
@multitable @columnfractions .3 .7
@item key_buffer_size @tab 523264
@item read_buffer_size @tab 262136
@item write_buffer_size @tab 262136
@item sort_buffer_size @tab 2097144
@item sort_key_blocks @tab 16
@item decode_bits @tab 9
@end multitable
@code{sort_buffer_size} is used when the keys are reparied by sorting
keys, which is the normal case when you use @code{--recover}.
@code{key_buffer_size} is used when you are checking the table with
@code{--extended-check} or when the keys are repaired by inserting key
row by row in to the table (like when doing normal inserts). Repairing
through the key buffer is used in the following cases:
@itemize @bullet
@item
If you use @code{--safe-recover}.
@item
If you are using a @code{FULLTEXT} index.
@item
If the temporary files needed to sort the keys would be more than twice
as big as when creating the key file directly. This is often the case
when you have big @code{CHAR}, @code{VARCHAR} or @code{TEXT} keys as the
sort needs to store the whole keys during sorting. If you have lots
of temporary space and you can force @code{myisamchk} to repair by sorting
you can use the @code{--sort-recover} option.
@end itemize
Reparing through the key buffer takes much less disk space than using
sorting, but is also much slower.
If you want a faster repair, set the above variables to about 1/4 of your
available memory. You can set both variables to big values, as only one
of the above buffers will be used at a time.
@item -s or --silent
Silent mode. Write output only when errors occur. You can use @code{-s}
twice (@code{-ss}) to make @code{myisamchk} very silent.
@item -v or --verbose
Verbose mode. Print more information. This can be used with @code{-d} and
@code{-e}. Use @code{-v} multiple times (@code{-vv}, @code{-vvv}) for more
verbosity!
@item -V or --version
Print the @code{myisamchk} version and exit.
@item -w or, --wait
Instead of giving an error if the table is locked, wait until the table
is unlocked before continuing. Note that if you are running @code{mysqld}
on the table with @code{--skip-locking}, the table can only be locked
by another @code{myisamchk} command.
@end table
@cindex check options, myisamchk
@cindex tables, checking
@node myisamchk check options, myisamchk repair options, myisamchk general options, myisamchk syntax
@subsubsection Check Options for @code{myisamchk}
@table @code
@item -c or --check
Check table for errors. This is the default operation if you are not
giving @code{myisamchk} any options that override this.
@item -e or --extend-check
Check the table VERY thoroughly (which is quite slow if you have many
indexes). This option should only be used in extreme cases. Normally,
@code{myisamchk} or @code{myisamchk --medium-check} should, in most
cases, be able to find out if there are any errors in the table.
If you are using @code{--extended-check} and have much memory, you should
increase the value of @code{key_buffer_size} a lot!
@item -F or --fast
Check only tables that haven't been closed properly.
@item -C or --check-only-changed
Check only tables that have changed since the last check.
@item -f or --force
Restart @code{myisamchk} with @code{-r} (repair) on the table, if
@code{myisamchk} finds any errors in the table.
@item -i or --information
Print informational statistics about the table that is checked.
@item -m or --medium-check
Faster than extended-check, but only finds 99.99% of all errors.
Should, however, be good enough for most cases.
@item -U or --update-state
Store in the @file{.MYI} file when the table was checked and if the table crashed. This should be used to get full benefit of the
@code{--check-only-changed} option, but you shouldn't use this
option if the @code{mysqld} server is using the table and you are
running @code{mysqld} with @code{--skip-locking}.
@item -T or --read-only
Don't mark table as checked. This is useful if you use @code{myisamchk}
to check a table that is in use by some other application that doesn't
use locking (like @code{mysqld --skip-locking}).
@end table
@cindex repair options, myisamchk
@cindex files, repairing
@node myisamchk repair options, myisamchk other options, myisamchk check options, myisamchk syntax
@subsubsection Repair Options for myisamchk
The following options are used if you start @code{myisamchk} with
@code{-r} or @code{-o}:
@table @code
@item -D # or --data-file-length=#
Max length of data file (when re-creating data file when it's 'full').
@item -e or --extend-check
Try to recover every possible row from the data file.
Normally this will also find a lot of garbage rows. Don't use this option
if you are not totally desperate.
@item -f or --force
Overwrite old temporary files (@code{table_name.TMD}) instead of aborting.
@item -k # or keys-used=#
If you are using ISAM, tells the ISAM table handler to update only the
first @code{#} indexes. If you are using @code{MyISAM}, tells which keys
to use, where each binary bit stands for one key (first key is bit 0).
This can be used to get faster inserts! Deactivated indexes can be
reactivated by using @code{myisamchk -r}. keys.
@item -l or --no-symlinks
Do not follow symbolic links. Normally @code{myisamchk} repairs the
table a symlink points at.
@item -r or --recover
Can fix almost anything except unique keys that aren't unique
(which is an extremely unlikely error with ISAM/MyISAM tables).
If you want to recover a table, this is the option to try first. Only if
myisamchk reports that the table can't be recovered by @code{-r}, you
should then try @code{-o}. (Note that in the unlikely case that @code{-r}
fails, the data file is still intact.)
If you have lots of memory, you should increase the size of
@code{sort_buffer_size}!
@item -o or --safe-recover
Uses an old recovery method (reads through all rows in order and updates
all index trees based on the found rows); this is a magnitude slower
than @code{-r}, but can handle a couple of very unlikely cases that
@code{-r} cannot handle. This recovery method also uses much less disk
space than @code{-r}. Normally one should always first repair with
@code{-r}, and only if this fails use @code{-o}.
If you have lots of memory, you should increase the size of
@code{key_buffer_size}!
@item -n or --sort-recover
Force @code{myisamchk} to use sorting to resolve the keys even if the
temporary files should be very big. This will not have any effect if you have
fulltext keys in the table.
@item --character-sets-dir=...
Directory where character sets are stored.
@item --set-character-set=name
Change the character set used by the index
@item .t or --tmpdir=path
Path for storing temporary files. If this is not set, @code{myisamchk} will
use the environment variable @code{TMPDIR} for this.
@item -q or --quick
Faster repair by not modifying the data file. One can give a second
@code{-q} to force @code{myisamchk} to modify the original datafile in case
of duplicate keys
@item -u or --unpack
Unpack file packed with myisampack.
@end table
@node myisamchk other options, , myisamchk repair options, myisamchk syntax
@subsubsection Other Options for @code{myisamchk}
Other actions that @code{myisamchk} can do, besides repair and check tables:
@table @code
@item -a or --analyze
Analyze the distribution of keys. This improves join performance by
enabling the join optimizer to better choose in which order it should
join the tables and which keys it should use:
@code{myisamchk --describe --verbose table_name'} or using @code{SHOW KEYS} in
@strong{MySQL}.
@item -d or --description
Prints some information about table.
@item -A or --set-auto-increment[=value]
Force auto_increment to start at this or higher value. If no value is
given, then sets the next auto_increment value to the highest used value
for the auto key + 1.
@item -S or --sort-index
Sort the index tree blocks in high-low order.
This will optimize seeks and will make table scanning by key faster.
@item -R or --sort-records=#
Sorts records according to an index. This makes your data much more localized
and may speed up ranged @code{SELECT} and @code{ORDER BY} operations on
this index. (It may be VERY slow to do a sort the first time!)
To find out a table's index numbers, use @code{SHOW INDEX}, which shows a
table's indexes in the same order that @code{myisamchk} sees them. Indexes are
numbered beginning with 1.
@end table
@cindex memory usage, myisamchk
@node myisamchk memory, , myisamchk syntax, Table maintenance
@subsection @code{myisamchk} Memory Usage
Memory allocation is important when you run @code{myisamchk}.
@code{myisamchk} uses no more memory than you specify with the @code{-O}
options. If you are going to use @code{myisamchk} on very large files,
you should first decide how much memory you want it to use. The default
is to use only about 3M to fix things. By using larger values, you can
get @code{myisamchk} to operate faster. For example, if you have more
than 32M RAM, you could use options such as these (in addition to any
other options you might specify):
@example
shell> myisamchk -O sort=16M -O key=16M -O read=1M -O write=1M ...
@end example
Using @code{-O sort=16M} should probably be enough for most cases.
Be aware that @code{myisamchk} uses temporary files in @code{TMPDIR}. If
@code{TMPDIR} points to a memory file system, you may easily get out of
memory errors. If this happens, set @code{TMPDIR} to point at some directory
with more space and restart @code{myisamchk}.
When repairing, @code{myisamchk} will also nead a lot of disk space:
@itemize @bullet
@item
Double the size of the record file (the original one and a copy). This
space is not needed if one does a repair with @code{--quick}, as in this
case only the index file will be re-created. This space is needed on the
same disk as the original record file!
@item
Space for the new index file that replaces the old one. The old
index file is truncated at start, so one usually ignore this space.
This space is needed on the same disk as the original index file!
@item
When using @code{--recover} or @code{--sort-recover}
(but not when using @code{--safe-recover}, you will need space for a
sort buffer for:
@code{(largest_key + row_pointer_length)*number_of_rows * 2}.
You can check the length of the keys and the row_pointer_length with
@code{myisamchk -dv table}.
This space is allocated on the temporary disk (specified by @code{TMPDIR} or
@code{--tmpdir=#}).
@end itemize
If you have a problem with disk space during repair, you can try to use
@code{--safe-recover} instead of @code{--recover}.
@cindex maintaining, tables
@cindex tables, maintenance regimen
@node Maintenance regimen, Table-info, Table maintenance, Maintenance
@section Setting Up a Table Maintenance Regimen
Starting with @strong{MySQL} Version 3.23.13, you can check MyISAM
tables with the @code{CHECK TABLE} command. @xref{CHECK TABLE}. You can
repair tables with the @code{REPAIR TABLE} command. @xref{REPAIR TABLE}.
It is a good idea to perform table checks on a regular basis rather than
waiting for problems to occur. For maintenance purposes, you can use
@code{myisamchk -s} to check tables. The @code{-s} option (short for
@code{--silent}) causes @code{myisamchk} to run in silent mode, printing
messages only when errors occur.
@tindex .pid (process ID) file
It's also a good idea to check tables when the server starts up.
For example, whenever the machine has done a reboot in the middle of an
update, you usually need to check all the tables that could have been
affected. (This is an ``expected crashed table''.) You could add a test to
@code{safe_mysqld} that runs @code{myisamchk} to check all tables that have
been modified during the last 24 hours if there is an old @file{.pid}
(process ID) file left after a reboot. (The @file{.pid} file is created by
@code{mysqld} when it starts up and removed when it terminates normally. The
presence of a @file{.pid} file at system startup time indicates that
@code{mysqld} terminated abnormally.)
An even better test would be to check any table whose last-modified time
is more recent than that of the @file{.pid} file.
You should also check your tables regularly during normal system
operation. At MySQL AB, we run a @code{cron} job to check all our important
tables once a week, using a line like this in a @file{crontab} file:
@example
35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI
@end example
This prints out information about crashed tables so we can examine and repair
them when needed.
As we haven't had any unexpectedly crashed tables (tables that become
corrupted for reasons other than hardware trouble)
for a couple of years now (this is really true), once a week is
more than enough for us.
We recommend that to start with, you execute @code{myisamchk -s} each
night on all tables that have been updated during the last 24 hours,
until you come to trust @strong{MySQL} as much as we do.
@cindex tables, defragment
Normally you don't need to maintain MySQL tables that much. If you are
changing tables with dynamic size rows (tables with @code{VARCHAR},
@code{BLOB} or @code{TEXT} columns) or have tables with many deleted rows
you may want to from time to time (once a month?) defragment/reclaim space
from the tables.
You can do this by using @code{OPTIMIZE TABLE} on the tables in question or
if you can take the @code{mysqld} server down for a while do:
@example
isamchk -r --silent --sort-index -O sort_buffer_size=16M */*.ISM
myisamchk -r --silent --sort-index -O sort_buffer_size=16M */*.MYI
@end example
@cindex tables, information
@node Table-info, Crash recovery, Maintenance regimen, Maintenance
@section Getting Information About a Table
To get a description of a table or statistics about it, use the commands shown
below. We explain some of the information in more detail later:
@table @code
@item myisamchk -d tbl_name
Runs @code{myisamchk} in ``describe mode'' to produce a description of
your table. If you start the @strong{MySQL} server using the
@code{--skip-locking} option, @code{myisamchk} may report an error for a
table that is updated while it runs. However, because @code{myisamchk}
doesn't change the table in describe mode, there isn't any risk of
destroying data.
@item myisamchk -d -v tbl_name
To produce more information about what @code{myisamchk} is doing, add @code{-v}
to tell it to run in verbose mode.
@item myisamchk -eis tbl_name
Shows only the most important information from a table. It is slow because it
must read the whole table.
@item myisamchk -eiv tbl_name
This is like @code{-eis}, but tells you what is being done.
@end table
@cindex examples, @code{myisamchk} output
@cindex @code{myisamchk}, example output
Example of @code{myisamchk -d} output:
@example
MyISAM file: company.MYI
Record format: Fixed length
Data records: 1403698 Deleted blocks: 0
Recordlength: 226
table description:
Key Start Len Index Type
1 2 8 unique double
2 15 10 multip. text packed stripped
3 219 8 multip. double
4 63 10 multip. text packed stripped
5 167 2 multip. unsigned short
6 177 4 multip. unsigned long
7 155 4 multip. text
8 138 4 multip. unsigned long
9 177 4 multip. unsigned long
193 1 text
@end example
Example of @code{myisamchk -d -v} output:
@example
MyISAM file: company
Record format: Fixed length
File-version: 1
Creation time: 1999-10-30 12:12:51