MySQL 5.6 MyISAMKristian Köhntopp

MyISAM Facts

• Old ‘default engine’

• Very small files

• Can do FULLTEXT (badly), GIS (badly)

• Can do compound PK w/ auto_increment (bad idea)

• Table level locking (performance problem)

2

MyISAM disadvantages

• No transactions.

• No table recovery (uses “REPAIR TABLE”)

• Small footprint = inefficient updates, fragmentation.

3

MyISAM file structure4

.MYD file

RecordRecordRecordRecordRecordRecordRecorddeletedRecordRecorddeletedRecord

.MYI File

Index 1 Index 2

MyISAM file structure

• Records packed next to each other in data file.

• Delete → Gap.

• Gap → Next Insert, or Append.

• FIXED vs. DYNAMIC (vs. COMPRESSED)

• Fragmentation (String extension!)

5

MyISAM file structure

• Index: record number (FIXED), byte offset (DYNAMIC)

• Data pointer size variable

• myisam_data_pointer_size = 6 (256TB)

• ALTER TABLE t MIN_ROWS = n MAX_ROWS = m AVG_ROW_SIZE = p;

6

MyISAM update storm

• UPDATE t SET somestring = CONCAT(somestring, somestring)

• Split record = Seek.

• Move to end of table = all indices updated.

• Either way: fragmentation, seeks.

7

MyISAM file structure

• UPDATE t SET count = count + 1, and server crash?

• Some records updated. You do not know which.

• Recover from uncrashed slave, or have invalid data.

• Also, the table is marked as crashed.

8

Compressed MyISAM

• Close all filehandles: FLUSH TABLES

• Run myisampack to compress data file: invalid index.

• Run myisamchk to rebuild index.

• Data footprint approx. 50% smaller.

• Read-only.

9

MyISAM locking

• Table level locks: SHARED (read), EXCLUSIVE (write)

• Many concurrent SELECT statements, serialized write statements.

• Writes have precedence.

• Running statements have precedence.

10

MyISAM locking

• SELECT high_priority …

• Writes (INSERT, UPDATE, DELETE, …)

• SELECT …

• Low priority (UPDATE LOW_PRIORITY …)

11

MyISAM locking

• many SELECTs, some slow (4s runtime).

• foreach $i (@stmt) { $dbh->do($i) }

• INSERT blocks subsequent reads.

• INSERT blocked by running (slow) read.

• One insert every 4s.

12

Explicit locking

• LOCK TABLES t1 READ, t2 READ, t3 WRITE;

• All locks taken at once: Avoids deadlocks.

• Unable to touch unlocked tables during lock: Ensures correctness and completeness.

• UNLOCK TABLES (or disconnect)

13

Explicit locking

• How does explicit locking affect the MyISAM interlock scenario?

• Locking can actually make things faster.

• But will increase latency.

14

Tuning MyISAM

• MyISAM caches MYI data, but no MYD data.

• System must have sufficient buffer cache: mysqld size must be limited.

• key_buffer_size = …

• Ideally: Sum of all MYI files.

15

Tuning MyISAM

• key_cache_division_limit = 100

• The division point between the hot and warm sublists of the key cache buffer list (percent)

16

Tuning MyISAM

• After a server restart, the key cache is cold.

• LOAD INDEX INTO CACHE <indexname>, <indexname2>, …

• This preloads the cache w/ linear disk accesses.

• Much faster than normal warmup.

17

Tuning MyISAM

• Handling MyISAM index update storms:

• delayed_key_write = OFF|ON|ALL

• CREATE TABLE t (…) DELAY_KEY_WRITE = 1;

• Status variable Key_blocks_not_flushed

• Large number of unflushed blocks + crash = monster repair table

18

Tuning MyISAM

• myisam_recover_options = FORCE,BACKUP

• may not be enough in the face of delayed key writes

• myisam_repair_threads = 1

• used to be broken with > 1

19

Monitoring MyISAM

• Table_locks_immediate

• Table locks granted without waiting

• Table_locks_waited

• Table locks granted after wait

• 1% = dangerous, 3% = deadly

20

MyISAM maintenance

• SHOW TABLE STATUS - data_free.

• OPTIMIZE TABLE to reclaim data_free.

• This is very slow and X-locks.

• ANALYZE TABLE - optimizer statistics.

• Also very slow (in MyISAM).

21