List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:February 4 2011 8:21pm
Subject:Re: Table/select problem...
View as plain text  
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.
awallace@stripped
(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.
Thread
Table/select problem...Andy Wallace4 Feb
  • Re: Table/select problem...Steve Musumeche4 Feb
    • Re: Table/select problem...Johan De Meersman4 Feb
      • Re: Table/select problem...Andy Wallace4 Feb
  • RE: Table/select problem...Travis Ard5 Feb