Bug #70328 | MySQL Server crashs while running larger Queries | ||
---|---|---|---|
Submitted: | 13 Sep 2013 10:57 | Modified: | 3 Jan 2014 19:55 |
Reporter: | Sebastian Strzelczyk | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.6.13 | OS: | Linux (Linux 3.0.74-0.6.10-default x86_64 (Suse Enterprice 11)) |
Assigned to: | CPU Architecture: | Any |
[13 Sep 2013 10:57]
Sebastian Strzelczyk
[13 Sep 2013 12:23]
MySQL Verification Team
process is running out of memory. please send us output in a text file: show global variables; show global status; show engine innodb status; show engine performance_schema status; ps fauxx free -m
[13 Sep 2013 12:38]
Peter Laursen
I am curious and listening! Are you serious that you upgraded from MySQL 5.3? Actually I think there was a (very short-lived) release named 5.3.x (along with 5.2.x - they were both out in between MySQL 6.0 and 5.5). But as Oracle now seems to have truncated the public FTP-mirrors I am not able to verify. Did you execute 'mysql_upgrade'? Or maybe 'mysql_upgrade has problems with upgrading from this rare version (if you mean what you write)? Peter (not a MySQL/Oracle person)
[14 Sep 2013 16:41]
Justin Swanhart
Did you mean you upgraded from MariaDB 5.3?
[16 Sep 2013 9:18]
Sebastian Strzelczyk
the previous version don't really matters, we had the software running at 5.1.17 and 5.5.25a and never had problems ... since we migrate to 5.6.x we have the problems on serveral installations. The migration was done by dumping from the old database to sql-script and importing the sql-dump to a new server. So problems with with mysql_upgrade could be excluded. the main change beside the upgrade of the server version is the increase of the memory. The innodb_buffer_size was incresed from 8GB to 30GB and we switched to innodb_file_per_table = 0 to innodb_file_per_table = 1 to save some space on hard disk. we also increased the linux process limits to avoid some linux side effects belonging process memory limits: /etc/security/limits.conf mysql soft nofile 32800 mysql hard nofile 32800 mysql soft rss 83886080 mysql hard rss 83886080 mysql soft as 83886080 mysql hard as 83886080
[16 Sep 2013 9:28]
Sebastian Strzelczyk
free -m
Attachment: free_srv1.txt (text/plain), 245 bytes.
[20 Sep 2013 9:46]
MySQL Verification Team
I expect mysqld to consume around 34GB at startup then probably 20M per connection at least. A few wrong queries with tmp_table_size of 300M will cause issues. o) performance_schema costs you ~930M o) binlog_cache_size=20M is surely a mistake. Set it to 64k or so. o) you need to account memory for innodb fulltext indexes. It is possible some part of code didn't handle out of memory correctly. We'd need to see the exact query and EXPLAIN for that..
[20 Sep 2013 13:55]
Sebastian Strzelczyk
Thanks for the advice, especially the binlog_cache_size we will reduce, that makes sence. We are still investigate to find the queries which make the problem, but hard to find it in production environment. we had two crash very close one behind another and we found out that before the crash the memory usage moved sharply to 2.9 GB more ... have you some tips what indicator we can monitor in the performace-monitor to find the problem?
[25 Sep 2013 18:59]
MySQL Verification Team
Are you doing any joins with views involved ? Try set tmp_table_size and join_buffer_size to their default values and see if things stabilize. At least, make sure no out of memory errors appear in the logs (or spikes on server)
[8 Oct 2013 10:16]
Sebastian Strzelczyk
Sorry for the late feedback, I have been on holiday. Yes we are working with views, in most of our queries are views involved. I will set the adviced variables to default, hopefully it will stablize the server. We have large, programmatically generated queries, which insert and delete a lot of records to some tables, normaly the server process them, but maybe this queries will bring the server into trouble. I will post them private.
[12 Oct 2013 17:44]
Ellis Feigenblatt
I've got the same error. MySQL crashed due to a long query string with more than 250 unions. ---------------------------------------------------------- Version: 5.6.12-log Community Servel (GPL) System: Windows 7 SP1 Error: 2013-10-12 13:03:08 660 [ERROR] C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld: Out of memory (Needed 89804 bytes) 2013-10-12 13:03:26 660 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space ---------------------------------------------------------- MySQL is run as a Windows Service. After the crash the service is fully stopped and MySQL has to be started from the beggining (not a service restart). Only for your information, the query that caused the crash is an union of more than 250 identical select statements, each one retrieving the last 10 entries from a different general ledger account. I believe that any very long query statement with 250 unions or more is prone to cause the crash. String that produced the error (reduced to fit this bug report): SELECT contablancamento.cDB_CR ,qBalance ,contabaccount.idcontabaccount ,sAccountDescription ,sSubAccount ,sAccount ,sDescricaoPlano ,contabtransaction.idContabTransaction ,dTransactionDate ,tsCadastro ,contabtransaction.idContabOperacao as idContabOperacao ,contabtransaction.idContabPrototipo as idContabPrototipo ,sDescricaoTransacao ,idTransacaoDeTransferencia ,qValor ,sDescricaoPrototipo ,sUsuario ,sDescricaoCaso ,idContabRecibo FROM contablancamento INNER JOIN contabtransaction ON contablancamento.idContabTransaction=contabtransaction.idContabTransaction INNER JOIN contaboperacao ON contabtransaction.idContabOperacao=contaboperacao.idContabOperacao INNER JOIN contabaccount ON contablancamento.idcontabaccount=contabaccount.idcontabaccount and contabaccount.qBalanceAtual<>0 INNER JOIN contabplano ON contabaccount.idContabPlano=contabplano.idContabPlano INNER JOIN contabprototipo ON contabtransaction.idContabPrototipo=contabprototipo.idContabPrototipo INNER JOIN processo ON contabaccount.idProcesso=processo.idProcesso INNER JOIN caso ON processo.idCaso=caso.idCaso INNER JOIN usuario ON contaboperacao.idUsuario=usuario.idUsuario LEFT JOIN contabrecibo ON contabtransaction.idContabTransaction=contabrecibo.idContabTransaction WHERE contabplano.iNivelSigiloContabil <= '5' AND contabaccount.idcontabaccount='6' AND dTransactionDate<='2013-10-12' AND contabaccount.idcontabaccount='2691' AND contabplano.sAccount!='1.2.2.3' AND contabplano.sAccount!='4.1.8.1' AND contabplano.sAccount!='4.1.8.2' AND contabplano.sAccount!='4.1.8.3' ORDER BY dTransactionDate DESC LIMIT 10) UNION (SELECT contablancamento.cDB_CR ,qBalance ,contabaccount.idcontabaccount ,sAccountDescription ,sSubAccount ,sAccount ,sDescricaoPlano ,contabtransaction.idContabTransaction ,dTransactionDate ,tsCadastro ,contabtransaction.idContabOperacao as idContabOperacao ,contabtransaction.idContabPrototipo as idContabPrototipo ,sDescricaoTransacao ,idTransacaoDeTransferencia ,qValor ,sDescricaoPrototipo ,sUsuario ,sDescricaoCaso ,idContabRecibo FROM contablancamento INNER JOIN contabtransaction ON contablancamento.idContabTransaction=contabtransaction.idContabTransaction INNER JOIN contaboperacao ON contabtransaction.idContabOperacao=contaboperacao.idContabOperacao INNER JOIN contabaccount ON contablancamento.idcontabaccount=contabaccount.idcontabaccount and contabaccount.qBalanceAtual<>0 INNER JOIN contabplano ON contabaccount.idContabPlano=contabplano.idContabPlano INNER JOIN contabprototipo ON contabtransaction.idContabPrototipo=contabprototipo.idContabPrototipo INNER JOIN processo ON contabaccount.idProcesso=processo.idProcesso INNER JOIN caso ON processo.idCaso=caso.idCaso INNER JOIN usuario ON contaboperacao.idUsuario=usuario.idUsuario LEFT JOIN contabrecibo ON contabtransaction.idContabTransaction=contabrecibo.idContabTransaction WHERE contabplano.iNivelSigiloContabil <= '5' AND contabaccount.idcontabaccount='2511' AND dTransactionDate<='2013-10-12' AND contabaccount.idcontabaccount='2691' AND contabplano.sAccount!='1.2.2.3' AND contabplano.sAccount!='4.1.8.1' AND contabplano.sAccount!='4.1.8.2' AND contabplano.sAccount!='4.1.8.3' ORDER BY dTransactionDate DESC LIMIT 10) UNION """"... 250 select statements with different account number."""" UNION (SELECT contablancamento.cDB_CR ,qBalance ,contabaccount.idcontabaccount ,sAccountDescription ,sSubAccount ,sAccount ,sDescricaoPlano ,contabtransaction.idContabTransaction ,dTransactionDate ,tsCadastro ,contabtransaction.idContabOperacao as idContabOperacao ,contabtransaction.idContabPrototipo as idContabPrototipo ,sDescricaoTransacao ,idTransacaoDeTransferencia ,qValor ,sDescricaoPrototipo ,sUsuario ,sDescricaoCaso ,idContabRecibo FROM contablancamento INNER JOIN contabtransaction ON contablancamento.idContabTransaction=contabtransaction.idContabTransaction INNER JOIN contaboperacao ON contabtransaction.idContabOperacao=contaboperacao.idContabOperacao INNER JOIN contabaccount ON contablancamento.idcontabaccount=contabaccount.idcontabaccount and contabaccount.qBalanceAtual<>0 INNER JOIN contabplano ON contabaccount.idContabPlano=contabplano.idContabPlano INNER JOIN contabprototipo ON contabtransaction.idContabPrototipo=contabprototipo.idContabPrototipo INNER JOIN processo ON contabaccount.idProcesso=processo.idProcesso INNER JOIN caso ON processo.idCaso=caso.idCaso INNER JOIN usuario ON contaboperacao.idUsuario=usuario.idUsuario LEFT JOIN contabrecibo ON contabtransaction.idContabTransaction=contabrecibo.idContabTransaction WHERE contabplano.iNivelSigiloContabil <= '5' AND contabaccount.idcontabaccount='3336' AND dTransactionDate<='2013-10-12' AND contabaccount.idcontabaccount='2691' AND contabplano.sAccount!='1.2.2.3' AND contabplano.sAccount!='4.1.8.1' AND contabplano.sAccount!='4.1.8.2' AND contabplano.sAccount!='4.1.8.3' ORDER BY dTransactionDate DESC LIMIT 10) ORDER BY sAccount ASC, sSubAccount ASC, dTransactionDate ASC, idContabTransaction ASC LIMIT 0,35;
[3 Dec 2013 19:55]
Sveta Smirnova
Sebastian, Ellis, thank you for the feedback. Please provide dump or, at least, output of SHOW CREATE TABLE and SHOW TABLE STATUS for all tables, participated in problematic queries and your current configuration files.
[4 Jan 2014 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".