2

I'm working on Nagios plugin to monitor our rsyslog server, which is back-ended into a MySQL database. The plugin operates by generating a unique string, sending it to syslog, and then querying the MySQL DB for the unique string, hence proving that the service is correctly receiving and storing messages.

The table is, unsurprisingly, big (~3.5 million rows, 2.1GB on disk), and in InnoDB format. The column holding the syslog messages is of type LONGTEXT.

I'm currently sending it queries of the form:

SELECT count(*) FROM allremote WHERE message LIKE '%check_rsyslogdb_1328869942%';

Where in this case check_rsyslogdb_1328869942 is the unique string logged by the plugin.

Is there a more efficient way for me to approach this query?

I looked into FULLTEXT indexes, but they are MyISAM only, and as a policy we avoid MyISAM because, it is, apparently, inferior.

Bart B
  • 661
  • 8
  • 18
  • 2
    http://stackoverflow.com/questions/1381186/fulltext-search-with-innodb – Gary Chambers Feb 10 '12 at 12:01
  • `select 1 from ...... limit 1` would help a bit. Instead of counting all matching rows we just find one match then return 1. If you get no result back it means no result found. – diolemo Feb 10 '12 at 13:27
  • If you were to have a unique ID (auto_increment) then you can just check the rows since the last time you checked. So if you had ID 5000 last time you ran the check just put a condition to ensure the ID > 5000 before the string match condition. – diolemo Feb 10 '12 at 13:29

1 Answers1

0

It turns out that since I'm using MySQL 5.* I don't need to use a LONGTEXT, I can use a VARCHAR(2048) instead. This lets me create a normal index, and since I know the exact message, I can leave off the %s as long as I add in an extra trailing space at the end (not sure where that's coming from).

Bart B
  • 661
  • 8
  • 18