I have a MyISAM table w/ ~1million rows and two indexes - one's a three-column key, and the other is a spatial index for bounded SELECT statements on a separate point column. My insert statements typically insert decimal degrees into "lat" and "long" columns, and an update trigger converts and inserts those into the point column for faster reads.
The problem is that every time I do a (smallish) bulk-insert from, say, an incremental mysqldump, the server goes into "Repair with keycache" mode and takes ~20 mins to finish inserting ~1,000 rows!
I'm admittedly on a dinky machine (AWS Micro instance w/ up to 2 cores and ~600megs memory) but it still shouldn't be using "Repair with keycache." I've googled for hours and tweaks and prodded settings and theories with no luck. Everything i can find says it has to do with the max theoretical index size, but even with myisam_max_sort_file_size set to ~1TB things don't work. I can't even figure out how to turn on granular enough logging to see what might be causing the issue.
[mysqld] datadir=/var/lib/mysql tmpdir=/var/lib/mysql/tmp socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 skip-external-locking long_query_time=3 slow_query_log slow_query_log_file=/var/log/log-slow-queries.log log-bin=mysql-bin log-error=/var/log/mysqld.log log-warnings=5 server-id= 1 myisam_max_sort_file_size=900G myisam_sort_buffer_size=300M myisam_repair_threads=1 skip-innodb key_buffer_size=128M bulk_insert_buffer_size=512M [mysqld_safe] log-error=/var/log/mysqld_safe.log pid-file=/var/run/mysqld/mysqld.pid myisam_recover_options
A chunk from the log files:
120410 06:15:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 120410 06:15:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 120410 6:15:58 [Note] Plugin 'InnoDB' is disabled. 120410 6:15:59 [Note] Event Scheduler: Loaded 0 events 120410 6:15:59 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.61-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 120410 7:06:57 [Note] /usr/libexec/mysqld: Normal shutdown 120410 7:06:57 [Note] Event Scheduler: Purging the queue. 0 events 120410 7:06:57 [Note] /usr/libexec/mysqld: Shutdown complete 120410 07:06:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 120410 07:06:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 120410 7:06:58 [Note] Plugin 'InnoDB' is disabled. 120410 7:06:58 [Note] Event Scheduler: Loaded 0 events 120410 7:06:58 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.61-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 120410 7:26:57 [Note] /usr/libexec/mysqld: Normal shutdown 120410 7:26:57 [Note] Event Scheduler: Purging the queue. 0 events 120410 7:26:57 [Note] /usr/libexec/mysqld: Shutdown complete 120410 07:26:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
The SHOW CREATE TABLE in question:
CREATE TABLE `production_objects_0` ( `objId` int(11) NOT NULL AUTO_INCREMENT, `col2` tinyint(1) DEFAULT NULL, `col3` varchar(200) DEFAULT NULL, `col1` int(32) DEFAULT NULL, `Lat` double DEFAULT NULL, `Long` double DEFAULT NULL, `lat_long_point` point NOT NULL, PRIMARY KEY (`objId`), SPATIAL KEY `point_index` (`lat_long_point`), KEY `name_index` (`col1`,`col2`,`col3`) ) ENGINE=MyISAM AUTO_INCREMENT=1081735 DEFAULT CHARSET=utf8 PACK_KEYS=1
I'm using standard insert statements created by mysqldump, the only thing that might be unusual is that I have this trigger applied to the table:
CREATE TRIGGER set_lat_long_point BEFORE INSERT ON production_objects_0 FOR EACH ROW SET new.lat_long_point = GeomFromText(concat('Point(',new.Long,' ',new.Lat,')'));
df -h output:
Filesystem Size Used Avail Use% Mounted on /dev/xvda1 1009G 6.0G 992G 1% / tmpfs 298M 0 298M 0% /dev/shm (ls -l shows that user mysql owns everything, including the tmp dir, in /var/lib/mysql)
Any ideas on what might be causing the server to completely overlook repair with sort? Is there a chance my trigger is causing thing somehow? Can spatial indexes even be used with "repair with sort" on recent versions of MySQL? I read that there has been trouble with that in past versions, but nothing (good or bad) about recent stuff (i'm on 5.1).
Ideas on how to log/out/watch what's happening so i can continue to triage would help as well.
Rolando's key buffer recommendation query suggested a size of 76M for my server, so I set it to 128M and added a
bulk_insert_buffer_size of 512M per Rolando's suggestion. I also increased the server's disk size to 1TB and elevated the
myisam_max_sort_file_size to 900G just to be sure that's not a culprit, and it's still recovering from inserts with "repair from keycache".
Is there a chance my trigger is causing thing somehow? Can spatial indexes even be used with "repair with sort" on recent versions of MySQL? I read that there has been trouble with that in past versions, but nothing (good or bad) about recent stuff (I'm on 5.1).
I upped the server's (admittedly small) 8GB disk to a 100GB disk. Running
df -h shows 92GB available on the same filesystem as the temp dir, against a ~500MB table. Could my ~90GB disk space still be too small?