Comparison: MemCP vs. MySQL

Comparison: MemCP vs. MySQL

MemCP is a Beta persistent main-memory, column-oriented database with MySQL protocol compatibility. It targets mixed operational and analytical workloads whose active columns benefit from compression, batch scans, and in-process APIs. It is not a complete replacement for every MySQL feature.

Area	MySQL	MemCP
Physical layout	Primarily row-oriented InnoDB pages	Compressed columns with main and delta storage
Working set	Buffer pool and operating-system cache	Active data and query structures managed in RAM, with reloadable persistent columns
Compatibility	Reference implementation of the MySQL dialect/protocol	Tested subset plus compatibility syntax; unsupported features remain
Durability	InnoDB configuration and transaction log	Per-table safe, logged, sloppy, memory, or cache
APIs	Normally accessed through a connector	MySQL protocol, SQL/HTTP, PostgreSQL-syntax HTTP, RDF, and embedded Scheme handlers
Analytical scans	Row pages and secondary indexes; columnar analytics commonly use another system or service	Compressed columns, batch reads, late materialization, parallel shards, and fused scan pipelines
Operational writes	Mature InnoDB row storage, locking, replication, and tooling	Main/delta column storage, indexes, constraints, transactions, and per-table durability for mixed read/write workloads
Query optimization	Mature general-purpose MySQL optimizer	Logical decorrelation and join ordering followed by cost-based selection of scans, RecSets, caches, computed columns, and streaming paths
Extensibility	Functions, plugins, stored programs, and a large connector ecosystem	Embedded functional Scheme runtime, custom HTTP/WebSocket handlers, SQL operator extensions, and Go storage engine
Operations	Established backup, replication, HA, cloud, and observability ecosystem	Built-in dashboard/process controls and multiple storage backends; smaller ecosystem and no production multi-node cluster today

Where MemCP can be attractive

• Mixed operational and analytical work: recent inserts and updates can feed dashboards, grouping, and ad-hoc analysis without first exporting a second analytical copy.
• Wide tables and selective columns: a query can read only referenced compressed columns instead of moving whole rows through memory.
• Repeated categories and ordered values: dictionary, bit-packed, sparse, prefix, and sequence representations can reduce cache traffic when the data distribution fits.
• Application-specific services: SQL over HTTP and embedded Scheme handlers can remove a separate middleware-to-database connection for small, carefully secured services.
• Per-table trade-offs: durable business data and reconstructible caches can use different engines in one instance.
• Natural ordering: MemCP exposes collations that can compare embedded numeric parts naturally, so values such as 2 and 10 need not sort lexicographically as 10 before 2.
• Flexible identifiers: long logical column names are not tied to the local persistence filename; long names are represented with stable hashed filenames. Applications must still test connector and MySQL-metadata limits.

These are architectural opportunities, not automatic wins. Point-heavy workloads that already fit MySQL's buffer pool, write-heavy contention, poorly compressible high-cardinality values, cold remote storage, or an unsuitable physical plan may favor MySQL.

Current measured trade-off

In project measurements, OLAP and search-oriented cases have reached speedups of 10× and more over MariaDB/PostgreSQL. RecSets are particularly important for large filtered domains and correlated membership workflows: they carry compact record-ID sets between operators, can adapt between ranges, sparse IDs and bitmaps, and let later column scans avoid materializing wide intermediate rows.

Isolated OLTP paths have taken about 1.3–2.0× as long as on the established database. In complete WordPress- and wiki-style page builds, however, this has made no significant difference to overall page-loading time in the measured application workflows; the end-to-end effect depends on the application's own work and on the number and mix of SQL queries it issues. That is real optimization debt, especially for high-throughput APIs, but it is a different user impact from a filtered-list search over roughly one million documents in which the same query took around 30 seconds on PostgreSQL and 1.6 seconds on MemCP. MemCP's immediate performance case is strongest where users wait on search, filtering, grouping and analytical reads.

The figures are workload observations rather than a promise for arbitrary SQL. Reproduce them with the deployed versions, representative data, equal durability and successful responses; see Performance Measurement.

Write performance can be chosen independently per table. Project measurements have reached about 10× the safe write throughput after switching to logged, because the latter omits the commit-time fsync. This is not a free optimization: process-crash recovery remains, but recent commits may be lost on kernel crash or power failure. sloppy goes further by omitting the WAL and normally publishing one compressed generation every 15 minutes; for reconstructible data this can reduce continuous SD-card writes and flash wear.

Where MySQL remains the safer default

Choose or retain MySQL when the application requires complete MySQL behavior, certified vendor integrations, mature replication and failover, a broad managed-service ecosystem, XA/distributed transactions, or operational practices that have not yet been reproduced and tested for MemCP. Protocol compatibility does not mean every SQL feature, metadata query, collation, error code, or connector edge case behaves identically.

Migration and fair evaluation

Start with a copy or shadow workload rather than replacing the source of truth. Import the schema/data, run representative reads against both systems, compare results, exercise constraints and transaction conflicts, restart and restore, then measure latency and throughput. Keep MySQL available until SQL compatibility, durability, capacity, and operational rollback gates are satisfied.

Performance and compression depend on schema, cardinality, ordering, query shape, hardware, durability mode, cache state, and concurrency. Numeric comparisons must name both versions, dataset, query, hardware, ENGINE, warm-up procedure, number of successful responses, and raw samples. The observed order-of-magnitude OLAP/search gains—including the 30-second versus 1.6-second filtered-list case—and historical compression reductions are important evidence for MemCP's target workloads, but they are not universal guarantees.

MemCP nevertheless aims to outperform conventional row storage for its target workloads. Publishing current losses—including application suites that remain interpreter- or compile-time-bound—is part of reaching that goal, not a reason to erase the performance proposition.

If SQL performance is the limiting factor, the useful next step is not another abstract feature comparison: run the slow production-shaped queries against MemCP. When result parity, durability, operations and rollback gates pass and MemCP wins the workload, move that workload to MemCP. Start with the hands-on evaluation.

See Migration from MySQL and PostgreSQL, Performance Measurement, Supported SQL, Database Tools compatibility with MemCP, and Persistency and Performance Guarantees.