tag:blogger.com,1999:blog-14455177.post6323478798724860309..comments2024-03-07T18:57:25.977+01:00Comments on Mikael Ronstrom: 1.05BN QPM using MySQL Cluster 7.2Mikael Ronstromhttp://www.blogger.com/profile/07134215866292829917noreply@blogger.comBlogger42125tag:blogger.com,1999:blog-14455177.post-49599359192366579152013-03-11T06:36:12.720+01:002013-03-11T06:36:12.720+01:00Dear Mikael,
Thanks for sharing such valuable inf...Dear Mikael,<br /><br />Thanks for sharing such valuable info. We're running something similar to your benchmarks, and I have a question about balancing Soft IRQs within the CPU cores; do you follow such a technique in the benchmark? Or you just use the OS' default (all Soft IRQs are handled by one core at a time)? And could you please elaborate more if it's the former?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-79898547629054213492012-11-09T23:56:18.150+01:002012-11-09T23:56:18.150+01:00Nice results. Do you also have some experience wit...Nice results. Do you also have some experience with ndb in virtualized environment?<br /><br />MartinFnoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-39581147722853445472012-08-04T01:35:51.458+02:002012-08-04T01:35:51.458+02:00seems like you just tested a distributed memcached...seems like you just tested a distributed memcached layer. Ok that goes fast we all know that by now. But what about an actual sql query ie a range query on the index? does that scale as well. and if so what is the speed of those sort of queries?natalino busahttp://natalino.busa.nlnoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-71002266197706341582012-04-18T19:34:15.096+02:002012-04-18T19:34:15.096+02:00Hi Mikael- any update on the write performance tes...Hi Mikael- any update on the write performance tests you ran? I'm particularly interested in what kinds of indexes were on the tables being written (PK only? compound?). I'm looking @ a situation where I'll need to push 6M rows daily into a table with a PK and a 3 column composite key. The table is expected to be around 600M rows.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-67744239100819064602012-03-22T17:52:35.137+01:002012-03-22T17:52:35.137+01:00hi Mikael,
i note that MySQL cluster "suppor...hi Mikael,<br /><br />i note that MySQL cluster "supports non-indexed data on disk".<br /><br />does MySQL cluster support tables with both primary and foreign indices that are greater than the aggregate memory size of the cluster?<br /><br />ex: we have a database that is 4TB in size and we run 4 nodes with a total of 512GB in storage (across the nodes). would MySQL cluster support this configuration? would we have the indices in storage and the rows being indexed on disk?<br /><br />we're looking at DB2 now and we would really appreciate some clarity on this topic.<br /><br />thanks!<br />DinahDinahnoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-78079094682813835682012-03-19T16:50:42.910+01:002012-03-19T16:50:42.910+01:00The code for 7.2.5 can be retrieved on launchpad. ...The code for 7.2.5 can be retrieved on launchpad. 7.2.5 is the next version to be released for MySQL Cluster.Mikael Ronstromhttps://www.blogger.com/profile/07134215866292829917noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-88382694201728285222012-03-18T14:03:47.037+01:002012-03-18T14:03:47.037+01:00Hi, all, I want to test 7.2.5, where I could downl...Hi, all, I want to test 7.2.5, where I could download 7.2.5 dev version since I understand it is not GA.<br /><br />Thanks.lihttps://www.blogger.com/profile/00475212124499286578noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-82671812152081648342012-03-08T09:02:27.821+01:002012-03-08T09:02:27.821+01:00Hi Miakel again.
If indexed column size is bigge...Hi Miakel again. <br /><br />If indexed column size is bigger than total RAM, then if mySQL cluster will automatically put part of it on disk or some place we configure (such as SSDs)? Or if we can drive MySQL cluster to load an index for particular table dynamically and those indexes can saved particularly on SSDs?<br />Because, we might have 6400 billion rows and each row has a primary key 4 bytes. <br /><br />We are seeking for a solution to deal with such a big data. Looking forward to your suggestion!<br /><br />Thanks. <br /><br />Nai Yan.Nai Yanhttps://www.blogger.com/profile/12926518133280009931noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-11843522690140962472012-03-06T02:59:23.257+01:002012-03-06T02:59:23.257+01:00thanks mikael. It was of much help to me.
By the...thanks mikael. It was of much help to me. <br /><br />By the way, don't use Dolphin sci?<br /><br />It looks like better use dolphin sci rather than use infiniband?brussnoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-72306351992971357012012-03-05T12:39:50.843+01:002012-03-05T12:39:50.843+01:00you are using Dual-X5670 based nodes and using 14 ...you are using Dual-X5670 based nodes and using 14 threads out of 24, so what if we use AMD Opteron based servers (like Quad-6272 which is almost 2x faster than the Dual-X5670) and will have 64 cores and upto 512GB memory support, do you think it can reach the 2B/m mark?<br /><br />also if we scale the cluster over different datacenters located in completely different geo-locations, what will be the effects?Briannoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-24351363382068063262012-03-02T14:11:09.745+01:002012-03-02T14:11:09.745+01:00The [TCP] section is only needed if you need to se...The [TCP] section is only needed if you need to set specific things related to one specific connection between two nodes. In this case you need to set both Node1 and Node2 of this section which is what the error message says.<br /><br />I didn't use the [TCP] section at all. I used however the [TCP DEFAULT] section to set TcpReceiveBufferSize and TcpSendBufferSize.<br /><br />What I meant with setting hostname was to set the hostname on the nodes, this is a mandatory parameter, simply set this hostname of the node to the Infiniband IP address used in IPoIB or SDP. Nothing more needed.<br /><br />We had 18 machines running the benchmark, so yes, there were Infiniband switches.Mikael Ronstromhttps://www.blogger.com/profile/07134215866292829917noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-62325612304843848722012-03-02T13:35:06.262+01:002012-03-02T13:35:06.262+01:00Thanks Miakel.
Sincerly thanks for your reply.
I h...Thanks Miakel.<br />Sincerly thanks for your reply.<br />I have some question additionally.<br /><br />>Simply set the hostname of the >node to the IP address of the >Infiniband interface, that's all >that is needed<br /><br />In [TCP] setting of config.ini,<br />only can set aboout data node.<br />When i set [TCP] between mgm node and data node<br />or between sql node and date node, ndb_mgm could not start with next error.<br /><br />MySQL Cluster Management Server mysql-5.5.19 ndb-7.2.4<br />/export/home2/pb2/build/sb_0-4838533-1327948997.21/mysql-cluster-gpl-7.2.4/storage/ndb/src/mgmsrv/ConfigInfo.cpp:4580: require((ctx.m_config->get("Node", id1, &node1))) failed<br /><br />Did you set in [TCP] of config.ini only between data node. <br /><br />and <br />I have other question.<br />Do you use infiniband switch?brussnoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-83066442824719320282012-03-02T11:30:49.744+01:002012-03-02T11:30:49.744+01:00There are no special settings needed to use Infini...There are no special settings needed to use Infiniband based on IPoIB or SDP. Simply set the hostname of the node to the IP address of the Infiniband interface, that's all that is needed.<br /><br />Irrespective of that I usually set some things a bit higher, in particular TcpSendBufferSize and TcpReceiveBufferSize (I usually set those to 256k). But this I do also for Ethernet, so nothing special for Infiniband.Mikael Ronstromhttps://www.blogger.com/profile/07134215866292829917noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-1251891719552992782012-03-01T08:03:24.125+01:002012-03-01T08:03:24.125+01:00I am very curios [TCP] setting in config ini about...I am very curios [TCP] setting in config ini about infiniband.<br /><br />Did you connect only between data node with infiniband.<br /><br />Otherwise, Alse did you connect between sql node and data node with infiniband?Brussnoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-554539155507326062012-02-28T15:53:47.776+01:002012-02-28T15:53:47.776+01:00Thanks your infiniband information.
1)
I want to ...Thanks your infiniband information.<br /><br />1)<br />I want to know config.ini setting in case of IPoIB and SDP.<br /><br />2)<br />I have only 2 data node.<br />and I am going to use infiniband using peer to peer connection, <br />without infiniband switch.<br /><br />It is possible and useful?brussnoreply@blogger.comtag:blogger.com,1999:blog-14455177.post-1942680702782247792012-02-25T05:42:48.513+01:002012-02-25T05:42:48.513+01:00So if I only index primary key field (4 bytes), th...So if I only index primary key field (4 bytes), then for my case, memory consumption can be cut down to 256B * (15+4) < 5T? - 15 bytes for hashed index and 4 bytes for index field. <br /><br />If my calculation is wrong? Thanks. <br /><br />Nai Yan.Nai Yanhttps://www.blogger.com/profile/12926518133280009931noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-23734504231911557052012-02-24T20:24:55.113+01:002012-02-24T20:24:55.113+01:00All rows have indexed fields in memory and also in...All rows have indexed fields in memory and also indexes are memory based. Fields that are non-indexed can be disk-based.Mikael Ronstromhttps://www.blogger.com/profile/07134215866292829917noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-17405702980422750332012-02-24T16:36:39.795+01:002012-02-24T16:36:39.795+01:00if you mean, mySQL cluster have to put all the row...if you mean, mySQL cluster have to put all the rows into memory? Is there any option to balance memory usage and performance? Thanks. <br /><br />Nai Yan.Nai Yanhttps://www.blogger.com/profile/12926518133280009931noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-30270971690965267352012-02-24T11:59:11.182+01:002012-02-24T11:59:11.182+01:00Hi,
256B rows each with 20-30 bytes data means tha...Hi,<br />256B rows each with 20-30 bytes data means that you need about 15 bytes (hash index) + 32 bytes (e.g. row data) + 15 bytes (row overhead) per row. Thus you need around 64 bytes per row.<br /><br />This means 256B * 64 bytes = 16TB. Replication means an additional 16TB. Thus you need servers with a total capacity of around 35TB taking additional overhead into account.<br /><br />If you have 2TB's of memory you can handle about 16B rows.<br /><br />MySQL Cluster supports IPoIB and SDP using Infiniband but not Infiniband RDMA. But it shouldn't matter very much to performance of the system given that we can use multiple CPUs for network handling.Mikael Ronstromhttps://www.blogger.com/profile/07134215866292829917noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-45543181788040146432012-02-24T10:05:31.085+01:002012-02-24T10:05:31.085+01:00Thank you, Mikael.
However if we have at least 2...Thank you, Mikael. <br /><br />However if we have at least 256B rows, each row contains 20 ~ 30 bytes data. Any recommendation from you for system design so that we can maximize our read throughput. We estimate, we can leverage totally around 2T memory (also need to consider HA) and some SSDs. <br /><br />In addition to this, if current MySQL cluster can support Infiniband RDMA as the interconnection? <br /><br />Thanks. <br /><br />Nai Yan.Nai Yanhttps://www.blogger.com/profile/12926518133280009931noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-6738762389519338382012-02-24T09:44:52.292+01:002012-02-24T09:44:52.292+01:00Nai Yan,
Certainly performance will take a major h...Nai Yan,<br />Certainly performance will take a major hit if index size isn't fitting in main memory, we cannot change the laws of physics :). In MySQL Cluster indexes stays in memory. We support disk-based attributes as long as they are not indexed.<br /><br />For calculating the size of memory used there is around 15 bytes of overhead for the primary key hash index, there is around 10-20 bytes overhead per row (I think the manual has a more exact estimate) and any additional ordered index will be another 10-15 bytes of overhead. So in our table with 1 primary key index, 25 attributes of 4 bytes in size and no ordered indexes we will use about 110-120 bytes of DataMemory per row and about 15 bytes of IndexMemory per row (if ordered indexes are used they are stored in DataMemory).<br /><br />So this means that with the 140GB's of memory accessible to DataMemory and IndexMemory I can assign 120GB to DataMemory and 20GB to IndexMemory and thus be able to store 1BN rows.Mikael Ronstromhttps://www.blogger.com/profile/07134215866292829917noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-83538875028854930792012-02-24T09:37:27.163+01:002012-02-24T09:37:27.163+01:00We ran with IPoIB (IP over Infiniband), simply bec...We ran with IPoIB (IP over Infiniband), simply because it was easiest to set-up, everything was already set-up and prepared for it. SDP is probably slightly more efficient (at least so my previous experience says), but IPoIB was quite good enough for these tests.Mikael Ronstromhttps://www.blogger.com/profile/07134215866292829917noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-87484875632571758632012-02-24T09:02:15.040+01:002012-02-24T09:02:15.040+01:00Hi Mikael,
Thank you for your reply. Actually ...Hi Mikael, <br /> Thank you for your reply. Actually my question is how many rows are there in your test database? And how big is the index size. <br /><br /> What I am afraid is if the index size is bigger than memory size, the performance will be far degraded - i.e. far lower than 1.05BN QPM. <br /><br /> If my worry is true, would you give me a recommendation for memory size required vs. rows of DB, so that we can reach 1.05BN QPM? <br /><br /> Thanks!<br /><br />Nai Yan.Nai Yanhttps://www.blogger.com/profile/12926518133280009931noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-80451606974454701992012-02-24T07:55:52.973+01:002012-02-24T07:55:52.973+01:00Curious if you were running IP over InfiniBand, SD...Curious if you were running IP over InfiniBand, SDP using LD_PRELOAD or something else?digitalpointhttps://www.blogger.com/profile/13595987087265745150noreply@blogger.comtag:blogger.com,1999:blog-14455177.post-11186069778344914062012-02-23T15:06:42.929+01:002012-02-23T15:06:42.929+01:00The possible memory size with 8 data nodes with 48...The possible memory size with 8 data nodes with 48 GB each is about 4 * 35 GB = 140GB since we need to use a part of the memory for internal memory structures to handle ongoing transactions, send buffers... , index memory. Additionally we use NoOfReplicas = 2, so two machines contains the same data.<br /><br />If database size is > memory size then you're using disk-based data and performance is dependent on how the cache hit rate in the page pool is and the speed of the disk.Mikael Ronstromhttps://www.blogger.com/profile/07134215866292829917noreply@blogger.com