List:General Discussion« Previous MessageNext Message »
From:<vpendleton Date:March 2 2004 8:43pm
Subject:Re: no one can log on any more
View as plain text  
Are you sure you allotted 512MB for both read_buffer and 
sort_buffer_size? Try reducing these values so that the total is roughly 
80 percent of your total physical memory.

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

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


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


> The database server becomes unresponsive to new threads?
> What is the result of the following equation?
> key_buffer_size + ( (read_buffer_size + sort_buffer_size) * max
> _connections )

> 256 +((512 +512)*100)=100.25Gb

> Yup, thats bigger that our physicall memory (100 Gb > 8 Gb)!!!
> So I guess that might be the problem.... ;-)

> Does the i/o error also relate to this problem?

> Is this larger than you physicall memory?

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

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


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