0

I have a MySQL instance running on an Ubuntu 12.04 server, equipped with 120 GB RAM. There are several PHP scripts running on it, which sometimes set new mysql parameters. One of these scripts uses large MEMORY tables to consolidate data before inserting it into MySISAM tables.

Lately I updated max_heap_table_size and tmp_table_size from 16 GB to 20 GB, to avoid "table is full" errors. As a result MySQL crashed during the next run of the script.

Actually it first generated a strack trace:

14:30:19 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=536870912
read_buffer_size=131072
max_used_connections=85
max_threads=700
thread_count=82
connection_count=81
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2055554 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7ff66dbb4f30
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ff632b80e60 thread_stack 0x28000
/usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7ff66b087589]
/usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7ff66af4c9d3]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7ff669c96cb0]
/usr/sbin/mysqld(_Z10field_convP5FieldS0_+0x37)[0x7ff66af49077]
/usr/sbin/mysqld(_ZN10Item_field13save_in_fieldEP5Fieldb+0x46)[0x7ff66af599b6]
/usr/sbin/mysqld(_Z11fill_recordP3THDPP5FieldR4ListI4ItemEb+0x4e)[0x7ff66adf3afe]
/usr/sbin/mysqld(_ZN12select_union9send_dataER4ListI4ItemE+0x6f)[0x7ff66aea705f]
/usr/sbin/mysqld(+0x32c554)[0x7ff66ae5a554]
/usr/sbin/mysqld(+0x3225cf)[0x7ff66ae505cf]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x7e)[0x7ff66ae5255e]
/usr/sbin/mysqld(+0x335274)[0x7ff66ae63274]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0xc03)[0x7ff66ae72ec3]
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x130)[0x7ff66ae6e610]
/usr/sbin/mysqld(_Z21mysql_derived_fillingP3THDP3LEXP10TABLE_LIST+0x121)[0x7ff66ae13671]
/usr/sbin/mysqld(_Z20mysql_handle_derivedP3LEXPFbP3THDS0_P10TABLE_LISTE+0x68)[0x7ff66ae130f8]
/usr/sbin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbjP19Prelocking_strategy+0x11a)[0x7ff66adf7c9a]
/usr/sbin/mysqld(+0x2fac95)[0x7ff66ae28c95]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16a6)[0x7ff66ae307f6]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10f)[0x7ff66ae35a0f]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1e71)[0x7ff66ae37951]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1bd)[0x7ff66aeddd9d]
/usr/sbin/mysqld(handle_one_connection+0x50)[0x7ff66aedde00]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7ff669c8ee9a]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7ff6693bf3fd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fea98004a80): is an invalid pointer
Connection ID (thread ID): 15144
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

The crash/restart happened an hour later:

131122 15:30:24 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
131122 15:30:24 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
131122 15:30:24 [Note] Plugin 'FEDERATED' is disabled.
131122 15:30:24 InnoDB: The InnoDB memory heap is disabled
131122 15:30:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131122 15:30:24 InnoDB: Compressed tables use zlib 1.2.3.4
131122 15:30:24 InnoDB: Initializing buffer pool, size = 128.0M
131122 15:30:24 InnoDB: Completed initialization of buffer pool
131122 15:30:24 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1319218667
131122 15:30:24  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.

Unfortunately I don't have binlog or slow-query log outputs to post here, but I what I can say is that the PHP script continued to run after the stack trace had been produced. It stopped during the mysql restart.

Hardware failure should not be considered, as this error already happened on 2 distinct servers.

What could be the cause of the crash ? How can I find out the maximum max_heap_table_size and tmp_table_size I can use without crashing mysql ?

ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1031141
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 1031141
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

mysql version is 5.5.34 for Ubuntu 12.04,1

3
  • So, what exact version of MySQL server are you running? Dec 6, 2013 at 2:59
  • You probably should try to find appropriate bug report and appeal directly to MySQL support, but before doing it run ulimit -a from mysql user and check for possible memory restrictions. Here is the problem similar to yours: bugs.mysql.com/bug.php?id=68064.
    – Veniamin
    Dec 6, 2013 at 12:45
  • added ulimit and mysql version outputs in first post. I am not sure it is related to this bug, as mysql still runs after the variable update in the PHP script. To be sure I'll have to try to set the value directly in the mysql config but I cannot test it now, I'd need to try on a test server
    – tilo
    Dec 6, 2013 at 14:10

2 Answers 2

2

