How To Upgrade PHP on AWS Linux

Author: , Posted on Thursday, August 22nd, 2019 at 10:26:04am

As root:

~or~

THEN:

Be sure to restart your web server!!

How to use Round-Robin Load Balancing with the Tungsten Connector

Author: , Posted on Thursday, August 8th, 2019 at 9:36:40am

Overview

The Skinny

Part of the power of Tungsten Clustering for MySQL / MariaDB is its intelligent MySQL Proxy, known as the Tungsten Connector. The Tungsten Connector has built-in read-write splitting capabilities, and it is also possible to configure different algorithms which select the appropriate slave (i.e. Round-Robin or Lowest-Latency).


The Question

Recently, a customer asked us:

How do we best share the load between read-only slaves? Currently, there appears to be an imbalance, with most of the read-only queries reaching just one slave. What may we do to improve this situation?

This customer noticed that a couple of long running, slow queries were running against one particular slave node.

Despite the increased CPU utilization caused by the expected, long running queries, the “quick” Read-Only requests would run against this same slave node causing what the customer referred to as “pilling up”, otherwise known as a bottleneck.


The Background

What IS the default Load Balancer Model
  • The default Load Balancer Model setting for the Tungsten Connector is MostAdvancedSlaveLoadBalancer.
  • This means that Read-Only operations are always reading from the most up-to-date slave in the cluster.
  • The MostAdvancedSlaveLoadBalancer Load Balancer Model setting selects the slave data source that has the lowest replication lag, or highWater in the ls -l output within cctrl.
  • If no slave data source is eligible, the master data source will be selected.
  • In a cluster experiencing rapid changes in latency, the 3-second (default) polling interval will be too low. This will result in higher CPU utilization and slower-running queries, which means that the MostAdvancedSlaveLoadBalancer can potentially create a bottleneck.

The Answer

Change the Load Balancer Model

It is possible to change the Tungsten Connector’s Load Balancer Model.

To more evenly distribute the workload (therefore resolving the bottleneck), Continuent recommends changing the Load Balancer Model from MostAdvancedSlaveLoadBalancer to RoundRobinSlaveLoadBalancer.

Why choose round-robin instead?

  • The RoundRobinSlaveLoadBalancer Load Balancer Model setting uses all available slave nodes, each in turn.
  • When the end of the slave node list is reached, the load balancer starts from the beginning of the list and repeats the loop.

Why NOT choose round-robin?

  • Consideration should be given to the fact that with the RoundRobinSlaveLoadBalancer, it is possible that a Read-Only request may read older/stale data from a slave that is not as caught-up as another slave.
  • It is also possible for the calling client to see slower response times due to slave CPU load

To change the Connector Load Balancer model, simply specify the property in the [defaults] stanza of your tungsten.ini file:


The Library

Please read the docs!

For more information the Load Balancer Model setting for the Tungsten Connector, please visit the docs page at https://docs.continuent.com/tungsten-clustering-6.0/connector-routing-loadbalancers.html

For more information about Tungsten clusters, please visit https://docs.continuent.com


Summary

The Wrap-Up

In this blog post we discussed changing the Load Balancer Model setting for the Tungsten Connector to a Round-Robin model, as well the behavioral changes that should be considered before making this change.

Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

For more information, please visit https://www.continuent.com/solutions

Want to learn more or run a POC? Contact us.

Enabling Autorecovery for the Tungsten Replicator

Author: , Posted on Wednesday, August 7th, 2019 at 10:58:04am

The Replicator is a critical piece of the Tungsten Clustering solution for MySQL / MariaDB, as well as its own stand-alone data replication product. Automatic recovery is a feature that enables the Replicator to go back online in the event of a transient failure. In this blog we discuss how to enable Automatic Recovery. For more information about Auto-Recovery, please click here to visit the online documentation page.

The Question

Recently, a customer asked us:

We see that the replicators receive a transaction which has a deadlock error in it:

If one performs a service online, it comes back online without issue and continues to replicate. Is there a setting that will allow the cluster (if in automatic mode) to try to online the service on its own (maybe try 2 or 3 times then stop trying if it continuously errors)?


The Answer

Yes! It’s called Automatic Recovery.

The following tpm options can be added to the [defaults] stanza of your tungsten.ini file:

