In my experienceALTER TABLEis one of the more maligned and misunderstood features of MySQL. It has a lot of potential to cause unexpected problems if you don't understand it. Here are a few common gotchas when using ALTER TABLE to modify a column attribute:

1) Alter Table is slow

For those of us who spend a lot of time working with MySQL this is expected behavior. Nonetheless, many people are still surprised when they're doing something simple like disallowing NULL values in a column, and it takes hours to run on a table with several GB of data. The reason for this is that most column changes affect the row format, and that requires rebuilding all of the rows for the entire table.

Here are some recommendations to mitigate this issue:

• Use master-master active/passive replication so you can always execute DDL on a passive DB
• Use …

In my experienceALTER TABLEis one of the more maligned and misunderstood features of MySQL. It has a lot of potential to cause unexpected problems if you don't understand it. Here are a few common gotchas when using ALTER TABLE to modify a column attribute:

1) Alter Table is slow

For those of us who spend a lot of time working with MySQL this is expected behavior. Nonetheless, many people are still surprised when they're doing something simple like disallowing NULL values in a column, and it takes hours to run on a table with several GB of data. The reason for this is that most column changes affect the row format, and that requires rebuilding all of the rows for the entire table.

Here are some recommendations to mitigate this issue:

• Use master-master active/passive replication so you can always execute DDL on a passive DB
• Use …

Sharing knowledge is not only beneficial to others, it also enhances one’s own knowledge and broadens the spectrum. Blogging is all about sharing and this Log Buffer Edition shares some great sharing of our bloggers around the database arena in Log Buffer #276. Oracle: Sometimes it is possible to build a very effective test case [...]

I have used the technique that I am going to describe below on several occasions to restore from a SQL dump generated using mysqldump. In some cases, the restore that had taken 2 days and still could not finish successfully was completed in 5-6 hours using this simple technique. Please keep in mind that this is not a pure parallel method but I'd say it is almost parallel and have proven to be faster than regular single-threaded restore.

In a previous post I wrote ARM based servers. Since then, and thanks to all the comments and responses I got, I looked more into this ARM thing and it's absolutely fascinating...

Look at this beauty (taken from the site of Calxeda, the manufacturer):

What is it? A chip? A server? No, it's a cluster of 4 servers...

And this:

is HP Redstone Server, 288 chips, 1,152 cores (Calxeda quad-core SoC) in a 4U server “Dramatically reducing the cost and complexity of cabling and …

At a time when most companies are doing all they can to maintain the status quo, SkySQL is daring to imagine a new “normal”. We’re on a mission to change the way companies interact with and manage their data, both in the enterprise and in the cloud. Along with our strategic partners, in particular MariaDB, we’re working to develop new open source database solutions that challenge proprietary enterprise solutions and business models.

To help us achieve our goal, we’re looking for a few daring open source engineers to join our team and take on the status quo:

read more

I did a Webinar about MySQL Upgrade – Best Practices Yesterday and there were some questions we could not answer during Webinar, following Jay’s Lead I decided to post them as a Blog Post.

Q: Can you go directly MySQL 5.0 to 5.5 for MyISAM tables?
MyISAM have not been getting any significant development since MySQL 4.1, so in-place upgrade of MySQL 5.0 to MySQL 5.5 should be rather safe from data standpoint. There are still possibilities for sorting order and data type related changes, so you still need to run mysql_upgrade to check if any tables need to be rebuilt for new versions. Note this only applies to the data part of upgrade, you still need to keep into …

Read the original article at Accidental DBA’s Guide to MySQL Management

So you’ve been tasked with managing the MySQL databases in your environment, but you’re not sure where to start.  Here’s the quick & dirty guide. Oh yeah, and for those who love our stuff, take a look to your right. See that subscribe button? Grab our newsletter!

1. Installation

The “yum” tool is your friend.  If you’re using debian, you’ll use apt-get but it’s very similar. You can do a “yum list” to see what packages are available. We prefer to use the Percona distribution of MySQL.  It’s fully compatible with stock MySQL distribution, but usually a bit ahead in terms of tweak and fixes.  Also if you’re …

If you are like me, then you like to record all of your query traffic into the slow query log with long_query_time=0. Since I'm usually using Percona Server, I usually set log_slow_verbosity="microtime,query_plan,innodb,profiling' to get as much data as possible into the slow query log for analysis with pt-query-digest.

Generating a lot of data in the slow query log makes it more difficult to work with as it grows larger. It is important to rotate the log regularly to ensure it does not grow too large to work with. This can be accomplished with a cron job, but it can be done in MySQL directly using the event scheduler.

SET GLOBAL event_scheduler=ON;
DROP EVENT if exists rotate_slow_log_event;
CREATE DEFINER=root@localhost EVENT rotate_slow_log_event
ON SCHEDULE
EVERY 1 HOUR
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Rotate slow query log hourly'
DO
set global …

If you are like me, then you like to record all of your query traffic into the slow query log with long_query_time=0. Since I'm usually using Percona Server, I usually set log_slow_verbosity="microtime,query_plan,innodb,profiling' to get as much data as possible into the slow query log for analysis with pt-query-digest.

Generating a lot of data in the slow query log makes it more difficult to work with as it grows larger. It is important to rotate the log regularly to ensure it does not grow too large to work with. This can be accomplished with a cron job, but it can be done in MySQL directly using the event scheduler.

SET GLOBAL event_scheduler=ON;
DROP EVENT if exists rotate_slow_log_event;
CREATE DEFINER=root@localhost EVENT rotate_slow_log_event
ON SCHEDULE
EVERY 1 HOUR
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Rotate slow query log hourly'
DO
set global …