coderchris.com

So, I needed to upgrade MySQL on our development boxes today and I was met by a little surprise from the RPM program…

Basically it won’t do an upgrade as the vendor has changed from being MySQL AB to Sun Microsystems, and as a result I have to do a complete uninstall and re-install manually…

Ho hum, I know it’s a small issue and for the best, but it’s still a pain in the ass when something silly such as  vendor name change wastes time in what would otherwise be a quick and simple upgrade.

So anyway as I’m going through it the following might be useful to you if you have to do the same any time soon.

First download all the current MySQL packages you need:

$hell> mkdir mysql-5.1.34

$hell> cd mysql-5.1.34

$hell> wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-server-community-5.1.34-0.rhel4.i386.rpm/from/http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/

$hell> wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-client-community-5.1.34-0.rhel4.i386.rpm/from/http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/

$hell> wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-shared-community-5.1.34-0.rhel4.i386.rpm/from/http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/

$hell> wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-shared-compat-5.1.34-0.rhel4.i386.rpm/from/http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/

$hell> wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-devel-community-5.1.34-0.rhel4.i386.rpm/from/http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/

$hell> wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-test-community-5.1.34-0.rhel4.i386.rpm/from/http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/

$hell> wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-community-debuginfo-5.1.34-0.rhel4.i386.rpm/from/http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/

Then  stop all running MySQL Process:

$hell> /etc/init.d/myst stop

Then find all the MySQL packages you need to remove by running:

$hell> rpm -qa | grep -i '^mysql-'

Then uninstall each e.g.:

$hell> rpm -e MySQL-client-community-5.1.29-0.rhel4

Then re-install all the new ones you just downloaded e.g.:

$hell> rpm -i MySQL-shared-community-5.1.34-0.rhel4.i386.rpm

Then run the MySQL upgrade program to do the final checks and upgrade the MySQL system database if necessary:

$hell> /usr/bin/mysql_upgrade -uroot -p

And that’s it, all should work nicely again

Remember though, you shouldn’t upgrade between major versions that aren’t in sequence. i.e. Don’t upgrade from MySQL from 4.0 to 5.1 as the additions to the software made in 4.1, 5.0, etc. can be lost by skipping these intermediate upgrades.

I can across a few other funky things in MySQL 5.1 today that I thought it might be worth telling you about in case you ever come across them too.

This time I was partitioning a number of large tables in and initially started to get the same weird errors as I did before when stupid queries were running away with themselves due to lack of temp space.

When you partition a table, MySQL seems to build a partitioned copy of it on the file system before swapping to that table for general use – which seems like a fair way to go, but if you don’t have enough temp space for the new table to be built in you get issues similar to those I discussed here, and you can get round them in the same way.

In my case, when doing this kind of maintenance I now add an ‘overflow’ path to the tmpdir variable which is basically a dir on part of the local filesystem with a large chunk of free space on it, but that isn’t on the same partition as the MySQL tables themselves.

This allows these operations to spill over when they need to without causing a lot of hassle – be warned though, it’s not generally a good idea to use a fileshare on a NAS for this procedure! I don’t know how well it’d work with a SAN as I don’t have one to play with, but doing it on a NAS will be REALLY slow in most cases and may cause other issues as a result.

But anyway that’s not the main thing I wanted to talk about.

What I wanted to talk about here is all your databases suddenly apparently vanishing altogether from MySQL after you’ve implemented some a partitioning scheme. I guess the same would apply if you suddenly added a load of new databases/database tables to your MySQL setup too.

At the same time as vanishing databases I’ve also seen errors where MySQL reports that it cannot open the directory on the file system that a particular database resides in, and other similar filesystem related error messages.

What causes this to happen? Well it appears to be the a combination of the max number of connections, the max files mysql  can open and the table cache, which dictates the number of files MySQL can have open at any one time.

