Проблемы с MariaDB

[vitaliy812]

Пользуюсь Vesta больше двух лет, никаких нареканий не возникало, но с сентября все стало плохо.

Причина:
Для большинства своих проектов использую сервер на DigitalOcean(хватает стандартного на 512мб)+Vesta CP и на неё уже накатываю Wordpress.
Всегда использовал стандартный конфиг.

Я так понимаю, сентября в стандартной конфигурации поменяли DB на MariaDB. Проблема в том, что почему-то резко возросло потребление RAM.
Пример.
1. Прошлая версия Vesta c MySQL


2. Один из моих новых проектов с MariaDB


Что нужно сделать, чтобы уменьшить потребление памяти?
Если ничего сделать нельзя, то как сделать так, чтобы при установке Vesta можно было выбрать mySQL а не MariaDB

Буду рад любой помощи, спасибо.

[demian]

поиск по форуму
viewtopic.php?f=29&t=4564#p9514

[PeaceData]

Если включена опция performance_schema, то стоит отключить.
Проверка командой mysql -e 'show variables like "performance_schema"'
В my.cnf добавить performance_schema=0

[demian]

Если включена опция performance_schema, то стоит отключить.
Проверка командой mysql -e 'show variables like "performance_schema"'
В my.cnf добавить performance_schema=0

а если чутка подумать? прежде чем советовать.
ТС по руски написал Wordpress, движком используется performance_schema для оптимизации запросов.

[vitaliy812]

поиск по форуму
viewtopic.php?f=29&t=4564#p9514

Окей, я гляну.

Но вообще часть вопроса была в другом
Можно ли сделать так, чтобы по дефолту ставился mySQL а не Maria? Я понимаю, MariaDB вроде как лучше, но меня вполне устраивало то, что было раньше.
А то для каждого проекта возиться с конфигом БД – то ещё удовольствие.

[demian]

конфиги в части потребления памяти сервером бд у мариидб и мускула одинаковые.
мариа быстрее и безопаснее, т.к. выход обновлений безопасности на марии почти моментальный по сравнению мускулом
если хотите что из-за быстродействия сервера BD проект не страдал, так и так придется оптимизировать базу под проект. с ростом проекта растет база растут индексы. поэтому придется корректировать key_bufer_size, query_cache_size и tmp_table_size, главное помните не задирать два последних больше 128M, производительность пойдет в негатив

[mamanov]

Постоянно падает база, раньше такого не было. Помогите как исправить.

Code: Select all

