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';
