Simulating Replication Lag on MySQL Development and Test Sandboxes

MySQL LogoMYSQL DBAs usually try to minimize replication slave lag, but there are scenarios where you need non-zero lag.

Production applications often write to a MySQL master and read from one or more slave databases.

Development and Test environments (sandboxes) seldom simulate possible replication lag though.

Galera

With a typical installation of Galera, replication lag is usually less than 0.1 second.

You can make a node lag more than that by either shrinking the transaction log size, using large transactions, or both.

You can measure lag by using or modifying one of the causality test scripts included with Galera.

Continuent

You can configure slave lag with the time-delay filter.

You can see what the lag is in the appliedLatency value.

MySQL Built-in Replication

An easy way to do that in MySQL 5.6 is to install two MySQL databases in your sandbox, then configure delayed replication:

mysql> CHANGE MASTER TO MASTER_DELAY = N;

For MySQL 5.x, if you can install the Percona toolkit, then you can use the pt-slave-delay program.

Otherwise, you can use the following commands manually to do something similar:

mysql> STOP SLAVE SQL_THREAD;
mysql> SELECT SLEEP(10);
mysql> START SLAVE SQL_THREAD;

Or programmatically in 5.x, like this pseudocode:

Loop:
   STOP SLAVE; # on slave
   SHOW MASTER STATUS; # on master, parse out File and Position values
   SELECT SLEEP(3);
   START SLAVE UNTIL MASTER_LOG_FILE='theMasterFile',
      MASTER_LOG_POS=theMasterPosition; # on slave
   SELECT MASTER_POS_WAIT('theMasterFile', theMasterPosition); # on slave, optional
   # detect any slave errors and report if found

A variation on the above pseudocode, if you don’t want to connect to the master, is to parse SHOW SLAVE STATUS for Master_Log_File and Read_Master_Log_Pos:

Loop:
   SHOW SLAVE STATUS;
   # detect any slave errors and report if found and
   # parse out Master_Log_File and Read_Master_Log_Pos values
   STOP SLAVE;
   SELECT SLEEP(3);
   START SLAVE UNTIL MASTER_LOG_FILE='theMasterFile',
      MASTER_LOG_POS=theMasterPosition;
   SELECT MASTER_POS_WAIT('theMasterFile', theMasterPosition); # optional

Note that show slave status and stop slave can block or even deadlock with various versions of MySQL, and when FTWRL (Flush Tables with Read Lock) is used.

MySQL Bug #68460: blocked with FLUSH TABLES WITH READ LOCK + SHOW SLAVE STATUS

A tip from the wikipedia project is to add the replication lag to application cookies. Deciding what to do with that info is left as an exercise for the reader. :)

dev.mysql.com: Delayed Replication,
Pausing Replication on the Slave, Start Slave
Stopping the slave exactly at a specified binlog position
MySQL 5.6 FAQ: Replication
xaprb.com: Introducing MySQL Slave Delay (2007)
Percona Toolkit – pt-slave-delay

IMUG: Computer Vision and Behavior Driven Visual Automation in L10n and i18n QA

Yahoo! LogoGeorge Betak from Yahoo! gave an excellent talk on “Computer Vision and Behavior Driven Visual Automation in L10n and i18n QA” at IMUG tonite. This is the future of QA automation.

George is an interesting fellow. Besides writing cutting edge software development for QA, he is also a leader for SF BayLEAF, the Nissan LEAF Owners Association.

TL;DR: Selenium+Sikuli computer vision library allows automated visual testing of web-based apps. It already works in the Yahoo! environment for mature apps that don’t drastically change.

Slides

- functional testing is low-level
- object level is still too low level
- complex frameworks take days or weeks to setup, tough for QA staff who are not generally expert programmers, even more bugs to find and fix
- ie. Selenium, TestNG, Java, Hudson/Jenkins

- Angry Birds demo (computer able to play a game, and well)

- Ostriches: Spot the Difference

- tofu, mojibake, text swell
- Sikuli adds visual test capabilities
- computer vision project based on OpenCV
- MIT and BSD licensed

- Robot framework based on Java
- Cucumber framework based on Ruby

- keyword-driven testing is more productive than low-level programming
- paves the way to true localization automation testing
- replaces human testers and increases productiviry
- cheaper and more consistent than manual review
- cross-platform and cross-device
- HTML object IDs and names replaced with images
- works with native apps and native dialogs (Save dialog, file picker, etc.) too

