I realized that this blog sometimes got too focused on my hardware experiemnets, and with less MySQL content. This will not stop completely, but it will be complemented with a new blog, in Swedish I'm afraid, focused on hardware and on my computer setup at home. I looking forward to writing about these things, and if you read swedish, then you might want to pop by and have a look, the blog is called Kaos hos Karlsson which mean "Chaos at Karlssons house" or something like that, and it's online at http://www.kaoshoskarlsson.se.
/Karlsson
And don't worry, I will not stop blogging here, far from it!
Tuesday, June 21, 2011
Sunday, June 19, 2011
This stuff rocks, It really does! And Open Source is a big part of why it does so!
Karlsson goes to Chaos Manor
Sometimes you find a product that just amazes you. If you follow me on facebook you may already know that I am talking about the Synology DJ211j NAS unit. This is a feature-packed 2-disk NAS unit that really rocks, but let me tell you how I got started on this.
I have a bunch of machines here at the Karlsson mansion, and there are three main boxes:
So an upgrade was necessary to fix the problems. The MoBo was on the suspect list, as well as athe Dsik RAID setup then. Fixing the MoBo was reasonably inexpensive, as I decided to postpone the RAID issue with doing this in another way: Replace the RAID setup with a single 2TB disk, and as I had decided on a new MoBo with two SATA III channels (the ASUS p7P55D-E Pro), I went with a WD SATA III 2 Tb disk in addition to the MoBo. Now, with this hooked up, I copied the data from the RAID disks to the new 2Tb internal disk, the system was still unstable, so maybe it really was the RAID controller that was the issue then. With all the data sucessfully on the 2Tb internal disk, I removed all the old RAID disks and the Adaptec controller, and the reasult (this is really scary, I know): The system was still unstable like hell! Yikes!
So, one more thing to try. I removed to of the memory sticks, to run with just 4Gb, to try it out. And rightly so, the system was now stable. Why I didn't try this before, is beyond me, and I should have done that, I know.
With all my important data on just one disk though, I had another issue to fix, but I had already decided how to do that: Get a NAS and RAID it. I already have a NAS actually, an old clunky D-Link 323, which works and seems to have good quality, but is lacking features and is a bit sluggish, and I also have some other data on that. So I wanted a new NAS. In addition to that, the Windows box was now running with 4Gb only, and it still had one original problem, the Antec 180 based box was way too noisy! I had installed a reasonable quiet Noctua CPU cooler, but that was not by far quiet enough. The noise problem was fixed by installing 2 new Noctus NF-S12B OLF fans, where one is used for the CPU cooler. The two remaing fans out of 4 in this box was attached through a front-panel fan controller. This is really enough cooling as long as you do not overclock (something I do not do), and it turned this machine from an In-House jet engine into something that is just barely noticeable: Lesson: Get some good fans, they are worth it, and not even the expensive ones are THAT expensive!
I also had some more memory in the box, and memory is cheap these days, so with two new 4Gb each memory sticks, the box is now running stable.
One more thing to solve then: the new SAN. Reviews has been good for the Synology DJ211j box, so I was prett set on that. The alternative was the smaller DS111, which has just 1 internal disk, but compensates for this with an E-SATA port and slightly better performance than the 211.But as I was after RAIDing the setup in the box, using the E-SATA for the mirror disk seemed like a bad idea, so I went with the 211.
And what a marvelous piece of kit that was! The hardware build quality was good, although the disk mounting and the process for that was not as smooth as with the D-Link 323. But when you know what you are doing, this was easy enough. Getting started with the DS211j was again a bit more difficult than the 323, as there was some software installation and stuff needed, whereas the 32 was just a matter of installing the disks and plugging in the net and pwoer cables. But there the lus points for the 323 ends, and there was really nothing difficult with the DS211j either, asumiing you know a bit of what you are doing (and if you don't, maybe a NAS is not for you anyway). With the software installed in the DS211, I started the process of formatting the RAID set, and already at this stage I saw something interesting, the DSM (Synologys Disk Stoarge manager software) was way cool, it looks much like a clssic windowed GUI, like Mac, Windows or a desktop Linux distro, but running in a web-broswer window! Real neat stuff!
This "desktop in a browser" thing is WAY powerful. All the usual windowing function, including multitaking, resizeable windows, icons and stuff like that is there. Managing the DS211J is hence a breeze, despite this box having a truckload of features compared to my old 323. Among the features are the ability to use the 211 with network based survivalence cameras, use it to stream music, using Squeezebox, iTunes and DLNA or a load of other protocols.
Start the Audio-station software in the "Browser GUI", and you find that this puppy also supports internet radio, that then gets streamed to the PC that you are browsing from. I was real amazed at this: The browser based GUI knows that the PC that I run the Browser on can play sound is streams audio to it, just like that. There nothing to setup of configure to manage this, nothing to download, no drivers to install in either the PC or the NAS, it just was there.
Copying data from the front mounted USB port to the NAS disk is possible by just plgging in a USB disk device on the front panel and then just clicking a button. It also has two USB ports on the back. I have tried using the #"# and other network based things for desktop printer sharing, with little sucess. This time, as things looked so good, I decided to try this on the 211j. I plugged in the printer, enabled printer sharing, and that was it, it worked out of the box.
Now, assuming you do not use the sharing facility in the box, can you upload files to it? Sure you can, ftp and many other protocols are available. But the Browser based GUI comes with a neat trick up it's sleeve, which I didn't expect. When I open the file browser, I get a classic explorer style window with the file tree on the NAS, but not only that, I also get the file tree on the machine that I am running the GUI on, and I can copy files between the NAS and my PC by simple drag-and-drop. Real neat!
These are just some of the great features of this NAS. And is there a MySQL connection here? Sure there is, of course! The Open Source based toolkit that makes up the Synology DSM also includes a web-server, PHP and MySQL, out of the box. Setting up a LAMP stack web-server has neever been easier.
To wrap this up, in conclusion: If you want a real pwerful NAS, which isn't that expensive either, and that you can have some fun with, and not just use as a NAS, but also do loads of other useful stuff with, then the Synology DS211j is the machine you want. And it also provide realy good performance. And no, I do NOT work for Synology and I am in no way affiliated with them.
/Karlsson
Sometimes you find a product that just amazes you. If you follow me on facebook you may already know that I am talking about the Synology DJ211j NAS unit. This is a feature-packed 2-disk NAS unit that really rocks, but let me tell you how I got started on this.
I have a bunch of machines here at the Karlsson mansion, and there are three main boxes:
- A laptop workhorse
- A Linux desktop workhorse
- A Windows desktop workhorse
So an upgrade was necessary to fix the problems. The MoBo was on the suspect list, as well as athe Dsik RAID setup then. Fixing the MoBo was reasonably inexpensive, as I decided to postpone the RAID issue with doing this in another way: Replace the RAID setup with a single 2TB disk, and as I had decided on a new MoBo with two SATA III channels (the ASUS p7P55D-E Pro), I went with a WD SATA III 2 Tb disk in addition to the MoBo. Now, with this hooked up, I copied the data from the RAID disks to the new 2Tb internal disk, the system was still unstable, so maybe it really was the RAID controller that was the issue then. With all the data sucessfully on the 2Tb internal disk, I removed all the old RAID disks and the Adaptec controller, and the reasult (this is really scary, I know): The system was still unstable like hell! Yikes!
So, one more thing to try. I removed to of the memory sticks, to run with just 4Gb, to try it out. And rightly so, the system was now stable. Why I didn't try this before, is beyond me, and I should have done that, I know.
With all my important data on just one disk though, I had another issue to fix, but I had already decided how to do that: Get a NAS and RAID it. I already have a NAS actually, an old clunky D-Link 323, which works and seems to have good quality, but is lacking features and is a bit sluggish, and I also have some other data on that. So I wanted a new NAS. In addition to that, the Windows box was now running with 4Gb only, and it still had one original problem, the Antec 180 based box was way too noisy! I had installed a reasonable quiet Noctua CPU cooler, but that was not by far quiet enough. The noise problem was fixed by installing 2 new Noctus NF-S12B OLF fans, where one is used for the CPU cooler. The two remaing fans out of 4 in this box was attached through a front-panel fan controller. This is really enough cooling as long as you do not overclock (something I do not do), and it turned this machine from an In-House jet engine into something that is just barely noticeable: Lesson: Get some good fans, they are worth it, and not even the expensive ones are THAT expensive!
I also had some more memory in the box, and memory is cheap these days, so with two new 4Gb each memory sticks, the box is now running stable.
One more thing to solve then: the new SAN. Reviews has been good for the Synology DJ211j box, so I was prett set on that. The alternative was the smaller DS111, which has just 1 internal disk, but compensates for this with an E-SATA port and slightly better performance than the 211.But as I was after RAIDing the setup in the box, using the E-SATA for the mirror disk seemed like a bad idea, so I went with the 211.
And what a marvelous piece of kit that was! The hardware build quality was good, although the disk mounting and the process for that was not as smooth as with the D-Link 323. But when you know what you are doing, this was easy enough. Getting started with the DS211j was again a bit more difficult than the 323, as there was some software installation and stuff needed, whereas the 32 was just a matter of installing the disks and plugging in the net and pwoer cables. But there the lus points for the 323 ends, and there was really nothing difficult with the DS211j either, asumiing you know a bit of what you are doing (and if you don't, maybe a NAS is not for you anyway). With the software installed in the DS211, I started the process of formatting the RAID set, and already at this stage I saw something interesting, the DSM (Synologys Disk Stoarge manager software) was way cool, it looks much like a clssic windowed GUI, like Mac, Windows or a desktop Linux distro, but running in a web-broswer window! Real neat stuff!
This "desktop in a browser" thing is WAY powerful. All the usual windowing function, including multitaking, resizeable windows, icons and stuff like that is there. Managing the DS211J is hence a breeze, despite this box having a truckload of features compared to my old 323. Among the features are the ability to use the 211 with network based survivalence cameras, use it to stream music, using Squeezebox, iTunes and DLNA or a load of other protocols.
Start the Audio-station software in the "Browser GUI", and you find that this puppy also supports internet radio, that then gets streamed to the PC that you are browsing from. I was real amazed at this: The browser based GUI knows that the PC that I run the Browser on can play sound is streams audio to it, just like that. There nothing to setup of configure to manage this, nothing to download, no drivers to install in either the PC or the NAS, it just was there.
Copying data from the front mounted USB port to the NAS disk is possible by just plgging in a USB disk device on the front panel and then just clicking a button. It also has two USB ports on the back. I have tried using the #"# and other network based things for desktop printer sharing, with little sucess. This time, as things looked so good, I decided to try this on the 211j. I plugged in the printer, enabled printer sharing, and that was it, it worked out of the box.
Now, assuming you do not use the sharing facility in the box, can you upload files to it? Sure you can, ftp and many other protocols are available. But the Browser based GUI comes with a neat trick up it's sleeve, which I didn't expect. When I open the file browser, I get a classic explorer style window with the file tree on the NAS, but not only that, I also get the file tree on the machine that I am running the GUI on, and I can copy files between the NAS and my PC by simple drag-and-drop. Real neat!
These are just some of the great features of this NAS. And is there a MySQL connection here? Sure there is, of course! The Open Source based toolkit that makes up the Synology DSM also includes a web-server, PHP and MySQL, out of the box. Setting up a LAMP stack web-server has neever been easier.
To wrap this up, in conclusion: If you want a real pwerful NAS, which isn't that expensive either, and that you can have some fun with, and not just use as a NAS, but also do loads of other useful stuff with, then the Synology DS211j is the machine you want. And it also provide realy good performance. And no, I do NOT work for Synology and I am in no way affiliated with them.
/Karlsson
Monday, June 13, 2011
Database cleaning made easy: MyCleaner 1.2 released
I have released MyCleaner 1.2 today and it is avaiable for download from sourceforge, as is the documentation pdf as usual. This version includes a whole bunch of new features, to allow different kinds of database cleaning operations than before. In this case, using this program is not terribly unique, it just a multi-threaded SQL runner. The noce thing is to allow you to use the same program for all your database cleaning needs.
In addition to this, this version has better statistics printout, better control og logging and a whole bunch of minor features.
Cleaning has never been easier or more fun than now! Also, when not used, MyCleaner will conveniently fold and fit under your bed :-)
/Karlsson
In addition to this, this version has better statistics printout, better control og logging and a whole bunch of minor features.
Cleaning has never been easier or more fun than now! Also, when not used, MyCleaner will conveniently fold and fit under your bed :-)
/Karlsson
Wednesday, June 8, 2011
Cleaning up a MySQL database: mycleaner 1.1 released
The MyCleaner program, that is used to clean up data in the MySQL database that is no longer used, no longer referenced or is just unnecessary, is now available for download from SourceForge.net in version 1.1. This new version adds a few things, like the ability to sleep between rounds, more extensive log print control, and above all, the ability to run a non-SELECT to get IDs.
The latter feature may seem weird, but in some cases it is useful, as it will allow you to select IDs into a temporary table, for example, and then use these for deleting the real data, something which can be achieved in many other ways of course, but which is still useful in some cases.
In addition, the ability to run SQL after the program having finished all other processing, has been added, to allow cleanup of temporary objects.
/Karlsson
The latter feature may seem weird, but in some cases it is useful, as it will allow you to select IDs into a temporary table, for example, and then use these for deleting the real data, something which can be achieved in many other ways of course, but which is still useful in some cases.
In addition, the ability to run SQL after the program having finished all other processing, has been added, to allow cleanup of temporary objects.
/Karlsson
Thursday, June 2, 2011
Automatically restarting MySQL slaves the easy way.
MySQL Replication is something that is used by many, many MySQL users, and here at Recorded Future we are no exception. In our case, the slaves are used for different purposes, and as we develop our system so much and so fast, so sometimes things happen which could have been avoided. Like something getting executed in the master that really should not end up in a slave at all, and which would cause all sorts of problems on the slave.
Also, some things that work fine on the master can sometimes cause things to break on the slave, a typical such issue is a big operation on the master that when executed on the slave would cause a lock timeout. And you could argue that the lock timeout should be increased, but the question is how much, and frankly, do you really want those locks hanging around? And in some cases, it's the replication thread that has a lock timeout.
I can easily identify three different actions to take based on the errors that the MySQL slave had, and which cased the slave to stop processing, and which I have so far processed manually:
To be honest, this is as far from rocket science as you can get I think, but the script works well so here it is.
It's a bash shell-script and I run it on Ubuntu, but I don't use many fancy bash features, so it should be easy enough to understand. There is a --help option, and you can configure which errors not to attempt a restart on, which errors to skip one binlog record and restart on (any errors beside these, a restart is attempted on) and a sleep-time to set the time between checking the slave status (default is 10 minutes).
As i said, nuthin' fancy, but simple and workable. And before I finish this up, no i didn't check if someone has already published a script for this, I assume someone has, but here is my attempt at the same thing then.
Cheers
/Karlsson
Also, some things that work fine on the master can sometimes cause things to break on the slave, a typical such issue is a big operation on the master that when executed on the slave would cause a lock timeout. And you could argue that the lock timeout should be increased, but the question is how much, and frankly, do you really want those locks hanging around? And in some cases, it's the replication thread that has a lock timeout.
I can easily identify three different actions to take based on the errors that the MySQL slave had, and which cased the slave to stop processing, and which I have so far processed manually:
- Errors which really is no issue at all to restart the slave on. One such issue is lock timeouts, but there are more.
- Then there are errors which you just know you can skip a record in the binlog on. In our case, error number 1317 is one such error. This is an error when a large operation has been interupted on the master, for example a large INSERT INTO .. SELECT statement. In our case, this only happens rarely when we use large temp tables, and more or less always we really should not have the temp table on the slave at all. What this means is that, in our case, these errors can be disregarded, we can stop this binlog record and go on to restart the slave. I.e. we can do a SET SQL_SLAVE_SKIP_COUNTER=1; and then a SLAVE START; just like that.
- And then there are errors which needs manual intervention always, we just should not attempt a restart.
To be honest, this is as far from rocket science as you can get I think, but the script works well so here it is.
It's a bash shell-script and I run it on Ubuntu, but I don't use many fancy bash features, so it should be easy enough to understand. There is a --help option, and you can configure which errors not to attempt a restart on, which errors to skip one binlog record and restart on (any errors beside these, a restart is attempted on) and a sleep-time to set the time between checking the slave status (default is 10 minutes).
As i said, nuthin' fancy, but simple and workable. And before I finish this up, no i didn't check if someone has already published a script for this, I assume someone has, but here is my attempt at the same thing then.
Cheers
/Karlsson
Subscribe to:
Posts (Atom)