List:Cluster« Previous MessageNext Message »
From:Magnus Blåudd Date:October 25 2011 8:53am
Subject:Re: Slow session (connection) set-up using MySQLD API
View as plain text  
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


#include <mysql.h>
#include <stdlib.h>
#include <stdio.h>

int main(int argc, const char** argv)
{
  MYSQL * conn;
  MYSQL_RES * res;
  MYSQL_FIELD * fields;
  my_ulonglong nrows;
  unsigned long nfields;
  int i;
  const int id = 1;
  char sql[1000];

  if (argc < 2)
  {
    fprintf(stderr, "usage: mysqlsample <tablename>\n");
    return 7;
  }

  for (i=0; i<10000; ++i)
  {
      conn = mysql_init(0);
      if (conn == NULL)
      {
        fprintf(stderr, "mysql_init failed\n");
        return 1;
      }

      if (mysql_real_connect(conn, "peek02", "root", "",
                             "test", 13001, 0, 0) == 0)
      {
        fprintf(stderr, "%d: mysql_real_connect failed: %d %s\n",
                i, mysql_errno(conn), mysql_error(conn));
        return 2;
      }

      //id = (int)(drand48() * 100000);
      sprintf(sql, "SELECT Value FROM %s WHERE ID=\'%d\'", argv[1], id);

      if (mysql_query(conn, sql))
      {
        fprintf(stderr, "%d, mysql_query failed: %d %s\n",
                i, mysql_errno(conn), mysql_error(conn));
        return 3;
      }

      do
	{
	  res = mysql_store_result(conn); 
	  nfields = mysql_field_count(conn);
	  nrows = mysql_affected_rows(conn);
    
	  if (res) 
	    {
	      fields = mysql_fetch_fields(res);
	      mysql_free_result(res);
	    }
	}
      while (mysql_next_result(conn) == 0);

      if (nrows != 1){
        fprintf(stderr, "Unexpected number of rows returned, got: %d\n",
                nrows);
        return 4;
      }

      mysql_close(conn);
      conn = NULL;
    }

  fprintf(stdout, "OK, %d loops completed!\n", i);

  return 0;
}
DROP TABLE IF EXISTS t1_ndb;

CREATE TABLE t1_ndb (
  ID char(10),
  Value INT,
  PRIMARY KEY(ID)
) engine = NDB;


INSERT INTO t1_ndb VALUES("1", 1);

DROP TABLE IF EXISTS t1_innodb;

CREATE TABLE t1_innodb(
    ID char(10),
      Value INT,
        PRIMARY KEY(ID)
      ) engine = innodb;

INSERT INTO t1_innodb VALUES("1", 1);

CREATE USER 'root'@'machine1';
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