Sikuli Issues

- Open Source project with limited support
- stability of software stack (bugs, crashes)
- making it work is an art, like configuring the tolerance setting
- 3x or 4x faster than Selenium plus manual

Commercial Tools

- branchauto
- eggPlant

Audience Questions

- can multiple browsers and languages be data-driven, meaning parameter-driven? Yes.
- what happens with global font change or color? Sikuli tolerance, or review major changes and promote as master
- false-positives? 97% with image-based comparison tools on global changes, TBD with Sikuli.
- CAPTCHA challenge? Sikuli gets great results.

Audience Reaction

Many audience members seemed to mistake machine vision for old-school image screenshot comparison.
Some of the translation-oriented members were unable to understand the programmerish aspects of the talk.

Thanks once again to Yahoo! for hosting the meeting, providing WebEX remote access, and the lovely wine and cheese assortment.

wikipedia: eggPlant, Sikuli
George’s Google+

Rackspace Cloud Servers and Cloud API Review

TL;DR: Rackspace Cloud is an acceptable option for existing Rackspace dedicated or managed colo customers, or those who need an alternative to AWS (or just lots of handholding and phone support), and can be very patient for future features.

Introduction

These are my observations based on meetings with Rackspace and daily operational experience.

I used Rackspace Cloud Servers (40 Ubuntu and CentOS linux servers of all sizes in ORD) and Cloud API v1.0 and v2.0 daily from mid-2012 to mid-February, 2013.

Multiple versions of Cloud Servers

Rackspace has seemingly had four Cloud Servers versions so far:

  1. early v1 (obsolete – forced migration to late v1)
  2. late v1 (v1 API frozen, incompatible with Cloud API v2, snapshots limited to 80 GB of “used” blocks)
  3. early v2/early OpenStack (incompatible with Cloud API v1)
  4. late v2/recent OpenStack (incompatible with Cloud API v1).

There are currently two Rackspace Cloud datacenters:

  1. ORD
  2. DFW.

The maximum RAM in each version is 32 GB.

Obviously you want to be using the latest version available to avoid multiple forced migrations down the road.

Server Migration Between Cloud Servers Versions

Migrating a server between different Cloud versions is quite a project.

Rackspace has a bash wrapper script for rsync named rsrsyncLive.sh that lets you copy files from one server to another. In practice it works well, especially for the same flavor of linux, though the result is not guaranteed and there’s no practical way to verify the end result of copying live files. (Please stop your email and database servers before trying this.)

A safer method is to put your server in rescue mode, then mount your filesystems readonly and use a modified version of the wrapper script to copy the now quiescent files to another server.

Each host server can only support one VM in rescue mode, but once you enter rescue mode it is available until the next VM requests it on the same host machine. (I’ve seen a VM in rescue mode last all weekend.)

Cloud Portal Admin UI

The Cloud Portal admin UI allows the end-user to control servers in one DC via the web, but tickets must be filed for the second DC.

Any user login or API key allows you to fully control all servers (no granular roles and permissions). The most common feature request Rackspace gets is to add granular roles and permissions.

Rackspace Cloud API Limitations

The API documentation is sadly typical in not being 100% accurate, both for the text and sample code. Users have contributed fixes in the comments which have mostly been incorporated. (If you see a Rackspace Cloud API response message about a “Java type violation”, then it means the pathinfo for the API request needs one level more or less.)

Neither the Cloud API v1 or v2 return bandwidth consumption data, regardless of what the documentation says.

The v1 Cloud API does not return the server creation date.
The v2 Cloud API does not return backup schedules information.

Rackspace Cloud Servers Features

Not as many total features as some other services, so compare your needs with Rackspace Cloud Servers capabilities first.

No Cloud Servers instance disappeared, although 1 did hang for no obvious reason and required a remote reboot.

It appears that there are only 2 recursive nameservers per DC.

Cloud API Performance and Reliability

The Cloud API worked reliably. API reboots were surprisingly fast, with servers coming up in a second or so.

VPN Between Your Office and Rackspace Cloud

Rackspace Connect is normally used to connect Rackspace leased servers to Rackspace Cloud servers with a Cisco ASA-based VPN. However you can request it from your office or datacenter major-brand router to Rackspace Cloud.

