List:General Discussion« Previous MessageNext Message »
From:Bernd Jagla Date:March 2 2004 6:08pm
Subject:Re: no one can log on any more
View as plain text  
An explain on the select statements reveals that no index is used and that
all 133856002 rows are searched.

Do you think that using a new index would make much of difference?
I think it has something to do with some system/mysql parameters that can be
adjusted.
I also don't understand why no one else can log on to the mysql system.

If you or someone could explain or point me to a documentation on this it
would be pretty close to what I want.

Thanks for your help

Bernd

----- Original Message ----- 
From: <vpendleton@stripped>
To: "Bernd Jagla" <jaglab@stripped>
Cc: "mysql" <mysql@stripped>
Sent: Tuesday, March 02, 2004 1:02 PM
Subject: Re: no one can log on any more


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