These settings will enable autorecovery, the maximum number of attempts to online the replicator. These settings will also identify the delay between the replicator identifying that autorecovery is needed and an auto-recovery is being attempted.

The Library

Please read the docs!

For more information about Auto-Recovery, please visit the documentation page at https://docs.continuent.com//tungsten-replicator-6.1/operations-autorecovery.html

For more information about Tungsten Replicator, please visit our full online documentation at https://docs.continuent.com


Summary

The Wrap-Up

In this blog post we discussed enabling Automatic Recovery for the Tungsten Replicator.

To learn more about Tungsten Replicator, check out https://www.continuent.com/solutions/tungsten-replicator-all-you-need-to-know/

Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

To learn about Continuent Clustering solutions, please visit https://www.continuent.com/solutions

Want to learn more or run a POC? Contact us

How To Prevent Vim From Auto-Commenting New Lines

Author: , Posted on Sunday, August 4th, 2019 at 1:12:42pm

To prevent vim from added net comments, edit the .vimrc file in your home directory, and add:

How To Enable Variable References in Perl While Using Strict

Author: , Posted on Sunday, August 4th, 2019 at 1:07:29pm

I wanted to call a sub-routine based on a variable in Perl, like this:

but it failed because of use strict:

To enable using a variable as a reference, simply specify that to Perl:

How To Print Matching Line and All After Using awk

Author: , Posted on Thursday, August 1st, 2019 at 4:53:41pm

Every time I use awk, my respect for it grows! Like grep, find and rsync, it is one of the powerhouse command-line tools in my arsenal.

Recently, I needed to list all MySQL binary log files from a starting file forwards in sequence. AWK to the rescue!

For example, I knew the starting file was mysql-bin.000325, and also that there is a list of binary log files kept by MySQL called mysql-bin.index, so I executed the following to extract the lines I wanted out of the 76 available in the file:

Per the man page:
“Two patterns separated by a comma is called a range pattern. With it, once the first pattern is matched, every subsequent record matches until the second pattern is matched.”

We can call the first argument the “starting match pattern” and the second argument the “ending match pattern“.

Awk will start to print lines (the default behavior for a match) when it finds the starting match pattern and end after it finds and prints the ending match pattern line.

Since the ending match pattern is zero, it will never be true and so awk simply prints all remaining lines in the source.

Tungsten Clustering 5.4.0 and Tungsten Replicator 5.4.0 Released

Author: , Posted on Wednesday, July 31st, 2019 at 2:09:23pm

Continuent is pleased to announce that the following new software releases are now available:

  • Tungsten Clustering version 5.4.0
  • Tungsten Replicator version 5.4.0

Releases 5.4.0 is significant in that it introduces MySQL 8 support, along with many new features, stability improvements and bug fixes.

Highlights common to both products:

Improvements, new features and functionality

  • Two new utility scripts have been added to the release to help with setting the Replicator position:
    • tungsten_find_position, which assists with locating information in the THL based on the provided MySQL binary log event position and outputs a dsctl set command as output.
    • tungsten_find_seqno, which assists with locating information in the THL based on the provided sequence number and outputs a dsctl set command as output.
  • A new, beta-quality command has been included called prov-sl.sh which is intended to eventually replace the current tungsten_provision_slave script.
    Currently, prov-sl.sh supports provisioning slaves using mysqldump and xtrabackup tools, and is MySQL 8-compatible.  
The prov-sl.sh command is written in Bash, has less dependencies compared to the current script and is meant to fix a number of issues with the current version. 
Backups are streamed from source to target so that an intermediate write to disk is not performed, resulting in faster provisioning times.
  • Upgraded the Drizzle driver to support MySQL 8 authentication protocols (SHA256, caching_sha2)
  • The replicator has been updated to support the new character sets supported by MySQL 5.7 and MySQL 8.0, including the UTF-8-mb4 series.

Bug Fixes and Behavior Changes

  • The tpm command now properly handles network interface names containing colons and/or dots.
  • The tpm diag --hosts={hostlist} command has been fixed. In a Staging-method install, using this option limits the tpm diag command to the specified hosts. In an INI-based install, tpm diag by itself gets diags for the local node only, and specifying the option --hosts={hostlist} will attempt to obtain diags from the named hosts only.
  • When using tpm with the INI method, the command would search multiple locations for suitable INI files. This could lead to multiple definitions of the same service, which could in turn lead to duplication of the installation process and occasional failures. If multiple INI files are found, a warning is now produced to highlight the potential for failures.
  • When executing mysqldump, all Tungsten tools no longer use the –add-drop-database flag as it will prevent MySQL 8+ from restoring the dump.
  • Now properly extracting the Geometry datatype.
  • Added support for missing charset GB18030.