The disadvantages of Rackspace Connect are:

  1. it can take quite a while for Rackspace to configure and test Connect since multiple groups are involved
  2. servers created before Connect is installed must be migrated to new servers on the Connect internal netblock
  3. more routers introduce more point of failure for your Cloud to the Internet, even if using redundant routers
  4. it reduces your total Cloud bandwidth to whatever can fit through the VPN router (likely 100 Mbps to 300 Mbps)

If Rackspace Connect is not right for you, their recommended alternative is OpenVPN, which would be a lot harder to manage for more than 2 servers.

Terms of Service Limitations

It’s very important that potential clients read the Rackspace Cloud ToS and discuss their usage patterns with Rackspace before any commitment of time or money:

  1. in the past, port scans (ie. nmap) were not allowed, which is an issue with third-party PCI compliance auditing
  2. there may be outbound email limitations
  3. only 1 IP address is allowed per server in v2 (rules out hosting providers)
  4. Cloud API calls have daily limits and throttling
  5. only Rackspace-provided server images are supported
  6. ensure your content is ok with Rackspace.

Billing Issues

We had recurring billing formatting and amount problems that were quite time-consuming to track down:

  1. some servers were billed more than 200 hours per month
  2. v1 and v2 Cloud Servers are billed at different hourly rates
  3. one server can be fragmented into multiple billing lines across multiple invoice pages, making reconciliation difficult
  4. the bill is not viewable online in the Cloud Portal admin UI v2 yet.

Rackspace Cloud API v1
Rackspace Cloud API v2
API v1.0 and API v2 Differences
Migrating A Linux Server from the Command Line – Scripted

Interesting commentary from theregister.co.uk:

Price cuts, OpenStack transition make Rackspace miss in Q1
Rackspace shares crater in drab results aftermath

What Happened to Japan’s Electronic Giants?

BBC has an excellent analysis of Japan’s decline in consumer electronics title, “What happened to Japan’s electronic giants?”

According to Tokyo-based economist Gerhard Fasol, “the Japanese giants were overtaken by the digital revolution. The Japanese giants actually built their empires on making complex electrical machines – colour televisions, radios, cassette players, refrigerators, washing machines. Yes, they contained electronic components, but they were basically mechanical devices.

“But then came the digital revolution, and the world changed. The Sony Walkman is a classic example. It has no software in it. It is purely mechanical. Today you need to have software business models that are completely different.”

Having worked for several Japanese companies over the past decade both in the USA and Japan, I saw first-hand their failure to develop and commercialize two entire generations of software: first shrink-wrap consumer software, and then Internet-scale services.

Some observations I made that illustrate failure milestones:

  • failure to export their early world-leading smartphones, based on CHTML
  • large-scale corporate purchases of Microsoft and Adobe suites, instead of developing local products
  • insular Open Source participation, until recently limited to forking projects into dead-end Japanese localizations. Matt Assay says it’s improved (Ruby being one example.)
  • corporate reluctance to embrace Open Source projects like linux and MySQL, in favor of non-financially scalable products like SunOS and Oracle, for years after the rest of the world
  • Sony’s fetish with DRM and non-standard consumer storage formats, like the Memory Stick line
  • ongoing cultural disdain for actual startup companies and entrepreneurs, doubly so for immigrants
  • airtight campus network firewalls, allowing only ports 25, 80 and 443.

“Digital Convergence” in practice equals “Internet-enabled”, which means software.

In summary, Japan’s aversion to consumer software development doomed their largest companies to the last millennium.

I asked Guy Kawasaki 5 years ago about Japan’s virtual disappearance in consumer technology. To paraphrase, he replied, “From my viewpoint it’s incompetence.”

If a Japanese executive asked for my opinion on how to improve, here’s my roadmap:

  1. design your products to be Internet-connected or Internet-enhanced
  2. in your HQ, dedicate an entire floor to an Advanced Software Group with a 2-year mission to be at the leading edge of Open Source software and a 20-year mission to stay there with the following teams: linux kernel, linux applications, MySQL, Postgres, NoSQL, SMTP, HTTP, cloud, Internet security. Do not penalize experimentation or risk-taking by employees.
  3. develop a large-scale paying, online business with ongoing feedback from #2. Do not penalize experimentation or risk-taking by employees
  4. study and get involved in the “maker” prosumer tools (CNC, 3D printing, etc.) trend.

