SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 15.01.2019 15:52:27

biryukovm
Участник
Зарегистрирован: 27.04.2017
Сообщений: 15

Здравствуйте у меня падает сервер mariadb

Здравствуйте.

У меня есть проблема падает сервер mariadb

Вот лог:

190115 14:10:45 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
190115 14:11:12 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
190115 14:11:12 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 2467 ...
190115 14:11:12 InnoDB: The InnoDB memory heap is disabled
190115 14:11:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190115 14:11:12 InnoDB: Compressed tables use zlib 1.2.7
190115 14:11:12 InnoDB: Using Linux native AIO
190115 14:11:12 InnoDB: Initializing buffer pool, size = 340.0M
190115 14:11:12 InnoDB: Completed initialization of buffer pool
190115 14:11:12 InnoDB: highest supported file format is Barracuda.
190115 14:11:12  InnoDB: Waiting for the background threads to start
190115 14:11:13 Percona XtraDB (<a href="http://www.percona.com">http://www.percona.com</a>) 5.5.59-MariaDB-38.11 started; log sequence number 2211812043
190115 14:11:13 [Note] Plugin 'FEEDBACK' is disabled.
190115 14:11:13 [Note] Server socket created on IP: '127.0.0.1'.
190115 14:11:13 [Note] Event Scheduler: Loaded 0 events
190115 14:11:13 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
190115 14:44:35 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_ from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

InnoDB: Error: trying to load index GEN_CLUST_INDEX for table ecopolza_db/tovar_product_price_1c_vrem_xml
InnoDB: but the index tree has been freed!
190115 14:44:35 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_vrem_xml from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn'
t support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

190115 14:45:00 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_ from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

InnoDB: Error: trying to load index GEN_CLUST_INDEX for table ecopolza_db/tovar_product_price_1c_vrem_xml
InnoDB: but the index tree has been freed!
190115 14:45:00 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_vrem_xml from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn'
t support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

190115 14:49:52 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_ from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

InnoDB: Error: trying to load index GEN_CLUST_INDEX for table ecopolza_db/tovar_product_price_1c_vrem_xml
InnoDB: but the index tree has been freed!
190115 14:49:52 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_vrem_xml from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn'
t support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

190115 14:51:17 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_ from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

InnoDB: Error: trying to load index GEN_CLUST_INDEX for table ecopolza_db/tovar_product_price_1c_vrem_xml
InnoDB: but the index tree has been freed!
190115 14:51:17 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_vrem_xml from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn'
t support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

190115 14:51:52 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_ from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

InnoDB: Error: trying to load index GEN_CLUST_INDEX for table ecopolza_db/tovar_product_price_1c_vrem_xml
InnoDB: but the index tree has been freed!
190115 14:51:52 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_vrem_xml from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn'
t support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

190115 15:03:14 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_ from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

InnoDB: Error: trying to load index GEN_CLUST_INDEX for table ecopolza_db/tovar_product_price_1c_vrem_xml
InnoDB: but the index tree has been freed!
190115 15:03:14 [ERROR] Cannot find or open table ecopolza_db/tovar_product_price_1c_vrem_xml from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn'
t support.
See <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; oting.html</a>
how you can resolve the problem.

190115 15:15:04 mysqld_safe Number of processes running now: 0
190115 15:15:04 mysqld_safe mysqld restarted
190115 15:15:04 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 4215 ...
190115 15:15:04 InnoDB: The InnoDB memory heap is disabled
190115 15:15:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190115 15:15:04 InnoDB: Compressed tables use zlib 1.2.7
190115 15:15:04 InnoDB: Using Linux native AIO
190115 15:15:04 InnoDB: Initializing buffer pool, size = 340.0M
190115 15:15:04 InnoDB: Completed initialization of buffer pool
190115 15:15:04 InnoDB: highest supported file format is Barracuda.
190115 15:15:04  InnoDB: Starting crash recovery from checkpoint LSN=2219946969
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
190115 15:15:04  InnoDB: Starting final batch to recover 5 pages from redo log
190115 15:15:05  InnoDB: Error: table 'ecopolza_db/tovar_product_price_1c_vrem_xml'
InnoDB: in InnoDB data dictionary has tablespace id 1256,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name ./ecopolza_db/tovar_product_price_1c_vrem_xml.ibd and id 1258, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.5/en/ &hellip; adict.html</a>
InnoDB: for how to resolve the issue.
190115 15:15:05  InnoDB: Waiting for the background threads to start
190115 15:15:06 Percona XtraDB (<a href="http://www.percona.com">http://www.percona.com</a>) 5.5.59-MariaDB-38.11 started; log sequence number 2219947361
190115 15:15:06 [Note] Plugin 'FEEDBACK' is disabled.
190115 15:15:06 [Note] Server socket created on IP: '127.0.0.1'.
190115 15:15:06 [Note] Event Scheduler: Loaded 0 events
190115 15:15:06 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
190115 16:00:03 mysqld_safe Number of processes running now: 0
190115 16:00:03 mysqld_safe mysqld restarted
190115 16:00:03 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 5339 ...
190115 16:00:03 InnoDB: The InnoDB memory heap is disabled
190115 16:00:03 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190115 16:00:03 InnoDB: Compressed tables use zlib 1.2.7
190115 16:00:03 InnoDB: Using Linux native AIO
190115 16:00:03 InnoDB: Initializing buffer pool, size = 340.0M
InnoDB: mmap(365920256 bytes) failed; errno 12
190115 16:00:03 InnoDB: Completed initialization of buffer pool
190115 16:00:03 InnoDB: Fatal error: cannot allocate memory for the buffer pool
190115 16:00:03 [ERROR] Plugin 'InnoDB' init function returned error.
190115 16:00:03 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190115 16:00:03 [Note] Plugin 'FEEDBACK' is disabled.
190115 16:00:03 [ERROR] Unknown/unsupported storage engine: InnoDB
190115 16:00:03 [ERROR] Aborting

190115 16:00:03 [Note] /usr/libexec/mysqld: Shutdown complete

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

190115 16:00:04 [Note] /usr/libexec/mysqld: Shutdown complete

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

190115 16:00:58 [Note] /usr/libexec/mysqld: Shutdown complete

190115 16:00:58 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
 


Вот конфигурация

[mysqld]
collation-server = utf8_general_ci
character-set-server = utf8
local-infile=0
#innodb_file_per_table = 1
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 <a href="http://fedoraproject.org/wiki/Systemd">http://fedoraproject.org/wiki/Systemd</a>


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

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

 

Может что подскажите
Не могу найти закономерность.


И кстати в логе я сморю есть таблица tovar_product_price_1c_vrem_xml
С ней вообще какие то глюки (это все началось как я перешел на VPS)

Глюки были следующие:
Таблица не отвечает в запросе, то удалилось кудато.
Я пытался создать заново но была ошибка, типа ошибка ключа уже не помню точную ошибку
Не создавалась таблица InnoDB   и я решил создать тогда в MyISAM   тогда таблица создалась и я перевел её в InnoDB 
Потом ошибок небыло дня 2 и вот опять началось.

Может кто что подскажет ?

Неактивен

 

#2 15.01.2019 17:58:16

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6709

Re: Здравствуйте у меня падает сервер mariadb

Я бы сказал, что не хватает памяти:
InnoDB: mmap(365920256 bytes) failed; errno 12
OS error code  12:  Cannot allocate memory

Посмотрите, что забило память, вряд ли у Вас впска, на которой нельзя запустить процесс на 400 мегабайт.


Ну и отдельный вопрос про битые фрмки — кто-то их удалил неудачно. Если бэкапы есть, я бы восстановился аккуратно, если нету — попробуйте сделать (возможно, придется включать innodb_force_recovery), а потом восстановиться. Всё это после того, как почините проблемы с памятью, разумеется.

Неактивен

 

#3 15.01.2019 18:28:37

biryukovm
Участник
Зарегистрирован: 27.04.2017
Сообщений: 15

Re: Здравствуйте у меня падает сервер mariadb

paulus написал:

Я бы сказал, что не хватает памяти:
InnoDB: mmap(365920256 bytes) failed; errno 12
OS error code  12:  Cannot allocate memory

Посмотрите, что забило память, вряд ли у Вас впска, на которой нельзя запустить процесс на 400 мегабайт.


Ну и отдельный вопрос про битые фрмки — кто-то их удалил неудачно. Если бэкапы есть, я бы восстановился аккуратно, если нету — попробуйте сделать (возможно, придется включать innodb_force_recovery), а потом восстановиться. Всё это после того, как почините проблемы с памятью, разумеется.

Характеристики у меня такие:
CentOS 7
Процессор 2.27 GHz (1 ядро)
Озу 1024 MB
Hdd 15 GB


Я запустил mysqltuner

Вот рекомендации:

General recommendations:                                                                                           
    Control warning line(s) into /var/log/mariadb/mariadb.log file                                                 
    Control error line(s) into /var/log/mariadb/mariadb.log file                                                   
    MySQL was started within the last 24 hours - recommendations may be inaccurate                                 
    Reduce your overall MySQL memory footprint for system stability                                               
    Dedicate this server to your database for highest performance.                                                 
    Enable the slow query log to troubleshoot bad queries                                                         
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1       
    Adjust your join queries to always utilize indexes                                                             
    Consider installing Sys schema from https://github.com/mysql/mysql-sys                                         
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU       
Variables to adjust:                                                                                               
  *** MySQL's maximum memory usage is dangerously high ***                                                         
  *** Add RAM before increasing MySQL buffer variables ***                                                         
    query_cache_size (=0)                                                                                         
    query_cache_type (=0)                                                                                         
    join_buffer_size (> 8.0M, or always use indexes with JOINs)                                                   
    innodb_log_file_size should be (=42M) if possible, so InnoDB total log files size equals to 25% of buffer pool
size.                                                                                                             
    innodb_buffer_pool_instances (=1)

Расширил файл конфигурации, добавил это но почему то при проверки mysqltuner  требует опять их указать :

innodb_stats_on_metadata = OFF
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 1M
join_buffer_size = 8M
innodb_buffer_pool_instances = 1

И вот вы написали что не хватает памяти тогда как это можно сделать ?
Я уже оптимизировать SQL запросы нагрузка у меня упала в 50% но эфекта нет пока

Неактивен

 

#4 15.01.2019 18:37:54

biryukovm
Участник
Зарегистрирован: 27.04.2017
Сообщений: 15

Re: Здравствуйте у меня падает сервер mariadb

paulus написал:

Я бы сказал, что не хватает памяти:
Ну и отдельный вопрос про битые фрмки — кто-то их удалил неудачно. Если бэкапы есть, я бы восстановился аккуратно, если нету — попробуйте сделать (возможно, придется включать innodb_force_recovery), а потом восстановиться. Всё это после того, как почините проблемы с памятью, разумеется.

Базу я создавал заново, проблем и ошибок не было где-то неделю или может я не замечал так как на сервере падала служба не только mariadb  но и nginx


Начел наезжать на тех поддержку они мне nginx настроили как на общем хостинге. И показали что у меня SQL запросы перегружают базу и вот падает ну я начил аптимизацию исправил правдо база не большая, но нагрузка есть и осталась но уже меньше.
Теперь я когда пишу в тех поддержку проблему с mariadb  их ответ мы вам настроили в рамках первой настройки а теперь ищите ошибки сами

Неактивен

 

#5 16.01.2019 09:47:54

biryukovm
Участник
Зарегистрирован: 27.04.2017
Сообщений: 15

Re: Здравствуйте у меня падает сервер mariadb

Кстати вот я писал про глюк с таблицей вот опять
Прилагаю скриншоты

Таблица которая магическим образом пропадает переодически tovar_product_price_1c_vrem_xml

https://image.prntscr.com/image/DCn-ImiMRyeD3l5mrqNf5Q.png
https://image.prntscr.com/image/HXe0n_H3RlyZqBUX-TxmwQ.png

Отредактированно biryukovm (16.01.2019 09:49:51)

Неактивен

 

#6 16.01.2019 17:56:43

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6709

Re: Здравствуйте у меня падает сервер mariadb

Но Вы на память посмотрели? Кто использует, сколько стоит в конфиге?

Неактивен

 

#7 16.01.2019 19:05:17

biryukovm
Участник
Зарегистрирован: 27.04.2017
Сообщений: 15

Re: Здравствуйте у меня падает сервер mariadb

paulus написал:

Но Вы на память посмотрели? Кто использует, сколько стоит в конфиге?

Нет я не знаю как это сделать

Неактивен

 

#8 21.01.2019 01:51:20

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6709

Re: Здравствуйте у меня падает сервер mariadb

top, shift-M внутри, или ps axu и посмотреть в нем

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson