Technical Digital Media Consultant
Posts tagged MySQL
MySQL Seconds Behind Master Weirdness
0
So, I'm running show slave status and I'm seeing that my slave is around 14 days behind my master, which is weird since I the server's set to expire logs after 3 days.
After a bit of digging I found this: http://bugs.mysql.com/bug.php?id=2826
So I checked the system time on the slave and it was way out of whack with the current date.
So, I restarted ntpd (which causes it to re-sync), stopped and restarted the slave and there we go problem fixed.
Random fact of the day
0If you use an unsigned MySQL int to count seconds you can count up to roughly 136 years before the field is full 
On another note I also got my Google Wave invite today, so if anyone wants to connect just add chrisjohndean@googlewave.com to your list!
Setting Up Ubuntu PHP 5.2, MySQL 5.1 and Apache 2 on a PS3
0Recently I wanted a new development box/streaming media server for use around the house and for work.
After looking at the prices of all the main small form factor PC's or NAS boxes that would let me do this I could see that I was easily looking at £200-£250 minimum for the kind of setup I wanted to put together.
At the same time, I happened to notice that Sony actually support the use of Ubuntu as an alternative OS on the playstation 3 (or possibly Ubuntu Support the PS3 as an installation platform - whichever, it doesn't matter, the point is that some one does and it works!).
Now, an 80Gb PS3 is about £290 at the time of writing and has oodles of inbuilt support as a streaming media client, media storage and playback and a blue-ray player, as well as giving me the option to install a fully functional version of linux that will allow me to do whatever the hell I liked (RAM permitting).
So last weekend I bought one and, aside from playing games, I have just finished setting up Ubuntu 9.04 on it and I'm actually blogging this via firefox on the PS3 - woohoo 
So what did I have to do to get this working?
Well the first thing to remember, if you've had a PS3 for a while and want to keep all your game saves, downloads, etc. is to back them up! There's an easy tool to do this in the system settings menu and I found having an external USB HDD rather than a memory stick to hand was quite useful for this. Also you might want to sync your trophy collection with the Playstation Network Servers if you're interested in keeping that kinda stuff.
Once you've done your backups, you'll need to set aside a partition for the new OS to use.
To do this, oddly enough you have to use the format disk utility from system menu, as this where the partition management is also done. Basically you select the option and you'll get the choice of either giving the Guest OS 10 GB, and the PS3 the rest of the space, or the PS3 10GB and the Guest OS the rest of the space.
I chose to give the guest OS 10Gb and keep the PS3 holding the bulk of the space as I can see myself downloading a fair few games from the playstation network over the next couple of years. Also though this 10GB default setting isn't massive, for my needs, as a system partition it's fine since I intend to run a 1TB data tank off the back of the PS3 to hold any other media.
Anyway, what seems to happen when you do this is that the PS3 re-formats the part of the disk allocated for all non-essential user data i.e. downloads and game saves and frees up a 10Gb chunk for the new OS.
At this point I'm pretty sure you get a prompt to insert the installation media for the new OS (if you don't just click through the options relating to guest OS's and it will pop up somewhere), so you then need to get (in my case as I'm using Ubuntu) the Ubuntu installation image for powerPC and PS3 and burn it to a CD. You can get Jaunty (9.04) here
Once you have the CD in hand, simply insert it into the PS3 and the install process will start as normal with any linux distro, allowing you to partition your allocated space and setup any basic system options - note however that the install may take quite a while due to the processing speed and allocated RAM of the PS3 - that said once installed Ubuntu seems to run pretty smoothly.
To get the linux side working you need to chose the default OS to use from the system settings menu i.e. PS3 or 'Guest OS' choosing the latter will fire up Ubuntu as normal on any standard PC. The PS3 will then always boot into Ubuntu unless you type 'game' at the boot prompt, which will reset the default OS to being the PS3's own OS.
Once Ubuntu has loaded all you need to do to install Apache, PHP and MySQL, is open up a terminal and type the following (Note: I'd install MySQL first as components of Apache and PHP seem to need the libs anyway):
$shell> sudo apt-get install mysql-server-5.1
$shell> sudo apt-get install apache2
$shell> sudo apt-get install php5
Note: for some reason the MySQL setup also requires you to setup postfix, I'm not sure why but I did this anyway as it prompted me to and having postfix active on this box is no bad thing really.
The last thing to do was to boot back into the PS3 and restore the backup up game files and resync the trophies and all is done...
So that's it. I now have a fancy pants gaming system, blue ray player, media server (when I setup twonky media) and a development box all for under £300
In all, if you've installed linux before and are not afraid to click around the system settings of your PS3 the whole process is pretty straightforwards and self explanatory. That said, before I actually went the whole hog I did do a little bit of research and found the following sites which was quite handy to start with. The comments on some of the articles aren't great, and some are dated now, as doing what I've outlined above has seemed to work fine for me:
https://help.ubuntu.com/community/PlayStation_3
http://psubuntu.com/wiki/UbuntuVersions?show_comments=1#comments
MySQL 5.1 partitioning and loosing all your databases (or not)!
0I 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://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
MySQL 5.1 multiple tmpdirs and incorrect key file for table errors
4Well I found out a few new things today...
Firstly if you see an error similar to this:
Incorrect key file for table '/tmp/#sql_913_4.MYI'; try to repair it
It's likely you've got a dodgy query that is creating a temp table so big it's maxing out all your available temp filesystem space.
Secondly if you try to get round this by throwing disk space at the issue then you'll come unstuck.
Queries that create a self-join cartesian product, and contain ORDER BY clauses, cause the server to materialize the entire result set into a temporary table before applying any limits. If you try to get round this by giving the server a larger tmp dir to expand into, then you'll probably watch your disk fill up very quickly, as these can chew up literally tens of gigs of space or worse.
(by the way for reference, i'm paraphrasing Mark Matthews comment at the end of this: http://bugs.mysql.com/bug.php?id=10075 and using experience from my own issue, similar results my be found with other queries that don't manage the data they're pulling back well)
Thirdly if you're trying to use mysql 5.1's new multiple tmpdir's option in the my.cnf file to provide that extra disk space you need to do it like this:
tmpdir = /tmp:/tmp2:/tmp3
Not like this:
tmpdir = /tmp/:/tmp2/:/tmp3/
As for some reason the mysql server doesn't like the trailing slash on the dir name and ignores the lot if you do it the latter way.
So there you go - I learned something new today, and hopefully, so have you!