Здарова всем.
Может кто подскажет что надо подправить в сервере.
Я уже и конфигурации менял (может что то не то менял), sql запросы сократил по времени обработки и серовно нет нет но служба падает.
Делал логи на запросы которые больше 1 сикунды, все исправил такого уже нет.

Вот логи MariaDB когда падал сервер может что то дельно подскажите.
авторVersion: '5.5.60-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
190130 11:00:32 mysqld_safe Number of processes running now: 0
190130 11:00:32 mysqld_safe mysqld restarted
190130 11:00:32 [Warning] You need to use --log-bin to make --binlog-format work.
190130 11:00:32 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 26258 ...
190130 11:00:33 InnoDB: The InnoDB memory heap is disabled
190130 11:00:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190130 11:00:33 InnoDB: Compressed tables use zlib 1.2.7
190130 11:00:33 InnoDB: Using Linux native AIO
190130 11:00:33 InnoDB: Initializing buffer pool, size = 340.0M
InnoDB: mmap(365920256 bytes) failed; errno 12
190130 11:00:33 InnoDB: Completed initialization of buffer pool
190130 11:00:33 InnoDB: Fatal error: cannot allocate memory for the buffer pool
190130 11:00:33 [ERROR] Plugin 'InnoDB' init function returned error.
190130 11:00:33 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190130 11:00:33 [Note] Plugin 'FEEDBACK' is disabled.
190130 11:00:33 [ERROR] Unknown/unsupported storage engine: InnoDB
190130 11:00:33 [ERROR] Aborting

190130 11:00:33 [Note] /usr/libexec/mysqld: Shutdown complete

190130 11:00:33 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
190130 11:00:34 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
190130 11:00:34 [Warning] You need to use --log-bin to make --binlog-format work.
190130 11:00:34 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 27350 ...
190130 11:00:34 InnoDB: The InnoDB memory heap is disabled
190130 11:00:34 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190130 11:00:34 InnoDB: Compressed tables use zlib 1.2.7
190130 11:00:34 InnoDB: Using Linux native AIO
190130 11:00:34 InnoDB: Initializing buffer pool, size = 340.0M
InnoDB: mmap(365920256 bytes) failed; errno 12
190130 11:00:34 InnoDB: Completed initialization of buffer pool
190130 11:00:34 InnoDB: Fatal error: cannot allocate memory for the buffer pool
190130 11:00:34 [ERROR] Plugin 'InnoDB' init function returned error.
190130 11:00:34 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190130 11:00:34 [Note] Plugin 'FEEDBACK' is disabled.
190130 11:00:34 [ERROR] Unknown/unsupported storage engine: InnoDB
190130 11:00:34 [ERROR] Aborting

190130 11:00:34 [Note] /usr/libexec/mysqld: Shutdown complete

190130 11:00:34 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
190130 11:03:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
190130 11:03:53 [Warning] You need to use --log-bin to make --binlog-format work.
190130 11:03:53 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 28531 ...
190130 11:03:53 InnoDB: The InnoDB memory heap is disabled
190130 11:03:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190130 11:03:53 InnoDB: Compressed tables use zlib 1.2.7
190130 11:03:53 InnoDB: Using Linux native AIO
190130 11:03:53 InnoDB: Initializing buffer pool, size = 340.0M
InnoDB: mmap(365920256 bytes) failed; errno 12
190130 11:03:53 InnoDB: Completed initialization of buffer pool
190130 11:03:53 InnoDB: Fatal error: cannot allocate memory for the buffer pool
190130 11:03:53 [ERROR] Plugin 'InnoDB' init function returned error.
190130 11:03:53 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190130 11:03:53 [Note] Plugin 'FEEDBACK' is disabled.
190130 11:03:53 [ERROR] Unknown/unsupported storage engine: InnoDB
190130 11:03:53 [ERROR] Aborting

190130 11:03:53 [Note] /usr/libexec/mysqld: Shutdown complete

