How good is MySQL INSERT TRIGGER performance

Abstract: In this article we discuss how big is the performance impact of MySQL TRIGGERs compared to application side logging (with INSERT) into a MySQL table.

What was in my mind from the past

A while ago when MySQL released its Stored Language features in v5.0 I have seen a book [1] about this topic. In this book was a performance comparison between different implementations of computational tasks, one was done in MySQL Stored Language. The result was, that MySQL Stored Language feature sucks also performance wise.

Now a customer of us wanted to use TRIGGERs to log/track some database activity. Because I am not a big fan of Stored Languages at all and because I had this performance comparison in mind I was not convinced if this is a good idea but I did not know it for sure and wanted to give an answer based on facts.

The Test

To find out how much the performance impact of MySQL TRIGGERs really is we made some little benchmarks. For this benchmark we used the following log table:

CREATE TABLE log (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

and modified the common.lua script for sysbench by adding the following TRIGGER on the table:

DROP TRIGGER test_trigger;

CREATE TRIGGER test_trigger AFTER INSERT ON sbtest1
FOR EACH ROW BEGIN
  INSERT INTO log (id, data, ts)
  VALUES (NULL, CONCAT('We log that user x has done '
                     , NEW.c, ' in record ', NEW.id), NULL);
END;

Then we run sysbench against a MySQL v5.5.14 database.

The Results

As expected an INSERT TRIGGER reduces our throughput. But we get more (+33%) throughput than half of the normal INSERT throughput.

Conclusion

With TRIGGERs we get always more throughput than half of the INSERT throughput. It looks like TRIGGERs are more efficient for logging than doing it manually in the application.
If MySQL Stored Language is NOT used for computational tasks but for SQL tasks the performance might be OK.