4

I'm away on leave and mysql on an EC2 (m3.medium general purpose) based server I have The displeasure of managing has suddenly started crashing. I have very intermittent internet connectivity through my phone to restart the whole server (which does resolve issue temporarily for a few hours). Running mysql 5.5 Apache 2.2 PHP 5.3 Instance has 3.75 GB memory.

As a very short term bodge to see us through this situation until I can get back and sort things properly I ran up a cron script that restarted mysqld if it became unavailable this worked a little, MySQL won't restart at all without a server restart so that idea is defunct.

I am still pretty new to db administration and have optimised my.cnf as best as I could understand - We are running magento which is very demanding.

A side note is that Yesterday I noticed that there were hundreds of sleeping processes from the magento site and wrote another script that would kill these if they had been around for ages- again a temp fix while I am away.

Can anyone advise what the issue causing the crashes might be or how to gain more insight? It looks to me that it is to do with innodb buffer pool but will reducing this help?

Error_log from the failed attempts to automatically restart.

150903 10:15:06 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
150903 10:20:12 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150903 10:20:14 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
150903 10:20:14 [Note] Plugin 'FEDERATED' is disabled.
150903 10:20:15 InnoDB: The InnoDB memory heap is disabled
150903 10:20:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150903 10:20:15 InnoDB: Compressed tables use zlib 1.2.7
150903 10:20:15 InnoDB: Using Linux native AIO
150903 10:20:16 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
150903 10:20:16 InnoDB: Completed initialization of buffer pool
150903 10:20:16 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150903 10:20:17 [ERROR] Plugin 'InnoDB' init function returned error.
150903 10:20:17 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150903 10:20:17 [ERROR] Unknown/unsupported storage engine: InnoDB
150903 10:20:17 [ERROR] Aborting

This happened before when my restart script triggered but this restart was successful. - the number of processes running now:0 looks significant but I don't know what it is telling me about what has happened.

150902 10:47:31 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150902 10:47:31 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
150902 10:47:31 [Note] Server socket created on IP: '0.0.0.0'.
150902 10:47:31 [Note] Event Scheduler: Loaded 1 event
150902 10:47:31 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.42-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
150903 08:30:14 mysqld_safe Number of processes running now: 0
150903 08:30:14 mysqld_safe mysqld restarted
150903  8:30:15 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
150903  8:30:15 [Note] Plugin 'FEDERATED' is disabled.

My.cnf

[client]
default-character-set = utf8mb4

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/var/tmp
# Disabling symbolic-links is recommended to prevent assorted security  risks
symbolic-links=0
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8mb4'
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

#mysqtuner recommendations
log_slow_queries = 1
slow_query_log_file = /var/log/mysql.slow-queries.log
key_buffer_size = 17M
max_connections = 1000
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd uni file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
default-character-set = utf8mb4

[innodb]
innodb_large_prefix = on
innodb_file_format=BARRACUDA
##add these for magento
innodb_buffer_pool_size = 1.5G
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_additional_mem_pool_size = 8M
innodb_thread_concurrency = 48
thread_cache_size = 256
tmp_table_size = 256M

Output from mysqltuner

-] Up for: 1h 24m 55s (227K q [44.630 qps], 12K conn, TX: 1B, RX: 20M)
[--] Reads / Writes: 89% / 11%
[--] Binary logging is disabled
[--] Total buffers: 305.0M global + 2.8M per thread (1000 max threads)
[OK] Maximum reached memory usage: 349.0M (9.30% of installed RAM)
[OK] Maximum possible memory usage: 3.0G (81.41% of installed RAM)
[OK] Slow queries: 0% (0/227K)
[OK] Highest usage of available connections: 1% (16/1000)
[OK] Aborted connections: 0.00%  (0/12540)
[OK] Query cache efficiency: 47.7% (26K cached / 55K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 64% (22K on disk / 35K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 1% (400 open / 35K opened)
[OK] Open file limit used: 0% (41/5K)
[OK] Table locks acquired immediately: 99% (37K immediate / 37K locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 17M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (5000) variable 
should be greater than table_open_cache ( 400)

Variables to adjust:
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_open_cache (> 400)

Output of free -m after a restart

Mem:   total       used       free     shared    buffers     cache.
    3752       2508       1244          0        109        861
-/+ buffers/cache:       1537       2215
Swap:         1023          0       1023
  • Does Fatal error: cannot allocate memory for the buffer pool ring a bell? It's look like for me that some other process(es) consume the precious RAM and mysql simply has no place to fit in. – Kondybas Sep 3 '15 at 21:02
  • Obvious when pointed out. Started some monitoring and in particular when mysql had barfed and found that free -m showed maxed out memory and top showed loads of Apache processes chewing up the RAM, swap was also maxed out. Apache is the culprit. am now monitoring server-status and it is showing that after several hrs apache processes with PID of - seem to persist for ages, I suspect these are building up eventually wiping out the ram. Will ask a separate question about this as seems to be off topic for this question and can't get any Google insight on these - process ids. – Tofuwarrior Sep 9 '15 at 18:49
2
150903 10:20:16 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
150903 10:20:16 InnoDB: Completed initialization of buffer pool
150903 10:20:16 InnoDB: Fatal  error: cannot allocate memory for the buffer pool

Note:

OS error code  12:  Not enough space

You have 3.75GB of RAM in a VM? But something is cluttering it. What? Do you have other applications (aside from Apache, PHP) running?

query_cache_size = 128M

Too big, make it only 50M

max_connections = 1000 [OK] Highest usage of available connections: 1% (16/1000)

You are not really needing that many? Suggest changing to 100.

[innodb]

Notice that the buffer_pool_size below that line is ignored. Remove [innodb]; the info should be in [mysqld].

thread_cache_size = 256

10 should suffice in most cases.

tmp_table_size = 256M [!!] Temporary tables created on disk: 64% (22K on disk / 35K total)

That is much too big for almost anyone. That much space can be allocated for a SELECT that needs a tmp table. A complex SELECT might need multiple tmp tables. And that is "per connection". Use 16M.

Looks like you need some better indexes.

[--] InnoDB is disabled.

Try the script again after fixing the memory problem

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8mb4'

Those are not consistent. And, keep in mind that user root does not execute the init_connect.

What version of MySQL/MariaDB are you running?

  • Thankyou for your helpful comment and also for pointing out a number of errors in the config. As noted in comment above Apache is the culprit and I am now pursuing this line of investigation. – Tofuwarrior Sep 9 '15 at 18:50
  • Hi Rick, I realised I didn't answer your question.Runnign mysql 5.5.42 – Tofuwarrior Sep 15 '15 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.