Oracle Open World is in full bloom. Enthusiasts of Oracle and MySQL are flocking to extract as much knowledge, news, and fun as possible. SQL Server aficionados are not far behind too.

Oracle:

Frank Nimphius have announced REST support for ADF BC feature on OOW today. Probably this functionality will be available in the next JDeveloper 12c update release.

RMAN Enhancements New Privilege A new SYSBACKUP privilege is created in Oracle 12c,  it allows the grantee to perform BACKUP and RECOVERY operations with RMAN SQL in RMAN.

To continue with the objective of separating duties and the least privileges, …

HAProxy is frequently used as a load-balancer in front of a Galera cluster. While diagnosing an issue with HAProxy configuration, I realized that logging doesn’t work out of the box on CentOS 6.5. Here is a simple recipe to fix the issue.

If you look at the top of /etc/haproxy/haproxy.cfg, you will see something like:

global
    log         127.0.0.1 local2
[...]

This means that HAProxy will send its messages to rsyslog on 127.0.0.1. But by default, rsyslog doesn’t listen on any address, hence the issue.

Let’s edit /etc/rsyslog.conf and uncomment these lines:

$ModLoad imudp
$UDPServerRun 514

This will make rsyslog listen on UDP port 514 for all IP addresses. Optionally you can limit to 127.0.0.1 by adding:

$UDPServerAddress …

Generated Columns is a new feature available in the latest lab release. This work is based on a contribution by Andrey Zhakov. Thanks, Andrey! The Optimizer team modified it to follow the current MySQL design, and to lift a number of limitations.

The syntax is:

<type>   [ GENERATED ALWAYS ]   AS   ( <expression> )   [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ]   [ [PRIMARY] KEY ]   [ NOT NULL ]   [ COMMENT <text> ]

There are two kinds of Generated Columns: virtual (default) and stored. Virtual means that the column will be calculated on the fly when a record is read from a table. Stored means that the column will be …

libAttachSQL Benchmarks With Sysbench

This week I have been spending a little bit of time creating a module for Sysbench so that it can use libAttachSQL as a database driver. The reason for doing this is twofold:

1. Brian (my boss at HP’s Advanced Technology Group) said now would be a good time to benchmark libAttachSQL
2. I really needed more than a few basic queries to shake out bugs in the library, and a benchmark is a good way to shove a few million through it

On the bug front, it did find a total of 5 bugs, a couple of them serious. Which is great, the more testing and bug finding the better. These have all been fixed in GitHub and will be part of the …

Thu, 2014-10-02 19:21maxmether

For certain situations, the static structure of tables in a relational database can be very limited. Each column is statically defined, has a pre-defined type and you can only enter a value of that type into the column.You can be creative and list multiple values in one column, but then those values are not generally easily accessed and manipulated with other functions. You have to use an API or contortions of a function like SUBSTRING() to pull out a value you want. Even then, you have to know what is contained in the column to be able to manipulate it properly. These methods can require too much manual intervention to assess and access the data contained in the column.

If you want to add columns as the information stored in your tables grows and your needs change you need to do fairly expensive ALTER TABLE operations. These have traditionally been very expensive in MySQL and MariaDB although the …

Thu, 2014-10-02 17:25kolbekegel

MySQL 5.6 introduces a new "multi-threaded slave" feature (aka "parallel replication") that turns the "slave SQL thread" into a number of distinct threads that each apply events to different schemas. This means that separate slave "worker threads" will be executing different positions in the slave's relay log (corresponding, of course, to different positions in the master's binary log) and that the Relay_Master_Log_File and Exec_Master_Log_Pos values in SHOW SLAVE STATUS output are no longer an accurate indicator of the current position of the slave. This causes severe problems if using Percona XtraBackup/innobackupex with the --slave-info option to get a backup that can be used to seed a new slave. Let's take a look at how multi-threaded slave works in MySQL 5.6, how innobackupex gets position information, how to recognize problems, …

Now it's time to release something useful! At least I hope so. I have been going through how I came up with this idea and how I came up with the implementation in a series of blog posts:

• Part 1
• Part 2
• Part 3
• Part 4 

 But now it's time for the real deal, the software itself. This is an Alpha 1.0 release but it should work OK in the more basic setups. It's available …

The InnoDB labs release includes a snapshot of the InnoDB Native Partitioning feature.

To better understand why we implemented this, we need to start with some background on tables, storage engines, and handlers. In MySQL an open instance of a table has a handler object as an interface to the table’s storage engine. For a partitioned table there is a main table handler that implements the partitioning feature, but for storage, each partition has its own handler. This worked fairly well, but the more partitions you had the more overhead from the per partition handlers. So to remove this overhead for partitioned InnoDB tables we’re introducing Native Partitioning support! This means a new InnoDB partitioning aware handler, so that we have a single handler object for a partitioned table and not one handler object per partition.

Let us create a simple table with 8k partitions:

[This post refers to innodb_ruby version 0.9.11 as of October 2, 2014.]

Many DBAs know that building indexes in “random” order (or really any order that greatly differs from ordered by key) can be much less efficient. However, it’s often hard to really understand why that is. With the “-illustrate” visualization modes available in innodb_ruby, it’s possible to quite easily visualize the structure of indexes. The space-lsn-age-illustrate mode to innodb_space allows visualization of all pages in a space file by “LSN age”, generating something like a heatmap of the space file based on how recently each page was modified.

(Note that a small Ruby script generate_data_simple.rb was used to generate the test tables used below. …

Have you donwloaded our latest eBook, Configuring MySQL for Performance? It offers simple advice on configuring the most important settings to maximize your MySQL performance.

Below are a couple tidbits:

The Default Storage Engine

The default_storage_engine setting is the engine used for tables that don’t specify an engine explicitly. This used to be MyISAM, but it’s changed to InnoDB more recently. This is a good thing; it’s the engine we’ve suggested as default for most use cases for years. Just check to ensure this is set as you desire. If you don’t know what engine to use, the right answer is probably InnoDB.

The Query Cache

The query cache (variables whose names begin query_cache ) should generally be disabled, unless you’re quite familiar with MySQL and its inner workings and know that it’ll help on your workload. You can find a lot of discussion online about the potential impacts …