From: Rick James Date: November 26 2012 6:05pm Subject: RE: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load? List-Archive: http://lists.mysql.com/mysql/228731 Message-Id: <582AFBFC517D194489EF570FE21694CF0701116C@GQ1-EX10-MB03.y.corp.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Nothing looks bad. 96G for the buffer_pool is bigger than I have experienced, but I know of no= reason for it to fail (given that you have 128GB of RAM). > -----Original Message----- > From: Tom [mailto:livefortheday05@stripped] > Sent: Wednesday, November 21, 2012 5:17 PM > To: mysql@stripped > Subject: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf > cause mysqld to randomly crash on high load? >=20 > We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - > running Ubuntu 12.04 with MySQL 5.5.28 . Doing random imports to large > InnoDB tables, over 50+ gigs, randomly after a few hours of heavy load, > mysql does a Signal 11 and crashes. >=20 > We have tried to move hardware. Doing a full dump (but not a restore > yet) gives no issues. Usually on corrupted tables, a dump would fail > no? >=20 > Below is the crash log and my.cnf . >=20 > --- > 12:45:4 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. >=20 > key_buffer_size=3D536870912 > read_buffer_size=3D131072 > max_used_connections=3D324 > max_threads=3D200 > thread_count=3D308 > connection_count=3D308 > It is possible that mysqld could use up to key_buffer_size + > (read_buffer_size + sort_buffer_size)*max_threads =3D > 965187 K bytes of memory > Hope that's ok; if not, decrease some variables in the equation. >=20 > Thread pointer: 0x7fc7eb1b5040 > 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 =3D 7fadf6abfe60 thread_stack 0x30000 > /usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7fc758522759] > /usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7fc7583e9ae3] > /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7fc75713bcb0] > /usr/sbin/mysqld(+0x6671b0)[0x7fc75863a1b0] > /usr/sbin/mysqld(+0x61d6b9)[0x7fc7585f06b9] > /usr/sbin/mysqld(+0x630d12)[0x7fc758603d12] > /usr/sbin/mysqld(+0x6319c2)[0x7fc7586049c2] > /usr/sbin/mysqld(+0x631d85)[0x7fc758604d85] > /usr/sbin/mysqld(+0x626e7d)[0x7fc7585f9e7d] > /usr/sbin/mysqld(+0x633cea)[0x7fc758606cea] > /usr/sbin/mysqld(+0x6347e2)[0x7fc7586077e2] > /usr/sbin/mysqld(+0x624426)[0x7fc7585f7426] > /usr/sbin/mysqld(+0x610871)[0x7fc7585e3871] > /usr/sbin/mysqld(+0x5d4cb0)[0x7fc7585a7cb0] > /usr/sbin/mysqld(+0x5b7c9c)[0x7fc75858ac9c] > /usr/sbin/mysqld(_ZN7handler21read_multi_range_nextEPP18st_key_multi_ra > nge+0x24)[0x7fc7583e9fe4] > /usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x3c)[0x7fc7584a3c8 > c] > /usr/sbin/mysqld(+0x4e9195)[0x7fc7584bc195] > /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x71)[0x7fc7582f > 1741] > /usr/sbin/mysqld(+0x32f025)[0x7fc758302025] > /usr/sbin/mysqld(_ZN4JOIN4execEv+0x4a5)[0x7fc758311155] > /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_E > S2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sele > ct_lex+0x130)[0x7fc75830d000] > /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x17c)[0x > 7fc758312f5c] > /usr/sbin/mysqld(+0x2f66b4)[0x7fc7582c96b4] > /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16d8)[0x7fc7582d1118] > /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10f)[0x7fc758 > 2d5daf] > /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13 > 80)[0x7fc7582d7200] > /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1bd)[0x7fc75837b7a > d] > /usr/sbin/mysqld(handle_one_connection+0x50)[0x7fc75837b810] > /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7fc757133e9a] > /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc756864cbd] >=20 > Trying to get some variables. > Some pointers may be invalid and cause the dump to abort. > Query (7faa4c18e440): is an invalid pointer Connection ID (thread ID): > 2286 > Status: NOT_KILLED >=20 > 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. > 121120 12:48:48 [Note] Plugin 'FEDERATED' is disabled. > 121120 12:48:48 InnoDB: The InnoDB memory heap is disabled > 121120 12:48:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins > 121120 12:48:48 InnoDB: Compressed tables use zlib 1.2.3.4 > 121120 12:48:48 InnoDB: Initializing buffer pool, size =3D 96.0G > 121120 12:48:56 InnoDB: Completed initialization of buffer pool > 121120 12:48:57 InnoDB: highest supported file format is Barracuda. > InnoDB: Log scan progressed past the checkpoint lsn 1341738337497 > 121120 12:48:58 InnoDB: Database was not shut down normally! > InnoDB: Starting crash recovery. > InnoDB: Reading tablespace information from the .ibd files... > InnoDB: Restoring possible half-written data pages from the doublewrite > InnoDB: buffer... > InnoDB: Doing recovery: scanned up to log sequence number 1341743580160 > InnoDB: Doing recovery: scanned up to log sequence number 1341748823040 > InnoDB: Doing recovery: scanned up to log sequence number 1341754065920 > InnoDB: Doing recovery: scanned up to log sequence number 1341759308800 > InnoDB: Doing recovery: scanned up to log sequence number 1341764551680 > ... > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > my.cnf: > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > [client] > port =3D 3306 > socket =3D /var/run/mysqld/mysqld.sock >=20 >=20 > [mysqld_safe] > socket =3D /var/run/mysqld/mysqld.sock > nice =3D 0 >=20 > [mysqld] > skip-name-resolve > innodb_file_per_table > default_storage_engine=3DInnoDB >=20 > user =3D mysql > socket =3D /var/run/mysqld/mysqld.sock > port =3D 3306 > basedir =3D /usr > datadir =3D /data/mysql > tmpdir =3D /tmp > skip-external-locking >=20 > key_buffer =3D 512M > max_allowed_packet =3D 128M > thread_stack =3D 192K > thread_cache_size =3D 64 >=20 > myisam-recover =3D BACKUP > max_connections =3D 500 > table_cache =3D 812 > table_definition_cache =3D 812 >=20 > #query_cache_limit =3D 4M > #query_cache_size =3D 512M > join_buffer_size =3D 512K >=20 >=20 > innodb_additional_mem_pool_size =3D 20M > innodb_buffer_pool_size =3D 96G > #innodb_file_io_threads =3D 4 > #innodb_thread_concurrency =3D 12 > innodb_flush_log_at_trx_commit =3D 1 > innodb_log_buffer_size =3D 8M > innodb_log_file_size =3D 1024M > innodb_log_files_in_group =3D 2 > innodb_max_dirty_pages_pct =3D 90 > innodb_lock_wait_timeout =3D 120 >=20 > log_error =3D /var/log/mysql/error.log >=20 > long_query_time =3D 5 > slow_query_log =3D 1 > slow_query_log_file =3D /var/log/mysql/slowlog.log >=20 > [mysqldump] > quick > quote-names > max_allowed_packet =3D 16M >=20 > [mysql] >=20 > [isamchk] > key_buffer =3D 16M > =3D=3D=3D=3D=3D=3D >=20 > Memory usage, disk space is fine. This only happens during high I/O. >=20 > Would anything in the my.cnf file be causing this issue? >=20 > tia. >=20 > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql