0

I have a very large MyISAM table named raw_detection (>100 million records). Data is continuously being inserted on it. When I need to do any long running operation like backups, complex queries, index creation, etc. the inserts get locked until the long running operation ends. Is there any way to avoid this?

Here is an example of query that hangs the inserts:

insert into `agreagate_months`
select year(r.created_at), month(r.created_at), right_holder_id, city_id, count(distinct r.id) from 
raw_detection r  
join audio_sources aso on aso.id = r.`audio_source_id` 
join cities c on c.id = aso.`city_id`
join track_right_holders tr on tr.`track_id` = r.`track_id`
where 
r.duplicated = 0 and 
r.score > 40 and
r.created_at >= '2014-11-1' and 
r.created_at < '2014-12-1' and 
tr.role = 'Interpreter'
group by 1,2,3,4;

MySQL version is MariDB 10.0.16 running on a server with 4GB RAM and 4 CPUs

Daniel Cukier
  • 11,502
  • 15
  • 68
  • 123
  • No way to avoid as far as I know...MYISAM locks the whole table...Can you convert to Innodb? That way you only have row level locking, not table level locking... – BK435 Mar 20 '15 at 18:07
  • I tried to use Innodb, but then I had another problem: slow performance for almost all queries (http://stackoverflow.com/questions/29168374/complex-query-performance-in-mysql-innodb-versus-myisam?noredirect=1#comment46561194_29168374) – Daniel Cukier Mar 20 '15 at 18:12
  • Can you set up a slave to do your backups and complex queries on? – BK435 Mar 20 '15 at 18:21
  • @BK435 yes, this could be a solution, but if there is something less complex... – Daniel Cukier Mar 20 '15 at 18:23
  • Setting up a slave is really not that complex...maintaining it is a bit of a hassle...you cannot get around table level locking in MYISAM – BK435 Mar 20 '15 at 18:24
  • Is INSERT DELAYED (http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html) an option? – Daniel Cukier Mar 20 '15 at 19:06
  • SO instead of being locked out of the table, you want to delay the inserts?? You are just delaying the insert rather than getting a lock out from table being occupied by another thread...how is that a solution?? – BK435 Mar 20 '15 at 19:15

0 Answers0