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 and real-time logging are. Each load balancers must also check regularly if the database …[Read more]
9 Older Entries »
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 user and accounting data. All servers are running CentOS 6.4 64bit.
FreeRadius Deployment with Galera
We will deploy a two-node FreeRadius cluster …[Read more]
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 Cluster (PXC)
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: './mysql/ndb_apply_status.frm'' on opening …[Read more]
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]
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
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.
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 …[Read more]
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 $$ drop procedure if exists compose_user_account_key $$ …[Read more]
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 tables …[Read more]
9 Older Entries »