[size=85]180118 07:07:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180118  7:07:31 InnoDB: The InnoDB memory heap is disabled
180118  7:07:31 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180118  7:07:31 InnoDB: Compressed tables use zlib 1.2.7
180118  7:07:31 InnoDB: Using Linux native AIO
180118  7:07:31 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 14497 ...
180118  7:07:31 InnoDB: Initializing buffer pool, size = 128.0M
180118  7:07:31 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
180118  7:07:31  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
180118  7:07:31  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
180118  7:07:31  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
180118  7:07:32  InnoDB: Waiting for the background threads to start
180118  7:07:33 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 0
180118  7:07:33 [Note] Plugin 'FEEDBACK' is disabled.
180118  7:07:33 [Note] Server socket created on IP: '0.0.0.0'.
180118  7:07:33 [Note] Event Scheduler: Loaded 0 events
180118  7:07:33 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
180118  7:40:52 [Warning] IP address '222.186.50.187' could not be resolved: Name or service not known
180118  9:11:33 [Warning] IP address '123.249.76.126' could not be resolved: Name or service not known
180118  9:47:42 [Warning] IP address '222.187.108.50' could not be resolved: Name or service not known
180118 10:15:30 [Warning] IP address '103.45.1.224' could not be resolved: Name or service not known
180118 11:38:24 [Warning] IP address '125.77.29.24' could not be resolved: Name or service not known
180118 11:38:34 [Warning] IP address '118.184.46.46' could not be resolved: Name or service not known
180118 12:56:20 [Warning] IP address '58.218.200.13' could not be resolved: Name or service not known
180118 13:05:06 [Warning] IP address '196.52.43.124' could not be resolved: Name or service not known
180118 14:10:53 [Warning] IP address '222.186.59.9' could not be resolved: Name or service not known
180118 14:16:15 [Warning] IP address '117.21.191.107' could not be resolved: Name or service not known
180118 16:00:23 [Warning] IP address '183.61.252.25' could not be resolved: Name or service not known
180118 17:18:08 [Warning] IP address '27.148.158.163' could not be resolved: Name or service not known
180118 18:47:11 [Warning] IP address '122.114.92.31' could not be resolved: Name or service not known
180118 21:25:43 [Warning] IP address '43.226.65.37' could not be resolved: Name or service not known
180118 21:30:42 [Warning] IP address '222.186.21.220' could not be resolved: Name or service not known
180118 23:36:36 [Warning] IP address '58.218.199.221' could not be resolved: Name or service not known
180119  7:40:15 [Warning] IP address '218.17.25.173' could not be resolved: Name or service not known
180119  9:09:32 [Warning] IP address '183.131.83.187' could not be resolved: Name or service not known
180119  9:16:49 [Warning] IP address '116.53.196.106' has been resolved to the host name '106.196.53.116.broad.km.yn.dynamic.163data.com.cn', which resembles IPv4-address itself.
180119  9:55:05 [Warning] IP address '123.249.76.132' could not be resolved: Name or service not known
180119 12:15:55 [Warning] IP address '222.186.138.72' could not be resolved: Name or service not known
180119 12:49:34 [Warning] IP address '222.186.59.197' could not be resolved: Name or service not known
180119 14:44:15 [Warning] IP address '122.114.143.177' could not be resolved: Name or service not known
180119 15:52:03 [Warning] IP address '182.18.23.125' could not be resolved: Name or service not known
180119 19:00:02 [Warning] IP address '123.249.13.76' could not be resolved: Name or service not known
180119 19:00:57 [Warning] IP address '221.229.166.121' could not be resolved: Name or service not known
180120  0:06:14 [Warning] IP address '222.186.172.41' could not be resolved: Name or service not known
180120  0:36:44 [Warning] IP address '118.123.15.32' could not be resolved: Name or service not known
180120  4:16:24 [Warning] IP address '118.184.38.102' could not be resolved: Name or service not known
180120  5:53:51 [Warning] IP address '222.186.46.6' could not be resolved: Name or service not known
180120  9:16:54 [Warning] IP address '85.93.20.74' could not be resolved: Name or service not known
180120 11:31:11 [Warning] IP address '222.186.46.15' could not be resolved: Name or service not known
180120 12:07:16 [Warning] IP address '221.229.166.125' could not be resolved: Name or service not known
180120 16:02:20 [Warning] IP address '209.126.136.5' could not be resolved: Name or service not known
180120 16:28:57 [Warning] IP address '122.114.70.13' could not be resolved: Name or service not known
180120 19:33:52 [Warning] IP address '164.52.6.148' could not be resolved: Name or service not known
180120 19:33:52 [Warning] IP address '164.52.6.148' could not be resolved: Name or service not known
180120 23:25:01 [Warning] IP address '196.52.43.65' could not be resolved: Name or service not known
180120 23:39:36 mysqld_safe Number of processes running now: 0
180120 23:39:36 mysqld_safe mysqld restarted
180120 23:39:36 InnoDB: The InnoDB memory heap is disabled
180120 23:39:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180120 23:39:36 InnoDB: Compressed tables use zlib 1.2.7
180120 23:39:36 InnoDB: Using Linux native AIO
180120 23:39:36 InnoDB: Initializing buffer pool, size = 128.0M
180120 23:39:36 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 21026 ...
180120 23:39:36 InnoDB: Completed initialization of buffer pool
180120 23:39:36 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!
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
180120 23:39:37  InnoDB: Waiting for the background threads to start
180120 23:39:38 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 72471803
180120 23:39:38 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
180120 23:39:38 [Note] Plugin 'FEEDBACK' is disabled.
180120 23:39:38 [Note] Server socket created on IP: '0.0.0.0'.
180120 23:39:38 [Note] Event Scheduler: Loaded 0 events
180120 23:39:38 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
180121  2:03:11 [Warning] IP address '222.186.46.15' could not be resolved: Name or service not known
180121  2:34:07 [Warning] IP address '222.186.138.72' could not be resolved: Name or service not known
180121  9:35:35 [Warning] IP address '211.150.68.160' could not be resolved: Name or service not known
180121 11:26:10 mysqld_safe Number of processes running now: 0
180121 11:26:10 mysqld_safe mysqld restarted
180121 11:26:10 InnoDB: The InnoDB memory heap is disabled
180121 11:26:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180121 11:26:10 InnoDB: Compressed tables use zlib 1.2.7
180121 11:26:10 InnoDB: Using Linux native AIO
180121 11:26:10 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 27498 ...
180121 11:26:10 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137756672 bytes) failed; errno 12
180121 11:26:10 InnoDB: Completed initialization of buffer pool
180121 11:26:10 InnoDB: Fatal error: cannot allocate memory for the buffer pool
180121 11:26:10 [ERROR] Plugin 'InnoDB' init function returned error.
180121 11:26:10 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180121 11:26:10 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
180121 11:26:10 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
180121 11:26:10 [Note] Plugin 'FEEDBACK' is disabled.
180121 11:26:10 [ERROR] Unknown/unsupported storage engine: InnoDB
180121 11:26:10 [ERROR] Aborting

