MAMP / MySQL config notes
Problem: MySQL taking forever to load some large data dumps. Forever or longer.
“mysql> show processlist;” shows it wedged at “Repair with keycache” and “Waiting for table metadata lock”.
According to a handy Stack Overflow article, this is a known and dreaded condition, which can be addressed by making sure tmp dir has plenty of space, and increasing size of myisam_max_sort_file_size from 2G (2146435072) to 30G (32212254720). Using MAMP 1.9.6 it took some more digging to find out how to add a local my.cnf settings file for MySQL. This now lives in /Applications/MAMP/conf/my.cnf (I added into [mysqld] section a line saying ‘myisam_max_sort_file_size = 30G’.
Does this work? Well I don’t know yet. But enough times I’ve searched around before and found my own notes, that I thought I should at least write this much down for my future self to find :)