List:Cluster« Previous MessageNext Message »
From:<paul Date:October 25 2011 1:52pm
Subject:RE: Slow session (connection) set-up using MySQLD API
View as plain text  
Magnus, Jonas and Johan,

On request of MySQL support I have re-run my tests on a MySQL Cluster
version "mysql-cluster-gpl-7.1.15a". This version does not seem to
exhibit the problem described below for v7.2.1-beta. 

More precisely, connect-query-disconnect performance is:
- low with cluster v7.2.1 + mysqld v5.5.15
- high with cluster v7.1.15a + mysqld v5.1.56
- low with cluster v7.1.15a + mysqld v5.5.15
- high with cluster v7.2.1 + mysqld v5.1.56

So there seems to be an issue in the mysqld v5.5 part of the chain. With
v5.1, in my test environment connect-query-disconnect rates of 2000 per
second and more can be reached, whereas with v5.5 this rate drops below
100 per second.

I guess that this information, combined with the report in my last mail
about using one or multiple concurrent clients, will help you pinpoint
the issue with mysqld v5.5.

Kind regards,

Paul 



> -------- Original Message --------
> Subject: RE: Slow session (connection) set-up using MySQLD API
> From: <paul@stripped>
> Date: Tue, October 25, 2011 11:41 am
> To: "Magnus Blåudd" <magnus.blaudd@stripped>
> Cc: "Jonas Oreland" <jonas.oreland@stripped>,
> cluster@stripped, "Johan Andersson" <johan@stripped>
> 
> 
> 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