With 120GB of memory, it seems possible that your machine also has multiple physical CPUs and a Non-Uniform Memory Access (NUMA) architecture... and if that's the case, you could have plenty of available memory but still, counter-intuitively, not enough available memory.

If I'm on track so far, you could be running into this problem when MySQL tries to grow the memory allocated to the MEMORY table, and encounters an error that's related to the way Linux handles memory allocations when running on this architecture -- by only considering the available memory that is on the memory sticks that are directly connected to one particular CPU that was somewhat arbitrarily chosen to be "the one" from which memory requests would be serviced -- in spite of other physical memory being free elsewhere on the motherboard.

NUMA is fine in theory but perhaps not ideally suited to single processes needing massive amounts of memory, as is the case with MySQL... but there's a workaround.

The fix will be to modify the mysqld_safe script by adding this line:

cmd="/usr/bin/numactl --interleave all $cmd"

...immediately after this line...

cmd="$NOHUP_NICENESS"

The reasons are explained here, in an excellent article originally written to address why MySQL servers with this architecture were heavily swapping in spite of free memory; however, that was a symptom of a larger issue which the author subsequently pointed out "is not entirely a swapping problem," which, even with swap disabled, could include "failed memory allocations."

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

I'm speculating, of course, as to whether this applies to your system, but it seems like a worthwhile possibility. I used exactly this fix when I encountered an issue on a new 128GB machine when I tried to provision a 64GB InnoDB Buffer Pool and MySQL was unable to find 64GB free on a machine with nothing else running. When I realized the highest value I could successfully use was something less than 1/4 of the total memory in a 16-core machine with 4 physical processors ... sort of like what you seem to be bumping into ... was when I put the pieces together as to the nature of the problem.

1

The documentation for tmp_table_size says that this setting is the maximum size a temporary table can be in-memory. Exceeding that size does not trigger any table is full errors; it triggers a change from using the table in memory to using a MyISAM table on disk.

The system is crashing because you're allowing temp tables to use up way more memory than they should be. Same with the memory tables.

You should drop the tmp_table_size way down. Remember, this setting is not an overall max. It's a max per temp table. If you have 5 queries making a massively huge temp table (say just under 20GB each), you now have temp tables using up 100GB of your RAM. Add in a 6th, and you've used more RAM than the server has in total.

If your scripts are actually using the MEMORY storage engine, then writing the data into a MyISAM file after you should look into changing that. If there's really a need for that fast of performance on a temporary table that large you should look into getting faster storage (eg. Fusion-io, Virident et al). If those are overkill or too pricey, I'd at least consider consumer-grade SSDs.

MyISAM performs best when it's data is cached into system memory (unused RAM). If your scripts are blowing the cache out every time they make a huge query (using the MEMORY engine), your MyISAM performance is going to suffer.

I suspect the script is using the MEMORY storage engine to 'improve' performance when it's actually killing it. If it's a temporary table, it should be using temp tables, and your tmp_table_size should be something much smaller, forcing it to disk after it breaks tmp_table_size.

The defaults for both max_heap_table_size and tmp_table_size are 16MB. If possible, I'd suggest changing the settings back to the defaults. Adjust upwards in small increments while monitoring resource usage (disk i/o, total memory usage, cpu usage, etc) until you find settings that work for your data set.

4
  • I (mostly) agree with yoonix. Instead of making these values HUGE you should move your tmpdir to tmpfs (RAM) and make these values as SMALL as possible (i.e. tmp_table-size=1K) so that the temp table goes into RAM as soon as possible.
    – HTTP500
    Dec 5, 2013 at 20:05
  • How would that be better? You're still evicting MyISAM data from the OS cache which is going to kill your MyISAM performance. If any queries need a table larger than the amount of free RAM, you have to swap now. Run out of swap, you've crashed the database. I can see this working very well if you have small enough datasets that wouldn't take up much ram at all, but we're talking 20GB here. Running out of memory is still running out of memory whether you're doing it internally in MySQL or through the filesystem layer (which would actually add overhead, negligible yeah, but it's there).
    – user143703
    Dec 5, 2013 at 22:51
  • yoonix I understand what you mean and it makes sense.
    – tilo
    Dec 6, 2013 at 14:16
  • What I don't understand is that we monitor actively this database and the server it's running on and it never swapped nor used all the available memory. That's why, even if changing the way we use the different mysql engines is a good idea, I'd like to understand what happened and if I hit some system or mysql limitation, if there are other parameters I am not aware of that can, in combination with high heap_table_size and tmp_table_size settings, produce this kind of crash.
    – tilo
    Dec 6, 2013 at 14:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .