Skip to main content

Issue with MySQL

david.cocos
I run a vps with: CPU: Intel(R) Xeon(R) CPU E5-2620 v2 @ 2.10GHz 15360 KB cache 8 cores RAM: 32 GB SSD drive CentOS 6.5 MySQL 5.6.17 The server runs about 100 domains. A problem with it is that it uses very little ram (like 10%at most). MySQL uses both myisam and innodb tables. Mysql crashes once or twice a day, and the errors have roughly a difference of almost 5 minutes between them, in the error log. This is one of the many errors from the error log, and the basically say the same thing, that innodb has ran out of locks. 2014-09-15 02:12:21 13973 [ERROR] InnoDB: Unable to lock ./"something"/ps_tax.ibd, error: 37 2014-09-15 02:12:21 7f22c8ac6700 InnoDB: Assertion failure in thread 139787372357376 in file fil0fil.cc line 875 InnoDB: Failing assertion: ret InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 00:12:21 UTC - mysqld got signal 6 ; 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=134217728 read_buffer_size=131072 max_used_connections=19 max_threads=1000 thread_count=5 connection_count=4 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 528775 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x32b2880 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 = 7f22c8ac5e18 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8c8af5] /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x65ffb4] /lib64/libpthread.so.0(+0xf710)[0x7f25459bb710] /lib64/libc.so.6(gsignal+0x35)[0x7f2544666635] /lib64/libc.so.6(abort+0x175)[0x7f2544667e15] /usr/sbin/mysqld[0xaafc78] /usr/sbin/mysqld[0xaaff19] /usr/sbin/mysqld[0xab02cb] /usr/sbin/mysqld[0xa815ad] /usr/sbin/mysqld[0xa81d1b] /usr/sbin/mysqld[0xa6f0b3] /usr/sbin/mysqld[0xa5b7e0] /usr/sbin/mysqld[0xa038d9] /usr/sbin/mysqld[0x9732e5] /usr/sbin/mysqld[0x9743b1] /usr/sbin/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3e)[0x5a68ce] /usr/sbin/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x624)[0x763724] /usr/sbin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP18Open_table_context+0x1c8)[0x695b08] /usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0xcaa)[0x69834a] /usr/sbin/mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x4f)[0x69846f] /usr/sbin/mysqld[0x710a5c] /usr/sbin/mysqld(_Z14get_all_tablesP3THDP10TABLE_LISTP4Item+0x670)[0x717510] /usr/sbin/mysqld(_Z24get_schema_tables_resultP4JOIN23enum_schema_table_state+0x2f1)[0x705111] /usr/sbin/mysqld(_ZN4JOIN14prepare_resultEPP4ListI4ItemE+0x9d)[0x6f956d] /usr/sbin/mysqld(_ZN4JOIN4execEv+0xdf)[0x6b9caf] /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x250)[0x6fa5e0] /usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x187)[0x6fae67] /usr/sbin/mysqld[0x6d782d] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x2138)[0x6dad78] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x32f)[0x6dd9ff] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x145f)[0x6def4f] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xcf)[0x6ac46f] /usr/sbin/mysqld(handle_one_connection+0x47)[0x6ac597] /usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0x9452aa] /lib64/libpthread.so.0(+0x79d1)[0x7f25459b39d1] /lib64/libc.so.6(clone+0x6d)[0x7f254471c86d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f224c01af60): is an invalid pointer Connection ID (thread ID): 13900 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. 140915 02:12:21 mysqld_safe Number of processes running now: 0 140915 02:12:21 mysqld_safe mysqld restarted 2014-09-15 02:12:22 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2014-09-15 02:12:22 23461 [Note] Plugin 'FEDERATED' is disabled. 2014-09-15 02:12:22 23461 [Note] InnoDB: Using atomics to ref count buffer pool pages 2014-09-15 02:12:22 23461 [Note] InnoDB: The InnoDB memory heap is disabled 2014-09-15 02:12:22 23461 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2014-09-15 02:12:22 23461 [Note] InnoDB: Compressed tables use zlib 1.2.3 2014-09-15 02:12:22 23461 [Note] InnoDB: Using Linux native AIO 2014-09-15 02:12:22 23461 [Note] InnoDB: Using CPU crc32 instructions 2014-09-15 02:12:22 23461 [Note] InnoDB: Initializing buffer pool, size = 8.0G 2014-09-15 02:12:23 23461 [Note] InnoDB: Completed initialization of buffer pool 2014-09-15 02:12:23 23461 [Note] InnoDB: Highest supported file format is Barracuda. 2014-09-15 02:12:23 23461 [Note] InnoDB: Log scan progressed past the checkpoint lsn 152091627747 2014-09-15 02:12:23 23461 [Note] InnoDB: Database was not shutdown normally! 2014-09-15 02:12:23 23461 [Note] InnoDB: Starting crash recovery. 2014-09-15 02:12:23 23461 [Note] InnoDB: Reading tablespace information from the .ibd files... 2014-09-15 02:12:23 23461 [Note] InnoDB: Restoring possible half-written data pages 2014-09-15 02:12:23 23461 [Note] InnoDB: from the doublewrite buffer... InnoDB: Doing recovery: scanned up to log sequence number 152091627757 2014-09-15 02:12:24 23461 [Note] InnoDB: 128 rollback segment(s) are active. 2014-09-15 02:12:24 23461 [Note] InnoDB: Waiting for purge to start 2014-09-15 02:12:24 23461 [Note] InnoDB: 5.6.17 started; log sequence number 152091627757 2014-09-15 02:12:24 23461 [Note] Server hostname (bind-address): '*'; port: 3306 2014-09-15 02:12:24 23461 [Note] IPv6 is available. 2014-09-15 02:12:24 23461 [Note] - '::' resolves to '::'; 2014-09-15 02:12:24 23461 [Note] Server socket created on IP: '::'. 2014-09-15 02:12:24 23461 [Note] Event Scheduler: Loaded 0 events 2014-09-15 02:12:24 23461 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.6.17' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140915 02:12:32 mysqld_safe Number of processes running now: 0 140915 02:12:32 mysqld_safe mysqld restarted 2014-09-15 02:12:33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2014-09-15 02:12:33 23766 [Note] Plugin 'FEDERATED' is disabled. 2014-09-15 02:12:33 23766 [Note] InnoDB: Using atomics to ref count buffer pool pages 2014-09-15 02:12:33 23766 [Note] InnoDB: The InnoDB memory heap is disabled 2014-09-15 02:12:33 23766 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2014-09-15 02:12:33 23766 [Note] InnoDB: Compressed tables use zlib 1.2.3 2014-09-15 02:12:33 23766 [Note] InnoDB: Using Linux native AIO 2014-09-15 02:12:33 23766 [Note] InnoDB: Using CPU crc32 instructions 2014-09-15 02:12:33 23766 [Note] InnoDB: Initializing buffer pool, size = 8.0G 2014-09-15 02:12:33 23766 [Note] InnoDB: Completed initialization of buffer pool 2014-09-15 02:12:33 23766 [Note] InnoDB: Highest supported file format is Barracuda. 2014-09-15 02:12:33 23766 [Note] InnoDB: The log sequence numbers 99802598776 and 99802598776 in ibdata files do not match the log sequence number 152091752069 in the ib_logfiles! 2014-09-15 02:12:33 23766 [Note] InnoDB: Database was not shutdown normally! 2014-09-15 02:12:33 23766 [Note] InnoDB: Starting crash recovery. 2014-09-15 02:12:33 23766 [Note] InnoDB: Reading tablespace information from the .ibd files... 2014-09-15 02:12:34 23766 [Note] InnoDB: Restoring possible half-written data pages 2014-09-15 02:12:34 23766 [Note] InnoDB: from the doublewrite buffer... 2014-09-15 02:12:34 23766 [Note] InnoDB: 128 rollback segment(s) are active. 2014-09-15 02:12:34 23766 [Note] InnoDB: Waiting for purge to start 2014-09-15 02:12:34 23766 [Note] InnoDB: 5.6.17 started; log sequence number 152091752069 2014-09-15 02:12:34 23766 [Note] Server hostname (bind-address): '*'; port: 3306 2014-09-15 02:12:34 23766 [Note] IPv6 is available. 2014-09-15 02:12:34 23766 [Note] - '::' resolves to '::'; 2014-09-15 02:12:34 23766 [Note] Server socket created on IP: '::'. 2014-09-15 02:12:34 23766 [Note] Event Scheduler: Loaded 0 events 2014-09-15 02:12:34 23766 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.6.17' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 2014-09-15 02:17:21 23766 [Note] /usr/sbin/mysqld: Normal shutdown 2014-09-15 02:17:21 23766 [Note] Giving 0 client threads a chance to die gracefully 2014-09-15 02:17:21 23766 [Note] Event Scheduler: Purging the queue. 0 events 2014-09-15 02:17:21 23766 [Note] Shutting down slave threads 2014-09-15 02:17:21 23766 [Note] Forcefully disconnecting 0 remaining clients 2014-09-15 02:17:21 23766 [Note] Binlog end 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'partition' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'ARCHIVE' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_METRICS' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_CMPMEM' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_CMP' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_LOCKS' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'INNODB_TRX' 2014-09-15 02:17:21 23766 [Note] Shutting down plugin 'InnoDB' 2014-09-15 02:17:21 23766 [Note] InnoDB: FTS optimize thread exiting. 2014-09-15 02:17:21 23766 [Note] InnoDB: Starting shutdown... 2014-09-15 02:17:23 23766 [Note] InnoDB: Shutdown completed; log sequence number 152091879169 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'BLACKHOLE' 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'MRG_MYISAM' 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'CSV' 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'MEMORY' 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'MyISAM' 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'sha256_password' 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'mysql_old_password' 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'mysql_native_password' 2014-09-15 02:17:23 23766 [Note] Shutting down plugin 'binlog' 2014-09-15 02:17:23 23766 [Note] /usr/sbin/mysqld: Shutdown complete
And this is my.cnf: [mysqld] thread_cache_size=16K query_cache_size=512M tmp_table_size=512M query_cache_limit=8M max_heap_table_size=256M max_connections=1000 key_buffer_size=128M join_buffer_size=12M innodb_buffer_pool_size=8G query_cache_type=1 max_allowed_packet=536870912 open_files_limit=50000
I would be very grateful if you could post a solution to my problem. Thanks in advance!

