SQL Reports High Number of DELETE QUERIES

[bestpa]

Hi Everyone;

I've got a mid-size cacti deployment (50 devices, 5000 data sources) on a Ubunut 9.10 Xeon dual-core, dual-processor @3GHz server w/ 4GB RAM and . Running cacti with Spine on a 5 minute poll cycle.

We are seeing periodic system slowdowns and are trying to understand the SQL queries breakdown. We seeing a high number of query type "DELETE". There are no other apps using the mysql database on the system.

Any ideas as to what this could be or how I can investigate or tune my system better?


Total ø per hour ø per minute ø per second
2,709 k 164.12 k 2.74 k 45.59
Query type ø per hour %
delete 2,474 k 149.871 k 91.81%
select 66 k 4.003 k 2.45%
update 42 k 2.520 k 1.54%
insert 11 k 672.523 0.41%
set option 2,814 170.494 0.10%
replace 1,809 109.603 0.07%
show variables 1,397 84.641 0.05%
change db 1,396 84.580 0.05%
show status 1,390 84.217 0.05%
show fields 1,048 63.496 0.04%
Query type ø per hour %
admin commands 295 17.873 0.01%
show table status 258 15.632 0.01%
show create table 252 15.268 0.01%
show triggers 252 15.268 0.01%
truncate 198 11.996 0.01%
show tables 55 3.332 0.00%
show binlogs 17 1.030 0.00%
show databases 10 0.606 0.00%
show create db 8 0.485 0.00%
show master status 7 0.424 0.00%
Query type ø per hour %
show slave status 7 0.424 0.00%
unlock tables 6 0.364 0.00%
lock tables 6 0.364 0.00%
show processlist 4 0.242 0.00%
show grants 2 0.121 0.00%
show collations 1 0.061 0.00%
show charsets 1 0.061 0.00%
show plugins 1 0.061 0.00%
flush 1 0.061 0.00%


SQL my.cnf config:

bestpa@fwmon-cacti:/etc/mysql$ grep -v "#" my.cnf

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 32M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
table_open_cache = 2048
tmp_table_size = 64M
query_cache_limit = 1M
query_cache_size = 32M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/

[gandalf]

On each polling cycle, the poller_output table gets filled first and then emptied and flushed to rrd files.
Latest cacti versions should bring some improvement at least to some delete operations. But I'm quite sure, that poller_output is not among them
R.

[bestpa]

Thanks for your reply. We are always interested in ways to speed up SQL operations, and this was a big question mark for us.

Regards!