180121 11:26:10 [Note] /usr/libexec/mysqld: Shutdown complete

180121 11:26:10 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
180121 11:27:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180121 11:27:23 InnoDB: The InnoDB memory heap is disabled
180121 11:27:23 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180121 11:27:23 InnoDB: Compressed tables use zlib 1.2.7
180121 11:27:23 InnoDB: Using Linux native AIO
180121 11:27:23 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 28233 ...
180121 11:27:23 InnoDB: Initializing buffer pool, size = 128.0M
180121 11:27:23 InnoDB: Completed initialization of buffer pool
180121 11:27:23 InnoDB: highest supported file format is Barracuda.
180121 11:27:23  InnoDB: Starting crash recovery from checkpoint LSN=89687254
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
180121 11:27:23  InnoDB: Starting final batch to recover 1 pages from redo log
180121 11:27:24  InnoDB: Waiting for the background threads to start
180121 11:27:25 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 89688362
180121 11:27:25 [Note] Plugin 'FEEDBACK' is disabled.
180121 11:27:25 [Note] Server socket created on IP: '0.0.0.0'.
180121 11:27:25 [Note] Event Scheduler: Loaded 0 events
180121 11:27:25 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
180121 12:42:28 [Warning] IP address '110.249.137.30' could not be resolved: Name or service not known
180121 14:50:31 [Warning] IP address '182.18.22.249' could not be resolved: Name or service not known
180121 16:33:31 [Warning] IP address '58.218.200.28' could not be resolved: Name or service not known
180121 16:56:05 [Warning] IP address '222.186.50.187' could not be resolved: Name or service not known
180121 18:54:27 [Warning] IP address '222.186.138.72' could not be resolved: Name or service not known
180121 19:29:25 [Warning] IP address '222.186.46.6' could not be resolved: Name or service not known
180121 21:31:29 [Warning] IP address '111.67.198.249' could not be resolved: Name or service not known
180121 22:06:12 [Warning] IP address '222.186.174.91' could not be resolved: Name or service not known
180121 23:33:32 [Warning] IP address '219.137.226.75' could not be resolved: Name or service not known
180121 23:45:06 [Warning] IP address '85.93.20.74' could not be resolved: Name or service not known
180122 00:44:35 mysqld_safe Number of processes running now: 0
180122 00:44:35 mysqld_safe mysqld restarted
180122  0:44:35 InnoDB: The InnoDB memory heap is disabled
180122  0:44:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180122  0:44:35 InnoDB: Compressed tables use zlib 1.2.7
180122  0:44:35 InnoDB: Using Linux native AIO
180122  0:44:35 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137756672 bytes) failed; errno 12
180122  0:44:35 InnoDB: Completed initialization of buffer pool
180122  0:44:35 InnoDB: Fatal error: cannot allocate memory for the buffer pool
180122  0:44:35 [ERROR] Plugin 'InnoDB' init function returned error.
180122  0:44:35 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180122  0:44:35 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 11107 ...
180122  0:44:35 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
180122  0:44:35 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
180122  0:44:35 [ERROR] mysqld: Out of memory (Needed 72499200 bytes)
180122  0:44:35 [Note] Plugin 'FEEDBACK' is disabled.
180122  0:44:35 [ERROR] Unknown/unsupported storage engine: InnoDB
180122  0:44:35 [ERROR] Aborting

180122  0:44:35 [Note] /usr/libexec/mysqld: Shutdown complete

