Avoiding table-locking in big tables

[EIke Budde]

Hi everybody,

I've been doing research on this topic, found some equal questions but no satisfying answer, so maybe there is none.

I'm having a huuuuuge table with lots of queries (3000-5000/min, insert and select) and about 3-4 million rows. The application is optimized now so that the db- and apache-load and processes are performing pretty good.
My problem is, as soon as I'm doing a bigger query on the table, the table gets locked and even a log lasting one minute results in service disruption.
I know acid-rules and why table locking is used, but in this case, 100% correct data is less important than speed and nonlocking.
So the question is, can I solve this?
These scenarios are in my mind:
select without locking -> did not seem to work, "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" did not change anything.
insert without locking -> INSERT DELAYED, can that help? The SELECTS will still block each other, I think.
Switching to INNODB -> not sure if INNODB can handle the load & size of the DB.
Last chance, setting up a replication and doing reports on the replicated DB. This is a step that I would like to avoid because its a lot of work and double disk space.

Thanks for any help!
Eike