Problems in 1.3.4

[limo]

Hi all,

I am trying to sumarize my experiences and problems with 1.3.4. Some of my problems are probably specific to me, other are global.

I have to say I upgraded sql from 1.1.x version to migrate my old data into 1.3.4.

Now we have some problems. Most critical is probably very slow db. I think zabbix need to maintain some statistics about basic profiling. It is very critical think. You need to know, how much time zabbix spent in which functions. It is not needed for all functions, but insert into history, trends and update_triggers would be enaught for start.

I think this problem is not only our problem. We can cleanup DB and do some things to speedup it, but imagine standard user who wants to test zabbix. When frontend is quite unusable due to timeouts and history is not filling because inserts take 60seconds to update indexes, it is really big problem. Zabbix should have some "feedback" to user, saying "Delete data from history". Worsest scenario is, that zabbix is runing but due to big database access times results are unpredictable and slow.

Now we have this problem. As I said, maybe it is not directly zabbix problem, but it is quite hard to find problem when everything seems to be working but does not.

Small example from mysql-slow.log:
Query_time: 586 Lock_time: 0 Rows_sent: 1000 Rows_examined: 204921
select distinct t.triggerid,t.priority,t.description,h.host,e.cloc k,e.value from events e, triggers t, functions f, items i, hosts h where (t.triggerid div 100000000000000)=0 and e.objectid=t.triggerid and e.object=0 and t.triggerid=f.triggerid and f.itemid=i.itemid and i.hostid=h.hostid and h.status=0 order by e.clock desc,h.host,t.priority,t.description,t.triggerid limit 1000;

Next, did I miss something in rights system ? I want to disable entire access for guest and enable only some hosts/items. But I did not find the way. I can only disable host by host...

Thanx!

[limo]

When I am looking into history table, I am not database expert, but I think it is not good idea to index timestamp() because it is integer and has many values to index. Or maybe, let it be, but to increase speed, there could be column date (only date without time), which could be better indexed (has only 351 levels per year).

It can dramaticaly speedup searching in history. When using inner select, results should be very quick. It is only about choosing "window" in history where to search. We do not have to search entire history for each trigger or item. If item has 14 days to keep in history, we can search only this 14 days.

example:

select itemid,clock,value from history where _something_ and itemid in (select itemid from history WHERE date='2007-03-01')

What do you think ? Can it help ?

[Alexei]

The "select itemid from history WHERE date='2007-03-01'" will return millions of records on a large ZABBIX system. I do not think this can improve performance.

Wait, why do you think selects from the history table work slow? Please give us an example of a slow query.

[limo]

Some lines from mysql-slow.log. As I said, it is possible I have problem in some indexes, my db is upgraded from earlier versions.

cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 13
model name : Intel(R) Pentium(R) M processor 1.86GHz
stepping : 8
cpu MHz : 1867.000
cache size : 2048 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat clflush dts acpi mmx fxsr sse sse2 ss tm pbe nx up est tm2
bogomips : 3729.65

free
total used free shared buffers cached
Mem: 515780 495636 20144 0 3032 119012
-/+ buffers/cache: 373592 142188
Swap: 1510068 187440 1322628

# Query_time: 19 Lock_time: 0 Rows_sent: 1 Rows_examined: 8250
select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.hist
ory,i.lastvalue,i.prevvalue,i.hostid,h.status,i.va lue_type,h.errors_from,i.snmp_port,i.delta,i.prevo rgvalue,i.lastclock,i.uni
ts,i.multiplier,i.snmpv3_securityname,i.snmpv3_sec uritylevel,i.snmpv3_authpassphrase,i.snmpv3_privpa ssphrase,i.formula,h.avai
lable,i.status,i.trapper_hosts,i.logtimefmt,i.valu emapid,i.delay_flex,h.dns from hosts h, items i where i.nextcheck<=11757563
27 and i.status in (0,3) and i.type not in (2,7,9) and h.status=0 and h.disable_until<=1175756327 and h.errors_from=0 and h.h
ostid=i.hostid and mod(i.itemid,35)=2 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid>=1000
00000000000*0 and h.hostid<=(100000000000000*0+99999999999999) order by i.nextcheck;

# Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
insert into history_uint (clock,itemid,value) values (1175685122,34482,1);

# Query_time: 25 Lock_time: 0 Rows_sent: 2 Rows_examined: 5193
select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.hist
ory,i.lastvalue,i.prevvalue,i.hostid,h.status,i.va lue_type,h.errors_from,i.snmp_port,i.delta,i.prevo rgvalue,i.lastclock,i.uni
ts,i.multiplier,i.snmpv3_securityname,i.snmpv3_sec uritylevel,i.snmpv3_authpassphrase,i.snmpv3_privpa ssphrase,i.formula,h.avai
lable,i.status,i.trapper_hosts,i.logtimefmt,i.valu emapid,i.delay_flex,h.dns from hosts h, items i where i.nextcheck<=11756842
90 and i.status in (0,3) and i.type not in (2,7,9) and h.status=0 and h.disable_until<=1175684290 and h.errors_from=0 and h.h
ostid=i.hostid and mod(i.itemid,35)=20 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid>=100
000000000000*0 and h.hostid<=(100000000000000*0+99999999999999) order by i.nextcheck;

# Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
insert into history_uint (clock,itemid,value) values (1175685122,34482,1);

# Query_time: 12 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
delete from history_uint where itemid=28562 limit 500;

# Query_time: 502 Lock_time: 0 Rows_sent: 1000 Rows_examined: 204819
select distinct t.triggerid,t.priority,t.description,h.host,e.cloc k,e.value from events e, triggers t, functions f, items i,
hosts h where (t.triggerid div 100000000000000)=0 and e.objectid=t.triggerid and e.object=0 and t.triggerid=f.triggerid and
f.itemid=i.itemid and i.hostid=h.hostid and h.status=0 order by e.clock desc,h.host,t.priority,t.description,t.triggerid
limit 1000;