MySQL – Truncate Table and Insert New Records Daily

database-designMySQL

I have my database in which I have to store new data on daily basis fetching from API. The problem is that I have to truncate table everyday and then insert newly fetched records in same table. Records count would be around 10k. It takes 5-10 seconds to insert new records.

So my concern is that if I truncate the table and some users are requesting at a time so he will end up with error because I truncated the table. So can you suggest what should I do to tackle this problem? I am not a DBA so please suggest what should be the better way to do it.

Best Answer

Instead, do

CREATE TABLE new LIKE real;
load new data into `new` by whatever means
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

Notes:

The second step is the only slow step.
The RENAME is atomic.
You can continue to read the table during the process.
Do not use any transactions or table locks.

Related Solutions

MySQL Performance – INSERT ON DUPLICATE UPDATE with Large BLOB Causes Query to Fail

This sounds like you have to increase the size of your MySQL Packets

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

Since MySQL Packets can hold rows of data, larger items in the packet can cause a lot of packets to filter in-and-out to prevent whole chunks of related data from splitting during processing. This can be a silent killer of DB connections for no apparent reason. If fact, I wrote a post about how this can affect certain mysqldumps.

Try increasing the max_allowed_packet (256M) using the following command:

SET max_allowed_packet = 1024 * 1024 * 256;

Database design: how to handle the “archive” problem

It's not clear to me if these requirements are for auditing purposes or just simple historical reference such as with CRM and shopping carts.

Either way, consider have an main and main_archive table for each major area where this is required. "Main" will only have current / active entries whereas "main_archive" will have a copy of everything that ever goes into main. Insert / update into main_archive can be a trigger from insert / update into main. Deletes against main_archive can then run across a longer period of time, if ever.

For the referential issues such as Cust X bought Product Y, the easiest way to solve your referential concern of cust_archive -> product_archive is to never delete entries from product_archive. Generally, churn should be much lower in that table so size shouldn't be too bad of a concern.

HTH.

Best Answer

Related Solutions

MySQL Performance – INSERT ON DUPLICATE UPDATE with Large BLOB Causes Query to Fail

Database design: how to handle the “archive” problem

Related Question