-1

The Out Of Memory killer was killing mariadb every hour or so but not regularly on time like a cron job to restart it. I found it doing a journalctl -n 200 -u mariadb to show the last 200 lines of log entries for mariadb

***** EDIT 10/10/2017 3:33 PM PST STARTS ****

This solution is for a CentOS 7 system. The server continued to kill every 20 minutes to two hours max - so the best solution was and is to upgrade to MariaDB 10.2 (10.2.9 at this writing). The reason is in 10.2 innodb_buffer_pool_size can now be DYNAMIC, thus the biggest and most tempramental memory hog and performance impact is minimized on a lower memory box. In 10.2 some my.cnf commands are ignored and depreciated such that the server intelligently starts in a lower memory footprint (about 2G instead of 12G because of the addition of the memory assignments of the variables of the old 10.1 version - even if only 6G physical memory is available) Also it now uses a "real" Innodb engine which beleive it or not has some quirks and features that did not work in the Xtradb clone version of Innodb.

See and familiarize yourself with mariadb [dot] com/kb/en/library/upgrading-from-mariadb-101-to-mariadb-102/

MySQL has some memory issues which are conventionally usually not reported to the log and cannot be positively discovered by conventional log searching. See bugs [dot] mysql [dot] com/bug.php?id=68514 and mariadb [dot] com/resources/blog/starting-mysql-low-memory-virtual-machines

The path to "UPGRADE" is not intuitively easy however and is "tricky" because there are lots of dependencies which make "Uninstalling" version 10.1 a bit more sour to the taste - as the MariaDB site directs. This way you can partially upgrade files out of order and save from destroying your dependencies.

It is goofy - but the better way is to install the server files in an order which upgrades the server files one at a time.

You CANNOT USE A REPOSITORY to automatically upgrade because it will complain of dependencies and want to keep the existing 10.1 version. You can configure the MariaDB 10.2 repo and you should do so - it just won't work to upgrade automatically without some nudging. You will also need a repo to Percona configured. This is because the MariaDB-libs (also called the "shared" file - now uses Percona's libs.

  1. Stop the server and duplicate the my.cnf file to safety of another name as well as your databases as anyone would know. 2 Manually go to downloads [dot] mariadb [dot] com/MariaDB/mariadb-10.2.9/yum/centos/7.3/x86_64/rpms/ - and leave the window open there - you will find the files needed there. (Yes I know we are on Centos 7.4 - but they labelled it that way.)
  2. If you have the "devel" server file installed - "yum remove" it. It may take one or two files with it as a dependency uninstall - not a big deal. Use "yum" as much as possible or you could end up with a mess between files you cannot uninstall properly.
  3. Assuming you have a ssh window open and root rights - right click on the "shared" file - it is the libs - which will destroy a huge host of depedencies if you removed it - and copy the link from the browser - and paste it to the ssh window
  4. Do a "yum install" in front of it - and paste the link behind it - it will be seen as an "upgrade" when you execute 6 If it fails because of Perconna Server dependency and ONLY a /etc/my.cnf file conflict then use a "rpm -ivh --force" for each file by their exact name.
  5. Make sure the ONLY conflict is the etc/my.cnf file conflict. You can get the proper Percona server shared file at percona [dot] com/downloads/Percona-Server-5.6/LATEST/ <= where "5.6" would be YOUR VERSION of PHP => e.g. percona [dot] com/downloads/Percona-Server-5.6/Percona-Server-5.6.37-82.2/binary/redhat/7/x86_64/Percona-Server-shared-xxxxxxxxxxxxxxx rpm - MAKE SURE IT IS EXACT SAME 'shared" SERVER FILE that was tried to be installed as a depedency.
  6. Next attempt to install the "compat" file from MariaDB - it should fail complaining the OLD VERSION "shared" libs are in the way - even though you just forced it to install. An "install" from RPM versus from YUM via repo are not the same to the YUM system. MAKE note - by copying and pasting the EXACT OLD VERSION file name to be pasted behind a "yum remove" command - and it should allow you to remove JUST the OLD file version.
  7. Then run the yum install of "compat" again and it will register as an upgrade and complete
  8. Then install the 10.2.9 "server" - it too will say it is an "upgrade" but will likely fail because it did not remove the old version of the server. Do the same as above and "yum remove" the old version of the server, and then run the yum install of the new server using a copied repo link behind "yum install"
  9. Now you can re-install the server devel file the same way.
  10. At this point you will have the six required files needed as 10.2.x

    yum list installed | grep MariaDB

    MariaDB-client.x86_64 10.2.9-1.el7.centos installed

    MariaDB-common.x86_64 10.2.9-1.el7.centos installed

    MariaDB-compat.x86_64 10.2.9-1.el7.centos installed

    MariaDB-devel.x86_64 10.2.9-1.el7.centos installed

    MariaDB-server.x86_64 10.2.9-1.el7.centos installed

    MariaDB-shared.x86_64 10.2.9-1.el7.centos installed

  11. Rename or copy your my.cnf file back to replace the one the server file installed - save it as "orig" or something.

  12. Start the server with the systemctl start mariadb.service command and it should start
  13. Then as root RUN "mysql_upgrade" which does two things: Upgrades the permission tables in the mysql database with some new fields Does a very quick check of all tables and marks them as compatible with MariaDB 10.2

