List:Cluster« Previous MessageNext Message »
From:<paul Date:October 26 2011 1:14pm
Subject:RE: Slow session (connection) set-up using MySQLD API
View as plain text  
Guys, an additional remark about this: In order to achieve the good
performance with non-persistent connections, I also had to disable the
query cache!

Paul

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