mysql heavy write and heavy read

I got an app that perform only two operations.

1. Produce about 300K log entries about the status of 10K hardware entities in 30 mins. ie, 1 entry / 1 entity / 1 minute

2. Mail to respective Ad开发者_运维百科min if 4 failures occur for a particular entity ie., every 4 minutes I got retrieve 4 status entries for each 10K entities and mail, if necessary.

   Now I got two tables Entity, StatusEntries with foreign key constraint. Now I put dummy entries with out checking hardware entities. Still my processor shoots up.

   Should I switch to MyIsam. I tried replication in the same machine, it further shoots up the processor.

   Suggest me a feasible solution to this problem.

Thanks.

300K log entries about the status of 10K hardware entities in 30 mins

About 166 INSERTs/s.

every 4 minutes I got retrieve 4 status entries for each 10K entities

About 41 simple SELECTs/s

You should not have any problems with that, it's not a very heavy load.

Can you give more details about table structure, how you do your INSERTs, your SELECTs ?

Indexes should definitely not be created for everything, only relevant indexes (those which actually speed up your queries) are ... relevant ... and worthy to pay the cost of updating them at each insert !