The server is back running now upgraded and you only lost maybe one or two files under the devel dependency which you can now "yum" back in.

You will notice the server foot print is now proportional to what the resources are - that is it is a little bit more intelligent in how it starts given the resources. Mine has important things like

Initialized buffer pool, total size = 1G, instances = 8, chunk size = 128M
(which is an intelligent dynamically increaseable InnoDB buffer pool / data size: 1.0G/2.1G - way better than the 3 or 4 G it took to make the site run right - but hogged memory and crashed the MySQL server with regularity)

and mysqltuner.pl reports in part . . .

Physical Memory : 6.0G [--] Max MySQL memory : 1.8G [--] Other process memory: 214.1M [--] Total buffers: 1.2G global + 2.9M per thread (200 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 1.3G (21.48% of installed RAM) [OK] Maximum possible memory usage: 1.8G (29.92% of installed RAM)

which is much better than reporting max MySQL memory at 13G - a recipe for crashing

At least now I have a server more tune-able to get a hold on the issue

***** EDIT 10/10/2017 3:33 PM PST STOPS ****

***** EDIT 9/29/17 5:41 AM PST STARTS ****

Despite all the fixes and edits to the server configuration to clear up start up errors below this EDIT - the issue persists - and now Mariadb 10.1.28 is now installed. See post from Muhamad NuR Hazman

 Sep 29 06:00:38 main mysqld[14419]: Version: '10.1.28-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
 Sep 29 06:00:38 main systemd[1]: Started MariaDB database server.
 Sep 29 06:00:40 main mysqld[14419]: 2017-09-29 06:00:40 7fa023df8700 InnoDB: Buffer pool(s) load completed at 170929  6:00:40
 Sep 29 06:45:34 main systemd[1]: mariadb.service: main process exited, code=killed, status=9/KILL
 Sep 29 06:45:34 main systemd[1]: Unit mariadb.service entered failed state.
 Sep 29 06:45:34 main systemd[1]: mariadb.service failed.
 Sep 29 06:45:39 main systemd[1]: mariadb.service holdoff time over, scheduling restart.
 Sep 29 06:45:39 main systemd[1]: Starting MariaDB database server...
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Note] /usr/sbin/mysqld (mysqld 10.1.28-MariaDB) starting as process 18750 ...
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Warning] option 'innodb-max-dirty-pages-pct': value 0 adjusted to 0.001
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Note] InnoDB: Using mutexes to ref count buffer pool pages
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Note] InnoDB: The InnoDB memory heap is disabled
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Note] InnoDB: Compressed tables use zlib 1.2.7
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Note] InnoDB: Using Linux native AIO
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Note] InnoDB: Using SSE crc32 instructions
 Sep 29 06:45:39 main mysqld[18750]: 2017-09-29  6:45:39 139758649993472 [Note] InnoDB: Initializing buffer pool, size = 2.0G
 Sep 29 06:45:40 main mysqld[18750]: 2017-09-29  6:45:40 139758649993472 [Note] InnoDB: Completed initialization of buffer pool
 Sep 29 06:45:40 main mysqld[18750]: 2017-09-29  6:45:40 139758649993472 [Note] InnoDB: Highest supported file format is Barracuda.
 Sep 29 06:45:40 main mysqld[18750]: 2017-09-29  6:45:40 139758649993472 [Note] InnoDB: Starting crash recovery from checkpoint LSN=21193250279
 Sep 29 06:45:40 main mysqld[18750]: 2017-09-29  6:45:40 139758649993472 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
 Sep 29 06:45:40 main mysqld[18750]: 2017-09-29  6:45:40 139758649993472 [Note] InnoDB: Starting final batch to recover 978 pages from redo log
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Note] InnoDB: 128 rollback segment(s) are active.
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Note] InnoDB: Waiting for purge to start
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.36-82.2 started; log sequence numbe
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Note] Plugin 'FEEDBACK' is disabled.
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Note] Recovering after a crash using tc.log
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Note] Starting crash recovery...
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Note] Crash recovery finished.
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139755712907008 [Note] InnoDB: Dumping buffer pool(s) not yet started
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29 06:45:41 7f1b699f8700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Note] Server socket created on IP: '0.0.0.0'.
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when th
 Sep 29 06:45:41 main mysqld[18750]: 2017-09-29  6:45:41 139758649993472 [Note] /usr/sbin/mysqld: ready for connections.
 Sep 29 06:45:41 main mysqld[18750]: Version: '10.1.28-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
 Sep 29 06:45:41 main systemd[1]: Started MariaDB database server.
 Sep 29 06:45:44 main mysqld[18750]: 2017-09-29 06:45:44 7f1b699f8700 InnoDB: Buffer pool(s) load completed at 170929  6:45:44

