Hello dear Stackers!
I am having an Issue with my Debian 8 Server VServer (1GB Ram, CPU 2x1GHz) Shared. MariaDB. I got three Wordpress installation that needs to keep running, and a few other Websites. And I cannot figure out what the issue is, this happens every few days, or when a bunch of Clients browser the Wordpress site at once.
I get the error that Wordpress isn't able to connect to the Database (because the DB crashed).
I tried a bunch of different things, one is disabling Performance Schema (= off), which does not change anything. I also changed the MySQL to Mixed, which solved a few warnings (not sure if dirty fix). Wordpress could not solve the issues by fixing the DB automatically (no errors found).
Thanks in advance! :)
A part of the my.cnf:
(...)
max_connections = 100
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M
max_heap_table_size = 32M
(...)
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size = 50M
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
(...)
Journalctl
(...)
Apr 17 23:07:39 Debian-84-jessie-64-LAMP mysqld[618]: 2017-04-17 23:07:39 139847238380288 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe beca
Apr 17 23:20:33 Debian-84-jessie-64-LAMP mysqld[618]: 2017-04-17 23:20:33 139847221811968 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe beca
Apr 18 00:01:56 Debian-84-jessie-64-LAMP mysqld[618]: 2017-04-18 0:01:56 139847220599552 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe beca
Apr 18 00:07:46 Debian-84-jessie-64-LAMP systemd[1]: mariadb.service: main process exited, code=killed, status=9/KILL
Apr 18 00:07:46 Debian-84-jessie-64-LAMP systemd[1]: Unit mariadb.service entered failed state.
Apr 18 00:07:51 Debian-84-jessie-64-LAMP systemd[1]: mariadb.service holdoff time over, scheduling restart.
Apr 18 00:07:51 Debian-84-jessie-64-LAMP systemd[1]: Stopping MariaDB database server...
Apr 18 00:07:51 Debian-84-jessie-64-LAMP systemd[1]: Starting MariaDB database server...
Apr 18 00:07:51 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:51 140160371898304 [Note] /usr/sbin/mysqld (mysqld 10.1.14-MariaDB-1~jessie) starting as process 2956 ...
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [Note] InnoDB: Using mutexes to ref count buffer pool pages
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [Note] InnoDB: The InnoDB memory heap is disabled
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [Note] InnoDB: Memory barrier is not used
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [Note] InnoDB: Compressed tables use zlib 1.2.8
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [Note] InnoDB: Using Linux native AIO
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [Note] InnoDB: Using SSE crc32 instructions
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [Note] InnoDB: Initializing buffer pool, size = 256.0M
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: InnoDB: mmap(276824064 bytes) failed; errno 12
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [ERROR] Plugin 'InnoDB' init function returned error.
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [ERROR] mysqld: Out of memory (Needed 128663552 bytes)
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [Note] Plugin 'FEEDBACK' is disabled.
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [ERROR] Unknown/unsupported storage engine: InnoDB
Apr 18 00:07:52 Debian-84-jessie-64-LAMP mysqld[2956]: 2017-04-18 0:07:52 140160371898304 [ERROR] Aborting
Apr 18 00:07:52 Debian-84-jessie-64-LAMP systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 18 00:07:52 Debian-84-jessie-64-LAMP systemd[1]: Failed to start MariaDB database server.
Apr 18 00:07:52 Debian-84-jessie-64-LAMP systemd[1]: Unit mariadb.service entered failed state.
(MANUALLY RESTARTED SERVER)
1 GB is not much these days. You can still run MariaDB 10.1 on a 1G host of course, but you need to make some adjustments to your configuration file.
Below are some examples how to reduce the memory usage on MariaDB server startup.
Please note that there are different parameters which affect memory usage upon client connections and query execution, but your error log indicates that you are getting the problem on startup.
Please also note that not all of these variables can be reduced at once, and not always can you choose the minimal value. There is no one-for-all advice, it depends on which engines you use, and how you use them. Check documentation for every variable that you intend to adjust to understand what exactly it affects.
Finally, since you have not quoted the whole config, it is possible that you have some other options that actually increase the memory usage. Notes below are based on hardcoded defaults.
Here is my 10.1.21 server started with all defaults (no config options) on a new clean datadir:
ALL DEFAULT OPTIONS
VIRT RES SHR COMMAND
747040 95108 14756 mysqld
It is a baseline for all memory usage examples below.
The default value in 10.1 is 128M. You increased it to 256M, so you lose 128M on it:
ALL DEFAULT OPTIONS + --innodb_buffer_pool_size=256M
VIRT RES SHR COMMAND
888608 104856 14820 mysqld
Instead, you can reduce it, the minimal possible value is 5M:
ALL DEFAULT OPTIONS + --innodb_buffer_pool_size=5M
VIRT RES SHR COMMAND
606752 85928 14848 mysqld
The default value is 128M, the minimum is 128K:
ALL DEFAULT OPTIONS + --aria-pagecache-buffer-size=128K
VIRT RES SHR COMMAND
611872 90004 14932 mysqld
The default value is 128M, minimum according to documentation is 8 (although on my machine everything below 4K is adjusted to 0):
ALL DEFAULT OPTIONS + --key_buffer_size=8
VIRT RES SHR COMMAND
611872 71264 14748 mysqld
The default value is 16M, minimum is 256K:
ALL DEFAULT OPTIONS + --innodb_log_buffer_size=256K
VIRT RES SHR COMMAND
718368 74980 14756 mysqld
You mentioned that you "changed the MySQL to Mixed", and it allowed to get rid of some warnings. Apparently it means that you set binlog_format=mixed, and that you have log-bin enabled (it is disabled by default). Adjusting the binary log format to MIXED is fine, but the binary logging itself uses some memory, even in default configuration:
ALL DEFAULT OPTIONS + --log-bin
VIRT RES SHR COMMAND
747312 95240 14820 mysqld
If you don't need it, you can disable it, it will save you a little bit of memory, and will improve the performance.
In the unlikely case that you don't need InnoDB at all, you can disable it, it will save you a lot of memory:
ALL DEFAULT OPTIONS + --skip-innodb --default-storage-engine=MyISAM
VIRT RES SHR COMMAND
383948 55696 12668 mysqld
So, hypothetically, if you do all of that at once (which you should not!), you can decrease the initial memory allocation significantly:
ALL DEFAULT OPTIONS + --aria-pagecache-buffer-size=128K --innodb_log_buffer_size=256K --key_buffer_size=8 --innodb_buffer_pool_size=5M --skip-innodb --default-storage-engine=MyISAM
VIRT RES SHR COMMAND
113612 26572 12784 mysqld