Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 19

Displaying posts with tag: NDB Cluster (reset)

The use of Iptables ClusterIP target as a load balancer for PXC, PRM, MHA and NDB
+1 Vote Up -0Vote Down

Most technologies achieving high-availability for MySQL need a load-balancer to spread the client connections to a valid database host, even the Tungsten special connector can be seen as a sophisticated load-balancer. People often use hardware load balancer or software solution like haproxy. In both cases, in order to avoid having a single point of failure, multiple load balancers must be used. Load balancers have two drawbacks: they increase network latency and/or they add a validation check load on the database servers. The increased network latency is obvious in the case of standalone load balancers where you must first connect to the load balancer which then completes the request by connecting to one of the database servers. Some workloads like reporting/adhoc queries are not affected by a small increase of latency but other workloads like oltp processing

  [Read more...]
Deploying an Active-Active FreeRadius Cluster with MySQL NDB or Galera
+0 Vote Up -0Vote Down
January 6, 2014 By Severalnines

MySQL Cluster is a popular backend for FreeRADIUS, as it provides a scalable backend to store user and accounting data. However, there are situations when the backend database becomes a centralized datastore for additional applications and services, and needs to take a more general-purpose role. NDB usually works very well for FreeRADIUS data, but for wider use cases and reporting type applications, InnoDB can be a better storage engine. For users who need to keep their data in InnoDB and still benefit from a highly available clustered datastore, Galera Cluster can be an appropriate alternative.

In this post, we will show you how to deploy FreeRadius both with MySQL Cluster and Galera Cluster to store

  [Read more...]
High-availability options for MySQL, October 2013 update
+1 Vote Up -0Vote Down

The technologies allowing to build highly-available (HA) MySQL solutions are in constant evolution and they cover very different needs and use cases. In order to help people choose the best HA solution for their needs, we decided, Jay Janssen and I, to publish, on a regular basis (hopefully, this is the first), an update on the most common technologies and their state, with a focus on what type of workloads suite them best. We restricted ourselves to the open source solutions that provide automatic failover. Of course, don’t simply look at the number of Positives/Negatives items, they don’t have the same values. Should you pick any of these technologies, heavy testing is mandatory, HA is never beyond scenario that have been tested.

Percona XtraDB  [Read more...]

Heterogeneous replication with NDB cluster
+0 Vote Up -0Vote Down

Recently, I was asked if it is possible to replicate an NDB cluster to a non-NDB MySQL database. So, I tried!

I created the following table on the MySQL master:

Create Table: CREATE TABLE `testrepl` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

and on the slave:

Create Table: CREATE TABLE `testrepl` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Of course, for obvious reasons, NDB only supports row based replication so I configured the master to use row based:

mysql> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

Then I tried and go the following error:

Last_Error: Error 'Incorrect information in file:
  [Read more...]
Social networking type queries with NDB (part 3)
+0 Vote Up -0Vote Down

In the previous 2 posts of this series, we basically talked about how to execute social networking type queries using SQL IN clause and how handle multiple columns IN clause. In this last post on the topic, I will introduce the notion of NDB API filters, although I don’t consider myself as an NDB API expert. Filters are to NDB API the equivalent WHERE clause in SQL. The point is that the filters can be nested and they are sent to the storage nodes only when the transaction is executed.

As an example, let’s consider the following table:

