How to Create an AWS MySQL RDS Read Replica

Why?

Read Replica provides you extra read-only power, so you can direct your heavy load SELECT (OLAP) queries to the read replica, while the main RDS cluster can serve your critical OLTP transactions.

How?

To create a MySQL read replica in Amazon Web Services (AWS) using Amazon Relational Database Service (RDS), you can follow these steps:

1. Sign in to the AWS Management Console and navigate to the RDS dashboard.
2. In the navigation pane, click on the "Instances" menu item.
3. Select the instance that you want to use as the source for the read replica.
4. From the "Actions" dropdown menu, select "Create read replica".
5. In the "Create Read Replica" dialogue box that appears, you can specify the following options:

• The instance identifier for the read replica. This should be unique within your AWS account.
• The instance class for the read replica. You can choose from a range of instance sizes based on your performance and capacity needs.
• The storage type and size for the read replica. You can choose between magnetic and SSD storage, and specify the size in gigabytes.
• The availability zone for the read replica. This determines the geographical region where the read replica will be located.
• The security group and VPC for the read replica. You can use the same security group and VPC as the source instance, or choose different ones.

6. Click "Create read replica" to create the read replica. It will be created as a new RDS instance, and will be automatically replicating data from the source instance.

You can monitor the progress of the read replica creation process by checking the "Status" column on the RDS dashboard. When the status of the read replica changes to "available", it is ready to use.

Keep in mind that read replicas are designed to be used for read-only workloads, and you cannot write to them directly. Any writes to the source instance will be replicated to the read replica, but you cannot write directly to the read replica.

Keep Performing, Moshe Kaplan

How did We Manage to Integrate Two Large Data based Services? or How to Query Large Number of Records from the MySQL database?

A common case in enterprise systems and micro-services-based architecture is the need to sync data between 2 systems or services.

For example, a billing system in a telco may need updated status regarding the customers' plan.

Common Solutions:

1. A Low latency Service

A common approach might be having a scaleable, low latency service that will be able to respond to status queries within 1ms. This can be achieved using golang based service and a Redis backend for example.

2. A PubSub architecture

In this approach, we will update the billing system w/ recent updates and will need to assume the billing database is updated with the latest updates. This is a very efficient method, yet it is prone to discrepancies and data drifting.

3. Batch Queries

The last method that we'll discuss in this post is having a batch query reg, "hot" subscribers, and getting back the results to the billing system. It might be less sophisticated than the other approaches, yet it is simple and less prone to load data drifting.

The Integration Patttern

The billing system would like to retrieve the status of up to 30K subscribers, in order to enrich the CDR (call data records) files.

This approach may be chosen, in order to minimize the number of calls between the two systems and to avoid data drifting.

As the number of subscribers is too large, to include in a single query, a naive solution, might be to query all the subscribers from the database and filter them at the application level.

A better solution might be using the TEMPORARY TABLE mechanism to extract only the needed subscribers from the table

Step by Step Solution

1. Insert the subscriber ids we get from the billing to a temporary table

CREATE TEMPORARY TABLE temp_billing_sps_subs_id (

   subscriber_id bigint

);

2. Add a JOIN to the current query w/ the temp table

3. Get back up to 30K records instead of 2M.

Few things to think about:

1. We may need to add a new index to match the updated queries

2. We may need to add permissions in production to create these temporary tables.

Bottom Line

It may improve query response time (fewer data to fetch from disk and return to app server) and app query time (less time to scan the 2M records and filter them).

Keep Performing,

Moshe Kaplan

Creating Webhooks on a MySQL Table?

If you need to create a webhook on a MySQL table, having a field that marks the last modification date of any record, can be a great start

ALTER TABLE  my_table
ADD COLUMN modified DATETIME ON UPDATE CURRENT_TIMESTAMP,
ADD INDEX IX_modified (modified);

Keep Performing,
Moshe Kaplan

An Open Source Data Masking Solution for MySQL

Today we'll disucss the need for data masking due to privacy regulations such as GDPR that becone more and more common in the industry.

