Published 3 months ago by deansatch
I see this error in my log files randomly, but quite often. Any idea what would cause this sporadically?
2018-02-12T07:41:36.444949Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2018-02-12T07:41:36.445215Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 2018-02-12T07:41:36.790070Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2018-02-12T07:41:36.794459Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.20-0ubuntu0.16.04.1) starting as process 1418 ... 2018-02-12T07:41:36.846238Z 0 [Note] InnoDB: PUNCH HOLE support available 2018-02-12T07:41:36.846293Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2018-02-12T07:41:36.846299Z 0 [Note] InnoDB: Uses event mutexes 2018-02-12T07:41:36.846318Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2018-02-12T07:41:36.846324Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8 2018-02-12T07:41:36.846328Z 0 [Note] InnoDB: Using Linux native AIO 2018-02-12T07:41:36.852588Z 0 [Note] InnoDB: Number of pools: 1 2018-02-12T07:41:36.853962Z 0 [Note] InnoDB: Using CPU crc32 instructions 2018-02-12T07:41:36.871179Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2018-02-12T07:41:36.871573Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12 2018-02-12T07:41:36.871602Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool 2018-02-12T07:41:36.871622Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error 2018-02-12T07:41:36.871635Z 0 [ERROR] Plugin 'InnoDB' init function returned error. 2018-02-12T07:41:36.871643Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2018-02-12T07:41:36.871651Z 0 [ERROR] Failed to initialize plugins. 2018-02-12T07:41:36.871657Z 0 [ERROR] Aborting 2018-02-12T07:41:36.871839Z 0 [Note] Binlog end 2018-02-12T07:41:36.872236Z 0 [Note] Shutting down plugin 'CSV' 2018-02-12T07:41:36.872259Z 0 [Note] Shutting down plugin 'MyISAM' 2018-02-12T07:41:36.873610Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
2018-02-12T07:41:36.871179Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2018-02-12T07:41:36.871573Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12 2018-02-12T07:41:36.871602Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
That's where it goes down.... Your server seems to be running out of memory.
Either add more memory to it, or add swap space, or change the MySQL settings to use less memory.
It's hard to optimize MySQL without knowing any numbers.
Setting your innodb buffer pool size to 64MB instead of the default 128MB would help you get rid of this problem, but it would probably slow down your queries as well.
Here are a few nice hints on optimizing MySQL for InnoDB tables. https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/
Can you check the following of your server:
When we know some specs of the server and it's availability of those resources we can start optimizing the DB and perhaps some of the other processes.
@lostdreamer_nl I read that swap files are a bad idea on ssd as it will kill the drive by exceeding the write limits?
Current server is just a 512MB ram but I will be putting up to 1gb tonight.
Nothing seems to use a big chunk of memory %mem is at a steady 0-1. Apache2 occasionally hits 6% and when I load a page mysqld hits 30% and about 10 apache2 processes appear around 6% each
KiB Mem : 500064 total, 61532 free, 274840 used, 163692 buff/cache KiB Swap: 0 total, 0 free, 0 used. 164500 avail Mem
yeah, that will be it than. 512 MB for a server is next to nothing. (right now only 61MB is free to use)
When apache will get a lot of requests, it will start spawning more of those processes, each costing a few (dozen) MB's
With MySQL also taking a 128MB chunk and PHP also needing some, this could get troublesome in no time.
About the swap, it depends: It wont kill your ssd drive very fast these days, but money wise you're better off getting more RAM.
I'd first start with enabling the 'mysql slow query log' see what comes up. https://www.google.nl/search?q=mysql+slow+query+log
set it on half a second and after a day or 2 check which queries are the slowest (these are mostly the ones where complete table filtering was needed and thus are using a lot of memory).
After that you could use some memory profiler for finding out which methods are the culprits for memory usage: for instance https://github.com/arnaud-lb/php-memory-profiler