Some so-called “Green” harddisks don’t like being in a RAID array. These are primarily SATA drives, and they gain their green credentials by being able reduce their RPM when not in use, as well as other aggressive power management trickery. That’s all cool and in a way desirable – we want our hardware to use less power whenever possible! – but the time it takes some drives to “wake up” again is longer than a RAID setup is willing to tolerate.

First of all, you may wonder why I bother with SATA disks at all for RAID. I’ve written about this before, but they simply deliver plenty for much less money. Higher RPM doesn’t necessarily help you for a db-related (random access) workload, and for tasks like backups which do have a lot of speed may not be a primary concern. SATA disks have a shorter command queue than SAS, so that means they might need to seek more – however a smart RAID controller would already arrange its I/O …

MySQL isn’t too concerned about table handler memory usage – it will allocate row size buffer thrice per each table invocation. There’s a few year old bug discussing UNION memory usage – for each mention in an union one can allocate nearly 200k of unaccounted memory – so a megabyte sized query can consume 7GB of RAM already.

Partitioning though adds even more pain here – it will allocate those three buffers per each partition, so opening a table with 1000 partitions looks like this on memory profile:

Click to enlarge, and you will see 191MB sent to execute a simple single-row fetching query from a table (I filed a bug on this).

There’re multiple real …

A special extended edition of Tech Messages for 2011-08-31 through 2011-09-21:

• Lab Overview
  This guy is serious about his lab!
• Throughput
  All sorts of useful information here. My favorite is the first table which includes bandwidth and packet throughput for different kids of circuits.
• Must see: 5 Steps to Scaling MongoDB (Or Any DB) in 8 Minutes
  These tips are relevant no matter what database system you are using.
• Understanding and …

If I were to ask you 'what are the (database) metrics?', what do you think I'm asking? Thats an interesting question and something that requires some thought so lets handle this by actually looking at the following two alternative questions. 'What am I trying to archive [with database metrics]?' and 'how will I know when I have achieved it [by having database metrics]'?

Define the problem

What are we really doing
What are we trying to achieve?

• Know how much capacity we have
• Know that our databases are fast

How do we know when we have achieved it?

• When we can forward plan upgrades when we are near a capacity limit
• To be alerted of when things are not fast we can we investigate

So, what is capacity

Capacity is knowing how much extra we can do without making things 'slow'. Its a …

On several occasions it may be necessary to do some tasks around the process of starting or stopping a MySQL server such as:

• Perform a warmup of the database using custom scripts and external information that the init_file configuration may not have access to.
• Change configuration settings such as innodb_log_file_size and adjust the on disk files that this needs.
• Change configuration settings such as log_bin (I try to use a relative paths to ensure that different instances use a common setting even if datadir is different, and changing from an absolute to relative path often requires adjusting the binlog index file).
• Shutting down MySQL may need to be delayed if the SQL thread has open temporary tables or open transactions are still being processed.
• etc…

You may have others and many of these are likely to be site …

Percona is glad to announce the release of Percona XtraBackup 1.6.3 on 22 September, 2011 (Downloads are available here and from the Percona Software Repositories).

This release is purely composed of bug fixes and is the current stable release of Percona Xtrabackup.

If the innodb_file_per_table server option is being used and DDL operations, TRUNCATE TABLE, DROP/CREATE the_same_table or ALTER statements on InnoDB tables are executed while taking a backup, an upgrade to XtraBackup 1.6.3 is strongly recommended. Under this scenario, if the server version is prior to 5.5.11 in 5.5 series or prior to 5.1.49 in 5.1 series, a server upgrade is …

Howdy all,

Just a quick update in the world of XtraBackup Manager development. In the last couple of weeks I have not been doing a great deal of work on XtraBackup Manager itself, but rather doing a lot of testing of XtraBackup Manager and implicitly XtraBackup along with it.

I hit upon some bugs that were basically roadblocks in the way that we intend to use XtraBackup and I'm sure issues that other folks will run into eventually once adoption of XtraBackup increases even more...

I have been working with Percona and SkySQL Support, as well as dabbling in some of the code myself to get fixes for these issues.

The main issues we hit were:

* tar4ibd crashes on certain InnoDB data files (unable to use streaming backups at all) - This was a regression in pre-release build of xtrabackup-1.6.3, For now "fixed" by using an older tar4ibd binary from stable 1.6.2 release. …

A was checking out the new Workbench today after I saw this blog post http://wb.mysql.com/?p=1169

It all worked great and very easy.  Nice clean code that helps out the PHP Developer.
example:
$host="localhost";
$port=3306;
$socket="";
$user="";
$password="";
$dbname="";

$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
    or die ('Could not connect to the database server' . mysqli_connect_error());

//$con->close();


$query = "SELECT * FROM exampledb";


if ($stmt = $con->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($field1, $field2);
    while ($stmt->fetch()) {
        …

Join us for Sunday Sessions organized by IOUG/MySQL Community at Moscone West, Levels 2 Lobby. Sunday 9:00 a.m. - 5:00 p.m. Don’t miss these talks by Giuseppe Maxia and Robert Hodges:Sunday, 9:00 -10:00 am Don't Be a Rookie Forever—Be in Command (Line)Sunday, 10:15 -11:15 am Jailbreaking MySQL Replication. It will a showcase of Tungsten Replicator, with quick examples of how to build replication

In a month, the 24th of October, Johan Andersson (severalnines.com) and I will be giving a full day tutorial on NDB cluster which will include both presentations and hands-on. Be ready for a fast ramp-up on NDB! Among items covered:

- Achitecture
- Installation
- Loading data
- Administration (common procedures)
- Node recovery
- NDB for sharding
- Replication
- Online scaling
- Other access methods

If NDB cluster is a technology that interests you and you would like to learn more, this tutorial is for you. Make sure to check out our website http://www.percona.com/live/london-2011/schedule-tutorial/ to get our full list of …