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
