6 ms·
A Look at MyRocks Performance
- aphextron 8y agoReally confusing title with "Facebooks" missing an apostrophe
- threeseed 8y agoCan we instead just have the original title: A Look at MyRocks Performance
- tlb 8y agoChanged, thanks.
- PeterZaitsev 8y agoThanks. Fixed. Can't ever figure out when apostrophe is needed :)
- threeseed 8y agoMySQL's pluggable engine really is a killer feature. From these results you would think it makes sense to have RocksDB the default for MySQL and then have InnoDB be there for the larger users.
- PeterZaitsev 8y agoNote MyRocks really excels when data set is larger than memory. For mostly in-memory workloads many smaller users have Innodb is better.
- bogomipz 8y agoCan you explain? I thought Rocks was an LSM storage engine and mostly excelled with write heavy work loads.
- PeterZaitsev 8y agoThe benefit of LSM for Write workloads is a lot higher with large data sets. Optimizing BTREE in memory is cheap, BTREE on disk requires a lot of IO LSM however does not require as much disk IO for inserts even if data is much larger than memory
- bogomipz 8y agoI'm not following. Myrocks uses a clustered key for the primary key from what I have read. Isn't this the same as innodb? Is it not a B-Tree? Could you elaborate on what's special about indexes - primary or secondary in Myrocks?
- PeterZaitsev 8y agoMyRocks is LSM based. The LSM is different from BTREE in what you need to do multiple "physical" lookups and merge result to perform one logical lookup. There are some tricks like Bloom Filters are employed to optimize it but it is still not as efficient in memory as BTREE especially for range lookups. Here is some information on WIKIPEDIA https://en.wikipedia.org/wiki/Log-structured_merge-tree https://en.wikipedia.org/wiki/Log-structured_merge-tree
- bogomipz 8y ago
- deleted 8y ago[deleted]
- cat199 8y agoJust going to point out that percona IMHO is potentially hugely underappreciated in the MySQL world... At $work we are managing a fairly heavy MySQL instance and having scaling issues (~5TB, lots of blobs, and frequent user-managed schema changes for interactive/batch data analysis, many concurrent complex joins involving multiple 1M+ row tables) and after reviewing the various MySQL derivatives, it seems that the percona ppl are really focusing on 'real deal' operational reliability/scalability issues... (e.g. minimizing transaction locking issues for coherent backups, real-time db cloning, replication data integrity testing, etc). I strongly recommend anyone looking at mysql flavors to not overlook their offerings. Looking forward to doing some production tests of their XtraDB mysql flavor in the coming months (for now usage has been limited to unit tests and using the percona toolkit/xtrabackup) Also: no I am not a paid sponsor.
- chucky_z 8y agoI've found MySQL to have issues with datasets that don't fit into the innodb buffer pool. Are you doing a lot of partitioning to make everything work well? If everything is behind an ORM, have you considered trying out PostgreSQL w/ the MySQL FDW? pgsql 10's improvements to FDW's make it a truly viable option. Also to clarify: I'm just saying this as pgsql seems to handle queries that involve disk access and large datasets much better. I have found that as long as your hot dataset fits into the innodb buffer pool and/or you're doing only key lookups (e.g.: select * from tbl where pk=1;) MySQL is most certainly faster for real-world usage. If you're pulling and sorting/filtering millions (or billions) of rows per query, I find that pgsql stands up extremely well to that. Even in a single huge server setup. Also a semi-unrelated ditty, but as someone who has to run migrations on pretty big (100m rows) tables, ALTER TABLE LOCK=NONE ALGORITM=INPLACE is pretty great (here's a table of what you can user per-situation https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-...) If you made it this far in my comment you'll probably realize I didn't grasp the article well. I just re-read it and tl;dr, MyRocks works well (as expected) with datasets that don't fit into the innodb buffer pool. :)
- cat199 8y ago
- alfiedotwtf 8y agoGood to see Percona get some love on HN. They put out really solid work, and have great blog posts!
- niroze 8y agoIndeed! Their expertise and quality are something I truly value. Percona Server is killer.
- PeterZaitsev 8y agoWait to see when Percona Server 8 comes out :)
- niroze 8y agoExciting!
- amanzi 8y agoCould this be a solution for those of us trying to run a big databases on a small VPS?
- dajonker 8y agoPerhaps, but it depends on your workload, as is described in the article. In any case: do your own measurements on your own database/application to see if it works for you.