GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL Crashing, need help to diagnose

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Crashing, need help to diagnose

    For the past couple of days I've returned to work in the morning to find MySQL has stopped, so I restart it and it's fine - until the next morning.

    I'm unsure why this is happening, could anyone help me please?

    System Info
    VPS with 2Gb RAM, 2 Core CPU, 40GB SSD
    Debian 7 x64
    Server version: 5.6.15-63.0-log Percona XtraDB Cluster (GPL), Release 25.5, wsrep_25.5.r4061

    No MySQL replication configured

    Percona is installed using the Debian Repositories
    Code:
    deb http://repo.percona.com/apt wheezy main
    deb-src http://repo.percona.com/apt wheezy main
    my.cnf
    Code:
     Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
    
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default-storage-engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    pid-file                       = /var/lib/mysql/mysql.pid
    bind-address                   = 0.0.0.0
    # MyISAM #
    key-buffer-size                = 32M
    myisam-recover                 = FORCE,BACKUP
    
    # SAFETY #
    max-allowed-packet             = 16M
    max-connect-errors             = 1000000
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    log-bin                        = /var/lib/mysql/mysql-bin
    expire-logs-days               = 14
    sync-binlog                    = 1
    
    # CACHES AND LIMITS #
    tmp-table-size                 = 32M
    max-heap-table-size            = 32M
    query-cache-type               = 0
    query-cache-size               = 0
    max-connections                = 500
    thread-cache-size              = 50
    open-files-limit               = 65535
    table-definition-cache         = 4096
    table-open-cache               = 4096
    
    # INNODB #
    innodb-flush-method            = O_DIRECT
    innodb-log-files-in-group      = 2
    innodb-log-file-size           = 128M
    innodb-flush-log-at-trx-commit = 1
    innodb-file-per-table          = 1
    innodb-buffer-pool-size        = 1456M
    
    # LOGGING #
    log-error                      = /var/lib/mysql/mysql-error.log
    log-queries-not-using-indexes  = 1
    slow-query-log                 = 1
    slow-query-log-file            = /var/lib/mysql/mysql-slow.log
    Examining the mysql error log gives the following:

    Code:
    key_buffer_size=33554432
    read_buffer_size=131072
    max_used_connections=0
    max_threads=502
    thread_count=0
    connection_count=0
    It is possible that mysqld could use up to 
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 233047 K  bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.
    
    Thread pointer: 0x0
    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 = 0 thread_stack 0x40000
    /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x8ed9fe]
    /usr/sbin/mysqld(handle_fatal_signal+0x4a1)[0x6b8181]
    /lib/x86_64-linux-gnu/libpthread.so.0(+0xf030)[0x7f085ea37030]
    You may download the Percona140507 00:34:33 mysqld_safe mysqld from pid file /var/lib/mysql/mysql.pid ended
    140507 09:06:56 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/
    140507 09:06:56 mysqld_safe Skipping wsrep-recover for empty datadir: /var/lib/mysql/
    140507 09:06:56 mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position
    2014-05-07 09:06:57 0 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1'
    2014-05-07 09:06:57 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2014-05-07 09:06:57 9750 [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.
    2014-05-07 09:06:57 9750 [Note] Plugin 'FEDERATED' is disabled.
    2014-05-07 09:06:57 9750 [Note] InnoDB: The InnoDB memory heap is disabled
    2014-05-07 09:06:57 9750 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2014-05-07 09:06:57 9750 [Note] InnoDB: Compressed tables use zlib 1.2.7
    2014-05-07 09:06:57 9750 [Note] InnoDB: Using Linux native AIO
    2014-05-07 09:06:57 9750 [Note] InnoDB: Not using CPU crc32 instructions
    2014-05-07 09:06:57 9750 [Note] InnoDB: Initializing buffer pool, size = 1.4G
    2014-05-07 09:06:57 9750 [Note] InnoDB: Completed initialization of buffer pool
    2014-05-07 09:06:57 9750 [Note] InnoDB: Highest supported file format is Barracuda.
    2014-05-07 09:06:57 9750 [Note] InnoDB: Log scan progressed past the checkpoint lsn 7378120
    2014-05-07 09:06:57 9750 [Note] InnoDB: Database was not shutdown normally!
    2014-05-07 09:06:57 9750 [Note] InnoDB: Starting crash recovery.
    2014-05-07 09:06:57 9750 [Note] InnoDB: Reading tablespace information from the .ibd files...
    2014-05-07 09:06:58 9750 [Note] InnoDB: Restoring possible half-written data pages 
    2014-05-07 09:06:58 9750 [Note] InnoDB: from the doublewrite buffer...
    InnoDB: Doing recovery: scanned up to log sequence number 7378130
    InnoDB: Last MySQL binlog file position 0 12986833, file name mysql-bin.000008
    2014-05-07 09:06:58 9750 [Note] InnoDB: 128 rollback segment(s) are active.
    2014-05-07 09:06:58 9750 [Note] InnoDB: Waiting for purge to start
    2014-05-07 09:06:58 9750 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.15-rel63.0 started; log sequence number 7378130
    2014-05-07 09:06:58 9750 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
    2014-05-07 09:06:58 9750 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
    2014-05-07 09:06:58 9750 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
    2014-05-07 09:06:58 9750 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
    2014-05-07 09:06:58 9750 [Note] Server socket created on IP: '0.0.0.0'.
    2014-05-07 09:06:58 9750 [Note] Event Scheduler: Loaded 0 events
    2014-05-07 09:06:58 9750 [Note] WSREP: Read nil XID from storage engines, skipping position init
    2014-05-07 09:06:58 9750 [Note] WSREP: wsrep_load(): loading provider library 'none'
    2014-05-07 09:06:58 9750 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.6.15-63.0-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona XtraDB Cluster (GPL), Release 25.5, wsrep_25.5.r4061
    2014-05-07 09:07:01 9750 [ERROR] /usr/sbin/mysqld: Table './dbispconfig/server' is marked as crashed and should be repaired
    2014-05-07 09:07:01 9750 [Warning] Checking table:   './dbispconfig/server'
    2014-05-07 09:07:06 9750 [ERROR] /usr/sbin/mysqld: Table './dbispconfig/ftp_user' is marked as crashed and should be repaired
    2014-05-07 09:07:06 9750 [Warning] Checking table:   './dbispconfig/ftp_user'
    2014-05-07 09:08:04 9750 [ERROR] /usr/sbin/mysqld: Table './c2mhpcomp/wp_options' is marked as crashed and should be repaired
    2014-05-07 09:08:04 9750 [Warning] Checking table:   './c2mhpcomp/wp_options'
    2014-05-07 09:08:07 9750 [ERROR] /usr/sbin/mysqld: Table './c2mhpcomp/wp_posts' is marked as crashed and should be repaired
    2014-05-07 09:08:07 9750 [Warning] Checking table:   './c2mhpcomp/wp_posts'
    2014-05-07 09:08:07 9750 [ERROR] /usr/sbin/mysqld: Table './c2mhpcomp/wp_postmeta' is marked as crashed and should be repaired
    2014-05-07 09:08:07 9750 [Warning] Checking table:   './c2mhpcomp/wp_postmeta'
    2014-05-07 09:08:09 9750 [ERROR] /usr/sbin/mysqld: Table './c2mhpcomp/wp_usermeta' is marked as crashed and should be repaired
    2014-05-07 09:08:09 9750 [Warning] Checking table:   './c2mhpcomp/wp_usermeta'
    2014-05-07 09:15:25 9750 [ERROR] /usr/sbin/mysqld: Table './c12stevemorris/wp_options' is marked as crashed and should be repaired
    2014-05-07 09:15:25 9750 [Warning] Checking table:   './c12stevemorris/wp_options'
    2014-05-07 09:19:02 9750 [ERROR] /usr/sbin/mysqld: Table './c10pasconwp/wp_options' is marked as crashed and should be repaired
    2014-05-07 09:19:02 9750 [Warning] Checking table:   './c10pasconwp/wp_options'
    2014-05-07 09:19:04 9750 [ERROR] /usr/sbin/mysqld: Table './c10pasconwp/wp_posts' is marked as crashed and should be repaired
    2014-05-07 09:19:04 9750 [Warning] Checking table:   './c10pasconwp/wp_posts'
    2014-05-07 09:19:04 9750 [ERROR] /usr/sbin/mysqld: Table './c10pasconwp/wp_postmeta' is marked as crashed and should be repaired
    2014-05-07 09:19:04 9750 [Warning] Checking table:   './c10pasconwp/wp_postmeta'
    2014-05-07 09:19:51 9750 [ERROR] /usr/sbin/mysqld: Table './c7sutton/wp_options' is marked as crashed and should be repaired
    2014-05-07 09:19:51 9750 [Warning] Checking table:   './c7sutton/wp_options'
    2014-05-07 09:19:51 9750 [ERROR] /usr/sbin/mysqld: Table './c7sutton/wp_usermeta' is marked as crashed and should be repaired
    2014-05-07 09:19:51 9750 [Warning] Checking table:   './c7sutton/wp_usermeta'

    I've tried to attach the entire error log, but I'm getting "Invalid File".

    I'm out of ideas, I have tried searching the bug tracker but nothing obvious.

  • #2
    Perhaps you ran out of file handlers. You can increase the number of file handlers for the user mysql in the file /etc/security/limits.conf

    Comment


    • #3
      Check the system uptime to see if the VM itself is restarting or just MySQL.

      If that's not it, my guess would be you are running out of memory. You have the buffer pool set to ~71% of your total memory, and since it's a VM you probably do not get the full 2GB to start with. So I would check to see if you are swapping, and perhaps lower the buffer pool size and see if it still restarts over night.

      Comment

      Working...
      X