Highlights in the Clustering product:

Behavior Changes
The following changes have been made to Continuent Tungsten and may affect existing scripts and integration tools. Any scripts or environment which make use of these tools should check and update for the new configuration:

  • The Connector passThroughMode configuration option is now deprecated, and will be removed from tungsten-connector/conf/connector.properties. There is currently no tpm option for this, and it is undocumented. The default will be kept to passThroughMode=true.

Improvements, new features and functionality

  • A new utility script has been added to the release, tungsten_post_process, which assists with the graceful maintenance of the static cross-site replicator configuration files on disk.
  • A new utility script has been added to the release, tungsten_reset_manager</strong >, which assists with the graceful reset of the manager’s dynamic state files on disk.
  • The Tungsten Stack now supports the new MySQL 8.0 authentication plugins. Both sha256_password and caching_sha2_password (the new default) are supported by the Replicator, Manager and Connector.
  • The Drizzle driver has been updated to support these new authentication methods, and the MySQL Connector/J 8 is also supported.
  • In order to be fully transparent with the new defaults, when connected to a MySQL 8+ data source, the Connector will advertise caching_sha2_password as the default plugin.
    With earlier versions of MySQL (pre-8.0), the previous default mysql_native_password is used by default and advertised to the client applications.
  • There is a new Connector property option for tpm: property=statement.display.size.in.kb=NNN, which allows for increased verbosity when Connector logging is set to debug or trace.
  • Removed spurious warnings during composite switch or failover.

Bug Fixes

  • The Connector will now wait indefinitely for a Master to become available before finishing startup.
  • Fixing the rpm-based post-install chown command so that symlinked directories get correct ownership.
  • The Tungsten Clustering RPM now preserves the original OS group memberships for the tungsten user.
  • Long service names within cctrl could cause output to fail when displaying information. The underlying issue has been fixed. Because long service names can cause formatting issues, a new option, --cctrl-column-width has been added which can be used to configure the minimum column width used to display information.
  • MySQL ping commands are now reconnected/retried upon “server gone away” error (Proxy mode ONLY).
  • Fixed an edge case where the master node and the coordinator node are the same, then the node was rebooted. The failover would not complete and throw an error.

Highlights for the Replicator product only:

  • The trepctl command now properly handles the -all-services option for the reset sub-command.
  • The Redshift Applier now allows AWS authentication using IAM Roles. Previously authentication was possible via Access and Secret Key pairs only.
  • Loading data into Redshift would fail with an error if a row of data contained a specific control character (0x00 (NULL))
  • The ddl_map.json file used by the apply_schema_changes filter was missing a rule to handle ALTER TABLE statements when replicating between MySQL and Redshift
  • The extract_schema_change filter wasn’t escaping ” (double-quotes) and the generated JSON would then cause the applier to error out.

Release notes:

https://docs.continuent.com/tungsten-clustering-5.4/release-notes-5-4-0.html

https://docs.continuent.com/tungsten-replicator-5.4/release-notes-5-4-0.html

Tungsten Clustering 6.1.0 and Tungsten Replicator 6.1.0 Released

Author: , Posted on Wednesday, July 31st, 2019 at 2:09:07pm

Continuent is pleased to announce that the following new software releases are now available:

  • Tungsten Clustering version 6.1.0
  • Tungsten Replicator version 6.1.0

Release 6.1.0 is significant in that it introduces MySQL 8 support, along with many new features, stability improvements and bug fixes.

Highlights common to both products:

Improvements, new features and functionality

  • Two new utility scripts have been added to the release to help with setting the Replicator position:
    • tungsten_find_position, which assists with locating information in the THL based on the provided MySQL binary log event position and outputs a dsctl set command as output.
    • tungsten_find_seqno, which assists with locating information in the THL based on the provided sequence number and outputs a dsctl set command as output.
  • A new, beta-quality command has been included called prov-sl.sh which is intended to eventually replace the current tungsten_provision_slave script.
    Currently, prov-sl.sh supports provisioning slaves using mysqldump and xtrabackup tools, and is MySQL 8-compatible.  
