Hi,
We, in our company plan to use zabbix to monitor a lot of network devices. Around 12K. router, switch,...
We have installed PGSQL 16 and timescaleDB 2.17 on a 10 CPU/256 Gb RAM.
Zabbix server 7.0.2 on a 16CPU/32Gb RAM
And 14 proxies, grouped by pair, on 16 CPU/32Gb RAM.
We are now around 3K nvps with 5K devices. Not reached the target yet.
We note that we have a lot of slow queries but don't know where to look at.
For exemple we have these lines in the zabbix server log file:
299149:20250205:090810.971 slow query: 950.138332 sec, "select clock,ns,value from history_uint where itemid=3049818 and clock>1738741940"
299148:20250205:090810.971 slow query: 951.141841 sec, "select clock,ns,value from history_uint where itemid=3049817 and clock>1738741939"
299151:20250205:090827.355 slow query: 950.450215 sec, "select clock,ns,value from history_uint where itemid=3053133 and clock>1738741956"
299147:20250205:090827.355 slow query: 952.477683 sec, "select clock,ns,value from history_uint where itemid=3052832 and clock>1738741954"
299150:20250205:090827.355 slow query: 949.446930 sec, "select clock,ns,value from history_uint where itemid=3053075 and clock>1738741957"
299054:20250205:092258.754 slow query: 3.312274 sec, "update ids set nextid=nextid+6 where table_name='items' and field_name='itemid'"
299057:20250205:092309.381 slow query: 3.168518 sec, "update ids set nextid=nextid+32 where table_name='items' and field_name='itemid'"
299061:20250205:092309.849 slow query: 3.533518 sec, "update ids set nextid=nextid+6 where table_name='items' and field_name='itemid'"
299059:20250205:092310.110 slow query: 4.123419 sec, "update ids set nextid=nextid+2 where table_name='items' and field_name='itemid'"
299058:20250205:092310.206 slow query: 4.082658 sec, "update ids set nextid=nextid+1 where table_name='items' and field_name='itemid'"
299150:20250205:102349.339 slow query: 949.127274 sec, "select clock,ns,value from history_uint where itemid=3452097 and clock>1738746480"
299147:20250205:102353.435 slow query: 952.217546 sec, "select clock,ns,value from history_uint where itemid=3452098 and clock>1738746481"
299151:20250205:102353.435 slow query: 951.213368 sec, "select clock,ns,value from history_uint where itemid=3452099 and clock>1738746482"
299149:20250205:103339.163 slow query: 948.770992 sec, "select clock,ns,value from history_uint where itemid=3452927 and clock>1738747070"
299148:20250205:103343.259 slow query: 951.864904 sec, "select clock,ns,value from history_uint where itemid=3452928 and clock>1738747071"
Also we have some queue but don't know where to look at also.
Can you give me some tips or where to diagnose ?
Is housekeeper be enabled when we use TimescalDB ?
Compression is enable and override
We, in our company plan to use zabbix to monitor a lot of network devices. Around 12K. router, switch,...
We have installed PGSQL 16 and timescaleDB 2.17 on a 10 CPU/256 Gb RAM.
Zabbix server 7.0.2 on a 16CPU/32Gb RAM
And 14 proxies, grouped by pair, on 16 CPU/32Gb RAM.
We are now around 3K nvps with 5K devices. Not reached the target yet.
We note that we have a lot of slow queries but don't know where to look at.
For exemple we have these lines in the zabbix server log file:
299149:20250205:090810.971 slow query: 950.138332 sec, "select clock,ns,value from history_uint where itemid=3049818 and clock>1738741940"
299148:20250205:090810.971 slow query: 951.141841 sec, "select clock,ns,value from history_uint where itemid=3049817 and clock>1738741939"
299151:20250205:090827.355 slow query: 950.450215 sec, "select clock,ns,value from history_uint where itemid=3053133 and clock>1738741956"
299147:20250205:090827.355 slow query: 952.477683 sec, "select clock,ns,value from history_uint where itemid=3052832 and clock>1738741954"
299150:20250205:090827.355 slow query: 949.446930 sec, "select clock,ns,value from history_uint where itemid=3053075 and clock>1738741957"
299054:20250205:092258.754 slow query: 3.312274 sec, "update ids set nextid=nextid+6 where table_name='items' and field_name='itemid'"
299057:20250205:092309.381 slow query: 3.168518 sec, "update ids set nextid=nextid+32 where table_name='items' and field_name='itemid'"
299061:20250205:092309.849 slow query: 3.533518 sec, "update ids set nextid=nextid+6 where table_name='items' and field_name='itemid'"
299059:20250205:092310.110 slow query: 4.123419 sec, "update ids set nextid=nextid+2 where table_name='items' and field_name='itemid'"
299058:20250205:092310.206 slow query: 4.082658 sec, "update ids set nextid=nextid+1 where table_name='items' and field_name='itemid'"
299150:20250205:102349.339 slow query: 949.127274 sec, "select clock,ns,value from history_uint where itemid=3452097 and clock>1738746480"
299147:20250205:102353.435 slow query: 952.217546 sec, "select clock,ns,value from history_uint where itemid=3452098 and clock>1738746481"
299151:20250205:102353.435 slow query: 951.213368 sec, "select clock,ns,value from history_uint where itemid=3452099 and clock>1738746482"
299149:20250205:103339.163 slow query: 948.770992 sec, "select clock,ns,value from history_uint where itemid=3452927 and clock>1738747070"
299148:20250205:103343.259 slow query: 951.864904 sec, "select clock,ns,value from history_uint where itemid=3452928 and clock>1738747071"
Also we have some queue but don't know where to look at also.
Can you give me some tips or where to diagnose ?
Is housekeeper be enabled when we use TimescalDB ?
Compression is enable and override