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 [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.

MySQL Stored Program comparison

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.

trigger_throughput.png

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.

AttachmentSize
File insert_trigger_perfromance.tgz1.75 KB

Comments

I don't think that 2X slower than Oracle sucks. Oracle has put a lot of resources into making PL/SQL fast. PHP results aren't very good on this test -- only 2X faster than Oracle and 4X faster than MySQL.
Mark Callaghancomment

I think Oli makes exactly that point here. I think it's correct to say that if you want performant code for some *computation*, then SQL is a bad choice, even PL/SQL. Whether MySQL or Oracle is "less bad" is kind of uninteresting, I agree with you MySQL performance is acceptable if it's within 2x from Oracle. The point with the given test here (which I didn't understand at first) is that using a trigger (or other stored procedure) you can save network roundtrips. In this case it turns out that writing your log table from a trigger is faster than writing 2 rows from sysbench.
Henrik Ingocomment

Henrik - your conclusions are too strong. If you want performant code then SQL, PL/SQL, whatever might be a bad choice. Or it might be a good choice. This just shows that that MySQL stored procedures are slower than PL/SQL and insert triggers slow down an insert. This also shows that PHP is slow and that hasn't prevented successful deployments of it. There are many other things to consider when deciding whether to use a stored procedure or do it on the client side.
Mark Callaghancomment