Simultaneous reading and writing has to be controlled carefully so that readers don’t see inconsistent results. MyISAM allows concurrent inserts and reads under some conditions, and it lets you “schedule” some operations to try to block as little as possible.
Before we look at MyISAM’s concurrency settings, it’s important to understand how MyISAM deletes and inserts rows. Delete operations don’t rearrange the entire table; they just mark rows as deleted, leaving “holes” in the table. MyISAM prefers to fill the holes if it can, reusing the spaces for inserted rows. If there are no holes, it appends new rows to the end of the table.
Even though MyISAM has table-level locks, it can append new rows concurrently with reads. It does this by stopping the reads at the last row that existed when they began. This avoids inconsistent reads.
However, it is much more difficult to provide consistent reads when something is changing the middle of the table. MVCC is the most popular way to solve this problem: it lets readers read old versions of data while writers create new versions.
MyISAM doesn’t support MVCC, 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, so you may need to optimize your tables more frequently, depending on your workload.
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. This involves the familiar tradeoff: 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.
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 bottleneck 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 example, 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 machines than on a single-CPU machine, even when these are the only queries running.
Before we look at MyISAM’s concurrency settings, it’s important to understand how MyISAM deletes and inserts rows. Delete operations don’t rearrange the entire table; they just mark rows as deleted, leaving “holes” in the table. MyISAM prefers to fill the holes if it can, reusing the spaces for inserted rows. If there are no holes, it appends new rows to the end of the table.
Even though MyISAM has table-level locks, it can append new rows concurrently with reads. It does this by stopping the reads at the last row that existed when they began. This avoids inconsistent reads.
However, it is much more difficult to provide consistent reads when something is changing the middle of the table. MVCC is the most popular way to solve this problem: it lets readers read old versions of data while writers create new versions.
MyISAM doesn’t support MVCC, 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, so you may need to optimize your tables more frequently, depending on your workload.
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. This involves the familiar tradeoff: 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.
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 bottleneck 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 example, 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 machines than on a single-CPU machine, even when these are the only queries running.
Source of Information : OReIlly High Performance MySQL Second Edition
|
|
0 comments
Post a Comment