Scale MySQL trigger for thousands of inserts per seconds

Question

We are using MYSQL database for an Analytics app. I have two table token and token-big (for overview of actual data) both have id(int) unix_starttime(int) unix_endtime(int) details(varchar 256)

A row is added to table token every second using python MySQLdb. I have an after insert trigger on token table which does the following:

1. Get the latest entry (last) from token-big's
2. If unix_starttime of newly inserted row (in token) is no more than 5 seconds apart from unix_endtime from token-big:
   • update unix_endtime of last row in token-big to unix_endtime of newly added row in token
   • else insert new row in token-big same as the newly added row in token

This works fine for a few token token-big table pairs but I start to get deadlocks if these pairs (tables) are in number of hundreds.

How can I scale this method? Is there any better alternative?

I don't wish to use events as it may delay the data shown to user. Same goes for background jobs which run every few minutes or so.

Answer 1

Don't use a TRIGGER. Instead, gather a minute's worth (say) of data in a staging table. Then do the desired updates en masse. If you need the user to get up-to-the-second information (instead of just up-to-the-minute), have the user's query do that final update also.

To phrase it differently, summarization is much more efficiently done in bulk.

Your statements are confusing on the scale of the problem: "thousands of inserts per second" versus "a row is added to table token every second". Which is it?

If serious scaling of inserts and processing, see my blog on staging tables. That discusses "ping ponging" of a pair of staging tables in order for the speed to be self-adjusting.

To discuss your situation further, please provide SHOW CREATE TABLE and some more details.