4 ms·

I've migrated from MySQL to Postgres to use CitusDB (for a company project, many years ago). For my own startup which the primary use-case is analytics, I have

I've migrated from MySQL to Postgres to use CitusDB (for a company project, many years ago). For my own startup which the primary use-case is analytics, I have moved back from Postgres to MemSQL. The four reasons were :- 1) In-memory rowstore, thus eliminating my in-memory nosql dependency. 2) The ability to join in-memory tables with disk to provide up-to the second data is awesome. 3) Multiple read/writer group nodes. 4) The biggest for me. Columnar storage. Some examples on how columnar storage is so awesome. Note that I am comparing MariaDB vs MemSQL on a old desktop. 8 cores, 8 GB Ram, HD (non ssd) drive. - A query with a count of 1 table with 50m rows. MariaDB - 49.60 sec MemSQL - 1.12 sec - A query with a count, sum, where clause and group by, with 50m rows. MariaDB - 17 min 44.84 sec MemSQL - 8 seconds --------- Here are some things to be aware. Prior to migrating over to Citus. We were shopping for MySQL consultants to see whether they could improve on the above figures. They quoted $70-100k to work on the project, without guaranteeing results. With Citus I was able to see far better results and this is why the company switched over. However fast forward to now, MemSQL beats Citus soundly. 1 desktop beats a cluster. I wager, that with a MemSQL cluster with decent hardware on the cloud, I will be seeing sub 1 second queries across the board. Oh and the amount of data? It's tens of billions of rows a month. That's the workload I'm talking about here. --------- This post isn't to bash postgres. I loved using it. If Postgres 11/12, included columnar storage. I would literally scream! :)

lngnmn 10y ago
Comparing PostgreSQL with MemSQL is like comparing a steam locomotive with a steam ship. PostgreSQL ensures on-disk data consistency for each transaction. MemSQL does not implement this kind of complex logic. VoltDB and MemSQL are of totally different kind of products. There is the CMU database course which explains the details.
- manigandham 10y ago
  MemSQL has full persistence and supports transactions. There is async lag to fsync in-memory data to disk, which is configurable, but obviously this is a trade-off with RAM based operations like any other database. It runs as a distributed cluster so it's faster and safer than typical single-node (with replica) installations. For primary operational datastores, it's probably not a fit, but it's great for real time data warehouse needs. VoltDB is on the other end of the spectrum and more exotic, designed for purely real-time in-memory SQL transactions, almost similar to in-memory datagrids like Apache Ignite/GridGain or Hazelcast.
  - jhugg 10y ago
    > There is async lag to fsync in-memory data to disk, which is configurable, but obviously this is a trade-off with RAM based operations like any other database. As a VoltDB dev, I don't accept this. Performance and sync disk persistence for every transaction don't have to be mutually exclusive, they just require the right architecture and engineering work. We get our speed from memory-centric data structures, and by never waiting on disks/users in a transaction. Doing batch commits (fsyncs) to the WAL every few MS means you can keep your throughput very close to an async thoughput without trading any persistence. I'm not sure how exotic VoltDB is, but I agree it's a different animal than MemSQL. VoltDB is focused on transactional and operational workloads, while MemSQL is more of a pure analytics play. It has "transactions", but with weaker guarantees all-around. We take our guarantees very seriously (see Jepsen https://aphyr.com/posts/331-jepsen-voltdb-6-3 https://aphyr.com/posts/331-jepsen-voltdb-6-3). VoltDB does do "real-time analytics", which we typically define as analytics you can do in milliseconds. This leverages materialized views, special sorted indexes with ranking support, and lots of other tricks that MemSQL doesn't focus on. This enables our users to transactionally respond to an event as it happens in a few milliseconds, all while leveraging gigabytes or terabytes of state.
    - manigandham 10y ago
      > Doing batch commits (fsyncs) to the WAL every few MS means you can keep your throughput very close to an async thoughput without trading any persistence. This is what I said. There's still a lag, regardless of disk speed, batching, append-only parallel writes, etc. It's physics. MemSQL and VoltDB and other in-memory db with persistence do it the same. Not much of an issue these days with distributed systems anyway since the network is faster and the data is already on another node.
      - jhugg 10y ago
        1) There's no lag between VoltDB confirming to clients that a write has been committed and that write being on disk. Zero. 2) The way VoltDB does persistence is actually quite different than MemSQL. VoltDB is able to write deterministic logical ops before the work even starts, while Mem has to write binary logs after the work is finished. The difference in practical terms is dramatic and will show up in almost any benchmark.