180122 00:44:35 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
180122 07:47:21 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180122  7:47:21 InnoDB: The InnoDB memory heap is disabled
180122  7:47:21 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180122  7:47:21 InnoDB: Compressed tables use zlib 1.2.7
180122  7:47:21 InnoDB: Using Linux native AIO
180122  7:47:21 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137756672 bytes) failed; errno 12
180122  7:47:21 InnoDB: Completed initialization of buffer pool
180122  7:47:21 InnoDB: Fatal error: cannot allocate memory for the buffer pool
180122  7:47:21 [ERROR] Plugin 'InnoDB' init function returned error.
180122  7:47:21 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180122  7:47:21 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 1252 ...
180122  7:47:21 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
180122  7:47:21 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
180122  7:47:21 [ERROR] mysqld: Out of memory (Needed 72499200 bytes)
180122  7:47:22 [Note] Plugin 'FEEDBACK' is disabled.
180122  7:47:22 [ERROR] Unknown/unsupported storage engine: InnoDB
180122  7:47:22 [ERROR] Aborting

180122  7:47:22 [Note] /usr/libexec/mysqld: Shutdown complete

180122 07:47:22 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
180122 07:47:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180122  7:47:27 InnoDB: The InnoDB memory heap is disabled
180122  7:47:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180122  7:47:27 InnoDB: Compressed tables use zlib 1.2.7
180122  7:47:27 InnoDB: Using Linux native AIO
180122  7:47:27 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 1855 ...
180122  7:47:27 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137756672 bytes) failed; errno 12
180122  7:47:27 InnoDB: Completed initialization of buffer pool
180122  7:47:27 InnoDB: Fatal error: cannot allocate memory for the buffer pool
180122  7:47:27 [ERROR] Plugin 'InnoDB' init function returned error.
180122  7:47:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180122  7:47:27 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
180122  7:47:27 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
180122  7:47:27 [ERROR] mysqld: Out of memory (Needed 72499200 bytes)
180122  7:47:27 [Note] Plugin 'FEEDBACK' is disabled.
180122  7:47:27 [ERROR] Unknown/unsupported storage engine: InnoDB
180122  7:47:27 [ERROR] Aborting

180122  7:47:27 [Note] /usr/libexec/mysqld: Shutdown complete

180122 07:47:27 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
180122 07:47:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180122  7:47:32 InnoDB: The InnoDB memory heap is disabled
180122  7:47:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180122  7:47:32 InnoDB: Compressed tables use zlib 1.2.7
180122  7:47:32 InnoDB: Using Linux native AIO
180122  7:47:32 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 2456 ...
180122  7:47:32 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137756672 bytes) failed; errno 12
180122  7:47:32 InnoDB: Completed initialization of buffer pool
180122  7:47:32 InnoDB: Fatal error: cannot allocate memory for the buffer pool
180122  7:47:32 [ERROR] Plugin 'InnoDB' init function returned error.
180122  7:47:32 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180122  7:47:32 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
180122  7:47:32 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
180122  7:47:32 [ERROR] mysqld: Out of memory (Needed 72499200 bytes)
180122  7:47:32 [Note] Plugin 'FEEDBACK' is disabled.
180122  7:47:32 [ERROR] Unknown/unsupported storage engine: InnoDB
180122  7:47:32 [ERROR] Aborting

180122  7:47:32 [Note] /usr/libexec/mysqld: Shutdown complete

180122 07:47:32 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
180122 07:47:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180122  7:47:46 InnoDB: The InnoDB memory heap is disabled
180122  7:47:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180122  7:47:46 InnoDB: Compressed tables use zlib 1.2.7
180122  7:47:46 InnoDB: Using Linux native AIO
180122  7:47:46 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 3501 ...
180122  7:47:46 InnoDB: Initializing buffer pool, size = 128.0M
180122  7:47:46 InnoDB: Completed initialization of buffer pool
180122  7:47:46 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!
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
180122  7:47:46  InnoDB: Waiting for the background threads to start
180122  7:47:47 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 111218277
180122  7:47:47 [Note] Plugin 'FEEDBACK' is disabled.
180122  7:47:47 [Note] Server socket created on IP: '0.0.0.0'.
180122  7:47:47 [Note] Event Scheduler: Loaded 0 events
180122  7:47:47 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
180122  8:17:07 [Warning] IP address '43.226.33.120' could not be resolved: Name or service not known
180122  8:52:25 [Warning] IP address '222.186.138.72' could not be resolved: Name or service not known
180122  9:04:11 [Warning] IP address '211.150.68.160' could not be resolved: Name or service not known
180122  9:34:34 [Warning] IP address '222.186.50.187' could not be resolved: Name or service not known[/size]