4

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.

2
  • Does your tables have any foreign keys? – Scratte Mar 21 '20 at 12:37
  • No it has no foreign key but primary key is there. – dheeraj saini Mar 21 '20 at 13:03
4

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.
2

You can use 2 table with the same structure and then use RENAME. RENAME is atomic and so it's not interruptible.

RENAME TABLE t1 TO tmp_table,
    t2 TO t1,
    tmp_table TO t2;
TRUNCATE Table t2;
2
  • What does it mean atomic? Is that mean when i will go to rename it. Concurrent requests will not able to read it for that duration? What will happen in that. Actually i have to refresh that data in production server DB thats why i am concered about concurrency as user not get into error state when i rename it. Please clear my doubt. – dheeraj saini Mar 21 '20 at 16:10
  • Yes, thats correct. When a concurrent request was made they can read/ write the old or the new table. So rename is not interruptebel. I have used it many years on prodiction servers to update the products and price tables in one step and it works without any errot. – Bernd Buffen Mar 21 '20 at 16:41
2

Using MySQL's Exchanging Partitions and Subpartitions with Tables you can work on a seperate identical table in your own time. When it's ready, you can then swap that in for a partition in your "real" table. The catch is that you have to create a partition of your table, but only one is required. Also from the reference mentioned (nt here is your table):

Table nt contains no foreign key references, and no other table has any foreign keys that refer to nt.

Example below is close to those of in the article, but given for completeness:


This is the "real" table:

CREATE TABLE stackoverflow (
    id INT PRIMARY KEY,
    test VARCHAR(30)
);

Create your new table before partitioning your "real" table. Then you don't have to remove anything from your copy:

CREATE TABLE stackoverflow_workinprogess LIKE stackoverflow;

Create just one partition on your "real" table:

ALTER TABLE stackoverflow PARTITION BY RANGE (id) (
    PARTITION p VALUES LESS THAN (MAXVALUE)
);

Inserting some random data for the example:

INSERT INTO stackoverflow VALUES
    (1, "Test 1"),
    (2, "Test 2"),
    (3, "Test 3"),
    (4, "Test 4"),
    (5, "Test 5");   

Now you can work on your data in the stackoverflow_workinprogess table:

TRUNCATE TABLE stackoverflow_workinprogess;
INSERT INTO stackoverflow_workinprogess VALUES
    (1, "Test a - new row"),
    (2, "Test b - new row"),
    (3, "Test c - new row"),
    (4, "Test d - new row"),
    (6, "Test e - new row"), -- skipping one on purpose
    (7, "Test f - new row");

When it's all done, swap/exchange the partitions:

ALTER TABLE stackoverflow EXCHANGE PARTITION p WITH TABLE stackoverflow_workinprogess;

Result:

select * from stackoverflow;

id  | test
----|-----------------
 1  | Test a - new row
 2  | Test b - new row
 3  | Test c - new row
 4  | Test d - new row
 6  | Test e - new row
 7  | Test f - new row

It's all included in this dbfiddle.uk


I didn't try to play around with creating the partition on the stackoverflow_workinprogess table, but I'd be surprised if the results were any different.

3
  • Thank you for this solution. I just want to know when i will swap it with partition it will acquire lock on real table. So concurrent request will not able to read data from that table?? In swapping data the data existing in partition will get overridden?? – dheeraj saini Mar 21 '20 at 16:07
  • I do not believe anything is actually overwritten by a swap. I'm sure that only the pointer to the partition is changed. It will look like everything is overwritten though. While a lot of other ALTER TABLE operations can make use of LOCK, partition_options can not. I expect the table/partition is briefly locked for all read and write operations while the swap takes place. – Insignificant Mar 21 '20 at 17:09
  • If you think it may pose problems, you should stress test it with queries while performing a swap. Even if you do not think it may pose problems, I believe you should still test this, or any other solution you want to try. – Insignificant Mar 21 '20 at 17:17
1

A table lock might work. Getting a table lock would cause other users to wait or timeout. However, for 5-10 seconds, that may be too long.

Explore if a transaction will work. Start a transaction with a BEGIN WORK statement and when done, do a COMMIT.

1
  • Actually its production DB and it might take more than usual sometimes while loading fresh data. So locking might work but i am not sure it will take 5-10 seconds always. – dheeraj saini Mar 21 '20 at 16:12
1

It looks like you are truncating records so that you can refresh the data from an API. However, this is causing problems because some people are using those records.

A better approach would be to use DELETE rather than TRUNCATE, so that you can wrap it in a transaction. I believe that table truncations are effected immediately, and so you will need the (slower) delete. It'd be something like this:

  • Start a transaction
  • Delete all records
  • Insert new records
  • Finish the transaction

5-10 seconds to insert 10K new records is probably fine - users that are using the system during this period will just see the old records.

5
  • Just to clear my doubt that when i will delete all records inside transaction and insert new records. It will not hamper user requests. so does it mean that transaction will not lock table to read old data. – dheeraj saini Mar 21 '20 at 16:16
  • I believe whether it locks your table depends on your table type. You will need to try it to make sure it does not. Would you do that, and let me know? – halfer Mar 21 '20 at 16:22
  • Its innodb type. – dheeraj saini Mar 22 '20 at 3:46
  • Ok i will try this solution. – dheeraj saini Mar 22 '20 at 3:46
  • Great. You could make the import operation artificially slow (e.g. put some sleeps in the process) and then access the website as a concurrent user. – halfer Mar 22 '20 at 12:13
0

From your description (~10k records), unless the records are yuuuge, I don't understand why you don't keep a few days' worth of data in there (with a date column that's part of the primary key) and have queries work against that.

The deletion can then be scheduled to run any time later after you are sure all existing queries against it have finished - which might not usually be long.

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.