190130 11:03:53 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
190130 11:04:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
190130 11:04:00 [Warning] You need to use --log-bin to make --binlog-format work.
190130 11:04:00 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 29622 ...
190130 11:04:00 InnoDB: The InnoDB memory heap is disabled
190130 11:04:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190130 11:04:00 InnoDB: Compressed tables use zlib 1.2.7
190130 11:04:00 InnoDB: Using Linux native AIO
190130 11:04:00 InnoDB: Initializing buffer pool, size = 340.0M
InnoDB: mmap(365920256 bytes) failed; errno 12
190130 11:04:00 InnoDB: Completed initialization of buffer pool
190130 11:04:00 InnoDB: Fatal error: cannot allocate memory for the buffer pool
190130 11:04:00 [ERROR] Plugin 'InnoDB' init function returned error.
190130 11:04:00 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190130 11:04:00 [Note] Plugin 'FEEDBACK' is disabled.
190130 11:04:00 [ERROR] Unknown/unsupported storage engine: InnoDB
190130 11:04:00 [ERROR] Aborting

190130 11:04:00 [Note] /usr/libexec/mysqld: Shutdown complete

190130 11:04:00 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
190130 11:04:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
190130 11:04:40 [Warning] You need to use --log-bin to make --binlog-format work.
190130 11:04:40 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 2912 ...
190130 11:04:40 InnoDB: The InnoDB memory heap is disabled
190130 11:04:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190130 11:04:40 InnoDB: Compressed tables use zlib 1.2.7
190130 11:04:40 InnoDB: Using Linux native AIO
190130 11:04:40 InnoDB: Initializing buffer pool, size = 340.0M
190130 11:04:40 InnoDB: Completed initialization of buffer pool
190130 11:04:40 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...
190130 11:04:42 InnoDB: Waiting for the background threads to start
190130 11:04:43 Percona XtraDB ( http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 3985635780
190130 11:04:43 [Note] Plugin 'FEEDBACK' is disabled.
190130 11:04:43 [Note] Server socket created on IP: '127.0.0.1'.
190130 11:04:43 [Note] Event Scheduler: Loaded 0 events
190130 11:04:43 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server

Akina,

Сам сервер Характеристики
авторПроцессор QEMU Virtual CPU version 1.5.3 2399.998 MHz
Озу 1гб
hdd 15гб


Озу - качается примерно 50-70%


Вот конфиг мне дал хостер
автор[mysqld]
collation-server = utf8_general_ci
character-set-server = utf8
local-infile=0
pid-file = /var/run/mysqld/mysqld.pid
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address = 127.0.0.1
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd


# Настройки с PS ---------------------------------------------------------------
max_allowed_packet = 1024M

# INNODB #
binlog_format = MIXED
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 32M
#innodb_log_file_size = 32M
innodb_read_io_threads = 8
innodb_write_io_threads = 8
transaction-isolation = READ-COMMITTED
innodb_stats_on_metadata = OFF

# CACHES AND LIMITS #
max_connections = 100
max_user_connections = 10
wait_timeout = 180
interactive_timeout = 180
connect_timeout = 180
tmp_table_size = 64M
max_heap_table_size = 64M
bulk_insert_buffer_size = 8M
open_files_limit = 65535
table_open_cache = 10240
table_definition_cache = 10240

query_cache_size = 64M
query_cache_type = 1
query_cache_limit = 2M
thread_cache_size = 32
thread_stack = 256K

read_rnd_buffer_size = 2M
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 4M

key_buffer_size = 256M


# Настройки с PS ---------------------------------------------------------------

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

slow_query_log = /var/log/mariadb/mysql-slow.log
long_query_time = 1

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

miksoft,

Таблицы у меня все InnoDB
пару таблиц пришлось переделать в MyISAM по причине Пропадают таблицы в которых я делаю постоянную обработку по крону
Пока вроде эти таблицы работают нормально но серовно приходят ошибки что подключение потеряно в PDO


Я кстати поменял innodb_buffer_pool_size = 750M

И сировно показывает автор190130 14:26:35 InnoDB: Initializing buffer pool, size = 340.0M

Привет всем еще раз.
Я гдето на неделю пропал следил за сервером.

Вот сегодня делал тесты по нагрузке запустил на проверку SEO сайт чтоб он пропарсил все страницы, загруженность 10 подключений в 1-2 секунды и гдето через ссылок 300 падает сервер (после перезагрузки сервера проверял)

Почистил логи получил такео:

автор190206 13:44:01 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
190206 13:44:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
190206 13:44:27 [Warning] You need to use --log-bin to make --binlog-format work.
190206 13:44:27 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 2914 ...
190206 13:44:27 InnoDB: The InnoDB memory heap is disabled
190206 13:44:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190206 13:44:27 InnoDB: Compressed tables use zlib 1.2.7
190206 13:44:27 InnoDB: Using Linux native AIO
190206 13:44:27 InnoDB: Initializing buffer pool, size = 340.0M
190206 13:44:28 InnoDB: Completed initialization of buffer pool
190206 13:44:28 InnoDB: highest supported file format is Barracuda.
190206 13:44:28 InnoDB: Waiting for the background threads to start
190206 13:44:29 Percona XtraDB ( http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 5322704217
190206 13:44:29 [Note] Plugin 'FEEDBACK' is disabled.
190206 13:44:29 [Note] Server socket created on IP: '127.0.0.1'.
190206 13:44:29 [Note] Event Scheduler: Loaded 0 events
190206 13:44:29 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
190206 13:45:03 [ERROR] mysqld: Table './**********/tovar_product_price_1c_vrem_xml' is marked as crashed and should be repaired
190206 13:45:03 [Warning] Checking table: './************/tovar_product_price_1c_vrem_xml'
190206 13:47:09 mysqld_safe Number of processes running now: 0
190206 13:47:09 mysqld_safe mysqld restarted
190206 13:47:09 [Warning] You need to use --log-bin to make --binlog-format work.
190206 13:47:09 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 3262 ...
190206 13:47:09 InnoDB: The InnoDB memory heap is disabled
190206 13:47:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190206 13:47:09 InnoDB: Compressed tables use zlib 1.2.7
190206 13:47:09 InnoDB: Using Linux native AIO
190206 13:47:09 InnoDB: Initializing buffer pool, size = 340.0M
InnoDB: mmap(365920256 bytes) failed; errno 12
190206 13:47:09 InnoDB: Completed initialization of buffer pool
190206 13:47:09 InnoDB: Fatal error: cannot allocate memory for the buffer pool
190206 13:47:09 [ERROR] Plugin 'InnoDB' init function returned error.
190206 13:47:09 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190206 13:47:09 [Note] Plugin 'FEEDBACK' is disabled.
190206 13:47:09 [ERROR] Unknown/unsupported storage engine: InnoDB
190206 13:47:09 [ERROR] Aborting

190206 13:47:09 [Note] /usr/libexec/mysqld: Shutdown complete

190206 13:47:09 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
190206 13:47:42 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
190206 13:47:42 [Warning] You need to use --log-bin to make --binlog-format work.
190206 13:47:42 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 4372 ...
190206 13:47:42 InnoDB: The InnoDB memory heap is disabled
190206 13:47:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190206 13:47:42 InnoDB: Compressed tables use zlib 1.2.7
190206 13:47:42 InnoDB: Using Linux native AIO
190206 13:47:42 InnoDB: Initializing buffer pool, size = 340.0M
190206 13:47:42 InnoDB: Completed initialization of buffer pool
190206 13:47:42 InnoDB: highest supported file format is Barracuda.
190206 13:47:42 InnoDB: Starting crash recovery from checkpoint LSN=5340657489
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
190206 13:47:42 InnoDB: Starting final batch to recover 12 pages from redo log
190206 13:47:43 InnoDB: Waiting for the background threads to start
190206 13:47:44 Percona XtraDB ( http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 5340660507
190206 13:47:44 [Note] Plugin 'FEEDBACK' is disabled.
190206 13:47:44 [Note] Server socket created on IP: '127.0.0.1'.
190206 13:47:44 [Note] Event Scheduler: Loaded 0 events
190206 13:47:44 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server


Настройки конфигурации вот:

автор[mysqld]
collation-server = utf8_general_ci
character-set-server = utf8
local-infile=0
pid-file = /var/run/mysqld/mysqld.pid
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address = 127.0.0.1
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd


# Настройки с PS ---------------------------------------------------------------
max_allowed_packet = 512M

# INNODB #
binlog_format = MIXED
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 768M
innodb_buffer_pool_instances = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 32M
#innodb_log_file_size = 42M
innodb_read_io_threads = 8
innodb_write_io_threads = 8
transaction-isolation = READ-COMMITTED
innodb_stats_on_metadata = OFF

# CACHES AND LIMITS #
max_connections = 151
max_user_connections = 0
wait_timeout = 1200
interactive_timeout = 180
connect_timeout = 180
tmp_table_size = 64M
max_heap_table_size = 64M
bulk_insert_buffer_size = 8M
open_files_limit = 65535
table_open_cache = 10240
table_definition_cache = 10240

query_cache_size = 32M
query_cache_type = 1
query_cache_limit = 2M
thread_cache_size = 32
thread_stack = 256K

read_rnd_buffer_size = 2M
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 4M

key_buffer_size = 128M


# Настройки с PS ---------------------------------------------------------------

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

slow_query_log = /var/log/mariadb/mysql-slow.log
long_query_time = 1

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d


И еще я когда включаю "innodb_log_file_size" то служба не запускается

miksoft,

Это сколько ?

miksoft,

Поменял на 250MB
Перезапустил сервер и почему то опять показывает автор InnoDB: Initializing buffer pool, size = 340.0M

miksoft,

И как я могу его найти ???
И следующий момент я когда что то в этом файле my.conf меняю служба не запускается значит эта конфигурация участвует в работе

miksoft,

Нашел отклчил работает но также плохо


Я в итоге вчера переделал my.cnf
автор[mysqld]
collation-server = utf8_general_ci
character-set-server = utf8
local-infile=0
pid-file = /var/run/mysqld/mysqld.pid
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address = 127.0.0.1
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd


# Настройки с Личная ---------------------------------------------------------------

max_allowed_packet = 16M
#key_buffer = 16M
innodb_buffer_pool_size = 512M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
#innodb_log_file_size = 64M


thread_stack = 128K
thread_cache_size = 128
myisam-recover = BACKUP
max_connections = 128
table_cache = 32

key_buffer_size = 32M
sort_buffer_size = 16M

query_cache_limit = 1M
query_cache_size = 16M
#query_cache_type = 0

expire_logs_days = 10
max_binlog_size = 100M

# Настройки с Личная ---------------------------------------------------------------


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

slow_query_log = /var/log/mariadb/mysql-slow.log
long_query_time = 1

#
# include all files from the config directory
#
# includedir /etc/my.cnf.d





В общем опишу ситуацию после мены my.cnf сервер себя показал довольно хорошо каждые 3 секунды по 10 подключений работало так около 10 минут потом упал

Но после служба отказалась запускаться и пришлось перезапустить сервер и тут пошли фокусы он выдерживал каждые 3 секунды по 10 подключений 30 секунд и падает


Я смотрел по службам sql работал в диапазоне 500 мегабайт
и было 10 служб "php" по 40 мегабайт - видимо это подключение каждое


Честно не знаю где капать , может я перегружаю запросами на сайте и за этого marinadb падает но я в логах сделал корректировку чтоб он мне логировал запросы которые обрабатываются больше 1 секунды (может есть какие то отладчики запросов в программном виде потому что при работе запросы у меня обычно занимают 0,0036 секунд)