Creating a simple Load Balance client for a MySQL Cluster

There are few key points in using the Connector/J with Load Balancing on MySQL Cluster
- Dynamic JMX with Connector/J

• loadBalanceEnableJMX=true&loadBalanceConnectionGroup=<group>

- Timeout value in the blacklist with Connector/J

• loadBalanceBlacklistTimeout

The following code piece is used on my notebook with MySQL Cluster running on 2 x VMs.  IPs for accessing the 2 MySQL Nodes on the Cluster are 192.168.56.104 and 192.168.56.105.   The port number with the 2 x MySQL Nodes are the same with the value of "3306".

Here is the MySQL Cluster status with MySQL Cluster Manager (MCM)



…

People using OpenStack Trove instances can hit a common issue in the MySQL world: how to perform schema change operations while minimizing the impact on the database server? Let’s explore the options that can allow online schema changes.

Summary

With MySQL 5.5, pt-online-schema-change from Percona Toolkit is your best option for large tables while regular ALTER TABLE statements are only acceptable for small tables. Also beware of metadata locks.

With MySQL 5.6, almost all types of schema changes can be done online. Metadata locks can also be an issue. pt-online-schema-change can still be worth using as it is also online on read replicas.

Regular ALTER TABLE with MySQL 5.5

If you are still using MySQL 5.5, almost all schema changes will require a table …

Did you ever encounter swap space issue with MySQL ? This problem is really annoying and here are some possible solutions :

1) Track memory usage.. Try to identify the bottleneck using query below. It is not trivial job to zero in on the problem heap.. There are several temp tables being created at run time. Also estimating the OS cache being used by system MYISAM tables is not easy

SELECT ( @@key_buffer_size + @@query_cache_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + 80 * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack + @@tmp_table_size ) ) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

2) Configure the swappiness to 10 or 15.. By default it might be set at 60

cat /proc/sys/vm/swappiness will give the current swappiness value on your system

3) Set Numa interleaving to ON. …

I'm happy to announce the release of mylvmbackup version 0.16. The source package is now available for download from http://lenzg.net/mylvmbackup/ and https://launchpad.net/mylvmbackup.

Installation packages for a number of platforms can be obtained from the openSUSE Build Service.

Version 0.16 adds support for sending out SNMP traps in case of backup successes or failures. I'd like to thank Alexandre Anriot for contributing this new feature and his patience with me.

Please see the ChangeLog and bzr …

Performance Schema (P_S) has been available since MySQL 5.5, more than 4 years ago. It is still difficult to see production servers with P_S enabled, even with MySQL 5.6 where the feature is enabled by default. There have been several complaints like the overhead, that the fix is a work in progress, and the ease of use. 52 tables to query and 31 configuration variables is enough to scare people.

There is a solution for the second problem, the usability. It’s name is “sys schema“. It is a collection of views, functions and procedures to make P_S more user friendly.

Installation

If you are a MySQL Workbench user the installation is pretty easy because sys schema is already included. You just need to install it. Click on “Performance – Performance Reports” and there you will find the “Install Helper” button that will install sys schema. …

You’ve already read it in What’s new in 5.7 (So Far) — the MySQL server now has new-and-improved supported for syslog (on unix-like systems) and EventLog (on Windows). In the next few paragraphs, we’ll take a look at what they are, what has changed, and how they can make your life easier.

The MySQL server supplies information in two main ways:

1. The client will receive a reply to every statement. If everything goes right, then we’ll see a simple OK for success, or a result set for SELECT, SHOW, etc.; and even a successful statement may be qualified by a set of warnings or notices. If the statement fails for some reason then we’ll receive an error regarding the failure.
2. On the server, we’ll see a variety of logs depending on the server configuration. Queries exceeding a certain execution …

MySQL is a database that has been bending the SQL standard in ways that make it hard to move off MySQL. What may appear to be a clever technique for vendor lockin (or maybe just oversight of the standard) can be quite annoying in understanding the real meaning of the SQL language. One such example … Continue reading Use MySQL’s Strict Mode on all new Projects! →

November 20, 2014 By Severalnines

Multi-source replication means that one server can have multiple masters from which it replicates. Why multi-source? One good reason is to consolidate databases (e.g. merge your shards) for analytical reporting or as a centralized backup server. MariaDB 10 already has this feature, and MySQL 5.7 will also support it. 

It is possible to set up your Galera Cluster as an aggregator of your masters in a multi-source replication setup, we’ll walk you through the steps in this blog. Note that the howto is for Galera Cluster for MySQL (Codership) and Percona XtraDB Cluster. In a separate post, we’ll show you how to configure MariaDB Cluster 10 instead. If you would like to use MySQL Cluster (NDB) as aggregator, then check out this blog.

Galera …

In this webinar, Baron Schwartz introduces the integration of Go and MySQL. He discusses idiomatic database/sql code, available drivers for MySQL and time saving tips and tricks.

If you did not have a chance to join the webinar, the slide deck is embedded below. You can also register for a recording here.

Using MySQL with Go - Golang's Database/SQL Driver from VividCortex

The MySQL/MariaDB optimiser likes to know things like the cardinality of an index – that is, the number of distinct values the index holds. For a PRIMARY KEY, which only has unique values, the number is the same as the number of rows.  For an indexed column that is boolean (such as yes/no) the cardinality would be 2.

There’s more to it than that, but the point is that the optimiser needs some statistics from indexes in order to try and make somewhat sane decisions about which index to use for a particular query. The statistics also need to be updated when a significant number of rows have been added, deleted, or modified.

In MyISAM, ANALYZE TABLE does a tablescan where everything is tallied, and the index stats are updated. InnoDB, on the other hand, has always done “index dives”, looking at a small sample and deriving from that. That can be ok as a methodology, but unfortunately the history is awkward. The …