If you enable logging of all queries as "slow queries" using the patch or MySQL 5.1 you can get log file to grow huge. Same may happen with general log file. In some cases we've got log file sizes of 100G or more which may need to be cleaned up.

Here is some danger waiting for you at least on typical Linux systems - If you follow most simple process - delete log file you do not need and run "FLUSH LOGS" so file is recreated and space reclaimed you can get into serious trouble. File close operation will when perform deletion which can be quite a long process, depending on filesystem and log file size - we've seen it to take 10 minutes or more in some cases. When log file is reopened MySQL will be practically unavailable causing unanticipated downtime.

The workaround for this problem is very simple - instead of deleting …

The review of my first six months of service with (mt) Media Temple is posted at Web Hosting Talk.

I was looking for a function in the MySQL documentation on how to get the first day of the month and I couldn't find one. Although I found that there was a LAST_DAY function. So, here's my function to get the first day of the month.

select DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 30 day), interval 1 day);

This is how I computed the first day of the month. I'm not sure if there is a simpler way of doing this.

If you are migrating your production environment to MySQL database or even thinking about it, you do not want to miss this webinar Experts from Zmanda and MySQL will present various methods to backup MySQL database and correlate them with tools and terminology you may be familiar with in other databases, including Oracle.

A good pre-read for this webinar would be Paddy’s blog on online MySQL Backup

WHO:

Chander Kant, CEO, Zmanda

Robin Schumacher, Director Product Management, MySQL

WHAT:

MySQL Backup Techniques for the Oracle DBA (web presentation)

WHEN:

Thursday, December 13, 2007, 10:00 am PDT, 1:00 pm EDT, 18:00 GMT
The presentation will be approximately 45 minutes long followed by Q&A.

What do you do when your database is falling behind?

1. Optimize the queries that are running on your database.
2. Try to partition your data so extensive I/O database will be distributed evenly.

If both approach don't work, you have one option left and that is to migrate your database to a new hardware. Here are pointers on what you have to do.

1. Backup your previous data.
2. Make sure that the new hardware configuration is identical with the old hardware.
3. Prepare for a downtime meaning you have to shutdown your database.
4. Copy your data to the new hardware.
5. Test your data in the new hardware.

Sheeri wrote a post (now a 404 error) referring to Maatkit on Ohloh, which I have never heard of before. I took a look at what Ohloh thinks about Maatkit. It's kind of neat. Beyond just the obvious "social website" stuff that's all the rage these days, it actually looks at the project's SVN history, analyzes the codebase, and so on.

It also estimates 8 person-years of work have gone into the project, and says that at $55,000/year it would cost $450,702 to write the code as it currently exists, which is kind of funny. It took me a whole lot less than 8 years to write. (Perhaps this is why that salary strikes me as unrealistic).

It has a couple of other interesting things, like a visual timeline of source …

Mark Atwood has published an interesting post with his wishlist for MySQL. One of his points is already (partly) covered with the LOAD XML command I made for MySQL 6.0 (or is it 5.2).

The main problem with it is perhaps that it works (like the LOAD DATA command) only with files, but in a web world you want it to be able to take input from other sources. You also need to be able to send an XML to several tables, with foreign keys coordinated etc.

Mark also has a wishlist for the opposite, the SELECT OUTFILE command. I have made a couple of stored procedures to help with this, but an extended SELECT OUTFILE would be mucke aesier to use and give much more possibilities. It would be fun to work on this, we'll see if I have the time, now that I am almost ready with my relocation..

There Ain't No Such Thing As A Free Lunch.
The price of a product, is only part of it's value. While not paying for the server itself, you end up paying more in development costs, as expected. It all makes perfect sense, and I'm certain everyone reading these words understands this without me explaining the obvious.

What I do intend to mention, are the specific shortcomings and limitations of MySQL, mostly compared to SQL Server - as this is the background I'm coming from.
Let's divide this to development tools, the database itself, and other stuff.

Development Tools
MySQL AB provides: MySQL Administrator, Query Browser and now the new MySQL Workbench.
There is also a myriad of 3rd party development tools for MySQL, but after getting used to the comforts of SQL Server's Management Studio, I can't say that any of them is truly …

Download Maatkit

Thanks again to all the great sponsors for my week of work on the kit!

This is the long-awaited "Baron worked on table sync" release. Hooray!

Please read the full blog post for important (very important!) information.