List:Cluster« Previous MessageNext Message »
From:Magnus Blåudd Date:October 25 2011 7:37am
Subject:Re: Slow session (connection) set-up using MySQLD API
View as plain text  
Hi Paul,

Thanks for the example program.

As I understand this problem - although the bug previously mentioned 
seems to have been fixed in MySQL Server 5.5 - you still don't get "fast 
enough" queries(I assume that select or update doesn't matter) when 
using non persistent connections to the MySQL Server. Unfortunately 
MySQL Cluster has not been optimized for non persistent connections, if 
you can configure some sort of connection caching I think that would be 
a workaround.

My suggestion is that you file a bug at bugs.mysql.com describing this 
performance degradation so we have a way of keeping track of the problem.


Best regards
Magnus







On 2011-10-21 13:13, paul@stripped wrote:
> Jonas,
>
> In that bug report it is mentioned that the processlist showed that
> 'connections were kept waiting at "cleaning up" phase.'. This is not
> what I see. Instead, in the processlist I often see Command "Killed"
> with State "NULL", and Command "Query" with State "Waiting for query
> cache lock".
>
> So, I disabled the query cache to see it that makes a difference. It
> does, but only slightly: Instead of 65 queries per second, I now get
> 100. In the processlist I still often see "Killed", and Command "Query"
> with State "Sending data".
>
> Another test I did was to do only connect-disconnect cycles, so without
> actually doing a query after the connect. In that case I manage to do
> more than 2,500 connect-disconnect cycles per second (using 20 threads).
> Whereas this seems to suggest that the query itself is the problem, but
> remember that on persistent connections, I manage to do 7,600 select
> queries per second (with 20 threads).
>
> Attached is some sample code that illustrates the way I'm testing. In
> the actual test-program, a configurable number of threads is executing
> the same commands. The "TestTable" contains 100,000 rows, with primary
> key ID char(10) ranging from "0" to "99999".
>
> Thanks, Paul
>
>> -------- Original Message --------
>> Subject: Re: Slow session (connection) set-up using MySQLD API
>> From: Jonas Oreland<jonas.oreland@stripped>
>> Date: Fri, October 21, 2011 10:25 am
>> To: paul@stripped
>> Cc: Magnus_Blåudd<magnus.blaudd@stripped>,        Johan Andersson
>> <johan@stripped>, cluster@stripped
>>
>>
>> ok...so archeology-department has found this bug:
> http://bugs.mysql.com/bug.php?id=48832
>>
>> we still haven't examined if this made it into 5.5
>>
>> /Jonas
>>
>> On 10/21/11 08:55, Magnus Blåudd wrote:
>>> Hi,
>>>
>>> I remember that we had a similar problem with the 5.1 based MySQL Cluster -
> holding a mutex while doing network IO to release resources when the session disconnected.
> Problem was  fixed with a custom patch and was supposed to be fixed in 5.5
>>>
>>> / Magnus
>>>
>>> ----- Reply message -----
>>> From: "Jonas Oreland"<jonas.oreland@stripped>
>>> Date: Thu, Oct 20, 2011 21:41
>>> Subject: Slow session (connection) set-up using MySQLD API
>>> To:<paul@stripped>
>>> Cc: "Johan
> Andersson"<johan@stripped>,<cluster@stripped>
>>>
>>>
>>> On 10/20/11 21:37, Johan Andersson wrote:
>>>> Hi Paul,
>>>>
>>>> Could you give an illustrative example of what the SELECT query looks
> like and what is the data model - roughly what the table(s) look like, and how it is
> indexed, and sizes, and if blobs/texts etc are used.
>>>>
>>>> I can try it then on my cluster.
>>>
>>> i'm also interested in what type of application it is...
>>>
>>> c program
>>> php
>>> perl
>>> something else cool
>>>
>>> /Jonas
>>>
>>>>
>>>> Best regards,
>>>> johan
>>>>
>>>> On 2011-10-20 17.38, paul@stripped wrote:
>>>>> Jonas,
>>>>>
>>>>> Attached are the config.ini file (for the management nodes), my.cnf
> file
>>>>> (for the MySQLD API nodes, in this case for node 55), and the
> ndbd.cnf
>>>>> file (for the data nodes).
>>>>>
>>>>> Unfortunately I am not allowed to share the source code of my test
>>>>> program, I'm sorry.
>>>>>
>>>>> Thanks for your help, Paul
>>>>>
>>>>>> -------- Original Message --------
>>>>>> Subject: Re: Slow session (connection) set-up using MySQLD API
>>>>>> From: Jonas Oreland<jonas.oreland@stripped>
>>>>>> Date: Thu, October 20, 2011 4:06 pm
>>>>>> To:paul@stripped
>>>>>> Cc:cluster@stripped
>>>>>>
>>>>>>
>>>>>> On 10/20/11 15:31,paul@stripped  wrote:
>>>>>>> Hello,
>>>>>>>
>>>>>>> I'm evaluating MySQL Cluster as a replacement for our current
> "normal"
>>>>>>> MySQL Server. The current server is processing many "update"
> queries per
>>>>>>> second, sent by a client application that always stays
> connected to the
>>>>>>> server. On the other hand, the server is serving "select"
> queries on the
>>>>>>> same table, using clients that disconnect after each query
> (i.e.,
>>>>>>> requests coming from a web server, without connection
> pooling).
>>>>>>>
>>>>>>> In my evaluation I have observed that MySQL Cluster with two
> data nodes
>>>>>>> offers a superior "update" performance compared to a MySQL
> Server
>>>>>>> instance running on identical hardware. However, performance
> for the
>>>>>>> "select" queries is much worse on MySQL Cluster (using MYSQLD
> as API).
>>>>>>>
>>>>>>> Apparently MySQL Cluster has much more "overhead" setting up
> a session
>>>>>>> (connection). The difference between MySQL Server and MySQL
> Cluster is
>>>>>>> quite dramatic: In our test set-up MySQL Cluster could only
> server about
>>>>>>> 65 connect-select-disconnect cycles, whereas MySQL Server can
> easily
>>>>>>> handle 1000 and more cycles per second. This is quite a
> show-stopper for
>>>>>>> our purposes...
>>>>>>>
>>>>>>> Note that all queries are done using the primary key, and all
> updates
>>>>>>> and selects work on exactly one row. In total our test table
> contains
>>>>>>> 100,000 rows. The MySQL Server version used to test was 5.1
> and MySQL
>>>>>>> Cluster was version 7.2.1 with MySQLD 5.5.
>>>>>>>
>>>>>>> Does anyone else has the same experience with MySQL Cluster?
> Is there
>>>>>>> any way to improve this connect-select-disconnect cycles?
>>>>>>>
>>>>>>> Thanks for your help,
>>>>>>>
>>>>>>> Paul
>>>>>> Hi Paul,
>>>>>>
>>>>>> Could you share your my.cnf and maybe your application (that does
> the SELECT) so
>>>>>> I can test for my self ?
>>>>>>
>>>>>> /Jonas
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>
>>>


/ Magnus
Thread
Re: Slow session (connection) set-up using MySQLD APIMagnus Blåudd21 Oct
  • Re: Slow session (connection) set-up using MySQLD APIPaul van Diepen21 Oct
  • Re: Slow session (connection) set-up using MySQLD APIJonas Oreland21 Oct
RE: Slow session (connection) set-up using MySQLD APIpaul22 Oct
  • Re: Slow session (connection) set-up using MySQLD APIMagnus Blåudd25 Oct
    • Re: Slow session (connection) set-up using MySQLD APIMagnus Blåudd25 Oct
RE: Slow session (connection) set-up using MySQLD APIpaul25 Oct
  • Re: Slow session (connection) set-up using MySQLD APIMagnus Blåudd25 Oct
RE: Slow session (connection) set-up using MySQLD APIpaul25 Oct
RE: Slow session (connection) set-up using MySQLD APIpaul26 Oct