You are here
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  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.
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.
As expected an
INSERT TRIGGER reduces our throughput. But we get more (+33%) throughput than half of the normal
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.