My favorite metaphor for explaining indexes is comparing them to index cards in an old library. In an old library, you used to (or still do) have index cards at the front desk which have some brief description of the books in the library. They also used to be categorized alphabetically.

(image taken from http://www.flickr.com/photos/reedinglessons/2239767394/)

Let’s pretend that you are simulating an application that is trying to find a book with a certain title in the library.

Not using an index

If you are not using the index cards, you would have to go shelf by shelf and row by row, look at each book’s title and see if its the one you need. This is very time consuming and is similar to how a database looks at blocks on the hard disk when its not using an index.

Using an …

For those that missed it – we added training in Sydney to our website.

We’ve booked a training venue near the Museum of Contemporary Art (200 George Street). Some minor logistical changes:

• The start time will now be 9am to 5pm

We look forward to seeing you there!

Earlier this month I published an article on this blog describing the system of record approach to multi-master replication.  As mentioned in that article my colleagues and I at Continuent have been working on improving Tungsten to make system of record design patterns easier to implement.  This article describes how to set up system of record using Tungsten Replicator shard filters, which are a new feature in Tungsten 2.0.4.  By doing so we will create a multi-master configuration that avoids replication loops and transaction conflicts.  On top of that, it is quite easy to set up.

There are many possible system of record patterns depending on how many schemas are shared and across how many masters.  The following diagram …

We’re pleased to let you know that the 2nd episode of our “Meet The MySQL Experts” podcast series, where Oracle engineers share their expertise, is now available.

Wei-Chen Chiu interviews InnoDB team member Inaam Rana who comes back on the new InnoDB features and improvements delivered in MySQL 5.5 and in the first development milestone release of MySQL 5.6. Inaam also talks to us about the InnoDB features available in labs.mysql.com.

Enjoy!

We’re pleased to let you know that the 2nd episode of our “Meet The MySQL Experts” podcast series, where Oracle engineers share their expertise, is now available.

Wei-Chen Chiu interviews InnoDB team member Inaam Rana who comes back on the new InnoDB features and improvements delivered in MySQL 5.5 and in the first development milestone release of MySQL 5.6. Inaam also talks to us about the InnoDB features available in labs.mysql.com.

Enjoy!

MySQL Tuning

This is another section that is broader than one would first imagine.  There’s a reason that many large organizations employ dedicated database administrators.  That said, this doesn’t prevent the average sysadmin from making some changes to enhance performance on their database.

The easiest way to start on this is to utilize a script to automatically check your configuration options and make suggestions based on status variables MySQL sets.  I’ve had good luck with a script called mysqltuner.pl.  You can visit the project page at GitHub here: …

The 95 percentile for query response times is and old concept; Peter and Roland blogged about it in 2008. Since then, MySQL tools have calculated the 95 percentile by collecting all values, either exactly or approximately, and returning all_values[int(number_of_values * 0.95)] (that’s an extreme simplification). But recently I asked myself*: must we save all values? The answer is no. I created a new algorithm** for calculating the 95 percentile that is faster, more accurate, and saves only 100 values.***

Firstly, my basis of comparison is the 95 percentile algo used by …

In our last episode we looked at getting MySQL, today we will be looking at how you connect to a MySQL server and what that implies.

Connection basics

Before a client can connect to a MySQL server it needs a path by which that connection can be made. One method that is almost universal is the 'socket'.  As its name implies it is a way of plugging two (or more) applications together.  Sockets can either be end points for a network connection (for instance a TCP socket) or can use the same system-level functions but use a local connection.  This is sometimes called a UNIX socket, and relies on there being a special file that the two applications can use to initiate a connection.  MySQL can use both.

For a network connection you need a number of pieces of information.  As a connection …

If you support Oracle RDBMS 11.2.0.2 and want to zero downtime applying
patches to databases then it is time to have a look at new possibility
of Online Patching delivered with 11.2.0.2 version and described here
RDBMS Online Patching Aka Hot Patching [ID 761111.1]

The syntax is “opatch apply online -connectString … ” but I could not find description
of the online option (only -connectString) using opatch -help (version 11.2.0.1.6)
although based on the note it is available since 11.1.0.6 version of opatch

And while going through README for the patch I found the blog
about online patching …

“I needed a password eight characters long so I picked Snow White and the Seven Dwarfes.” — Nick Helm1

MySQL Account Management is often nebulous mess to new DBAs. Frank2 is a new MySQL with many years of Linux administration duties in his background and now he has been taking on more duties after his companies previous DBA departed. One of his co-workers was in the middle of a name change and could no longer access the need instance. Frank had run

mysql -u root mysql
mysql>UPDATE user SET Name='jjones' where Name='jsmith';

But Mrs. Jones was not able to access the database. Frank tried for a while to find the answer. To greatly simplify the MySQL authorization process, the sever checks the Host of a new connection, then the User and Password. Frank had checked all three. The name of the host Mrs. Jones used had not changed. Neither …