List:General Discussion« Previous MessageNext Message »
From:<vpendleton Date:March 2 2004 6:02pm
Subject:Re: no one can log on any more
View as plain text  
What does the explain plan reveal?

>>>>>>>>>>>>>>>>>> Original Message
> <<<<<<<<<<<<<<<<<<

On 3/2/04, 9:05:17 AM, Bernd Jagla <jaglab@stripped> wrote regarding no 
one can log on any more:


> 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