Hello guys,
I am new to this forum 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 see, this is a tree. The fields are foreign keys to a second table, but the second table is not involved in the problem. The table has around 100.000 rows.
What the code does is simple. It starts in the root and it goes 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, write the value in a global array and leave.
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 open a new connection, runs the select and it close the connection. 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.
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 until the end.
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 with Mac OS X Leopard in a very fast machine and MySQL 5.1.
Any ideas why is this happening?
Thanks,