SM SKP ADS-1
Planet MySQL - http://www.planetmysql.org/
Loss-less failover using MySQL semi-syncronous replication and MySQL Fabric!
In my last post playing with MySQL fabric here and here I used MySQL fabric version 1.4.1 and MySQL 5.6, i must say lot's of things have happened over the last 20 months! MySQL have released several new versions of MySQL Fabric and also a new version of the server with MySQL 5.7.Current GA version of MySQL Fabric is 1.5.6 and there is also a MySQL Fabric 1.6.2 beta, both versions can be downloaded from here.As I said earlier, lots of new things have happened, looks like focus has been on making it easier to work with MySQL Fabric but also making output from fabric commands much easier to understand. With latest version 1.6.2 (not yet GA) fabric also have support for running multiple fabric instances, more news can be found here.Semi-synchronous replicationSemi-synchronous replication is an enhancement to normal replication making it possible to failover to a slave server without losing any data. In MySQL 5.7 we have introduced some changes making this feature even better, more details can be found here . So, how can we use semi-synchronous replication together with MySQL Fabric? Well it's not that hard to configure, just a few commands as explained in our manual, but hey let's try to upgrade the old MySQL fabric environment and make it use semi-synchronous replication!Upgrading environment to MySQL 5.7Some things in installation/configuration had to be modified moving from MySQL 5.6 to 5.7:installation process has changed a lot (more secure by default), if you want old behavior use --initialize-insecure to get back some old behavior.server-id is now mandatory if you enble binary logging, more information here and here.For more information on what new in MySQL 5.7 and some removed or deprecated functionality read more here and here.MySQL FabricNext step is to install MySQL Fabric, I will not cover the steps as this was covered on my old posts and the procedure for latest version of MySQL fabric is explained in detail in our manual.Scenario is, we have MySQL Fabric up and running and 3 stand alone MySQL 5.7 servers waiting to be used by MySQL Fabric.First step is to create our group and populate it with our 3 MySQL servers. We also run promote command to elect a primary server, the activate command enables automatic failover is primary server dies.mysqlfabric group create group1mysqlfabric group add group1 127.0.0.1:63301mysqlfabric group add group1 127.0.0.1:63302mysqlfabric group add group1 127.0.0.1:63303mysqlfabric group promote group1mysqlfabric group activate group1Let's look at current status of our group of servers:ted@ted-PORTEGE-Z30-A:/opt/MySQL-fabricII$ mysqlfabric group lookup_servers group1Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249eTime-To-Live: 1 server_uuid address status mode weight------------------------------------ --------------- --------- ---------- ------8066541b-9423-11e5-ab31-7c7a910307fa 127.0.0.1:63301 PRIMARY READ_WRITE 1.0839f04ac-9423-11e5-ab4d-7c7a910307fa 127.0.0.1:63302 SECONDARY READ_ONLY 1.08681ea65-9423-11e5-ad0f-7c7a910307fa 127.0.0.1:63303 SECONDARY READ_ONLY 1.0All looks fine, our servers are up and running and group is ready!Enabling Semi-synchronous replicationThis whole procedure can be done without restarting our secondary server, for safety we set the secondary server in SPARE mode so it might not be elected as primary on case of failover whilst we are working with the server.On Primary Server load semi-sync plugin and enable semi-sync replication:mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;On one or both of the secondary servers; load semi-sync plugin, enable semi-sync replication, set server in SPARE mode and restart replication channel.mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;mysqlfabric server set_status <server-uuid> SPAREmysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;mysqlfabric server set_status <server-uuid> SECONDARYVerify semi-synchronous replication is up and running on slaves:mysql> show status like 'Rpl_semi_sync_slave_status';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | ON |+----------------------------+-------+We are done, you now have semi-sync replication up and running in your group!My MySQL Fabric wish list for the future is:Feature request Semisynchronous replication support in MySQL Fabric.Fabric GUI section in workbench for managing my groups of servers. I would also like to extend this functionality to handle basic monitoring and help to architect/deploy groups or failed servers.Support for MySQL Group Replication in fabric, so we have three different ways of replicating data between servers in our HA-groups.
JSON memory consumption
I got some more questions on the new JSON data type and functions during our TechTours. And I like to summarize the answers in this blogpost.Memory consumption The binary format of the JSON data type should consume more memory. But how much? I did a little test by comparing a freshly loaded 25,000 row dataset stored as JSON and stored as TEXT. Seven top level attributes per JSON document. Average JSON_DEPTH is 5.9 . Let's see:mysql> DESC data_as_text;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || doc | text | YES | | NULL | |+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> SELECT COUNT(*),AVG(JSON_LENGTH(doc)) FROM data_as_text;+----------+-----------------------+| COUNT(*) | AVG(JSON_LENGTH(doc)) |+----------+-----------------------+| 25359 | 7.0000 |+----------+-----------------------+1 row in set (0.81 sec)mysql> DESC data_as_json;+-------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || doc | json | NO | | NULL | |+-------+---------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> SELECT COUNT(*),AVG(JSON_LENGTH(doc)) FROM data_as_json;+----------+-----------------------+| COUNT(*) | AVG(JSON_LENGTH(doc)) |+----------+-----------------------+| 25359 | 7.0000 |+----------+-----------------------+1 row in set (0.08 sec)mysql> select name,allocated_size/1024/1024 AS "size in MB" from information_schema.innodb_sys_tablespaces where name like "%temp%";+-------------------+-------------+| name | size in MB |+-------------------+-------------+| temp/data_as_json | 23.00390625 || temp/data_as_text | 22.00390625 |+-------------------+-------------+2 rows in set (0.00 sec)The increased memory consumption is 1/22 in this case, which is roughly 4,5%. At the same time you see the benefit: The full table scan with some JSON operation has a 90% reduction in runtime when using JSON datatype.Don't take this number for real. Of course it depends on the number of JSON attributes, character set and others. Just a rough indication. If you want all the details look at the JSON architecture in WL#8132.
A Gratitude List
A gratitude list is one of my favorite ways to influence my mood. Here’s a
gratitude list on Thanksgiving Day 2015 (United States) to express some of the
reasons we are so fortunate at VividCortex to have great customers, employees,
investors, advisors, friends, vendors, community, and more. This list will omit
lots of things and people equally worthy of inclusion, but that’s the point–a
gratitude list only makes you aware of how much larger than 26 the list really
Amazon Web Services, without whom VividCortex would have been
much harder to get off the ground.
Bugsnag, which helps us diagnose many confusing problems our customers
experience but we can’t reproduce.
Customers. All of them.
Dyn. A special customer who took a chance on us before we were a “safe”
service to risk trying.
Employees, past, present, and future (we’re hiring).
Free Software. As in beer, and as in speech. Several of us are FSF members.
Go, which has probably reduced our technical overhead by 50% through
compounding effects in various ways.
HighScalability.com for being an inspiration and for cultivating interest in
the disciplined pursuit of better performance.
Intercom for connecting us and our customers more directly and
frictionlessly. Customers consistently praise “our” support chat.
Julien Schmidt and Arne Hormann for writing the MySQL driver for Go that we
Kate Matsudaira for advice, inspiration, and encouragement from the first
early ideas onward.
LinkedIn for connecting the world of business in new ways.
MySQL for so many reasons.
Nespresso several times a day.
Open Source is this a duplicate of Free Software? Doesn’t matter.
Percona to whom I personally owe so much.
Quorum Algorithms and Raft for making possible many distributed systems
such as Consul and Zookeeper, both of which we use. Also for being cool.
Static Site Generators which is why we haven’t been hacked and turned into
an unwilling assistant of some nefarious corner of the Internet.
Time Off to reflect like today.
Uber for ferrying us around between venture capitalists and customers and
conferences and billiards.
VictorOps for being our event timeline and communications backbone when
things get a little dicey.
WCGW gifs to keep it light-hearted on the chat channel (and give us more
reasons to be thankful).
X might be an eXercise for the reader, or it could be for the oXford Comma.
Y is also coming up blank for me right now.
Zlib for helping reduce our data ingest and Kafka disk space usage.
Smallest MySQL Server in the World (under $60) which can even make you toast while fixing MySQL bug #2
In my last blog post, Internet of Things, Messaging and MySQL, I have showed how to start your own Internet of Things with Particle Photon board. That implementation is great, but requires constant internet (wi-fi) access as the Particle Photon board does not have any local storage. If you do not have a reliable network access (i.e. in some remote places) or need something really small to store your data you can now use Intel Edison. I’ve even install MySQL on Edison, which makes it the smallest (in size) MySQL server in the world! Other options include:
Raspberry Pi (my colleague Roel installed Percona Server on the Raspberry Pi)
Android based system (i.e. Nexus 7: Vadim installed Percona Server on Nexus 7)
Intel Edison is a tiny computer based on 22 nm Silvermont dual-core Intel Atom CPU, 500MHz, running Linux (Ubuntu based distribution called Yocto). To program the Edison we will need a breakout board. Options include Arduino compatible breakout board (which includes SD card) and a small Intel breakout board.
The installation and configuration is straightforward. I’ve used the Get Started with Yocto Project on the Intel Edison Board guide to setup and configure the board. First we need to connect to Edison via serial connection and configure sshd and Wi-Fi; when it is done we can connect to Edison using SSH.
The MySQL installation is relatively easy as Linux generic binaries are compatible with Yocto Linux (so you do not have to compile anything). There are 2 challenges though:
By default the Yocto linux (as well as the official repository) does not include libraries needed for MySQL: libaio1, libcrypto, libssl
The internal storage is tiny and MySQL 5.7 binaries did not even fit into any partition. I had to remove some “tests” and other stuff I do not need. For the real installation one can use SD card (SD slot is available on some boards).
To install the libraries I’ve used the un-official Edison repositories following the excellent guide: Edison package repo configuration. Setup is simple:
To configure your Edison to fetch packages from this repo, replace anything you have in /etc/opkg/base-feeds.conf with the following (other opkg config files don’t need any changes):
src/gz all http://repo.opkg.net/edison/repo/all
src/gz edison http://repo.opkg.net/edison/repo/edison
src/gz core2-32 http://repo.opkg.net/edison/repo/core2-32Then we will need to setup the libraries:# opkg install libaio1_0.3 libcrypto1.0.0 libssl1.0.0Finally we can download Percona Server 5.6 and place it somewhere (use basedir in my.cnf to point to the installation path):# wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.25-73.1/binary/tarball/Percona-Server-5.6.25-rel73.1-Linux.i686.ssl100.tar.gzPlease note that the latest Percona Server 5.6 depends on the Numa library and there is no such library for Yocto (does not make sense for Edison). So 5.6.25 is the latest Percona Server you can install here.
The simple (and rather useless) benchmark on Intel Edison:root@edison:/usr/local/mysql# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 74
model name : Genuine Intel(R) CPU 4000 @ 500MHz
mysql> SELECT BENCHMARK(10000000,ENCODE('hello','goodbye'));
| BENCHMARK(10000000,ENCODE('hello','goodbye')) |
| 0 |
1 row in set (18.77 sec)Can MySQL make you toast?
The famous MySQL Bug#2, submitted 12 Sep 2002, states that “MySQL Connector/J doesn’t make toast”. With Intel Edison and Arduino compatible breakout board it is now trivial to fix this bug: not only MySQL Connector/J but also MySQL server itself can make you a toast! This can be done via UDF or, in MySQL 5.7, with Query Rewrite Plugins, so you can execute MySQL query:mysql> make toast;For the actual implementation you can either “hack” an existing toaster to interface with breakout board pins or use a Arduino compatible Robotic Arm. Ok, MySQL, make me toast!
The post Smallest MySQL Server in the World (under $60) which can even make you toast while fixing MySQL bug #2 appeared first on MySQL Performance Blog.
Press Release: Severalnines aims for the stars with EGO boost
Swedish database technology ensures intergalactic research data protected “to infinity and beyond” for scientists
Stockholm, Sweden and anywhere else in the world - Severalnines, the provider of database infrastructure management software, today announced its latest customer, the European Gravitational Observatory (EGO). Severalnines’ ClusterControl platform is used to manage and monitor the operations of some of EGO’s open source databases and help scientists to track, analyse and share data gathered from research on gravitational waves.
EGO is one of the world’s leading research structures devoted to the study of gravitational waves. EGO hosts the Virgo experiment, a project involving about 300 scientists spread across 19 laboratories in France, Italy, Poland, the Netherlands and Hungary. The observatory also collaborates with a wider network of scientists based in the United States and Asia. Gravitational waves were predicted one century ago by Einstein's theory of General Relativity but they have never been observed directly, although there is indirect evidence of their existence. The direct detection of gravitational waves, the main goal of experiments such as Virgo, will solve one of the long-standing puzzles of Einstein's theory and provide a new powerful tool to observe the Universe.
Research on gravitational waves creates at least 6 Terabytes of new data every day. The data gathered is used for scientific research into gravitational waves and to help the development of the cutting-edge technologies used by Virgo, which span various fields, from mechanics to optics and electronics.
The scale of data created on a daily basis provides challenges for EGO’s IT team which has to ensure research data is not lost either when at rest or in transfer. The IT team needed a robust technology which could work with various open and proprietary IT environments, and around-the-clock access to database performance and management expertise.
After a review process, Severalnines was selected by EGO in January 2015 after the team read a series of strong online reviews on how Severalnines’ ClusterControl platform manages MySQL database clusters. EGO tested ClusterControl on its Scientific Linux platform, based on the popular Red Hat system, during the trial period. The ease of installation during testing was another reason why EGO’s IT team decided to work with Severalnines beyond the trial period.
With ClusterControl, EGO can manage and monitor all database clusters on a single dashboard and reduce time spent on backing-up research data. Severalnines helped EGO to focus on new business initiatives such as improving the sharing and presentation of research data on various graphical formats.
Giuseppe Di Biase, Systems Administrator at EGO, said: “Data is so important to the work EGO carries out on a daily basis. Without it, important scientific discoveries based on gravitational waves may be missed. The Severalnines team offered excellent, personalised support and gave us practical advice on how to enhance our systems. I still learn something new every time I talk to Severalnines, they have made my job easier .”
Vinay Joosery, Severalnines CEO said: “ We are very proud at Severalnines to support the leading edge of science with the most advanced database management software. The scale of EGO’s operation is tremendous and it relies on stable and robust technology, we look forward to taking this relationship to another world.”
Severalnines provides automation and management software for database clusters. We help companies deploy their databases in any environment, and manage all operational aspects to achieve high-scale availability.
Severalnines' products are used by developers and administrators of all skills levels to provide the full 'deploy, manage, monitor, scale' database cycle, thus freeing them from the complexity and learning curves that are typically associated with highly available database clusters. The company has enabled over 7,000 deployments to date via its popular online database configurator. Currently counting BT, Orange, Cisco, CNRS, Technicolor, AVG, Ping Identity and Paytrail as customers. Severalnines is a private company headquartered in Stockholm, Sweden with offices in Singapore and Tokyo, Japan. To see who is using Severalnines today visit, http://www.severalnines.com/company
About the European Gravitational Observatory
The European Gravitational Observatory (EGO) is a French-Italian private Consortium founded by a joint agreement between the French Centre National de la Recherche Scientifique (CNRS) and the Italian Istituto Nazionale di Fisica Nucleare (INFN). Both these research institutions equally finance the running of the Consortium since its foundation in December 2000.
In 2009 EGO was pleased to welcome the Dutch Institute for Subatomic Physics (Nikhef) as Observer within its Council. Today about 60 people work at the headquarters of EGO in Cascina, a small town about 10 kilometers from Pisa, in the Italian region of Tuscany. The main purpose of EGO is to ensure the construction of Advanced Virgo and to guarantee its operations, maintenance, and upgrade, as well as the exploitation of scientific data. EGO is integrated to the Virgo Collaboration by providing a large and crucial support to the experiment and is fully responsible for the site of the Virgo interferometer. Another key goal of EGO is to promote the studies on gravitational physics in Europe and to be on the frontline of the gravitational wave research.
Fore more information, contact Jean-Jérôme Schmidt: firstname.lastname@example.org
Blog category: Company NewsTags: big dataclustercontrolDatabaseMySQL