Basically what seems to happen is that when table cache gets full, MySQL essentially dies not being able to open any more database files, and therefore cannot access any more information either. BUT because the main operation on the server is not interrupted it seems that the MySQL process doesn’t die - it just continues to run but without access to any information, as if none of it ever existed!

This tends to happen after partitioning has been done because a partitioning scheme can result in a large number of new database data files being produced – after all, that’s all it’s doing, breaking one massive single myISAM file down into more manageable chunks.

Also another reason it tends to happen after partitioning is because partitioning came in in MySQL 5.1 and in MySQL 5.1 the name of the table cache server variable (at least) changed from table_cache to table_open_cache. So, if your my.cnf or my.ini still references table_cache and your running 5.1.3 upwards you’ll not actually be setting this value any more and as a result the server will revert to its default value – namely 64 – a tiny amount.

I have seen recommendations that you should set this value to around to 2048 for a lot of systems (which seems a bit arbitrary really), but the way to determine what kind of numbers you should be using here comes from analysing the results of the opened_files server status variable traded off against the max connections, and whatever the table cache is currently set to - see the resources links below for more info on this – you can access this status variable and a few other useful related parameters by running: 

SHOW STATUS LIKE '%open%';

 

Issues can also ensue when the number of files opened by the server exceeds/hits the limit of the total number of files the operating system allows any one user to open, and if this actually appears to be the case you need to check the docs for your OS in order to up this limit.

To get an idea of the maximum number of tables mysql might look to open at any one time you can count the number of files in the mysql data dir  - this dir might also contain other junk like bin logs, error logs, etc. but for a rough upper limit this will do it:

ls -1R | wc -l

If your data dir is polluted with logs then you can filter this kind of a count through grep and add up the results:

ls -1R | grep *.MYI | wc -l

ls -1R | grep *.MYD | wc -l

 

To find out the number of files mysql has open currently you can also do this:

$shell> lsof | grep mysql | wc –l

You can find out the number of max open files your OS supports by running this:

$shell> cat /proc/sys/fs/file-max

Some resources on these issues:

http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

http://forums.mysql.com/read.php?35,168304,168304

http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_open-files-limit

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_table_open_cache

http://dev.mysql.com/doc/refman/5.0/en/not-enough-file-handles.html

http://confluence.atlassian.com/display/DOC/Fix+%27Too+many+open+files%27+error+on+Linux+by+increasing+filehandles

http://www.linuxquestions.org/questions/linux-general-1/number-of-files-in-directory-274630/

http://www.techiesabode.com/article/read_article_w.php?article_id=2

When you have a number of different servers to administer (yes administer - administrate is not a real word!), all across different platforms, switching between different client programs can get very tiresome very quickly.

As a result there are a few programs out there that act as all in one clients for Windows Remote Desktop connections, VNC Connections, SSH, Citrix, etc. These can be REALLY useful in this situation and can save a lot of time and hassle while in some cases reducing the chance of user error when switching between apps.

We worked with a commercial tool, iShadow, for about a year, for this and soon realised its utility but although it was commercial, it was clunky and very very temperamental when it came to storing/loosing passwords and connection profiles. So we set out to find an alternative.

Thankfully, Kelvin, our Technical Manager found MRemote, a free, stable and nice to use client which does the job very well. Yes, it is basically an interface on top of a lot of existing open source client programs which it loads as components, but why re-invent the wheel when these things in their own right work, and work well?

So without raving about it much more, if you manage a load of servers and want to simplify the process somewhat why not give MRemote a go. The only thing I think it's missing, from my point of view, is an interface to the NX Client which I use on some of my machines, and maybe database servers such as MySQL, but aside from that it's fantastic!

I have a dual boot Windows Vista / Linux Laptop and recently I needed to access the Linux partitions from within Windows to copy data between the operating systems.

So I did a bit of searching on the net and had trouble finding a tool/page describing how to do it with an LVM (Logical Volume Manager) partition, all tools seemed to just work with plain old ext2 or ext3 file systems with no mention of others.

