We often encounter customers who have partitioned their applications among a number of databases within the same instance of MySQL (think application service providers who have a separate database per customer organization ... or wordpress-mu type of apps). For example, take the following single MySQL instance with multiple (identical) databases:

SHOW DATABASES;
+----------+
| Database |
+----------+
| db1      |
| db2      |
| db3      |
| db4      |
| mysql    |
+----------+

Separating the data in this manner is a great setup for being able to scale by simply migrating a subset of the databases to a different physical host when the existing host begins to get overloaded. But MySQL doesn't allow us to examine statistics on a per-database basis.

Enter Maatkit.

There is an often-ignored gem in Maatkit's mk-query-digest, and that is the --group-by argument. This can …

It is important that you monitor the MySQL error log. There are a few different options available for defining the details of the log. If not specified the default is [datadir]/[hostname].err. This is not an ideal location for 2 reasons.

First, a correctly configured MySQL datadir will have permissions only for the mysql user, and this is generally restrictive access to the user only and no group or world permissions. Other users/groups should have limited access to the mysql error log.

Second, the datadir is for data, not logs, especially logs that can potentially fill file systems. I am referring here to more then just the error log.

I would recommend you create a separate directory for MySQL logs such as the error, slow and general logs. An example I implement for single installation environments using Linux mysql packages is:

mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql
chmod 750 /var/log/mysql

This Friday (Sept 18th 2009) will be my last day at Sun. I found an opportunity that I could not refuse. It been great working at Sun for the past 10 years. I have nothing but great things to say about Sun (and PAE - the organization I worked for). In case you need to reach me, you can try emailing me at realneel at gmaildotcom or on Linkedin

I will continue blogging on my personal site at http://realneel.blogspot.com. Hope to see you there.

This Friday (Sept 18th 2009) will be my last day at Sun. I found an opportunity that I could not refuse. It been great working at Sun for the past 10 years. I have nothing but great things to say about Sun (and PAE - the organization I worked for). In case you need to reach me, you can try emailing me at realneel at gmaildotcom or on Linkedin

I will continue blogging on my personal site at http://realneel.blogspot.com. Hope to see you there.

MySQL/Sun have releases a new white paper…

Do you want to ensure that your Authentication, Authorization and Accounting (AAA) infrastructure will scale to support your business growth? As network use grows and services become more dynamic, limitations can occur which add administrative overhead, inhibit flexible scaling and impact the timely synchronization of data across the AAA environment. To address these challenges, Sun has collaborated with the FreeRADIUS server team, the most widely deployed RADIUS server in the world, to integrate the carrier-grade, real-time MySQL Cluster database with the FreeRADIUS Server. Delivering Scalable Authentication Services: Get the whitepaper now! Attend Webinar Download our free whitepaper “Delivering Scalable and Highly Available Authentication, Authorization and Accounting Services” now to better understand: • The concepts of current data storage solutions for AAA environments and their …

Most folks do not know about Jackalopes. They are rabbits, usually Cotton Tails, with antlers from some sort of deer. The one pictured is from the Austin Museum of the Weird. Jackalopes, like unicorns, are fabled creatures may be based on real creatures with injuries, growths, or encounters with taxidermists with too much spare time. What does that have to do with MySQL Certification you ask?

I updating the MySQL Cluster DBA certification exam from the 5.1 to 7.0/7.1 product level. The current exam has not kept pace with the product and the question bank needs updates. The question bank has roughly 180 questions of which we use 130. The extra 50 are the questions that were either …

If you happen to be registered with VMworld and missed the MySQL talk here is the replay
EA3408 MySQL on VMware: Performance and Deployment Best Practices
http://www.vmworld.com/docs/DOC-3615

One to the interesting questions that came up near the end of the talk was related to use of memory reservations for VMs running MySQL. Interested in feedback related to real world experience. One audience member found it useful in their environment.

When a data node fails, the other data nodes must perform clean-up of the failed node.

E.g, to abort transactions in the PREPARE phase (Cluster uses a variant of the Two Phase Commit protocol) that has been started on the Data node that fails.
Various protocols (if the failed data node is the master) must also fail-over.

These things are carried out quite swiftly (configurable, but a couple of seconds by default but it also depends on the size of transactions that in the prepare phase that needs to be aborted).

I wanted to check how long time it takes to do node failure handling with many tables (16000+) in the cluster, because in a "normal" database with a couple of hundred tables, then the time spent on node failure handling is not a factor.

In this case I have two data nodes:

…

I hear fairly often that consulting firms and systems integrators are more likely to use open source tools that IT because it allows them to be more competitive. They gain an edge by saving customers money on software licenses, or by having more customizable tools for projects, thus pricing themselves under competitors or providing a better fit with client needs. The other hope is that by freeing project budget from the software licenses, this could translate into more money spent on work with the consultants.

While these points are all valid, the survey data on adoption seems to disprove the belief. An interesting pattern in the data is that consultants are generally less likely than IT professionals to use open source tools in this space (10% for consultants versus 36% for IT). The usage by respondent role is shown in the chart.

It is notable that 49% of the consultants and …

(EDIT: this article was published with a ‘part 1′ suffix in the title.  There will not be a’ part 2′. Read comments and you will understand why)

SELECT * FROM `first`;
/* returns
a
——
1
2
3
*/

CREATE TABLE IF NOT EXISTS `second`(`a` INT) SELECT * FROM `third`;

SELECT * FROM `first`;
/* returns
a
——
1
2
3
7
8
9
*/

Think about it!  Of course some special conditions will need to exist for this to be reproducible.

More details will appear here in a couple of days.