[ZBX-2426] postgresql - slowest queries Created: 2010 May 12  Updated: 2017 May 30  Resolved: 2010 May 18

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 1.8.2
Fix Version/s: 1.8.2

Type: Incident report Priority: Major
Reporter: Martin Janda Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

debian lenny, zabbix 1.8.2, postgresql 8.3



 Description   

I tried to log slow queries. Slowest queries are of this type for example:

select value from history_uint where itemid=23287 and clock<=1273669687 order by clock desc limit 2;

It selects many thousand lines, and takes several seconds to perform. Finally, it selects only 2 rows with LIMIT.

I propose that find this SQL query and restrict choice from clock.



 Comments   
Comment by Martin Janda [ 2010 May 18 ]

I found that this query causes the trigger

{device:item.delta(#2)}

#0

I guess the problem is resolved. It is necessary to build a different trigger.

I'm sorry that I did not write sooner.

Comment by Aleksandrs Saveljevs [ 2010 May 18 ]

Whether this query comes from your trigger or is executed by zabbix_server for its own needs, is not important. If such queries take too long, this should be fixed, if possible.

We are closing this problem for now, because we could not reproduce it in our environment with a relatively large PostgreSQL database (approximately 10^8 records in history_uint). If your PostgreSQL installation has some specific settings or you find a reliable way to reproduce the issue when the database is not under heavy load, please feel free to reopen.

Comment by Martin Janda [ 2010 May 19 ]

Our history_uint have 5*10^6 rows every month. I therefore applied to the partitioning large tables built on rules. For example:

CREATE OR REPLACE RULE history_uint_2010_04 AS
ON INSERT TO history_uint
WHERE new.clock >= 1270072801 AND new.clock <= 1272664800 DO INSTEAD INSERT INTO history_uint_2010_04 (itemid, clock, value)
VALUES (new.itemid, new.clock, new.value);

Similarly for other large tables history* and trends*. SQL queries for plotting graphs are faster. Data entry also takes less time. Only those SQL queries of this type triggers (

{device:item.delta(#2)}

) takes longer time of database.

Comment by Aleksandrs Saveljevs [ 2010 May 21 ]

Martin, thank you for your comment. However, this is probably something we are not going to fix.

Generated at Fri Mar 29 10:47:08 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.