MariaDB Xpand 6.1 provides uninterrupted scale out

Posted on March 30, 2023 by
spacer

MariaDB Xpand is an ACID-compliant, distributed SQL database for high availability and elastic scale. The newest release of Xpand, version 6.1, builds on availability by providing teams with these new capabilities:

Xpand 6.1 is also now available in MariaDB SkySQL as a fully managed cloud database service. SkySQL also offers the benefits of autoscaling to handle increases in demand as well as autoscaling back when demand normalizes for cost savings.

Scale out with zero interruptions

The latest release of Xpand adds the ability to have uninterrupted scale out when adding nodes. This means that adding a node instantly increases the capacity of the server. The node immediately accepts connections, compiles queries, distributes work, and coordinates results. Xpand immediately begins distributing replicas to new nodes, and increasing the read and write capacity of the cluster.

To put this in context, let’s look at some peer offerings and how they approach uptime when adding a node. CockroachDB states it can take “tens of minutes to add a new virtual machine to a CockroachDB Dedicated cluster.” Amazon Aurora is positioned at providing better scale than RDS; however, with a single primary database architecture it can scale reads, but does not add additional write capacity. Xpand, on the other hand, sees the entire cluster as a single database where all nodes participate in reads and writes for read/write scale.

Faster analytic queries

Xpand provides fast OLTP processing with row store tables and B-tree indexes, plus columnar indexes to deliver up to 50x speed up for analytical queries, coexisting in the same schema with an intelligent optimizer that determines the best choice. There are major optimizer enhancements delivered in Xpand 6.1 including faster aggregations and improved costing, resulting in better plan choices along with a 3x improvement in query compile and recompile times. Because of these improvements there is a 5x query speed improvement in the TPC-H benchmark.

It may seem strange to test an operational database such as Xpand with an analytical query benchmark (TPC-H) but unlike other distributed SQL offerings, Xpand is not solely an operational database. In order to support modern real-time business needs and simplified architecture, Xpand supports hybrid workloads and operational analytics. What does this look like in technical terms?

For instance, there is a 5x improvement in the TPC-H benchmark which includes a pricing summary report query (TPC_H 3.01 pg 29):

select
l_returnflag,l_linestatus,sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price, 
sum(l_extendedprice*(1-l_discount)) as sum_disc_price, 
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge, avg(l_quantity) as avg_qty,avg(l_extendedprice) as avg_price,avg(l_discount) as avg_disc,count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '[DELTA]' day (3)
group by l_returnflag,
l_linestatus order by
l_returnflag, l_linestatus;

While this query is ostensibly an analytical query, “for what price did today’s products sell” is exactly the kind of question a financial trading system might need to answer. It is also a common e-commerce question. Performant execution of this type of query on an operational database means faster answers and in some cases eliminates the need to feed a separate analytical system (no ETL required).

Xpand’s 8x improvement of the product returns query (p 45) is even more relevant to e-commerce users:

Select c_custkey, c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal,
N_name, c_address, c_phone, c_comment
from
Customer, orders, lineitem, nation
where
c_custkey = o_custkey and l_orderkey = o_orderkey
and o_orderdate >= date '[DATE]'
and o_orderdate < date '[DATE]' + interval '3' month and l_returnflag = 'R'
and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
order by
revenue desc;

These results were captured without the use of a columnar index, a core feature in Xpand which could improve the performance of range queries further. These are only a few examples of the overall 5x improvement for TPC-H performance. Additionally, Xpand 6.1 includes other features outside of performance and availability such as support for ROLES, additional collations and charsets for increased compatibility and support for the PROXY protocol.

There are also further improvements to performance, compatibility and stability. For the full release notes, see https://mariadb.com/docs/xpand/release-notes/mariadb-xpand-6-1/. If you would like to see Xpand’s subsecond recovery in action, watch the webcast Xpand 6.1 in Action – Unstoppable Reliability.

Backup and restore to cloud storage

We support highly durable, low-cost cloud storage on Amazon S3. Like Xpand’s replication feature, backup happens in parallel and online to ensure your database stays up and running, and that backups happen faster!

Start your journey on Xpand

If you need a high performance, elastically scalable database or just want to kick the tires of a distributed SQL database, these resources are a great place to start: