Personal blog of Yzmir Ramirez

 Three members of the senior investing team at Genesis Partners, a major Israeli venture firm, are stepping out with a new, early-stage fund of their own called F2 Capital. For the unfamiliar, Genesis Partners’ portfolio companies have been acquired by the likes of Apple, IBM, Microsoft and Sapiens over the past decade, and have gone public on the Nasdaq exchange in the U.S. as well.… Read More

This post describes how to recover Galera Cache (or gcache) on restart.

Recently Codership introduced (with Galera 3.19) a very important and long awaited feature. Now users can recover Galera cache on restart.

Need

If you gracefully shutdown cluster nodes one after another, with some lag time between nodes, then the last node to shutdown holds the latest data. Next time you restart the cluster, the last node shutdown will be the first one to boot. Any followup nodes that join the cluster after the first node will demand an SST.

Why SST, when these nodes already have data and only few write-sets are missing? The DONOR node caches missing write-sets in Galera cache, but on restart this cache is wiped clean and restarted fresh. So the DONOR node doesn’t have a Galera cache to donate missing write-sets.

This painful set up made it necessary for users to think and plan before gracefully taking down the cluster. With the introduction of this new feature, the user can retain the Galera cache.

How does this help ?

On restart, the node will revive the galera-cache. This means the node can act as a DONOR and service missing write-sets (facilitating IST, instead of using SST). This option to retain the galera-cache is controlled by an option named

gcache.recover=yes/no

. The default is NO (Galera cache is not retained). The user can set this option for all nodes, or selective nodes, based on disk usage.

gcache.recover in action

The example below demonstrates how to use this option:

• Let’s say the user has a three node cluster (n1, n2, n3), with all in sync.
• The user gracefully shutdown n2 and n3.
• n1 is still up and running, and processes some workload, so now n1 has latest data.
• n1 is eventually shutdown.
• Now the user decides to restart the cluster. Obviously, the user needs to start n1 first, followed by n2/n3.
• n1 boots up, forming an new cluster.
• n2 boots up, joins the cluster, finds there are missing write-sets and demands IST but given that n1 doesn’t have a gcache, it falls back to SST.

n2 (JOINER node log):

2016-11-18 13:11:06 3277 [Note] WSREP: State transfer required:
 Group state: 839028c7-ad61-11e6-9055-fe766a1886c3:4680
 Local state: 839028c7-ad61-11e6-9055-fe766a1886c3:3893

n1 (DONOR node log),

gcache.recover=no

:

2016-11-18 13:11:06 3245 [Note] WSREP: IST request: 839028c7-ad61-11e6-9055-fe766a1886c3:3893-4680|tcp://192.168.1.3:5031
2016-11-18 13:11:06 3245 [Note] WSREP: IST first seqno 3894 not found from cache, falling back to SST

Now let’s re-execute this scenario with

gcache.recover=yes

.

n2 (JOINER node log):

2016-11-18 13:24:38 4603 [Note] WSREP: State transfer required:
 Group state: ee8ef398-ad63-11e6-92ed-d6c0646c9f13:1495
 Local state: ee8ef398-ad63-11e6-92ed-d6c0646c9f13:769
....
2016-11-18 13:24:41 4603 [Note] WSREP: Receiving IST: 726 writesets, seqnos 769-1495
....
2016-11-18 13:24:49 4603 [Note] WSREP: IST received: ee8ef398-ad63-11e6-92ed-d6c0646c9f13:1495

n1 (DONOR node log):

2016-11-18 13:24:38 4573 [Note] WSREP: IST request: ee8ef398-ad63-11e6-92ed-d6c0646c9f13:769-1495|tcp://192.168.1.3:5031
2016-11-18 13:24:38 4573 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

You can also validate this by checking the lowest write-set available in gcache on the DONOR node.

mysql> show status like 'wsrep_local_cached_downto';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| wsrep_local_cached_downto | 1 |
+---------------------------+-------+
1 row in set (0.00 sec)

So as you can see,

gcache.recover

 could restore the cache on restart and help service IST over SST. This is a major resource saver for most of those graceful shutdowns.

gcache revive doesn’t work if . . .

If gcache pages are involved. Gcache pages are still removed on shutdown, and the gcache write-set until that point also gets cleared.

Again let’s see and example:

• Let’s assume the same configuration and workflow as mentioned above. We will just change the workload pattern.
• n1, n2, n3 are in sync and an average-size workload is executed, such that the write-set fits in the gcache. (seqno=1-x)
• n2 and n3 are shutdown.
• n1 continues to operate and executes some average size workload followed by a huge transaction that results in the creation of a gcache page. (1-x-a-b-c-h) [h represent transaction seqno]
• Now n1 is shutdown. During shutdown, gcache pages are purged (irrespective of the

  keep_page_sizes

   setting).

• The purge ensures that all the write-sets that has seqno smaller than

  gcache-page-residing

   write-set are purged, too. This effectively means (1-h) everything is removed, including (a,b,c).

• On restart, even though n1 can revive the gcache it can’t revive anything, as all the write-sets are purged.
• When n2 boots up, it requests IST, but n1 can’t service the missing write-set (a,b,c,h). This causes SST to take place.