In order to deploy such a solution we'll utlize two great products:

1. Percona Server 8.0.17 that has recently introduced the data masking plugin (that is compatible w/ MySQL Enterprise one. This plugin exposes multiple functions that translate sensitive strings such as SSN and emails to masked strings.

2. ProxySQL a proxy server that supports modifying SQL queries on the fly. For example replacing SELECT ssn FROM users; with SELECT mask_ssn(ssn) FROM users;

The Percona Server will serve as our MySQL solution (you can use it as a slave instance if you need it for analyst purposes only). while the ProxySQL will serve as a Proxy that modifies SQL queries to utilize the Percona server data masking functions. You may also need to limit users access from the network to the Percona server.

Bottom Line

New times bring new products that can serve us to create novel solutions

Keep Performing,

Moshe Kaplan

Poor TokuDB Performance due to Unreasonable Statisitcs

The details behind TokuDB Statistics 

https://dba.stackexchange.com/questions/135180/percona-5-7-tokudb-poor-query-performance-wrong-non-clustered-index-chosen

All the details about TokuDB Background ANALYZE TABLE

https://www.percona.com/doc/percona-server/LATEST/tokudb/tokudb_background_analyze_table.html

Upgrade to 5.6.38: to enjoy TokuDB: ANALYZE TABLE Is No Longer a Blocking Operation - Percona Database Performance Blog

https://www.percona.com/blog/2018/03/27/analyze-table-is-no-longer-a-blocking-operation/

Keep Performing,

Moshe Kaplan

Disaster Recovery Plan (DRP) for MySQL/MariaDB Galera Cluster

When S#!t Hits the Fan...

That is a good reason to prepare for failure to minimize data loss and downtime

Cluster Design Documentation

First document your cluster, and verify you have:

1. Odd number of instances (>=3) on at least 3 independent location
2. At least a daily backup using XtraBackup is saved remotely
3. Enabled Monitoring is enabled (warning you from low disk space or under performing instances). 
4. Enabled slow queries monitoring to make sure query performance is monitored and you take care of slow queries to maximize UX

Data Recovery Plan (DRP)

DR Cases

1. Data was deleted/modified accidentally. This case will require either:
1. Accept the data loss
2. Get back to daily backup and lose any data collected since last backup (T1+T2).
3. Recover the database on a new node, and cherry picking the changes on the current cluster (T1)
2. Single node was crushed
   Galera support an automatic recovery of a node w/o significant work. Recovery can be accelerated by recovering the node from the daily backup (T1)
3. All Cluster is not working
1. Requires recovery of a single node from daily backup (T1)
2. Setup the cluster (T2)

Technical Procedures

T1: Restore a node from daily backup

1. Bring back the files from the the remote backup to /mnt/backup/
2. Uncompress the files
   sudo tar xvfz $file_name
3. Shutdown the MySQL
   sudo service stop mysqld
4. Copy the files to your data folder (/var/lib/mysql)
   sudo rm -rf /var/lib/mysql/*
   sudo innobackupex --copy-back /mnt/datadrive/mysqlbackup/
5. Verify the folder permissions
   sudo chown -R mysql:mysql /var/lib/mysql
6. Restart the MySQL and verify everything is working.
   sudo service mysql start

T2: Setup a cluster

1. Verify Galera was defined in my.cnf and define
   [mysqld]
   wsrep_cluster_address=gcomm://10.10.10.10
   wsrep_provider=/usr/lib64/libgalera_smm.so
2. Start the first node:
   sudo service mysql start --wsrep-new-cluster
3. Verify the cluster size
   mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
   +--------------------+-------+
   | Variable_name      | Value |
   +--------------------+-------+
   | wsrep_cluster_size | 1     |
   +--------------------+-------+
4. Repeat the process on the other nodes, this time just w/ a simple MySQL restart
   sudo service mysql start

T3: Add/Remove a node

1. Restore the node from the nightly backup (T1)
2. Perform step 4 in setup a cluster (T2)

Bottom Line

Being ready for the worst, can help you mitigate it with minimal data loss and minimal downtime

Keep Performing,
Moshe Kaplan

MySQL Installation on Ubuntu

It seems that MySQL was created to be installed on RHEL/CentOS/Fedora...
But what if you are actually using Ubuntu?
Well, this is the purpose of the following tutorial

1. Trace all old MySQL installations on the servers (yes, some vendors are delivering baseline images with some old MySQL versions)
   sudo dpkg --get-selections | grep -v deinstall | grep -i mysql
   sudo apt-get --purge remove libdbd-mysql-perl libmysqlclient18 mysql-client mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 php5-mysql
2. Get the MySQL repository
   sudo wget -O mysql.deb http://dev.mysql.com/get/mysql-apt-config_0.2.1-1ubuntu14.04_all.deb
3. Install the repository
   sudo dpkg -i mysql.deb
   sudo apt-get update
4. Install the mysql package (you may need to select the right version and select a root password)
   sudo apt-get -y install mysql-server mysql-client
5. If you have a pre-configured my.cnf, get it and replace the current my.cnf file. Then you should restart the MySQL. I would recommend you to keep your my.cnf in a code repository like git to manage the versionssudo apt-get -y install git
   sudo git clone wherever tmp && git mv tmp/.git . && rm -rf tmp
   sudo service mysql restart
6. Now it time to recover your database from your backup (if you have one) and back to business...

Keep Performing,

Moshe Kaplan

9 steps to Implement Offline Backup at Azure IaaS: Backup a MySQL to Azure Storage

A good backup is probably something you will thank for when s%$t hits the fan.
If you chose MySQL as your data infrastructure and Microsoft Azure as your cloud infrastructure, you will probably thank this procedure (that I actually turned into a a script).

Chosen Products
Our first task is choosing the backup and automation products. I selected the following two:

1. Percona XtraBackup: a leading backup product by Percona. The product creates an hot backup of the database that is equivalent to disk copy. This method is much faster to backup and recover than mysqldump. It also support increment backup.
2. Azure SDK Tools Xplat: a node.js based SDK that enables command line interface to the various Azure services including Azure Storage.

Backup Implementation Guide

1. Install Percona XtraBackup
   sudo wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.2.3/binary/debian/precise/x86_64/percona-xtrabackup_2.2.3-4982-1.precise_amd64.deb
   sudo dpkg -i percona-xtrabackup_2.2.3-4982-1.precise_amd64.deb
   sudo apt-get update
   sudo apt-get install -f
   sudo dpkg -i percona-xtrabackup_2.2.3-4982-1.precise_amd64.deb
   
   The CentOS
   sudo rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
   sudo yum -y install percona-xtrabackup.x86_64
2. Install Azure SDK Tools Xplat
   sudo apt-get updatesudo apt-get -y install nodejs python-software-propertiessudo add-apt-repository ppa:chris-lea/node.jssudo wget https://npmjs.org/install.sh --no-check-certificate | sudo shsudo apt-get install npmsudo npm config set registry http://registry.npmjs.org/sudo npm install -g azure-clisudo ln -s /usr/bin/nodejs /usr/bin/node
3. Install a backup procedure
1. Get a publish settings file from Azure (can be done from the console).
2. Get account name and the matching base64 key from the Azure console.
3. Import the publish setting filesudo azure account import /opt/mysqlbackup/mysqlbackup.publishsettings
4. Create a Storage Container
   sudo azure storage container create --container container_name -a account_name -k base64_account_key
4. Run the backup
1. The xtrabackup way
1. Run a full backup and prepare it twice to make it ready for recovery
   sudo xtrabackup --backup
   sudo xtrabackup --prepare
   sudo xtrabackup --prepare
2. Add the frm files and mysql database to your backup
   sudo chmod -R +r /var/lib/mysql/
   sudo cp -R /var/lib/mysql/myql/* /mnt/backup/mysql/
   sudo cp -R /var/lib/mysql/yourdb/*.frm /mnt/backup/yourdb/
2. The innobackupex way:
1. Run full backup and prepare (adjust the memory usage for your case):
   sudo rm -rf /mnt/datadrive/mysqlbackup/*
   sudo innobackupex --user=DBUSER --password=DBUSERPASS /mnt/datadrive/mysqlbackup/
   sudo innobackupex --apply-log /mnt/datadrive/mysqlbackup/ --use-memory=500M
5. Tar the files into a unique daily name_now=$(date +"%Y_%m_%d")
   _file="/mnt/backup/mysqlbackup$_now.tar.gz"
   tar cvzf "$_file" /mnt/datadrive/mysqlbackup/
6. Copy the folder to Azure Storage using
7. azure storage blob upload -f "$_file" -q --container container_name -a account_name -k base64_account_key
8. Create a cron that will run it daily:
   > sudo cron -e
   * 0 * * * /opt/mysqlbackup/daily.backup.sh >/dev/null 2>&1

Recovery Guide

1. Bring back the files from the Azure storage to /mnt/backup/
   sudo cd /mnt/backup/ sudo azure storage blob download --container container_name -a account_name -k base64_account_key -b $file_name
2. Uncompress the files
   sudo tar xvfz $file_name
3. Copy the files to your data folder (/var/lib/mysql) after shutting down the MySQL
1. The xtrabackup waysudo service mysql stopsudo rsync -avrP /mnt/backup/ /var/lib/mysql/
2. The innobackupex way:
   sudo service mysql stopsudo rm -rf /var/lib/mysql/*sudo innobackupex --copy-back /mnt/datadrive/mysqlbackup/sudo chown -R mysql:mysql /var/lib/mysql
4. Verify the folder permissions
   sudo chown -R mysql:mysql /var/lib/mysql
5. Restart the MySQL and verify everything is working.
   sudo service mysql start

Bottom Line 

It may be tough. It may be resource and time consuming. However, you must have good recovery process to keep your a$$...

Keep Performing,

Moshe Kaplan

How to Disable MySQL Binlog

@ MySQL, clean the the old bin log files 

SQL> SHOW MASTER STATUS\G

Take the File name

SQL> PURGE BINARY LOGS TO 'file';

@ The /etc/my.cnf

Comment out log-bin

> #log-bin = ....

Restart the mysql

> sudo service mysql restart

Keep Performing,

Moshe Kaplan

How to Migrate from MySQL to MongoDB

In the last week I was working on a key project to migrate a BI platform from MySQL to MongoDB. The product that its development is headed by Yuval Leshem is gaining a major adaption and the company was facing a scale challenge.
We chosen MongoDB as the platform data infrastructure to support high data insert rate and scale data analysis.
Unlike many projects of this type, we accomplished the migration from plan to production in a week, mostly due to smart and simple plan.

I wanted to share with you some of lessons we learnt during the process:


Data Migration: Mongify
This tool provides a two steps solution to migrate your RDBMS (MySQL) to NoSQL (Mongo):

1. Mapping database structure
2. Export the data and import it according to the defined structure

Since it's an open source you can easily dive into the code and adjust it to your own business case. Moreover, the code is maintained by Andrew Kalek that is very cooperative.

Filter by Date Part (Day, Month, Year..)
If you are regular to using the DB date parts functions such as YEAR() and MONTH(), there are options to do it in MongoDB (see $where and aggregation framework). However, both require intensive IO. The best solution in this case is saving 3 (or more) more fields for each original field. These fields will include the relevant date part, and can be indexed for effective query:

1. [original field]
2. [original field]_[year part]
3. [original field]_[month part]
4. [original field]_[day part]
5. [original field]_[hour part]

Default Values
MongoDB has no defined schema, so there are no default values as well. Therefore it's up to your data layer (or ORM) to take care of it
This is relevant to to default timestamp as well

Data Casting
Same case as with default values. You app should take care of it.
Please notice that there is a defined mapping from values and types that you can find at Mongify code.

Auto Numbers (1..N)
Same case here, but you may have to choose one of following ways:

1. Shift your way of thinking of auto increment ids and start using MongoDB auto "_id"s a solution.
2. You can generate the auto increment ids using a counters database and findAndModify (in this case I will recommend you having a special purpose database and 1:1 collection mapping to gain future releases granular locking). For details see the link on top.

Mongoose as an ORM
If you use node.js consider using Mongoose as your ORM, This one will solve many of your issues by adding structure to your schema. However, please notice that you may loose some flexibility.

Data Analysts
MongoDB is not SQL compliant, and you will have hard time with your data analysts. However, you can ease the change by using the following two methods:

1. Introduce them to Query Mongo.
2. Make sure your documents have no sub documents, if you don't have to. Elsewhere, transforming the data to tabulator view will require a major effort from them.

Avoid Normalizing Your Data
If you designed your data infrastructure as a non normalized structure, it will be much easier to move data to NoSQL. If your data is normalized, it is better to the app to take care of the data reconstruction.

Queries Results Limitation

MongoDB results are limited to a document size. If you need to query 200K+ records, you may need to page the data using skip and limit (or better, adding a filter based on the last limited row key value).

Bottom Line
Migration from MySQL to MongoDB requires some effort and a shift in your state of mind, but it can be done relatively fast using careful planning according to the steps defined before.

Keep Performing,
Moshe Kaplan

MySQL Indexing: Don't Forget to Have Enough Free Space

When you modify you indexes in MySQL (and especially in MyISAM), make sure that the free space on the disk that holds your tmpdir folder is larger than your largest index file.

Why We Need to Such a Large Free Space?
MySQL is using the tmpdir to copy the original index file to and "repair it" by sorting the data.

What Happens if We Don't Have Enough Space?
In this case MySQL will make it best to modify the index file based on the given space. The result is a very slow process (or never ending one) and poor results. If you will check the show processlist, you will find out the state "Repair by keycache" instead of "Repair by sorting"

What to Do?
Make sure you have enough free space (> largest index file) and that the tmpdir option file is located on this disk.

Bottom Line
Make sure you have enough free space to get best performance

Keep Performing,
Moshe Kaplan

When Should I Use MongoDB rather than MySQL (or other RDBMS): The Billing Example

NoSQL is a hot buzz in the air for a pretty long time (well, it not only a buzz anymore).

However, when should we really use it?

Best Practices for MongoDB

NoSQL products (and among them MongoDB) should be used to meet challenges. If you have one of the following challenges, you should consider MongoDB:

You Expect a High Write Load

MongoDB by default prefers high insert rate over transaction safety. If you need to load tons of data lines with a low business value for each one, MongoDB should fit. Don't do that with $1M transactions recording or at least in these cases do it with an extra safety.

You need High Availability in an Unreliable Environment (Cloud and Real Life)

Setting replicaSet (set of servers that act as Master-Slaves) is easy and fast. Moreover, recovery from a node (or a data center) failure is instant, safe and automatic

You need to Grow Big (and Shard Your Data)

Databases scaling is hard (a single MySQL table performance will degrade when crossing the 5-10GB per table). If you need to partition and shard your database, MongoDB has a built in easy solution for that.

Your Data is Location Based

MongoDB has built in spacial functions, so finding relevant data from specific locations is fast and accurate.

Your Data Set is Going to be Big (starting from 1GB) and Schema is Not Stable

Adding new columns to RDBMS can lock the entire database in some database, or create a major load and performance degradation in other. Usually it happens when table size is larger than 1GB (and can be major pain for a system like BillRun that is described bellow and has several TB in a single table). As MongoDB is schema-less, adding a new field, does not effect old rows (or documents) and will be instant. Other plus is that you do not need a DBA to modify your schema when application changes.

You Don't have a DBA

If you don't have a DBA, and you don't want to normalize your data and do joins, you should consider MongoDB. MongoDB is great for class persistence, as classes can be serialized to JSON and stored AS IS in MongoDB. Note: If you are expecting to go big, please notice that your will need to follow some best practices to avoid pitfalls

Real World Case Study: Billing

In the last ILMUG, Ofer Cohen presented BillRun, a next generation Open Source billing solution that utilizes MongoDB as its data store. This billing system runs in production in the fastest growing cellular operator in Israel, where it processes over 500M CDRs (call data records) each month. In his presentation Ofer presented how this system utilizes MongoDB advantages:

1. Schema-less design enables rapid introduction of new CDR types to the system. It let BillRun keep the data store generic.
2. Scale BillRun production site already manages several TB in a single table, w/o being limited by adding new fields or being limited by growth
3. Rapid replicaSet enables meeting regulation with easy to setup multi data center DRP and HA solution.
4. Sharding enables linear and scale out growth w/o running out of budget.
5. With over 2,000/s CDR inserts, MongoDB architecture is great for a system that must support high insert load. Yet you can guarantee transactions with findAndModify (which is slower) and two-phase commit (application wise).
6. Developer oriented queries, enable developers write a elegant queries.
7. Location based is being utilized to analyze users usage and determining where to invest in cellular infrastructure.

Bottom Line

MongoDB is great tool, that should be used in the right scenarios to gain unfair advantage in your market. BillRun is a fine example for that.

Keep Performing,
Moshe Kaplan

Monitoring Your SQL Server w/ Monitis

One of the nicest monitoring products around is Monitis.com
If you are using MySQL, you will probably find out that some of the thresholds there should be modified or tuned (or just removed, since they are not critical to your day to day operations):

Key Cache Writes Rate
Actual value: 0
Proposed range: 75-100
What should be done: If you are not using MyISAM, ignore this one. If you do use it, modify the key_buffer_size variable and increase it to accommodate the index in the memory.

Indexes Usage
Actual value: 5
Proposed range: 80-100
What should be done: Indexes usage is a key indicator to understand if you write your queries in a way that utilizes your indexes and if you defined correct indexes to support your questions.
Enable your slow log query and examine your queries. 

Key Cache Hit Rate
Actual value: N/A
Proposed range: 95-100
What should be done: Seems that this is not a critical variable, since you must check it is relevant in your case. If you believe that your system business case should use most data out of cache and value is low, modify the key_buffer (MyISAM) or innodb_buffer_pool_size (InnoDB).

Table Cache Hit Rate
Actual value: N/A
Proposed range: 85-100
What should be done: See the one above

Bottom Line
Monitoring system is critical component of critical systems. However, in most cases you should verify that the default configuration matches your business case (and if it is the case, take care the alerts!).

Keep Performing,
Moshe Kaplan

MySQL and Application Servers Connection Pool

As we discussed in the past, MySQL is very lightweight in terms of connections creation and closure overhead, and therefore classic LAMP installations rarely use it.
However, some application frameworks such as Java and Ruby tend to use it and in these cases it is recommended to adjust some configurations in order to avoid long connections cuts.

The Pattern
Since MySQL default is 8 hours timeout, this pattern is usually when connection pools are configured with high number of connections (and low utilization) or when pattern usage is limited to defined hours (daytime for example).

How to Detect this Pattern?
By using MySQLTuner, you will find out that Connections Aborted parameter is too high (I've seen cases with as high as 80%).

What Should We Do?
Adjust the connections timeout, in order to avoid connection pool connections cutting:
#1 day timeout
SET GLOBAL wait_timeout = 86400
SET GLOBAL interactive_timeout = 86400

or in the my.cnf
wait_timeout = 86400
interactive_timeout = 86400

Bottom Line
Connection pools are not a native case for MySQL, but it does not mean your cannot support it wisely.

Keep Performing,
Moshe Kaplan

Changing MySQL Binary Log Files Location to Another Directory

What is the Default?
Usually in most installations, binary log files are located in the MySQL default directory (/var/lib/mysql) just next to the data files.

Why Should I Move the Binary Logs to Another Directory?
Each data modification (INSERT, UPDATE, DELETE...) and data definition (ALTER, ADD, DROP...) statement that you perform in your server are recorded in the Log files.
Therefore, each time you make any of these statements, you actually update both your data files and your log files. The result is high IO utilization that is focused on a specific disk area.
A common recommendation in the database field is to separate these files to two different disks in order to get a better performance.

How to Perform it?

1. Change the log-bin variable in the my.cnf to log-bin=/path/to/new/directory/mysql-bin
2. Purge as many files as you can (PURGE BINLOG...) in order to minimize the number of moved files (see stop 4).
3. Stop the master (service mysql stop).
4. Move the files to the new directory: mv /var/lib/mysql/mysql-bin.* /path/to/new/directory
5. Start the master again (service mysql start).

Bottom Line
Few steps and your server is ready for more traffic and data.

Keep Performing,
Moshe Kaplan

MySQL Replication Deadlock Detection and Recovery (The code inside)

The best practice to handle deadlocks (after eliminating them from the code) is recovering from them. Unfortunately, if you ever came across the "SHOW SLAVE STATUS\G" Error 'Deadlock found when trying to get lock: try restarting transaction', you probably know that MySQL Replication does not recover automatically from deadlocks.


Therefore, I implemented a Nagios plugin. This Nagios plugin detects the MySQL Replication deadlock and recover from it by restart the slave ("STOP SLAVE; START SLAVE;"). 


Technical
Brief: This a shell script (/bin/sh) that connects to a specified MySQL slave database, check the replication status for Deadlock errors + Automatically recover from the state by performing "SLAVE STOP; SLAVE START;"
Required parameters: host, user, password.
If no Deadlock is detected, "OK" is returned
O/w, a message is printed, slave restarted and Warning state is returned.


Bottom Line
Fast, Simple and very effective and now available from Nagios Exchange.


Download the MySQL Replication Deadlock Recovery Nagios Plugin.

Suggestions are welcome,


Keep Performing,
Moshe Kaplan

MySQL and Lucene: Server Side Cursors or Client Side Cursors?

What is the Default?
Client Side Cursors: this method returns the client the whole result set upon a SQL request.


What is Faster?
Client Side Cursors: once you get the result set back to the client, you can manipulate it and read through it using minimal communication with the server.


So Why Should I Choose Server Side Cursors?
If you have a very large result set (say 100GB and millions of records), it might not be wise to fetch all the record set and only then start manipulating it. Lucene is one of the most common use cases for it. 
Server Side Cursors are the solution in this case. Instead of fetching the whole result set to the client, the result set is being stored in a server's In Memory table. Upon request, the server returns to the client a defined number of rows. That way the client can start processing the results, while further fetch is done over the network.


What are the Limitations?

1. Server Side Cursors support forward only result sets (you must set in JDBC for example fetchSize="NUMBER" resultSetType="FORWARD_ONLY") .
2. MySQL Server Memory. This method requires a lot of memory. Fortunately, if you don't have enough memory, MySQL will automatically use MyISAM table to store the temporary table.

How should I Configure It?
Client Side Cursors need 0 configuration since it's the original behavior of MySQL.
The Server Side Cursors feature was added at MySQL 5.0 and is nowadays considered stable. Yet, you will have to enable it in your connection definition, and while fetching 


Can I Use Them Both on the Same Connection Pool?
Yes. Server Side Cursors are used only if two terms accomplished: 1) Server Side Cursors are enabled (useCursorFetch) and 2) setFetchSize is positive (>0). Therefore, you can enable Server Side Cursors by setting setFetchSize to positive value and set fetchsize to 0 to keep using Client Side Cursors. If you are tired of setting setFetchSize to 0 every time, just set defaultFetchSize to 0.


Bottom Line
Now, you have the tools and you can better use MySQL for your cloud highly scalable system.


Keep Performing,
Moshe Kaplan

5 Options to Backup Your MySQL and Keep Your Service Online

An old customer was facing a major issue. They just expanded their service from a local shop to a global "follow the sun" service, providing SaaS to worldwide customers.
Their MySQL server were the finest, and their procedures are the same, including backup procedures. Yet, concepts that matched a local shop my not fit a global firm.

Going from Local to Global
Their current procedures include both binary backup and mysqldump of the server. Although both procedures are aimed to keep the service available while performing the backup, the bottom line is that the CPU is highly utilized and the service is poor.

Backup Methods
There are two major backup methods (you can read more about them and others at Lenz Grimmer's presentation):

1. mysqldump: This method extracts the database content into a flat file. This file includes all the DDL and DML SQL statements. Although this method is considered as a "hot backup", it tends to reduce the SLA and leave users unsatisfied. Moreover, if the database is not locked during the process, you may result an inconsistent backup.
2. Binary files backup: This method requires stopping the MySQL server. On the other hand if you copy the binary file to a disk on the same physical server, it could be a very short downtime. In order to keep the files in a safe place, you should later on backup these copied files over the network. Please notice that this method has a another disadvantage: one of the binary files may be corrupted and you may not notice that till getting to the money time.

Where the Backup Can Be Performed?
There are three possible locations:

1. The Master Server: The safest since we know that data was not "disappeared" during replication. The significant drawback is service downtime...
2. The Existing Slave Server: At the risk losing information that was not replicated correctly, we get a service with no downtime. Yet, notice that if you use this server for other tasks you may affect these tasks SLA.
3. Dedicated Slave: This can be a lightweight machine (yes, even a VPS) that can perform backups once or more times a day without causing downtime and without affecting the service at all.

The Bottom Line or How to Keep Their Service Available?
Well, in this case, I suggested the customer choosing the Dedicated Slave option that in a very small price helped him reduce the service downtime and provide great SLA to their global customers.

Keep Performing,
Moshe Kaplan

How to Install MySQL on an Ubuntu Based Machine

It is a common question: "How should I install MySQL on an Ubuntu based machine?"
To make it easier for you, I documented the procedure:

1. Uninstall current MySQL packages
sudo dpkg --get-selections | grep -i mysql
sudo dpkg --purge mysql-package-name


2. Download the relevant generic RPM packages from the MySQL site (server, client and devel)
wget path

3. Install alien
sudo apt-get install alien

4. Install the 3 packages using alien
sudo alien -i downloaded_package_file.rpm

Keep Performing,
Moshe Kaplan

Adding One More MySQL Slave to Cloud Based System (Amazon AWS)

MySQL Replication tends to be complex.

Creating a new slave to support your system expansion is not easier: coping large data files over the LAN, recovering replication, etc.

Fortunately, virtualization and SAN help us do it easier and faster.

Lucky us, Cloud Computing make it even easier.

Adding a New Slave is Easier than Ever

1. Select an existing slave that can be reboot w/o affecting the service.
1. Take the slave out of the production environment.
2. Stop the MySQL slave and perform a read lock: SLAVE STOP;FLUSH TABLES WITH READ LOCK;
3. Create an AMI based on this instance using AWS console or API.
4. Recover the instance upon AMI creation completion and get the slave back to production: UNLOCK TABLES;SLAVE START;SHOW SLAVE STATUS\G
2. Create a new slave based on the AMI
1. Launch a new slave using the AWS console or API.
2. Change the server's /etc/my.cnf file to fit a new slave instance:
1. Change the server-id=N value to a new numeric value.
2. Set relay-log='server-relay-bin' to the original slave server relay log name. (if you want to make it even easier, configure in the first slave's my.cnf relay-log=mysqld-relay-bin, and you'll be able to avoid this step).
3. Restart the MySQL instance: sudo /etc/init.d/mysql restart
4. Recover the MySQL: UNLOCK TABLES;SLAVE START;SHOW SLAVE STATUS\G
5. Add this new slave to the production environment.

Bottom Line

Expanding your business today is easier w/o the need for file copy or any other tedious action.

Keep Performing,

Moshe Kaplan