The prov-sl.sh command is written in Bash, has less dependencies compared to the current script and is meant to fix a number of issues with the current version. 
Backups are streamed from source to target so that an intermediate write to disk is not performed, resulting in faster provisioning times.
  • Upgraded the Drizzle driver to support MySQL 8 authentication protocols (SHA256, caching_sha2)

Bug Fixes and Behavior Changes

  • When executing mysqldump, all Tungsten tools no longer use the –add-drop-database flag as it will prevent MySQL 8+ from restoring the dump.
  • Now properly extracting the Geometry datatype.
  • Added support for missing charset GB18030.

Highlights for the Clustering product:

Behavior Changes
The following changes have been made to Continuent Tungsten and may affect existing scripts and integration tools. Any scripts or environment which make use of these tools should check and update for the new configuration:

  • The Connector passThroughMode configuration option is now deprecated, and will be removed from tungsten-connector/conf/connector.properties. There is currently no tpm option for this, and it is undocumented. The default will be kept to passThroughMode=true.

Improvements, new features and functionality

  • A new utility script has been added to the release, tungsten_post_process, which assists with the graceful maintenance of the static cross-site replicator configuration files on disk.
  • There is a new Connector property option for tpm: property=statement.display.size.in.kb=NNN, which allows for increased verbosity when Connector logging is set to debug or trace.
  • The Tungsten Stack now supports the new MySQL 8.0 authentication plugins. Both sha256_password and caching_sha2_password (the new default) are supported by the Replicator, Manager and Connector.
  • The Drizzle driver has been updated to support these new authentication methods, and the MySQL Connector/J 8 is also supported.
  • In order to be fully transparent with the new defaults, when connected to a MySQL 8+ data source, the Connector will advertise caching_sha2_password as the default plugin.
    With earlier versions of MySQL (pre-8.0), the previous default mysql_native_password is used and advertised to the client applications.

Bug Fixes

  • The Connector will now wait indefinitely for a Master to become available before finishing startup.

Highlights for the Replicator product only:

  • The Redshift Applier now allows AWS authentication using IAM Roles. Previously authentication was possible via Access and Secret Key pairs only.
  • Loading data into Redshift would fail with an error if a row of data contained a specific control character (0x00 (NULL))
  • The ddl_map.json file used by the apply_schema_changes filter was missing a rule to handle ALTER TABLE statements when replicating between MySQL and Redshift
  • The extract_schema_change filter wasn’t escaping ” (double-quotes) and the generated JSON would then cause the applier to error out.

Release notes:

https://docs.continuent.com/tungsten-clustering-6.1/release-notes-6-1-0.html

https://docs.continuent.com/tungsten-replicator-6.1/release-notes-6-1-0.html

How To Add a Simple Checkbox in Confluence

Author: , Posted on Wednesday, July 31st, 2019 at 9:20:11am

While using Confluence, I wanted to learn how to add a simple task checkbox in free-flow text and lists without creating a “real” task.

The answer is very easy – just type the square bracket pair [] and the checkbox will appear!

How To Fix File Sharing Problems in MacOS 10.14 Mojave

Author: , Posted on Sunday, July 28th, 2019 at 9:01:06am

When I upgraded to Mojave, my laptops were no longer able to connect to my desktop. Deeply frustrating.

Long story short, combo of factors:
– Mojave filesystems on disk now default to APFS containers
– APFS filesystems can ONLY be shared out via SMB!
– Hostname resolution in Mojave also seems broken

So, the fix for me was to do the following on the calling laptops:

Go into the Finder app and press ⌘+K to open up the Connect to Server dialog:

Type: smb:// followed by the server’s IP address:

You should be presented with a list of available volumes to choose from:

Once the sharing is working via the IP address, next fix and test via the hostname:

sudo vi /etc/hosts
Add entries for all hosts you wish to connect TO, then save and exit.

Go into the Finder app and press ⌘+K to open up the Connect to Server dialog, and enter smb:// followed by the server’s hostname:

This should also result in a list of volumes to be selected from.

Once that happens you are Good To Go!

As always, YMMV!