I have another identical Mariadb server running - production - and it is not doing doing this - but it also has no "relay" code in it - which I did not put in to the configuration.

As can be seen STILL from the log something is KILLING the server - and it is not an orderly restart

Thus I have taken the steps to disable replication (to nowhere) shown here => How do I completely disable MySQL replication

I will wait and see again

***** EDIT 9/29/17 5:41 AM PST STOPS ****

This is (WAS) a typical log output - notice the time it started and how long it lasted until killed - and auto-restarted

LOG PASTE

 Sep 26 10:50:42 main mysqld[10829]: 2017-09-26 10:50:42 140606795569408 [Note] /usr/sbin/mysqld: ready for connections. 
 Sep 26 10:50:42 main mysqld[10829]: Version: '10.1.27-MariaDB' socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server 
 Sep 26 10:50:42 main systemd[1]: Started MariaDB database server. 
 Sep 26 12:03:13 main systemd[1]: mariadb.service: main process exited, code=killed, status=9/KILL
 Sep 26 12:03:13 main systemd[1]: Unit mariadb.service entered failed state.
 Sep 26 12:03:13 main systemd[1]: mariadb.service failed.
 Sep 26 12:03:18 main systemd[1]: mariadb.service holdoff time over, scheduling restart.
 Sep 26 12:03:18 main systemd[1]: Starting MariaDB database server...
 Sep 26 12:03:19 main mysqld[16333]: 2017-09-26 12:03:19 140206636972288 [Warning] option 'max_allowed_packet': unsigned value 4294967296

I have a cron job that runs a mysqlcheck with optimize on all tables every Sunday at 4 am, and the last run had this restart occur which crashed the Innodb setup by corrupting the tables in the middle of optimization.

I need to stop this regular kill - and it is not my cron - or occurs regularly like a cron job would - it is just approximately about every hour.

Anyone have an idea where I should look or how I can trace the kill to?


System info is a VPS and as reported by Webmin below

  • Operating system: CentOS Linux 7.4.1708 Kernel and CPU Linux 2.6.32-042stab120.16 on x86_64
  • Processor information: Intel(R) Xeon(R) CPU E5-2680 0 @ 2.70GHz, 4 cores
  • Running processes: 110
  • Real memory 5.86 GB total / 2.73 GB used
  • Virtual memory 0 bytes total / 0 bytes used
  • Local disk space 30 GB total / 11.33 GB free / 18.67 GB used

Last line of the log fully read - as posted now from the latest restart it is the same.

I then realized the server was defaulting down to the MAXIMUM allowed for max_allowed_packet - because the my.conf entries were confused from the entries from bytes which should not have had a "M" at the end.

LOG PASTE

Sep 27 02:04:33 main mysqld[25532]: 2017-09-27 2:04:33 140406853101824 [Warning] option 'max_allowed_packet': unsigned value 4294967296 adjusted to 1073741824

