Building MySQL on Windows – MySQL Forge Wiki
This one covers running mysqld in the VisualStudio debugger, which can be useful.
I have no special ndb_mgmd.exe or ndbd.exe in debugger instructions or wisdom (running them from mysql-test-run.pl at least). I’ve attached debugger to already running (started by mysql-test-run.pl) ndb processes, but haven’t made any changes to mtr to make it like the mysqld of “go and enter this”.
You will need:
Then, get and build it:
Now you can go and have much whisky as this will take a few minutes. You should now have a set of built binaries for MySQL Cluster on Windows. Scary.
“MySQL Cluster 6.4 Windows tree†branch in Launchpad
(which really should have the -fail suffix… but anyway)
In what will (soon) be mirrored to launchpad, all but 17 targets (yeah, working on that… but it’s out of 130 or something) build.
Not only that, I’ve used the management client (ndb_mgm.exe) to monitor the cluster running my Bugzilla instance (which is now a rather old 6.3 build).
Getting closer to NDB on Windows.
Be afraid. Be very, very afraid.
“MySQL Cluster 6.4 Windows tree†branch in Launchpad
That’s right folks, I’m pushing up patches for MySQL Cluster on Windows. This tree is incomplete, and no promises on when enough will be pushed for it to even compile on Windows.
Tree is updated when launchpad pulls from our internal tree.
Vervante Books Etc — MySQL 5.1 Cluster DBA Certification Study Guide
Only $40. Written by absolute brilliant people (and me). Needing to learn MySQL Cluster so you can go and use it? test it out? work out if it’s for you? Get this book!
In code just tested:
ndb_mgm> ndbinfo MEMUSAGE
RESOURCE_NAMEÂ Â Â NODE_IDÂ Â Â PAGE_SIZE_KBÂ Â Â PAGES_USEDÂ Â Â PAGES_TOTALÂ Â Â BLOCK
IndexMemory,1,8192,16,160,DBACC
DataMemory,1,32768,20,640,DBTUP
IndexMemory,2,8192,16,160,DBACC
DataMemory,2,32768,20,640,DBTUP
Win!
This is the first time that we’ve been able to get this kind of info out of the cluster without using the magic “all dump 1000” (or “all report MemUsage”) which end up using events, which go to the log file, aren’t exactly reliable etc.
This performs a scan on the NDBINFO tables (in ndbd) from ndb_mgmd and returns the result to the management client. You can then use this in scripts from the command line. e.g. to find out how many pages of datamemory are used on each node:
$ ./storage/ndb/src/mgmclient/ndb_mgm -c localhost:9311 -e ‘ndbinfo MEMUSAGE’|tail -n +3|grep ‘DataMemory’|cut -d ‘,’ -f4
20
20
now, just to clean it up a bit, fix the one bug (yes, you guessed it: in metadata caching) and get a review….
but, a milestone!
There’s been talk over the years of better monitoring for NDB (MySQL Cluster). This has been dubiously named NDB$INFO, after some special magical naming convention for tables holding information on the insides of NDB. Otherwise known as Worklog 3363 (viewable on MySQL Forge).
The basic idea is to get a bunch of things that are already known inside NDB available through a rather standard interface (SQL is preferred).
My top examples are “How much DataMemory is used?” and “Do I need to increase MaxNoOf(Tables|Attributes|ConcurrentTransactions)?”. You can get some of this information now either through the management client (ndb_mgm -e “all report MemoryUsage”) or the MGM API using events and some other foo.
This is a rather limited interface though. It would be great if you could point all your monitoring stuff to a MySQL Server, throwing queries at it and finding out the state of your cluster.
So this year I’ve been working on implementing NDB$INFO. The big requirements (for me at least) are:
Among the 492 things I’m currently doing, is fixing up a basic patchset for NDB$INFO and working on getting it into the tree. It’s all going to be basic scan interfaces in the current version, so things may be slow if there’s lots of rows, but they’ll get there.
What would you like to see exposed?
Following my successful linux.conf.au talk “Eat My Data: How Everybody Gets POSIX File I/O Wrong“, I started to feel the need to easily be able to have my data eaten.
Okay, not quite. However, when you’ve written your software properly, so it uses fsync() correctly, opening files with O_SYNC or whatever – tests take longer as you’re having to wait for things to hit the rust.
So….. LD_PRELOAD=libeatmydata.so to the rescue! With a POSIX compliant fsync() (that does nothing) and filtering on open(2), it can take your test run times down dramatically.
The only time you shouldn’t use it for your tests is when you end up crashing the machine to test durability (i.e. when the OS doesn’t have the opportunity to cleanly write out the data to disk).
See the libeatmydata project page: http://www.flamingspork.com/projects/libeatmydata/
and the bazaar repository: http://www.flamingspork.com/src/libeatmydata
(it’s seemed to have saved somewhere between 20 and 30% of the time for innodb/ndb tests in mysql-test-run).
Quick experiment with online changing of enabling/disabling compressed backups and local checkpoints (LCPs).
Backup is incredibly trivial and correct (even have some nodes do compressed, some not).
LCPs are a bit trickier when it comes to restore… currently how the code sits is that a block using the compressed file interface in NDBFS must specify if it wants to use the compressed read/write interface or not. So when you have LCPs that differ in compressed/non-compressed than the current config file setting, you’re not going to be able to restore them (although setting CompressedLCP=1 should let you restore either compressed or non-compressed LCPs).
At some point, I’ll probably move AsyncFile (our async file IO class) to just use azio alway, and modify azio to be transparent for non-compressed files…. I just have to fix up azio for direct io.
possibly:
“Achieving Web 2.0 Social Networking Synergies with NDBAPI through MySQL Proxy”
(yet another possible cool thing coming from a quick hack at DevConf)
So, here is an Alpha quality port of the MySQL Cluster management server to Win32 based on the current MySQL 5.0 tree.
This isn’t going into 5.0, so don’t expect to ever have that.
This isn’t going into 5.1 either, so don’t expect it there.
It’ll go into some future release at some level of general “supported” status that has yet to be decided.
ONLY USE THIS FOR EXPERIMENTAL PURPOSES.
IT IS EARLY RELEASE – IT HARMS PUPPIES!
But, it would be great for those who may be interested in having a ndb_mgmd on Win32 at some point to grab the binary, have a play and find some bugs.
For any bugs filed, please submit to bugs.mysql.com and explicitly mention that it’s version “5.0.50-ndbwin32r1” and mention that it’s the specific build (i.e. it shouldn’t go through the normal bug verification procedure and instead end up with me looking at it directly).
So, here’s the files:
Hopefully this brings you joy.
Oh, and yes, you can go and run it under WINE so you don’t have to actually use MS Windows.
enjoy!
Well… there’s been some work. Even some in-progress patches. Being involved with this has just perfectly refreshed my memory of why I left the platform. Oh my it’s a horrible, horrible platform. Everything from UI to API… ick.
Expect something around soon….
Thoughts on latest changes:
However, I have other thoughts (that are a lot more interesting to discuss):
We should:
Past problems:
There are a lot of things looking good and being done right too (or if not right, a lot better than a year or two ago). e.g.
Things we should fix with commits, code review and all that:
Things we should fix internally:
Things I don’t think will happen but could be useful…:
What if it all goes proprietary:
My thoughts on the non-free Network Monitoring and Advisory Service:
P.S. there’s nothing here I wouldn’t say to anybody… and they’re welcome to disagree (and they do… sometimes even for good reasons).
ndb_mgm> all report MemoryUsage
Node 1: Data usage is 11%(632 32K pages of total 5440)
Node 1: Index usage is 22%(578 8K pages of total 2592)
Node 2: Data usage is 61%(3331 32K pages of total 5440)
Node 2: Index usage is 40%(1039 8K pages of total 2592)
ndb_mgm>
Oh, and that’s coming from saved command history.
(as seen when upgrading my cluster here to mysql-5.1.19 ndb-6.2.3 – i.e. MySQL Cluster Carrier Grade Edition – i.e. the -telco tree)
Over the past N weeks/couple of months, we’ve been making a number of improvements to how backups are done in MySQL Cluster.
Once you get to large data sets, you start to really care about how long a backup takes.
Traditionally, MySQL Cluster has been in-memory only. The way to back this up is to just write from memory to disk (rate limited) and synchronised across the cluster. Since memory is really fast (compared to the rate we’re writing out to disk) – never had a problem.
In MySQL 5.1 (and Cluster Carrier Grade Edition- CGE), disk based attributes are supported. This means that a row has both in memory and disk based parts. As we all (should) know, disk seeks take a very long time. We don’t want to seek.
So, at some point recently we changed the scanning order from in-memory order (which previously made perfect sense) to on disk order. Randomly seeking through RAM is much cheaper than all the disk seeks. This greatly improved backup performance.
We also did some read-ahead work, which again, greatly improved performance.
Today, I see mail from Jonas about changing the way we read tuples for backup (and LCP) to make it even more efficient (READ_PACKED). This should also reduce CPU usage for LCP/Backup… which is a casual issue. I should really take the time to look closely at this and review.
I also wrote a patch to the code in NDB that writes files to disk to write a compressed gzio stream instead of an uncompressed one. This happens in a different thread, so potentially using one of those CPU cores that ndb wouldn’t otherwise use… and also dramatically reducing the amount of data written to disk…. this patch isn’t in any tree yet, and I’ve yet to try it with the READ_PACKED patch, which together should work rather well.
I also need to grab Brian at some point and find out why azio (as used by the ARCHIVE engine) doesn’t work the same way as gzio for basic stream writing…
Linus is right when he talks about the performance of SCMs…. and that BitKeeper was about the first one to be worth using at all (really).
But as an interesting speed comparison… I’ve managed to pull the latest git (with git) and build it in less time than BitKeeper has taken to pull the latest NDB tree…. hrrm..
One of the reasons I’m so enjoying quilt for every day hacking is that it is blindingly fast.
Kristian is currently talking about the new NdbRecord API for the NDBAPI and how it relates to ha_ndbcluster (the mysql storage engine, which uses ndbapi to talk to the cluster nodes) and how it can be used by ndbapi applications.
It looks like we’re getting a really neat API that avoids so much mess and makes it possible to write incredibly efficient mappings between what comes over the wire from data nodes and whatever internal structures the application wants to fill out.
Talking about this and Monty Taylor’s ORM mapping stuff could be very interesting.
Also now up is the patch series in my “ndb-work” tree which small patch for adding INFORMATION_SCHEMA.NDB_NODE_STATUS. It’s nearly useful… I haven’t brought in the nice “id to string” functions in the management client that make pretty printing nice… so not quite end user friendly :)
But it’s a nice patch to learn how to add an INFORMATION_SCHEMA table in a pluggable engine and put some engine specific information in it.
(kudos to the falcon code… which i looked at on how to do it).
Doesn’t take long – this was completed in less than 2hrs while watching and paying attention to sessions…. so should take next to no time if you actually concentrate on it.
Of course, this totally abuses the purity of the information schema.
I’ve just put up the current “add node” patch… which is like, totally experimental and kills kittens… but could be interesting for people to have a look at as it progresses. Still lots of work before production ready – but people here at the MySQL Conf have said they’re interested in looking at the code for it.
You can grab a combined patch or the quilt series from:
http://saturn.flamingspork.com/~stewart/ndb-experimental/
Applies to 5.1… at least on a few weeks ago tree.
Ivan mentions the Q and As from a Q&A session in which MySQL Cluster is mentioned – I thought I’d add my perspective here as well:
Q from Matthew: When are we likely to see disk based indexing for ndb?
Disk based indexing is planned in one of the future releases, but we can’t say when we will implement it. During the webinar, Anders pointed out that he does not see this as an important thing. I tend to agree with Anders, at least considering the current status of the storage engine. At the moment, ndb can perform an unbeatable job (in terms of HA and performance) on small transactions and simple queries and we should not consider it as a full replacement for the whole database, in general. The future versions of ndb will probably be more and more general purpose and at some point a full disk based ndb will be valuable. Please take this as my personal opinion.
Implementing disk based indexes is a fair bit of work… Certainly not this year (or early next). Sure, it’s a crucial step towards world domination… but it does have to sit in a priority queue of other steps.
Q from Malcolm: Is their any difference between MysQL Cluster and the telecoms version?
As Bertrand said, MySQL Cluster Carrier Grade is a specific version for telecom, developed closely with major equipment manufacturers. During the presentation I have highlighted some differences – such as the availability of more data nodes and so on. We will cover MySQL Cluster and MySQL Cluster Carrier Grade Edition in one of the future sessions.
It’ll be good to have a special session on the difference. The basic difference is that we’re a bit more selective about what patches go into the Carrier grade trees – and sometimes some features will go there first (when customers really need it). We will typically try to be less invasive in some areas too. Odds are though, if you’re not a telco, you don’t need it.
Q from Fabio: Any plan for MySQL Cluster for Windows?
We are considering it sometimes in the future, but no plans have been made so far.
Yes, this has been “being considered” for years. No, it’s not going to happen any time soon. Patches welcome.
Q from Owen: Is it difficult to define memory requirements for MySQL Cluster?
MySQL Cluster configuration is the most important step when you adopt this technology. We have seen several do-it-yourself configurations, running perfectly. But Cluster configuration is not straightforward and we always recommend to get some help from our Professional Services team.
Each time I patch ndb_size.pl it gets more accurate and is less outrageously wrong in some scenarios now :) It can help… although you also need to know what you’re measuring – and account for future growth.
Q from Alessandro: Is carrier grade avalaible for download?
As Bertrand said, please contact us at http://www.mysql.com/company/contact/ if you are interested in MySQL Cluster Carrier Grade for telecom customers
I beleive the plan is to publish the BK trees as well… but certainly not the supported way to run it.
There was also some talk on DRBD and shared disk clusters. Neither of these prevent against file system corruption. Also, if using a non-crash safe engine (e.g. MyISAM) when you fail over you’ll probably have to do a bunch of table checks – not exactly HA.