List:General Discussion« Previous MessageNext Message »
From:Bernd Jagla Date:March 2 2004 8:04pm
Subject:Re: no one can log on any more
View as plain text  
The system is working fine.
There is no error message when logging on from the command line.
You just don't get to the prompt.
On the web you obviously get a time-out error.
We are running 4.0.12-standard-log

I guess the substring function doesn't make any use of temp tables etc since
it is working on a per record basis.

When we are using a moderate table when using select count(distinct
field_name) from table while running the two queries
we get the following error message:

Error5 Out of memory
=> i/o error.

Other sql statements run fine. Of course you had be logged on to the mysql
server already.



B

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


What error message, if any is thrown when someone attempts to log in when
the two queries are running? Is the system as a whole performing slowly?
What version of MySQL are you running?


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

On 3/2/04, 1:37:34 PM, Bernd Jagla <jaglab@stripped> wrote regarding Re:
no one can log on any more:


> table: text_data
> type: ALL
> possible_keys: NULL
> key: NULL
> key_len: NULL
> ref: NULL
> rows 133856002
> Extra: Using where

> No temp tables created, no i/o problem, no swapping.


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


> Can you post the explain plan? I would be interested to see if temp table
> tables are being created or if the system is swapping or if the the temp
> tables are so large that the entire system begins to act poorly.

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

> On 3/2/04, 12:35:20 PM, Bernd Jagla <jaglab@stripped> wrote regarding Re:
> no one can log on any more:


> > There are no indices on the new table.
> > Top looks normal: approx 100-150% usage (out of 800% [8 cpus])
> > There is no major i/o problem: sar -d says that less than 2% of io are
> used.

> > The only problems in this direction might be connected with memory and
> the
> > associated parameters in mysql.
> > The file system cache is 5GB. I hope this is no problem. mysqld uses
> about
> > 54619 K (gmemusage)
> > Top shows the actual size as 1794M and Res=1609

> > The problem only occurs when I do two similar queries at the same time.
> Both
> > queries access the same table for read (not write). Might this be the
> > problem? How would I solve this one?

> > Bernd

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


> > When the queries in question are running, what does top look like? With
> > an insert that large it may be beneficial to disable any keys that may
be
> > active on the target table and re-enable them after the data has been
> > loaded.

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

> > On 3/2/04, 12:08:05 PM, Bernd Jagla <jaglab@stripped> wrote regarding
Re:
> > no one can log on any more:


> > > 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