Ironically, I learned computer programming on the Sharp PC-1211, as well as a lot of other programmers. The PC-1211 looked so advanced that I wondered if it was from outer space. Sharp has since declined so much that they may not be an independent company in 2014.

Programming with the eBay API and Perl

I wrote a web application to search and filter daily eBay computer listings using the eBay API, with Perl, XML, CSS, JavaScript and HTML.


eBay API Data Flow

eBay has done a good job with Getting Started info and creating and managing credentials on their eBay Developer site, which only takes 5 minutes to create an account and generate your appid and credentials. An eBay account is not needed for search-only applications.

Although the eBay API works well and has good email support from employees, the documentation is not friendly to new users. For example, finding items by keywords does not by default return all selling info, like seller id.

API programming is made more complicated by legacy eBay data schema limitations, including ambiguity between BuyItNow and FixedPrice listings.

So it took me a few extra minutes of experimentation to get exactly what I wanted, but the result was worth it.


eBay API
Screenshot of final navigation UI, implemented using a button bar with the HTML table and CSS multiple classes technique (see references)

Here is a minimal but non-trivial sample search program using the CPAN eBay::API::Simple module, which is supported by eBay staff.

#!/usr/bin/perl

# Program: ebay_find_test.cgi
# Author: James Briggs (james.briggs -AT- yahoo.com)
# Purpose: demo of the eBay finding API
# Date: 2013 04 16
# Env: Perl5
# Note:

use strict;
use diagnostics;

use eBay::API::Simple::Finding;
use CGI qw / :standard *table /;
use DateTime::Format::ISO8601;

   binmode STDOUT, ':utf8';

   my @keywords = ( 'Dell R710 Quad 4TB' );
   my $app_id = '';
   my $tz = 'America/Los_Angeles';

   print header(), start_html( -title => 'eBay API', );

   my $call = eBay::API::Simple::Finding->new(
    { appid => $app_id }
  );

  for my $keyword (@keywords) { 
     $call->execute( 'findItemsByKeywords', {
         keywords => $keyword,
         paginationInput => { entriesPerPage => 200 },
         outputSelector => [ 'SellerInfo' ],
         sortOrder => 'EndTimeSoonest',
      } );

     if ( $call->has_error() ) {
        die "error: find call failed (check your appid and Internet connectivity):" . $call->errors_as_string();
     }
   
     # getters for the response DOM or Hash
     my $dom  = $call->response_dom();
     my $hash = $call->response_hash();

     print "Keywords: '$keyword', Time: " . $call->nodeContent( 'timestamp' ) . ' # ' .  $call->nodeContent( 'totalEntries' ) . " items\n";
   
     my @nodes = $dom->findnodes(
       '//item'
     );

     print start_table(), "\n";

     for my $n (@nodes) {
       next if $n->findvalue('sellingStatus/sellingState/text()') ne 'Active';
  
       my $dt_start = date_from_utc($n->findvalue('listingInfo/startTime/text()'), $tz);
       my $dt_end   = date_from_utc($n->findvalue('listingInfo/endTime/text()'), $tz);

       print Tr(td([
          $dt_start,
          $dt_end,
          $n->findvalue('itemId/text()'),
          $n->findvalue('sellerInfo/sellerUserName/text()'),
          $n->findvalue('sellerInfo/feedbackScore/text()'),
          $n->findvalue('title/text()'),
          $n->findvalue('sellingStatus/currentPrice/text()'),
          $n->findvalue('listingInfo/buyItNowPrice/text()'),
          $n->findvalue('sellingStatus/bidCount/text()'),
          $n->findvalue('country/text()'),
          $n->findvalue('sellingStatus/timeLeft/text()'),
       ])), "\n";
     }
   }

   print end_table(), "\n", end_html(), "\n";

sub date_from_utc {
   my ($dt, $tz) = @_;

   return '' if not defined $dt or $dt eq '';

   if ($dt ne 'UTC') {
      $dt = DateTime::Format::ISO8601->parse_datetime($dt);
      $dt->set_time_zone($tz);
      $dt = $dt->strftime("%F_%T");
   }

   return $dt;
}

References

developer.ebay.com: eBay Finding API
SO: Show/hide rows based on class in HTML
SO:Changing the class of an element with onclick javascript
wikipedia: eBay API