insert strategies to busy myisam table

Hello my friends

Here's the problem I'm facing: I have a table that has about 20 selects per second. Every hour, about 5 vendors (it's a e-commerce website) uploads additions to their inventory, inserting about 5.000 records each one and taking some precious 2-3 seconds while I get 40-60 queries sitting there and waiting to be run. (so, the next second I have 60-80 queries trying to run at the same time)

The 20 queries/second themselves are already a very intense load. I'd say if it was only them, we'd be at 60% of what we can expect to get from this server we're in. But when the inserts happen, our query time really climbs from 50 ms average to more than 2 seconds sometimes. And the mess takes many seconds to be solved by itself.

We're talking about a 3 million row CD/DVD inventory table, with many fulltext indexes (one for artist, other for album name, other for description and another one for all of them). So our inserts certainly are paying the price for that. But the fact is that we really have to keep all these indexes.

Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2853361
Avg_row_length: 244
Data_length: 696384596
Max_data_length: 281474976710655
Index_length: 876121088
Data_free: 0

+----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
Ś Table Ś Non_unique Ś Key_name Ś Seq_in_index Ś Column_name Ś Collation Ś Cardinality Ś Sub_part Ś Packed Ś Null Ś Index_type Ś Comment Ś
+----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
Ś inventory Ś 0 Ś PRIMARY Ś 1 Ś inv_id Ś A Ś 2853363 Ś NULL Ś NULL Ś Ś BTREE Ś Ś
Ś inventory Ś 1 Ś index_user_id Ś 1 Ś user_id Ś A Ś 5907 Ś NULL Ś NULL Ś YES Ś BTREE Ś Ś
Ś inventory Ś 1 Ś index_genre Ś 1 Ś genre Ś A Ś 75 Ś NULL Ś NULL Ś YES Ś BTREE Ś Ś
Ś inventory Ś 1 Ś index_ativo Ś 1 Ś ativo Ś A Ś 3 Ś NULL Ś NULL Ś YES Ś BTREE Ś Ś
Ś inventory Ś 1 Ś index_label Ś 1 Ś label Ś A Ś 45291 Ś 5 Ś NULL Ś YES Ś BTREE Ś Ś
Ś inventory Ś 1 Ś index_inv_id_user_id Ś 1 Ś user_id Ś A Ś 5907 Ś NULL Ś NULL Ś YES Ś BTREE Ś Ś
Ś inventory Ś 1 Ś index_inv_id_user_id Ś 2 Ś inv_id Ś A Ś 2853363 Ś NULL Ś NULL Ś Ś BTREE Ś Ś
Ś inventory Ś 1 Ś index_author Ś 1 Ś author Ś NULL Ś 1 Ś NULL Ś NULL Ś Ś FULLTEXT Ś Ś
Ś inventory Ś 1 Ś index_title Ś 1 Ś title Ś NULL Ś 1 Ś NULL Ś NULL Ś YES Ś FULLTEXT Ś Ś
Ś inventory Ś 1 Ś index_authoretitle Ś 1 Ś author Ś NULL Ś 1 Ś NULL Ś NULL Ś Ś FULLTEXT Ś Ś
Ś inventory Ś 1 Ś index_authoretitle Ś 2 Ś title Ś NULL Ś 1 Ś NULL Ś NULL Ś YES Ś FULLTEXT Ś Ś
Ś inventory Ś 1 Ś index_descr Ś 1 Ś descr Ś NULL Ś 1 Ś NULL Ś NULL Ś YES Ś FULLTEXT Ś Ś
+----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

I've tried LOAD DATA INFILE, but the insert didn't get much faster.

I am now thinking about some approach that deals with some temporary tables. For example: having the data inserted to a temporary table INVENTORY_TEMP and them doing only one insert per hour on the production 'inventory' table.

This way I'd tell my vendors: "Hey, ok, your inventory table was read, but the data will only show up at the searches at the next hour, ok?"

But I've done some testing, and the
INSERT INTO INVENTORY SELECT * FROM INVENTORY_TEMP;
doesn't get any faster than the sum of the inserts I would have avoided, though. (I thought that transfering from one table to another could somehow be faster, with mysql somehow merging the already built indexes. But it seems it doesn't do that)

I know INNODB's row-level lock would help not to make readers wait untill the 2 second write is done. But the fact is that I need to keep the fulltext indexes - and innodb doesn't support them, as you know. (Then I thought: "Mmm, what if I had one MYISAM table for the searches (reads) and other INNODB for the writes. But, despite the initial feeling, I couldn't go much further with the concept. Ended up thinking it was a stupid irrelevant idea I had.)

Any ideas, folks? Has anyone ran into this problem and found a good strategy?

Thanks a lot

Phoenix

PS: I'm running mysql 5 on RHEL 5.

insert strategies to busy myisam table

lots of reads per second; even with load data infile inserts are unbearable

phoenix_fly

physics

LifeinAsia

phoenix_fly

physics

physics

phoenix_fly

physics

phoenix_fly

physics

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week