lima 10y ago
And apparently ClickHouse out-performs MemSQL: https://clickhouse.yandex/benchmark.html https://clickhouse.yandex/benchmark.html Has anyone here experience with ClickHouse?
- dignan 10y ago
  No experience, but did a thorough read through of the docs. One thing to keep in mind about clickhouse is that their replication guarantees aren't very strong. From the docs: "There are no quorum writes. You can't write data with confirmation that it was received by more than one replica." That's pretty troubling, but at least they're open about it. That said their performance claims are pretty spectacular, and it seems solidly engineered. Further if you're not planning on using replication it certainly seems interesting. I'd be curious to hear about someone's production experience as well, since the list of companies running it seems rather thin.
  - ztlpn 10y ago
    (ClickHouse dev here) Yes, replication in ClickHouse is asynchronous by default. For intended use cases (OLAP queries aggregating data from many rows) data that is a few seconds stale is usually okay. In a serious production deployment you absolutely should enable replication, otherwise you risk losing all your data, not just last couple of seconds of inserts. That said, sometimes synchronous replication is necessary despite the latency penalty that comes with it. This feature is actually implemented but not yet considered ready for prime time. We have several years of production experience with ClickHouse (as a DBMS powering Yandex.Metrica - second largest web analytics system in the world). If you have questions - just ask.
- hblanks 10y ago
  We're using it internally, and also for a relatively small customer-facing analytics API, on the order of 10-30 nodes. It's been good so far. Good support from the maintainers, too.
deleted 10y ago
[deleted]
orf 10y ago
> Note that I am comparing MariaDB vs MemSQL on a old desktop. 8 cores, 8 GB Ram, HD (non ssd) drive. ... so not really a good comparison then. You're comparing the speed of your 8GB of RAM to an old, spinning bit of metal on an old desktop. Anyone who would use such a benchmark to drive their tech choices is being rather silly.
- no1youknowz 10y ago
  You're missing the point entirely. I'm showing that a columnar store beat out a rowstore. With insane results. Not only that, but MemSQL on a really crappy desktop beat out a CitusDB cluster of 5 bare-metal servers, each with E5-2620 CPU v2, 128GB Ram, 10x4TB 7200 RPM SATA Drives, RAID 10. I actually still have the communication with Citus and here's similar workloads they worked on for me: Query 7: local 1139.0s, distributed 76.2s (~15x improvement) I could dig up the query, but I'm pretty sure it's similar. But guess what. It took days of consulting and optimising with CitusDB and one of their engineers. With MemSQL I was getting insane results in a matter of hours. Talking about costs. A MemSQL cluster is going to be significantly cheaper to run than a Citus/Postgres cluster when looking at tens of billions of rows. I know because I'm looking at the CitusDB numbers and the MemSQL numbers when using enterprise products and bare-metal servers.
manigandham 10y ago
We use MemSQL and yes it's great, it's really meant as a data warehouse built for speed and realtime inserts/updates and queries. Postgres has CStore today but even with included columnstore, it still isn't distributed which is one of the issues. Citus is looking into it though so maybe it'll all come together in a few years.

lngnmn 10y ago

Comparing PostgreSQL with MemSQL is like comparing a steam locomotive with a steam ship. PostgreSQL ensures on-disk data consistency for each transaction. MemSQL does not implement this kind of complex logic. VoltDB and MemSQL are of totally different kind of products. There is the CMU database course which explains the details.

manigandham 10y ago
MemSQL has full persistence and supports transactions. There is async lag to fsync in-memory data to disk, which is configurable, but obviously this is a trade-off with RAM based operations like any other database. It runs as a distributed cluster so it's faster and safer than typical single-node (with replica) installations. For primary operational datastores, it's probably not a fit, but it's great for real time data warehouse needs. VoltDB is on the other end of the spectrum and more exotic, designed for purely real-time in-memory SQL transactions, almost similar to in-memory datagrids like Apache Ignite/GridGain or Hazelcast.
- jhugg 10y ago
  > There is async lag to fsync in-memory data to disk, which is configurable, but obviously this is a trade-off with RAM based operations like any other database. As a VoltDB dev, I don't accept this. Performance and sync disk persistence for every transaction don't have to be mutually exclusive, they just require the right architecture and engineering work. We get our speed from memory-centric data structures, and by never waiting on disks/users in a transaction. Doing batch commits (fsyncs) to the WAL every few MS means you can keep your throughput very close to an async thoughput without trading any persistence. I'm not sure how exotic VoltDB is, but I agree it's a different animal than MemSQL. VoltDB is focused on transactional and operational workloads, while MemSQL is more of a pure analytics play. It has "transactions", but with weaker guarantees all-around. We take our guarantees very seriously (see Jepsen https://aphyr.com/posts/331-jepsen-voltdb-6-3 https://aphyr.com/posts/331-jepsen-voltdb-6-3). VoltDB does do "real-time analytics", which we typically define as analytics you can do in milliseconds. This leverages materialized views, special sorted indexes with ranking support, and lots of other tricks that MemSQL doesn't focus on. This enables our users to transactionally respond to an event as it happens in a few milliseconds, all while leveraging gigabytes or terabytes of state.
  - manigandham 10y ago
    > Doing batch commits (fsyncs) to the WAL every few MS means you can keep your throughput very close to an async thoughput without trading any persistence. This is what I said. There's still a lag, regardless of disk speed, batching, append-only parallel writes, etc. It's physics. MemSQL and VoltDB and other in-memory db with persistence do it the same. Not much of an issue these days with distributed systems anyway since the network is faster and the data is already on another node.
    - jhugg 10y ago
      1) There's no lag between VoltDB confirming to clients that a write has been committed and that write being on disk. Zero. 2) The way VoltDB does persistence is actually quite different than MemSQL. VoltDB is able to write deterministic logical ops before the work even starts, while Mem has to write binary logs after the work is finished. The difference in practical terms is dramatic and will show up in almost any benchmark.

lima 10y ago

And apparently ClickHouse out-performs MemSQL: https://clickhouse.yandex/benchmark.html https://clickhouse.yandex/benchmark.html Has anyone here experience with ClickHouse?

dignan 10y ago
No experience, but did a thorough read through of the docs. One thing to keep in mind about clickhouse is that their replication guarantees aren't very strong. From the docs: "There are no quorum writes. You can't write data with confirmation that it was received by more than one replica." That's pretty troubling, but at least they're open about it. That said their performance claims are pretty spectacular, and it seems solidly engineered. Further if you're not planning on using replication it certainly seems interesting. I'd be curious to hear about someone's production experience as well, since the list of companies running it seems rather thin.
- ztlpn 10y ago
  (ClickHouse dev here) Yes, replication in ClickHouse is asynchronous by default. For intended use cases (OLAP queries aggregating data from many rows) data that is a few seconds stale is usually okay. In a serious production deployment you absolutely should enable replication, otherwise you risk losing all your data, not just last couple of seconds of inserts. That said, sometimes synchronous replication is necessary despite the latency penalty that comes with it. This feature is actually implemented but not yet considered ready for prime time. We have several years of production experience with ClickHouse (as a DBMS powering Yandex.Metrica - second largest web analytics system in the world). If you have questions - just ask.
hblanks 10y ago
We're using it internally, and also for a relatively small customer-facing analytics API, on the order of 10-30 nodes. It's been good so far. Good support from the maintainers, too.

deleted 10y ago

[deleted]

orf 10y ago

> Note that I am comparing MariaDB vs MemSQL on a old desktop. 8 cores, 8 GB Ram, HD (non ssd) drive. ... so not really a good comparison then. You're comparing the speed of your 8GB of RAM to an old, spinning bit of metal on an old desktop. Anyone who would use such a benchmark to drive their tech choices is being rather silly.

no1youknowz 10y ago
You're missing the point entirely. I'm showing that a columnar store beat out a rowstore. With insane results. Not only that, but MemSQL on a really crappy desktop beat out a CitusDB cluster of 5 bare-metal servers, each with E5-2620 CPU v2, 128GB Ram, 10x4TB 7200 RPM SATA Drives, RAID 10. I actually still have the communication with Citus and here's similar workloads they worked on for me: Query 7: local 1139.0s, distributed 76.2s (~15x improvement) I could dig up the query, but I'm pretty sure it's similar. But guess what. It took days of consulting and optimising with CitusDB and one of their engineers. With MemSQL I was getting insane results in a matter of hours. Talking about costs. A MemSQL cluster is going to be significantly cheaper to run than a Citus/Postgres cluster when looking at tens of billions of rows. I know because I'm looking at the CitusDB numbers and the MemSQL numbers when using enterprise products and bare-metal servers.

manigandham 10y ago

We use MemSQL and yes it's great, it's really meant as a data warehouse built for speed and realtime inserts/updates and queries. Postgres has CStore today but even with included columnstore, it still isn't distributed which is one of the issues. Citus is looking into it though so maybe it'll all come together in a few years.