From: Date: June 26 2008 5:46pm Subject: Re: Performance problem with more than 500 concurrent queries List-Archive: http://lists.mysql.com/mysql/213471 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_alternative 00569874C1257474_=" --=_alternative 00569874C1257474_= Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable Sorry about the long signature in the email. I forgot to remove it... Guillermo From: Guillermo.Acilu@stripped To: mysql@stripped Date: 26.06.2008 17:39 Subject: Re: Performance problem with more than 500 concurrent queries Hello, thanks for the answer. Where is the error.log stored? I run the mysqladmin, it requires the=20 password and it exits immediately. But I cannot find any error.log. Thanks, Guillermo --- Guillermo Acilu Senior Engineer, Koiaka GmbH Koiaka GmbH Riesserkopfstr. 17=20 82467 Garmisch-Partenkirchen Tel: +49 (0)8821 9679555=20 Fax: +49 (0)8821 730 9185=20 Mailto:guillermo.acilu@stripped http://www.koiaka.com Amtsgericht M=FCnchen: HR B 161 041=20 Gesch=E4ftsf=FChrer: Guillermo Acilu=20 Sitz: Garmisch-Partenkirchen Diese Email kann vertrauliche und/oder rechtlich gesch=FCtzte Informationen= =20 enthalten. Wenn Sie nicht der richtige Adressat sind oder diese Email=20 irrt=FCmlich erhalten haben, d=FCrfen Sie diese weder benutzen, kopieren,=20 weiterleiten oder irgend eine Ma=DFnahme einleiten, die im Zusammenhang mit= =20 dem Inhalt dieser Email steht. Informieren Sie bitte sofort den Absender=20 und vernichten Sie die irrt=FCmlich erhaltene Email vollst=E4ndig. Vielen Dank! This e-mail message may contain confidential and/or privileged=20 information. If you are not an addressee or otherwise authorized to=20 receive this message, you should not use, copy, disclose or take any=20 action based on this e-mail or any information contained in the message.=20 If you have received this material in error, please advise the sender=20 immediately by reply e-mail and delete this message completely. Thank you! From: "Ananda Kumar" To: "Guillermo.Acilu@stripped" Cc: mysql@stripped Date: 26.06.2008 16:30 Subject: Re: Performance problem with more than 500 concurrent queries do this mysqladmin -uroot -p debug and check the error.log, see if there are any locks on the tables. On 6/26/08, Guillermo.Acilu@stripped wrote: > > Hello guys, > > I am new to this list and also kind of new to mysql too. > > I have a multi-thread application written in Ruby. The application is > reading one table that has two columns (Father, Children). As you might > suspect, this is a tree. The fields are foreign keys to a second table, > but the second table is not involved in the problem. The father-children > table has around 100000 rows. > > What the code does is simple. It starts in the root and it navigates the > tree up to the leafs. The first thread takes the root and runs a select=20 to > get all the children. Then it triggers new threads per children and it > ends, leaving the other threads alive. Every thread does exactly the=20 same > until they reach the leafs. > > When the threads reach the leafs, they read the description from the=20 other > table using the leaf code, write the value in a global array and end. > > With a few rows, the algorithm is very fast. The problem starts when=20 each > node has many children. To give you an idea, in one point in time there > are more than 600 threads running, but for some reason I always see no > more than two queries running in parallel from the MySQL Administrator. > > Each thread opens a new connection, runs the select, closes the=20 connection > and ends. I have the default maximum connections, 100. So I should see > more queries in parallel than only two or three. All the connections are > constantly used while the algorithm runs. So the connections are open,=20 but > the database is not doing anything with them. It sounds like the table=20 is > locked, or something. I have checked the code several times but=20 everything > is correct. The code is only 25 lines long. > > The other symptom I can see is that when I start the script, there are=20 up > to 30 or 40 queries in parallel, but then the number goes down quickly > until it reaches only 2 or 3 concurrent queries a few seconds later. And > it stays like this. > > I've started playing around with the caches and memory values for MySQL > server, but to be honest, I am just guessing and the performance does=20 not > change. I am Oracle DBA and I am trying to find some points in common=20 with > mysql to gain performance, but I cannot find the source of the problem. > > I am with Mac OS X Leopard in a very fast machine and MySQL 5.0.51b. The > problem is also present in 5.1.25-rc > > Any ideas why is this happening? > > Thanks, > > Guillermo > --=_alternative 00569874C1257474_=--