0

My Website is on a VPS with 1 GN RAM and for some reason, it get crashed sometimes automatically. I can the error.log but couldn't understand what the error means and what might be the real cause. Here are the logs below which generated when mysql server crashed last time.

 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 [Note] /usr/sbin/mysqld (mysqld 5.7.18-0ubuntu0.16.04.1) starting as process 27819 ...
[Note] InnoDB: PUNCH HOLE support available
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Uses event mutexes
[Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
[Note] InnoDB: Compressed tables use zlib 1.2.8
[Note] InnoDB: Using Linux native AIO
 [Note] InnoDB: Number of pools: 1
[Note] InnoDB: Using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
 [Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
[Note] InnoDB: Highest supported file format is Barracuda.
[Note] InnoDB: Log scan progressed past the checkpoint lsn 599964175
 [Note] InnoDB: Doing recovery: scanned up to log sequence number 599964380

 [Note] InnoDB: Doing recovery: scanned up to log sequence number 599964380
 [Note] InnoDB: Database was not shutdown normally!
 [Note] InnoDB: Starting crash recovery.
 [Note] InnoDB: Starting an apply batch of log records to the database...
 InnoDB: Progress in percent: 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
[Note] InnoDB: Apply batch completed
[Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
[Note] InnoDB: Creating shared tablespace for temporary tables
[Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
[Note] InnoDB: File './ibtmp1' size is now 12 MB.
[Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
[Note] InnoDB: 32 non-redo rollback segment(s) are active.
[Note] InnoDB: Waiting for purge to start
[Note] InnoDB: 5.7.18 started; log sequence number 599964380
[Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
[Note] Plugin 'FEDERATED' is disabled.
[Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
[Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
[Note]   - '127.0.0.1' resolves to '127.0.0.1';
[Note] Server socket created on IP: '127.0.0.1'.
[Note] InnoDB: Buffer pool(s) load completed at 170609  9:13:52
[Note] Event Scheduler: Loaded 0 events
 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.18-0ubuntu0.16.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
[Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check


[Note] Beginning of list of non-natively partitioned tables
 [Note] End of list of non-natively partitioned tables
 [Note] Access denied for user 'root'@'localhost' (using password: NO)
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_posts' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_posts'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_postmeta' is marked as crashed and should be repaired
[Warning] Checking table:   './table_name/wp_postmeta'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_comments' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_comments'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_commentmeta' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_commentmeta'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_options' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_options'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_term_taxonomy' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_term_taxonomy'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_term_relationships' is marked as crashed and should be repaired
[Warning] Checking table:   './table_name/wp_term_relationships'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_termmeta' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_termmeta'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_users' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_users'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_usermeta' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_usermeta'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_woocommerce_order_items' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_woocommerce_order_items'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_woocommerce_order_itemmeta' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_woocommerce_order_itemmeta'
 [ERROR] /usr/sbin/mysqld: Table './table_name/wp_woocommerce_sessions' is marked as crashed and should be repaired
 [Warning] Checking table:   './table_name/wp_woocommerce_sessions'

Can you please help me decipher the logs and guide me to the solution. Thanks!!

tushar attar
  • 3
  • 1
  • 2

4 Answers4

1

Maybe the MySQL server was killed by the OOM killer. Give a look at dmesg | grep -i memory to check for OOM intervention.

shodanshok
  • 44,038
  • 6
  • 98
  • 162
0

I agree with shodanshok. Your MySQL killed by OOM-killer. If you don't have enabled swap and use Apache on your VPS, it could take all memory and invoke OOM-killer, sometimes it kill MySQL. To check it you could run cat /var/log/syslog | grep kill

Alexander Tolkachev
  • 4,513
  • 3
  • 14
  • 23
  • There is no entry in syslog which include kill – tushar attar Jun 11 '17 at 05:15
  • run dmesg, and then check if mysqld was killed by OOM, or find that part in the mysql error log where mysql crashed. the error log events you pasted only says that mysql did not shutdown correctly – jerichorivera Jun 11 '17 at 07:26
0

Please check for the largest database in the MySQL. You can use du command inside the MySQL data directory. And try disabling the database from the production server. Most probably one of the largest database is causing this. Whenever there is a attempt to use the particular DB can cause this. Further you can track any particular table inside the DB which might be corrupted or contains invalid data.

Karthik
  • 114
  • 4
0

I also agree with what was said here, here's something I read.

OOM Killer

The Linux kernel has a functionality called Out Of Memory Killer (or OOM Killer) responsible for dealing with memory exhaustion. If system reaches a point where it may soon run out of all memory, OOM Killer looks for a process it could kill and ends its life.

 May 16 00:12:33 mysql-server-01 kernel: Out of Memory: Killed process
 3154 (mysqld).

How does it work and why does it often kill MySQL?

OOM Killer uses a heuristic system to choose a processes for termination. It is based on a score associated with each running application, which is calculated by oom_badness() call, formerly named badness(), inside Linux kernel. Those interested in more details can check the source code in mm/oom_kill.c.

The algorithm is relatively simple and usually the more memory a process uses, the higher score it receives, which makes it more likely to be killed. But there are actually more factors that are considered:

  • memory consumption

  • process ownership

  • process age (older kenerls only)

  • CPU time used (older kernels only)

  • process nice value (older kernels only)

  • process flags

  • oom_adj/oom_score_adj setting

    The complete list can be different for different Linux versions as the algorithm was mostly re-written in kernel 2.6.29.

    In the past, the modifiers used to impact the score significantly and for example if a task had niceness above zero, its score was doubled. If it was owned by a privileged user, the score was divided by eight. In new kernels it is no longer the case. For instance, a process belonging to root now only receive a very small bonus of 30 points out of the possible 1000. With these changes the developers wanted a more predictable algorithm and apparently this was the way to achieve that.

    So why does it kill MySQL so often? The answer is simple — because MySQL typically uses the most memory out of all processes running in a system.

Here is the link from where I read this. https://www.psce.com/blog/2012/05/31/mysql-oom-killer-and-everything-related/

Oron Zimmer
  • 154
  • 4