List:General Discussion« Previous MessageNext Message »
From:Bernd Jagla Date:March 2 2004 3:05pm
Subject:no one can log on any more
View as plain text  
Hi there,

I have some weird sql statements that seem to eat up all resources from mysqld.
Once I have two of them running at the same time no one can log on any more. Simple
queries involving distinct get a out of memory error.

We have tried changing the index buffer size from 256M to 2G, no change. We have 7G of
memory on an IRIX system with 8 cpus.
No replication.

Do you have any idea what is happening?

Thanks a lot

Bernd


the sql statements look like this:

insert into rule2 
select id
from text_uniq_bin 
where (substring(rev_rep,1,1)="1") AND 
(
substring(replaced,1,1) + 
substring(replaced,2,1) +
substring(replaced,3,1) +
substring(replaced,4,1) +
substring(replaced,5,1) +
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1)  
) >2 AND 
(substring(replaced,3,1)="0") AND 
(
substring(replaced,12,1) + 
substring(replaced,13,1) +
substring(replaced,14,1) +
substring(replaced,15,1) +
substring(replaced,16,1) +
substring(replaced,17,1) +
substring(replaced,18,1) +
substring(replaced,19,1)  
) >5
;

AND 

insert into rule3 
select id
from text_uniq_bin
where (substring(rev_rep,1,1)="1") AND 
(
substring(replaced,1,1) + 
substring(replaced,2,1) +
substring(replaced,3,1) +
substring(replaced,4,1) +
substring(replaced,5,1) +
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1)  
) >2 AND 
(substring(rev_rep,3,1)="0") AND 
(
substring(replaced,12,1) + 
substring(replaced,13,1) +
substring(replaced,14,1) +
substring(replaced,15,1) +
substring(replaced,16,1) +
substring(replaced,17,1) +
substring(replaced,18,1) +
substring(replaced,19,1)  
) <=5 AND
(substring(rev_rep,2,1)="0") AND 
(
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1) + 
substring(replaced,10,1) + 
substring(replaced,11,1) + 
substring(replaced,12,1) + 
substring(replaced,13,1) +
substring(replaced,14,1) +
substring(replaced,15,1) +
substring(replaced,16,1) +
substring(replaced,17,1) +
substring(replaced,18,1) +
substring(replaced,19,1)  
) >7
Thread
no one can log on any moreBernd Jagla2 Mar
  • Re: no one can log on any morevpendleton2 Mar
  • Re: no one can log on any moreBernd Jagla2 Mar
    • Re: no one can log on any morevpendleton2 Mar
  • Re: no one can log on any moreBernd Jagla2 Mar
    • Re: no one can log on any morevpendleton2 Mar
  • Re: no one can log on any moreBernd Jagla2 Mar
    • Re: no one can log on any morevpendleton2 Mar
  • Re: no one can log on any moreBernd Jagla2 Mar
    • Re: no one can log on any morevpendleton2 Mar
  • Re: no one can log on any moreBernd Jagla2 Mar
    • Re: no one can log on any morevpendleton2 Mar
  • Re: no one can log on any moreMartijn Tonies2 Mar
  • Re: no one can log on any moreBernd Jagla2 Mar
    • Re: no one can log on any morevpendleton2 Mar
  • Re: no one can log on any moreSasha Pachev2 Mar