Background
A MySQL server stop working. You’re confused as this is a new Virtualmin installation and everything went smooth. You start troubleshooting:
# service mariadb status
Redirecting to /bin/systemctl status mariadb.service ● mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled) Active: inactive (dead) since Tue 2019-03-05 05:12:56 UTC; 3h 43min ago Process: 13655 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS) Process: 13654 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS) Process: 13624 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS) Main PID: 13654 (code=exited, status=0/SUCCESS)
# tail -f /var/log/mariadb/mariadb.log
190305 5:12:55 InnoDB: Fatal error: cannot allocate memory for the buffer pool 190305 5:12:55 [ERROR] Plugin 'InnoDB' init function returned error. 190305 5:12:55 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 190305 5:12:55 [Note] Plugin 'FEEDBACK' is disabled. 190305 5:12:55 [ERROR] Unknown/unsupported storage engine: InnoDB 190305 5:12:55 [ERROR] Aborting 190305 5:12:55 [Note] /usr/libexec/mysqld: Shutdown complete 190305 05:12:56 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
Solution
vi /etc/my.cnf and change:
innodb_buffer_pool_size=256M
service mariadb start
Redirecting to /bin/systemctl start mariadb.service
Please note 256M is just a basic value, if you have a lot more RAM, e.g. if you have 8GB, you might allocate up to 1GB or more.
How to check the value:
MariaDB [(none)]> SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'innodb_buffer_pool_size'; +----------------+ | variable_value | +----------------+ | 268435456 | +----------------+ 1 row in set (0.00 sec)
268 435 456 / 1024 / 1024 = 256M
134 217 728 / 1024 / 1024 = 128M
128M appears to be the default.
If you’re not sure where to locate my.cnf, run mysqladmin --help
References
- https://stackoverflow.com/questions/13769598/where-else-can-the-variable-innodb-buffer-pool-size-be-accessed-besides-my-cnf
- https://support.plesk.com/hc/en-us/articles/213370829-MySQL-crashes-with-error-Fatal-error-cannot-allocate-memory-for-the-buffer-pool
- https://stackoverflow.com/questions/2482234/how-do-i-find-the-mysql-my-cnf-location