failed queries of large tables with error mysqld got signal 11
One of our users is working with fairly large tables (tens of millions of rows, 15-30 Gb per table). Queries of such tables are failing with the error message below. The same query on smaller subsets (fewer rows) of the same table succeed. I've created a synthetic example (see below) that mimcs the user's table/query and see the same error.
This is on Ubuntu 14.04 (and 12.04), with 5.6.19 MySQL Community Server (a related error seems to occur with 5.6.23). It occurs on two different machines,both of which have over 100 GB RAM and for which top gives no indication of problems with not enough memory being available for the mysql server process.
In addition to the primary error (below) that I see with 5.6.19, I've also gotten the following error on a server running 5.6.23:
ERROR 9 (HY000) at line 2: Unexpected EOF found when reading file '/tmp/MYxjFmaC' (Errcode: 175 - File to short; Expected more data in file)
Does anyone have any suggestions?
thanks,
Chris
Chris Paciorek
Statistical Computing Consultant
University of California, Berkeley
=== PRIMARY ERROR ===========
Here's the error message and backtrace
02:30:11 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=21474836480
read_buffer_size=2147479552
max_used_connections=1
max_threads=10
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 251658336 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f5cfe3174d0
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 = 7f5528073e40 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x7f5cec4bd1cc]
/usr/sbin/mysqld(handle_fatal_signal+0x3d2)[0x7f5cec21d572]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f5ceaf32340]
/usr/sbin/mysqld(heap_rrnd+0x2b)[0x7f5cec55c34b]
/usr/sbin/mysqld(_ZN7ha_heap7rnd_posEPhS0_+0x33)[0x7f5cec558683]
/usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x9c)[0x7f5cec14f3ac]
/usr/sbin/mysqld(+0x61d354)[0x7f5cec3e4354]
/usr/sbin/mysqld(_ZN13QEP_tmp_table8end_sendEv+0x32d)[0x7f5cec28566d]
/usr/sbin/mysqld(_Z13sub_select_opP4JOINP13st_join_tableb+0x69)[0x7f5cec2818b9]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x2d0)[0x7f5cec27ef60]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x23d)[0x7f5cec2c8c9d]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x165)[0x7f5cec2c9585]
/usr/sbin/mysqld(+0x34ab1a)[0x7f5cec111b1a]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x30e4)[0x7f5cec2a7b54]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x3c8)[0x7f5cec2ab308]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x2363)[0x7f5cec2add03]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1f5)[0x7f5cec273715]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x7f5cec273770]
/usr/sbin/mysqld(pfs_spawn_thread+0x140)[0x7f5cec4ff370]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f5ceaf2a182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f5cea64cfbd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f5514004f80): select sum(transaction_amount) as tx_amount, user_id, tx_id, transaction_description from trial where transaction_amount < 800 group by transaction_description, user_id, tx_id
Connection ID (thread ID): 1
Status: NOT_KILLED
===== QUERY THAT IS FAILING ======
select sum(transaction_amount) as tx_amount, user_id, tx_id, transaction_description
into outfile '~/file.out'
from trial
where transaction_amount < 800
group by transaction_description, user_id, tx_id;
===== INFO ON THE TABLE =====
mysql> show create table trial;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| trialshort | CREATE TABLE `trial` (
`transaction_amount` float NOT NULL,
`user_id` char(32) DEFAULT NULL,
`transaction_description` varchar(100) DEFAULT NULL,
`tx_id` char(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
==== HOW TO CREATE THE TABLE =====
CREATE TABLE trial (
transaction_amount FLOAT NOT NULL
);
DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT, IN MinVal FLOAT, IN MaxVal FLOAT)
BEGIN
DECLARE i INT;
SET i = 1;
START TRANSACTION;
WHILE i <= NumRows DO
INSERT INTO trial VALUES (MinVal + RAND() * (MaxVal - MinVal));
SET i = i + 1;
END WHILE;
COMMIT;
END$$
DELIMITER ;
CALL InsertRand(70000000, 0.0, 1000.0);
ALTER TABLE trialshort ADD user_id char(32);
UPDATE trialshort SET user_id = CONCAT(substring(MD5(RAND(1)), 1, 4), 'aaaaaaaaaaaaaaaaaaaaaaaaaaaa');
ALTER TABLE trialshort ADD transaction_description varchar(100);
UPDATE trialshort SET transaction_description = CONCAT(substring(MD5(RAND(1)), 1, 6), 'abcdefghijklmnopqrstuvwxyz012345abcdefghijklmnopqrstuvwxyz012345abcdefghijklmnopqrstuvwxyz0123');
ALTER TABLE trialshort ADD tx_id char(64);
UPDATE trialshort SET tx_id = concat(MD5(RAND(1)), MD5(RAND(2)) );
===== CONFIGURATION INFO ==============
> cat .my.cnf
[client]
user = root
password = check123
port = 53308
socket = /accounts/staff/paciorek/mysql/run/mysqld.sock
[mysqld_safe]
user = paciorek
socket = /accounts/staff/paciorek/mysql/run/mysqld.sock
nice = 0
[mysql]
socket = /accounts/staff/paciorek/mysql/run/mysqld.sock
port = 53308
#database = main
[mysqld]
user = paciorek
port = 53308
pid_file = /accounts/staff/paciorek/mysql/run/mysqld.pid
socket = /accounts/staff/paciorek/mysql/run/mysqld.sock
datadir = /accounts/staff/paciorek/mysql/lib
tmpdir = /tmp
log_error = /accounts/staff/paciorek/mysql/log/error.log
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=10
query_cache_size=250m
myisam_max_sort_file_size=20G
myisam_sort_buffer_size = 20G
key_buffer_size = 20G
read_buffer_size = 20G
read_rnd_buffer_size = 20G
sort_buffer_size = 20G
join_buffer_size = 20G
max_heap_table_size = 20G
tmp_table_size = 20G
innodb_buffer_pool_size= 70G
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 100M
innodb_log_file_size = 500M