I was researching ways to improve mysql performance for inserts.
About the scenario, in a busy day, for every 3 seconds about 3000 rows for big table, another 100 for medium table and around 30 for small table concurrently. This has to be continue for 24 hours and then i'm choosing important part which is only 30.000-40.000 rows then i'm flushing all 3 tables.
So, i'm using mysql 5.5.29. All tables are using innodb, there are few indexes plus primary keys.
About the question, i saw a comment about using memory engine tables for those inserts and then moving them to primary tables:
"using LOCK TABLE .. WRITE may speed up the insertion, but when processing as well as loading rows, you will lock out any reader. A select on primary key (which should take close to 0 seconds) takes up to 0.2 seconds when my loader is busy loading batches of 1000 rows.
A very good workaround is to put the new rows into a temp table, then flush that table every now and then in a single tansaction. This makes the insert very quick, because all the checking and conversion was already done in the temp table.
CREATE TEMPORARY TABLE ..._temp (....) ENGINE=MEMORY;
-- Loop from here --
DELETE FROM .._temp;
INSERT INTO .._temp ... VALUES (...);
INSERT INTO .._temp ... VALUES (...);
LOCK TABLE ... WRITE, ..._temp READ;
INSERT INTO ... SELECT * FROM ..._temp;
UNLOCK TABLES;
-- Repeat until done --
DROP TABLE ..._temp;
What I've seen in my system, is that the INSERTs go at about the same speed as when locking the table, but the clients do not notice any performance loss at all when the loader is running."
Is that makes sense to using memory engine in that case ? Should i use innodb for avoding table locks in case of need to access rows or should i prefer to myisam for speed ?
If you need to wipe your table daily, but need to get data out of it first. The best bet would be to just quickly swap out the existing full table with a new empty table and then do whatever data operation you need on the full table afterwards. Here's how you do it.
First create a table just like the table you are trying to operate on:
CREATE TABLE big_table_temp LIKE big_table;
You now have an empty table with same schema as the big table.
Then rename the tables to swap them out:
RENAME TABLE big_table TO big_table_archive, big_table_temp TO big_table;
The beauty of this is that this operation is basically instantaneous, as MySQL just renames the binary database files on disk. Make sure you have both rename operations in one query like this as you want the operation to succeed or fail as a whole.
You can then operate on big_table_archive as you want, extracting data out of it, without worrying about blocking live queries on big_table.