Summing it up

Needless to say,

gcache.recover

 is a much needed feature, given it saves SST pain. (Thanks Codership.) It would be good to see if the feature can be optimized to work with gcache pages.

And yes, Percona XtraDB Cluster inherits this feature in its upcoming release.

In this blog post, I’ll discuss how to use the InnoDB buffer pool pre-load feature in MySQL 5.7

Starting MySQL 5.6, you can configure MySQL to save the contents of your InnoDB buffer pool and load it on startup. Starting in MySQL 5.7, this is the default behavior. Without any special effort, MySQL saves and restores a portion of buffer pool in the default configuration. We made a similar feature available in Percona Server 5.5 – so the concept has been around for quite a while.

Frankly, time has reduced the need for this feature. Five years ago, we would typically store databases on spinning disks. These disks often took quite a long time to warm up with normal database workloads, which could lead to many hours of poor performance after a restart. With the rise of SSDs, warm up happens faster and reduces the penalty from not having data in the buffer pool. Typically, a system reaches 90% of its fully warmed up performance in 10 minutes or less. But since it takes virtually no effort to use, saving the contents of the InnoDB buffer pool is a great feature to enable by default.

This blog post looks into some issues with this feature that might not be totally obvious from its name or documentation.

#1 

By default, MySQL only saves 25% of the most actively accessed pages (by the LRU) in the InnoDB buffer pool (not the whole buffer pool).

This is a reasonable choice for many use cases: it saves the most valuable pages, which can then be loaded faster than if you try to load every page in the buffer pool (many of which might not be relevant for continuing workload).

You can change this number by setting the

innodb_buffer_pool_dump_pct

 variable. If you’re using InnoDB essentially as an in-memory database, and want to ensure all data is memory resident and can be accessed without a disk read, set it to 100.     

Note that this variable is based on the actual amount of data present in memory, not the buffer pool size, For example, if you have a 100GB buffer pool but it only contains 10GB of data, by default only 25% of 10GB (2.5GB) gets saved. (As the manual explains, it will not take nearly as much on disk as only the page identifiers are stored, not full page contents.)

#2

MySQL starts and becomes accessible through the network before the buffer pool load on startup is complete. Immediately before the start, a lot of resources fetch buffer pool contents from the disk as quickly as possible, possibly affecting performance. If you have multiple MySQL nodes – like using MySQL Replication or running Percona XtraDB Cluster – you might consider bringing them back for production traffic only after the buffer pool load operation completes. You can monitor the buffer pool load progress by watching the GLOBAL STATUS variable:

Buffer pool load is in progress:

| Innodb_buffer_pool_load_status          | Loaded 403457/419487 pages         |

Buffer pool load is complete:

| Innodb_buffer_pool_load_status          | Buffer pool(s) load completed at 161123  9:18:57 |

As a side note, it would be great if MySQL would provide a clearer concept of the “State” of the node: being UP versus being READY to serve the traffic in an optimal way are often not the same.

#3

InnoDB’s buffer pool preload is not very efficient, at least with fast storage. In my test environment, with a rather capable NVMe storage, I get a more than 400MB/sec warmup rate if I run read-only sysbench workload. The InnoDB’s buffer pool preload warmup rate is around 100MB/sec or so.  I would guess the problem is that it doesn’t drive as many parallel IO requests as SSD storage needs to perform optimally. I did not investigate it further.

#4

Innodb buffer pool save/restore only stores the buffer pool contents on a clear shutdown.  If the server crashes MySQL still does a buffer pool preload, but with the content information saved on last clean shutdown (stored in the ib_buffer_pool  file). This might end up wasting time loading data that is not relevant for the current workload. Periodically running the following ensures a fresh set of pages is available for a quick warmup, even if MySQL crashed:

SET GLOBAL innodb_buffer_pool_dump_now=ON;

This preserves the current list of buffer pool pages.

Note that while you (hopefully) do not see your MySQL crash that often, the same issue exists with backups, MySQL slave cloning with Percona XtraBackup, or LVM snapshot. This causes these operations to be less efficient.

I hope the observations in this blog help you put this feature to better use!

 Amazon’s popular Snowball storage container got a major update today at the company’s re:invent conference. Though largely overshadowed by the new batshit crazy AWS Snowmobile, the aforementioned Snowball will be getting a storage increase to 100 terabytes in addition to computing functionality. Users of the new Snowball Edge will be able to perform basic analysis on their data… Read More

 Meet AWS Snowmobile, a tractor-trailer for when your big data is just too damn big. The truck houses a container that can store up to 100 petabytes of data. Real-life data hoarders can contract Amazon to move exabytes of data to the cloud using the new tricked-out trucks. Snowmobile attaches directly to your data center with power and network fibre to move critical information to AWS,… Read More

 There were a lot of jokes and comments at Oracle’s expense today at the AWS re:Invent conference, but perhaps the boldest statement came when AWS announced it  was adding PostgresSQL support to the AWS Aurora database, making it easier to move an Oracle database to the AWS cloud. Even as Oracle makes its own move to the cloud, it is still held up as the prime example of the… Read More

 Google today announced the launch of App Maker, the newest entry in the low-code, drag-and-drop app building market. Like its competitors from Microsoft and numerous startups, App Maker promises to make it easy for anybody to quickly develop basic apps that serve a very specific purpose inside an organization. The new service features a cloud-based drag-and-drop development environment that… Read More

 Amazon kicked off its 2016 AWS re:Invent conference with a peppering of announcements regarding its EC2 instance roadmap. The company dropped an updated R4 instance, expanded T2 instances, and a new F1 instance. The F1 utilizes field-programmable gate arrays (FPGAs) to offer a solution that is faster than traditional CPU/GPU combinations for quick reprogramming. While those new instances… Read More

