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]
