do this mysqladmin -uroot -p debug
and check the error.log, see if there are any locks on the tables. On 6/26/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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 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 same > until they reach the leafs. > > When the threads reach the leafs, they read the description from the 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 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 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, but > the database is not doing anything with them. It sounds like the table is > locked, or something. I have checked the code several times but 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 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 not > change. I am Oracle DBA and I am trying to find some points in common 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 >