May 19, 2014 By Severalnines

Thanks to everyone who attended and participated last week’s joint webinar on ClusterControl 1.2.6! We had great questions from participants (thank you), most of which are transcribed below with our answers to them.

If you missed the sessions or would like to watch the webinar again & browse through the slides, they are now available online.

Webinar topics discussed: 

• Database Infrastructure Lifecycle
• Deploy, Monitor, Manage, Scale
• MySQL, MariaDB & MongoDB Clusters
• ClusterControl Overview & Demo
• ClusterControl New Features in 1.2.6 & Demo
• Centralized Authentication using LDAP or Active Directory
• Role-Based Access …

MySQL Cluster automatically shards at the database layer, spreading the database out across nodes so that developers do not have to write complex and intrusive application-sharding logic (which is required by other platforms).

To understand the types of nodes in a MySQL Cluster and to learn how to design, install, configure, and maintain this product, take the MySQL Cluster training course. Below is a selection of the events already on the schedule for this 3-day training course:

I have previously written about the new replication protocol that comes with GTIDs in MySQL 5.6. Because of this new replication protocol, you can inadvertently create errant transactions that may turn any failover to a nightmare. Let’s see the problems and the potential solutions.

In short

• Errant transactions may cause all kinds of data corruption/replication errors when failing over.
• Detection of errant transactions can be done with the GTID_SUBSET() and GTID_SUBTRACT() functions.
• If you find an errant transaction on one …

This is a follow-up on my previous blog post about using Lua enabled sysbench. Today I will dive into how to write Lua scripts for sysbench. Look at this simple example:

function prepare ()
  local i
  print("creating table sbtest.t1 ...")
  db_query("create table t1 (c1 int unsigned primary key, c2 int)")
  db_query("begin")
  for i= 1, 1000 do
    db_query("insert into t1 values (" .. i .. "," .. i .. ")")
  end
  db_query("commit")
end

function cleanup()
  db_query("drop table t1")
end

function help()
  print("sysbench Lua demo; no special command line options available")
end

function thread_init(thread_id)
end

function thread_done(thread_id)
  db_disconnect()
end

function event(thread_id)
  db_query("select c2 from t1 where c1=" .. sb_rand(1, 1000))
end

There are 3 functions prepare(), cleanup() and help(). Those are …

A quite common benchmark for MySQL is sysbench. It was written nearly 10 years ago by Alexey Kopytov.

Sysbench has modes to benchmark raw CPU performance, mutex speed, scheduler overhead and file IO performance. The probably most often used sysbench mode is OLTP. This benchmark mimics a OLTP scenario with small transactions hitting an optimized database. There are many variables to play with, most important is the number of simulated application threads (option --num-threads). The OLTP benchmark can be run read-only, then it does 14 SELECT queries per transaction. Or it can be run read-write which adds 2 UPDATEs and one INSERT and DELETE.

The latest release of this official sysbench tree is 0.4.12. Many Linux distributions ship a package for this.

However there is also a newer version of sysbench, that comes as version number 0.5.
…

MySQL Triggers are changing in 5.7 in a big way. Triggers have been around since 5.0 and have not changed much up to 5.6 but will gain the ability to have multiple triggers on the same event. Previously you had ONE trigger maximum on a BEFORE UPDATE, for example, and now you can have multiple triggers and set their order.

So what is a trigger? Triggers run either BEFORE or AFTER an UPDATE, DELETE, or INSERT is performed. You also get access to the OLD.col_name and NEW.col_name variables for the previous value and the newer value of the column.

So how do you use a trigger? Let say you are updating the price of an inventory item in a product database with a simple UPDATE statement. But you also want to track when the price change and the old price.

The table for products.
…

The MySQL developer tools team announces 6.1.6 as our GA release for MySQL Workbench 6.1.

MySQL Workbench 6.1.6 is a maintenance release and contains over 30 fixes and minor enhancements made since the original GA release.

MySQL Workbench 6.1

Introducing over 30 new features, this version has many significant enhancements focusing on real-time performance assessment and analysis from the SQL statement level to server internals and file IO. You can see this from additions to the SQL Editor as well as new dashboard visualization and reporting that take advantage of MySQL Server 5.6 and 5.7 Performance Schema, and enhancements to the MySQL Explain Plans.

Additionally Workbench 6.1 is leveraging work from various teammates in MySQL Engineering by introducing a schema called "SYS" that provides simplified views on Performance Schema, Information Schema, and other areas. Special …

The MySQL developer tools team announces 6.1.6 as our GA release for
MySQL Workbench 6.1.

MySQL Workbench 6.1.6 is a maintenance release and contains over 30
fixes and minor enhancements made since the original GA release.

MySQL Workbench 6.1

Introducing over 30 new features, this version has many significant
enhancements focusing on real-time performance assessment and analysis
from the SQL statement level to server internals and file IO. You can
see this from additions to the SQL Editor as well as new dashboard
visualization and reporting that take advantage of MySQL Server 5.6
and 5.7 Performance Schema, and enhancements to the MySQL Explain Plans.

Additionally Workbench 6.1 is leveraging work from various teammates in
MySQL Engineering by introducing a schema called “SYS” that provides
simplified views on …

We recently introduced 'mysqlrplsync' in MySQL Utilities release-1.4.2 RC. This new utility allows users to check the data consistency of an active replication system. In this blog we provide more details about how 'mysqlrplsync' works.

In an active replication topology, slaves may be slightly behind the master in processing events. Depending on the workload and capabilities of each slave, transactions may be applied at different times. Should this occur and something untoward happen to one of the slaves (such as a user making a manual change directly on the slave), a synchronization process may be required to ensure that the slaves have the same data - to manually catch up all of the slaves that are behind the master.

The strategy we choose was to build on the top of the replication process and makes use of GTIDs; it works independently of the binary log format (row, statement, or mixed) and does not create any new data …

Github user Adrianlzt provided a python-twisted alternative version of pyclustercheck per discussion on issue 7.

Due to sporadic performance issues noted with the original implementation in SimpleHTTPserver, the benchmarks which I’ve included as part of the project on github use mutli-mechanize library,

• cache time 1 sec
• 2 x 100 thread pools
• 60s ramp up time
• 600s total duration
• testing simulated node fail (always returns 503, rechecks mysql node on cache expiry)
• AMD FX(tm)-8350 Eight-Core Processor
• Intel 330 SSD
• local loop back test (127.0.0.1)

The SimpleHTTPServer instance faired as follows:

…