tag:blogger.com,1999:blog-195155822024-03-07T20:01:41.903-08:00MySQL QAThis blog has been created to discuss the MySQL family of databases and the QA testing done for each release. I mainly focus on test automation and performance testing technology, but I am open to all feedback of likes or dislikes of the product.jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-19515582.post-11494095671527449702014-06-17T09:09:00.000-07:002014-06-17T09:09:17.528-07:00Did you see MySQL @ Texas Linux Fest?Read about it here<br><br>
<a href="http://opensourcedba.wordpress.com/2014/06/17/mysql-fabric-a-big-hit-at-texas-linuxfest/">mysql-fabric-a-big-hit-at-texas-linuxfest</a><br><br>jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com0tag:blogger.com,1999:blog-19515582.post-55613625252964712322014-06-03T19:40:00.003-07:002014-06-04T08:13:10.299-07:00Whats new in MySQL Workbench 6.1I have always had the attitude one should eat their own dog food. (so to speak ;-) </br></br>
Especially when it comes to software.</br></br>
As such, MySQL just released Workbench 6.1 with Visual Explain. </br></br>
I have a production database that holds results from automated performance tests which is updated by an xml parser. Using the Visual Explain I found several queries in the parser causing table scans. Applying the suggest indexes resolved these scans. </br></br>
There a lot of great additions to this latest release and it is free. Worth a look for sure.</br></br>
<a href="http://dev.mysql.com/doc/workbench/en/wb-what-is-new-61.html"> Whats new in MySQL Workbench 6.1</a></br></br>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrw3Ks_5W3iV0_4J-Pe9UAlNqsYfmJZS4DkXIAiXl6VRjgosdH7V1j1EUlsdLFE4_ABU0qRLH8_S6UKEgM4E_GeJEc1xEgouw2yNBSqUU8XfLYAo14wHygv4Du5jccb6ggdH6e8w/s1600/wb-new-visual-explain-61.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrw3Ks_5W3iV0_4J-Pe9UAlNqsYfmJZS4DkXIAiXl6VRjgosdH7V1j1EUlsdLFE4_ABU0qRLH8_S6UKEgM4E_GeJEc1xEgouw2yNBSqUU8XfLYAo14wHygv4Du5jccb6ggdH6e8w/s400/wb-new-visual-explain-61.png" /></a></div>jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com0tag:blogger.com,1999:blog-19515582.post-33057595906287612032013-05-03T09:40:00.000-07:002013-05-03T09:40:18.844-07:00What's Oracle really doing with MySQL?For those that wonder what is really going on with MySQL inside Oracle I invite you to read Tomas Ulin <a href="http://insidemysql.com/april-engineering-news/"> Inside MySQL Blog</a><br><br>
Stayed tuned for more to come later.
jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com0tag:blogger.com,1999:blog-19515582.post-65629769096585306262013-05-03T09:03:00.000-07:002013-05-03T09:05:29.103-07:00New MySQL System QA blogHi,<br>
<br>
It has been a while since I last posted, but I wanted to post about my teams NEW blog page.<br>
<br>
<a href="https://blogs.oracle.com/mysqltesting/entry/welcome_to_testing_blog"> New System QA Blog</a><br>
<br>
As always, we look for ways to improve QA and your feedback is always welcomed.
<br>
Best Wishes,<br>
/Jeb <br>jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com4tag:blogger.com,1999:blog-19515582.post-12831280556474026602010-04-16T13:56:00.000-07:002010-04-16T13:56:33.251-07:00Perl IO::Socket, how to make them talk on both ends Cient/ServerHi,<br />
<br />
Was exploring IO::Socket for some test automation I'm looking at doing.<br />
<br />
I found a lot of examples on talking to the server side, but not really any good ones on how to recieve and process on the client side what the sever sent in response.<br />
<br />
After playing with it for a while today, I finially got the basics working and wanted to share incase others are wanting to deal with 2 way comunication. The main thing to remember is the $_ and what it is used for.<br />
<br />
Examples:<br />
<br />
Cleint Side:<br />
-----------<br />
<br />
use IO::Socket;<br />
<br />
$sock = new IO::Socket::INET(<br />
PeerAddr => "127.0.0.1",<br />
PeerPort => 1234,<br />
Proto => 'tcp') || die "Error creating socket: $!";<br />
<br />
print $sock "test\n"; #test coms and start a chat<br />
<br />
while ( <$sock> ){<br />
print "$_"; # The servers reponse will be in $_<br />
if ($_ ne "MySQLD is now down\n"){<br />
print $sock "shutdown\n";<br />
}<br />
else{<br />
print $sock "exit\n";<br />
}<br />
}<br />
<br />
close($sock);<br />
<br />
<br />
-------------<br />
Client output<br />
-------------<br />
<br />
perl ./client.pl<br />
Received<br />
MySQLD is now down<br />
<br />
<br />
Server Side:<br />
------------<br />
<br />
use IO::Socket;<br />
<br />
$sock = new IO::Socket::INET(<br />
LocalHost => '127.0.0.1',<br />
LocalPort => 1234,<br />
Listen => 1,<br />
Reuse => 1,<br />
Proto => 'tcp') || die "Error creating socket: $!<br />
";<br />
<br />
$client = $sock->accept();<br />
<br />
while($line = <$client>) {<br />
print $line;<br />
if ($line eq "exit\n"){<br />
print "Client sent exit\n";<br />
close($client);<br />
}<br />
elsif ($line eq "test\n"){<br />
print $client "Received\n";<br />
}<br />
elsif ($line eq "shutdown\n"){<br />
print $client "MySQLD is now down\n";<br />
}<br />
}<br />
<br />
close($sock);<br />
<br />
-------------<br />
Server output<br />
-------------<br />
perl ./server.pl<br />
test<br />
shutdown<br />
exit<br />
Client sent exit<br />
<br />
<br />
Start the server.pl and then run the client.pl<br />
<br />
Hope this example helps someone! ;-)<br />
<br />
Best wishes,<br />
/Jebjbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com3tag:blogger.com,1999:blog-19515582.post-12402051694193331562009-06-03T13:13:00.000-07:002009-06-03T13:26:53.842-07:00kill, exit(), _exit() and issues getting gcov resultsHi,<br /><br />We are currently running code coverage for MySQL Cluster testing and have hit a few bumps along the road when it cam to collecting the "block" coverage for the NDBD.<br /><br />I wanted to share them here for others that might run into similar frustrations when doing testing code coverage.<br /><br />Gcov accumulates during each run information on which functions and lines of code are called and a total for the times called.<br /><br />The problem comes from when gcov dumps this information out. The actual data is dumped on the "exit" of the program.<br /><br />After much time tracking this issue down, it turns out that the NDBD code had been changed to use _exit() instead of just exit()<br /><br />What is the difference?<br /><br />exit()<br />Terminate the process after cleanup.<br /><br />_exit()<br />Terminate process immediately.<br /><br />So by calling _exit(), gcov never had a chance to dump the data that it had collected.<br /><br />So the quick workaround to this is to wrap all the _exit() calls with an #ifndef<br /><br />i.e.<br /><br />#ifndef HAVE_gcov<br /> _exit(0)<br />#else<br /> exit(0)<br />#endif<br /><br />Question: Why use the _exit? If you have trouble with a program hanging after the exit() has been called a solution is to use _exit().<br /><br />Then we also found that some of the testing was using the "kill -9" to end the Cluster instance. This, of course, is the same as calling _exit().<br /><br />So if you are using gcov, and not getting results, the above may have to do with your frustration.<br /><br />Hope this saves someone some time and frustration!!!<br /><br />Cheers,<br />/Jebjbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com2tag:blogger.com,1999:blog-19515582.post-55265380015173134682009-04-23T08:05:00.000-07:002009-04-23T09:26:03.866-07:00Scalability Issue, or just tuning required for Auto IncrementHi,<br /><br />First let me start by making sure you know Sun's MySQL Cluster 7.0 has been released as GA. The was announced at the MySQL Users Conference this week.<br /><br />You can read more about the release at:<br /><br />http://www.mysql.com/products/database/cluster/<br />http://www.mysql.com/news-and-events/generate-article.php?id=2009_06<br /><br />Now, on to the topic.<br /><br />Disclaimer:<br />All performance numbers released here are used for development purpose only and are not intended for marketing. The systems are not set for optimal throughput and the results of tests are "only" used to ensure that we have not lost performance from commit to commit or to, as in this case, track down issues.<br /><br />Moreover, the TPC-"B" test is a retired test from www.tpc.org, but is still a very useful test when looking at simple transaction and scalability of simple transactions w/ a low probability of deadlocks and rollbacks.<br /><br />Results for other users or customers may vary and each should try different combination of options to see which works best for them.<br /><br />Article:<br />During testing of 7.0, our automated testing system that runs and compares benchmark tests found a scalability issue.<br /><br />The issue first centered around the ndb-connection-pool option.<br /><br />If the option was set to 1 (or default) we seemed to scale okay:<br /><br />TPC-B results for ndb-connection-pool=1<br /><pre><br />Thread(s) Value(s)<br />----------------------------<br />4 695.2407989502<br />8 1133.3599700928<br />12 1287.9699935913<br />16 1299.6866531372<br />20 1290.1900100708<br />24 1294.7066650391<br /></pre><br />but when we set the connection pool to say 4, we then saw where the performance seemed to flat-line:<br /><br />TPC-B results for ndb-connection-pool=4<br /><pre><br />Thread(s) Value(s)<br />----------------------------<br />4 202.85333633423<br />8 200.13333511353<br />12 265.94666481018<br />16 276.55333137512<br />20 276.59096050262<br />24 282.05245113373<br /></pre><br />After some pretty extensive testing, it was isolated to the insert into the history table which is an alone transaction.<br /><br />Transaction Profile:<br /><pre><br />BEGIN<br />DECLARE ran INT;<br />START TRANSACTION;<br /> INSERT into $database.history (aid,tid,bid,TransTime,scriptID,filler)<br /> VALUES(ran,ran,ran,now(),ran,'MoreMoney');<br />COMMIT;<br />END<br /></pre><br />History Schema: (Take note of the AUTO_INCREMENT)<br /><pre><br />CREATE TABLE history (id BIGINT NOT NULL AUTO_INCREMENT,<br /> aid INT,<br /> tid INT,<br /> bid INT,<br /> TransTime DATETIME,<br /> scriptId CHAR(10),<br /> filler CHAR(80),<br /> PRIMARY KEY (id))ENGINE=$engine_type; <br /></pre><br />There some known weirdness with cluster and auto inc. For performance reasons a MySQL Demon will pre-fetch a series of auto inc numbers from the cluster.<br /><br />After we realized that AUTO_INCREMENT was involved, it was time to start playing with it to see what made it better (if anything) or worse. We realized that having multiple thread from the mysqld to the cluster can cause contentions if there are more clients then there are MySQLD threads into the cluster.<br /><br />So that open the question, what happens if ndb-connection-pool is greater then the number of clients that you have connecting and inserting into this table?<br /><br />Side Note:<br /><br />---------<br />Make sure that you have enough API slots in the cluster to cover all the MySQLD threads that will be connecting or, you will run into bug#44330.<br />{http://bugs.mysql.com/bug.php?id=44330 }<br />---------<br /><br /><br />Some testing outside the framework using a higher ndb-connection-pool setting showed promise.<br /><br />We updated the automated testing to include an ndb-connection-pool = 24 and added additional API slots to cover these threads.<br /><br />TPC-B results for ndb-connection-pool=24<br /><pre><br />Thread(s) Value(s)<br />----------------------------<br />4 940.3466796875<br />8 1425.5633239746<br />12 1643.7499847412<br />16 1671.7252731323<br />20 1674.6633224487<br />24 1670.5512924194<br /></pre><br />The results showed a scalability improvement with an average 28% increase of throughput.<br /><br />Deviation percentage(s) for run.<br /><pre><br />Threads %<br />------- ------<br />4 35.2548183460184<br />8 25.7820429159744<br />12 27.623313657942<br />16 28.6252551026102<br />20 29.7997434003385<br />24 29.0293266829633<br /></pre><br />Yet, there where some other unanswered questions.<br /><br />We had also seen issues with auto inc when it came to Ndb_cache_check_time.<br /><br />In addition, we were wondering what effect the Ndb_autoinc_fetch_sz (default 256) was having on scalability.<br /><br />We ran well over 30 small case tests changing these different setting to see what had the greatest throughput.<br /><br />What showed to give the most performance (in this case, maybe not yours) was to have the following setting.<br /><br />NDB API Slots = ndb-connection-pool<br />ndb-connection-pool= or > number of clients<br />Ndb_cache_check_time=1<br />Ndb_autoinc_fetch_sz=200<br /><br />To verify this, we updated the automated test suite configurations leaving the pool = 24 and adding the other 2 new settings.<br /><br />ndb-connection-pool=24<br />Ndb_cache_check_time=1<br />Ndb_autoinc_fetch_sz=200<br /><pre><br />Thread(s) Value(s)<br />----------------------------<br />4 1216.9033508301<br />8 2115.7299804688<br />12 2684.7500152588<br />16 3216.4866790771<br />20 3341.2700042725<br />24 3353.1133270264<br /></pre><br />This gave us a huge boot.<br /><pre><br />Deviation percentage(s) for run.<br />Threads %<br />------- ------<br />4 29.4100757855078<br />8 48.4136091948517<br />12 63.3308009235662<br />16 92.4052193725821<br />20 99.5189098299998<br />24 100.718968776481<br /></pre><br /><br />Summary:<br /><br />If (you are using Sun's MySQL Cluster && MySQLD to insert rows into a table with an Auto Increment column && your just not getting the throughput you are wanting) then you should take a close look at the 3 options listed to see what combination works best for your setup.<br /><br />ndb-connection-pool=?<br />Ndb_cache_check_time= (suggested 1)<br />Ndb_autoinc_fetch_sz= (suggested 200)<br /><br />In addition, there are other tests such as TPC-C (DBT2) that showed similar gains for the connection-pool, but no additional gains for the other setting as this test does not use auto inc columns.<br /><br />There is still a bug open over the original problem http://bugs.mysql.com/bug.php?id=43469 and this is to be fixed at a later date.<br /><br />Side Note:<br /><br />TPC-"B" utilizing Disk Data also benefited greatly from this new combination.<br /><br />[Disk Data Results]<br />ndb-connection-pool=1<br /><pre><br />Thread(s) Value(s)<br />----------------------------<br />4 891.16665649414<br />8 1337.1700134277<br />12 1554.7266845703<br />16 1616.2533416748<br />20 1589.3699951172<br />24 1579.4092941284<br /></pre><br />ndb-connection-pool=24<br />Ndb_cache_check_time=1<br />Ndb_autoinc_fetch_sz=200<br /><pre><br />Thread(s) Value(s)<br />----------------------------<br />4 1162.4266662598<br />8 1972.8933258057<br />12 2556.6375579834<br />16 2910.8500213623<br />20 3147.7466583252<br />24 3058.708442688<br /></pre>jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com2tag:blogger.com,1999:blog-19515582.post-42464671129132595772008-12-19T12:04:00.000-08:002008-12-19T21:42:54.918-08:00MySQL 5.1 GA is of good qualityHi,<br /><br />Been a while since I lasted posted.<br /><br />It has been a busy year with testing on 5.1, 5.1 Replication, the Telco branches of cluster and doing test Automation.<br /><br />I know many have read the post that one of the founders did about MySQL 5.1 GA and the known issues with it. I as QA wanted to extend my take.<br /><br />I started working for MySQL back in April of 2005. Official QA had only been around since Feb of 2005 and I think I was the 3rd or 4th brought in. My position at another DB software provider had gone to India which allowed me to find this opportunity.<br /><br />When I first started, there were not many things in place for QAing the product and very limited automation. Even so, finding bugs was like shooting fish in a barrel with and over sized shotgun. <br /><br />In fact, I spent more time writing bug reports then I did testing. There was a lot of low hanging fruit. Some the size of cherries and some the size of watermelons.<br /><br />MySQL 5.0 was already behind and had many known and unknown issues.<br /><br />Before the official start of QA inside MySQL, testing was left up to developers and community to find issues. It was much easier to ship with no known issues as that is just what they were, not known. This is not to say they did not exist in those versions, just that no one had found them. <br /><br />Fast forward to today, QA is in place and detailed testing is being done <br /><br />We have system, stress, load, performance, regression, unit, etc... testing that happening each and every day. In addition, there is better automated testing in place. Due to this most of all, if not all, the low hanging fruit has been picked. <br /><br />I now spend more time testing and developing tests/automation then I do writing bug reports.<br /><br />I know that 5.1 is a much better release then 5.0 was by far. Are there still bugs? Sure, but many are known and listed for anyone to review. I know of no software company (closed or open source) that ships and guarantees no bugs.<br /><br />5.1 has had thousands of bugs found and fixed before it ever received the GA title. It has many wonderful features that many have been waiting on GA so that could officially use it in production. (i.e. Row Based Replication).<br /><br />So if you are holding off looking at or trying MySQL 5.1 because of a naysayer, I would encourage you to try it, see for yourself and makeup your own mind.<br /><br />As always, I am open to better ways to test our product. Feel free to email me and let me know your thoughts.<br /><br />/Jebjbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com1tag:blogger.com,1999:blog-19515582.post-18578043248364382032008-02-20T04:30:00.000-08:002008-02-20T04:32:52.965-08:00MySQL Conference User and contacting me<a href="http://en.oreilly.com/mysql2008/"><br /><img src="http://en.oreilly.com/mysql2008/public/asset/asset/560" width="336" height="280" border="0" alt="MySQL Conference & Expo 2008" title="MySQL Conference & Expo 2008" /><br /></a><br /><br /><br />Hi,<br /><br />I don't get out here too often. If you have a question, or comment, please feel free to post it here, but also send it directly to me @ jmiller@mysql.com. You will get a faster response.<br /><br />Best wishes,<br />/Jebjbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com1tag:blogger.com,1999:blog-19515582.post-18897165624546629992007-05-28T19:27:00.000-07:002007-05-28T19:53:05.808-07:00Just when you think you know somethingHi,<br /><br />Been a long time since last I posted.<br /><br />I just got back from the Cluster Summit in Stockholm. <br /><br />During this meeting we covered topics such as Design and Internals of MySQL Cluster, Dolphin Express for MySQL Cluster, Road Map discussion, Cluster & Replication Troubleshooting, Benchmarks with new performance patches and of course QA.<br /><br />Items brought up during the QA Discussion included:<br /><br />* HA Testing (High Availability) <br />* Log Events Testing<br />* Large Data sets<br />* Backup/Restore<br />* Configuration Testing<br />* Functional testing (Regression testing of Standard Functionality)<br />* Profiling<br />* Performance<br /><br />So as you can see from above, I will be busy for some time to come :-)<br /><br />Many of the above are being done now. We discussed how to take them to the next level.<br /><br />During this meeting I was asked to take a Cluster Certification Test. No problem I thought, this should be a breeze. Just when you think you know something you are reminded that there is always areas to learn about. <br /><br />I have been working with Cluster for over 2 years now and loving the product. The Certification test showed me that there was still plenty for me to learn. I am happy to know that MySQL DBA's that take and pass this test will have to know what they are talking about. I am saying that this is not just some easy test; which would lower the value of the Certification IMHO.<br /><br />Want to see me and others Cluster MySQLers sweating... I mean taking the test?<br />Checkout this:<br />http://rpbouman.blogspot.com/2007/05/ssst-quietcluster-certification-exam-in.html<br /><br />I know, you are asking, well did he pass the test? The answer is yes, but I have to admit that my score was not what I would have liked for it to be. Just means that I have much more to learn.<br /><br />So if you are a Cluster Shop and you are looking to hire someone that is a MySQL Certified DBA with Cluster Certification, I for one can tell you that they had to have good Cluster knowledge to get that Cert. <br /><br />Don't believe me, well then, just take the test and see for yourself ;-) <br /><br />As always, I love to have feedback and ideas about QAing Cluster and Replication.<br /><br />Jonathan Miller<br />Austin, Texas USA<br />Senior Lead Quality Assurance Developer<br />MySQL AB www.mysql.comjbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com2tag:blogger.com,1999:blog-19515582.post-1159582266099485242006-09-29T18:50:00.000-07:002007-04-11T08:50:19.190-07:00MySQL 5.1 Cluster TestingHi,<br /><br />It has been a while since I last posted. We have been busy testing MySQL Cluster, MySQL Replication and MySQL Cluster Replication. Good news is the serious bugs are getting harder to find. This is allowing us to spend some time automating more testing like configuration and up/down grade testing for example.<br /><br />The documentation guy for cluster, Jonathan "Jon" Stephens, has been doing great work to expand the Cluster on-line manual. If you have not been out there in a while to review you should have a look.<br /><br />http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html<br /><br />I feel very positive about this version of the cluster as it has had more hands on testing then ever before. The testing includes: Functional, Negative, Load/Stress, Performance, Recovery, Network Failure, etc...<br /><br />The new Disk Data will allow customer with huge database to start taking advantage of the reliability and scalability that the cluster offers.<br /><br />In addition the Cluster Replication opens a whole new use for cluster. This feature adds a few more 9's to the 99.999 up time for services and allows for many different strategies in database design to take place.<br /><br />One example would be to replicate to another cluster so that data mining and report generation can be done with out putting a load on the main system.<br /><br />In addition, you could replicate to another site so that if something goes wrong at the main site the secondary site could take over minimizing the companies down time.<br /><br />I would love to here from the users out there in the world that are using the cluster and have ideas of test they would like to see done, and or want to share how they are using it so we can incorporate that into our testing.<br /><br />I will attempt to post more often and keep you apprised of our efforts and share any tips or trick I run across that might run across.<br /><br />Cheers!<br /><br />/ Jeb<br /><br />Jonathan Miller<br />Austin, Texas USA<br />Senior Lead Quality Assurance Developer<br />MySQL AB www.mysql.com<br /><br />Jumpstart your cluster!<br />http://www.mysql.com/consulting/packaged/cluster.html<br /><br />Get training on clusters<br />http://www.mysql.com/training/courses/mysql_cluster.html<br /><br />All-in-one Enterprise-grade Database, Support and Services<br />http://www.mysql.com/network/jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com1tag:blogger.com,1999:blog-19515582.post-1154623303774261772006-08-03T09:18:00.000-07:002006-08-03T09:41:43.856-07:00MySQL Cluster can be too much typing<p class="MsoPlainText">Testing the MySQL Cluster can mean a lot of typing. Not being the best typist and getting tired of typing the same thing over and over again I created a short cut to make life better.<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">I have a file called shortcut.sh. Inside my .bash_profile for each of my test systems, I have added this file to be run when I login.<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">.bash_profile example<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">.<span style=""> </span>/home/ndb/jmiller/shortcut.sh<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">Then all the command and connection strings I need are just few key strokes away.<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">shortcut.sh<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">alias ndbd='/home/ndb/jmiller/builds/libexec/ndbd'<o:p></o:p></p> <p class="MsoPlainText">alias ndb_mgmd='/home/ndb/jmiller/builds/libexec/ndb_mgmd'<o:p></o:p></p> <p class="MsoPlainText">alias ndb_mgm='/home/ndb/jmiller/builds/bin/ndb_mgm'<o:p></o:p></p> <p class="MsoPlainText">alias mysql='/home/ndb/jmiller/builds/bin/mysql'<o:p></o:p></p> <p class="MsoPlainText">alias mysql_server='/etc/init.d/mysql.server'<o:p></o:p></p> <p class="MsoPlainText">alias ndb_restore='/home/ndb/jmiller/builds/bin/ndb_restore'<o:p></o:p></p> <p class="MsoPlainText">alias mysqldump='/home/ndb/jmiller/builds/bin/mysqldump'<o:p></o:p></p> <p class="MsoPlainText">alias installdb='/home/ndb/jmiller/builds/bin/mysql_install_db'<o:p></o:p></p> <p class="MsoPlainText">export NDB_CONNECTSTRING=n10:14000<o:p></o:p></p> <p class="MsoPlainText">alias mysqladmin='/home/ndb/jmiller/builds/bin/mysqladmin'<o:p></o:p></p> <p class="MsoPlainText">dbvar="/space/var"<o:p></o:p></p> <p class="MsoPlainText">ndbrun="/space/run"<o:p></o:p></p> <p class="MsoPlainText">jmiller="/home/ndb/jmiller"<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">Before doing this, to start a cluster I would have to do the following:<o:p></o:p></p> <p class="MsoPlainText">(short example)<o:p></o:p></p> <p class="MsoPlainText">Either cd to<o:p></o:p></p> <p class="MsoPlainText">$> /home/ndb/jmiller/builds/libexec/<o:p></o:p></p> <p class="MsoPlainText">or<o:p></o:p></p> <p class="MsoPlainText">type<o:p></o:p></p> <p class="MsoPlainText">$> /home/ndb/jmiller/builds/libexec/ndb_mgmd -f /path/to/config/file<o:p></o:p></p> <p class="MsoPlainText">and then<o:p></o:p></p> <p class="MsoPlainText">$>/home/ndb/jmiller/builds/libexec/ndbd -c n10:14000 --initial<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">as you can see this would get to be <span style="font-size: 12pt; font-family: "Times New Roman";">tiring</span>.<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">By adding the shortcut, I can just do<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">$>ndb_mgmd -f /path/to/config/file<o:p></o:p></p> <p class="MsoPlainText">$> ndbd<span style=""> </span>--initial<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">Note that since I exported my NDB_CONNECTSTRING I do not need to include the -c option when starting the ndbd. In addition, I can now use ndb_mgm (mgt client) any where on the host and I do not need to include the connect string.<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">example:<o:p></o:p></p> <p class="MsoPlainText">$>ndb_mgm -e show<o:p></o:p></p> <p class="MsoPlainText">$>ndb_mgm -e "all status"<o:p></o:p></p> <p class="MsoPlainText">$>ndb_mgm -e shutdown<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">In addition I created in the file above some shortcuts to directories that I often need to go to.<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">Example:<o:p></o:p></p> <p class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">cd /space/var<o:p></o:p></p> <p class="MsoPlainText">Or<o:p></o:p></p> <p class="MsoPlainText">cd $db<o:p></o:p></p> <p class="MsoPlainText"><o:p> It only saved 7 key strokes, but in a day of typing that can really add up.<br /></o:p></p> <p class="MsoPlainText">In short, we are supposed to work smarter, not harder. Hope this helps for those using MySQL Cluster<o:p></o:p></p>jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com1tag:blogger.com,1999:blog-19515582.post-1144861162315361412006-04-12T09:59:00.000-07:002007-04-09T04:07:34.766-07:00MySQL Cluster Replication<a href="http://mysql-qa.blogspot.com/">MySQL QA</a><br /><br />In the 5.1 release MySQL Cluster will support replication to another MySQL installation. The replication is handled using Row Bases Replication instead of Statement Based Replication.<br /><br />I have been testing it for about a year now and it has gotten really hard for me to break at this point, but I am still trying.<br /><br />So what is so cool about Cluster Replication? Good question!<br /><br />One answer:<br /><br />Cluster Replication really gives a company five 9's (99.999%) in up time. You can have a Master Cluster in the main site supporting the business and have another Slave Cluster in a total different site that can be used as a backup for times there are issues with main site. In addition, the Slave Cluster can also be used for reporting or data mining to take that traffic off of the main Cluster.<br /><br />How hard is it to setup? Another great question.<br /><br />Answer: Easy<br /><br />So to set it up, you would create two set of Clusters configured the way that you wanted them. You configure the Master MySQLD to create a bin log and to connect to the Master Cluster. You configure the Slave MySQLD to read from that Master MySQLD and to connect to the Slave Cluster. That is basically it. Of course you have to grant replication permissions, but I am sure you knew that. Here is are some examples:<br />******************************************************<br /><br />Master MySQLD my.cnf:<br /><br />[mysqld]<br />server-id=1<br />log-bin = /space/var/master1<br />log = /space/var/master1.log<br />log-error = /space/var/master1.err<br />socket = /tmp/mysql.sock<br />port = 3306<br />pid-file = /space/var/hostname.pid<br />datadir = /space/var/<br />language = /usr/mysql/english/<br />ndbcluster # Use NDB engine<br />ndb-connectstring=master01:1234 # location of MGM node ndb-connectstring=host:port<br /><br />Slave Cluster MySQLD my.cnf:<br />[mysqld]<br />server-id=8<br />relay-log=slave-relay-bin<br />replicate-ignore-db=mysql<br />master-user=rep<br />master-connect-retry=1<br />master-host=master01<br />master-password=test<br />master-port=3306<br />skip-slave-start<br />datadir=/space/var/<br />log-warnings<br />log-error=/space/var/slave.err<br />ndbcluster<br />ndb-connectstring=slave01:1234 # location of MGM node<br />******************************************************<br /><br />Hey, in your example the slave has an ID of 8, why? You noticed.<br /><br />Answer:<br />To really get the most out of the Cluster you need a lot feeding it. You can have more then one MySQLD process feeding the Master Cluster and producing bin logs for that matter.<br /><br />Well if you have more then one MySQLD feeding the Master Cluster then how does replication work?<br /><br />Answer: The injector thread.<br /><br />The Master Cluster is responsible for updating the Bin Log, not the MySQLD process. This is done through the Cluster Bin Log Injector Thread.<br /><br />Therefore you can have many MySQLD processes feeding the Master Cluster, but have the Slave only reading from one of them and not miss a beat (or data action).<br /><br />One other questions is why do you have skip-slave-start in you Slave my.cnf? You saw that too, hmmm.<br /><br />Answer:<br />Sometimes your Master Cluster will have been up a long time and you are wanting to bring a slave on-line, but you really don't want the slave in catch-up mode for days. So you can start the the MySQLD on the slave telling it to not start replication yet, restore a backup from the Master, run a couple of command, then start the slave and you will catch-up quickly and be in sync.<br /><br />To read more about this, checkout:<br /><br />http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication.html<br /><br />If you have ideas for testing, or have different scenarios you would like to see tested, please send them to me. Always looking to improve.<br /><br />Cheers!jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com9tag:blogger.com,1999:blog-19515582.post-1144759755302456092006-04-11T05:49:00.000-07:002006-04-11T10:05:02.206-07:00MySQL CLuster Disk Data QA<a href="http://mysql-qa.blogspot.com/">MySQL QA</a><br /><br />Now that we have Disk Data for MySQL Cluster you can have the best of both worlds. You can use memory tables for those needing quick access and updates and Disk Data Files for those that will be too large to keep in memory all the time.<br /><br />One issue with this first release is not having the ability to spread the data and undo files accross the disk sub system using the config.ini file. This can be a important issue for performance. You really don't want to have everything on one disk. Here I list a work around for moving undo and/or data files off to different drives to help in disk performance that I used during performance testing.<br /><br />1) Bring up the cluster to include all data nodes<br />2) Each data node will create its own File System ((e.g. ndb_#_fs) where # == the DN ID)).<br />3) Under the data node FS create the symbolic links pointing to the other drives:<br /><br />Examples Single Data Node:<br />[ndb_2_fs]$ ls<br />D1 D10 D11 D2 D8 D9 LCP<br />[ndb_2_fs]$ ln -s /data0/log1/ dn_logs<br />[ndb_2_fs]$ ln -s /data1/data/ dn_data<br /><br />We now have 2 symbolic links<br /><br />12 Jan 31 20:31 dn_data -> /data1/data/<br />12 Jan 31 20:31 dn_logs -> /data0/log1/<br /><br />Note: This needs to be done for each and every data node before going to next step:<br /><br />4) Now do the create statements<br />CREATE LOGFILE GROUP lg1<br />ADD UNDOFILE './dn_logs/undofile.dat'<br />INITIAL_SIZE 150M<br />UNDO_BUFFER_SIZE = 1M<br />ENGINE=NDB;<br /><br />CREATE TABLESPACE ts1<br />ADD DATAFILE './dn_data/datafile.dat'<br />USE LOGFILE GROUP TPCB_LOG<br />INITIAL_SIZE 5M<br />ENGINE=NDB;<br /><br />5) Doing the same for multi data nodes running on one host.<br /><br />Give each data node its own path<br />[ndbd]<br />Id: 2<br />HostName: host13<br />FileSystemPath: /data0<br /><br />[ndbd]<br />Id: 3<br />HostName: host13<br />FileSystemPath: /data1<br /><br />DN ID 2 will create it file system on /data0 and DN ID 3 will create its file system on /data1<br /><br />we then repeat the steps in steps 2 & 3 placing the files in the desired location using symbolic links, and the step 4 for the actual create.<br /><br />You can then make sure the above worked correctly:<br /><br />[dn_data]$ cd /data1/data/<br />[data]$ ls -l<br />total 2099304<br />-rw-rw-r-- 1 user group 2147581952 Jan 28 19:58 datafile.dat<br /><br /><br />Best wishes,<br />/JBMjbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com0tag:blogger.com,1999:blog-19515582.post-1144713231793755752006-04-10T16:53:00.000-07:002006-04-10T16:53:51.836-07:00MySQL 5.1<a href="http://mysql-qa.blogspot.com/">MySQL QA</a><br /><br />Testing 5.1 MySQL CLuster is moving along. We now have more automated test cases, have been using TPC-B and DBT2 in testing, conducted load and HA testing.<br /><br />The great news is that many issues are now found early before the customer base has to deal with them.<br /><br />This relase will be packed full of great stuff for Cluster including the first release of Disk Data and Replication for MySQL Cluster.<br /><br />The MySQL User Conf is just a few weeks away where much of this will be shown. Hope to see you there.jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com0tag:blogger.com,1999:blog-19515582.post-1133533599372842042005-12-02T06:26:00.000-08:002005-12-02T06:26:39.380-08:00MySQL QA<a href="http://mysql-qa.blogspot.com/">MySQL QA</a>
<br />Just a blog to leave comments about and suggestion of QA of MySQL, MySQL Cluster and MySQL Replication software.jbmhttp://www.blogger.com/profile/02201426409664912723noreply@blogger.com0