Sep 27 02:04:33 main mysqld[25532]: 2017-09-27 2:04:33 140406853101824 [Note] /usr/sbin/mysqld (mysqld 10.1.27-MariaDB) starting as process 25532 ...

Sep 27 02:04:33 main mysqld[25532]: 2017-09-27 2:04:33 140406853101824 [Warning] Could not increase number of max_open_files to more than 16364 (request: 32979)

Sep 27 02:04:34 main mysqld[25532]: 2017-09-27 2:04:34 140406853101824 [Warning] option 'innodb-max-dirty-pages-pct': value 0 adjusted to 0.001

I have already adjusted the mariadb.service file to increase # LimitNOFILE=16364 and to add as below:

LimitNOFILE=65535     
LimitNPROC=65535

To address the second to last line of the log issue above see => https://www.centos.org/forums/viewtopic.php?t=49525

The my.cnf originally had max_allowed_packet=4096M - I have changed it to

[mysqld] 
max_allowed_packet=256M

and

[mysqldump] 
max_allowed_packet=512M

See => https://drupal.stackexchange.com/questions/31890/what-is-a-reasonable-value-for-max-allowed-packet-for-drupal-7-with-moderate-tra

It starts now without errors - so we will see if it still kills and restarts.

The my.cnf is pasted below with most of the comment lines removed for easier reading. This conf file runs and loads with => NO <= errors now.

my.cnf file:

[client-server]

!includedir /etc/my.cnf.d #

MariaDB database server configuration file.

[server] 

# Basic Settings - left to defaults


#  bind-address = 127.0.0.1 <= in my.cnd.d includes

[mysqld]

# Fine Tuning

datadir = /var/lib/MySQL
max_connect_errors = 100000
wait_timeout = 7000
join_buffer_size = 8M
connect_timeout = 600
interactive_timeout = 400
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 256M
thread_cache_size = 128
max_connections = 200
max_user_connections = 150

# MyISAM

key_buffer_size = 128M
myisam_sort_buffer_size = 512M
open_files_limit = 65535
query_cache_type = 1
table_open_cache = 16384
table_definition_cache = 4096
max_join_size = 5M
read_rnd_buffer_size = 64M
query_cache_limit = 256K
query_cache_size = 8M
key_buffer_size = 16M

# Innodb

default_storage_engine = InnoDB
sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
innodb_file_per_table = 1

### un-comment these two below to recover

# innodb_force_recovery = 6    
# innodb_purge_threads = 0

### these two above to recover

### See for below https://mariadb.com/kb/en/mariadb/defragmenting-innodb-tablespaces/

# innodb-defragment = 1

innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_instances = 8
innodb_lock_wait_timeout = 180
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_fast_shutdown = 0

### see How to repair, or drop/create a corrupted table in mysql? for below three commands

innodb_max_dirty_pages_pct = 0
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_large_prefix = true
innodb_file_format = barracuda
innodb_file_per_table = true

[mysqld_safe]  

[myisamchk] write_buffer = 16M
sort_buffer = 64M
read_buffer = 16M
key_buffer = 16M

[mysqlhotcopy] interactive-timeout

[mysqldump] quick quote-names max_allowed_packet = 512M

[mysql]

The max_allowed_packet shown before the edit - which was 4096M was the old amount - it is actually 256M.

An orderly restart now produces this log output below:

