Locked table

Mucello

I'm using MySQL and primarily work with one very large table. I have scripts to insert new rows several times per hour, and each run takes several minutes to complete.

The problem is that no select statement works while data is being inserted. For each select or update command on the processlist, the status is "Locked" I think I understand why it's doing that. After a day or two, several hundred processes build up that are Locked and everything comes to a grinding halt.

How do I get around this issue? Is there something I can configure in my.cnf to alleviate this problem?

Thanks,
Mark

leatherback

1> - Why does your insert take so long
(e.g.: What is the query trying to do?)

2> - Is one table the best setup
3> - How do the processes build up? Just 'waiting in line'?

Mucello

The inserts and updates take so long because we're inserting or updating thousands of rows at a time. We're talking hundreds of megs of data.
Yes one table is the best setup. We used to use multiple tables but recently consolidated to one table for the purpose of speeding up mapserver queries.
The processes that build up are simple select statements, waiting in line until the insert or update is finished. The status of these selects is "Locked"

So is there a way to allow the select statements to go through anyway regardless of insert or update on the table?

leatherback

--out of my knowledge-leage, sorry--