October 31, 2014 By Severalnines

When running a MariaDB Cluster or Percona XtraDB Cluster, it is common to use a load balancer to distribute client requests across multiple database nodes. Load balancing SQL requests aims to optimize the usage of the database nodes, maximize throughput, minimize response times and avoid overload of the Galera nodes. 

In this blog post, we’ll take a look at four different open source load balancers, and do a quick benchmark to compare performance:

• HAproxy by HAproxy Technologies
• IPVS by Linux Virtual Server Project
• Galera Load Balancer by Codership
• mysqlproxy by Oracle (alpha)

Note that there are other options out there, e.g. MaxScale from the MariaDB team, that we plan to cover in a future post.

…

Since this is my first blog I feel it's necessary to introduce myself. I'm Tim Callaghan, I work at Tokutek (makers of TokuDB and TokuMX), and I love benchmarking. While some of the content on this blog will certainly be about Tokutek technologies, I plan on exploring a wide variety of others as well. These are strictly my own personal views and opinions, and comments/feedback are always welcome. Lets get started...

A few weeks ago I noticed an EnterpriseDB NoSQL Benchmark that measured Data Load, Insert, Select, and Size. It wasn't just a NoSQL benchmark, it was specifically calling out …

As I mentioned on my last post, where I compared the default configurations options in 5.6 and 5.7, I have been doing some testing for a particular load in several versions of MySQL. What I have been checking is different ways to load a CSV file (the same file I used for testing the compression tools) into MySQL. For those seasoned MySQL DBAs and programmers, you probably know the answer, so you can jump over to my 5.6 versus 5.7 results. However, the first part of this post is dedicated for developers and MySQL beginners that want to know the answer to the title question, in a step-by-step fashion. I must say I also learned something, as I under- and over-estimated some of the effects of certain …

I've been working on the CIS security benchmark for Oracle MySQL 5.6. There already is a benchmark document for earlier versions of MySQL.

The benchmark document helps you to ensure you have a secure configuration for MySQL.

Center for Internet Security (CIS) is a nonprofit organization.

The Call For Participation is now open.

If you are already a CIS member then you can download the document here. If you don't have an account you can register for free.

August 7, 2014 By Severalnines

Deploying Galera Clusters across WAN environments might lead to concerns around data privacy and security - especially as more organisations are having to comply with national and international regulations. You would not want hackers eavesdropping or intercepting replication traffic. Encrypted replication hides what is sent between the Galera nodes, and makes sure each node is only communicating to the ones it trusts. But how expensive is encryption?

In this blog, we will show you how to encrypt the replication traffic between your Galera nodes. We will also look into the performance impact of this encryption.

Encrypted Replication

Galera supports SSL for the encryption of replication traffic. When encryption is enabled, Galera group communication and Incremental State Transfer (IST) happens over an SSL-encrypted …

The following sentence is brought to you by IBM Legal: The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions.

My previous post covered the work needed to get MySQL 5.6.17 running reliably on modern POWER systems. The patch to MySQL 5.6.17 that’s needed is available here.

For those who don’t know, POWER8 is the latest Power Architecture processors from IBM (my employer). These chips will be available in systems from IBM in June 2014 (i.e. Real Soon Now(TM)). There’s some fairly …

“As the database gets used, shards can grow at an uneven rate and one shard might carry a majority of the load. MongoDB corrects this by balancing shards, but because of MongoDB’s lack of concurrency this operation can stall the database unacceptably.”–John Partridge.

I have interviewed John Partridge, President & CEO of Tokutek, Inc.

RVZ

Q1. Tokutek recently announced to have eliminated performance issues of MongoDB sharding. What was the problem?

John Partridge: The problem occurs after a shard is created. As the database gets used, shards can grow at an uneven rate and one shard might carry a majority of the load. MongoDB corrects this by balancing shards, but because of MongoDB’s lack of concurrency this operation can stall the database unacceptably (see the …

So benchmarking different MySQL queries against your database is a wise thing to do. That should go without saying. While we optimize queries the best we can using EXPLAIN (and EXPLAIN EXTENDED)  taking them time to benchmark them should prove helpful.

This is a simple example of executing a mysqlslap statement.

For this example I loaded the WORLD database from MySQL. ( http://dev.mysql.com/doc/index-other.html )

I created a query that joined all three tables  and put it into /tmp/tests.sql. The explain plan is below.

root@localhost [world]> EXPLAIN EXTENDED SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population FROM City C INNER JOIN Country Y ON C.CountryCode = Y.Code INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode WHERE C.Name LIKE 'D%' AND Y.Continent='Europe' \G
…

Memcache access for MySQL Cluster (or NDBCluster) provides faster access to the data because it avoids the SQL parsing overhead for simple lookups – which is a great feature. But what happens if I try to get multiple records via memcache API (multi-GET) and via SQL (SELECT with IN())? I’ve encountered this a few times now, so I decided to blog about it. I did a very simple benchmark with the following script:

#!/bin/bash
mysql_server="192.168.56.75"
mc_server="192.168.56.75"
mysql_cmd="mysql -h${mysql_server} --silent --silent"
mysql_schema="percona"
mysql_table="memcache_t"
mc_port=11211
mc_prefix="mt:"
function populate_data () {
  nrec=$1
  $mysql_cmd -e "delete from ${mysql_table};" $mysql_schema > /dev/null 2>&1
  for rec in `seq 1 $nrec`
  do
    $mysql_cmd -e "insert into ${mysql_table} values ($rec, repeat('a',10), 0, 0);" $mysql_schema > /dev/null 2>&1
  done
}
function mget_via_sql() {
  nrec=$1
  in_list=''
  for rec in …

Following are benchmark results comparing Tokutek TokuDB and Percona XtraDB at scale factor 10 on the Star Schema benchmark. I’m posting this on the Shard-Query blog because I am going to compare the performance of Shard-Query on the benchmark on these two engines. First, however, I think it is important to see how they perform in isolation without concurrency.

Because I am going to be testing Shard-Query, I have chosen to partition the “fact” table (lineorder) by month. I’ve attached the full DDL at the end of the post as well as the queries again for reference.

I want to note a few things about the results:
First and foremost, TokuDB was configured to use quicklz compression (the default) and InnoDB compression was not used. No tuning of TokuDB was performed, which means it will use up to 50% of memory by default. Various InnoDB tuning options were set (see the end of the post) but the most important is that the …