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

Below is my measurement with a program which spawns your example loop in 
a couple of threads. Clearly shows that as soon as there are more than 
one thread it slows down. Although the trend is decreasing with more 
threads...the difference is significant.

## One thread and 1000 loops
[mblaudd@machine1 ~]$ time ./mysqlbench 1 t1_innodb 1000
OK, 1000 loops completed!

real	0m0.685s
user	0m0.056s
sys	0m0.055s
[mblaudd@machine1 ~]$ time ./mysqlbench 1 t1_ndb 1000
OK, 1000 loops completed!

real	0m0.978s
user	0m0.113s
sys	0m0.148s


## Two threads and 1000 loops(each thread doing 500)
[mblaudd@machine1 ~]$ time ./mysqlbench 2 t1_ndb 1000
OK, 500 loops completed!
OK, 500 loops completed!

real	0m14.078s
user	0m0.005s
sys	0m0.007s
[mblaudd@machine1 ~]$ time ./mysqlbench 2 t1_innodb 1000
OK, 500 loops completed!
OK, 500 loops completed!

real	0m0.442s
user	0m0.057s
sys	0m0.085s

## Ten threads and 1000 loops(each thread doing 100)
[mblaudd@peek01 ~]$ time ./mysqlbench 10 t1_ndb 1000
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!

real	0m10.805s
user	0m0.089s
sys	0m0.129s

[mblaudd@peek01 ~]$ time ./mysqlbench 10 t1_innodb 1000
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!
OK, 100 loops completed!

real	0m0.275s
user	0m0.075s
sys	0m0.118s


Thanks for your help reproducing this!

Best regards
Magnus

On 2011-10-25 12:41, paul@stripped wrote:
> Magnus,
>
> Indeed I get similar results with my "sample" test program: It takes in
> my case 4.3 seconds to do 1000 queries (non-persistent connection). This
> seemingly contradicts results I reported earlier with my "real" test
> program, so I tested again, but now with just one thread. It reaches 233
> queries per second, consistent with the 1000 queries in 4.3 seconds of
> the sample test program.
>
> However, as soon as I use more than one thread, performance drops
> dramatically: With 2 threads it can only do 65 queries per second.
>
> You can confirm this by starting two instances of my sample test program
> at the same time (on the same or different machines, it does not matter:
> In my test environment it now takes 33 seconds (!) for each instance to
> complete 1000 queries! However, if I start two instances, each
> connecting to a different MySQLD API node, they run at normal speed of
> 4.3 seconds again.
>
> So, it seems that the MySQLD API nodes have difficulty when
> connect-query-disconnect cycles are not serialized.
>
> If I re-run the same test-program against a "normal" MySQL server, using
> MyISAM tables, a single instance takes 20 seconds to run 1000 queries
> (non-persistent connection). If two instances are started
> simultaneously, both take 20 seconds. Compared to MySQL Cluster this
> shows two interesting findings: The normal MySQL Server is slower than
> MySQL Cluster when requests are coming from a single client, but overall
> performance gets better when more clients connect simultaneously,
> whereas the Cluster performance gets worse.
>
> Summarized:
>
> One client on one MySQL Cluster API node: 240 queries per second
> Two clients one MySQL Cluster API node: 60 queries per second
> Two clients on two MySQL Cluster API nodes: 480 queries per second
> One client on "normal" MySQL Server with MyISAM: 50 queries per second
> Two clients on "normal" MySQL Server with MyISAM: 100 queries per second
>
> Magnus, I think you should be able to reproduce and confirm these
> findings with your test program.
>
> Paul
>
> P.s.: In the sample test program I added a call to "srand48" using
> time(0) as seed to initialize the random generator.
>
>
>
>
>
>
>
>
>> -------- Original Message --------
>> Subject: Re: Slow session (connection) set-up using MySQLD API
>> From: Magnus Blåudd<magnus.blaudd@stripped>
>> Date: Tue, October 25, 2011 9:53 am
>> To: paul@stripped
>> Cc: Jonas Oreland<jonas.oreland@stripped>, cluster@stripped,
>>        Johan Andersson<johan@stripped>
>>
>>
>> Hi again Paul,
>>
>> further testing with your mysqlsample program - slightly modified to  1)
>> take table name as argument, 2) query only for ID=1 and 3) doing 10000
>> connect/disconnect cycles - shows that with non persistent connection
>> the difference between querying from innodb vs. ndb is not that big(see
>> below).
>>
>>
>> [mblaudd@machine1 ~]$ time ./mysqlsample t1_innodb
>> OK, 10000 loops completed!
>>
>> real	0m7.320s
>> user	0m0.591s
>> sys	0m0.815s
>>
>> [mblaudd@machine1 ~]$ time ./mysqlsample t1_ndb
>> OK, 10000 loops completed!
>>
>> real	0m10.016s
>> user	0m0.130s
>> sys	0m0.326s
>>
>>
>> The above test was run from machine1 with entire cluster running on
>> machine2 as I think this would be showing the difference in
>> connect/disconnect time best.
>>
>> Attaching the new version of the test program as well as some .sql to
>> load the db.
>>
>> Best regards
>> Magnus
>>
>>
>>
>>
>> On 2011-10-25 09:37, Magnus Blåudd wrote:
>>> 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
>>>
>>
>>
>> / Magnus
>
>


/ 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