2

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.

  • Generally it's better to get the server to do any work. That way the data is always consistently updated and you're not relying on programmers to remember that in you have to INSERT into tableb when you INSERT into tablea. – Vérace Dec 23 '16 at 20:59
  • Good point, I totally agree. I'm looking for some information how such a "chained triggering" internally works in mysql. Data insert in the huge table is all done by clients, and that are a lot. I look for a way to secure that client doesn't get delayed by some locks on the huge table. – jgnoss Dec 23 '16 at 21:45
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.