1

A specific table in my database keeps crashing. I suspect it to be a bad buffer setting in the my.conf file, because it shows this error before crashing (most of the time it needs to be repaired):

18:26:59 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './myDB/myTable.MYI'; try to repair it
18:54:57 [ERROR] /usr/libexec/mysqld: Out of memory (Needed 59542644 bytes) 141020  
18:54:57 [ERROR] /usr/libexec/mysqld: Sort aborted

I don't think it is hard disk related, since tmpdir is set to /tmp/ and has around 48 GB of free disk space.

Input 

mysql> SHOW VARIABLES LIKE 'tmpdir';

Output 

tmpdir         /tmp/

Here is a look at the my.conf settings. Take special note of the parameter sort_buffer_size which has been set way to high. However, I'm unsure if it will produce this kind of error when set too high. This machine runs Ubuntu with 8 GB RAM.

[mysqld]
max_connections         = 1500
back_log                = 50
skip-locking
key_buffer              = 819M
max_allowed_packet      = 16M
table_cache             = 512 
sort_buffer_size        = 2048M
read_buffer_size        = 16M
read_rnd_buffer_size    = 8M
myisam_sort_buffer_size = 64M
thread_cache_size       = 16
query_cache_size        = 16M
thread_concurrency      = 8
max_heap_table_size     = 512M
wait_timeout            = 60
interactive_timeout     = 60

Any thoughts on this issue?

| improve this question | |
  • 1
    2G sort buffer? That's crazy. Keep in mind, the sort buffer is per session buffer. There are plenty of sites that run well with 2M or less buffer. – Manny Calavera Oct 21 '14 at 0:34
  • Could you please use the formatting options in future? – Vérace Oct 21 '14 at 6:00
1

Set in config file this line

max_sort_length=8M

And happy day!

| improve this answer | |
  • 4
    Can you edit your answer to provide a little more detail? Like, what this setting does and how that would address the problem? Perhaps link to some documentation? More complete answers are preferred here. Thanks! – Paul White 9♦ May 3 '18 at 21:53

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.