Databases Reference
In-Depth Information
MyISAM doesn't support MVCC as InnoDB does, so it doesn't support concurrent
inserts unless they go at the end of the table.
You can configure MyISAM's concurrent insert behavior with the
concurrent_insert
variable, which can have the following values:
0
MyISAM allows no concurrent inserts; every insert locks the table exclusively.
1
This is the default value. MyISAM allows concurrent inserts, as long as there are
no holes in the table.
2
This value is available in MySQL 5.0 and newer. It forces concurrent inserts to
append to the end of the table, even when there are holes. If there are no threads
reading from the table, MySQL will place the new rows in the holes. The table can
become more fragmented than usual with this setting.
You can also configure MySQL to delay some operations to a later time, when they can
be combined for greater efficiency. For instance, you can delay index writes with the
delay_key_write
variable, which we mentioned earlier in this chapter. This involves
the familiar trade-off: write the index right away (safe but expensive), or wait and hope
the power doesn't fail before the write happens (faster, but likely to cause massive index
corruption in the event of a crash because the index file will be very out of date).
You can also give
INSERT
,
REPLACE
,
DELETE
, and
UPDATE
queries lower priority than
SELECT
queries with the
low_priority_updates
option. This is equivalent to globally
applying the
LOW_PRIORITY
modifier to
UPDATE
queries. It's actually a very important
option when you use MyISAM; it lets you get decent concurrency for
SELECT
queries
that would otherwise starve in the presence of a very small number of queries getting
top priority for write locks.
Finally, even though InnoDB's scalability issues are more often talked about, MyISAM
has also had problems with mutexes for a long time. In MySQL 4.0 and earlier, a global
mutex protected any I/O to the key buffer, which caused scalability problems with
multiple CPUs and multiple disks. MySQL 4.1's key buffer code is improved and
doesn't have this problem anymore, but it still holds a mutex on each key buffer. This
is an issue when a thread copies key blocks from the key buffer into its local storage,
rather than reading from the disk. The disk bottleneck is gone, but there's still a bot-
tleneck when accessing data in the key buffer. You can sometimes work around this
problem with multiple key buffers, but this approach isn't always successful. For ex-
ample, there's no way to solve the problem when it involves only a single index. As a
result, concurrent
SELECT
queries can perform significantly worse on multi-CPU ma-
chines than on a single-CPU machine, even when these are the only queries running.
MariaDB offers segmented (partitioned) key buffers, which can help significantly when
you experience this problem.
