tag:blogger.com,1999:blog-553267881220650796.post8341902396551303969..comments2023-03-10T10:14:51.673+01:00Comments on The MySQL n00b: MyRocks: migrating a large MySQL dataset from InnoDB to RocksDB to reduce footprintRick Pizzihttp://www.blogger.com/profile/11150796911364427515noreply@blogger.comBlogger18125tag:blogger.com,1999:blog-553267881220650796.post-16909687818266390232018-06-05T12:04:40.611+02:002018-06-05T12:04:40.611+02:00Very helfpul article about migrating DB Very helfpul article about migrating DB Frankhttps://www.advoxstudio.com/noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-25550655759644158522017-11-06T14:25:09.834+01:002017-11-06T14:25:09.834+01:00I understand your unhappiness. But the team is sma...I understand your unhappiness. But the team is small and we can't fix everything right away. Of course, we are happy to take contributions.Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-58691849450116229652017-11-06T10:55:19.265+01:002017-11-06T10:55:19.265+01:00True, binary collation brings case sensitive match...True, binary collation brings case sensitive matches. Not sure if the MyRocks team will ever change this requirement (of having binary collation), I think it is tied to how the LSM engine work, but I may be wrong; I haven't looked at the latest MyRocks deployments...Rick Pizzihttps://www.blogger.com/profile/11150796911364427515noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-4671157007564010122017-11-06T03:39:09.636+01:002017-11-06T03:39:09.636+01:00I am unhappy with Rocks for one reason, the binary...I am unhappy with Rocks for one reason, the binary collation does not allow case-insensitive searches, so it breaks any application that was ever working with innnodb or tokudb. Please correct me if I am wrong.William of Baskervillehttps://www.blogger.com/profile/14325937204875972131noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-63625795073402420252017-06-20T07:36:06.425+02:002017-06-20T07:36:06.425+02:00Column family usage is enabled by a special syntax...Column family usage is enabled by a special syntax in per-index comments in a create table statement. If you aren't doing that then you aren't using column families.<br /><br />From the my.cnf details above I don't know why RSS is 20gb. Which memory allocator do you use -- glibc, tcmalloc or jemalloc? I use jemalloc most of the time and RSS with glibc malloc has been too high in the past -- http://smalldatum.blogspot.com/2015/10/myrocks-versus-allocators-glibc.htmlMark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-16663721092771617612017-06-20T06:40:52.723+02:002017-06-20T06:40:52.723+02:00I get it from FB Mysql : https://github.com/facebo...I get it from FB Mysql : https://github.com/facebook/mysql-5.6<br /><br />What does you mean 'using column families' ?<br />All my config in my above comment.<br />And memory that used by mysqld is from Resident size (RSS).Longhttps://www.blogger.com/profile/04101086771679252126noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-6987624922487354132017-06-20T05:41:51.324+02:002017-06-20T05:41:51.324+02:00A better place for questions is https://groups.goo...A better place for questions is https://groups.google.com/forum/#!forum/myrocks-devMark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-3667577689137214282017-06-20T05:41:04.304+02:002017-06-20T05:41:04.304+02:00From where do you get MyRocks -- MariaDB, Percona,...From where do you get MyRocks -- MariaDB, Percona, FB MySQL?<br /><br />Are you using column families?<br /><br />When mysqld uses more than 20gb of RAM is that from RSS or VSZ?Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-2199294019099376862017-06-19T10:48:46.444+02:002017-06-19T10:48:46.444+02:00Hi,
I'm new and don't know how to limit me...Hi,<br />I'm new and don't know how to limit memory size that MyRocks use.<br />My my.cnf:<br />[mysqld]<br />rocksdb<br />default-storage-engine=rocksdb<br />skip-innodb<br />default-tmp-storage-engine=MyISAM<br />collation-server=utf8_bin<br />character-set-server = utf8<br />transaction-isolation=READ-COMMITTED<br /><br />log-bin<br />binlog-format=ROW<br />user=zdeploy<br /><br />datadir=/home/zdeploy/myrock/data<br />port=3333<br /><br />rocksdb_max_open_files=-1<br />rocksdb_base_background_compactions=1<br />rocksdb_max_background_compactions=8<br />rocksdb_max_total_wal_size=2G<br />rocksdb_max_background_flushes=4<br />rocksdb_block_size=16384<br />rocksdb_block_cache_size=2G<br />rocksdb_table_cache_numshardbits=6<br /><br /># rate limiter<br />rocksdb_bytes_per_sync=4194304<br />rocksdb_wal_bytes_per_sync=4194304<br />rocksdb_rate_limiter_bytes_per_sec=104857600 #100MB/s. Increase if you're running on higher spec machines<br /><br /># read free replication<br />#rocksdb_rpl_lookup_rows=0<br /><br />rocksdb_default_cf_options=write_buffer_size=1024m;target_file_size_base=256m;max_bytes_for_level_base=512m;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=10;level0_stop_writes_trigger=15;max_write_buffer_number=4;compression_opts=-14:1:0;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=1};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;compaction_pri=kMinOverlappingRatio<br /><br />My expectation is MyRocks doesn't use more than 10GB memory of RAM, but now it is using >20G. I read the document but can't find anything can help.<br /><br />Thank you!Longhttps://www.blogger.com/profile/04101086771679252126noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-23453623836723332602016-11-11T10:35:30.735+01:002016-11-11T10:35:30.735+01:00I have executed a table checksum against the MyRoc...I have executed a table checksum against the MyRocks slave and am pleased to tell that the slave is 100% consistent with the InnoDB master. Yay!!Rick Pizzihttps://www.blogger.com/profile/11150796911364427515noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-23525475919979278562016-11-03T15:41:05.132+01:002016-11-03T15:41:05.132+01:00Here's my try at it... http://mysqlnoob.blogsp...Here's my try at it... http://mysqlnoob.blogspot.com/2016/11/can-myrocks-and-innodb-play-along-in-mysql.htmlRick Pizzihttps://www.blogger.com/profile/11150796911364427515noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-41594978836755801552016-11-02T15:51:32.414+01:002016-11-02T15:51:32.414+01:00Gave it a try but already found issues..... https...Gave it a try but already found issues..... https://github.com/facebook/mysql-5.6/issues/386Rick Pizzihttps://www.blogger.com/profile/11150796911364427515noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-60596588100279440362016-11-02T12:31:21.358+01:002016-11-02T12:31:21.358+01:00For this particular workload, with above config, l...For this particular workload, with above config, looks like it's not using more than 3.5G anyways regardless what I have set above, so the unused memory still goes into the OS cache.Rick Pizzihttps://www.blogger.com/profile/11150796911364427515noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-89574819539695581892016-11-01T09:35:37.460+01:002016-11-01T09:35:37.460+01:00I've tried at first to avoid disabling InnoDB ...I've tried at first to avoid disabling InnoDB but after a mysqld crash I have been unable to complete crash recovery properly. Since I wanted a MyRocks-only slave I didn't fdig the issue deeper. But will certainly experiment with the combo in the coming weeks!Rick Pizzihttps://www.blogger.com/profile/11150796911364427515noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-11001119266674799772016-11-01T07:02:44.477+01:002016-11-01T07:02:44.477+01:00MyRocks + InnoDB has been supported for some time....MyRocks + InnoDB has been supported for some time. Our communication about that could have been better.<br />https://github.com/facebook/mysql-5.6/issues/106Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-1251022482678843852016-10-31T22:38:47.918+01:002016-10-31T22:38:47.918+01:00It looks like --allow-multiple-engines option will...It looks like --allow-multiple-engines option will allow both engines at the same time (https://github.com/facebook/mysql-5.6/commit/c380d14ee50059dcefc35da13d8713bbcad72061).Alexey Maykovhttps://www.blogger.com/profile/06295810199316638919noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-70435174078921051222016-10-31T21:53:35.774+01:002016-10-31T21:53:35.774+01:00Mark, you have been really helpful and got me goin...Mark, you have been really helpful and got me going in the right direction, so a big thank you for your great posts on RocksDB and MyRocks (and for the hard work) and I am really pleased by your visit here to my newborn small space on the Internet. I'm looking forward for the day when RocksDB will coexist with InnoDB and I'm sure I'll find many many use cases for it. <br /><br />Regarding the memory for the block size cache, although I have given 50% so far it is only using about 10%, my guess is that it only uses what it needs depending on the actual dataset. I confess I need to learn a bit more about the internals, but I was so eager to give it a try, I just couldn't wait :-)<br /><br />Thanks again!<br />RickRick Pizzihttps://www.blogger.com/profile/11150796911364427515noreply@blogger.comtag:blogger.com,1999:blog-553267881220650796.post-29687868735196431552016-10-31T20:50:52.217+01:002016-10-31T20:50:52.217+01:00Wow. Sorry for not resolving your open issues. The...Wow. Sorry for not resolving your open issues. The QA team is small today (me).<br /><br />My theory is to use between 20% and 30% of RAM for the RocksDB block cache when sizeof(database) >> sizeof(RAM). But I have yet to confirm that with benchmark results -- http://smalldatum.blogspot.com/2016/09/tuning-rocksdb-block-cache.htmlMark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.com