Question

Mysql shut down problem In Ubuntu

Posted March 11, 2020 182 views
MySQL

Hi

While my external app is working on server. It always shut down and failed to connect again.

What is the problem.
thanks

Error log

2020-03-11T15:35:38.507160Z 0 [Warning] Changed limits: maxopenfiles: 1024 (requested 5000)
2020-03-11T15:35:38.511341Z 0 [Warning] Changed limits: tableopencache: 431 (requested 2000)
2020-03-11T15:35:39.657307Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicitdefaultsfortimestamp server option (see documentation for more details).
2020-03-11T15:35:39.683864Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-0ubuntu0.16.04.1) starting as process 3244 …
2020-03-11T15:35:39.776947Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-03-11T15:35:39.779305Z 0 [Note] InnoDB: Mutexes and rw
locks use GCC atomic builtins
2020-03-11T15:35:39.779410Z 0 [Note] InnoDB: Uses event mutexes
2020-03-11T15:35:39.779497Z 0 [Note] InnoDB: GCC builtin _atomicthread_fence() is used for memory barrier
2020-03-11T15:35:39.779580Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2020-03-11T15:35:39.779662Z 0 [Note] InnoDB: Using Linux native AIO
2020-03-11T15:35:39.780503Z 0 [Note] InnoDB: Number of pools: 1
2020-03-11T15:35:39.781534Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-03-11T15:35:39.805432Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-03-11T15:35:39.811489Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2020-03-11T15:35:39.811599Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2020-03-11T15:35:39.811690Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-03-11T15:35:39.811895Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.
2020-03-11T15:35:39.812001Z 0 [ERROR] Plugin 'InnoDB’ registration as a STORAGE ENGINE failed.
2020-03-11T15:35:39.812066Z 0 [ERROR] Failed to initialize builtin plugins.
2020-03-11T15:35:39.812127Z 0 [ERROR] Aborting

2020-03-11T15:35:39.812199Z 0 [Note] Binlog end
2020-03-11T15:35:39.812673Z 0 [Note] Shutting down plugin 'MyISAM’
2020-03-11T15:35:39.819977Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
2 answers

Hello, @csselo

Looking at the provided output from the error_log it seems like the droplet/server is running out of memory:

2020-03-11T15:35:39.811599Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool

You can check if you’ve assigned a value that is too high (compared to the droplet’s total RAM/memory). You can check the available free memory with this command:

free -m

You can check the value of buffer pool size in /etc/my.cnf less than amount of free RAM:

cat /etc/my.cnf
innodb_buffer_pool_size=100M

Once you’ve set the value of the innodbbufferpool_size to be less than the available RAM on the droplet you can restart the mysql service:

systemctl restart mysql

Hope this helps!

Regards,
Alex

  • now

    mysql> SELECT @@innodbbufferpoolsize/1024/1024;
    +————————————-+
    | @@innodb
    bufferpoolsize/1024/1024 |
    +————————————-+
    | 100.00000000 |
    +————————————-+
    1 row in set (0.00 sec)

    and

              total        used        free      shared  buff/cache   available
    

    Mem: 488 363 11 0 112 90
    Swap: 4095 637 3458

    but it crashed yesterday

    • Hello, @csselo

      I will recommend you to inspect/monitor the memory usage on the droplet and see if there is a service that is consuming the memory and causing the issue here.

      You can monitor the server performance and the memory usage top or htop and see if there is a service that is consuming the memory, like Apache. You can examine the logs for killed processes as well:

      grep -i kill /var/log/messages
      

      My suggestion will be to decrease the innodb_buffer_pool_size and test again. You can also consider upgrading the droplet’s RAM.

      Hope this helps. Let me know how it goes.

      Regards,
      Alex

      • thanks for all but there is no messages in this path and now error log is

        2020-03-16T12:26:15.007252Z 0 [Note] InnoDB: PUNCH HOLE support available
        2020-03-16T12:26:15.007310Z 0 [Note] InnoDB: Mutexes and rwlocks use GCC atomic builtins
        2020-03-16T12:26:15.007319Z 0 [Note] InnoDB: Uses event mutexes
        2020-03-16T12:26:15.007327Z 0 [Note] InnoDB: GCC builtin _
        atomicthreadfence() is used for memory barrier
        2020-03-16T12:26:15.007334Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
        2020-03-16T12:26:15.007341Z 0 [Note] InnoDB: Using Linux native AIO
        2020-03-16T12:26:15.008141Z 0 [Note] InnoDB: Number of pools: 1
        2020-03-16T12:26:15.009135Z 0 [Note] InnoDB: Using CPU crc32 instructions
        2020-03-16T12:26:15.056083Z 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
        2020-03-16T12:26:15.056404Z 0 [ERROR] InnoDB: mmap(107380736 bytes) failed; errno 12
        2020-03-16T12:26:15.056438Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
        2020-03-16T12:26:15.056448Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
        2020-03-16T12:26:15.056557Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.
        2020-03-16T12:26:15.056571Z 0 [ERROR] Plugin 'InnoDB’ registration as a STORAGE ENGINE failed.
        2020-03-16T12:26:15.056582Z 0 [ERROR] Failed to initialize builtin plugins.

        • Hello, @csselo

          Looking the provided output the error is still the same:

          2020-03-16T12:26:15.056438Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
          

          Can you try to set the innodbbufferpool_size value to something lower? As I’ve already mentioned you can check the available free memory on your droplet and set the value of the buffer pool size accordingly

          innodb_buffer_pool_size=50M
          

          You can also consider upgrading the memory of your droplet as well.

          Hope this helps.

          Regards,
          Alex

Hello, @csselo

You will need to sort out the memory usage in order to resolve the problem with the MySQL service. As already mentioned you can monitor the server/droplet’s performance using top / htop and see the available free memory with free -m

You can check if the web server is busy handling requests and thus causing the memory exhaustion or if any other process is consuming the memory.

You can consider a memory upgrade as well, but I’ll suggest that you should check everything else before going for a memory upgrade.

Let me know how it goes.

Regards,
Alex

Submit an Answer