Come and speak at Asia’s largest Open Source Conference, OSI Days. We invite you to come and lead a tutorial / session or participate in Panel Discussions at OSI Days 2010. The last date is 15th June 2010.

Submit a proposal now!

If you are one of the following:

• Open Source Enthusiast
• Developer/ Hacker/ Techie/ Geek
• IT Manager/ C XO
• Open Source Entrepreneur
• Designer/ UI Expert
• Open Source Trainer/ Educationist

We invite you to submit a proposal on one of the topics below:

• Mobile: App Development, Game Development, Android, iPhone, Symbian & Others
• IT Managers / Business: Legal, Community Management, Best Practices, Marketing Strategies, Open Web / Standardization, Business Models
• Cloud Computing: Tools and Platforms, Cloudnomics, …

Come and speak at Asia’s largest Open Source Conference, OSI Days. We invite you to come and lead a tutorial / session or participate in Panel Discussions at OSI Days 2010. The last date is 15th June 2010.

Submit a proposal now!

If you are one of the following:

• Open Source Enthusiast
• Developer/ Hacker/ Techie/ Geek
• IT Manager/ C XO
• Open Source Entrepreneur
• Designer/ UI Expert
• Open Source Trainer/ Educationist

We invite you to submit a proposal on one of the topics below:

• Mobile: App Development, Game Development, Android, iPhone, Symbian & Others
• IT Managers / Business: Legal, Community Management, Best Practices, Marketing Strategies, Open Web / Standardization, Business Models
• Cloud Computing: Tools and Platforms, Cloudnomics, …

If you’re new to the MySQL DBA role, you’ll be excited to learn about the Maatkit toolset.  It provides a whole host of valuable functionality and fills many of the DBAs day-to-day needs.

DatabaseJournal – Wonders of Maatkit

Andrew just published an update to his Clusters from Scratch documentation that contains a step-by-step guide for initially setting up DRBD based cluster resources on Fedora 13.

Yes, that’s the Fedora release with Linux 2.6.33, so no more installing DRBD kernel modules.

…

• Make sure that nothing can access it
  (take it off the load balancer).
• in mysql client do: STOP SLAVE;
• in mysql client do: FLUSH TABLES;
  (if it gets stuck here, then you might need to fix it. You can try UNLOCK TABLES.)
• in command line do: /etc/init.d/mysql stop

...do your what you need to do here...

• in command line do: /etc/init.d/mysql start
  (check for errors, sometimes in my.cnf)
• in mysql client do: START SLAVE;
  (normally this would be done automatically when you restart mysql)
• in mysql client do: SHOW SLAVE STATUS\G
  (check that replication is working properly)
• After you are satisfied, set up the slave server back on the load balancer

The problem I am going to describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries, it might easily go unnoticed. I used it as one of the examples in our talk given at phpDay.it conference last week to demonstrate some pitfalls one may hit when designing schemas and queries, but then I thought it could be a good idea to publish this on the blog as well.

To demonstrate the issue let’s use a typical example – a sales query. Our data is a tiny store directory consisting of three very simple tables:

PLAIN TEXT SQL:

1. CREATE TABLE `products` (
2.   `prd_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.   `prd_name` varchar(32) NOT NULL,
4.   PRIMARY KEY (`prd_id`),
5.   KEY …

The implementation of MySQL Monitoring is critical for any organization that uses a database and wants to avoid the inevitable disaster. There are 3 important components that all serve a key purpose to “MySQL Monitoring” in general:

• Monitoring – Historical and graphical information
• Alerting – Tell me when something is wrong
• Dashboard – The State of NOW

Monitoring

There is no one option for Monitoring that is significantly better then another. A short list of what’s on offer can be found at http://monitoring-mysql.com/monitoring-products. What’s important is you have monitoring in place so historically you can review situations and compare across your servers and enabling the better identification of physical or database bottlenecks. My recommendations for products are …

We have secured approval for our final two speakers and now have a full schedule for the 4 day MySQL track at ODTUG Kaleidoscope conference. The conference is in Washington DC from Monday June 28th to Thursday July 1st. Welcome to Josh Sled and Craig Sylvester that will be joining our existing list of speakers.

This conference will include 19 sessions of dedicated MySQL content from Monday thru Thursday by well qualified MySQL community members, as well a forums discussion and reception on Monday night. You don’t need to be an Oracle developer to get the benefit of this conference. We will offering a discount code for MySQL attendees in the upcoming days.

If you are in the DC area, the Monday night forum (known as the sundown sessions) as well as the reception are FREE for the MySQL community. This was a great jesture of the Oracle …

I used to warn people with few details and lots of hand waving that LOAD DATA INFILE should not be run on important MySQL servers in production. I spent a few hours today crashing slaves during LOAD DATA INFILE and now have a much better warning. It should be OK to use LOAD DATA INFILE when the slaves use rpl_transaction_enabled, the loaded table has a primary key and the load doesn't run for a long time.

Replication isn't crash proof on a MySQL slave even when InnoDB is used. I have written about this before and rpl_transaction_enabled fixes the problem for InnoDB. The MySQL docs have a brief warning about the problem and a few other warnings are …

At a customer we had a nice example of how the MySQL Optimizer is cheating when used in combination with the MySQL Cluster. The customer had queries running not too slow in the development environment but when he tried them on the acceptance test environment (with more data) the query was running much too long which was unacceptable because this query can occur many times per second.

What has happened?

First of all we had a look at the execution plan of the query generated by the MySQL Optimizer:

EXPLAIN
SELECT t0.*, t1.*
  FROM t2
  JOIN t0 ON t2.t0_id = t0.id
  JOIN t1 ON t1.t0_id = t0.id
 WHERE t2.productnumber LIKE '%3301'
   AND t0.organization_id = 157
   AND t0.type = 'User';

1 row in set (8.78 sec)

+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+
| table | type   | possible_keys       | key           | key_len | ref      | rows | Extra …