Performance problem with more than 500 concurrent queries

[Guillermo.Acilu]

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