0

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.

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.

10
  • By "thousands of inserts per second" i meant concurrent inserts to different tables every second. (1 row is inserted to 1000 different tables every second). I will benchmark results with insert + update with same query. thanks for your answer – Junaid May 7 '15 at 10:21
  • It is rare to see a schema design that needs 1000 tables. So, I question the wisdom of that aspect. Please elaborate. Do not give each user (or widget or whatever) its own identical table. But them all in the same table. (Or small set of dissimilar tables.) – Rick James May 7 '15 at 16:09
  • But at that rate a single table will have 86 million entries every day. data retrieval from such a big table will also take too much time. hence those many tables. let me know if my understanding about mysql performance is incorrect. – Junaid May 7 '15 at 17:54
  • A billion-row table handle some queries in milliseconds. Other queries will take hours. Let's see the SELECTs you need to run. – Rick James May 7 '15 at 21:02
  • For Detailed event i use. SELECT details, unix_starttime, unix_endtime FROM $token WHERE unix_starttime >= $START_DATE AND endtime <= $END_DATE limit 500; – Junaid May 8 '15 at 5:18

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.