Hi Doug,

Thanks for the digging; I initially thought about doing it with limit until there were no affected rows, but that was a while back and I totally forgot.

I see Mike fixed it so one should no longer run into this, and indeed, the database grows very fast so people running it for a while should hopefully also see an improvement in web interface speed and less mysql thrashing :-)

Queries don't need a ; when run from PHP, so that should be ok.

Thanks, Tom

On 07/23/2014 08:30 AM, Crozier, Doug wrote:

I have a rather old and crufty installation of Observium (now at 5668) with over 70 million syslog, eventlog, and timing records that are older than 7 days.

The first time I ran housekeeping --s to trim syslog messages it took about 30 minutes to return showing 42 million records. Just to be sure I was actually done deleting 42 million records, I re-ran the command. The same 42 million records were found. Meh.

I ran the housekeeping in debug mode and noticed that I didn't have posix support installed. So I installed the php-process package and tried again. Still no records deleted. I tried deleting fewer records by keeping 18 months worth and that didn't help either.

Here's the debug output from attempting to delete records older than 7 days:

[root@xxx ~]# php /opt/observium/housekeeping.php -s -d

DEBUG!

SQL[SELECT COUNT(*) FROM `syslog` WHERE UNIX_TIMESTAMP(`timestamp`) < 1405322208;]

42123350 syslog entries from before 2014-07-14 15:16:48 will be deleted [y/N]: y

SQL[DELETE FROM `syslog` WHERE UNIX_TIMESTAMP(`timestamp`) < 1405322208;]

Syslog housekeeping: deleted entries

Deleting the eventlog records also fails to remove the records:

[root@xxxx observium]# php housekeeping.php -e -d

DEBUG!

SQL[SELECT COUNT(*) FROM `eventlog` WHERE UNIX_TIMESTAMP(`timestamp`) < 1380059875;]

29289378 eventlog entries from before 2013-09-25 05:57:55 will be deleted [y/N]: y

SQL[DELETE FROM `eventlog` WHERE UNIX_TIMESTAMP(`timestamp`) < 1380059875]

Event log housekeeping: deleted entries

(As an aside, I'm not sure, but there may be a semi-colon missing on the delete statement for event log records.)

What I eventually found was that trying to delete this many records exhausts the lock table (?).

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Issuing the same command from the mysql CLI and adding a 10,000,000 limit allowed the deletion to work correctly

mysql> SELECT COUNT(*) FROM `eventlog` WHERE UNIX_TIMESTAMP(`timestamp`) < 1380059875;

+----------+

| COUNT(*) |

+----------+

| 28177378 |

+----------+

1 row in set (51.76 sec)

mysql> DELETE FROM `eventlog` WHERE UNIX_TIMESTAMP(`timestamp`) < 1380059875 LIMIT 10000000;

Query OK, 10000000 rows affected (3 min 40.32 sec)

mysql> SELECT COUNT(*) FROM `eventlog` WHERE UNIX_TIMESTAMP(`timestamp`) < 1380059875;

+----------+

| COUNT(*) |

+----------+

| 18177378 |

+----------+

1 row in set (37.01 sec)

Interesting to note that deleting 10 million records at a time with the LIMIT statement doesn't always work. Adjusting the LIMIT up and down eventually got me where I was going.

Now that I have it the system down to a reasonable number of records, a nightly/weekly cron job should do just fine.

Thanks for the effort you put into this Tom. For me, this was like a hardware upgrade.

---

observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium