MySQL Installer 5.5 (5.5.13 beta, published on Thursday, 07 Jul 2011)
PlanetMySQL Voting: Vote UP / Vote DOWN

It’s the scorching summer season in this part of the world, Pakistan. There are many summer woes as the temperature reaches to 45 centigrade and beyond, but that also brings in the most savored fruit of the world, the king of fruits, Mango. Pakistan is one of the largest producer and exporter of this fruit from paradise. There are many variates of mangoes and its hard to pick from them as they all are so sweet and eye catching. Same is the case with the database related blogs, when it comes to selection for the edition of Log Buffer. So once again here you are, about to savor the sweet blogs in Log Buffer #224.

Oracle:

Automatic Diagnostic Repository (ADR) has changed the way we think about logs and traces in Oracle. Uwe Hesse dishes out a survival guide for the DBA about the cli tool adrci, which is used to manage ADR.

I am big fan of the team which decides and design the logs for Google main page. On Les Paul’s 96th Birthday, Google main page provided a live piano, and I don’t have words to describe its beauty and joy. Kerry Osborne, though tries to describe it.

Randolf Geist informs that in recent versions Oracle changes the costs of a full table scan (FTS or index fast full scan / IFFS) quite dramatically if the “flashback query” clause gets used.

Jonathan Lewis writes a quick tutorial about how to use the hints.

Tim Hall carries on his weight reduction adventure, which is the dream of many.

SQL Server:

Mark Broadbent helps us understanding as how to add meta to our audio training files.

Denny Cherry recaps the Microsoft’s Tech Ed 2011 in his recent blog post.

Stuart Ainsworth busts three myths about the so celebrated Agile Development.

Have you been thinking about getting certified?  Are you close on finishing your studying but aren’t quite sure if you are ready?  If so, then check the new blog post by Jason Strate.

Melissa Coates blogs about Nested Conditional Operators in an SSIS Derived Column.

MySQL:

NDB cluster is a very interesting solution in term of high availability since there are no single point of failure. In an environment like EC2, where a node can disappear almost without notice, one would think that it is a good fit. Hmmm, perhaps not so. Yves Trudeau blogs.

Webyog says that he doesn’t need to emphasize how productive you can be if you find what you are looking for in a quick and easy way. Google has shown it to us. Wouldn’t it be fun to have the same ease and speed while searching for data in your databases?

Baron Schwartz (xaprb) introduces a pocket-sized guide to setting up and managing MySQL replication.

Nocholas Goodman is sharing an example on how we can enable easy reporting on top of BIg Data immediately for CouchDB users.

Today, more and more proprietary software vendors are choosing to go Open Source. Doing this enables them to leverage the community benefits of Open Source, shorten the sales cycle, and gain a competitive advantage over other proprietary products. Sandro Groganz blogs about choosing the right Open Source Product Architecture.

Happy Summer and Enjoy the Mangoes!!!

PlanetMySQL Voting: Vote UP / Vote DOWN

After the success of Percona Live in New York, we have published in our website the presentation slides of talks from the leading experts in MySQL. Check it out.

PlanetMySQL Voting: Vote UP / Vote DOWN

Percona is glad to announce the release of Percona Server 5.5.12-20.3 on June 9, 2011 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.5.12, Percona Server 5.5.12-20.3 is now the current stable release in the 5.5 series.

Bug Fixes

• Fixed InnoDB I/O code so that the interrupted system calls are restarted if they are interrupted by a signal. InnoDB I/O code was not fully conforming to the standard on POSIX systems, causing a crash with an assertion failure when receiving a signal on pwrite(). Bug Fixed: LP #764395 / MySQL bug #60788 (A. Kopytov)

• Fixed InnoDB handler code to make sure that that the target engine is InnoDB instead of the original when converting a table with ALTER TABLE. The problem was in the Fast Index Creation patch, for ALTER TABLE ENGINE=… the code mistakenly made sure the original table’s engine is InnoDB, rather than the target’s one. This resulted in a failure if the target engine was MEMORY because it does not support certain handler calls. Bug Fixed: #757749 (A. Kopytov)

• Fixed memory leaks in XtraDB initialization. Bug Fixed: 791849 (L.Biveinis)

• In some cases records were not purged during shutdown, even when a full purge is requested with innodb_fast_shutdown=0. Bug Fixed: #756387 (Y. Kinoshita)

Other Changes

• The list of authors of the plugins used have been corrected. Bug Fixes:#723050 (Y. Kinoshita)

For more information, please see the following links:

• Downloads: Binary distribution, Percona Software Repositories

• Installation instructions

• Consulting, support, training, and custom development

• Release notes

• Documentation

• Developer discussion group

 

PlanetMySQL Voting: Vote UP / Vote DOWN

Open Ocean's new Fund celebrated with pitching competition - I'll give the winner Black vodka as much as he/she can carry (inside and outside)!

I am delighted that we now have been able to launch our new Open Ocean Fund. With 40 M euro (60M USD) in first closing, the race is now on to identify new disruptive start ups, that we with Community and Open Source strategy and proper funding, can help drive for similar success as achieved with MySQL.

In my mind a vital part of entrepreneurship is to be able to combine work with fun!

For this reason we decided to launch a pitching competition, where we bring the winning team to Finland, both for good worktime with our team, but also for some social time and nice summer activities.

On top of the boat trip and crayfish party already mentioned in the news item, I add here an additional personal price. During the evening party I will offer our guests with a chance to taste some (or a lot) of one of my personal favorites: The Finnish Black Vodka. And, the next morning, as a gift to take home, I will provide the winner with Black Vodka - as much as he/she can carry - to take home as a souvenir. Something that should help to remember the trip, and something enabling the entrepreneur(s) to share the fun with family, friends and colleagues!
PlanetMySQL Voting: Vote UP / Vote DOWN

NDB cluster is a very interesting solution in term of high availability since there are no single point of failure. In an environment like EC2, where a node can disappear almost without notice, one would think that it is a good fit.

It is indeed a good fit but reality is a bit trickier. The main issue we faced is that IPs are dynamic in EC2 so when an instance restarts, it gets a new IP. What the problem with a new IP? Just change the IP in the cluster config and perform an rolling restart! no? In fact this will not work, since the cluster is already in degraded mode, restarting the surviving node of the degraded node group (NoOfReplicas=2) will cause the NDB cluster to shutdown.

This can be solved by using host names instead of IPs in the config.ini file. What needs to be done is to define, in /etc/hosts, on entry per cluster member. The API nodes are not required. Here is an example:

$ more /etc/hosts 127.0.0.1 localhost.localdomain localhost 10.11.11.11 mgmn1 10.22.22.22 mgmn2 10.33.33.33 data1 10.44.44.44 data2

the file will be present and identical, at least for the NDB part, in all hosts. Next the NDB configuration must use the hostname like:

[NDBD DEFAULT] NoOfReplicas=2 Datadir=/var/lib/mysql-cluster/ DataMemory=1G IndexMemory=100M [NDB_MGMD] Id=1 Hostname=mgmn1 [NDB_MGMD] Id=2 Hostname=mgmn2 [NDBD] Id=3 Hostname=data1 [NDBD] Id=4 Hostname=data2 [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD]

This is, of course, a very minimalistic configuration but I am sure you get the point. Let’s go back to our original problem and consider that data2 went down. You spin up a new host, configure it with NDB. Consider, for example, that the IP of the new host is 10.55.55.55. The first thing to do is to update all the /etc/hosts files so that they look like:

$ more /etc/hosts 127.0.0.1 localhost.localdomain localhost 10.11.11.11 mgmn1 10.22.22.22 mgmn2 10.33.33.33 data1 10.55.55.55 data2

And now, you start the ndbd process (or ndbmtd) on data2. Since the management nodes read the /etc/hosts file before the change to get the IP from which to expect the connection, you’ll get the “No free node” error. But then, the management node, when looping back in its connection handling code, will read the new /etc/hosts file and the second time, the connection will succeed.

PlanetMySQL Voting: Vote UP / Vote DOWN

This post has sample configurations, and an update, to the Nagios plugin called mysql_health_check.pl that PalominoDB has developed.  Presentation slides, the presentation video, the whitepaper and the download link to the actual Nagios check are always up-to-date at http://palominodb.com/about-us/projects.

There has been exciting news for the Nagios plugin PalominoDB developed for MySQL -- we now have --critical and --warning options for all modes (for --varcomp mode we only had a critical option before).

 

Here are some sample configurations (which are also seen in the slides):

To check rate of temporary tables:

define command { command_name    check_mysql_tmp_tables command_line    $USER1$/mysql_health_check.pl--hostname $HOSTADDRESS$ --user myuser --password mypass --cache-dir=/var/lib/nagios/mysql_cache--max-cache-age=300 --mode=varcomp --expression="Created_tmp_tables/Uptime_since_flush_status" --warning=">(8/60)" --critical=">(10/60)"}define service {      use                      generic-service      host_name                __HOSTNAME__      service_description      MySQL Temp Tables      check_command            check_mysql_tmp_tables}To check long-running queries (and an example of using a parameter, setting 2 different values one for master servers and one for slave servers):define command { command_name    check_mysql_locked_queries command_line    $USER1$/mysql_health_check.pl--hostname $HOSTADDRESS$ --user myuser --password mypass --cache-dir=/var/lib/nagios/mysql_cache--max-cache-age=300 --mode=locked-query  --warning=$ARG1$ --critical=$ARG2$ }define service {      use                      generic-service      host_name                __HOSTNAME__      service_description      MySQL Master Locked Queries      check_command            check_mysql_locked_queries!60!120}

 

define service {      use                      generic-service      host_name                __HOSTNAME__      service_description      MySQL Slave Locked Queries      check_command            check_mysql_locked_queries!300!600}

If there are questions, comments or feedback, please let us know!

PlanetMySQL Voting: Vote UP / Vote DOWN

I don’t need to emphasize how productive you can be if you find what you are looking for in a quick and easy way. Google has shown it to us. Wouldn’t it be fun to have the same ease and speed while searching for data in your databases? Well, that’s what Data Search is all about. Find the data you want without writing any queries or worrying about the underlying schema. Type in the search string and you’ll have a table of all search results waiting to be clicked and be taken to the exact cell of the table. It also supports search operators like AND, OR and “”. Note that the results of this search is not the same as WHERE clause query results. Its rather a natural way of search. Lets say its like googling your database. For better precision you can search with regular expressions too.

MySQL Data Search

This release also includes many enhancements to the User Interface. Most of the dialog boxes are now re-sizable, and the ‘Advanced’ option of Import External Data tool is redesigned. You can also set the foreground color for tabs and object browser.

I would also like to shed light on Master Table Lookup feature introduced in the earlier version. Enter any text in a cell and this nifty feature finds you all the matches of the entered text in the parent table.

Both of these features should be a real time saver, making SQLyog 9.x the must have upgrade for all users. Master Table Lookup is available in all editions whereas Data Search is only present in the Ultimate edition. We are very excited about this release and hope you like it. Feel free to leave your feedback in the comments section below.

For bug fixes and other changes please refer to these release notes.

SQLyog customers can download SQLyog 9.1 GA from the Customer Area.

To evaluate SQLyog 9.1 GA please download a 30-day Trial.

Regards,
Chirag
Team SQLyog

Tweet
PlanetMySQL Voting: Vote UP / Vote DOWN

We have been busy, at Continuent. In addition to our usual work with high performance replication, we have addressed usability issues, since we know that a hard-to-use problem, no matter how powerful, has low adoption. Thus, is with some personal satisfaction that I can announce the release of Tungsten Replicator 2.0.3, which comes with several huge improvements in matter of user friendliness. The new installation procedure is so user friendly, in fact, that I was able to build a sophisticated tungsten-sandbox with a 150-line shell script. (The corresponding features for MySQL Sandbox required 4,500 lines of Perl). Enough self celebration, though. Let's get started, as the title of this post suggests, with the practical steps.
RequirementsBefore we begin, there are a few requirements to meet.

1. You need to be on a Unix-like operating system. Our main choice is Linux. If you want to test on Mac OSX, it works, but we won't recommend it.
2. Java JRE must be installed. And it must be the original one, not the Open JDK.
3. Ruby 1.8 must be installed. This is mainly needed during the installation phase only, but it is required nonetheless.
4. The user account that will install and run Tungsten must have ssh access to the other hosts involved in the cluster
5. The above mentioned user must have sudo access. This is only needed if you want to use Tungsten Replicator to run backups that involve root access (like xtrabackup). We may lift this requirement later, but for now you need to enable it, at least during the installation, and remove the access when you are done.
6. This user must also have read access to MySQL binary logs. Usually you achieve this by making sure that the binary logs are readable by users belonging to the "mysql" group, and by adding such group to your user.
7. There must be a MySQL users for Tungsten replication. This user must have full access to the database server, with grant option.
8. The MySQL server must have binary logging enabled.
9. If you have MySQL native replication running, you must stop it.

Getting the code and installThe code is released in the downloads section of Tungsten's home. The current recommended version is 2.0.3, but if you like to be really up to date, we also publish a list of recent builds from our build server, which you can use to have a go at the replicator. For this simple installation, I will use four servers from our server farm. The servers are named R1, R2, R3, and R4. The first good news of the new installation process is this: you need to install in one server only!. More details follow. First off, create a directory where you want to install. Use a non-root account. Just make sure that it's the same user in all the servers, and that such user can access the directory where you want to install. I am going to call this directory planet.
cd $HOME
for N in 1 2 3 4 ; do ssh r$N mkdir planet ; done
cd planet
wget http://tungsten-replicator.googlecode.com/files/tungsten-replicator-2.0.3.tar.gz
tar -xzf tungsten-replicator-2.0.3.tar.gz
cd tungsten-replicator-2.0.3
I have already a MySQL user named tungsten with password "mypwd" (but it can be anything you like, as long as it has the required privileges). Now we have all the components. If you have read the Tungsten documentation, please ignore the ./configure script. That is left for compatibility reasons, and will be deprecated soon. Instead, to install the cluster of our 4 servers, let's do the following:
export TUNGSTEN_BASE=$HOME/planet

./tools/tungsten-installer \
--master-slave \
--master-host=r1 \
--datasource-user=tungsten \
--datasource-password=mypwd \
--service-name=dragon \
--home-directory=$TUNGSTEN_BASE \
--cluster-hosts=r1,r2,r3,r4 \
--start
Some comment on this command: --master-slave is the installation mode (see below for more info). --service-name can be anything you want. --home-directory is where all the installation sub directories will go. --cluster-hosts is the list of servers you want to install, and finally, --master-host is the host that will be installed as a master, while all the others will be slaves of that one. If you have followed the instructions carefully, the installer will bring up the Tungsten cluster without any fuss, Unix style. If you hate silent installations, you can get the full monty by adding some options:
./tools/tungsten-installer \
--verbose \
--master-slave \
--master-host=r1 \
--datasource-user=tungsten \
--datasource-password=mypwd \
--service-name=dragon \
--home-directory=$TUNGSTEN_BASE \
--cluster-hosts=r1,r2,r3,r4 \
--start-and-report
If you run the installer in verbose mode, you will see an extremely long list of validation checks that the installed does on your current servers and on the ones that are listed in the --cluster-hosts option. If everything went well, you will find the following directories in $HOME/planet (for all servers in your cluster):

• configs, containing the configuration file created by the installer. This file describes your cluster
• releases, containing the Tungsten binaries.
• thl, containing Tungsten's Transaction History Logs. These logs are like MySQL binary logs, but with much more metadata, including a global transaction ID, which is missing in MySQL native replication.
• relay, which should be empty, unless you install in "direct" mode (see below.)
• tungsten, which is a symlink to the Tungsten directory inside releases.

In addition to the above mentioned directories, Tungsten Replicator creates a database for each service. Since we have only one service in this topology, you will find a database named "tungsten_dragon". (If you have called your service "bunny", you will instead find "tungsten_bunny"). Inside this database there is the replication metadata necessary for making the servers fault tolerant. Only a small amount of data is kept on that database. It's roughly corresponding to what you get from the .info files in MySQL native replication. To test that the system is OK, let's find our tools. The first one is trepctl, which, among other things, can give us an overview of the running services.
cd $HOME/planet
./tungsten/tungsten-replicator/bin/trepctl services
Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: 0
appliedLatency : 1.152
role : slave
serviceName : dragon
serviceType : local
started : true
state : ONLINE
Finished services command...

./tungsten/tungsten-replicator/bin/trepctl -host r1 services
Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: 0
appliedLatency : 0.936
role : master
serviceName : dragon
serviceType : local
started : true
state : ONLINE
Finished services command...

mysql -h r1 -e 'create schema if not exists test'
mysql -h r1 -e 'create table test.t1 (i int)'

./tungsten/tungsten-replicator/bin/trepctl services
Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: 2
appliedLatency : 0.155
role : slave
serviceName : dragon
serviceType : local
started : true
state : ONLINE
Finished services command...

./tungsten/tungsten-replicator/bin/trepctl -host r1 services
Processing services command...
NAME VALUE
---- -----
appliedLastSeqno: 2
appliedLatency : 0.044
role : master
serviceName : dragon
serviceType : local
started : true
state : ONLINE
Finished services command...
After the installation, trepctl reported the last applied sequence number (appliedLastSeqno) as 0. Following the execution of two commands in the master, such number became 2. If you want to know more of what was happening, you can use the thl command. This corresponds roughly to using mysqlbinlog with MySQL native replication logs.
/tungsten/tungsten-replicator/bin/thl -service dragon list |less
SEQ# = 1 / FRAG# = 0 (last frag)
- TIME = 2011-06-09 14:51:23.0
- EVENTID = 000002:0000000000000514;197609
- SOURCEID = qa.r1.continuent.com
- STATUS = COMPLETED(2)
- METADATA = [mysql_server_id=10;service=dragon;shard=test]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- OPTIONS = [##charset = ISO8859_1, createOrDropDB = , autocommit = 1, sql_auto_is_null = 1, foreign_key_checks = 1, unique_checks = 1, sql_mode = '', character_set_client = 8, collation_connection = 8, collation_server = 8]
- SQL(0) = create schema if not exists test /* ___SERVICE___ = [dragon] */
SEQ# = 2 / FRAG# = 0 (last frag)
- TIME = 2011-06-09 14:51:30.0
- EVENTID = 000002:0000000000000601;197610
- SOURCEID = qa.r1.continuent.com
- STATUS = COMPLETED(2)
- METADATA = [mysql_server_id=10;service=dragon;shard=test]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- OPTIONS = [##charset = ISO8859_1, autocommit = 1, sql_auto_is_null = 1, foreign_key_checks = 1, unique_checks = 1, sql_mode = '', character_set_client = 8, collation_connection = 8, collation_server = 8]
- SCHEMA =
- SQL(0) = create table test.t1 (i int) /* ___SERVICE___ = [dragon] */
Once we are satisfied that replication is working, we can clean up the cluster and try other installation experiments. To clean up a cluster, you need to do the following:

1. stop the replicator in all servers.
   for N in 1 2 3 4; do $PWD/tungsten/tungsten-replicator/bin/replicator stop; done
2. remove the thl files from all servers.
3. remove the tungsten_SERVICE_NAME database from all mysql servers
4. run a "reset master" in the master database
5. remove the directories created by the installer in all servers

Installation typesThe procedure described above was, until a few months ago, the only thing you could do with Tungsten. Now you can broaden your horizons with a wider range of possibilities.

Master/slave is of course the main option, and it's the one that you have seen in the previous section. This method gives you the full set of Tungsten features and performance. It is the recommended method for production use and for benchmarking. In this scenario, the Tungsten replicator on the master will extract transactions from the binary log, transfer them to the THL, and share it with the slaves. The slaves will read from the THL and apply the transactions to the database. There are a few steps more in between, but for the sake of brevity I will skip them You can have a look at Robert Hodges blog for more info.
Slave "direct" is the alternative that you can use in production, and it's been designed to satisfy users who only want some particular benefits on the slave side, and don't care about global transaction IDs. If you are looking at parallel apply, this is probably a setup that you want to try. In this scenario, there is no replicator on the master. The slave pulls data remotely from the binary logs, copies them locally, and extracts data to the THL. Here's an example of how to start a slave-direct system:
./tools/tungsten-installer \
--direct \
--master-host=r1 \
--slave-host=r4 \
--master-user=tungsten \
--slave-user=tungsten \
--master-password=secret \
--slave-password=secret \
--service-name=Castor \
--thl-port=21124 \
--rmi-port=10104 \
--channels=5 \
--home-directory=$TUNGSTEN_BASE \
--start-and-report

If your purpose is testing Tungsten, probably the Tungsten Sandbox is what you should try. This system is based on MySQL Sandbox, a framework that lets you install more than one MySQL server in the same host. Building on top of MySQL Sandbox, and leveraging the new flexibility in Tungsten installer, tungsten-sandbox allows you to build a master/slave system inside a single host. Let's give it a try. You need to have MySQL Sandbox installed, and at least one MySQL tarball expanded under $HOME/opt/mysql/X.X.XX (where X.X.XX is the MySQL version, such as 5.5.12).
cd $HOME/planet
mkdir sb
cd tungsten-replicator-2.0.3
wget http://tungsten-replicator.googlecode.com/files/tungsten-sandbox
./tungsten-sandbox -h
USAGE: ./tungsten-sandbox [flags] args
flags:
-n,--nodes: how many nodes to install (default: 3)
-m,--mysql_version: which MySQL version to use (default: '5.1.56')
-t,--tungsten_base: where to install the sandbox (default: '/home/tungsten/tsb2')
-d,--group_dir: sandbox group directory name (default: 'tr_dbs')
-s,--service: how the service is named (default: 'tsandbox')
-P,--base_port: port base for MySQL sandbox nodes (default: 710)
-l,--thl_port: port for the THL service (default: 1211)
-r,--rmi_port: port for the RMI service (default: 1010)
-v,--[no]version: show Tungsten sandbox version (default: false)
-h,--[no]help: show Tungsten sandbox help (default: false)
In my server, I have already expanded MySQL 5.5.10, and I want to install inside $HOME/tsb. So, here is what I do:
./tungsten-sandbox -m 5.5.10 -t ~/tsb
This command installs three instances of MySQL under $HOME/sandboxes and three of Tungsten under $HOME/tsb. Inside this directory, in addition to the running instances, we find some more goodies:

• clear_all send_kill_all start_all status_all stop_all use_all : symlinks to the corresponding commands in MySQL sandbox
• db1 db2 db3: these are the tungsten instances.
• n1 n2 n3: quick links to access each MySQL node
• replicator_all trepctl_all: utilities that run "replicator" or "trepctl" for each node with the arguments provided on the command line

Additionally, there are a few scripts inside each Tungsten instance in the sandbox:

• trepctl: a link to the deep down trepctl
• thl: a link to the thl utility
• show_log: a quick way of showing the replicator log

Since this is a tool for testing, removing it is going to be easy.
~/tsb/replicator_all stop
~/tsb/clear_all
rm -rf ~/tsb/*

A final method of installing is a sandbox with ths slave-direct method. There is no dedicated script for this method, but thanks to the new installer, you can get the job done quite easily:
export NODE_OPTIONS='-c innodb_flush_log_at_trx_commit=2 -c max_allowed_packet=48M'
make_multiple_sandbox --group_directory=tr_dbs --sandbox_base_port=7100 5.5.10

TUNGSTEN_BASE=$HOME/tinstall/tsb/
./tools/tungsten-installer \
--direct \
--master-host=127.0.0.1 \
--master-port=7101 \
--slave-host=db2 \
--slave-port=7102 \
--master-user=root \
--slave-user=root \
--master-password=msandbox \
--slave-password=msandbox \
--master-log-directory=$HOME/sandboxes/tr_dbs/node1/data \
--service-name=Castor \
--thl-port=12112 \
--channels=5 \
--rmi-port=20000 \
--home-directory=$TUNGSTEN_BASE \

./tools/tungsten-installer \
--direct \
--master-host=127.0.0.1 \
--master-port=7101 \
--slave-host=db3 \
--slave-port=7103 \
--master-user=root \
--slave-user=root \
--master-password=msandbox \
--slave-password=msandbox \
--master-log-directory=$HOME/sandboxes/tr_dbs/node1/data \
--service-name=Pollux \
--thl-port=22112 \
--channels=1 \
--rmi-port=20000 \
--home-directory=$TUNGSTEN_BASE \
--relay-directory=$TUNGSTEN_BASE/relay --start-and-report
This script creates two services (Castor and Pollux), with only one instance of Tungsten replicator, with all the servers (MySQL and Tungsten ones) in the same host.
ConclusionsThere should be much more to say, but I will leave it for the coming days. In the meantime, I encourage everyone to try the new Tungsten and submit bug reports when things don't work as expected. As always, happy hacking!
P.S. Today at 10am PT there is a webinar on this very topic!
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Replication by Russell Dyer, Silent Killdeer, 2010. About 180 pages.

This is a pocket-sized guide to setting up and managing MySQL replication. It is self-published and made via print-on-demand technology. Topics include how replication works, setting up replication, making backups, and administering replication after it’s working. There are several appendixes for replication-related functionality in the MySQL server and command-line tools.

This book doesn’t go into great depth, so don’t expect it to be a reference manual to replication internals or anything like that. It’s more of a how-to manual for beginners, walking through the basics of binary and relay logs, SQL and I/O threads, and so on.

I wish it were more comprehensive in some areas, and talked about tools such as Percona XtraBackup and Maatkit. I don’t think any book on replication today is complete without these tools. I also wish the text font had serifs and was colored black instead of a foggy gray color. This might be an artifact of the print-on-demand technology, I’m not sure.

Some readers will likely prefer the official MySQL manual to this book, while others might find the book to be a convenient and helpful reference. If I had to summarize the book in a sentence, I’d say that it tells readers the basics of setting up and administering replication, but excludes advanced topics, some of which I’d consider essential (e.g. Maatkit, Percona XtraBackup).

Related posts:

1. A review of Get it Done with MySQL 5&6 by Peter Brawley and Arthur Fuller
2. A review of MySQL Administrator’s Bible
3. Why MySQL replication is better than mysqlbinlog for recovery
4. What are your favorite MySQL replication filtering rules?
5. How VoltDB does transaction ordering and replication

PlanetMySQL Voting: Vote UP / Vote DOWN

In the first part of this article I have showed how I align IO, now I want to share results of the benchmark that I have been running to see how much benefit can we get from a proper IO alignment on a 4-disk RAID1+0 with 64k stripe element. I haven’t been running any benchmarks in a while so be careful with my results and forgiving to my mistakes

The environment

Here is the summary of the system I have been running this on (for brevity I have removed some irrelevant information):

# Aspersa System Summary Report ############################## Platform | Linux Release | Ubuntu 10.04.2 LTS (lucid) Kernel | 2.6.32-31-server Architecture | CPU = 64-bit, OS = 64-bit # Processor ################################################## Processors | physical = 2, cores = 12, virtual = 24, hyperthreading = yes Speeds | 24x1600.000 Models | 24xIntel(R) Xeon(R) CPU X5650 @ 2.67GHz Caches | 24x12288 KB # Memory ##################################################### Total | 23.59G ... Locator Size Speed Form Factor Type Type Detail ========= ======== ================= ============= ============= =========== DIMM_A1 4096 MB 1333 MHz (0.8 ns) DIMM {OUT OF SPEC} Other ... # Disk Schedulers And Queue Size ############################# sda | [deadline] 128 # RAID Controller ############################################ Controller | LSI Logic MegaRAID SAS Model | MegaRAID SAS 8704EM2, PCIE interface, 8 ports Cache | 128MB Memory, BBU Present BBU | 100% Charged, Temperature 34C, isSOHGood= VirtualDev Size RAID Level Disks SpnDpth Stripe Status Cache ========== ========= ========== ===== ======= ====== ======= ========= 0(no name) 1.088 TB 1 (1-0-0) 2 2-2 64 Optimal WT, RA PhysiclDev Type State Errors Vendor Model Size ========== ==== ======= ====== ======= ============ =========== Hard Disk SAS Online 0/0/0 SEAGATE ST3600057SS 558.911 Hard Disk SAS Online 0/0/0 SEAGATE ST3600057SS 558.911 Hard Disk SAS Online 0/0/0 SEAGATE ST3600057SS 558.911 Hard Disk SAS Online 0/0/0 SEAGATE ST3600057SS 558.911

It says controller cache is set to write-through (WT), though in fact for every benchmark I have repeated it with (a) write-through and (b) write-back to see if write-back cache would minimize the effects of misalignment.

File system of choice was XFS. Barriers and physical disk cache was disabled. The tool I used was sysbench 0.4.10 that came with this Ubuntu system. I have run every fileio benchmark and an IO bound read-write oltp benchmark in autocommit mode.

File IO benchmark

For the FileIO benchmark, I used 64 files – 1GB, 4GB and 16GB total in size with 1, 4 and 8 threads. The operations were done in 16kB units to mimic InnoDB pages. There were couple interesting surprised I faced:

1. After I got (what I thought was) the best configuration, I added LVM on top of that and the performance improved another 20-40%. It took me a while to figure it out, but here’s what happened – for XFS file system on a raw partition I was using full partition size which was slightly over 1TB in size. When I added LVM on top however, I made the logical volume slightly below 1TB. Investigating this I found that 32-bit xfs inodes (which are used by default) have to live in the first terabyte of the device which seems to have affected the performance here (IMO that’s because of where first data extents were placed in this case). When I have mounted the partition with inode64 option however, the effect disappeared and performance without LVM was slightly better than with LVM as expected. I had to redo all of the benchmarks to get the numbers right.

2. I was running vmstat during one of the tests and my eye caught the spike in OS buffers during “prepare” phase of sysbench. I found out that sysbench would not honor –file-extra-flags during “prepare” phase and instead of having files created using direct IO they were buffered in OS cache and so writes to files were serialized until they were fully overwritten and that way flushed from OS buffers. Buffers would be flushed within first few seconds so the effects of this were marginal. Alexey Kopytov fixed this in the sysbench trunk immediately, though I didn’t want to recompile sysbench on this system so I’ve used Domas’ uncache after prepare to make sure caches were clean.

OLTP benchmark

As the goal was to compare performance with different IO alignment, not different MySQL configurations, I didn’t try out different MySQL versions or settings. Moreover, I have been running these benchmarks for a customer so I just used the setting that they would have used anyway. One thing I did change was – I have significantly reduced InnoDB buffer pool to make sure the benchmark is IO bound.

That said, benchmark was running on a Percona Server 5.0.92-87 with the following my.cnf configuration:

[mysqld] datadir=/data/mysql socket=/var/run/mysqld/mysqld.sock innodb_file_per_table = true innodb_data_file_path = ibdata1:10M:autoextend innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 8M innodb_buffer_pool_size = 128M innodb_log_file_size = 64M innodb_log_files_in_group = 2 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 200 port = 3306 back_log = 50 max_connections = 2500 max_connect_errors = 10 table_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 16M max_heap_table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size = 64M key_buffer_size = 8M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover skip-grant-tables

Amount of rows used was 20M, transactions were not used (autocommit), number of threads – 1, 4, 8, 16 and 32.

Benchmark scenarios

Here’s the different settings that I have ran the same benchmark on. As I mentioned earlier, each of those were run twice – first with RAID controller cache set to Write-Through and then to Write-Back.

1. Baseline – misalignment on the partition table, no LVM and no alignment settings in the file system. This is what you would often get on RHEL5, Ubuntu 8.04 or similar “older” systems if you wouldn’t do anything with respect to IO alignment.

2. Misalignment on the partition table, but proper alignment options on the file system. This is what we get when file system tries to balance writes but is not aware that it is not aligned to the beginning of the stripe element.

3. 1M alignment in partition table but no options on the file system. You should get this on RHEL6, Ubuntu 10.04 and similar systems if you wouldn’t do anything with respect to IO alignment yourself. In this case offset is correct, but file system is unaware how to align files properly.

4. Partition table and file system properly aligned; sunit/swidth set during mkfs. No LVM at this point.

5. Partition table aligned properly; sunit/swidth set during mounting but not during mkfs. This is your best option if you have a proper alignment in partition table but you did not set alignment options in xfs when creating it and you don’t want or can’t format the file system. One thing to note however – files that were written before this was set may still be unaligned, though xfs defragmentation may be able to fix that (not verified).

6. Added LVM on top of aligned partition table, used proper file system alignment.

Benchmark results

I had a hard time thinking how it would be best to present results so it’s not too stuffed and actually interesting. I decided that instead of preparing charts for each benchmark, I’ll just describe few less interesting numbers first, then I’ll show graphs for more interesting results. Let me know if you thought this was a bad idea

File IO benchmark results

Sequential read results are expectedly the least interesting. Read-ahead kicked in immediately giving ~9’600 iops (~150MB/s) at 1 thread, 14500 iops (~230MB/s) at 4 threads and ~16300 iops (~250MB/s) at 8 threads. Neither IO alignment nor file size made any difference. Adding LVM here reduced single-thread performance by 5-10%.

Sequential write results were a bit more interesting. With WT (write-through) cache enabled, performance was really poor whatsoever and there was virtually no difference whether it was 1 thread, 4 or 8 threads. Different file sizes made no difference too. Write-back cache gave an incredible performance boost – up to 33x in single-threaded workload. File system IO alignment seems to have made a difference – up to 15% with write-back cache enabled. Here’s 1GB seqwr with WT cache:

Here’s same test with WB cache:

And just to show you the difference between sequential writes with WT cache and WB cache:

Random read. This is probably the most interesting number for OLTP workload which is usually light on writes (especially if there’s a BBU protected Write-Back cache) and heavy on random reads. Regardless of the file size, the difference between aligned and misaligned reads was the same and, WT -vs- WB cache of course showed no difference at all. Here are the results:

As you can see IO alignment makes a difference here and improves performance up to 15% in case of 8 threads running concurrently. Because the customer was running a database which was way bigger than 16G, I’ve repeated the random read (and write) benchmark with 8 threads and total size of 256G. While the number of operations per second was slightly lower, the difference was still 15% — 909 iops unaligned -vs- 1049 aligned.

Random write. This is an important metric for write intensive workloads where there’s a lot of data being modified, inserts are done to random positions (not consecutive PK causing page splits) etc. Benchmark results are fairly consistent regardless of file size, let’s look at them. First, results with WT cache:

And here’s with WB cache:

Apparently proper IO alignment in this case gives up to 23% improvement when WB cache is used. With WT cache enabled, single thread performance improvement is marginal however WB cache brings single thread random write performance close to what 8 threads can do, and IO alignment gives extra 23% in this case.

I mentioned I did single test on a larger files (same test I did for random reads) i.e. 8 thread random write benchmark on files totaling to 256GB. With WB cache enabled, I got 919 iops unaligned and 1127 iops aligned i.e. the improvement is still 23%.

OLTP benchmark results

From this benchmark, I only have two graphs to show you. First one is with RAID controller set to WT cache:

The second is with WB cache:

I couldn’t figure out what exactly happened with setting #3 when WB cache was disabled, what I do know though is that, based on IO stats I was gathering during the benchmarks, the reason was in fact lower number of IO operations and higher response time – so it seems in this case misaligned IO had some collateral effects in a mixed read/write environment. Note that the benchmarks were all scripted and oltp benchmarks would automatically start after file tests so if there was an error in the setting, it would have reflected across all other benchmarks for the same setting.

Summary

For the two workloads that are most relevant to databases – random reads and random writes – IO alignment on a 4-disk RAID10 with standard 64k stripe element size makes a significant difference. When I launched the system that I was benchmarking, I could clearly see the difference in production as I had another machine running sideways with the same hardware, but with a misaligned IO. Here’s diskstats from the two shards running side by side:

Aligned: #ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg {540} dm-0 447.1 34.0 7.4 0% 2.4 5.4 23.4 49.6 0.6 0% 0.0 0.6 85% 0 Misaligned: #ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg {925} dm-0 462.1 34.1 7.7 0% 3.8 8.2 12.1 87.0 0.5 0% 0.0 0.7 93% 0

While number of operations from the OS perspective is very similar, due to high concurrency response time in the first case is significantly better.

It would be interesting however to run similar benchmarks on a larger RAID5 system where it should make even bigger difference on writes. Another interesting setting might be a [mirrored] RAID0 with many more stripes as not having proper file system alignment should have really interesting effects. Large stripe on the other hand should somewhat reduce the effects of misalignment, though it would definitely be interesting to run benchmarks and verify that. If you have some numbers to share, please leave a comment. Next, I plan to look at IO alignment on Flash cards to see what benefits we can get there from proper alignment.

You can find scripts and plain data here on our public wiki.

PlanetMySQL Voting: Vote UP / Vote DOWN

Now that flash storage is becoming more popular, IO alignment question keeps popping up more often than it used to when all we had were rotating hard disk drives. I think the reason is very simple – when systems only had one bearing hard disk drive (HDD) as in RAID1 or one disk drive at all, you couldn’t really have misaligned IO because HDDs operate in 512-byte sectors and that’s also the smallest amount of disk IO that systems can do. NAND flash on the other hand can have a page size of 512-bytes, 2kbytes or 4kbytes (and often you don’t know what size it is really) so the IO alignment question becomes more relevant.

It was and still is, however, relevant with HDD RAID storage – technology we have been using for many years – when there’s striping like in RAID0, 5, 6 or any variation of them (5+0, 1+0, 1+0+0 etc.). While IO inside the RAID is perfectly aligned to disk sectors (again due to the fact operations are done in multiples of 512-bytes), outside of the RAID you want to align IO to a stripe element as you may otherwise end up reading or writing to more disks than you would like to. I decided to do some benchmarks on a hard disk array and see when this matters and whether it matters at all.

In this article I will however focus on the process of alignment, if you’re curious about benchmark results, here they are.

What is IO alignment

I would like to start with some background on IO alignment. So what is IO alignment and how does a misaligned IO look like? Here is one example of it:

In this case the RAID controller is using 32KB stripe unit and that can fit in 2 standard InnoDB pages (16KB in size) as long as they are aligned properly. In first case when reading or writing a single InnoDB page RAID will only read or write to a single disk because of the alignment to a stripe unit. In the second example however every other page spans two disks so there is going to be twice as many operations to read or write these pages which could mean more waiting in some cases and more work for the RAID controller for that same operation. In practice stripes by default are bigger in size – I would often see see 64KB (mdadm default chunk size) or 128KB stripe unit size so in these cases there would be fewer pages spanning multiple disks so the effects of misalignment would be less significant.

Here’s another example of misalignment, described in SGI xfs training slides:

D stands for the disk here so the RAID has 4 bearing disks (spindles) and if there’s a misalignment on the file system, you can see how RAID ends up doing 5 IO operations – two to D4 and one on each of the other three disks instead of just doing one IO to each of the disks. In this case even if this is the single IO request from OS, it’s guaranteed to be slower both for reading and writing.

So, how do we avoid misalignment? Well, we must ensure alignment on each layer of the stack. Here’s how a typical stack looks like:

Let’s talk about each of them briefly:

InnoDB page

You don’t need to do anything to align InnoDB pages – file system takes care of it (assuming you configure the file system correctly). I would however mention couple things about InnoDB storage: first – in Percona Server you can now customize page size and it may be good idea to check that page size is no bigger than stripe element; second – logs are actually written in 512 byte units (in Percona Server 5.1 and 5.5 you can customize this) while I will be talking here about InnoDB data pages which are 16KB in size.

File system

File system plays very important role here – it maps files logical address to physical address (at a certain level) so when writing a file, file system decides how to distribute writes properly so they make the best use of the underlying storage, it also makes sure file starts in a proper position with respect to stripe size. The size of logical IO units also is up to the file system.

The goal is to write and read as little as possible. If you gonna be writing small (say 500 byte) files mostly, it’s best to use 512-byte blocks, for bigger files 4k may make more sense (you can’t use blocks bigger than 4k (page size) on Linux unless you are using HugePages). Some file systems let you set stripe width and stripe unit size so they can do a proper alignment based on that. Mind however that different file systems (and different versions of them) might be using different units for these options so you should refer to a manual on your system to be sure you’re doing the right thing.

Say we have 6-disk RAID5 (so 5 bearing disks) with 64k stripe unit size and 4k file system block size, here’s how we would create the file system:

xfs - mkfs.xfs -b 4k -d su=64k,sw=5 /dev/ice (alternatively you can specify sunit=X,swidth=Y as options when mounting the device) ext2/3/4 - mke2fs -b 4096 -E stride=16,stripe-width=80 /dev/ice (some older versions of ext2/3 do not support stripe-with)

You should be all set with the file system alignment at this point. Let’s get down one more level:

LVM

If you are using LVM, you want to make sure it does not introduce misalignment. On the other hand it can be used to fix it if it was misaligned on the partition table. On the system that I have been benchmarking, defaults worked out just fine because I was using a rather small 64k stripe element. Have I used 128k or 256k RAID stripe elements, I would have ended up with LVM physical extent starting somewhere in the middle of the stripe which would in turn screw up file system alignment.

You can only set alignment options early in the process when using pvcreate to initialize disk for LVM use, the two options you are interested in are –dataalignment and –dataalignmentoffset. If you have set the offset correctly when creating partitions (see below), you don’t need to use –dataalignmentoffset, otherwise with this option you can shift the beginning of data area to the start of next stripe element. –dataalignment should be set to the size of the stripe element – that way the start of a Physical Extent will always align to the start of the stripe element.

In addition to setting correct options for pvcreate it is also a good idea to use appropriate Volume Group Physical Extent Size for vgcreate – I think default 4MB should be good enough for most cases, when changing however, I would try to not make it smaller than a stripe element size.

To give you a bit more interesting alignment example, let’s assume we have a RAID with 256k stripe element size and a misalignment in partition table – partition /dev/sdb1 starts 11 sectors ahead of the stripe element start (reminder: 1 sector = 512 bytes). Now we want to get to the beginning of next stripe element i.e. 256th kbyte so we need to offset the start by 501 sectors and set proper alignment:

pvcreate --dataalignmentoffset 501s --dataalignment 256k /dev/sdb1

You can check where physical extents will start (or check your current setup) using pvs -o +pe_start. Now let’s move down one more level.

Partition table

This is the most frustrating part of the IO alignment and I think the reason people get frustrated with it is that by default fdisk is using “cylinders” as units instead of sectors. Moreover, on some “older” systems like RHEL5 it would actually align to “cylinders” and leave first “cylinder” blank. This comes from older times when disks were really small and they were actually physical disks. Drive geometry displayed here is not real- this RAID does not really have 255 heads and 63 sectors per track:

db2# fdisk -l Disk /dev/sda: 1198.0 GB, 1197998080000 bytes 255 heads, 63 sectors/track, 145648 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes ... Device Boot Start End Blocks Id System /dev/sda1 * 1 1216 9764864 83 Linux /dev/sda2 1216 1738 4194304 82 Linux swap / Solaris Partition 2 does not end on cylinder boundary. /dev/sda3 1738 145649 1155959808 83 Linux Partition 3 does not end on cylinder boundary.

So it makes a lot more sense to use sectors with fdisk these days which you can get with -u when invoking it or with “u” when working in the interactive mode:

db2# fdisk -ul Disk /dev/sda: 1198.0 GB, 1197998080000 bytes 255 heads, 63 sectors/track, 145648 cylinders, total 2339840000 sectors Units = sectors of 1 * 512 = 512 bytes ... Device Boot Start End Blocks Id System /dev/sda1 * 2048 19531775 9764864 83 Linux /dev/sda2 19531776 27920383 4194304 82 Linux swap / Solaris Partition 2 does not end on cylinder boundary. /dev/sda3 27920384 2339839999 1155959808 83 Linux Partition 3 does not end on cylinder boundary.

The rest of the task is easy – you just have to make sure that Start sector divides by number of sectors in a stripe element without a remainder. Let’s check if /dev/sda3 aligns to 1MB stripe element. 1MB is 2048 sectors, dividing 27920384 by 2048 we get 13633 so it does align to 1MB boundary.

Recent systems like RHEL6 (not verified) and Ubuntu 10.04 (verified) would by default align to 1MB if storage does not support IO alignment hints which is good enough for most cases, however here’s what I got on Ubuntu 8.04 using defaults (you would get the same on RHEL5 and many other systems):

db1# fdisk -ul Disk /dev/sda: 1197.9 GB, 1197998080000 bytes 255 heads, 63 sectors/track, 145648 cylinders, total 2339840000 sectors Units = sectors of 1 * 512 = 512 bytes Disk identifier: 0x00091218 Device Boot Start End Blocks Id System /dev/sda1 * 63 19535039 9767488+ 83 Linux /dev/sda2 19535040 27342629 3903795 82 Linux swap / Solaris /dev/sda3 27342630 2339835119 1156246245 8e Linux LVM

sda1 and sda3 do not even align to 1k. sda2 aligns up to 32k but the RAID controller actually has 64k stripe so all IO on this system is unaligned (unless compensated by LVM, see above). So on such a system, when creating file systems with fdisk, don’t use the default value for a start sector, instead use the next number that divides by the number of sectors in a stripe element without a reminder and make sure you’re using sectors as units to simplify the math.

Besides DOS partition table which you would typically work with using fdisk (or cfdisk, or sfdisk), there’s also a more modern – GUID partition table (GPT). The tool for the task of working with GPT is typically parted. If you are already running GPT on your system and want to check if it’s aligned, here’s a command for you:

db2# parted /dev/sda unit s print Model: LSI MegaRAID 8704EM2 (scsi) Disk /dev/sda: 2339840000s Sector size (logical/physical): 512B/512B Partition Table: msdos Number Start End Size Type File system Flags 1 2048s 19531775s 19529728s primary ext4 boot 2 19531776s 27920383s 8388608s primary linux-swap(v1) 3 27920384s 2339839999s 2311919616s primary

This is the same output we saw from fdisk earlier. Again you want to look at Start sector and make sure it divides by the size of stripe element without a reminder.

Lastly, if this is not a system boot disk you are working on, you may not need partition table at all – you can just use the whole raw /dev/sdb and either format it with mkfs directly or add it as an LVM physical volume. This let’s you avoid any mistakes when working on partition table.

RAID stripe

Further down below on the storage stack there’s a group of RAID stripe units (elements) sometimes referred to as a stripe though most of the tools refer to it as a stripe width. RAID level, number of disks and the size of a stripe element set the stripe width size. In case of RAID1 and JBOD there’s no striping, with RAID0 number of bearing disks is actual number of disks (N), with RAID1+0 (RAID10) it’s N/2, with RAID5 – N-1 (single parity), with RAID6 – N-2 (double parity). You want to know that when setting parameters for file system but when RAID is configured, there’s nothing more you can do about it – you just need to know these.

Stripe unit size is the amount of data that will be written to single disk before skipping to next disk in the array. This is also one of the options you usually have to decide on very early when configuring RAID.

Disk sectors

Most if not all hard disk drives available on the market these days use 512-byte sectors so most of the time if not always you don’t care about alignment at this level and nor do RAID controllers as they also operate in 512-bytes internally. This however gets more complicated with SSD drives which often operate in 4kbyte units, though this is surely a topic for another research.

Summary

While it may seem there are many moving parts between the database and actual disks, it’s not really all that difficult to get a proper alignment if you’re careful when configuring all of the layers. Not always however you have a fully transparent system – for example in the cloud you don’t really know if the data is properly aligned underneath: you don’t know if you should be using an offset, what stripe size and how many stripe elements. It’s easy to check if you’re aligned – run a benchmark with an offset and compare to a base, but it’s much harder to figure out proper alignment options if you are not aligned.

Now it may be interesting to see what are real life effects of misalignment, my benchmark results are in the second part.

PlanetMySQL Voting: Vote UP / Vote DOWN

Released on June 8, 2011 (Downloads are available here and from the Percona Software Repositories.

Percona Server 5.1.57-12.8 is now the current stable release in the 5.1 series. It is is based on MySQL 5.1.57.

Bug Fixes

• Fixed InnoDB I/O code so that the interrupted system calls are restarted if they are interrupted by a signal. InnoDB I/O code was not fully conforming to the standard on POSIX systems, causing a crash with an assertion failure when receiving a signal on pwrite(). Bug Fixed: LaunchPad: #764395 / MySQL bug #60788 (A. Kopytov)

• The maximum value for innodb_use_purge_threads has been corrected to 32 (maximum number of parallel threads in a parallelized operation). The innodb_purge_thread patch accepted a value up to 64 for the innodb_use_purge_thread variable, leading to an assertion failure for greater than the actual maximum. Bug Fixed: #755017 (L. Biveinis)

Other Changes

• HandlerSocket, a NoSQL plugin for MySQL, has been updated to the latest stable version as April 11th, 2011.
• The list of authors of the plugins used have been corrected. Bug Fixes: #723050 (Y. Kinoshita)

For more information, please see the following links:

• Downloads: Binary distributions, Percona Software Repositories

• Installation instructions

• Consulting, support, training, and custom development

• Release notes

• Documentation

• Developer discussion group

PlanetMySQL Voting: Vote UP / Vote DOWN

Following up on my first post on an alternative, more SQL-eee metadata driven approach to doing BI on Big Data, I’d like to share an example on how we can enable easy reporting on top of BIg Data immediately for CouchDB users. We’re very keen on discussing with CouchDB/Hive/other Big Data users about their Ad Hoc and BI needs; please be in touch with us to let us know what you think.

We’ve been working with some new potential customers on how to leverage their investment in Big Data (specifically Big Couch provided by the fine folks at Cloudant. In particular, this prospects users are thrilled with their dashboards and analytics that are baked into their application, but they need to be able to enable Ad Hoc, easy analysis on top of their data via a traditional reporting tool. Like any organization, IT is overworked and reports always seem to find their way to close to the back of the priority. Our initiative at this customer is about enabling easy access to their high performance and well tuned/architected CouchDB instance.

I figured I could generalize what we’re doing at this prospect to a more general post, and hope that this post (and the software behind it, namely LucidDB) finds there way into the hands of others that are trying to do Ad Hoc and easy SQL based analytics on top of CouchDB.

First, the overview:

We’ve created a solution that allows HTTP views in CouchDB to be made accessible via SQL inside of LucidDB. This means two things:

• Direct access to LIVE, real time data in CouchDB is available to any tool that can talk to LucidDB (SQL + JDBC/ODBC).
• We can use this access to very easily, keep a cached up to date “cache” of this data in LucidDB, an optimized column storage database. In fact, the periodic refresh is a single MERGE statement (TO BE COVERED IN A SUBSEQUENT BLOG)

Our vision of Big Data BI requires that the user be able to “choose” which version of the data they want, easily. Cached and summarized inside LucidDB -OR- up to date, real time results from Big Data (aka CouchDB). This example focuses on the first part of the “hot or cold” level of aggregation ability; namely the live “HOT” piece.

First, let’s look at our source data. We’ve used the Federal Purchasing Dataset (a sample of 1000 records, since this runs on my laptop). Here’s a couple of screenshots from Futon that show the source data docs, and then the CouchDB View.

and also the “view” that we’ll access

NOTE: This view is definitely atypical. It is just emitting docs/flattening. it is NOT doing any aggregation with is what is almost always done with HTTP views. Our connector initially supports streaming data from the values, but we’re already working on getting proper support for the KEYs (and ability to push down group_level, etc).

There’s another key piece of our philosophy on Big Data BI that is also present here. Notice that we’re not trying to “dumb down” the source, Big Data system? We’re not trying to strangely Map SQL onto some remote language so that you’ve lost some richness. CouchDB’ers take note: You can do all that you normally can and want to do in that view. We believe in effeciently accessing (group_level optimizations) not about reducing your abilities to write very interesting, complex map / reduce that returns your base level results.

Let’s make these available, via ANSI SQL, in LucidDB.

I’ll start with a vanilla installation of LucidDB (NOTE: I used a pre release version of 0.9.4 for this).

First, let’s give LucidDB the ability, in a general way, to talk to CouchDB as a foreign data source:

Now LucidDB has the ability to talk to CouchDB, in a general sense. Let’s now configure and access our test dataset.

First step is to create a server connection from LucidDB to our CouchDB server:

Now, we’ve established a connection from our LucidDB installation to CouchDB; let’s now make that “all” view available via SQL.

That statement looks complicated. It is, unfortunately, a little more involved than this process usually is. If you were talking to say, Oracle database, we can import and “discover” all this information (datatypes, column names, etc) from the driver. We have not done all this additional metadata work for the CouchDB connector yet; or actually, how to get ALL this information from the remote view as well.

So, we’ve told LucidDB about the server, the view, the datatypes/fields. We’re all set to now do ANY SQL we like on top of it.

How about doing some aggregations?

We’ll cover creating the “local” cached copy of the CouchDB dataset in the next section.

PlanetMySQL Voting: Vote UP / Vote DOWN

UPDATE: Think we’re right?  Think we’re wrong?  Desperate to figure out a more elegant solution to self service BI on top of CouchDB, Hive, etc?  Get in touch, and please let us know!

There’s a ton of swirling about Hadoop, Big Data, and NoSQL.  In short, these systems have relaxed the  relational model into schema(less/minimal) to do a few things:

• Achieve massive scalability, resiliency and redundancy on commodity hardware for data processing
• Allow for flexible evolution and disparity in content of data, at scale, over time
• Process semi-structured data and algorithms on these (token frequencies, social graphs, etc)
• Provide analytics and insights into customer behaviors using an exploding amount of data now available about customers (application reports, web logs, social networks, etc)

I won’t spend that much more time delving into the specifics of all the reasons that people are going so ape-sh*t over Big Data.

Big Data has a secret though:

It’s just a bunch of technology that propeller heads (I am one myself) sling code with that crunch data to get data into custom built reporting type applications.  Unlike SQL databases, they’re NOT ACCESSIBLE to analysts, and reporting tools for easy report authoring and for businesses to quickly and easily write reports.

Until businesses get to ACTUALLY USE Big Data systems (and not via proxy built IT applications) it’s value to the business will be minimal.  When businesses get to use Big Data systems directly; there will be dramatic benefit to the business in terms of timeliness, decision making, and insights.

And don’t get me wrong, there’s HUGE value in what these systems can do for APPLICATION developers.  Sure sure sure.  There’s Hive, and Pig, and all these other pieces but here’s the deal:  Not a single set of technology has assembled, from start the finish, the single system needed to quickly and easily build reports on top of these Big Data systems.

It’s starting to get real confusing since vendors see Hadoop/Big Data exactly how they want you to see it:

• If you’re a BI vendor, it’s a new datasource that people can write apps and stream data into your reports.
• If you’re an ETL vendor, it’s a new datasource and you’ll simply make it practical.
• If you’re an EII vendor, it’s a new target for federating queries.
• If you’re an analytic DB vendor, it’s an extension point to do semi-structured or massive log processing.

We have a different vision for doing BI on Big Data; it’s different than the “our product now works with Big Data too” you’ve been told from other vendors:

• Metadata matters: Build a single location/catalog for it! Where did this report on my dashboard actually come from?  When I see this thing called “Average Bid Amount” on my report, which fields back in my NoSQL datastore was that calculated from?  Why bother with a separate OLAP modeling tool when you already know your aggregation methods and data types.  Current solutions where “it’s just another source” of data that shove summarized Big Data into MySQL databases for reporting miss a huge opportunity for data lineage, management, and understanding.
• Summary levels require different “types” of data storage and access
  The total number of events can be represented and aggregated to many many different levels of aggregation.  Some, very highly summarized figures (such as daily event counts) should be kept in memory and accessible extremely fast for dashboards.  Relatively summarized figures (10s of thousands) should be kept in a database.  Datamarts (star schemas) that represent some first or second level of aggregation (100m rows) should be kept in a fast column store database.  The detail transaction data, and its core processing technologies (M/R, graph analytics, etc) are in the Big Data system.  Current solutions provide only tools for data slinging between these levels of aggregation; none provide the ability to access and manage them in a single system.
• Inexpensive BI tools allow for cheaper, quicker and better analytic solution development
  The “Business Intelligence” industry has been driving the application developer out of the required chain of events for building dashboards/analytic for years.  In short, BI is a huge win for companies because it’s cheaper, helps get insights faster, and ultimately allows analysts to be somewhat self sufficient to do their job.  Big Data has taken the industry back 10-15 years by making not just complicated reports but literally EVERY report be built by a developer!  Current solutions allow for direct connect in reports to Big Data systems but you still have to write programs to access the data.  Other solutions simply pull data out of Big Data systems and shove it at MySQL because of this exact reason!
• SQL is familiar, well known, and matches with the “easy” data analytics
  How easy is it to find and hire someone who can write SQL and build reports (in Crystal/Pentaho/Actuate/etc)?  There are literally millions of people that can know SQL.  Like it?  Who knows.  Know it, can use it?  MILLIONS!  How about hiring someone to build reports who knows the ins and outs of cluster management in Hadoop, knows how to write multi step programs and write application code to push that data into visualization components in the application?  10s of thousands, maybe.  And  70% of them right now are in Silicon Valley.  Trust me; these skills won’t spread outside of Silicon Valley in great numbers (or at least quickly).
• Reporting Only data matters!  Make it easy to access/integrate
  Simple reporting rollups (say categories of products/web pages, etc) have no place being “pushed” into the Big Data system.  Having a system that is doing BI on top of Big Data needs a way to easily, in a metadata driven fashion, match up the Big Data with other reporting only data.  Current solutions require complex ETL development and assemble it as part of that stream of data to shove at a MySQL database.
• Hot or Cold reporting, let the user choose via SQL
  For dashboards the user is almost certainly willing to use the previous load (last hours) data instead of waiting 17minutes to run in the Big Data system.  For Ad Hoc analysis reports need to be speed of thought; big data systems can do OK here, but on smaller datasets the best choice is a columnar, high performance BI database (ahem!).  For small summaries, or consistently accessed datasets it should be stored in memory.  Current solutions require someone building reports to KNOW where the data that they want is, and then connect to an entirely different system (with different query languages such as MDX, SQL, and M/R) to build a report.

We’re building a system, with LucidDB at the center, that is the most complete solution for doing real, day to day, full featured (adhoc, metadata, etc), inexpensive analytics on top of Big Data.  Ok, Yawn to that.  Since Hadoop and Big Data is hype-du-jour I don’t expect you to believe my bold statements.  We’re releasing LucidDB 0.9.4 in the coming weeks, and this core database release will be the infrastructure for the new UI, extensions, and pieces of our solution.

In short DynamoBI’s Big Data solution provides:

• Ability to use inexpensive, commodity BI tools on top of Big Data directly and cached (Pentaho, BIRT, etc).  Connect with your existing BI/DW system (via JDBC/ODBC).
• Ability to connect, and make accessible via SQL Big Data systems (currently Hive/HDFS, CouchDB)
• Easily define and schedule incremental updates of local, fast column store caches of data from Big Data systems for Ad Hoc analysis
• Ability to quickly link and include data from other places (Flat File, JDBC, Salesforce, Freebase, etc) with Big Data
• Define aggregations, rollups, and reporting metadata (hierarchies, aggregations, etc)
• Drag and drop reports (via Saiku, Pentaho Analyzer, BIRT)
• Easy, RESTful interaction with the server so our solution fits nicely with your existing systems.

Looking forward to blogging more about it in the coming weeks; if you’re sick and tired of hacking together and spending tons of developer time on building reports on top of Big Data systems please get in touch with us.

PlanetMySQL Voting: Vote UP / Vote DOWN

Permalink: http://mysql-0v34c10ck.blogspot.com/2011/06/truly-random-and-complex-password.html



Its an important matter of security to enforce complex passwords that have a sufficient length. From personal experience, if you ask a normal user to create their own passwords, their passwords will be based on a character set consisting of 36 case-insensitive alphanumeric characters: a-z, 0-9 instead of the full 94 character set typable on all keyboard layouts. Also, most normal users would use dictionary based passwords with a predictable pattern: dictionary words at the beginning and numbers at the end.

Relying solely on the client-side or front-end to enforce the creation of passwords of at least 8 characters long and the use of special characters will not be practical in preventing the use of dictionary words as well as the usage of a certain pattern. Whatever the mechanism is on the client-side, the backend MySQL database should complement it.

Assigning complex passwords to users will, in effect, increase the number of characters from 36 to 94. By making the password randomly generated, the predictability of dictionary words and pattern matching is removed. The number of possible passwords is substantially increased. For an 8-character password string, under a reasonable time limitation, say 6 hours, this results to an uncrackable password:

SELECT FORMAT(POW(32, 8), 0);
-- Results to 1,099,511,627,776 possible combinations. Note that the number of possible combinations is greatly reduced when the user limits the password to use dictionary words and pattern matching. This results to a crackable password in a short period of time.


SELECT FORMAT(POW(94, 8), 0);
-- Results to 6,095,689,385,410,816 possible combinations. By being randomly generated, the number of combinations is not reduced as explained above. This results to an uncrackable password given a short period of time.

A password generator, to be truly random, should satisfy the following:

• The character set for the generator should include all the typable characters on any keyboard layout: 
  
  a-z, A-Z, 0-9, and ` ~ ! @ # $ % ^ & * ( ) - = _ + [ ] { } \ / | ? ; : ' " , . < >
  
  This results to 26 + 26 + 10 + 32 = 94 characters.
• Each of the allowed characters should all have an equal chance of being generated.

For practical purposes, we'll take aside arguments on password complexity versus password length, and we'll assume an 8-character password string. To generate any of the 62 alphanumeric characters, we'll use a base 36 statement as the formula:

      SELECT SUBSTRING((
        CONV(
          SUBSTRING(
             RAND(),
          3),
        10, 36)),
      2, 1);

Using a base 36 statement gives us the most compact alphanumeric numeral system. The case sensitivity will be based on odds from a random number range in order to include the LOWER case of the alphabet. The first character is discarded since it will never generate 0. The second character is a safe pick as a random character in order for 0 to have an equal chance to be randomly generated.

The special characters can be generated by using the ELT function as the basis for the formula like:

      SELECT ELT(1 + FLOOR(RAND() * 32),
      '`', '~', '!', '@', '#', '$', '%', '^',
      '&', '*', '(', ')', '-', '=', '_', '+',
      '[', ']', '{', '}', '\\', '/', '|', '?',
      ';', ':', '\'', '"', ',', '.', '<', '>');



In the continuation of this entry is an example of a true random and complex password generator function.


PlanetMySQL Voting: Vote UP / Vote DOWN

A customer rose a support case with a problem on his Master-Master set-up. The 2nd Master claims to have a problem:

master2> SHOW SLAVE STATUS\G ... Master_Log_File: master1-bin.000014 Read_Master_Log_Pos: 97975045 Slave_IO_Running: No Slave_SQL_Running: Yes Exec_Master_Log_Pos: 97975045 Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'

What has happened?

When we look at the actual Masters binary logs we see the following situation:

master1> SHOW BINARY LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | master1-bin.000013 | 68757710 | | master1-bin.000014 | 97973131 | | master1-bin.000015 | 626440 | +--------------------+-----------+

That looks really odd: The Slave wants some events above the size of the Masters binary log!
Looking at the masters binary log we cannot see anything suspicious. Binary log #14 was closed and rotated properly and the size of the file matches exactly the size mentioned in the output.

When we were asking the customer a bit more in detail what has happened he told us that both masters crashed because their SAN had a problem and all virtual machines went down.

Master 1 is located on the SAN and the volume was mounted through VMWare and Master 2 had direct attached disks directly mounted into the VM.

So it looks like during the crash on the active Master 1 we lost some data which were already arrived on the Master 2. After everything came back on-line the Application continued to write to Master 1.

So Master 1 had some data missing which were on the Master 2 but Master 1 had in addition some data which were not yet replicated to Master 2 because the replication broke.

To make the replication work again we first pointed Master 2 to the beginning of Master 1's next binary log file:

master2> CHANGE MASTER TO master_log_file='master1-bin.000015', master_log_pos=4;

Then we started the Slave on Master 2 again. The replication caught up within seconds without any further problems. Fortunately the application was build in a way that the replication just continued and did not have any conflicts.

Now we had the situation that the Master-Master replication was working again, application was running fine on Master 1 and we have for sure more data on Master 2 than on Master 1 and we are not 100% sure if Master 2 had some data missing.

Luckily there is the Maatkit [1] Toolkit which provides the 2 scripts mk-table-checksum [2] and mk-table-sync [3]. Those scripts help in such a situation.

To find the differences we run on the Master 1:

mk-table-checksum --chunk-size=100000 \ --create-replicate-table --replicate=test.checksum --empty-replicate-table \ u=root,p=secret,h=localhost,P=3306

Then we had to wait until everything was replicated to the Master 2. When Master 2 had caught up we executed on Master 2 (Slave):

mk-table-checksum --replicate=test.checksum --replicate-check=2 \ u=root,h=localhost,P=3306,p=secret

And got the following output:

Differences on P=3306,h=master2 DB TBL CHUNK CNT_DIFF CRC_DIFF BOUNDARIES sales accounting 6 3 1 `account_id` >= 1694287 AND `account_id` < 1976668 sales accounting 7 7 1 `account_id` >= 1976668 AND `account_id` < 2259049 sales notification 0 -1 0 1=1 monitoring server_export 5 8 1 `server_date` >= "2011-06-01"

Now we can see that we have on 3 different tables in 2 different schemata an inconsistency and 18 rows in total were affected.

To sync the tables again you can run the following command: Make sure, that you run it on the right Master:

mk-table-sync --sync-to-master h=localhost,u=root,p=secret,P=3306,D=sales,t=accounting --print mk-table-sync --sync-to-master h=localhost,u=root,p=secret,P=3306,D=sales,t=accounting --execute

If you do it on the wrong side it will suggest you a DELETE instead of a REPLACE.

We found that the mk-table-sync script had some problems with FLOAT values resulting in empty REPLACE statement. Those rows we fixed manually. Luckily it was only a hand-full of rows and not zillions.

Learnings:

• Be careful with virtualization solutions. They swallow you precious data in some situations.
• SAN can be a Single-Point-of-Failor (SPoF). When it goes down you loose all your virtual instances!

PlanetMySQL Voting: Vote UP / Vote DOWN

The MyCleaner program, that is used to clean up data in the MySQL database that is no longer used, no longer referenced or is just unnecessary, is now available for download from SourceForge.net in version 1.1. This new version adds a few things, like the ability to sleep between rounds, more extensive log print control, and above all, the ability to run a non-SELECT to get IDs.
The latter feature may seem weird, but in some cases it is useful, as it will allow you to select IDs into a temporary table, for example, and then use these for deleting the real data, something which can be achieved in many other ways of course, but which is still useful in some cases.

In addition, the ability to run SQL after the program having finished all other processing, has been added, to allow cleanup of temporary objects.

/Karlsson
PlanetMySQL Voting: Vote UP / Vote DOWN

Today, more and more proprietary software vendors are choosing to go Open Source. Doing this enables them to leverage the community benefits of Open Source, shorten the sales cycle, and gain a competitive advantage over other proprietary products.

However, for those firms considering a switch to Open Source, there are some hard decisions to make with regard to their product architecture. Should they provide only a single Open Source product, and earn revenue from add-on services like support and consulting (RedHat)? Or should they adopt the Open Core model, offering their product under both Open Source and proprietary licenses (MySQL)? Or perhaps some hybrid of these two approaches?

In this post, I’ll consider two common architectures, which I’ll respectively call the “Open Core” and “Solutions” architectures.

Open Core Architecture

The term Open Core is used to describe business models where vendors offer both an Open Source “community edition” and a more fully featured, commercially licensed “enterprise edition”. MySQL is undoubtedly the most common example of this model, although it’s widely in use among other firms as well.

Typically, the enterprise edition product comes with vendor guarantees and support, thereby making it more attractive to larger enterprises. At the same time, it ensures that smaller firms and individuals, who may not require these attributes, are not excluded, as they can freely access and use the community edition of the product, albeit without any guarantees.

Solutions Architecture

Under the Solutions Architecture, a vendor offers separate packages or distributions of the same Open Source product, identified in terms of the functionality they add. Thus, rather than an “enterprise edition” and a “community edition”, the vendor might provide specialized offerings for different skill sets such as a “developer edition”, “database administrator edition”, “project manager edition”, and so on.

Typically, each solution comes with vendor guarantees and all the distributions are based on the same Open Source code base. This ensures interoperability and peace of mind. At the same time, this approach is economical for end-users, as they only pay for the product features that they need, and so it allows both individuals and large enterprises to participate in the product ecosystem.

Which is Better?

Given these two options, which should a firm choose? Here are some arguments to consider:

• Under the “Open Core” architecture, the vendor’s community edition may be perceived as “crippleware” lacking the more advanced features found in the enterprise edition. Users may accuse it of cynically providing a functionally limited Open Source version merely to drive sales of its commercial version. This can lead to a negative impression of the vendor, and may adversely affect its market positioning.
• At the same time, by using Open Core terminology such as “community edition” and “professional edition”, the vendor might be seen as implicitly categorizing some users as “non-professional”. Where these users are also contributing to the community, this will naturally cause friction and conflict. The Solutions Architecture, by contrast, makes no such claims and is less likely to produce an “us versus them” mindset.
• In a fragmented, highly competitive market, a vendor faces competition from multiple niche products, both proprietary and Open Source. In this situation, the Solutions approach allows the vendor to compete effectively against these specialist products, as each distribution is narrowly positioned to attract a distinct market segment.
• On the other hand, the Open Core approach is an easy sell to the enterprise market. The typical enterprise edition product will include technical support, warranties, SLAs and compatibility guarantees, all of which are critical to medium- and large-size enterprises. In general, it is also easier for a direct sales force to sell a single enterprise-focused product rather than a distribution-based product line.
• The Open Core model is already successfully in use by many firms. It therefore has the benefit of pre-existing market awareness and acceptance, and it is easy for customers to understand its nomenclature and business benefits. This simplifies the sales process, as there is less customer and salesperson education required.

Conclusion

As the above arguments make clear, there aren’t any hard and fast rules about which product architecture is “the best”. However, it’s possible to identify arguments for and against each approach, and thereby decide which one will be best suited to the firm’s specific goals and requirements. Needless to say, the final decision will have a far-reaching impact on the firm’s business model, competitive positioning and marketing strategy…and so, it should be taken after due consideration of all the relevant factors.

PlanetMySQL Voting: Vote UP / Vote DOWN

From August 15th to 17th FromDual will have an Advanced MySQL Developer Workshop with Citrus in Helsinki (Finland). The workshop will be held in English. The workshop topics you can find here and the registration form is available here.

PlanetMySQL Voting: Vote UP / Vote DOWN