Interesting... we have a process that runs once a night to delete old data (we
are only
keeping events from the last 6 months).
And I believe you have described the primary issue exactly - the read is
locking the
table, so the writes get blocked. All inserts do happen at the end of the
table, but
since we do delete records, and usually several thousand at a time (delete from
event_log
where date < some value), I don't think that the concurrent_insert option will
work for
us, as we would like the space to be reused when possible.
I'll look into partitioning the table, thanks!
I'm still leaning towards changing the table to InnoDB to avoid the locking
problem,
I made this one MyISAM because I thought that selects would be faster. But the
main
issue is, we do a LOT of inserts (there are upwards of 50-100 inserts blocked
when the
one select locks the table).
thanks! And thanks Steve, as well. I don't think that the periodic import of
events
from a file will work for us, we need relatively timely reporting from the
table. We
don't have a lot of hits for the event reports, but when we do get them, they
want the
data up-to-date...
andy
On 2/4/11 12:12 PM, Johan De Meersman wrote:
Do you delete data from the table ?
MyISAM will only grant a write lock when there are no locks on the table -
including
> implicit read locks. That may be your problem.
There is a single situation when concurrent reads and writes are possible on
MyISAM,
> however: when your table has no holes in the data. At that point, selects
happen on
> the existing data, while the insert queue is handled (sequentially) at the
same time.
If that is indeed your problem, you may "fix" the table using OPTIMIZE TABLE.
Two other options might be:
* set the variable concurrent_insert to 2 - this will allow concurrent
inserts at
> the end of the table even when there are holes.
Downside is that freed space (from deletes) is not reused.
* use INSERT DELAYED. Code returns immediately, but you have no way of
knowing wether
> or not any given insert succeeded.
If you delete data, but only relatively old data, you might also benefit from
partitioning
> the table: I'm not sure about this, but it seems reasonable that concurrent
updates would
> be possible on partitions with no holes. Should try this sometime.
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
