| Summary: | dev-db/mysql-5.6.28 - unresponsive MySQL after few days of running | ||
|---|---|---|---|
| Product: | Gentoo Linux | Reporter: | SysadminF2C <sysadmin> |
| Component: | Current packages | Assignee: | Gentoo Linux MySQL bugs team <mysql-bugs> |
| Status: | RESOLVED NEEDINFO | ||
| Severity: | normal | CC: | hydrapolic |
| Priority: | Normal | ||
| Version: | unspecified | ||
| Hardware: | AMD64 | ||
| OS: | Linux | ||
| Whiteboard: | |||
| Package list: | Runtime testing required: | --- | |
| Attachments: |
emerge --info dev-db/mysql
kern.log dmesg MySQL error log |
||
|
Description
SysadminF2C
2016-06-13 10:02:09 UTC
Hello, please provide us with some more information - what hardware do you run the server on? Output of emerge --info, content of dmesg and /var/log/kern.log, your mysql log. Have you tried running FLUSH HOSTS? Have you tried increasing max_connections and/or max_connect_errors? Some random observations: - 2GB query cache is an overkill and may actually slow down your application. It's better to use application caching. - you have a limit on innodb_data_file_path (max:100GB) - isn't that over the limit? You shouldn't limit it. - innodb_log_file_size is 500MB, do you really need that big one? - binlog_format is set to MIXED - do you use replication? If so, the only recommended format is ROW. How many disk space do you have in your paritions? Especially /var/lib/mysql and /tmp? Created attachment 437486 [details]
emerge --info dev-db/mysql
Created attachment 437488 [details]
kern.log
Created attachment 437490 [details]
dmesg
Hello Tomáš, The server is a physical server: - CPU: 2 * Intel(R) Xeon(R) CPU E5-2697 v2 @ 2.70GHz - Memory: 256 Go DDR3 16866 Mhz ECC (16 * 16 Go) - Hard Drive: 12 x 600 Go SAS 15K RPM - RAID: RAID 10 with LSI MegaRAID SAS 9271-8i Do you want more information about the server ? You will find the 'emerge --info', 'kern.log' and 'dmesg' in attachments. Have you tried running FLUSH HOSTS? => No but I can try when the problem will happen again. Have you tried increasing max_connections and/or max_connect_errors? => No because I can kill a few queries and connections. After this, the number of connections is lower than max_connections. The problem has often occurred during off-peak hours. I think that MySQL does not respond to a request and that the other accumulates behind. - 2GB query cache is an overkill and may actually slow down your application. It's better to use application caching. - you have a limit on innodb_data_file_path (max:100GB) - isn't that over the limit? You shouldn't limit it. - innodb_log_file_size is 500MB, do you really need that big one? - binlog_format is set to MIXED - do you use replication? If so, the only recommended format is ROW. => Thank you for advices. We are on a production server so it isn't easy to change running configuration without qualification. We have plans to split our SQL server into multiple smaller servers: we will take the opportunity to test different values to these parameters. How many disk space do you have in your paritions? Especially /var/lib/mysql and /tmp? => At the moment we have a single partition: Filesystem Size Used Avail Use% Mounted on /dev/root 3.3T 1.7T 1.4T 55% / The only interesting stuff in the logs is pretty old: Aug 27 11:30:19 sql1 kernel: EXT4-fs warning (device sda4): ext4_dx_add_entry:1990: Directory index full! Please attach the logs from /var/log/mysql. Hello, Indeed this problem is old and it was fixed since. Please find "mysqld_2016.err" in attachment. It's the single file that we have. Created attachment 437678 [details]
MySQL error log
Those shutdowns from the MySQL log - are you doing them on purpose? When MySQL has the problem, we have to restart him. When we try to stop it with '/etc/init.d/mysql stop', it nothing happens even after several minutes so we must kill it with 'kill -9'. I tried to compile MySQL with 'debug' flag but 'mysqld -V' return: mysqld Ver 5.6.28 for Linux on x86_64 (Source distribution) => we haven't 'mysqld-debug'. I tried to enable this parameter in '/etc/mysql/my.cnf': debug = d:t:i:o,/tmp/mysqld.trace But I had this error: 2016-06-15 22:15:12 38311 [ERROR] /usr/sbin/mysqld: unknown variable 'debug=d:t:i:o,/tmp/mysqld.trace' Do you know how can I enable the debug mode please ? A normal shutdown was issued: 2016-06-10 23:47:33 16945 [Note] /usr/sbin/mysqld: Normal shutdown 2016-06-10 23:47:33 16945 [Note] Giving 350 client threads a chance to die gracefully Then MySQL was killed and started 2 minutes afterwards: 2016-06-10 23:49:30 48949 [Note] InnoDB: Using atomics to ref count buffer pool pages You mentioned you have 256GB of RAM and Innodb buffer pool of 128GB. Even though you have innodb_flush_log_at_trx_commit = 1, MySQL has make sure everything is dumped to the disk. I would try giving it more time, because there is nothing unusual in the log, it's a normal shutdown. Can you check iotop or similar tool to see if any disks read/writes are being made? I suppose there will be a lot of writes by MySQL. I think the problem can be caused by the PHP application itself. If PHP is a module for apache and it stucks for some reason (networking,other services,etc.), it will remain there until you restart Apache. Try increasing max_connections and max_connect_errors and check how many PHP threads are actually being used. Debugging is mainly when you really have problems like crashes, depends what you want to find. Since it's a normal shutdown, you can still strace attach and see what system calls are issued. More on debugging: https://wiki.gentoo.org/wiki/Debugging https://wiki.gentoo.org/wiki/Project:Quality_Assurance/Backtraces Hi, Sorry for the response time. When we stop MySQL properly, it takes about 15 minutes. When we have the problem, even after 40 minutes, MySQL is already running. Furthermore, the number of open file by MySQL doesn't decrease :-/ We already tried to restart Apache and connections remained on MySQL. The problem appeared with MySQL upgrade (MySQL 5.6.24 -> 5.6.27). I'm not sure that the problem is side PHP :-/ Can you try a newer release? Was this the only thing changed (I mean the MySQL version)? Hi, Sorry for the response time. We have upgraded MySQL to MySQL 5.6.30 two weeks ago (2016-06-22). For the moment the problem hasn't appeared but we have stopped MySQL on the 2016-06-30 for maintenance therefore we have to wait and see. Thanks for the update. I'll close the bug for now, if it happens again, feel free to reopen and we can continue. |