mardi 3 février 2015

54M InnoDB keeps shutting down unexpectedly


I have a mariadb server running several databases with very small load. From time to time, when I have 20+ users browsing one of the wordpress websites i have, the daemon just shuts down with the following log:



150203 12:33:07 mysqld_safe Number of processes running now: 0
150203 12:33:07 mysqld_safe mysqld restarted
150203 12:33:08 InnoDB: The InnoDB memory heap is disabled
150203 12:33:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150203 12:33:08 InnoDB: Compressed tables use zlib 1.2.7
150203 12:33:08 InnoDB: Using Linux native AIO
150203 12:33:08 InnoDB: Initializing buffer pool, size = 512.0M
InnoDB: mmap(551026688 bytes) failed; errno 12
150203 12:33:08 InnoDB: Completed initialization of buffer pool
150203 12:33:08 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150203 12:33:08 [ERROR] Plugin 'InnoDB' init function returned error.
150203 12:33:08 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150203 12:33:08 [Note] Plugin 'FEEDBACK' is disabled.
150203 12:33:08 [ERROR] Unknown/unsupported storage engine: InnoDB
150203 12:33:08 [ERROR] Aborting

150203 12:33:08 [Note] /usr/libexec/mysqld: Shutdown complete

150203 12:33:08 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
150203 12:37:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150203 12:37:45 InnoDB: The InnoDB memory heap is disabled
150203 12:37:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150203 12:37:45 InnoDB: Compressed tables use zlib 1.2.7
150203 12:37:45 InnoDB: Using Linux native AIO
150203 12:37:45 InnoDB: Initializing buffer pool, size = 512.0M
150203 12:37:45 InnoDB: Completed initialization of buffer pool
150203 12:37:45 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
150203 12:37:45 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
150203 12:37:45 InnoDB: Waiting for the background threads to start
150203 12:37:46 Percona XtraDB (http://www.percona.com) 5.5.37-MariaDB-34.0 started; log sequence number 4997214447
150203 12:37:46 [Note] Plugin 'FEEDBACK' is disabled.
150203 12:37:46 [Note] Server socket created on IP: '0.0.0.0'.
150203 12:37:46 [Note] Event Scheduler: Loaded 0 events
150203 12:37:46 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.37-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server


The problem is my database size is very small (54MB) and im setting the pool size to 512MB which is more than enough, i have no idea why im getting cannot allocate memory for the buffer pool, this is the mysqltuner output as well



-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.37-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 8M (Tables: 200)
[--] Data in InnoDB tables: 54M (Tables: 1100)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 50)
[!!] Total fragmented tables: 1106

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12s (664 q [55.333 qps], 16 conn, TX: 1M, RX: 254K)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 680.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 1.1G (59% of installed RAM)
[OK] Slow queries: 0% (0/664)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Key buffer size / total MyISAM indexes: 128.0M/1.8M
[!!] Query cache efficiency: 12.7% (85 cached / 667 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 26 sorts)
[!!] Temporary tables created on disk: 38% (10 on disk / 26 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 300% (39 open / 13 opened)
[OK] Open file limit used: 2% (21/1K)
[OK] Table locks acquired immediately: 100% (590 immediate / 590 locks)
[!!] Connections aborted: 12%
[OK] InnoDB data size / buffer pool: 54.6M/512.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
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
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_limit (> 2M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)




Aucun commentaire:

Enregistrer un commentaire