Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

my.conf:

[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.

share|improve this question
    
Does /var/lib/mysql/tmp (the mysql tmpdir) have enough free space to store the indexes? If not, it'll revert to a repair with keycache no matter what your myisam_max_sort_file_size is set to. –  Phil Apr 10 '12 at 10:31
    
I was originally concerned about that when I first read up on the issue, and 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? –  Amos Apr 10 '12 at 14:44

1 Answer 1

up vote 0 down vote accepted

Your my.conf says the following: key_buffer_size=32M That's really small. You could afford to set it higher: Run this query please:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give you a recommended size of key_buffe_size. Please you common sense when setting this value.

Here is something else: I don't see a bulk_insert_buffer_size. The default is 8MB. Try setting it to 512MB:

[mysqld]
bulk_insert_buffer_size=512M

I just noticed your myisam_sort_buffer_size is only 300M. Set it to 4G (max value)

[mysqld]
myisam_sort_buffer_size=4G
share|improve this answer
    
Thanks for the notes on buffer size! The 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 your 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" :( –  Amos Apr 10 '12 at 19:14
    
Updated my answer !!! –  RolandoMySQLDBA Apr 10 '12 at 19:30
    
Thanks again, Rolando! Unfortunately, that didn't do it either...it's still uses repair from keycache and takes ~15 mins for ~1000 rows. 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). –  Amos Apr 11 '12 at 5:51
1  
So, somehow, I neglected to mention that I was pulling the insert statements from a mysqldump file...and including the part where it turns the index off before the inserts, and on again after (the code looked like it was commented out - i just didn't understand the mysqldump syntax). Apparently, turning the index off and on like that ALWAYS causes the table to reindex from scratch...removing the --disable-keys or -K switch on the mysqldump fixed the issue, and my inserts take a couple seconds now ;) –  Amos Apr 21 '12 at 6:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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