Centos 7 mariadb problem

General support questions
Blisk
Posts: 224
Joined: 2011/07/04 14:49:51
Contact:

Centos 7 mariadb problem

Postby Blisk » 2017/11/17 17:27:50

Last fev days I have constant problems with mariadb.
When I restart it it works fev minutes and after that all apps which use mysql tables stops to work and than I restart it again and it works again ok for fev minutes.
I checked logs but nothing relevant is in logs.
Until fev days ago all works ok but now don't know what is wrong.

Blisk
Posts: 224
Joined: 2011/07/04 14:49:51
Contact:

Re: Centos 7 mariadb problem

Postby Blisk » 2017/11/17 19:18:50

Everytime I do mysqldump I get error.
mysqldump: Got error: 1016: "Can't open file: './forum/phpbb_pa_download_info.frm' (errno: 24)" when using LOCK TABLES
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_6fbb_2.MAD' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Got error: 1142: "SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances'" when using LOCK TABLES

User avatar
avij
Forum Moderator
Posts: 2176
Joined: 2010/12/01 19:25:52
Location: Helsinki, Finland
Contact:

Re: Centos 7 mariadb problem

Postby avij » 2017/11/17 19:40:14


Blisk
Posts: 224
Joined: 2011/07/04 14:49:51
Contact:

Re: Centos 7 mariadb problem

Postby Blisk » 2017/11/18 08:27:43

[mysqld]
open_files_limit = 5000

restarted mariadb
but still not working.

mysqldump: Got error: 1016: "Can't open file: './ezoterika/phpbb_user_group.frm' (errno: 24)" when using LOCK TABLES
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_6078_2.MAD' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Got error: 1016: "Can't open file: './forum/cc_ip_bot.frm' (errno: 24)" when using LOCK TABLES
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_6078_2.MAD' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Got error: 1142: "SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances'" when using LOCK TABLES

User avatar
TrevorH
Forum Moderator
Posts: 21174
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: Centos 7 mariadb problem

Postby TrevorH » 2017/11/18 09:57:39

What does show status like "open%" say?
CentOS 5 died in March 2017 - migrate NOW!
Full time Geek, part time moderator. Use the FAQ Luke

Blisk
Posts: 224
Joined: 2011/07/04 14:49:51
Contact:

Re: Centos 7 mariadb problem

Postby Blisk » 2017/11/18 12:28:46

+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_files | 21 |
| Open_streams | 0 |
| Open_table_definitions | 33 |
| Open_tables | 26 |
| Opened_files | 80 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Opened_views | 0 |
+--------------------------+-------+

User avatar
TrevorH
Forum Moderator
Posts: 21174
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: Centos 7 mariadb problem

Postby TrevorH » 2017/11/18 15:18:51

Have you set limits in /etc/security/limits.conf (or limits.d/*.conf)? Or modifed the mariadb systemd unit file?
CentOS 5 died in March 2017 - migrate NOW!
Full time Geek, part time moderator. Use the FAQ Luke

Blisk
Posts: 224
Joined: 2011/07/04 14:49:51
Contact:

Re: Centos 7 mariadb problem

Postby Blisk » 2017/11/19 08:27:12

TrevorH wrote:Have you set limits in /etc/security/limits.conf (or limits.d/*.conf)? Or modifed the mariadb systemd unit file?

I setup those too but not working.

* soft core 0
* hard rss 10000


I did it in
/etc/my.cnf
open-files-limit=65550

and did this
mkdir -p /etc/systemd/system/mariadb.service.d/
touch /etc/systemd/system/mariadb.service.d/limits.conf

[Service]
LimitNOFILE=20000

Still not working

User avatar
TrevorH
Forum Moderator
Posts: 21174
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: Centos 7 mariadb problem

Postby TrevorH » 2017/11/19 10:24:11

What's the output from cat /proc/$(pidof mysqld)/limits
CentOS 5 died in March 2017 - migrate NOW!
Full time Geek, part time moderator. Use the FAQ Luke

Blisk
Posts: 224
Joined: 2011/07/04 14:49:51
Contact:

Re: Centos 7 mariadb problem

Postby Blisk » 2017/11/19 10:47:12

Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 8388608 unlimited bytes
Max core file size 0 unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 127268 127268 processes
Max open files 1024 4096 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 127268 127268 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us