In episode 133 we talk about something that everyone does when building and supporting websites, debugging.

Show Notes

• Can you determine where the issue originates from? Is it front end, backup, css, or a module.
• RIR Strategy – Reproduce, Isolate and Resolve.
• Reproduce
  • Gather pertinent information to help resolve a bug quicker
  • Train your customer and provide a bug report form to help them provide you the best information.
  • When trying to reproduce an issue, make sure your development/QA enviroments mirror the production environment. For example, do you PHP version match? Listen to show #127 Local Development with Vagrant.
  • If an issue cannot be reproduced in a development environment, look at the server configuration and data.
• Isolating
  • When the issue can be reproduced, isolating it is the next step.
  • Tools and techniques you can use to help
    • Drupal and server logs – check your log files first.
    • Turn on error reporting for PHP to display errors
    • Inspector – use the Console and Networking sections in the inspector. This will help identify front end issues. I common issue is javascript not loading from third parties.
    • What are the last things that changed? Looking back at the most recent code or configuration changes will uncover the source of a bug.
    • Peer Programming – work with someone else to resolve a problem. Often, just explaining it will help resolve it.
    • Walk Away – Taking a short break from a difficult bug will you have fresh perspective.
    • Note Pad – Take notes during the research will help you organize your isolation process.
• Resolve
  • Fix it.
  • Think about how you can prevent this in the future.
  • Document it.

Module of the Week

Search API – https://www.drupal.org/project/searchapi

This module provides a framework for easily creating searches on any entity known to Drupal, using any kind of search engine. For site administrators, it is a great alternative to other search solutions, since it already incorporates faceting support and the ability to use theViews module for displaying search results, filters, etc. Also, with the Apache Solr integration, a high-performance search engine is available for this module.

Developers, on the other hand, will be impressed by the large flexibility and numerous ways of extension the module provides. Hence, the growing number of additional contrib modules, providing additional functionality or helping users customize some aspects of the search process.

Bonus Content

Download Sample Bug Report PDF and Webform version.

Hosts

Stephen Cross – www.ParallaxInfoTech.com @stephencross

John Picozzi – www.oomphinc.com @johnpicozzi

Nic Laflin – www.nLightened.net @nicxvan

 

In episode 133 we talk about something that everyone does when building and supporting websites, debugging.

Show Notes

• Can you determine where the issue originates from? Is it front end, backup, css, or a module.
• RIR Strategy – Reproduce, Isolate and Resolve.
• Reproduce
  • Gather pertinent information to help resolve a bug quicker
  • Train your customer and provide a bug report form to help them provide you the best information.
  • When trying to reproduce an issue, make sure your development/QA enviroments mirror the production environment. For example, do you PHP version match? Listen to show #127 Local Development with Vagrant.
  • If an issue cannot be reproduced in a development environment, look at the server configuration and data.
• Isolating
  • When the issue can be reproduced, isolating it is the next step.
  • Tools and techniques you can use to help
    • Drupal and server logs – check your log files first.
    • Turn on error reporting for PHP to display errors
    • Inspector – use the Console and Networking sections in the inspector. This will help identify front end issues. I common issue is javascript not loading from third parties.
    • What are the last things that changed? Looking back at the most recent code or configuration changes will uncover the source of a bug.
    • Peer Programming – work with someone else to resolve a problem. Often, just explaining it will help resolve it.
    • Walk Away – Taking a short break from a difficult bug will you have fresh perspective.
    • Note Pad – Take notes during the research will help you organize your isolation process.
• Resolve
  • Fix it.
  • Think about how you can prevent this in the future.
  • Document it.

Module of the Week

Search API – https://www.drupal.org/project/searchapi

This module provides a framework for easily creating searches on any entity known to Drupal, using any kind of search engine. For site administrators, it is a great alternative to other search solutions, since it already incorporates faceting support and the ability to use theViews module for displaying search results, filters, etc. Also, with the Apache Solr integration, a high-performance search engine is available for this module.

Developers, on the other hand, will be impressed by the large flexibility and numerous ways of extension the module provides. Hence, the growing number of additional contrib modules, providing additional functionality or helping users customize some aspects of the search process.

Bonus Content

Download Sample Bug Report PDF and Webform version.

Hosts

Stephen Cross – www.ParallaxInfoTech.com @stephencross

John Picozzi – www.oomphinc.com @johnpicozzi

Nic Laflin – www.nLightened.net @nicxvan