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

Attachment: [text/x-c] mysqlsample.c
Attachment: [text/x-c] mysqlsample.c
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