Create Table: CREATE TABLE `MultiColPK` (
  `region_id` int(11) NOT NULL DEFAULT '0',
  `application_id` int(11) NOT NULL DEFAULT '0',
  `first_name` varchar(30) NOT NULL DEFAULT '',
  `payload` varchar(30) DEFAULT NULL,
  PRIMARY KEY
  [Read more...]
Upcoming webinar on NDB Cluster 7.0 new features
+0 Vote Up -0Vote Down

I just learned that there will be a Webinar “What’s New in the Next Generation of MySQL Cluster?”, April 30th. From what I know, the webinar is supposed to be at a good technical level, it is not a marketing like introduction. If you are interested, just register at: http://www.mysql.com/news-and-events/web-seminars/display-320.html (http://www.mysql.com/news-and-events/web-seminars/display-320.html)

The upcoming MySQL UC 2009
+0 Vote Up -0Vote Down

The MySQL UC 2009 is coming and it is time for my own little marketing. As Matt already annonced it a few months ago we (Matt and I) are doing a WaffleGrid presentation, Distributed InnoDB caching with Memcached, Tuesday at 2PM. I am also presenting at the MySQL Camp or unconference, NBD (MySQL Cluster) performance tuning and pitfalls, also Tuesday at 4:25PM.

NDB Cluster one step closer to become a DB killer app!
+0 Vote Up -0Vote Down

If you have been following the development of the NDB Cluster storage engine lately, you are probably as excited as I am. NDB Cluster is becoming a kind of large database killer app. Look at all the nice features that have been added:

  • Replication, if you know MySQL you know what I am talking about
  • Distribution awareness, optimize query execution based on the distribution, a strong scaling factor
  • Disk based data, the possibility of pushing some columns to disk
  • Online add index, among the only online DDL I know of in MySQL
  • Multi-threading, no more need to configure many data nodes per server
  • Realtime, when query execution times matter

and I probably miss some. And now, with version 7 (renamed from 6.4) it is possible to extend a NDB cluster

  [Read more...]
Social networking type queries with NDB (part 2)
+0 Vote Up -0Vote Down

Recently, I talked about how to optimize social networking type queries for the NDB storage engine using IN clause statements. In clauses are great but they have one fundamental limitation, they work only on one column (Actually, this is not true, I discovered, thanks to Roland’s comment, that MySQL supports multiple columns IN clause). What if the primary key is a composite of let’s say “region_id”, “application_id” and “user_id”? Recently, while on site with a client, Brian Morin showed me a very clever way of dealing these type of primary keys in an IN clause. The main problem is that you cannot return a binary or varbinary from a function. So the idea was to used the return values of a stored proc. First we need to compose the varbinary from the actual values with this stored proc:
.

delimiter

  [Read more...]
High performance replacement of the MySQL Memory storage engine with NDB
+0 Vote Up -0Vote Down

People often wants to use the MySQL memory engine to store web sessions or other similar volatile data.
There are good reasons for that, here are the main ones:

  • Data is volatile, it is not the end of the world if it is lost
  • Elements are accessed by primary key so hash index are good
  • Sessions tables are accessed heavily (reads/writes), using Memory tables save disk IO

Unfortunately, the Memory engine also has some limitations that can prevent its use on a large scale:

  • Bound by the memory of one server
  • Variable length data types like varchar are expanded
  • Bound to the CPU processing of one server
  • The Memory engine only supports table level locking, limiting concurrency

Those limitations can be hit fairly rapidly, especially if the session payload data is large. What is less known is that NDB Cluster can creates


  [Read more...]
Social Networking type queries with NDB (part 1)
+0 Vote Up -0Vote Down

NDB Cluster is the only integrated sharding framework that I know of (educate me if I am wrong) but it is known to have issues with large joins. These days, large databases that would benefit from a sharding framework are often for social networking type applications that requires large joins.

Actually it is not NDB that is the root cause of the joins problem, it is the way MySQL executes joins. Instead of asking the cluster for a large number of rows for the secondary table it joins to, the current version of MySQL does ask one row at a time. NDB cluster answers those queries very rapidly but, the time to hop over the network kills performance. The MySQL-6.0 branch will implement the Batch Key Access (BKA) algorithm which will solve that issue and might create database application killer with NDB cluster.

Although right now BKA is not available, there are ways to execute those queries in an

  [Read more...]
NDB cluster and Max_connections
+0 Vote Up -0Vote Down

NDB cluster is a strange beast. Usually, performance wise, it is a good idea to limit the number of threads inside MySQL, that’s why there are parameters like thread_concurrency and innodb_thread_concurrency. MySQL is known to show mutexes contention with a number of active threads greater than a hundred (actually even less) but with NDB the situation is quite different since threads have to wait for the network latency. With NDB Cluster, be prepared to use unusually high numbers of connections and be prepared to crank up the number of active workers if you want to push NDB to its limit. The following figure shows some tests I made recently during one my engagements. As one can see, the number of active connections has an important impact on the overall throughput and it peaks at approximately 800 connections!!!

  [Read more...]
Using local tables and replication in a clever way with NDB
+0 Vote Up -0Vote Down

Tuning queries for MySQL NDB cluster is way trickier than tuning for any other storage engines. This is especially true for highly normalized schema. Look at the following query trying to retrieve all male account from a given city. Since a city is not unique, we also need to specify the state.

SELECT a.First_name, a.Last_name
FROM Account a INNER JOIN Cities c ON a.City_id = c.Id
  INNER JOIN States s ON c.State_id = s.Id
  INNER JOIN Gender g ON a.Gender_id = g.Id
WHERE c.Name = ‘Columbia’ AND s.Name = ‘South Caroline’ and g.Name = ‘Male’;

If you look at how the query will be executed with NDB, you realize that many hops over the network will be needed, at least one per table. If you push the cluster a lot, these extra hops will limit the performance. Since tables like Cities, States and especially Gender are nearly





  [Read more...]
NDB realtime options, choosing CPU and balancing interrupts
+0 Vote Up -0Vote Down

With the NDB realtime options, you can choose on which CPU the execution thread and the maintenance will be running. The point is, which CPUs to use.

The output of “cat /proc/interrupts” will help you determine which CPU to use. Here is an example of a dual quad-cores box:

$ cat /proc/interrupts
           CPU0       CPU1       CPU2       CPU3       CPU4       CPU5       CPU6       CPU7


  [Read more...]
Reducing latency of queries with NBD Cluster realtime extensions
+0 Vote Up -0Vote Down

I was recently involved in a project where the main requirement was the smallest possible latency for queries. The queries were simple insert and update statements, the update being by primary key. The cluster was using regular Gigabits Ethernet and formed by 3 servers with 8 cores each, one Management node and 2 data nodes. Since throughput was not a concern, a design decision has been made to locate the MySQL daemons on the same servers as the data nodes, in order to save network hops. MySQL chooses the closest NDB data node as its transaction coordinators and the closest on will be reachable on localhost.

Apart from the architectural decision to host MySQL and the NDB nodes on the same boxes, we decided to benchmark the new real time options of NDB. The options are the following:

  • RealtimeScheduler: enable the real time schedule, was set to 1
  • LockExecuteThreadToCPU: assign
  •   [Read more...]
    Linux Swappiness
    +0 Vote Up -0Vote Down

    Have you ever been upset by the Linux tendancy to swap… Especially when trying to allocate a large InnoDB buffer pool.. Look at the following output:

    yves@yves-laptop:~$ free
    total used free shared buffers cached
    Mem: 2041888 1991096 50792 0 52 954592
    -/+ buffers/cache: 1036452 1005436
    Swap: 975200 1308 973892

    There is still 50792 + 52 + 954592 = 1005436 of free memory and Linux starts to swap!!! The reason is hidden here:

    yves@yves-laptop:~$ cat /proc/sys/vm/swappiness
    60

    The swappiness controls the Linux to swap for the File cache. For a file server or a web server or even MySQL with MyISAM tables, the file cache is interesting but for InnoDB or NDB Cluster it is






      [Read more...]
    e1000 InterruptThrottleRate
    +0 Vote Up -0Vote Down

    The e1000 Nic is very common and can be found in many servers.  During my last NBD cluster engagement, on a set of 5 Dell 1950 servers equipped with a quad e1000 Nic, we were trying to tune cluster performance and one of the thing that was disapointing was that the system time, to processed interrupts from the Nics, was always low.  Since neither the ndbd processes or the mysqld processes were using a significant amount of CPU, we were wondering where the bottleneck was.

    Then, Michel Donais, from the client, read the Linux driver documentation and bingo!!! Those e1000 Nics throttle interrupts by default at 8000/s.  8000… it is a big limiting factor….   design to prevent DOS attacks.  We removed the throttling and… reach more than 30,000 interrupt/s and guess what, the number of transaction per second scaled with it along with the ndbd and mysqld cpu usage.  Interesting isn’t it?

      [Read more...]
    NDB Cluster and Max_rows
    +0 Vote Up -0Vote Down

    Just before Christmas, I was working with a client that needed to insert more than a billion rows into a NDB table. The cluster was big,
    14 nodes, and after a few hundred millions rows, we got the following error:

    2008-12-09 18:28:52 [ndbd] INFO     -- dbacc/DbaccMain.cpp
    2008-12-09 18:28:52 [ndbd] INFO     -- DBACC (Line: 5274) 0x0000000e
    2008-12-09 18:28:52 [ndbd] INFO     -- Error handler shutting down system
    2008-12-09 18:28:53 [ndbd] INFO     -- Error handler shutdown completed - exiting
    2008-12-09 18:28:56 [ndbd] ALERT    -- Node 10: Forced node shutdown completed. Caused by error 2304: 'Array index out of range(Internal error, programming error or missing error message,

    which looks like a bug in the NDB kernel.  In fact, it is not really a bug.  This error is the equivalent of a “table too large” error










      [Read more...]
    About FragmentLogFileSize and NoOfFragmentLogFiles
    +0 Vote Up -0Vote Down

    I was recently involved with a client that has to deal with a very large dataset  in MySQL NDB cluster.  The schema was very simple, a few (one or two) key columns and a large varbinary column.   The cluster was large with 14 data nodes having each close to 60 GB devoted to DataMemory (DM) for a total combine size of

    The main problem we had with the cluster during this engagement was the lack of disk space and the main cause of this, was the Redo log files.  The cluster has been configured according to Johan best practices of 6xDM (http://johanandersson.blogspot.com/2008/04/great-configini.htm) forFragmentLogFileSize and NoOfFragmentLogFiles and, adding the 2 Local CheckPoint (LCP), this mean 8xDM or 480 GB, hard to fit on a pair or 146 GB drives.  The purpose for 6xDM is to make sure write transaction are never rejected by the cluster because of a lack of redo log space.  The redo log space basically

      [Read more...]
    Showing entries 1 to 19

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.