2

So I've launched the myisamchk utility with repair option on all tables within the database.

Smaller tables (1GB max) got fixed in no time, virtually seconds. However, my largest table (9GB) takes forever (20mb/min) to fix. My server is down because of that.

I've checked 'iotop -ao' and this is what i got:

IOTOP dump

Whats going on here? The read of 300 KB/S is VERY HIGH in this screenshot, i was aiming at capturing the super-fast write. Sometimes the read spikes to 5-10 MB/s, but for the rest of the time its within 0-50KB/s range.

The table has only several columns, 2 indexes (1) Long, (2) Varchar(72). Mysql 5.5.56, CentOS 6. Its doing repair by sort.

  • IS there a way to move the question to stackexchange? Or are they two separate services? Thanks! – Michael Zyskowski Jan 25 '18 at 16:44
  • @MichaelZyskowski Mod flag the question and explain you'd like it moved to DBA. They should move it for you – Machavity Jan 25 '18 at 16:46
1

To anyone whom it may concern;

The problem of such slow disk read lies in the fragmentation of the data. A subsequent repair of the same table gives very high disk read i/o, indicating sequential access as opposed to random access.

The table has plenty of deleted rows, and the newly inserted records must then be read in a random fashion, resulting in slow read speeds. Again, after the table was repaired, the records where stored sequentially, and the same repair operation gave very fast results. I guess this is another reason to use InnoDB over MyISAM.

Thanks, Michael.

| improve this answer | |
0

"Repair" involves rebuilding index(es). What indexes, and how many?

Rebuilding a MyISAM index is done in one of two ways:

  • using key_buffer
  • by sorting

"By sorting" seems to be usually much faster, especially when the index is bigger than key_buffer_size.

I suggest you experiment with key_buffer_size and max...size to see which way works best. Please report your findings.

(Sorry, I don't have a straight answer; it has been a question that has plagued me for most of 18 years. I don't think about it much anymore, since MyISAM is going away, and everyone is moving to InnoDB.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.