Sep 27 14:58:22 main mysqld[2243]: 2017-09-27 14:58:22 7f89ae5f8700 InnoDB: Buffer pool(s) load completed at 170927 14:58:22    
Sep 27 15:15:28 main systemd[1]: Stopping MariaDB database server...
Sep 27 15:15:28 main mysqld[2243]: 2017-09-27 15:15:28 140229506550528 [Note] /usr/sbin/mysqld: Normal shutdown
Sep 27 15:15:28 main mysqld[2243]: 2017-09-27 15:15:28 140229506550528 [Note] Event Scheduler: Purging the queue. 0 events    
Sep 27 15:15:28 main mysqld[2243]: 2017-09-27 15:15:28 140229344212736 [Note] InnoDB: FTS optimize thread exiting.    
Sep 27 15:15:28 main mysqld[2243]: 2017-09-27 15:15:28 140229506550528 [Note] InnoDB: Starting shutdown...    
Sep 27 15:15:28 main mysqld[2243]: 2017-09-27 15:15:28 140229312743168 [Warning] InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool    
Sep 27 15:15:28 main mysqld[2243]: 2017-09-27 15:15:28 140229312743168 [Warning] InnoDB: Buffer pool(s) dump completed at 170927 15:15:28    
Sep 27 15:15:29 main mysqld[2243]: 2017-09-27 15:15:29 140229506550528 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool    
Sep 27 15:15:30 main mysqld[2243]: 2017-09-27 15:15:30 140229506550528 [Note] InnoDB: Shutdown completed; log sequence number 15438587118    
Sep 27 15:15:30 main mysqld[2243]: 2017-09-27 15:15:30 140229506550528 [Note] /usr/sbin/mysqld: Shutdown complete    
Sep 27 15:15:30 main systemd[1]: Starting MariaDB database server...    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] /usr/sbin/mysqld (mysqld 10.1.27-MariaDB) starting as process 5159 ...    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Warning] option 'innodb-max-dirty-pages-pct': value 0 adjusted to 0.001    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: Using mutexes to ref count buffer pool pages    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: The InnoDB memory heap is disabled    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: Compressed tables use zlib 1.2.7    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: Using Linux native AIO    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: Using SSE crc32 instructions    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: Initializing buffer pool, size = 2.0G    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: Completed initialization of buffer pool    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: Highest supported file format is Barracuda.    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: 128 rollback segment(s) are active.    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB: Waiting for purge to start    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.36-82.2 started; log seque    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] Plugin 'FEEDBACK' is disabled.    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140291461838592 [Note] InnoDB: Dumping buffer pool(s) not yet started    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 7f9826bf7700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] Server socket created on IP: '0.0.0.0'.    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may brea    
Sep 27 15:15:31 main mysqld[5159]: 2017-09-27 15:15:31 140294368913664 [Note] /usr/sbin/mysqld: ready for connections.    
Sep 27 15:15:31 main mysqld[5159]: Version: '10.1.27-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server    
Sep 27 15:15:31 main systemd[1]: Started MariaDB database server.    
Sep 27 15:15:33 main mysqld[5159]: 2017-09-27 15:15:33 7f9826bf7700 InnoDB: Buffer pool(s) load completed at 170927 15:15:33

It did still KILL one more time - but it took two hours this time - I will wait and see after this restart.

closed as off-topic by Paul White Dec 13 '17 at 4:58

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • If you want to add a self-answer please do so, but answers do not belong in the question. Also, we don't put edit timestamps or other meta in posts because the system keeps a revision history automatically. – Paul White Oct 11 '17 at 0:49
4

A regression was discovered after the release of MariaDB 10.1.27. It has been pulled from the downloads system, but some mirrors may still have it. Do not download or install this version. Stay with MariaDB 10.1.26 until 10.1.28 is released

Taken from https://mariadb.org/mariadb-10-1-27-now-available/

Update. 10.1.28 has been released https://mariadb.com/kb/en/library/mariadb-10128-release-notes/

1

my.cnf things to do:

  • in [mysqld] section, REMOVE to allow system DEFAULTS to work for you:

    • sort_buffer_size
    • read_rnd_buffer_size
    • join_buffer_size


    The current RAM requested are per connection values and your 5.68 GB of real RAM will not support many connections with their high current values. See MySQLCalculator.com for more likely realistic default values.

    • max_connect_errors = 10 # from 100000 to 10
      attempting to STOP hackers/crackers
    • innodb_buffer_pool_size = 3G # nearly half of available memory
      (consider upgrade, if needed)

For 5 more specific in depth configuration suggestions, please post complete results of

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW ENGINE INNODB STATUS;
  • I am running. The upgrade (above) to MariaDB 10.2.9 - helped - but MariaDB STILL had the largest memory footprint around 3 GB - no matter what - and the Out Of Memory killer (OOM Killer) kept killing it sporadically. so I doubled the ram to 12GB, and was able to then dynamically set innodb_buffer_pool_size = 1G at 4 instances dynamically in 128K chunks and it has not been killed again for 3 days so far. I would need the extra memory anyway to run twindb-backup reliably via cron – wilburunion Oct 22 '17 at 17:41
  • Thanks for the GREAT news, after 7 days of uptime, for 5 more specific in depth configuration suggestions, please post complete results of SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES; SHOW ENGINE INNODB STATUS; – Wilson Hauck Oct 23 '17 at 10:45

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