I have a memory table and I am updating it somewhere around 3000 updates per second. I setup a another table in Innodb and then created a Trigger so that all data can be send to the innodb table as a backup since memory table can disappear if server reboots.
My problem is I get errors like this Waiting for table level lock, from my show process list. I am unable to update Memory table anymore and all my operations blocked.
As soon as I delete these triggers no more locks and I can continue updating the tables.
Can anyone help with this, do I need to create triggers differently or some setting need to be changed ?
Best Answer
In one situation, I found it possible to ingest data faster into InnoDB than into MEMORY. Perhaps you should simply get rid of the MEMORY table and the TRIGGER.
I discuss that an many other tidbits in my high-speed ingestion blog.
Do you have multiple inserters? Do you insert multiple rows in a single INSERT? Are you pulling the data out of the MEMORY table? How often, how, when, etc? Is Replication involved? Is "normalization" happening? Summarization?