[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 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. |