MySQL数据库

开发平台：

Visual C++

manual.texi：源码内容

@item
A character class @samp{[...]} matches any character within the brackets.
For example, @samp{[abc]} matches @samp{a}, @samp{b}, or @samp{c}. To name a
range of characters, use a dash. @samp{[a-z]} matches any lowercase letter,
whereas @samp{[0-9]} matches any digit.
@item
@samp{*} matches zero or more instances of the thing preceding it. For
example, @samp{x*} matches any number of @samp{x} characters,
@samp{[0-9]*} matches any number of digits, and @samp{.*} matches any
number of anything.
@item
Regular expressions are case sensitive, but you can use a character class to
match both lettercases if you wish. For example, @samp{[aA]} matches
lowercase or uppercase @samp{a} and @samp{[a-zA-Z]} matches any letter in
either case.
@item
The pattern matches if it occurs anywhere in the value being tested.
(SQL patterns match only if they match the entire value.)
@item
To anchor a pattern so that it must match the beginning or end of the value
being tested, use @samp{^} at the beginning or @samp{$} at the end of the
pattern.
@end itemize
To demonstrate how extended regular expressions work, the @code{LIKE} queries
shown above are rewritten below to use @code{REGEXP}.
To find names beginning with @samp{b}, use @samp{^} to match the beginning of
the name:
@example
mysql> SELECT * FROM pet WHERE name REGEXP "^b";
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
@end example
Prior to MySQL 3.23.4, @code{REGEXP} is case sensitive, and the previous
query will return no rows. To match either lowercase or uppercase @samp{b},
use this query instead:
@example
mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";
@end example
From MySQL 3.23.4 on, to force a @code{REGEXP} comparison to be case
sensitive, use the @code{BINARY} keyword to make one of the strings a
binary string. This query will match only lowercase @samp{b} at the
beginning of a name:
@example
mysql> SELECT * FROM pet WHERE name REGEXP BINARY "^b";
@end example
To find names ending with @samp{fy}, use @samp{$} to match the end of the
name:
@example
mysql> SELECT * FROM pet WHERE name REGEXP "fy$";
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
@end example
To find names containing a lowercase or uppercase @samp{w}, use this query:
@example
mysql> SELECT * FROM pet WHERE name REGEXP "w";
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
@end example
Because a regular expression pattern matches if it occurs anywhere in the
value, it is not necessary in the previous query to put a wild card on either
side of the pattern to get it to match the entire value like it would be if
you used a SQL pattern.
To find names containing exactly five characters, use @samp{^} and @samp{$}
to match the beginning and end of the name, and five instances of @samp{.}
in between:
@example
mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
@end example
You could also write the previous query using the @samp{@{n@}}
``repeat-@code{n}-times'' operator:
@example
mysql> SELECT * FROM pet WHERE name REGEXP "^.@{5@}$";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
@end example
@cindex rows, counting
@cindex tables, counting rows
@cindex counting, table rows
@node Counting rows, , Pattern matching, Retrieving data
@subsubsection Counting Rows
Databases are often used to answer the question, ``How often does a certain
type of data occur in a table?'' For example, you might want to know how
many pets you have, or how many pets each owner has, or you might want to
perform various kinds of censuses on your animals.
Counting the total number of animals you have is the same question as ``How
many rows are in the @code{pet} table?'' because there is one record per pet.
The @code{COUNT()} function counts the number of non-@code{NULL} results, so
the query to count your animals looks like this:
@example
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
@end example
Earlier, you retrieved the names of the people who owned pets. You can
use @code{COUNT()} if you want to find out how many pets each owner has:
@example
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+
@end example
Note the use of @code{GROUP BY} to group together all records for each
@code{owner}. Without it, all you get is an error message:
@example
mysql> SELECT owner, COUNT(owner) FROM pet;
ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause
@end example
@code{COUNT()} and @code{GROUP BY} are useful for characterizing your
data in various ways. The following examples show different ways to
perform animal census operations.
Number of animals per species:
@example
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+
@end example
Number of animals per sex:
@example
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+
@end example
(In this output, @code{NULL} indicates sex unknown.)
Number of animals per combination of species and sex:
@example
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
@end example
You need not retrieve an entire table when you use @code{COUNT()}. For
example, the previous query, when performed just on dogs and cats, looks like
this:
@example
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = "dog" OR species = "cat"
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
@end example
Or, if you wanted the number of animals per sex only for known-sex animals:
@example
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
@end example
@cindex tables, multiple
@node Multiple tables, , Retrieving data, Database use
@subsection Using More Than one Table
The @code{pet} table keeps track of which pets you have. If you want to
record other information about them, such as events in their lives like
visits to the vet or when litters are born, you need another table. What
should this table look like? It needs:
@itemize @bullet
@item
To contain the pet name so you know which animal each event pertains
to.
@item
A date so you know when the event occurred.
@item
A field to describe the event.
@item
An event type field, if you want to be able to categorize events.
@end itemize
Given these considerations, the @code{CREATE TABLE} statement for the
@code{event} table might look like this:
@example
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
@end example
As with the @code{pet} table, it's easiest to load the initial records
by creating a tab-delimited text file containing the information:
@multitable @columnfractions .15 .15 .15 .55
@item Fluffy @tab 1995-05-15 @tab litter @tab 4 kittens, 3 female, 1 male
@item Buffy @tab 1993-06-23 @tab litter @tab 5 puppies, 2 female, 3 male
@item Buffy @tab 1994-06-19 @tab litter @tab 3 puppies, 3 female
@item Chirpy @tab 1999-03-21 @tab vet @tab needed beak straightened
@item Slim @tab 1997-08-03 @tab vet @tab broken rib
@item Bowser @tab 1991-10-12 @tab kennel
@item Fang @tab 1991-10-12 @tab kennel
@item Fang @tab 1998-08-28 @tab birthday @tab Gave him a new chew toy
@item Claws @tab 1998-03-17 @tab birthday @tab Gave him a new flea collar
@item Whistler @tab 1998-12-09 @tab birthday @tab First birthday
@end multitable
Load the records like this:
@example
mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event;
@end example
Based on what you've learned from the queries you've run on the @code{pet}
table, you should be able to perform retrievals on the records in the
@code{event} table; the principles are the same. But when is the
@code{event} table by itself insufficient to answer questions you might ask?
Suppose you want to find out the ages of each pet when they had their
litters. The @code{event} table indicates when this occurred, but to
calculate the age of the mother, you need her birth date. Because that is
stored in the @code{pet} table, you need both tables for the query:
@example
mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark
-> FROM pet, event
-> WHERE pet.name = event.name AND type = "litter";
+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2.27 | 4 kittens, 3 female, 1 male |
| Buffy | 4.12 | 5 puppies, 2 female, 3 male |
| Buffy | 5.10 | 3 puppies, 3 female |
+--------+------+-----------------------------+
@end example
There are several things to note about this query:
@itemize @bullet
@item
The @code{FROM} clause lists two tables because the query needs to pull
information from both of them.
@item
When combining (joining) information from multiple tables, you need to
specify how records in one table can be matched to records in the other.
This is easy because they both have a @code{name} column. The query uses
@code{WHERE} clause to match up records in the two tables based on the
@code{name} values.
@item
Because the @code{name} column occurs in both tables, you must be specific
about which table you mean when referring to the column. This is done
by prepending the table name to the column name.
@end itemize
You need not have two different tables to perform a join. Sometimes it is
useful to join a table to itself, if you want to compare records in a table
to other records in that same table. For example, to find breeding pairs
among your pets, you can join the @code{pet} table with itself to pair up
males and females of like species:
@example
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+
@end example
In this query, we specify aliases for the table name in order
to refer to the columns and keep straight which instance of the table
each column reference is associated with.
@cindex databases, information about
@cindex tables, information about
@findex DESCRIBE
@node Getting information, Batch mode, Database use, Tutorial
@section Getting Information About Databases and Tables
What if you forget the name of a database or table, or what the structure of
a given table is (for example, what its columns are called)? @strong{MySQL}
addresses this problem through several statements that provide information
about the databases and tables it supports.
You have already seen @code{SHOW DATABASES}, which lists the databases
managed by the server. To find out which database is currently selected,
use the @code{DATABASE()} function:
@example
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie |
+------------+
@end example
If you haven't selected any database yet, the result is blank.
To find out what tables the current database contains (for example, when
you're not sure about the name of a table), use this command:
@example
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| event |
| pet |
+---------------------+
@end example
If you want to find out about the structure of a table, the @code{DESCRIBE}
command is useful; it displays information about each of a table's columns:
@example
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
@end example
@code{Field} indicates the column name, @code{Type} is the data type for
the column, @code{Null} indicates whether or not the column can contain
@code{NULL} values, @code{Key} indicates whether or not the column is
indexed, and @code{Default} specifies the column's default value.
If you have indexes on a table,
@code{SHOW INDEX FROM tbl_name} produces information about them.
@cindex modes, batch
@cindex batch mode
@cindex running, batch mode
@cindex script files
@cindex files, script
@node Batch mode, Twin, Getting information, Tutorial
@section Using @code{mysql} in Batch Mode
In the previous sections, you used @code{mysql} interactively to enter
queries and view the results. You can also run @code{mysql} in batch
mode. To do this, put the commands you want to run in a file, then
tell @code{mysql} to read its input from the file:
@example
shell> mysql < batch-file
@end example
If you need to specify connection parameters on the command line, the
command might look like this:
@example
shell> mysql -h host -u user -p < batch-file
Enter password: ********
@end example
When you use @code{mysql} this way, you are creating a script file, then
executing the script.
Why use a script? Here are a few reasons:
@itemize @bullet
@item
If you run a query repeatedly (say, every day or every week), making it a
script allows you to avoid retyping it each time you execute it.
@item
You can generate new queries from existing ones that are similar by copying
and editing script files.
@item
Batch mode can also be useful while you're developing a query, particularly
for multiple-line commands or multiple-statement sequences of commands. If
you make a mistake, you don't have to retype everything. Just edit your
script to correct the error, then tell @code{mysql} to execute it again.
@item
If you have a query that produces a lot of output, you can run the output
through a pager rather than watching it scroll off the top of your screen:
@example
shell> mysql < batch-file | more
@end example
@item
You can catch the output in a file for further processing:
@example
shell> mysql < batch-file > mysql.out
@end example
@item
You can distribute your script to other people so they can run the commands,
too.
@item
Some situations do not allow for interactive use, for example, when you run
a query from a @code{cron} job. In this case, you must use batch mode.
@end itemize
The default output format is different (more concise) when you run
@code{mysql} in batch mode than when you use it interactively. For
example, the output of @code{SELECT DISTINCT species FROM pet} looks like
this when run interactively:
@example
+---------+
| species |
+---------+
| bird |
| cat |
| dog |
| hamster |
| snake |
+---------+
@end example
But like this when run in batch mode:
@example
species
bird
cat
dog
hamster
snake
@end example
If you want to get the interactive output format in batch mode, use
@code{mysql -t}. To echo to the output the commands that are executed, use
@code{mysql -vvv}.
@cindex Twin Studies, queries
@cindex queries, Twin Studeis project
@node Twin, , Batch mode, Tutorial
@section Queries from Twin Project
At Analytikerna and Lentus, we have been doing the systems and field work
for a big research project. This project is a collaboration between the
Institute of Environmental Medicine at Karolinska Institutet Stockholm
and the Section on Clinical Research in Aging and Psychology at the
University of Southern California.
The project involves a screening part where all twins in Sweden older
than 65 years are interviewed by telephone. Twins who meet certain
criteria are passed on to the next stage. In this latter stage, twins who
want to participate are visited by a doctor/nurse team. Some of the
examinations include physical and neuropsychological examination,
laboratory testing, neuroimaging, psychological status assessment, and family
history collection. In addition, data are collected on medical and
environmental risk factors.
More information about Twin studies can be found at:
@example
@url{http://www.imm.ki.se/TWIN/TWINUKW.HTM}
@end example
The latter part of the project is administered with a Web interface
written using Perl and @strong{MySQL}.
Each night all data from the interviews are moved into a @strong{MySQL}
database.
@menu
* Twin pool:: Find all non-distributed twins
* Twin event:: Show a table on twin pair status
@end menu
@node Twin pool, Twin event, Twin, Twin
@subsection Find all Non-distributed Twins
The following query is used to determine who goes into the second part of the
project:
@example
select
concat(p1.id, p1.tvab) + 0 as tvid,
concat(p1.christian_name, " ", p1.surname) as Name,
p1.postal_code as Code,
p1.city as City,
pg.abrev as Area,
if(td.participation = "Aborted", "A", " ") as A,
p1.dead as dead1,
l.event as event1,
td.suspect as tsuspect1,
id.suspect as isuspect1,
td.severe as tsevere1,
id.severe as isevere1,
p2.dead as dead2,
l2.event as event2,
h2.nurse as nurse2,
h2.doctor as doctor2,
td2.suspect as tsuspect2,
id2.suspect as isuspect2,
td2.severe as tsevere2,
id2.severe as isevere2,
l.finish_date
from
twin_project as tp
/* For Twin 1 */
left join twin_data as td on tp.id = td.id and tp.tvab = td.tvab
left join informant_data as id on tp.id = id.id and tp.tvab = id.tvab
left join harmony as h on tp.id = h.id and tp.tvab = h.tvab
left join lentus as l on tp.id = l.id and tp.tvab = l.tvab
/* For Twin 2 */
left join twin_data as td2 on p2.id = td2.id and p2.tvab = td2.tvab
left join informant_data as id2 on p2.id = id2.id and p2.tvab = id2.tvab
left join harmony as h2 on p2.id = h2.id and p2.tvab = h2.tvab
left join lentus as l2 on p2.id = l2.id and p2.tvab = l2.tvab,
person_data as p1,
person_data as p2,
postal_groups as pg
where
/* p1 gets main twin and p2 gets his/her twin. */
/* ptvab is a field inverted from tvab */
p1.id = tp.id and p1.tvab = tp.tvab and
p2.id = p1.id and p2.ptvab = p1.tvab and
/* Just the sceening survey */
tp.survey_no = 5 and
/* Skip if partner died before 65 but allow emigration (dead=9) */
(p2.dead = 0 or p2.dead = 9 or
(p2.dead = 1 and
(p2.death_date = 0 or
(((to_days(p2.death_date) - to_days(p2.birthday)) / 365)
>= 65))))
and
(
/* Twin is suspect */
(td.future_contact = 'Yes' and td.suspect = 2) or
/* Twin is suspect - Informant is Blessed */
(td.future_contact = 'Yes' and td.suspect = 1 and id.suspect = 1) or
/* No twin - Informant is Blessed */
(ISNULL(td.suspect) and id.suspect = 1 and id.future_contact = 'Yes') or
/* Twin broken off - Informant is Blessed */
(td.participation = 'Aborted'
and id.suspect = 1 and id.future_contact = 'Yes') or
/* Twin broken off - No inform - Have partner */
(td.participation = 'Aborted' and ISNULL(id.suspect) and p2.dead = 0))
and
l.event = 'Finished'
/* Get at area code */
and substring(p1.postal_code, 1, 2) = pg.code
/* Not already distributed */
and (h.nurse is NULL or h.nurse=00 or h.doctor=00)
/* Has not refused or been aborted */
and not (h.status = 'Refused' or h.status = 'Aborted'
or h.status = 'Died' or h.status = 'Other')
order by
tvid;
@end example
Some explanations:
@table @asis
@item @code{concat(p1.id, p1.tvab) + 0 as tvid}
We want to sort on the concatenated @code{id} and @code{tvab} in
numerical order. Adding @code{0} to the result causes @strong{MySQL} to
treat the result as a number.
@item column @code{id}
This identifies a pair of twins. It is a key in all tables.
@item column @code{tvab}
This identifies a twin in a pair. It has a value of @code{1} or @code{2}.
@item column @code{ptvab}
This is an inverse of @code{tvab}. When @code{tvab} is @code{1} this is
@code{2}, and vice versa. It exists to save typing and to make it easier for
@strong{MySQL} to optimize the query.
@end table
This query demonstrates, among other things, how to do lookups on a
table from the same table with a join (@code{p1} and @code{p2}). In the example, this
is used to check whether a twin's partner died before the age of 65. If so,
the row is not returned.
All of the above exist in all tables with twin-related information. We
have a key on both @code{id,tvab} (all tables), and @code{id,ptvab}
(@code{person_data}) to make queries faster.
On our production machine (A 200MHz UltraSPARC), this query returns
about 150-200 rows and takes less than one second.
The current number of records in the tables used above:
@multitable @columnfractions .3 .5
@item @strong{Table} @tab @strong{Rows}
@item @code{person_data} @tab 71074
@item @code{lentus} @tab 5291
@item @code{twin_project} @tab 5286
@item @code{twin_data} @tab 2012
@item @code{informant_data} @tab 663
@item @code{harmony} @tab 381
@item @code{postal_groups} @tab 100
@end multitable
@node Twin event, , Twin pool, Twin
@subsection Show a Table on Twin Pair Status
Each interview ends with a status code called @code{event}. The query
shown below is used to display a table over all twin pairs combined by
event. This indicates in how many pairs both twins are finished, in how many
pairs one twin is finished and the other refused, and so on.
@example
select
t1.event,
t2.event,
count(*)
from
lentus as t1,
lentus as t2,
twin_project as tp
where
/* We are looking at one pair at a time */
t1.id = tp.id
and t1.tvab=tp.tvab
and t1.id = t2.id
/* Just the sceening survey */
and tp.survey_no = 5
/* This makes each pair only appear once */
and t1.tvab='1' and t2.tvab='2'
group by
t1.event, t2.event;
@end example
@cindex functions, server
@cindex server functions
@node Server, Replication, Tutorial, Top
@chapter MySQL Server Functions
@menu
* Languages:: What languages are supported by @strong{MySQL}?
* Table size:: How big @strong{MySQL} tables can be
@end menu
@cindex error messages, languages
@cindex messages, languages
@cindex files, error messages
@cindex language support
@node Languages, Table size, Server, Server
@section What Languages Are Supported by MySQL?
@code{mysqld} can issue error messages in the following languages:
Czech, Danish, Dutch, English (the default), Estonian, French, German, Greek,
Hungarian, Italian, Japanese, Korean, Norwegian, Norwegian-ny, Polish,
Portuguese, Romanian, Russian, Slovak, Spanish, and Swedish.
To start @code{mysqld} with a particular language, use either the
@code{--language=lang} or @code{-L lang} options. For example:
@example
shell> mysqld --language=swedish
@end example
or:
@example
shell> mysqld --language=/usr/local/share/swedish
@end example
Note that all language names are specified in lowercase.
The language files are located (by default) in
@file{@var{mysql_base_dir}/share/@var{LANGUAGE}/}.
To update the error message file, you should edit the @file{errmsg.txt} file
and execute the following command to generate the @file{errmsg.sys} file:
@example
shell> comp_err errmsg.txt errmsg.sys
@end example
If you upgrade to a newer version of @strong{MySQL}, remember to repeat
your changes with the new @file{errmsg.txt} file.
@menu
* Character sets:: The character set used for data and sorting
* Adding character set:: Adding a new character set
* Character arrays:: The character definition arrays
* String collating:: String collating support
* Multi-byte characters:: Multi-byte character support
@end menu
@cindex character sets
@cindex data, character sets
@cindex sorting, character sets
@node Character sets, Adding character set, Languages, Languages
@subsection The Character Set Used for Data and Sorting
By default, @strong{MySQL} uses the ISO-8859-1 (Latin1) character
set. This is the character set used in the USA and western Europe.
All standard @strong{MySQL} binaries are compiled with
@code{--with-extra-charsets=complex}. This will add code to all
standard programs to be able to handle @code{latin1} and all multi-byte
character sets within the binary. Other character sets will be
loaded from a character-set definition file when needed.
The character set determines what characters are allowed in names and how
things are sorted by the @code{ORDER BY} and @code{GROUP BY} clauses of
the @code{SELECT} statement.
You can change the character set with the
@code{--default-character-set} option when you start the server.
The character sets available depend on the @code{--with-charset=charset}
option to @code{configure}, and the character set configuration files
listed in @file{SHAREDIR/charsets/Index}.
@xref{Quick install}.
If you change the character set when running MySQL (which may also
change the sort order), you must run myisamchk -r -q on all
tables. Otherwise your indexes may not be ordered correctly.
When a client connects to a @strong{MySQL} server, the server sends the
default character set in use to the client. The client will switch to
use this character set for this connection.
One should use @code{mysql_real_escape_string()} when escaping strings
for a SQL query. @code{mysql_real_escape_string()} is identical to the
old @code{mysql_escape_string()} function, except that it takes the MYSQL
connection handle as the first parameter.
If the client is compiled with different paths than where the server is
installed and the user who configured @strong{MySQL} didn't included all
character sets in the @strong{MySQL} binary, one must specify for
the client where it can find the additional character sets it will need
if the server runs with a different character set than the client.
One can specify this by putting in a @strong{MySQL} option file:
@example
[client]
character-sets-dir=/usr/local/mysql/share/mysql/charsets
@end example
where the path points to where the dynamic @strong{MySQL} character sets
are stored.
One can force the client to use specific character set by specifying:
@example
[client]
default-character-set=character-set-name
@end example
but normally this is never needed.
@cindex character sets, adding
@cindex adding, character sets
@node Adding character set, Character arrays, Character sets, Languages
@subsection Adding a New Character Set
To add another character set to @strong{MySQL}, use the following procedure.
Decide if the set is simple or complex. If the character set
does not need to use special string collating routines for
sorting and does not need multi-byte character support, it is
simple. If it needs either of those features, it is complex.
For example, @code{latin1} and @code{danish} are simple charactersets while
@code{big5} or @code{czech} are complex character sets.
In the following section, we have assumed that you name your character
set @code{MYSET}.
For a simple character set do the following:
@enumerate
@item
Add MYSET to the end of the @file{sql/share/charsets/Index} file
Assign an unique number to it.
@item
Create the file @file{sql/share/charsets/MYSET.conf}.
(You can use @file{sql/share/charsets/latin1.conf} as a base for this).
The syntax for the file very simple:
@itemize @bullet
@item
Comments start with a '#' character and proceed to the end of the line.
@item
Words are separated by arbitrary amounts of whitespace.
@item
When defining the character set, every word must be a number in hexadecimal
format
@item
The @code{ctype} array takes up the first 257 words. The
@code{to_lower}, @code{to_upper} and @code{sort_order} arrays take up
256 words each after that.
@end itemize
@xref{Character arrays}.
@item
Add the character set name to the @code{CHARSETS_AVAILABLE} and
@code{COMPILED_CHARSETS} lists in @code{configure.in}.
@item
Reconfigure, recompile, and test.
@end enumerate
For a complex character set do the following:
@enumerate
@item
Create the file @file{strings/ctype-MYSET.c} in the @strong{MySQL} source
distribution.
@item
Add MYSET to the end of the @file{sql/share/charsets/Index} file.
Assign an unique number to it.
@item
Look at one of the existing @file{ctype-*.c} files to see what needs to
be defined, for example @file{strings/ctype-big5.c}. Note that the
arrays in your file must have names like @code{ctype_MYSET},
@code{to_lower_MYSET}, and so on. This corresponds to the arrays
in the simple character set. @xref{Character arrays}. For a complex
character set
@item
Near the top of the file, place a special comment like this:
@example
/*
* This comment is parsed by configure to create ctype.c,
* so don't change it unless you know what you are doing.
*
* .configure. number_MYSET=MYNUMBER
* .configure. strxfrm_multiply_MYSET=N
* .configure. mbmaxlen_MYSET=N
*/
@end example
The @code{configure} program uses this comment to include
the character set into the @strong{MySQL} library automatically.
The strxfrm_multiply and mbmaxlen lines will be explained in
the following sections. Only include them if you the string
collating functions or the multi-byte character set functions,
respectively.
@item
You should then create some of the following functions:
@itemize @bullet
@item @code{my_strncoll_MYSET()}
@item @code{my_strcoll_MYSET()}
@item @code{my_strxfrm_MYSET()}
@item @code{my_like_range_MYSET()}
@end itemize
@xref{String collating}.
@item
Add the character set name to the @code{CHARSETS_AVAILABLE} and
@code{COMPILED_CHARSETS} lists in @code{configure.in}.
@item
Reconfigure, recompile, and test.
@end enumerate
The file @file{sql/share/charsets/README} includes some more instructions.
If you want to have the character set included in the @strong{MySQL}
distribution, mail a patch to @email{internals@@lists.mysql.com}.
@node Character arrays, String collating, Adding character set, Languages
@subsection The character definition arrays
@code{to_lower[]} and @code{to_upper[]} are simple arrays that hold the
lowercase and uppercase characters corresponding to each member of the
character set. For example:
@example
to_lower['A'] should contain 'a'
to_upper['a'] should contain 'A'
@end example
@code{sort_order[]} is a map indicating how characters should be ordered for
comparison and sorting purposes. For many character sets, this is the same as
@code{to_upper[]} (which means sorting will be case insensitive).
@strong{MySQL} will sort characters based on the value of
@code{sort_order[character]}. For more complicated sorting rules, see
the discussion of string collating below. @xref{String collating}.
@code{ctype[]} is an array of bit values, with one element for one character.
(Note that @code{to_lower[]}, @code{to_upper[]}, and @code{sort_order[]}
are indexed by character value, but @code{ctype[]} is indexed by character
value + 1. This is an old legacy to be able to handle EOF.)
You can find the following bitmask definitions in @file{m_ctype.h}:
@example
#define _U 01 /* Uppercase */
#define _L 02 /* Lowercase */
#define _N 04 /* Numeral (digit) */
#define _S 010 /* Spacing character */
#define _P 020 /* Punctuation */
#define _C 040 /* Control character */
#define _B 0100 /* Blank */
#define _X 0200 /* heXadecimal digit */
@end example
The @code{ctype[]} entry for each character should be the union of the
applicable bitmask values that describe the character. For example,
@code{'A'} is an uppercase character (@code{_U}) as well as a
hexadecimal digit (@code{_X}), so @code{ctype['A'+1]} should contain the
value:
@example
_U + _X = 01 + 0200 = 0201
@end example
@cindex collating, strings
@cindex string collating
@node String collating, Multi-byte characters, Character arrays, Languages
@subsection String Collating Support
If the sorting rules for your language are too complex to be handled
with the simple @code{sort_order[]} table, you need to use the string
collating functions.
Right now the best documentation on this is the character sets that are
already implemented. Look at the big5, czech, gbk, sjis, and tis160
character sets for examples.
You must specify the @code{strxfrm_multiply_MYSET=N} value in the
special comment at the top of the file. @code{N} should be set to
the maximum ratio the strings may grow during @code{my_strxfrm_MYSET} (it
must be a positive integer).
@cindex characters, multi-byte
@cindex multi-byte characters
@node Multi-byte characters, , String collating, Languages
@subsection Multi-byte Character Support
If your want to add support for a new character set that includes
multi-byte characters, you need to use the multi-byte character
functions.
Right now the best documentation on this is the character sets that are
already implemented. Look at the euc_kr, gb2312, gbk, sjis and ujis
character sets for examples. These are implemented in the
@code{ctype-'charset'.c} files in the @file{strings} directory.
You must specify the @code{mbmaxlen_MYSET=N} value in the special
comment at the top of the source file. @code{N} should be set to the
size in bytes of the largest character in the set.
@cindex tables, maximum size
@cindex size of tables
@cindex operating systems, file size limits
@cindex limits, file size
@cindex files, size limits
@node Table size, , Languages, Server
@section How Big MySQL Tables Can Be
@strong{MySQL} Version 3.22 has a 4G limit on table size. With the new
@code{MyISAM} in @strong{MySQL} Version 3.23 the maximum table size is
pushed up to 8 million terabytes (2 ^ 63 bytes).
Note, however, that operating systems have their own file size
limits. Here are some examples:
@multitable @columnfractions .5 .5
@item @strong{Operating System} @tab @strong{File Size Limit}
@item Linux-Intel 32 bit@tab 2G, 4G or bigger depending on Linux version
@item Linux-Alpha @tab 8T (?)
@item Solaris 2.5.1 @tab 2G (possible 4G with patch)
@item Solaris 2.6 @tab 4G
@item Solaris 2.7 Intel @tab 4G
@item Solaris 2.7 ULTRA-SPARC @tab 8T (?)
@end multitable
On Linux 2.2 you can get bigger tables than 2G by using the LFS patch for
the ext2 file system. On Linux 2.4 there exists also patches for ReiserFS
to get support for big files.
This means that the table size for @strong{MySQL} is normally limited by
the operating system.
By default, @strong{MySQL} tables have a maximum size of about 4G. You can
check the maximum table size for a table with the @code{SHOW TABLE STATUS}
command or with the @code{myisamchk -dv table_name}.
@xref{SHOW}.
If you need bigger tables than 4G (and your operating system supports
this), you should set the @code{AVG_ROW_LENGTH} and @code{MAX_ROWS}
parameter when you create your table. @xref{CREATE TABLE}. You can
also set these later with @code{ALTER TABLE}. @xref{ALTER TABLE}.
If your big table is going to be read-only, you could use
@code{myisampack} to merge and compress many tables to one.
@code{myisampack} usually compresses a table by at least 50%, so you can
have, in effect, much bigger tables. @xref{myisampack, ,
@code{myisampack}}.
You can go around the operating system file limit for @code{MyISAM} data
files by using the @code{RAID} option. @xref{CREATE TABLE}.
Another solution can be the included MERGE library, which allows you to
handle a collection of identical tables as one. @xref{MERGE, MERGE
tables}.
@cindex replication
@cindex increasing, speed
@cindex speed, increasing
@cindex databases, replicating
@node Replication, Performance, Server, Top
@chapter Replication in MySQL
@menu
* Replication Intro:: Introduction
* Replication Implementation:: Replication Implementation Overview
* Replication HOWTO:: HOWTO
* Replication Features:: Replication Features
* Replication Options:: Replication Options in my.cnf
* Replication SQL:: SQL Commands related to replication
* Replication FAQ:: Frequently Asked Questions about replication
* Replication Problems:: Troubleshooting Replication.
@end menu
@node Replication Intro, Replication Implementation, Replication, Replication
@section Introduction
One way replication can be used is to increase both robustness and
speed. For robustness you can have two systems and can switch to the backup if
you have problems with the master. The extra speed is achieved by
sending a part of the non-updating queries to the replica server. Of
course this only works if non-updating queries dominate, but that is the
normal case.
Starting in Version 3.23.15, @strong{MySQL} supports one-way replication
internally. One server acts as the master, while the other acts as the
slave. Note that one server could play the roles of master in one pair
and slave in the other. The master server keeps a binary log of updates
(@xref{Binary log}.) and an index file to binary logs to keep track of
log rotation. The slave, upon connecting, informs the master where it
left off since the last successfully propagated update, catches up on
the updates, and then blocks and waits for the master to notify it of
the new updates.
Note that if you are replicating a database, all updates to this
database should be done through the master!
On older servers one can use the update log to do simple replication.
@xref{Log Replication}.
Another benefit of using replication is that one can get live backups of
the system by doing a backup on a slave instead of doing it on the
master. @xref{Backup}.
@cindex master-slave setup
@node Replication Implementation, Replication HOWTO, Replication Intro, Replication
@section Replication Implementation Overview
@strong{MySQL} replication is based on the server keeping track of all
changes to your database (updates, deletes, etc) in the binary
log. (@xref{Binary log}.) and the slave server(s) reading the saved
queries from the master server's binary log so that the slave can
execute the same queries on its copy of the data.
It is @strong{very important} to realize that the binary log is simply a
record starting from a fixed point in time (the moment you enable binary
logging). Any slaves which you set up will need copies of all the data
from your master as it existed the moment that you enabled binary
logging on the master. If you start your slaves with data that doesn't
agree with what was on the master @strong{when the binary log was
started}, your slaves may fail.
A future version (4.0) of @strong{MySQL} will remove the need to keep a
(possibly large) snapshot of data for new slaves that you might wish to
set up through the live backup functionality with no locking required.
However, at this time, it is necessary to block all writes either with a
global read lock or by shutting down the master while taking a snapshot.
Once a slave is properly configured and running, it will simply connect
to the master and wait for updates to process. If the master goes away
or the slave loses connectivity with your master, it will keep trying to
connect every @code{master-connect-retry} seconds until it is able to
reconnect and resume listening for updates.
Each slave keeps track of where it left off. The master server has no
knowledge of how many slaves there are or which ones are up-to-date at
any given time.
The next section explains the master/slave setup process in more detail.
@node Replication HOWTO, Replication Features, Replication Implementation, Replication
@section HOWTO
Below is a quick description of how to set up complete replication on
your current @strong{MySQL} server. It assumes you want to replicate all
your databases and have not configured replication before. You will need
to shutdown your master server briefly to complete the steops outlined
below.
@enumerate
@item
Make sure you have a recent version of @strong{MySQL} installed on the master
and slave(s).
Use Version 3.23.29 or higher. Previous releases used a different binary
log format and had bugs which have been fixed in newer releases. Please,
do not report bugs until you have verified that the problem is present
in the latest release.
@item
Set up special a replication user on the master with the @code{FILE}
privilege and permission to connect from all the slaves. If the user is
only doing replication (which is recommended), you don't need to grant any
additional privileges.
For example, to create a user named @code{repl} which can access your
master from any host, you might use this command:
@example
GRANT FILE ON *.* TO repl@@"%" IDENTIFIED BY '<password>';
@end example
@item
Shut down @strong{MySQL} on the master.
@example
mysqladmin -u root -p<password> shutdown
@end example
@item
Snapshot all the data on your master server.
The easiest way to do this (on Unix) is to simply use @strong{tar} to
produce an archvie of your entrie data directory. The exact data
directory location depends on your installation.
@example
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir
@end example
Windows users can use WinZip or similar software to create an archive of
the data directory.
@item
In @code{my.cnf} on the master add @code{log-bin} and
@code{server-id=unique number} to the @code{[mysqld]} section and
restart it. It is very important that the id of the slave is different from
the id of the master. Think of @code{server-id} as something similar
to the IP address - it uniquely identifies the server instance in the
comminity of replication partners.
@example
[mysqld]
log-bin
server-id=1
@end example
@item
Restart @strong{MySQL} on the master.
@item
Add the following to @code{my.cnf} on the slave(s):
@example
master-host=<hostname of the master>
master-user=<replication user name>
master-password=<replication user password>
master-port=<TCP/IP port for master>
server-id=<some unique number between 2 and 2^32-1>
@end example
replacing the values in <> with what is relevant to your system.
@code{server-id} must be different for each server participating in
replication. If you don't specify a server-id, it will be set to 1 if
you have not defined @code{master-host}, else it will be set to 2. Note
that in the case of @code{server-id} omission the master will refuse
connections from all slaves, and the slave will refuse to connect to a
master. Thus, omitting @code{server-id} is only good for backup with a
binary log.
@item
Copy the snapshot data into your data directory on your slave(s). Make
sure that the privileges on the files and directories are correct. The
user which @strong{MySQL} runs as needs to be able to read and write to
them, just as on the master.
@item Restart the slave(s).
@end enumerate
After you have done the above, the slave(s) should connect to the master
and catch up on any updates which happened since the snapshot was taken.
If you have forgotten to set @code{server-id} for the slave you will get
the following error in the error log file:
@example
Warning: one should set server_id to a non-0 value if master_host is set.
The server will not act as a slave.
@end example
If you have forgot to do this for the master, the slaves will not be
able to connect to the master.
If a slave is not able to replicate for any reason, you will find error
messages in the error log on the slave.
Once a slave is replicating, you will find a file called
@code{master.info} in the same directory as your error log. The
@code{master.info} file is used by the slave to keep track of how much
of the master's binary log is has processed. @strong{Do not} remove or
edit the file, unless you really know what you are doing. Even in that case,
it is preferred that you use @code{CHANGE MASTER TO} command.
@cindex options, replication
@cindex @code{my.cnf} file
@cindex files,@code{my.cnf}
@node Replication Features, Replication Options, Replication HOWTO, Replication
@section Replication Features and known problems
Below is an explanation of what is supported and what is not:
@itemize @bullet
@item
Replication will be done correctly with @code{AUTO_INCREMENT},
@code{LAST_INSERT_ID}, and @code{TIMESTAMP} values.
@item
@code{RAND()} in updates does not replicate properly. Use
@code{RAND(some_non_rand_expr)} if you are replcating updates with
@code{RAND()}. You can, for example, use @code{UNIX_TIMESTAMP()} for the
argument to @code{RAND()}.
@item
@code{LOAD DATA INFILE} will be handled properly as long as the file
still resides on the master server at the time of update
propagation. @code{LOAD LOCAL DATA INFILE} will be skipped.
@item
Update queries that use user variables are not replication-safe (yet).
@item
@code{FLUSH} commands are not stored in the binary log and are because
of this not replicated to the slaves. This is not normally a problem as
@code{FLUSH} doesn't change anything. This does however mean that if you
update the @code{MySQL} privilege tables directly without using
@code{GRANT} statement and you replicate the @code{MySQL} privilege
database, you must do a @code{FLUSH PRIVILEGES} on your slaves to put
the new privileges into effect.
@item
Temporary tables starting in 3.23.29 are replicated properly with the
exception of the case when you shut down slave server ( not just slave thread),
you have some temporary tables open, and the are used in subsequent updates.
To deal with this problem, to shut down the slave, do @code{SLAVE STOP}, then
check @code{Slave_open_temp_tables} variable to see if it is 0, then issue
@code{mysqladmin shutdown}. If the number is not 0, restart the slave thread
with @code{SLAVE START} and see
if you have better luck next time. There will be a cleaner solution, but it
has to wait until version 4.0.
In earlier versions temporary tables are not being replicated properly - we
recommend that you either upgrade, or execute @code{SET SQL_LOG_BIN=0} on
your clients before all queries with temp tables.
@item
@strong{MySQL} only supports one master and many slaves. We will in 4.x
add a voting algorithm to automaticly change master if something goes
wrong with the current master. We will also introduce 'agent' processes
to help doing load balancing by sending select queries to different
slaves.
@item
Starting in Version 3.23.26, it is safe to connect servers in a circular
master-slave relationship with @code{log-slave-updates} enabled.
Note, however, that many queries will not work right in this kind of
setup unless your client code is written to take care of the potential
problems that can happen from updates that occur in different sequence
on different servers. Note that the log format has changed in Version 3.23.26
so that pre-3.23.26 slaves will not be able to read it.
@item
If the query on the slave gets an error, the slave thread will
terminate, and a message will appear in the @code{.err} file. You should
then connect to the slave manually, fix the cause of the error (for
example, non-existent table), and then run @code{SLAVE START} sql
command (available starting in Version 3.23.16). In Version 3.23.15, you
will have to restart the server.
@item
If connection to the master is lost, the slave will retry immediately,
and then in case of failure every @code{master-connect-retry} (default
60) seconds. Because of this, it is safe to shut down the master, and
then restart it after a while. The slave will also be able to deal with
network connectivity outages.
@item
Shutting down the slave (cleanly) is also safe, as it keeps track of
where it left off. Unclean shutdowns might produce problems, especially
if disk cache was not synced before the system died. Your system fault
tolerance will be greatly increased if you have a good UPS.
@item
If the master is listening on a non-standard port, you will also need to
specify this with @code{master-port} parameter in @code{my.cnf} .
@item
In Version 3.23.15, all of the tables and databases will be
replicated. Starting in Version 3.23.16, you can restrict replication to
a set of databases with @code{replicate-do-db} directives in
@code{my.cnf} or just exclude a set of databases with
@code{replicate-ignore-db}. Note that up until Version 3.23.23, there was a bug
that did not properly deal with @code{LOAD DATA INFILE} if you did it in
a database that was excluded from replication.
@item
Starting in Version 3.23.16, @code{SET SQL_LOG_BIN = 0} will turn off
replication (binary) logging on the master, and @code{SET SQL_LOG_BIN =
1} will turn in back on - you must have the process privilege to do
this.
@item
Starting in Version 3.23.19, you can clean up stale replication leftovers when
something goes wrong and you want a clean start with @code{FLUSH MASTER}
and @code{FLUSH SLAVE} commands. In Version 3.23.26 we have renamed them to
@code{RESET MASTER} and @code{RESET SLAVE} respectively to clarify
what they do. The old @code{FLUSH} variants still work, though, for
compatibility.
@item
Starting in Version 3.23.21, you can use @code{LOAD TABLE FROM MASTER} for
network backup and to set up replication initially. We have recently
received a number of bug reports concerning it that we are investigating, so
we recommend that you use it only in testing until we make it more stable.
@item
Starting in Version 3.23.23, you can change masters and adjust log position
with @code{CHANGE MASTER TO}.
@item
Starting in Version 3.23.23, you tell the master that updates in certain
databases should not be logged to the binary log with @code{binlog-ignore-db}.
@item
Starting in Version 3.23.26, you can use @code{replicate-rewrite-db} to tell
the slave to apply updates from one database on the master to the one
with a different name on the slave.
@item
Starting in Version 3.23.28, you can use @code{PURGE MASTER LOGS TO 'log-name'}
to get rid of old logs while the slave is running.
@end itemize
@node Replication Options, Replication SQL, Replication Features, Replication
@section Replication Options in my.cnf
If you are using replication, we recommend you to use MySQL Version 3.23.30 or
later. Older versions work, but they do have some bugs and are missing some
features.
On both master and slave you need to use the @code{server-id} option.
This sets an unique replication id. You should pick a unique value in the
range between 1 to 2^32-1 for each master and slave.
Example: @code{server-id=3}
The following table has the options you can use for the @strong{MASTER}:
@multitable @columnfractions .3 .7
@item @strong{Option} @tab @strong{Description}
@item @code{log-bin=filename} @tab
Write to a binary update log to the specified location. Note that if you
give it a parameter with an extension
(for example, @code{log-bin=/mysql/logs/replication.log} ) versions up to 3.23.24
will not work right during replication if you do @code{FLUSH LOGS} . The
problem is fixed in Version 3.23.25. If you are using this kind of log name,
@code{FLUSH LOGS} will be ignored on binlog. To clear the log, run
@code{FLUSH MASTER}, and do not forget to run @code{FLUSH SLAVE} on all
slaves. In Version 3.23.26 and in later versions you should use @code{RESET MASTER}
and @code{RESET SLAVE}
@item @code{log-bin-index=filename} @tab
Because the user could issue the @code{FLUSH LOGS} command, we need to
know which log is currently active and which ones have been rotated out
and in what sequence. This information is stored in the binary log index file.
The default is `hostname`.index. You can use this option if you want to
be a rebel. (Example: @code{log-bin-index=db.index})
@item @code{sql-bin-update-same} @tab
If set, setting @code{SQL_LOG_BIN} to a value will automatically set
@code{SQL_LOG_UPDATE} to the same value and vice versa.
@item @code{binlog-do-db=database_name} @tab
Tells the master it should log updates for the specified database, and
exclude all others not explicitly mentioned.
(Example: @code{binlog-do-db=some_database})
@item @code{binlog-ignore-db=database_name} @tab
Tells the master that updates to the given database should not be logged
to the binary log (Example: @code{binlog-ignore-db=some_database})
@end multitable
The following table has the options you can use for the @strong{SLAVE}:
@multitable @columnfractions .3 .7
@item @strong{Option} @tab @strong{Description}
@item @code{master-host=host} @tab
Master hostname or IP address for replication. If not set, the slave
thread will not be started.
(Example: @code{master-host=db-master.mycompany.com})
@item @code{master-user=username} @tab
The user the slave thread will us for authentication when connecting to
the master. The user must have @code{FILE} privilege. If the master user
is not set, user @code{test} is assumed. (Example:
@code{master-user=scott})
@item @code{master-password=password} @tab
The password the slave thread will authenticate with when connecting to
the master. If not set, an empty password is assumed. (Example:
@code{master-password=tiger})
@item @code{master-port=portnumber} @tab
The port the master is listening on. If not set, the compiled setting of
@code{MYSQL_PORT} is assumed. If you have not tinkered with
@code{configure} options, this should be 3306. (Example:
@code{master-port=3306})
@item @code{master-connect-retry=seconds} @tab
The number of seconds the slave thread will sleep before retrying to
connect to the master in case the master goes down or the connection is
lost. Default is 60. (Example: @code{master-connect-retry=60})
@item @code{master-info-file=filename} @tab
The location of the file that remembers where we left off on the master
during the replication process. The default is master.info in the data
directory. Sasha: The only reason I see for ever changing the default
is the desire to be rebelious. (Example:
@code{master-info-file=master.info})
@item @code{replicate-do-table=db_name.table_name} @tab
Tells the slave thread to restrict replication to the specified database.
To specify more than one table, use the directive multiple times,
once for each table. .
(Example: @code{replicate-do-table=some_db.some_table})
@item @code{replicate-ignore-table=db_name.table_name} @tab
Tells the slave thread to not replicate to the specified table. To
specify more than one table to ignore, use the directive multiple
times, once for each table.(Example:
@code{replicate-ignore-table=db_name.some_table})
@item @code{replicate-wild-do-table=db_name.table_name} @tab
Tells the slave thread to restrict replication to the tables that match the
specified wildcard pattern. .
To specify more than one table, use the directive multiple times,
once for each table. .
(Example: @code{replicate-do-table=foo%.bar%} will replicate only updates
to tables in all databases that start with foo and whose table names
start with bar)
@item @code{replicate-wild-ignore-table=db_name.table_name} @tab
Tells the slave thread to not replicate to the tables that match the given
wild card pattern. To
specify more than one table to ignore, use the directive multiple
times, once for each table.(Example:
@code{replicate-ignore-table=foo%.bar%} - will not upates to tables in all databases that start with foo and whose table names
start with bar)
@item @code{replicate-ignore-db=database_name} @tab
Tells the slave thread to not replicate to the specified database. To
specify more than one database to ignore, use the directive multiple
times, once for each database. This option will not work if you use cross
database updates. If you need cross database updates to work, make sure
you have 3.23.28 or later, and use
@code{replicate-wild-ignore-table=db_name.%}(Example:
@code{replicate-ignore-db=some_db})
@item @code{replicate-do-db=database_name} @tab
Tells the slave thread to restrict replication to the specified database.
To specify more than one database, use the directive multiple times,
once for each database. Note that this will only work if you do not use
cross-database queries such as @code{UPDATE some_db.some_table SET
foo='bar'} while having selected a different or no database. If you need
cross database updates to work, make sure
you have 3.23.28 or later, and use
@code{replicate-wild-do-table=db_name.%}
(Example: @code{replicate-do-db=some_db})
@item @code{log-slave-updates} @tab
Tells the slave to log the updates from the slave thread to the binary
log. Off by default. You will need to turn it on if you plan to
daisy-chain the slaves.
@item @code{replicate-rewrite-db=from_name->to_name} @tab
Updates to a database with a different name than the original (Example:
@code{replicate-rewrite-db=master_db_name->slave_db_name}
@item @code{skip-slave-start} @tab
Tells the slave server not to start the slave on the startup. The user
can start it later with @code{SLAVE START}.
@end multitable
@cindex SQL commands, replication
@cindex commands, replication
@cindex replication, commands
@node Replication SQL, Replication FAQ, Replication Options, Replication
@section SQL Commands Related to Replication
Replication can be controlled through the SQL interface. Below is the
summary of commands:
@multitable @columnfractions .30 .70
@item @strong{Command} @tab @strong{Description}
@item @code{SLAVE START}
@tab Starts the slave thread. (Slave)
@item @code{SLAVE STOP}
@tab Stops the slave thread. (Slave)
@item @code{SET SQL_LOG_BIN=0}
@tab Disables update logging if the user has process privilege.
Ignored otherwise. (Master)
@item @code{SET SQL_LOG_BIN=1}
@tab Re-enables update logging if the user has process privilege.
Ignored otherwise. (Master)
@item @code{SET SQL_SLAVE_SKIP_COUNTER=n}
@tab Skip the next @code{n} events from the master. Only valid when
the slave thread is not running, otherwise, gives an error. Useful for
recovering from replication glitches.
@item @code{RESET MASTER}
@tab Deletes all binary logs listed in the index file, resetting the binlog
index file to be empty. In pre-3.23.26 versions, @code{FLUSH MASTER} (Master)
@item @code{RESET SLAVE}
@tab Makes the slave forget its replication position in the master
logs. In pre 3.23.26 versions the command was called
@code{FLUSH SLAVE}(Slave)
@item @code{LOAD TABLE tblname FROM MASTER}
@tab Downloads a copy of the table from master to the slave. (Slave)
@item @code{CHANGE MASTER TO master_def_list}
@tab Changes the master parameters to the values specified in
@code{master_def_list} and restarts the slave thread. @code{master_def_list}
is a comma-separated list of @code{master_def} where @code{master_def} is
one of the following: @code{MASTER_HOST}, @code{MASTER_USER},
@code{MASTER_PASSWORD}, @code{MASTER_PORT}, @code{MASTER_CONNECT_RETRY},
@code{MASTER_LOG_FILE}, @code{MASTER_LOG_POS}. Example:
@example
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4;
@end example
You only need to specify the values that need to be changed. The values that
you omit will stay the same with the exception of when you change the host or
the port. In that case, the slave will assume that since you are connecting to
a different host or a different port, the master is different. Therefore, the
old values of log and position are not applicable anymore, and will
automatically be reset to an empty string and 0, respectively (the start
values). Note that if you restart the slave, it will remember its last master.
If this is not desirable, you should delete the @file{master.info} file before
restarting, and the slave will read its master from @code{my.cnf} or the
command line. (Slave)
@item @code{SHOW MASTER STATUS} @tab Provides status information on the binlog of the master. (Master)
@item @code{SHOW SLAVE STATUS} @tab Provides status information on essential parameters of the slave thread. (Slave)
@item @code{SHOW MASTER LOGS} @tab Only available starting in Version 3.23.28. Lists the binary logs on the master. You should use this command prior to @code{PURGE MASTER LOGS TO} to find out how far you should go.
@item @code{PURGE MASTER LOGS TO 'logname'}
@tab Available starting in Version 3.23.28. Deletes all the
replication logs that are listed in the log
index as being prior to the specified log, and removed them from the
log index, so that the given log now becomes first. Example:
@example
PURGE MASTER LOGS TO 'mysql-bin.010'
@end example
This command will do nothing and fail with an error if you have an
active slave that is currently reading one of the logs you are trying to
delete. However, if you have a dormant slave, and happen to purge one of
the logs it wants to read, the slave will be unable to replicate once it
comes up. The command is safe to run while slaves are replicating - you
do not need to stop them.
You must first check all the slaves with @code{SHOW SLAVE STATUS} to
see which log they are on, then do a listing of the logs on the
master with @code{SHOW MASTER LOGS}, find the earliest log among all
the slaves (if all the slaves are up to date, this will be the
last log on the list), backup all the logs you are about to delete
(optional) and purge up to the target log.
@end multitable
@node Replication FAQ, Replication Problems, Replication SQL, Replication
@section Replication FAQ
@cindex @code{Binlog_Dump}
@strong{Q}: Why do I sometimes see more than one @code{Binlog_Dump} thread on
the master after I have restarted the slave?
@strong{A}: @code{Binlog_Dump} is a continuous process that is handled by the
server in the following way:
@itemize @bullet
@item
Catch up on the updates.
@item
Once there are no more updates left, go into @code{pthread_cond_wait()},
from which we can be awakened either by an update or a kill.
@item
On wake up, check the reason. If we are not supposed to die, continue
the @code{Binlog_dump} loop.
@item
If there is some fatal error, such as detecting a dead client,
terminate the loop.
@end itemize
So if the slave thread stops on the slave, the corresponding
@code{Binlog_Dump} thread on the master will not notice it until after
at least one update to the master (or a kill), which is needed to wake
it up from @code{pthread_cond_wait()}. In the meantime, the slave
could have opened another connection, which resulted in another
@code{Binlog_Dump} thread.
The above problem should not be present in Version 3.23.26 and later
versions. In Version 3.23.26 we added @code{server-id} to each
replication server, and now all the old zombie threads are killed on the
master when a new replication thread connects from the same slave
@strong{Q}: How do I rotate replication logs?
@strong{A}: In Version 3.23.28 you should use @code{PURGE MASTER LOGS
TO} command after determining which logs can be deleted, and optionally
backing them up first. In earlier versions the process is much more
painful, and cannot be safely done without stopping all the slaves in
the case that you plan to re-use log names. You will need to stop the
slave threads, edit the binary log index file, delete all the old logs,
restart the master, start slave threads,and then remove the old log files.
@strong{Q}: How do I upgrade on a hot replication setup?
@strong{A}: If you are upgrading pre-3.23.26 versions, you should just
lock the master tables, let the slave catch up, then run @code{FLUSH
MASTER} on the master, and @code{FLUSH SLAVE} on the slave to reset the
logs, then restart new versions of the master and the slave. Note that
the slave can stay down for some time - since the master is logging
all the updates, the slave will be able to catch up once it is up and
can connect.
After 3.23.26, we have locked the replication protocol for modifications, so
you can upgrade masters and slave on the fly to a newer 3.23 version and you
can have different versions of @strong{MySQL} running on the slave and the
master, as long as they are both newer than 3.23.26.
@cindex replication, two-way
@strong{Q}: What issues should I be aware of when setting up two-way
replication?
@strong{A}: @strong{MySQL} replication currently does not support any
locking protocol between master and slave to guarantee the atomicity of
a distributed (cross-server) update. In in other words, it is possible
for client A to make an update to co-master 1, and in the meantime,
before it propagates to co-master 2, client B could make an update to
co-master 2 that will make the update of client A work differently than
it did on co-master 1. Thus when the update of client A will make it
to co-master 2, it will produce tables that will be different than
what you have on co-master 1, even after all the updates from co-master
2 have also propagated. So you should not co-chain two servers in a
two-way replication relationship, unless you are sure that you updates
can safely happen in any order, or unless you take care of mis-ordered
updates somehow in the client code.
You must also realize that two-way replication actually does not improve
performance very much, if at all, as far as updates are concerned. Both
servers need to do the same amount of updates each, as you would have
one server do. The only difference is that there will be a little less
lock contention, because the updates originating on another server will
be serialized in one slave thread. This benefit, though, might be
offset by network delays.
@cindex performance, improving
@cindex increasing, performance
@strong{Q}: How can I use replication to improve performance of my system?
@strong{A}: You should set up one server as the master, and direct all
writes to it, and configure as many slaves as you have the money and
rackspace for, distributing the reads among the master and the slaves.
You can also start the slaves with @code{--skip-bdb},
@code{--low-priority-updates} and @code{--delay-key-write-for-all-tables}
to get speed improvements for the slave. In this case the slave will
use non-transactional @code{MyISAM} tables instead of @code{BDB} tables
to get more speed.
@strong{Q}: What should I do to prepare my client code to use
performance-enhancing replication?
@strong{A}:
If the part of your code that is responsible for database access has
been properly abstracted/modularized, converting it to run with the
replicated setup should be very smooth and easy - just change the
implementation of your database access to read from some slave or the
master, and to awlays write to the master. If your code does not have
this level of abstraction,
setting up a replicated system will give you an opportunity/motivation
to it clean up.
You should start by creating a wrapper library
/module with the following functions:
@itemize
@item
@code{safe_writer_connect()}
@item
@code{safe_reader_connect()}
@item
@code{safe_reader_query()}
@item
@code{safe_writer_query()}
@end itemize
@code{safe_} means that the function will take care of handling all
the error conditions.
You should then convert your client code to use the wrapper library.
It may be a painful and scary process at first, but it will pay off in
the long run. All applications that follow the above pattern will be
able to take advantage of one-master/many slaves solution. The
code will be a lot easier to maintain, and adding troubleshooting
options will be trivial. You will just need to modify one or two
functions, for example, to log how long each query took, or which
query, among your many thousands, gave you an error. If you have written a lot of code already,
you may want to automate the conversion task by using Monty's
@code{replace} utility, which comes with the standard distribution of
@strong{MySQL}, or just write your own Perl script. Hopefully, your
code follows some recognizable pattern. If not, then you are probably
better off re-writing it anyway, or at least going through and manually
beating it into a pattern.
Note that, of course, you can use different names for the
functions. What is important is having unified interface for connecting
for reads, connecting for writes, doing a read, and doing a write.
@strong{Q}: When and how much can @strong{MySQL} replication improve the performance
of my system?
@strong{A}: @strong{MySQL} replication is most beneficial for a system
with frequent reads and not so frequent writes. In theory, by using a
one master/many slaves setup you can scale by adding more slaves until
you either run out of network bandwidth, or your update
load grows to the point
that the master cannot handle it.
In order to determine how many slaves you can get before the added
benefits begin to level out, and how much you can improve performance
of your site, you need to know your query patterns, and empirically
(by benchmarking) determine the relationship between the throughput
on reads (reads per second, or @code{max_reads}) and on writes
@code{max_writes}) on a typical master and a typical slave. The
example below will show you a rather simplified calculation of what you
can get with replication for our imagined system.
Let's say our system load consists of 10% writes and 90% reads, and we
have determined that @code{max_reads} = 1200 - 2 * @code{max_writes},
or in other words, our system can do 1200 reads per second with no
writes, our average write is twice as slow as average read,
and the relationship is
linear. Let us suppose that our master and slave are of the same
capacity, and we have N slaves and 1 master. Then we have for each
server (master or slave):
@code{reads = 1200 - 2 * writes} (from bencmarks)
@code{reads = 9* writes / (N + 1) } (reads split, but writes go
to all servers)
@code{9*writes/(N+1) + 2 * writes = 1200}
@code{writes = 1200/(2 + 9/(N+1)}
So if N = 0, which means we have no replication, our system can handle
1200/11, about 109 writes per second (which means we will have 9 times
as many reads due to the nature of our application).
If N = 1, we can get up to 184 writes per second.
If N = 8, we get up to 400.
If N = 17, 480 writes.
Eventually as N approaches infinity (and our budget negative infinity),
we can get very close to 600 writes per second, increasing system
throughput about 5.5 times. However, with only 8 servers, we increased
it almost 4 times already.
Note that our computations assumed infinite network bandwidth, and
neglected several other factors that could turn out to be signficant on
your system. In many cases, you may not be able to make a computation
similar to the one above that will accurately predict what will happen
on your system if you add N replication slaves. However, answering the
following questions should help you decided whether and how much, if at
all, the replication will improve the performance of your system:
@itemize @bullet
@item
What is the read/write ratio on your system?
@item
How much more write load can one server handle if you reduce the reads?
@item
How many slaves do you have bandwidth for on your network?
@end itemize
@strong{Q}: How can I use replication to provide redundancy/high
availability?
@strong{A}: With the currently available features, you would have to
set up a master and a slave (or several slaves), and write a script
that will monitor the
master to see if it is up, and instruct your applications and
the slaves of the master change in case of failure. Some suggestions:
@itemize @bullet
@item
To tell a slave to change the master use the @code{CHANGE MASTER TO} command.
@item
A good way to keep your applications informed where the master is by
having a dynamic DNS entry for the master. With @strong{bind} you can
use @code{nsupdate} to dynamically update your DNS.
@item
You should run your slaves with the @code{log-bin} option and without
@code{log-slave-updates}. This way the slave will be ready to become a
master as soon as you issue @code{STOP SLAVE}; @code{RESET MASTER}, and
@code{CHANGE MASTER TO} on the other slaves. It will also help you catch
spurious updates that may happen because of misconfiguration of the
slave (ideally, you want to configure access rights so that no client
can update the slave, except for the slave thread) combined with the
bugs in your client programs (they should never update the slave
directly).
@end itemize
We are currently working on intergrating an automatic master election
system into @strong{MySQL}, but until it is ready, you will have to
create your own monitoring tools.
@node Replication Problems, , Replication FAQ, Replication
@section Troubleshooting Replication
If you have followed the instructions, and your replication setup is not
working, first elliminate the user error factor by checking the following:
@itemize @bullet
@item
Is the master logging to the binary log? Check with @code{SHOW MASTER STATUS}.
If it is, @code{Position} will be non-zero. If not, verify that you have
given the master @code{log-bin} option and have set @code{server-id}.
@item
Is the slave running? Check with @code{SHOW SLAVE STATUS}. The answer is found
in @code{Slave_running} column. If not, verify slave options and check the
error log for messages.
@item
If the slave is running, did it establish connection with the master? Do
@code{SHOW PROCESSLIST}, find the thread with @code{system user} value in
@code{User} column and @code{none} in the @code{Host} column, and check the
@code{State} column. If it says @code{connecting to master}, verify the
privileges for the replication user on the master, master host name, your
DNS setup, whether the master is actually running, whether it is reachable
from the slave, and if all that seems ok, read the error logs.
@item
If the slave was running, but then stopped, look at SHOW SLAVE STATUS
output andcheck the error logs. It usually
happens when some query that succeeded on the master fails on the slave. This
should never happen if you have taken a proper snapshot of the master, and
never modify the data on the slave outside of the slave thread. If it does,
it is a bug, read below on how to report it.
@item
If a query on that succeeded on the master refuses to run on the slave, and
a full database resync ( the proper thing to do ) does not seem feasible,
try the following:
@itemize bullet
@item
First see if there is some stray record in the way. Understand how it got
there, then delete it and run @code{SLAVE START}
@item
If the above does not work or does not apply, try to understand if it would
be safe to make the update manually ( if needed) and then ignore the next
query from the master.
@item
If you have decided you can skip the next query, do
@code{SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START;} to skip a query that
does not use auto_increment, last_insert_id or timestamp, or
@code{SET SQL_SLAVE_SKIP_COUNTER=2; SLAVE START;} otherwise
@item
If you are sure the slave started out perfectly in sync with the master,
and no one has updated the tables involved outside of slave thread,
report the bug, so
you will not have to do the above tricks again.
@end itemize
@item
Make sure you are not running into an old bug by upgrading to the most recent
version.
@item
If all else fails, read the error logs. If they are big,
@code{grep -i slave /path/to/your-log.err} on the slave. There is no
generic pattern to search for on the master, as the only errors it logs
are general system errors - if it can, it will send the error to the slave
when things go wrong.
@end itemize
When you have determined that there is no user error involved, and replication
still either does not work at all or is unstable, it is time to start working
on a bug report. We need to get as much info as possible from you to be able
to track down the bug. Please do spend some time and effort preparing a good
bug report. Ideally, we would like to have a test case in the format found in
@code{mysql-test/t/rpl*} directory of the source tree. If you submit a test
case like that, you can expect a patch within a day or two in most cases,
although, of course, you mileage may vary depending on a number of factors.
Second best option is a just program with easily configurable connection
arguments for the master and the slave that will demonstrate the problem on our
systems. You can write one in Perl or in C, depending on which language you
know better.
If you have one of the above ways to demonstrate the bug, use
@code{mysqlbug} to prepare a bug report and send it to
@email{bugs@@lists.mysql.com}. If you have a phantom - a problem that
does occur but you cannot duplicate "at will":
@itemize @bullet
@item
Verify that there is no user error involved. For example, if you update the
slave outside of the slave thread, the data will be out of sync, and you can
have unique key violations on updates, in which case the slave thread will
stop and wait for you to clean up the tables manually to bring them in sync.
@item
Run slave with @code{log-slave-updates} and @code{log-bin} - this will keep
a log of all updates on the slave.
@item
Save all evidence before reseting the replication. If we have no or only
sketchy information, it would take us a while to track down the problem. The
evidence you should collect is:
@itemize @bullet
@item
All binary logs on the master
@item
All binary log on the slave
@item
The output of @code{SHOW MASTER STATUS} on the master at the time
you have discovered the problem
@item
The output of @code{SHOW SLAVE STATUS} on the master at the time
you have discovered the problem
@item
Error logs on the master and on the slave
@end itemize
@item
Use @code{mysqlbinlog} to examine the binary logs. The following should
be helpful
to find the trouble query, for example:
@example
mysqlbinlog -j pos_from_slave_status /path/to/log_from_slave_status | head
@end example
@end itemize
Once you have collected the evidence on the phantom problem, try hard to
isolate it into a separate test case first. Then report the problem to
@email{bugs@@lists.mysql.com} with as much info as possible.
@cindex performance, maximizing
@cindex optimization
@node Performance, MySQL Benchmarks, Replication, Top
@chapter Getting Maximum Performance from MySQL
Optimization is a complicated task because it ultimately requires
understanding of the whole system. While it may be possible to do some
local optimizations with small knowledge of your system/application, the
more optimal you want your system to become the more you will have to
know about it.
So this chapter will try to explain and give some examples of different
ways to optimize @strong{MySQL}. But remember that there are always some
(increasingly harder) additional ways to make the system even faster.
@menu
* Optimize Basics:: Optimization overview
* System:: System/Compile time and startup parameter tuning
* Data size:: Get your data as small as possible
* MySQL indexes:: How @strong{MySQL} uses indexes
* Query Speed:: Speed of queries that access or update data
* Tips:: Other optimization tips
* Benchmarks:: Using your own benchmarks
* Design:: Design choices
* Design Limitations:: MySQL design limitations/tradeoffs
* Portability:: Portability
* Internal use:: What have we used MySQL for?
@end menu
@node Optimize Basics, System, Performance, Performance
@section Optimization Overview
The most important part for getting a system fast is of course the basic
design. You also need to know what kinds of things your system will be
doing, and what your bottlenecks are.
The most common bottlenecks are:
@itemize @bullet
@item Disk seeks.
It takes time for the disk to find a piece of data. With modern disks in
1999, the mean time for this is usually lower than 10ms, so we can in
theory do about 1000 seeks a second. This time improves slowly with new
disks and is very hard to optimize for a single table. The way to
optimize this is to spread the data on more than one disk.
@item Disk reading/writing.
When the disk is at the correct position we need to read the data. With
modern disks in 1999, one disk delivers something like 10-20Mb/s. This
is easier to optimize than seeks because you can read in parallel from
multiple disks.
@item CPU cycles.
When we have the data in main memory (or if it already were
there) we need to process it to get to our result. Having small
tables compared to the memory is the most common limiting
factor. But then, with small tables speed is usually not the problem.
@item Memory bandwidth.
When the CPU needs more data than can fit in the CPU cache the main
memory bandwidth becomes a bottleneck. This is an uncommon bottleneck
for most systems, but one should be aware of it.
@end itemize
@cindex compiling, optimizing
@cindex system optimization
@cindex startup parameters, tuning
@node System, Data size, Optimize Basics, Performance
@section System/Compile Time and Startup Parameter Tuning
We start with the system level things since some of these decisions have
to be made very early. In other cases a fast look at this part may
suffice because it not that important for the big gains. However, it is always
nice to have a feeling about how much one could gain by changing things
at this level.
The default OS to use is really important! To get the most use of
multiple CPU machines one should use Solaris (because the threads works
really nice) or Linux (because the 2.2 kernel has really good SMP
support). Also on 32-bit machines Linux has a 2G file size limit by
default. Hopefully this will be fixed soon when new filesystems are
released (XFS/Reiserfs). If you have a desperate need for files bigger
than 2G on Linux-intel 32 bit, you should get the LFS patch for the ext2
file system.
Because we have not run @strong{MySQL} in production on that many platforms, we
advice you to test your intended platform before choosing it, if possible.
@cindex locking
Other tips:
@itemize @bullet
@item
If you have enough RAM, you could remove all swap devices. Some
operating systems will use a swap device in some contexts even if you
have free memory.
@item
Use the @code{--skip-locking} @strong{MySQL} option to avoid external
locking. Note that this will not impact @strong{MySQL}'s functionality as
long as you only run one server. Just remember to take down the server (or
lock relevant parts) before you run @code{myisamchk}. On some system
this switch is mandatory because the external locking does not work in any
case.
The @code{--skip-locking} option is on by default when compiling with
MIT-pthreads, because @code{flock()} isn't fully supported by
MIT-pthreads on all platforms. It's also on default for Linux
as Linux file locking are not yet safe.
The only case when you can't use @code{--skip-locking} is if you run
multiple @strong{MySQL} @emph{servers} (not clients) on the same data,
or run @code{myisamchk} on the table without first flushing and locking
the @code{mysqld} server tables first.
You can still use @code{LOCK TABLES}/@code{UNLOCK TABLES} even if you
are using @code{--skip-locking}
@end itemize
@menu
* Compile and link options:: How compiling and linking affects the speed of MySQL
* Disk issues:: Disk issues
* Server parameters:: Tuning server parameters
* Table cache:: How MySQL opens and closes tables
* Creating many tables:: Drawbacks of creating large numbers of tables in the same database
* Open tables:: Why so many open tables?
* Memory use:: How MySQL uses memory
* Internal locking:: How MySQL locks tables
* Table locking:: Table locking issues
* DNS::
@end menu
@node Compile and link options, Disk issues, System, System
@subsection How Compiling and Linking Affects the Speed of MySQL
Most of the following tests are done on Linux with the
@strong{MySQL} benchmarks, but they should give some indication for
other operating systems and workloads.
@cindex linking, speed
@cindex compiling, speed
@cindex speed, compiling
@cindex speed, linking
You get the fastest executable when you link with @code{-static}.
On Linux, you will get the fastest code when compiling with @code{pgcc}
and @code{-O6}. To compile @file{sql_yacc.cc} with these options, you
need about 200M memory because @code{gcc/pgcc} needs a lot of memory to
make all functions inline. You should also set @code{CXX=gcc} when
configuring @strong{MySQL} to avoid inclusion of the @code{libstdc++}
library (it is not needed). Note that with some versions of @code{pgcc},
the resulting code will only run on true Pentium processors, even if you
use the compiler option that you want the resulting code to be working on
all x586 type processors (like AMD).
By just using a better compiler and/or better compiler options you can
get a 10-30 % speed increase in your application. This is particularly
important if you compile the SQL server yourself!
We have tested both the Cygnus CodeFusion and Fujitsu compilers, but
when we tested them, neither was sufficiently bug free to allow
@strong{MySQL} to be compiled with optimizations on.
When you compile @strong{MySQL} you should only include support for the
character sets that you are going to use. (Option @code{--with-charset=xxx}).
The standard @strong{MySQL} binary distributions are compiled with support
for all character sets.
Here is a list of some mesurements that we have done:
@itemize @bullet
@item
If you use @code{pgcc} and compile everything with @code{-O6}, the
@code{mysqld} server is 1% faster than with @code{gcc} 2.95.2.
@item
If you link dynamically (without @code{-static}), the result is 13%
slower on Linux. Note that you still can use a dynamic linked
@strong{MySQL} library. It is only the server that is critical for
performance.
@item
If you connect using TCP/IP rather than Unix sockets, the result is 7.5%
slower on the same computer. (If you are connection to @code{localhost},
@strong{MySQL} will, by default, use sockets).
@item
If you compile with @code{--with-debug=full}, then you will loose 20 %
for most queries, but some queries may take substantially longer (The
@strong{MySQL} benchmarks ran 35 % slower)
If you use @code{--with-debug}, then you will only loose 15 %.
@item
On a Sun SPARCstation 20, SunPro C++ 4.2 is 5 % faster than @code{gcc} 2.95.2.
@item
Compiling with @code{gcc} 2.95.2 for ultrasparc with the option
@code{-mcpu=v8 -Wa,-xarch=v8plusa} gives 4 % more performance.
@item
On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native
threads on a single processor. With more load/CPUs the difference should
get bigger.
@item
Running with @code{--log-bin} makes @strong{[MySQL} 1 % slower.
@item
Compiling without frame pointers @code{-fomit-frame-pointer} with gcc makes
MySQL 1 % faster.
@end itemize
The @strong{MySQL}-Linux distribution provided by MySQL AB used to be
compiled with @code{pgcc}, but we had to go back to regular gcc because
of a bug in @code{pgcc} that would generate the code that does not run
on AMD. We will continue using gcc until that bug is resolved. In the
meantime, if you have a non-AMD machine, you can get a faster binary by
compiling with @code{pgcc}. The standard MySqL Linux binary is linked
statically to get it faster and more portable.
@cindex disk issues
@cindex performance, disk issues
@node Disk issues, Server parameters, Compile and link options, System
@subsection Disk Issues
@itemize @bullet
@item
As mentioned before, disks seeks are a big performance bottleneck. This
problems gets more and more apparent when the data starts to grow so
large that effective caching becomes impossible. For large databases,
where you access data more or less randomly, you can be sure that you
will need at least one disk seek to read and a couple of disk seeks to
write things. To minimize this problem, use disks with low seek times.
@item
Increase the number of available disk spindles (and thereby reduce
the seek overhead) by either symlink files to different disks or striping
the disks.
@table @strong
@item Using symbolic links
This means that you symlink the index and/or data file(s) from the
normal data directory to another disk (that may also be striped). This
makes both the seek and read times better (if the disks are not used for
other things). @xref{Symbolic links}.
@cindex striping, defined
@item Striping
Striping means that you have many disks and put the first block on the
first disk, the second block on the second disk, and the Nth on the
(N mod number_of_disks) disk, and so on. This means if your normal data
size is less than the stripe size (or perfectly aligned) you will get
much better performance. Note that striping is very dependent on the OS
and stripe-size. So benchmark your application with different
stripe-sizes. @xref{Benchmarks}.
Note that the speed difference for striping is @strong{very} dependent
on the parameters. Depending on how you set the striping parameters and
number of disks you may get a difference in orders of magnitude. Note that
you have to choose to optimize for random or sequential access.
@end table
@item
For reliability you may want to use RAID 0+1 (striping + mirroring), but
in this case you will need 2*N drives to hold N drives of data. This is
probably the best option if you have the money for it! You may, however,
also have to invest in some volume-management software to handle it
efficiently.
@item
A good option is to have semi-important data (that can be regenerated)
on RAID 0 disk while storing really important data (like host information
and logs) on a RAID 0+1 or RAID N disk. RAID N can be a problem if you
have many writes because of the time to update the parity bits.
@item
You may also set the parameters for the file system that the database
uses. One easy change is to mount the file system with the noatime
option. That makes it skip the updating of the last access time in the
inode and by this will avoid some disk seeks.
@item
On Linux, you can get much more performance (up to 100 % under load is
not uncommon) by using hdpram to configure your disk's interface! The
following should be quite good hdparm options for @strong{MySQL} (and
probably many other applications):
@example
hdparm -m 16 -d 1
@end example
Note that the performance/reliability when using the above depends on
your hardware, so we strongly suggest that you test your system
throughly after using @code{hdparm}! Please consult the @code{hdparm}
man page for more information! If @code{hdparm} is not used wisely,
filesystem corruption may result. Backup everything before experimenting!
@item
On many operating systems you can mount the disks with the 'async' flag to set the file
system to be updated asynchronously. If your computer is reasonable stable,
this should give you more performance without sacrificing too much reliability.
(This flag is on by default on Linux.)
@item
If you don't need to know when a file was last accessed (which is not
really useful on a databasa server), you can mount your file systems
with the noatime flag.
@end itemize
@menu
* Symbolic links:: Using symbolic links for databases and tables
@end menu
@cindex symbolic links
@cindex links, symbolic
@cindex databases, symbolic links
@cindex tables, symbolic links
@node Symbolic links, , Disk issues, Disk issues
@subsubsection Using Symbolic Links for Databases and Tables
You can move tables and databases from the database directory to other
locations and replace them with symbolic links to the new locations.
You might want to do this, for example, to move a database to a file
system with more free space.
If @strong{MySQL} notices that a table is symbolically linked, it will
resolve the symlink and use the table it points to instead. This works
on all systems that support the @code{realpath()} call (at least Linux
and Solaris support @code{realpath()})! On systems that don't support
@code{realpath()}, you should not access the table through the real path
and through the symlink at the same time! If you do, the table will be
inconsistent after any update.
@strong{MySQL} doesn't that you link one directory to multiple
databases. Replacing a database directory with a symbolic link will
work fine as long as you don't make a symbolic link between databases.
Suppose you have a database @code{db1} under the @strong{MySQL} data
directory, and then make a symlink @code{db2} that points to @code{db1}:
@example
shell> cd /path/to/datadir
shell> ln -s db1 db2
@end example
Now, for any table @code{tbl_a} in @code{db1}, there also appears to be
a table @code{tbl_a} in @code{db2}. If one thread updates @code{db1.tbl_a}
and another thread updates @code{db2.tbl_a}, there will be problems.
If you really need this, you must change the following code in
@file{mysys/mf_format.c}:
@example
if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))
@end example
to
@example
if (1)
@end example
On Windows you can use internal symbolic links to directories by compiling
@strong{MySQL} with @code{-DUSE_SYMDIR}. This allows you to put different
databases on different disks. @xref{Windows symbolic links}.
@cindex parameters, server
@cindex @code{mysqld} server, buffer sizes
@cindex buffer sizes, @code{mysqld} server
@cindex startup parameters
@node Server parameters, Table cache, Disk issues, System
@subsection Tuning Server Parameters
You can get the default buffer sizes used by the @code{mysqld} server
with this command:
@example
shell> mysqld --help
@end example
@cindex @code{mysqld} options
@cindex variables, @code{mysqld}
This command produces a list of all @code{mysqld} options and configurable
variables. The output includes the default values and looks something
like this:
@example
Possible variables for option --set-variable (-O) are:
back_log current value: 5
bdb_cache_size current value: 1048540
binlog_cache_size current_value: 32768
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 1048540
lower_case_table_names current value: 0
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_binlog_cache_size current_value: 4294967295
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_write_lock_count current value: 4294967295
myisam_sort_buffer_size current value: 8388608
net_buffer_length current value: 16384
net_retry_count current value: 10
net_read_timeout current value: 30
net_write_timeout current value: 60
query_buffer_size current value: 0
record_buffer current value: 131072
slow_launch_time current value: 2
sort_buffer current value: 2097116
table_cache current value: 64
thread_concurrency current value: 10
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800
@end example
If there is a @code{mysqld} server currently running, you can see what
values it actually is using for the variables by executing this command:
@example
shell> mysqladmin variables
@end example
You can find a full description for all variables in the @code{SHOW VARIABLES}
section in this manual. @xref{SHOW VARIABLES}.
You can also see some statistics from a running server by issuing the command
@code{SHOW STATUS}. @xref{SHOW STATUS}.
@strong{MySQL} uses algorithms that are very scalable, so you can usually
run with very little memory. If you, however, give @strong{MySQL} more
memory, you will normally also get better performance.
When tuning a @strong{MySQL} server, the two most important variables to use
are @code{key_buffer_size} and @code{table_cache}. You should first feel
confident that you have these right before trying to change any of the
other variables.
If you have much memory (>=256M) and many tables and want maximum performance
with a moderate number of clients, you should use something like this:
@example
shell> safe_mysqld -O key_buffer=64M -O table_cache=256
-O sort_buffer=4M -O record_buffer=1M &
@end example
If you have only 128M and only a few tables, but you still do a lot of
sorting, you can use something like:
@example
shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M
@end example
If you have little memory and lots of connections, use something like this:
@example
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k
-O record_buffer=100k &
@end example
or even:
@example
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k
-O table_cache=32 -O record_buffer=8k -O net_buffer=1K &
@end example
When you have installed @strong{MySQL}, the @file{support-files} directory will
contain some different @code{my.cnf} example files, @file{my-huge.cnf},
@file{my-large.cnf}, @file{my-medium.cnf}, and @file{my-small.cnf}, you can
use as a base to optimize your system.
If there are very many connections, ``swapping problems'' may occur unless
@code{mysqld} has been configured to use very little memory for each
connection. @code{mysqld} performs better if you have enough memory for all
connections, of course.
Note that if you change an option to @code{mysqld}, it remains in effect only
for that instance of the server.
To see the effects of a parameter change, do something like this:
@example
shell> mysqld -O key_buffer=32m --help
@end example
Make sure that the @code{--help} option is last; otherwise, the effect of any
options listed after it on the command line will not be reflected in the
output.
@cindex tables, opening
@cindex tables, closing
@cindex opening, tables
@cindex closing, tables
@cindex table cache
@findex table_cache
@node Table cache, Creating many tables, Server parameters, System
@subsection How MySQL Opens and Closes Tables
@code{table_cache}, @code{max_connections}, and @code{max_tmp_tables}
affect the maximum number of files the server keeps open. If you
increase one or both of these values, you may run up against a limit
imposed by your operating system on the per-process number of open file
descriptors. However, you can increase the limit on many systems.
Consult your OS documentation to find out how to do this, because the
method for changing the limit varies widely from system to system.
@code{table_cache} is related to @code{max_connections}. For example,
for 200 concurrent running connections, you should have a table cache of
at least @code{200 * n}, where @code{n} is the maximum number of tables
in a join.
The cache of open tables can grow to a maximum of @code{table_cache}
(default 64; this can be changed with the @code{-O table_cache=#}
option to @code{mysqld}). A table is never closed, except when the
cache is full and another thread tries to open a table or if you use
@code{mysqladmin refresh} or @code{mysqladmin flush-tables}.
When the table cache fills up, the server uses the following procedure
to locate a cache entry to use:
@itemize @bullet
@item
Tables that are not currently in use are released, in least-recently-used
order.
@item
If the cache is full and no tables can be released, but a new table needs to
be opened, the cache is temporarily extended as necessary.
@item
If the cache is in a temporarily-extended state and a table goes from in-use
to not-in-use state, the table is closed and released from the cache.
@end itemize
A table is opened for each concurrent access. This means that
if you have two threads accessing the same table or access the table
twice in the same query (with @code{AS}) the table needs to be opened twice.
The first open of any table takes two file descriptors; each additional
use of the table takes only one file descriptor. The extra descriptor
for the first open is used for the index file; this descriptor is shared
among all threads.
You can check if your table cache is too small by checking the mysqld
variable @code{opened_tables}. If this is quite big, even if you
haven't done a lot of @code{FLUSH TABLES}, you should increase your table
cache. @xref{SHOW STATUS}.
@cindex tables, too many
@node Creating many tables, Open tables, Table cache, System
@subsection Drawbacks to Creating Large Numbers of Tables in the Same Database
If you have many files in a directory, open, close, and create operations will
be slow. If you execute @code{SELECT} statements on many different tables,
there will be a little overhead when the table cache is full, because for
every table that has to be opened, another must be closed. You can reduce
this overhead by making the table cache larger.
@cindex tables, open
@cindex open tables
@node Open tables, Memory use, Creating many tables, System
@subsection Why So Many Open tables?
When you run @code{mysqladmin status}, you'll see something like this:
@example
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
@end example
This can be somewhat perplexing if you only have 6 tables.
@strong{MySQL} is multithreaded, so it may have many queries on the same
table simultaneously. To minimize the problem with two threads having
different states on the same file, the table is opened independently by
each concurrent thread. This takes some memory and one extra file
descriptor for the data file. The index file descriptor is shared
between all threads.
@cindex memory use
@node Memory use, Internal locking, Open tables, System
@subsection How MySQL Uses Memory
The list below indicates some of the ways that the @code{mysqld} server
uses memory. Where applicable, the name of the server variable relevant
to the memory use is given:
@itemize @bullet
@item
The key buffer (variable @code{key_buffer_size}) is shared by all
threads; Other buffers used by the server are allocated as
needed. @xref{Server parameters}.
@item
Each connection uses some thread-specific space: A stack (default 64K,
variable @code{thread_stack}), a connection buffer (variable
@code{net_buffer_length}), and a result buffer (variable
@code{net_buffer_length}). The connection buffer and result buffer are
dynamically enlarged up to @code{max_allowed_packet} when needed. When
a query is running, a copy of the current query string is also allocated.
@item
All threads share the same base memory.
@item
Only the compressed ISAM / MyISAM tables are memory mapped. This is
because the 32-bit memory space of 4GB is not large enough for most
big tables. When systems with a 64-bit address space become more
common we may add general support for memory mapping.
@item
Each request doing a sequential scan over a table allocates a read buffer
(variable @code{record_buffer}).
@item
All joins are done in one pass, and most joins can be done without even
using a temporary table. Most temporary tables are memory-based (HEAP)
tables. Temporary tables with a big record length (calculated as the
sum of all column lengths) or that contain @code{BLOB} columns are
stored on disk.
One problem in @strong{MySQL} versions before Version 3.23.2 is that if a HEAP table
exceeds the size of @code{tmp_table_size}, you get the error @code{The
table tbl_name is full}. In newer versions this is handled by
automatically changing the in-memory (HEAP) table to a disk-based
(MyISAM) table as necessary. To work around this problem, you can
increase the temporary table size by setting the @code{tmp_table_size}
option to @code{mysqld}, or by setting the SQL option
@code{SQL_BIG_TABLES} in the client program. @xref{SET OPTION, ,
@code{SET OPTION}}. In @strong{MySQL} Version 3.20, the maximum size of the
temporary table was @code{record_buffer*16}, so if you are using this
version, you have to increase the value of @code{record_buffer}. You can
also start @code{mysqld} with the @code{--big-tables} option to always
store temporary tables on disk. However, this will affect the speed of
many complicated queries.
@item
Most requests doing a sort allocates a sort buffer and 0-2 temporary
files depending on the result set size. @xref{Temporary files}.
@item
Almost all parsing and calculating is done in a local memory store. No
memory overhead is needed for small items and the normal slow memory
allocation and freeing is avoided. Memory is allocated only for
unexpectedly large strings (this is done with @code{malloc()} and
@code{free()}).
@item
Each index file is opened once and the data file is opened once for each
concurrently running thread. For each concurrent thread, a table structure,
column structures for each column, and a buffer of size @code{3 * n} is
allocated (where @code{n} is the maximum row length, not counting @code{BLOB}
columns). A @code{BLOB} uses 5 to 8 bytes plus the length of the @code{BLOB}
data. The @code{ISAM}/@code{MyISAM} table handlers will use one extra row
buffer for internal usage.
@item
For each table having @code{BLOB} columns, a buffer is enlarged dynamically
to read in larger @code{BLOB} values. If you scan a table, a buffer as large
as the largest @code{BLOB} value is allocated.
@item
Table handlers for all in-use tables are saved in a cache and managed as a
FIFO. Normally the cache has 64 entries. If a table has been used by two
running threads at the same time, the cache contains two entries for the
table. @xref{Table cache}.
@item
A @code{mysqladmin flush-tables} command closes all tables that are not in
use and marks all in-use tables to be closed when the currently executing
thread finishes. This will effectively free most in-use memory.
@end itemize
@code{ps} and other system status programs may report that @code{mysqld}
uses a lot of memory. This may be caused by thread-stacks on different
memory addresses. For example, the Solaris version of @code{ps} counts
the unused memory between stacks as used memory. You can verify this by
checking available swap with @code{swap -s}. We have tested
@code{mysqld} with commercial memory-leakage detectors, so there should
be no memory leaks.
@cindex internal locking
@cindex locking, tables
@cindex tables, locking
@node Internal locking, Table locking, Memory use, System
@subsection How MySQL Locks Tables
You can find a discussion about different locking methods in the appendix.
@xref{Locking methods}.
All locking in @strong{MySQL} is deadlock-free. This is managed by always
requesting all needed locks at once at the beginning of a query and always
locking the tables in the same order.
The locking method @strong{MySQL} uses for @code{WRITE} locks works as follows:
@itemize @bullet
@item
If there are no locks on the table, put a write lock on it.
@item
Otherwise, put the lock request in the write lock queue.
@end itemize
The locking method @strong{MySQL} uses for @code{READ} locks works as follows:
@itemize @bullet
@item
If there are no write locks on the table, put a read lock on it.
@item
Otherwise, put the lock request in the read lock queue.
@end itemize
When a lock is released, the lock is made available to the threads
in the write lock queue, then to the threads in the read lock queue.
This means that if you have many updates on a table, @code{SELECT}
statements will wait until there are no more updates.
To work around this for the case where you want to do many @code{INSERT} and
@code{SELECT} operations on a table, you can insert rows in a temporary
table and update the real table with the records from the temporary table
once in a while.
This can be done with the following code:
@example
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;
@end example
You can use the @code{LOW_PRIORITY} options with @code{INSERT} if you
want to prioritize retrieval in some specific cases. @xref{INSERT, ,
@code{INSERT}}.
You could also change the locking code in @file{mysys/thr_lock.c} to use a
single queue. In this case, write locks and read locks would have the same
priority, which might help some applications.
@cindex problems, table locking
@node Table locking, DNS, Internal locking, System
@subsection Table Locking Issues
The table locking code in @strong{MySQL} is deadlock free.
@strong{MySQL} uses table locking (instead of row locking or column
locking) on all table types, except @code{BDB} tables, to achieve a very
high lock speed. For large tables, table locking is MUCH better than
row locking for most applications, but there are, of course, some
pitfalls.
For @code{BDB} tables, @strong{MySQL} only uses table locking if you
explicitely lock the table with @code{LOCK TABLES} or execute a command that
will modify every row in the table, like @code{ALTER TABLE}.
In @strong{MySQL} Version 3.23.7 and above, you can insert rows into
@code{MyISAM} tables at the same time other threads are reading from
the table. Note that currently this only works if there are no holes after
deleted rows in the table at the time the insert is made.
Table locking enables many threads to read from a table at the same
time, but if a thread wants to write to a table, it must first get
exclusive access. During the update, all other threads that want to
access this particular table will wait until the update is ready.
As updates on tables normally are considered to be more important than
@code{SELECT}, all statements that update a table have higher priority
than statements that retrieve information from a table. This should
ensure that updates are not 'starved' because one issues a lot of heavy
queries against a specific table. (You can change this by using
LOW_PRIORITY with the statement that does the update or
@code{HIGH_PRIORITY} with the @code{SELECT} statement.)
Starting from @strong{MySQL} Version 3.23.7 one can use the
@code{max_write_lock_count} variable to force @strong{MySQL} to
temporary give all @code{SELECT} statements, that wait for a table, a
higher priority after a specific number of inserts on a table.
Table locking is, however, not very good under the following senario:
@itemize @bullet
@item
A client issues a @code{SELECT} that takes a long time to run.
@item
Another client then issues an @code{UPDATE} on a used table. This client
will wait until the @code{SELECT} is finished.
@item
Another client issues another @code{SELECT} statement on the same table. As
@code{UPDATE} has higher priority than @code{SELECT}, this @code{SELECT}
will wait for the @code{UPDATE} to finish. It will also wait for the first
@code{SELECT} to finish!
@item
A thread is waiting for something like @code{full disk}, in which case all
threads that wants to access the problem table will also be put in a waiting
state until more disk space is made available.
@end itemize
Some possible solutions to this problem are:
@itemize @bullet
@item
Try to get the @code{SELECT} statements to run faster. You may have to create
some summary tables to do this.
@item
Start @code{mysqld} with @code{--low-priority-updates}. This will give
all statements that update (modify) a table lower priority than a @code{SELECT}
statement. In this case the last @code{SELECT} statement in the previous
scenario would execute before the @code{INSERT} statement.
@item
You can give a specific @code{INSERT}, @code{UPDATE}, or @code{DELETE}
statement lower priority with the @code{LOW_PRIORITY} attribute.
@item
Start @code{mysqld} with a low value for @strong{max_write_lock_count} to give
@code{READ} locks after a certain number of @code{WRITE} locks.
@item
You can specify that all updates from a specific thread should be done with
low priority by using the SQL command: @code{SET SQL_LOW_PRIORITY_UPDATES=1}.
@xref{SET OPTION, , @code{SET OPTION}}.
@item
You can specify that a specific @code{SELECT} is very important with the
@code{HIGH_PRIORITY} attribute. @xref{SELECT, , @code{SELECT}}.
@item
If you have problems with @code{INSERT} combined with @code{SELECT},
switch to use the new @code{MyISAM} tables as these support concurrent
@code{SELECT}s and @code{INSERT}s.
@item
If you mainly mix @code{INSERT} and @code{SELECT} statements, the
@code{DELAYED} attribute to @code{INSERT} will probably solve your problems.
@xref{INSERT, , @code{INSERT}}.
@item
If you have problems with @code{SELECT} and @code{DELETE}, the @code{LIMIT}
option to @code{DELETE} may help. @xref{DELETE, , @code{DELETE}}.
@end itemize
@cindex DNS
@cindex hostname caching
@node DNS, , Table locking, System
@subsection How MySQL uses DNS
When a new threads connects to @code{mysqld}, @code{mysqld} will span a
new thread to handle the request. This thread will first check if the
hostname is in the hostname cache. If not the thread will call
@code{gethostbyaddr_r()} and @code{gethostbyname_r()} to resolve the
hostname.
If the operating system doesn't support the above thread-safe calls, the
thread will lock a mutex and call @code{gethostbyaddr()} and
@code{gethostbyname()} instead. Note that in this case no other thread
can resolve other hostnames that is not in the hostname cache until the
first thread is ready.
You can disable DNS host lookup by starting @code{mysqld} with
@code{--skip-name-resolve}. In this case you can however only use IP
names in the @strong{MySQL} privilege tables.
If you have a very slow DNS and many hosts, you can get more performance by
either disabling DNS lookop with @code{--skip-name-resolve} or by
increasing the @code{HOST_CACHE_SIZE} define (default: 128) and recompile
@code{mysqld}.
You can disable the hostname cache with @code{--skip-host-cache}. You
can clear the hostname cache with @code{FLUSH HOSTS} or @code{mysqladmin
flush-hosts}.
If you don't want to allow connections over @code{TCP/IP}, you can do this
by starting mysqld with @code{--skip-networking}.
@cindex data, size
@cindex reducing, data size
@cindex storage space, minimizing
@cindex tables, improving performance
@cindex performance, improving
@node Data size, MySQL indexes, System, Performance
@section Get Your Data as Small as Possible
One of the most basic optimization is to get your data (and indexes) to
take as little space on the disk (and in memory) as possible. This can
give huge improvements because disk reads are faster and normally less
main memory will be used. Indexing also takes less resources if
done on smaller columns.
@strong{MySQL} supports a lot of different table types and row formats.
Choosing the right table format may give you a big performance gain.
@xref{Table types}.
You can get better performance on a table and minimize storage space
using the techniques listed below:
@itemize @bullet
@item
Use the most efficient (smallest) types possible. @strong{MySQL} has
many specialized types that save disk space and memory.
@item
Use the smaller integer types if possible to get smaller tables. For
example, @code{MEDIUMINT} is often better than @code{INT}.
@item
Declare columns to be @code{NOT NULL} if possible. It makes everything
faster and you save one bit per column. Note that if you really need
@code{NULL} in your application you should definitely use it. Just avoid
having it on all columns by default.
@item
If you don't have any variable-length columns (@code{VARCHAR},
@code{TEXT}, or @code{BLOB} columns), a fixed-size record format is
used. This is faster but unfortunately may waste some space.
@xref{MyISAM table formats}.
@item
The primary index of a table should be as short as possible. This makes
identification of one row easy and efficient.
@item
For each table, you have to decide which storage/index method to
use. @xref{Table types}.
@item
Only create the indexes that you really need. Indexes are good for
retrieval but bad when you need to store things fast. If you mostly
access a table by searching on a combination of columns, make an index
on them. The first index part should be the most used column. If you are
ALWAYS using many columns, you should use the column with more duplicates
first to get better compression of the index.
@item
If it's very likely that a column has a unique prefix on the first number
of characters, it's better to only index this prefix. @strong{MySQL}
supports an index on a part of a character column. Shorter indexes are
faster not only because they take less disk space but also because they
will give you more hits in the index cache and thus fewer disk
seeks. @xref{Server parameters}.
@item
In some circumstances it can be beneficial to split into two a table that is
scanned very often. This is especially true if it is a dynamic
format table and it is possible to use a smaller static format table that
can be used to find the relevant rows when scanning the table.
@end itemize
@cindex indexes, uses for
@node MySQL indexes, Query Speed, Data size, Performance
@section How MySQL Uses Indexes
Indexes are used to find rows with a specific value of one column
fast. Without an index @strong{MySQL} has to start with the first record
and then read through the whole table until it finds the relevant
rows. The bigger the table, the more this costs. If the table has an index
for the colums in question, @strong{MySQL} can quickly get a position to
seek to in the middle of the data file without having to look at all the
data. If a table has 1000 rows, this is at least 100 times faster than
reading sequentially. Note that if you need to access almost all 1000
rows it is faster to read sequentially because we then avoid disk seeks.
All @strong{MySQL} indexes (@code{PRIMARY}, @code{UNIQUE}, and
@code{INDEX}) are stored in B-trees. Strings are automatically prefix-
and end-space compressed. @xref{CREATE INDEX, , @code{CREATE INDEX}}.
Indexes are used to:
@itemize @bullet
@item
Quickly find the rows that match a @code{WHERE} clause.
@item
Retrieve rows from other tables when performing joins.
@item
Find the @code{MAX()} or @code{MIN()} value for a specific indexed
column. This is optimized by a preprocessor that checks if you are
using @code{WHERE} key_part_# = constant on all key parts < N. In this case
@strong{MySQL} will do a single key lookup and replace the @code{MIN()}
expression with a constant. If all expressions are replaced with
constants, the query will return at once:
@example
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
@end example
@item
Sort or group a table if the sorting or grouping is done on a leftmost
prefix of a usable key (for example, @code{ORDER BY key_part_1,key_part_2 }). The
key is read in reverse order if all key parts are followed by @code{DESC}.
The index can also be used even if the @code{ORDER BY} doesn't match the index
exactly, as long as all the unused index parts and all the extra
are @code{ORDER BY} columns are constants in the @code{WHERE} clause. The
following queries will use the index to resolve the @code{ORDER BY} part:
@example
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3;
SELECT * FROM foo WHERE column=constant ORDER BY column, key_part1;
SELECT * FROM foo WHERE key_part1=const GROUP BY key_part2;
@end example
@item
In some cases a query can be optimized to retrieve values without
consulting the data file. If all used columns for some table are numeric
and form a leftmost prefix for some key, the values may be retrieved
from the index tree for greater speed:
@example
SELECT key_part3 FROM table_name WHERE key_part1=1
@end example
@end itemize
Suppose you issue the following @code{SELECT} statement:
@example
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
@end example
If a multiple-column index exists on @code{col1} and @code{col2}, the
appropriate rows can be fetched directly. If separate single-column
indexes exist on @code{col1} and @code{col2}, the optimizer tries to
find the most restrictive index by deciding which index will find fewer
rows and using that index to fetch the rows.
@cindex indexes, leftmost prefix of
@cindex leftmost prefix of indexes
If the table has a multiple-column index, any leftmost prefix of the
index can be used by the optimizer to find rows. For example, if you
have a three-column index on @code{(col1,col2,col3)}, you have indexed
search capabilities on @code{(col1)}, @code{(col1,col2)}, and
@code{(col1,col2,col3)}.
@strong{MySQL} can't use a partial index if the columns don't form a
leftmost prefix of the index. Suppose you have the @code{SELECT}
statements shown below:
@example
mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
@end example
If an index exists on @code{(col1,col2,col3)}, only the first query
shown above uses the index. The second and third queries do involve
indexed columns, but @code{(col2)} and @code{(col2,col3)} are not
leftmost prefixes of @code{(col1,col2,col3)}.
@findex LIKE, and indexes
@findex LIKE, and wildcards
@cindex indexes, and @code{LIKE}
@cindex wildcards, and @code{LIKE}
@strong{MySQL} also uses indexes for @code{LIKE} comparisons if the argument
to @code{LIKE} is a constant string that doesn't start with a wild-card
character. For example, the following @code{SELECT} statements use indexes:
@example
mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";
@end example
In the first statement, only rows with @code{"Patrick" <= key_col <
"Patricl"} are considered. In the second statement, only rows with
@code{"Pat" <= key_col < "Pau"} are considered.
The following @code{SELECT} statements will not use indexes:
@example
mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;
@end example
In the first statement, the @code{LIKE} value begins with a wild-card
character. In the second statement, the @code{LIKE} value is not a
constant.
@findex IS NULL, and indexes
@cindex indexes, and @code{IS NULL}
Searching using @code{column_name IS NULL} will use indexes if column_name
is an index.
@strong{MySQL} normally uses the index that finds the least number of rows. An
index is used for columns that you compare with the following operators:
@code{=}, @code{>}, @code{>=}, @code{<}, @code{<=}, @code{BETWEEN}, and a
@code{LIKE} with a non-wild-card prefix like @code{'something%'}.
Any index that doesn't span all @code{AND} levels in the @code{WHERE} clause
is not used to optimize the query. In other words: To be able to use an
index, a prefix of the index must be used in every @code{AND} group.
The following @code{WHERE} clauses use indexes:
@example
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
/* optimized like "index_part1='hello'" */
... WHERE index1=1 and index2=2 or index1=3 and index3=3;
/* Can use index on index1 but not on index2 or index 3 */
@end example
These @code{WHERE} clauses do @strong{NOT} use indexes:
@example
... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
... WHERE index=1 OR A=10 /* Index is not used in both AND parts */
... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */
@end example
Note that in some cases @strong{MySQL} will not use an index, even if one
would be available. Some of the cases where this happens are:
@itemize @bullet
@item
If the use of the index would require @strong{MySQL} to access more
than 30 % of the rows in the table. (In this case a table scan is
probably much faster, as this will require us to do much fewer seeks).
Note that if such a query uses @code{LIMIT} to only retrieve
part of the rows, @strong{MySQL} will use an index anyway, as it can
much more quickly find the few rows to return in the result.
@end itemize
@cindex queries, speed of
@cindex permission checks, effect on speed
@cindex speed, of queries
@node Query Speed, Tips, MySQL indexes, Performance
@section Speed of Queries that Access or Update Data
First, one thing that affects all queries: The more complex permission
system setup you have, the more overhead you get.
If you do not have any @code{GRANT} statements done, @strong{MySQL} will
optimize the permission checking somewhat. So if you have a very high
volume it may be worth the time to avoid grants. Otherwise more
permission check results in a larger overhead.
If your problem is with some explicit @strong{MySQL} function, you can
always time this in the @strong{MySQL} client:
@example
mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+