Concurrent Inserts
712
• Start
mysqld
with
--low-priority-updates
[414]
. For storage engines that use only table-
level locking (such as
MyISAM
,
MEMORY
, and
MERGE
), this gives all statements that update (modify)
a table lower priority than
SELECT
statements. In this case, the second
SELECT
statement in the
preceding scenario would execute before the
UPDATE
statement, and would not need to wait for the
first
SELECT
to finish.
• To specify that all updates issued in a specific connection should be done with low priority, set the
low_priority_updates
[465]
server system variable equal to 1.
• To give a specific
INSERT
,
UPDATE
, or
DELETE
statement lower priority, use the
LOW_PRIORITY
attribute.
• To give a specific
SELECT
statement higher priority, use the
HIGH_PRIORITY
attribute. See
Section 13.2.8, “
SELECT
Syntax”
.
• Start
mysqld
with a low value for the
max_write_lock_count
[473]
system variable to force
MySQL to temporarily elevate the priority of all
SELECT
statements that are waiting for a table after
a specific number of inserts to the table occur. This permits
READ
locks after a certain number of
WRITE
locks.
• If you have problems with
INSERT
combined with
SELECT
, consider switching to
MyISAM
tables,
which support concurrent
SELECT
and
INSERT
statements. (See
Section 8.7.3, “Concurrent
Inserts”
.)
• If you mix inserts and deletes on the same table,
INSERT DELAYED
may be of great help. See
Section 13.2.5.2, “
INSERT DELAYED
Syntax”
.
• If you have problems with mixed
SELECT
and
DELETE
statements, the
LIMIT
option to
DELETE
may
help. See
Section 13.2.2, “
DELETE
Syntax”
.
• Using
SQL_BUFFER_RESULT
with
SELECT
statements can help to make the duration of table locks
shorter. See
Section 13.2.8, “
SELECT
Syntax”
.
• You could change the locking code in
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.
Here are some tips concerning table locks in MySQL:
• Concurrent users are not a problem if you do not mix updates with selects that need to examine
many rows in the same table.
• You can use
LOCK TABLES
to increase speed, because many updates within a single lock is much
faster than updating without locks. Splitting table contents into separate tables may also help.
• If you encounter speed problems with table locks in MySQL, you may be able to improve
performance by converting some of your tables to
InnoDB
or
BDB
tables. See
Section 14.2, “The
InnoDB
Storage Engine”
, and
Section 14.5, “The
BDB
(
BerkeleyDB
) Storage Engine”
.
8.7.3. Concurrent Inserts
The
MyISAM
storage engine supports concurrent inserts to reduce contention between readers and
writers for a given table: If a
MyISAM
table has no holes in the data file (deleted rows in the middle), an
INSERT
statement can be executed to add rows to the end of the table at the same time that
SELECT
statements are reading rows from the table. If there are multiple
INSERT
statements, they are queued
and performed in sequence, concurrently with the
SELECT
statements. The results of a concurrent
INSERT
may not be visible immediately.
The
concurrent_insert
[445]
system variable can be set to modify the concurrent-insert
processing. By default, the variable is set to 1 and concurrent inserts are handled as just described.
If
concurrent_insert
[445]
is set to 0, concurrent inserts are disabled. If the variable is set to 2,
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
