From: Magnus Blåudd Date: October 25 2011 8:53am Subject: Re: Slow session (connection) set-up using MySQLD API List-Archive: http://lists.mysql.com/cluster/8139 Message-Id: <4EA67903.2080306@oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="------------040701090801010103090203" --------------040701090801010103090203 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 >>> Date: Fri, October 21, 2011 10:25 am >>> To: paul@stripped >>> Cc: Magnus_Blåudd, Johan Andersson >>> , 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" >>>> Date: Thu, Oct 20, 2011 21:41 >>>> Subject: Slow session (connection) set-up using MySQLD API >>>> To: >>>> Cc: "Johan Andersson", >>>> >>>> >>>> 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 >>>>>>> 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 --------------040701090801010103090203 Content-Type: text/plain; name="mysqlsample.c" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="mysqlsample.c" I2luY2x1ZGUgPG15c3FsLmg+CiNpbmNsdWRlIDxzdGRsaWIuaD4KI2luY2x1ZGUgPHN0ZGlv Lmg+CgppbnQgbWFpbihpbnQgYXJnYywgY29uc3QgY2hhcioqIGFyZ3YpCnsKICBNWVNRTCAq IGNvbm47CiAgTVlTUUxfUkVTICogcmVzOwogIE1ZU1FMX0ZJRUxEICogZmllbGRzOwogIG15 X3Vsb25nbG9uZyBucm93czsKICB1bnNpZ25lZCBsb25nIG5maWVsZHM7CiAgaW50IGk7CiAg Y29uc3QgaW50IGlkID0gMTsKICBjaGFyIHNxbFsxMDAwXTsKCiAgaWYgKGFyZ2MgPCAyKQog IHsKICAgIGZwcmludGYoc3RkZXJyLCAidXNhZ2U6IG15c3Fsc2FtcGxlIDx0YWJsZW5hbWU+ XG4iKTsKICAgIHJldHVybiA3OwogIH0KCiAgZm9yIChpPTA7IGk8MTAwMDA7ICsraSkKICB7 CiAgICAgIGNvbm4gPSBteXNxbF9pbml0KDApOwogICAgICBpZiAoY29ubiA9PSBOVUxMKQog ICAgICB7CiAgICAgICAgZnByaW50ZihzdGRlcnIsICJteXNxbF9pbml0IGZhaWxlZFxuIik7 CiAgICAgICAgcmV0dXJuIDE7CiAgICAgIH0KCiAgICAgIGlmIChteXNxbF9yZWFsX2Nvbm5l Y3QoY29ubiwgInBlZWswMiIsICJyb290IiwgIiIsCiAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgInRlc3QiLCAxMzAwMSwgMCwgMCkgPT0gMCkKICAgICAgewogICAgICAgIGZwcmlu dGYoc3RkZXJyLCAiJWQ6IG15c3FsX3JlYWxfY29ubmVjdCBmYWlsZWQ6ICVkICVzXG4iLAog ICAgICAgICAgICAgICAgaSwgbXlzcWxfZXJybm8oY29ubiksIG15c3FsX2Vycm9yKGNvbm4p KTsKICAgICAgICByZXR1cm4gMjsKICAgICAgfQoKICAgICAgLy9pZCA9IChpbnQpKGRyYW5k NDgoKSAqIDEwMDAwMCk7CiAgICAgIHNwcmludGYoc3FsLCAiU0VMRUNUIFZhbHVlIEZST00g JXMgV0hFUkUgSUQ9XCclZFwnIiwgYXJndlsxXSwgaWQpOwoKICAgICAgaWYgKG15c3FsX3F1 ZXJ5KGNvbm4sIHNxbCkpCiAgICAgIHsKICAgICAgICBmcHJpbnRmKHN0ZGVyciwgIiVkLCBt eXNxbF9xdWVyeSBmYWlsZWQ6ICVkICVzXG4iLAogICAgICAgICAgICAgICAgaSwgbXlzcWxf ZXJybm8oY29ubiksIG15c3FsX2Vycm9yKGNvbm4pKTsKICAgICAgICByZXR1cm4gMzsKICAg ICAgfQoKICAgICAgZG8KCXsKCSAgcmVzID0gbXlzcWxfc3RvcmVfcmVzdWx0KGNvbm4pOyAK CSAgbmZpZWxkcyA9IG15c3FsX2ZpZWxkX2NvdW50KGNvbm4pOwoJICBucm93cyA9IG15c3Fs X2FmZmVjdGVkX3Jvd3MoY29ubik7CiAgICAKCSAgaWYgKHJlcykgCgkgICAgewoJICAgICAg ZmllbGRzID0gbXlzcWxfZmV0Y2hfZmllbGRzKHJlcyk7CgkgICAgICBteXNxbF9mcmVlX3Jl c3VsdChyZXMpOwoJICAgIH0KCX0KICAgICAgd2hpbGUgKG15c3FsX25leHRfcmVzdWx0KGNv bm4pID09IDApOwoKICAgICAgaWYgKG5yb3dzICE9IDEpewogICAgICAgIGZwcmludGYoc3Rk ZXJyLCAiVW5leHBlY3RlZCBudW1iZXIgb2Ygcm93cyByZXR1cm5lZCwgZ290OiAlZFxuIiwK ICAgICAgICAgICAgICAgIG5yb3dzKTsKICAgICAgICByZXR1cm4gNDsKICAgICAgfQoKICAg ICAgbXlzcWxfY2xvc2UoY29ubik7CiAgICAgIGNvbm4gPSBOVUxMOwogICAgfQoKICBmcHJp bnRmKHN0ZG91dCwgIk9LLCAlZCBsb29wcyBjb21wbGV0ZWQhXG4iLCBpKTsKCiAgcmV0dXJu IDA7Cn0K --------------040701090801010103090203 Content-Type: text/plain; name="mysqlsample.sql" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="mysqlsample.sql" RFJPUCBUQUJMRSBJRiBFWElTVFMgdDFfbmRiOwoKQ1JFQVRFIFRBQkxFIHQxX25kYiAoCiAg SUQgY2hhcigxMCksCiAgVmFsdWUgSU5ULAogIFBSSU1BUlkgS0VZKElEKQopIGVuZ2luZSA9 IE5EQjsKCgpJTlNFUlQgSU5UTyB0MV9uZGIgVkFMVUVTKCIxIiwgMSk7CgpEUk9QIFRBQkxF IElGIEVYSVNUUyB0MV9pbm5vZGI7CgpDUkVBVEUgVEFCTEUgdDFfaW5ub2RiKAogICAgSUQg Y2hhcigxMCksCiAgICAgIFZhbHVlIElOVCwKICAgICAgICBQUklNQVJZIEtFWShJRCkKICAg ICAgKSBlbmdpbmUgPSBpbm5vZGI7CgpJTlNFUlQgSU5UTyB0MV9pbm5vZGIgVkFMVUVTKCIx IiwgMSk7CgpDUkVBVEUgVVNFUiAncm9vdCdAJ21hY2hpbmUxJzsK --------------040701090801010103090203--