This is a problem with at least CentOS 5.1 (Red Hat Enterprise) and probably all modern Linux distributions as there seems to be a move towards the LVM methodology for all operating systems - understandably so when you read what it's all about from wikipedia: http://en.wikipedia.org/wiki/Logical_volume_management

But then I noticed that Explore2FS from chrysocome.net actually also supports LVM2 as well as ext2 / ext3, and it works great!

All I had to do was download (at the time of writing) explore2fs 1.08beta9 extract it from the archive and run it - job done! I can get to my standard Linux files (and more actually) with ease from within windows through an explorer style interface. Just what I needed!

The guy(s) who developed Explore2FS are also working on another tool called Virtual Volumes but this really is a beta (at the time of writing) and I couldn't get this to work in a useful way what so ever BUT this should be a winner when it works too!

So now it just remains for me to setup ntfs support in CentOS 5.1 and I can go both ways - woohoo!

After having no end of issues with the proprietary flash media server 2 from Adobe I decided to give installing the Red5 open source version a go instead.

I have to say I was fairly impressed with just how easy it was to get running without having to do any of the faffing around that was required to get FMS2 running.

Here is the process in a nutshell:

1. Download and install the Java Development Kit (jdk) from here (I used the rpm version of JDK 6 Update 2): http://java.sun.com/javase/downloads/index.jsp
2. Download and extract Apache ant from here: http://archive.apache.org/dist/ant/binaries/apache-ant-1.6.5-bin.tar.gz
3. Copy the extracted apache-ant-1.6.5 folder to /usr/local/ant
4. Setup the environment variables to include the path for java and ant by typing the following at the shell prompt:PATH=$PATH:$HOME/bin:/usr/local/ant/bin
   export PATHThen also remember to add this to  /etc/profile so the settings don't get lost the next time you login
5. Download and extract the Red5 server from here: http://osflash.org/red5
    
6. cd to the Red5 directory you just extracted and type the following at the shell prompt to allow java to retrieve the files it needs from the net and compile and run the server as a background process (the module retrieval is automatic and only has to be done once):ant server & 
7. Then that's it it's running. In my case I needed to kill the ant/java processes running the server after the java modules were initially retrieved and the server built for the first time, but the second time it was fine even though it did take about 30 seconds to fully startup - this may just be a glitch on my system as I have a lot of other stuff also going on.
8. The final step is to test it. This can be done by moving the contents of the webapps/root/demos/ folder from the extracted Red5 directory to a webserver somewhere (I don't think it hast to be the same machine) and then viewing the list of demos in the index.html file.I started off by using the port tester to make sure the demo apps could actually connect to the server on the ports they were meant to (basically rtmp port 1935) and then moved onto testing the video streaming app and then tried a proper live broadcast through the server using the simpleBroadcaster app to two friends who were logged on using the simple subscriber app. All worked well especially as far as the video was concerned though the audio was a little crackly on my live broadcast - this could just be the quality of home connections however.The only last not is just a simple reminder that when running the test apps you'll need to specify the server they need to connect to - the apps are setup to access rtmp://localhost/[APP_NAME] but if you're not running these apps as a local user you will need to change localhost to the name or ip of the server Red5 is installed on

So far I'm quite happy with Red5 as a free (very important) alternative to FMS2, but should you be looking for a commercial product as this is not quite up your street (that is a cheaper than FMS2) then take a look at this instead: http://www.wowzamedia.com/index.html

Wowza only requires java to run and is like a very polished commercial version of the Red5 server (it was actually through installing Wowza that I worked out what to do with the web apps for Red5 - having had no previous experience in flash streaming other than literally installing FMS2) which has a very reasonable price tag in comparison to the cost of FMS2

Just before I go I just need to mention the map below - Red5 ask you to add yourself to their user map if you're using the software so I have - you can find me in Brighton, England