0

I am trying to auto-archive active database table to archive database table using stored procedure and event in MariaDB10. These are the steps I am following:

  1. Create new table everyday just before midnight in archive database.
  2. copy rows from active table to archive table.
  3. delete all rows from active table.

This seems to work mostly except one issue. PLC is writing to active database table every second. When the above mentioned process is taking place, there is a gap of 2 mins between last entry of archive table and first entry of active table (meaning active table is loosing 2 mins worth of data i.e 120 rows). As per my understanding this 2 mins gap is when rows are copied over to arch table and deleted from active table. There are approximately 87K rows(256MB)generated in a day.

Question is how can I reduce this 2 mins gap (loss of rows/data)? Any help or pointers would be really helpful.

I just noticed that for step 2, I am using INSERT INTO with SELECT, probably this is locking the rows of active table hence PLC is not able to insert new rows to the active table thus loosing the 2 mins of data/rows. IT is innoDB engine. Is this thinking correct? If yes, any way to get around it?

PS: I have instrumentation engineering background (and minimal experience of DBA) and I have inherited this PLC-MariaDB setup. Original DB is not indexed, also, there is no auto-archiving taking place. Thus DB is growing in size and affecting the performance when doing any queries on DB. I have indexed the DB now and performing auto-archiving.

| improve this question | | | | |
  • did you try pt-archiver? – jerichorivera Aug 20 '19 at 22:05
  • @jerichorivera, no. MariaDB10 is installed on Synology NAS with DSM OS (its a limited capacity version of Linux) and there is no option to install percona toolkit on it. – Akshay Aug 21 '19 at 8:52
0

The perhaps obvious solution would be to modify step 3: Don't just delete all rows from the active table. Instead, find a column you can use as an indicator of whether the rows are newer or older than the most recent row that will be copied to the archive table/database.

This column can be e.g. an integer used for the primary key column, or if you don't have that, perhaps a timestamp column.

So instead of deleting every row, you can do something like:

DELETE FROM active_table WHERE id <= @maxIdThatWasArchived; 

Or something like:

DELETE FROM active_table WHERE ts <= @maxTimeStampThatWasArchived; 
| improve this answer | | | | |
  • Yes, I am using similar query to delete rows in step 3. DELETE FROM active_reading WHERE DateTime<@datetimevar; – Akshay Aug 19 '19 at 15:59
  • I just noticed that for step 2, I am using INSERT INTO with SELECT, probably this is locking the rows of active table hence PLC is not able to insert new rows to the active table thus loosing the 2 mins of data/rows. IT is innoDB engine. Is this thinking correct? If yes, any way to get around it? – Akshay Aug 19 '19 at 16:14

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.