List:Cluster« Previous MessageNext Message »
From:<paul Date:October 25 2011 10:41am
Subject:RE: Slow session (connection) set-up using MySQLD API
View as plain text  
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

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