Does after insert trigger cause delay?

Question

MySQL 5.7 innoDB

My plan: Create an after insert trigger on one table, that copies some of the inserted data to another table. Create an before insert trigger on second table that includes some evaluation (collect data from another table) before doing the insert in second table or depend on the evaluation doesn't do the insert on the second table at all.

Will the planned triggers cause any locks on the tables/records they are created? Especially is there a possibility that the before insert trigger on second table will cause a lock or insert delay on the first table?

Some background: I have a heavy loaded table with actually 120GB data (growing) in it. About 15-40 inserts/sec.

In some occasions client connections raise up explosive and cause the applications to stall.

Not 100% sure but what I see is: Some queries in some occasions on that huge table does locks and delay the inserts. That causes the client connections to raise up.

The plan above, will put some work that actually clients do into the database itself. Is there a chance that my plan does eliminate that problem.

For doing a test I need to copy that huge table on a sandbox environment and simulate the inserts. Uff. So I ask before, is there is a chance that my plan works before occupy the net with all that data load.

Answer 1

The question is not the same, but my answer is: https://dba.stackexchange.com/a/159012/1876

Don't use Triggers; instead batch the data and batch the processing. It is a lot more efficient than doing everything one row at a time, especially when a spike occurs.

Another issue... If you let "too many" connections get started, yes, the entire system can stall. This is sometimes because MySQL (and the OS) are carefully parceling out resources (CPU, I/O, etc) to more clients than the resources (cores, etc) can handle. Throughput may be fine, but latency suffers terribly.