Mysql – Auto-archive MariaDB10 database table

archivemariadbMySQL

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:

Create new table everyday just before midnight in archive database.
copy rows from active table to archive table.
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.

Best Answer

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;

Related Solutions

Mysql – How does thesql decide if concurrent insert is possible for a MyISAM table

While you can do what Rolando suggests and set concurrent_insert=2 to always enable concurrent inserts, to answer your question about filling holes:

we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Yes (emphasis mine):

If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. [src]

Disclaimer: I haven't actually tested it. It seems unless you inserted the exact same data-length in the holes, you will still have holes somewhere.

You can see if there are holes from a query such as this (data_free=0 would mean no holes):

SELECT table_name, data_free FROM information_schema.tables WHERE table_schema='FOO' AND engine='myisam'

MySQL auto increment problem with deleting rows / archive table

You can check the AUTO_INCREMENT value from INFORMATION_SCHEMA like this:

SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='x';

you should also see the AUTO_INCREMENT with

SHOW CREATE TABLE mydb.x\G

SUGGESTION

If the table has barely 1000 rows, you could manually compress it and force to have MAX(id) each time. Suppose the table looks something like this:

USE mydb
CREATE TABLE x
(
    id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (id)
);

Try doing OPTIMIZE TABLE manually as follows:

InnoDB or MyISAM tables with no non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

MyISAM tables with non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
ALTER TABLE x_new DISABLE KEYS;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x_new ENABLE KEYS;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

This should preserve all id's and assign AUTO_INCREMENT appropriately.

It should work just fine with MySQL Replication.

CAVEAT

If the ids are different for any reason between Master and Slave, blame MySQL (I mean blame Oracle) : http://dev.mysql.com/doc/refman/5.5/en/replication-features-auto-increment.html

If the aforementioned suggestion does not rectify this, there is only one thing left to do and it is guaranteed to work. What is it?

Run this on the Master Only:

mysqldump -uroot -p... --triggers mydb x > mydb_x.sql
mysql -uroot -p... -Dmydb < mydb_x.sql

With a table of 1000 rows, this should

go pretty quickly
replicate and produce an exact copy on the slave
restore auto_increment behavior to Master and Slave

Give it a Try !!!

UPDATE 2013-02-11 19:30 EDT

Let's assume that x and xhistory have identical layouts. Let's also add some columns:

CREATE TABLE x
(
    xid int(10) unsigned NOT NULL AUTO_INCREMENT,
    col1 ... ,
    col2 ... ,
    col3 ... ,
    PRIMARY KEY (xid)
) ENGINE=MyISAM AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE xhistory
(
    xid int(10) unsigned NOT NULL DEFAULT '0',
    col1 ... ,
    col2 ... ,
    col3 ... ,
    PRIMARY KEY (xid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Perhaps you could just use the REPLACE command. It mechanically operates as either an INSERT or UPDATE via DELETE and INSERT.

REPLACE INTO xhistory SELECT * FROM x;
TRUNCATE TABLE x;

Previously existing rows get deleted from xhistory and then inserted from x into xhistory. New rows are simply inserted.