> "With Clustrix, you never have to worry about database scalability again."
That's a huge claim. My two super-basic questions, which I can't find answered on the site, are:
1) Let's say I need to join 100,000 rows in one table to 100,000 rows in another table, all matching ID's and indexed, and then SUM certain values in the second table. If Clustrix has distributed the rows/tables across different machines, how long is this going to take, compared to a single-machine solution?
2) How do transactions work on a single table? If four people are doing INSERT's with an AUTOINCREMENT on the same table, but on four different machines (because it's distributed), how is this going to wind up being faster than on a single server? How are the four new ID's not going to collide? And since presumably one machine is responsible for preventing collision, how does that machine not become a bottleneck?
I'd love to know what kind of advanced techniques can solve these problems.
1) Both joins and aggregates are processed in parallel. In short, the larger your cluster, the faster we evaluate a complex query because we can bring more hardware to the problem.
You can get a better idea of how we scale distributed joins from one of my blog posts:
Once we complete the join, we will also perform the aggregate SUM operation across multiple nodes, getting individual sums, and then forwarding the results to another node for a final tally.
As a rule of thumb, each node has the throughput of about 70% of MySQL on the same hardware. So if you have a complex query that can take advantage of the cluster (100k rows per table examined qualifies), then you will see a 10x speedup on a 16 node cluster over a single instance.
However, that assumes that your entire data set fits in cache on the single instance box. As soon as that's no longer true, it's more likely that you will see a 100x or more speedup from Clustrix because you will have way more memory available for the buffer cache.
For example, if you have a 32GB single instance MySQL server, but you have more than 64GB of working set size, you're going to be limited by IO.
On Clustrix, if you move this to say a 4 node cluster, you are going to have an effective cache footprint of 128GB. So Clustrix will operate entirely out of cache.
2) We're fully ACID compliant using a two phase commit w/ PAXOS for transaction resolution. Because we're MVCC, readers never have to take out locks. To coordinate WRITE access to the same data, we have a row level distributed lock manager. That means that the entire cluster participates in lock management.
So yes, this has a higher communication overhead over a single server. If you have a small write workload with very little concurrency, you will see better query latencies from a single instance database.
However, as soon as you start getting to more serious write workloads with higher concurrency, Clustrix will distribute your writes across several machines in the cluster. We can handle an aggregate write throughput you can't touch with a single instance database, even with a fancy/expensive storage array.
One of our customers, for example, has a peak throughput of 70,000 transactions/sec. They're about 90/10 read/write, so that breaks down to 10,000 TPS for writes. And they are really fond of multi-row updates, insert .. ignore, insert .. on dup update kind of queries. So it's not exactly a lightweight key-value application. Prior to Clustrix, their MySQL installation peaked out at 5,500 TPS total (read+write). They now have a 15 node Clustrix cluster.
It's true, you can always construct a workload that will not distribute well. But these are generally rare degenerate cases that stem from poor application design, and would perform equally poorly on a single instance database.
How do you handle distributed joins with group by's? Are there certain SQL syntaxes which aren't handled well by queries? What are the 90th percentile response times?
We have been using clustrix at AOL for quite a while. You can check their site for our testimonial. We find it to be reliable and easy to use. It requires so little care and feeding we haven't had a full time DBA on our application for several months. We've done consolidations from a sharded MySQL environment to clustrix using nothing more complex than MySQL replication. We also been able to make online schema changes without locking the DB on very large tables in excess of 400 million rows. Support is quite proactive as well both for regular release upgrades as well as features additions and minor bug fixes. We've been in production for more than 2 years and other than minor issues Clustrix has been trouble free.
Clustrix is legit. Running it in production now (on their all-SSD hardware, not the new AWS version) across two physical sites. Functionality is sufficient, performance is admirable, support is top shelf.
Their bet was contrarian: embracing SQL -- specifically MySQL wire & dialect compatibility -- at a time when the open source options were trending toward NoSQL.
For funded start-ups with a calculable data-to-revenue ratio, Clustrix buys you the option for arbitrary future expansion while retaining the functional benefits of the RDBMS stack.
Once your product succeeds to a level where daily operational reporting is necessary, you'll appreciate the ubiquity of SQL: analysts and off-the-shelf tools all speak SQL without the overhead of glue code, data staging, or ETL.
We use it on a large Rails e-commerce site and it works well. All features work as advertised and it is a drop in replacement for mysql. Their support team is also great and is like having your own dba team.
I can also say that I've been participating in the private beta of this AWS version for a week now and it is both stable and performant. Although we use their appliance in production, I'm very excited about this AWS version as it allows us to run clustrix in our testing and staging environments (where we previously had to run mysql).
I think it's a really great thing that more people/companies will be able to use their awesome database without having to purchase an appliance.
We've been using Clustrix in production for two years at TheLadders. Very satisfied with performance, wide feature set, low maintenance cost. Saved money on: not doing sharding; eliminating a full-time DBA position; repurposing hardware used for MySQL slave farm and storage. Clustrix support is excellent.
Worked for an employer who used it. FAAANTASTIC. I met with the entire Executive team out in SFO on Friday, great company. I have never seen anyone take such care in doing massive QA (including month+ long regression testing on every generations of their platform)
Redshift is a column-store database for big analytics workloads and has a lot of very big established competitors, though the cloud space for that is mostly startups. There's a number of products that are really storage backends for postgres, or use its SQL parsing frontend.
What's different about Clustrix compared to all the other distributed databases branding themselves as "NewSQL" is that it's intended for totally traditional OLTP workloads (though it can distribute OLAP queries across the cluster too as a bonus).
"Development of parallel query execution in PostgreSQL proper has been stagnant for quite a while unfortunately"
To clarify, this is related to the parallel execution of a single query. PostgreSQL has been making huge improvements scaling to many cores when there are many concurrent queries.
Even for single queries, postgres has been chipping away at the edges by moving more tasks to background processes.
Minor gripe: Your websites top menu has an item "Solutions", which then seems to have a grab bag of links which as far as I can tell only relate to the single solution (i.e. your main db product). It took me a minute to find the pricing page as a result of it not being a "Solution". Otherwise, if this was even slightly in my price range I would use it in a second, looks like an awesome product.
Yes, but they are in-memory only. I think the largest database they support is 50GB.
It's the kind of solution that works really well if you have key-value store problem where you want really low latencies. Telco call session state and session state for gaming comes to mind.
But beyond those use cases, you start running into a lot of architectural limitations...
does anyone know more about their "unique parallel query approach that generates distributed query plans and query fragments for speed and scale." ? I'm not going to trust a database which internals (or at least core mechanisms) I don't understand. Any links to papers perhaps?
It's different from a typical sharding approach (including what MongoDB does). In their model, you take a single key and distribute your data using that key (e.g. user_id). The problem surfaces when you look at secondary indexes.
If you have a secondary index say on user_location, and you want to query by that index, you don't know which shard to go to. So you end up broadcasting.
Another problem is enforcing unique index constraints.
With Clustrix, every table and index gets its own distribution.
So if you have a schema like this:
foo(a, b, c, d)
unique idx1(b,c)
idx2(d)
Clustrix treats each table and index as a different distribution. So if I need to look something up by d, I know exactly which node has the data. I can also enforce index uniqueness.
I saw claims that Clusterix is very good for OLAP applications. Can you shed more light on it? Does it support materialized views for instance (to speed up calculating aggregation on higher levels) ?
We don't support materialized views at the moment, though I can't think of a reason why we couldn't support them if needed. A big strength for our OLAP performance is simply having CPU and memory resources that scale with storage and a query planner that is smart enough to take advantage of those resources.
Excuse me for, maybe, stupid question, but:
we have few (250+) InnoDB tables and triggers - if we will try to move all data to Clustrix, will our application continue to work? I've read about "drop-in replacement" but maybe somebody here already have experience in same issue.
It's a good question. We built the entire database from the ground up, without any MySQL code. So while we support most of the frequently used features, we don't support everything.
You can get a list of unsupported features and differences from our docs:
Having said that, our customers find that the value that they get out of scale out, fault tolerance, and performance is worth the tradeoffs of not supporting every single MySQL feature.
Honestly, our goal is not to be 100% MySQL compatible. We're aiming to be compatible enough to capture a significant share of existing MySQL installs, but we're really after building a truly scalable relational db.
DBaaS - but what about latency between apps and db servers? If we use own cluster, servers can be placed in local network, but how it's possible with DBaaS model?
That's a huge claim. My two super-basic questions, which I can't find answered on the site, are:
1) Let's say I need to join 100,000 rows in one table to 100,000 rows in another table, all matching ID's and indexed, and then SUM certain values in the second table. If Clustrix has distributed the rows/tables across different machines, how long is this going to take, compared to a single-machine solution?
2) How do transactions work on a single table? If four people are doing INSERT's with an AUTOINCREMENT on the same table, but on four different machines (because it's distributed), how is this going to wind up being faster than on a single server? How are the four new ID's not going to collide? And since presumably one machine is responsible for preventing collision, how does that machine not become a bottleneck?
I'd love to know what kind of advanced techniques can solve these problems.