Re: Performance problem with more than 500 concurrent queries

[Guillermo.Acilu]

Hello,

thanks for the answer.

Where is the error.log stored? I run the mysqladmin, it requires the 
password and it exits immediately. But I cannot find any error.log.

Thanks,

Guillermo


---
Guillermo Acilu
Senior Engineer, Koiaka GmbH

Koiaka GmbH
Riesserkopfstr. 17 
82467 Garmisch-Partenkirchen
Tel: [phone] 
Fax: [phone] 
Mailto:guillermo.acilu@stripped
http://www.koiaka.com

Amtsgericht München: HR B 161 041 
Geschäftsführer: Guillermo Acilu 
Sitz: Garmisch-Partenkirchen

Diese Email kann vertrauliche und/oder rechtlich geschützte Informationen 
enthalten. Wenn Sie nicht der richtige Adressat sind oder diese Email 
irrtümlich erhalten haben, dürfen Sie diese weder benutzen, kopieren, 
weiterleiten oder irgend eine Maßnahme einleiten, die im Zusammenhang mit 
dem Inhalt dieser Email steht. Informieren Sie bitte sofort den Absender 
und vernichten Sie die irrtümlich erhaltene Email vollständig.
Vielen Dank!

This e-mail message may contain confidential and/or privileged 
information. If you are not an addressee or otherwise authorized to 
receive this message, you should not use, copy, disclose or take any 
action based on this e-mail or any information contained in the message. 
If you have received this material in error, please advise the sender 
immediately by reply e-mail and delete this message completely.
Thank you!





From:
"Ananda Kumar" <anandkl@stripped>
To:
"Guillermo.Acilu@stripped" <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 <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 
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
>