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 <[EMAIL PROTECTED]> wrote regarding Re: no one can log on any more: > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: "Bernd Jagla" <[EMAIL PROTECTED]> > Cc: "mysql" <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> 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: <[EMAIL PROTECTED]> > > To: "Bernd Jagla" <[EMAIL PROTECTED]> > > Cc: "mysql" <[EMAIL PROTECTED]> > > 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 <[EMAIL PROTECTED]> 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: <[EMAIL PROTECTED]> > > > To: "Bernd Jagla" <[EMAIL PROTECTED]> > > > Cc: "mysql" <[EMAIL PROTECTED]> > > > 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 <[EMAIL PROTECTED]> 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: <[EMAIL PROTECTED]> > > > > To: "Bernd Jagla" <[EMAIL PROTECTED]> > > > > Cc: "mysql" <[EMAIL PROTECTED]> > > > > 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 <[EMAIL PROTECTED]> 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: <[EMAIL PROTECTED]> > > > > > To: "Bernd Jagla" <[EMAIL PROTECTED]> > > > > > Cc: "mysql" <[EMAIL PROTECTED]> > > > > > 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 <[EMAIL PROTECTED]> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
