[ZBX-4190] Need to break apart the compounding of deletes followed by a logical or up in housekeeper.c Created: 2011 Sep 30 Updated: 2017 May 30 Resolved: 2013 Dec 31 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Server (S) |
Affects Version/s: | 1.8.8 |
Fix Version/s: | None |
Type: | Incident report | Priority: | Minor |
Reporter: | Kam Lane | Assignee: | Unassigned |
Resolution: | Duplicate | Votes: | 1 |
Labels: | housekeeper, mysql | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified | ||
Environment: |
MySQL Community Server 5.5.15-log |
Issue Links: |
|
Description |
I'm not sure if this is a bug or an improvement, but I'm seeing a significant amount of this in my zabbix_server.log. The most interesting part is that my mysql server is on the same box with my data dir sitting on a SAN LUN and zabbix is connecting via a socket connection. The database isn't restarting; I have a feeling the code is losing it's connection due to the query below in addition to a timeout. Is there any way to break this query up into multiple statements that operate on a smaller number of rows...like everytime an "OR" condition is thrown in the example below? That logical OR is actually a pain point in this query due to the number of items it has to lookup in the index. 26063:20110930:130423.330 [Z3005] query failed: [2006] MySQL server has gone away [delete from housekeeper where (housekeeperid in (100100000120079,100100000120080,100100000120081,100100000120082,100100000120083,100100000120084,100100000120085,100100000120086,100100000120087,100100000120088,100100000120089,100100000120090,100100000120091,100100000120092,100100000120093,100100000120094,100100000120095,100100000120096,100100000120097,100100000120098,100100000120099,100100000120100,100100000120101,100100000120102,100100000120103,100100000120104,100100000120105,100100000120106,100100000120107,100100000120108,100100000120109,100100000120110,100100000120111,100100000120112,100100000120113,100100000120114,100100000120115,100100000120116,100100000120117,100100000120118,100100000120119,100100000120120,100100000120121,100100000120122,100100000120123,100100000120124,100100000120125,100100000120126,100100000120127,100100000120128,100100000120129,100100000120130,100100000120131,100100000120132,100100000120133,100100000120134,100100000120135,100100000120136,100100000120137,100100000120138,100100000120139,100100000120140,100100000120141,100100000120142,100100000120143,100100000120144,100100000120145,100100000120146,100100000120147,100100000120148,100100000120149,100100000120150,100100000120151,100100000120152,100100000120153,100100000120154,100100000120155,100100000120156,100100000120157,100100000120158,100100000120159,100100000120160,100100000120161,100100000120162,100100000120163,100100000120164,100100000120165,100100000120166,100100000120167,100100000120168,100100000120169,100100000120170,100100000120171,100100000120172,100100000120173, ... zabbix[;]> show table status where Name = 'housekeeper';
------------
------------ zabbix[;]> show index from housekeeper;
------------
------------ zabbix[;]> select count from housekeeper;
----------
---------- |
Comments |
Comment by Kam Lane [ 2011 Oct 10 ] |
Further analysis executing the same queries by hand that end up in the log [as seen above], led me to discover that even a manual run of these queries [or subsets of these queries splitting at the 'OR'] were to big in terms of character/string size and the mysql server would kill my connection. I was able to cut down on this occurance significantly, but not completely, by modifying the mysql my.cnf file and adding: max_allowed_packet = 16M I tried many other values for packet size, but 16M was really the only value that I could change "max_allowed_packet" to in MySQL 5.5.15 and actually have that value hold through the server restart and be visible when calling the variable [show variables like 'max_allowed_%';]. The MySQL developer site talks about the max_allowed_packet size [the maximum size of one packet or any generated/intermediate string] by default being 1MB and obviously these queries can exceed that. I've also been wondering if zabbix is holding on to open database connections to long and round robin-ing them, but that's for another investigation. To work around this, I propose that the code that generates the housekeeper delete queries be refactored to keep all queries under 1MB, as that's the default for the MySQL server. I also recommend that the documentation be updated in the interim stating that the 'max_allowed_packet' be increased from the default of 1MB. The big issue here is if the code isn't re-factored breaking apart the compounding queries, it only takes a few failed queries before the housekeeper table starts queuing up and the process won't ever be able to cleanup as it's exceeded the max_allowed_packet size. The max max_allowed_packet size is 1GB on both the client and server. If it were to ever queue up to this, manual intervention would be required to cleanup the table. |
Comment by Alexander Vladishev [ 2013 Dec 31 ] |
Closed as duplicate. |