Comments

9 comments

Date Votes
  • cPanelMichael
    Hello :) The following thread is useful for troubleshooting InnoDB corruption, which is referenced in your error output: InnoDB Repair Guide Thank you.
    0
  • david.cocos
    I don't think that it is a corruption issue, because the server never failed to go back on, after crashing, and the error log doesn't show the same file that can't be locked. Also, the errors mention a file named "fil0fil.cc" which I am unable to find on the server (I used the "find" command via SSH). Can it be something regarding the maximum number of open files? It is set to 50000, is that enough? Regarding the RAM issue, what values should I increase in my.cnf? Would this be any good? Global key_buffer_size=128M->4G innodb_buffer_pool_size=8->11G query_cache_limit=8M->1G Thread sort_buffer_size=256K->1M read_buffer_size=128K->512K join_buffer_size=12M->512K read_rnd_buffer_size=256k->1M thread_stack=256K->1M
    0
  • cPanelMichael
    You may want to run the MySQL tuner and make changes based on it's recommendations. We have a "Workarounds and Optimization" forum that you can open a thread in to get input from others on how to best configure MySQL. Thank you.
    0
  • david.cocos
    Can you give me any information about the "fil0fil.cc" file? This file is called on every error that appears in the error log "Assertion failure in thread in file fil0fil.cc line 875", and it always says that it is unable to lock a ibd file, giving the error 37.
    0
  • cPanelMichael
    It's simply part of the InnoDB corruption error message. Please see this part of the output: [QUOTE]InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace.
    The fact that MySQL starts does not mean there is not InnoDB corruption. You may need to consult with a qualified system administrator if the guide linked earlier is not helpful. Thank you.
    0
  • david.cocos
    Hello, I have decoded this with stack trace. fil_node_open_file(fil_node_t*, fil_system_t*, fil_space_t*) + 1624 fil_node_prepare_for_io(fil_node_t*, fil_system_t*, fil_space_t*) + 89 fil_io(unsigned long, bool, unsigned long, unsigned long, unsigned long, unsigned long, unsigned long, void*, void*) + 299 buf_read_page_low(dberr_t*, bool, unsigned long, unsigned long, unsigned long, unsigned long, long, unsigned long) + 285 buf_read_page(unsigned long, unsigned long, unsigned long) + 75 buf_page_get_gen(unsigned long, unsigned long, unsigned long, unsigned long, buf_block_t*, unsigned long, char const*, unsigned + 659 btr_cur_open_at_index_side_func(bool, dict_index_t*, unsigned long, btr_cur_t*, unsigned long, char const*, unsigned long, mtr_ + 864 row_search_max_autoinc(dict_index_t*, char const*, unsigned long*) + 281 ha_innobase::innobase_initialize_autoinc() + 373 ha_innobase::open(char const*, int, unsigned int) + 2801 handler::ha_open(TABLE*, char const*, int, int) + 62 open_table_from_share(THD*, TABLE_SHARE*, char const*, unsigned int, unsigned int, unsigned int, TABLE*, bool) + 1572 open_table(THD*, TABLE_LIST*, Open_table_context*) + 456 open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) + 3242 open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int) + 79 fill_schema_table_by_open(THD*, bool, TABLE*, st_schema_table*, st_mysql_lex_string*, st_mysql_lex_string*, Open_tables_backup* + 524 get_all_tables(THD*, TABLE_LIST*, Item*) + 1648 get_schema_tables_result(JOIN*, enum_schema_table_state) + 753 JOIN::prepare_result(List**) + 157 JOIN::exec() + 223 mysql_select(THD*, TABLE_LIST*, unsigned int, List&, Item*, SQL_I_List*, SQL_I_List*, Item*, unsigned + 592 handle_select(THD*, select_result*, unsigned long) + 391 execute_sqlcom_select(THD*, TABLE_LIST*) + 621 mysql_execute_command(THD*) + 8504 mysql_parse(THD*, char*, unsigned int, Parser_state*) + 815 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 5215 do_handle_one_connection(THD*) + 207 handle_one_connection + 71 pfs_spawn_thread + 298 _end + 110703905 _end + 91210685
    Can you please tell me what it does and if there is anything wrong with it.
    0
  • cPanelMichael
    I'm not seeing much information there to help determine the cause of the issue. Have you reviewed the InnoDB repair guide linked in the earlier post? Thank you.
    0
  • david.cocos
    Yes I've done that. I have checked all the ibd files with innochecksum and they all appeared to be ok. What happens is: every 5 minutes innodb finds a corrupted tablespace, and it crashes, and when it gets back up it repairs the broken file. And like once a day I receive a like 5 e-mails saying that mysql has failed, and after that it recovers. My problem is that I can't figure out what is causing these crashes. Also I tried to install mysqlmymonlite, and when I try to use ./mysqlmymonlite mysqltuner " The --forcemem option is required for remote connections". How can I run past this?
    0
  • cPanelMichael
    You may need to consult with a database specialist or a qualified system administrator if you are unable to determine why MySQL is crashing. I don't believe a MySQL tuner would help very much, but you can post to the following thread if you are having trouble with that application: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post