MariaDB OOM when using MyRocks

[guil...@signal18.io]

Hello,

I use MariaDB 10.2.6 with myrocks to load 1TB of data from CSV files with a single thread.

However, in a couple of hours all the server memory has been used up by mysqld and the kernel kills the process.

Please advise if I did anything wrong.

I used the following configuration:

[mariadb]
plugin-load-add=ha_rocksdb.so
default-tmp-storage-engine=MyISAM
binlog_format=ROW
collation-server=latin1_bin
transaction-isolation=READ-COMMITTED
rocksdb_max_open_files=-1
rocksdb_base_background_compactions=1
rocksdb_max_background_compactions=8
rocksdb_max_total_wal_size=128M
rocksdb_max_background_flushes=4
rocksdb_block_size=16384
rocksdb_block_cache_size=2G
rocksdb_table_cache_numshardbits=6
loose_rocksdb_rpl_lookup_rows=0
rocksdb_default_cf_options=write_buffer_size=128m;target_file_size_base=32m;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_per_level=kLZ4Compression;bottommost_compression=kZSTD;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

[MARK CALLAGHAN]

Thank you for trying MyRocks.

Are you inserting a huge number of rows in one transaction?

Uncommitted changes are buffered in MyRocks so there are two ways to reduce the max memory usage:
1) explicit - commit more frequently to avoid huge transactions
2) implicit - set rocksdb_commit_in_the_middle to get MyRocks to commit for you every N rows

See https://github.com/facebook/mysql-5.6/wiki/data-loading

--
You received this message because you are subscribed to the Google Groups "MyRocks - RocksDB storage engine for MySQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to myrocks-dev+unsubscribe@googlegroups.com.
To post to this group, send email to myroc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/myrocks-dev/b0a08b5a-e4d0-43e1-af21-e082d5aa0525%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Mark Callaghan
mdca...@gmail.com

[guil...@signal18.io]

Hi Mark,

I forgot to mention - I'm using LOAD DATA INFILE to load the data. How does MyRocks handle that?

-GL

To unsubscribe from this group and stop receiving emails from it, send an email to myrocks-dev...@googlegroups.com.

To post to this group, send email to myroc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/myrocks-dev/b0a08b5a-e4d0-43e1-af21-e082d5aa0525%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Mark Callaghan
mdca...@gmail.com

[MARK CALLAGHAN]

I assume that setting rocksdb_commit_in_the_middle for your session is the only option with LOAD DATA INFILE.
Are you willing to try that? I don't have much experience with LOAD DATA and MyRocks.

To unsubscribe from this group and stop receiving emails from it, send an email to myrocks-dev+unsubscribe@googlegroups.com.

To post to this group, send email to myroc...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/myrocks-dev/160e4f1e-2b69-4f9c-a7ec-65a74ce255dc%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Mark Callaghan
mdca...@gmail.com

[guil...@signal18.io]

Hi Mark,

I've tried setting rocksdb_commit_in_the_middle=1, unfortunately the memory leak still happens with MariaDB 10.2.6. I'm willing to try MySQL FB branch if it helps?

rocksdb_bulk_load=1 seems not to work for me either. I get these errors:

ERROR 1105 (HY000) at line 1: [./.rocksdb/crypto_data.market_history_minute_history#P#p201409_PRIMARY_0_0.bulk_load.tmp] bulk load error: Invalid argument: Global seqno is required, but disabled

Error (Code 1105): [./.rocksdb/crypto_data.market_history_minute_history#P#p201409_PRIMARY_0_0.bulk_load.tmp] bulk load error: Invalid argument: Global seqno is required, but disabled

Error (Code 1105): Failed to add a key to sst file writer([./.rocksdb/crypto_data.market_history_minute_history#P#p201409_PRIMARY_0_0.bulk_load.tmp] Invalid argument: Global seqno is required, but disabl

ed)

I'm not sure what that means. Is it an issue with PK ordering?

-GL

To view this discussion on the web visit https://groups.google.com/d/msgid/myrocks-dev/160e4f1e-2b69-4f9c-a7ec-65a74ce255dc%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Mark Callaghan
mdca...@gmail.com

[MARK CALLAGHAN]

Can you move this to the MariaDB discuss list?
https://launchpad.net/~maria-discuss

To unsubscribe from this group and stop receiving emails from it, send an email to myrocks-dev+unsubscribe@googlegroups.com.

To post to this group, send email to myroc...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/myrocks-dev/4d1f4201-ca2d-4e06-8ea8-a75b79e68395%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Mark Callaghan
mdca...@gmail.com

[Sergey Petrunya]

Hello,

On Thu, Jun 29, 2017 at 5:50 PM, <guil...@signal18.io> wrote:

I forgot to mention - I'm using LOAD DATA INFILE to load the data. How does MyRocks handle that?

(sorry for this answer coming late, hopefully it still could be somehow useful)
 

As far as storage engine is concerned, LOAD DATA INFILE is just a big multi-line insert  (which is done as one huge transaction, uniqueness checks, etc).

Additional settings (like rocksdb_bulk_load, rocksdb_commit_in_the_midddle) are needed to make it faster/use less memory.

[Sergey Petrunya]

On Mon, Jul 3, 2017 at 3:28 PM, <guil...@signal18.io> wrote:

I've tried setting rocksdb_commit_in_the_middle=1, unfortunately the memory leak still happens with MariaDB 10.2.6.

I have tried loading a 39G CSV file (dataset is 22G in MyRocks) on a machine with 16G RAM  with a single LOAD DATA INFILE command.

1. With default settings, the memory is quickly exhaused and mysqld is killed (this is expected)

2.  With @@rocksdb_bulk_load=1, mysqld process' rss was nearly constant and around 320MB

3. With  @@rocksdb_commit_in_the_middle=1, rss was steadily growing, reaching about 670MB by the end of the load.

I think #2 is an indication that there are no leaks on the SQL layer.  #3 might (or might not) look like a leak in MyRocks. I'll try a bigger dataset.

@Guillaume, does your table have secondary indexes?  What datatypes are used?  Is the table partitioned (judging from the names of .tmp files below looks like yes) ? 

rocksdb_bulk_load=1 seems not to work for me either. I get these errors:

ERROR 1105 (HY000) at line 1: [./.rocksdb/crypto_data.market_history_minute_history#P#p201409_PRIMARY_0_0.bulk_load.tmp] bulk load error: Invalid argument: Global seqno is required, but disabled

Error (Code 1105): [./.rocksdb/crypto_data.market_history_minute_history#P#p201409_PRIMARY_0_0.bulk_load.tmp] bulk load error: Invalid argument: Global seqno is required, but disabled

Error (Code 1105): Failed to add a key to sst file writer([./.rocksdb/crypto_data.market_history_minute_history#P#p201409_PRIMARY_0_0.bulk_load.tmp] Invalid argument: Global seqno is required, but disabl

ed)

I'm not sure what that means. Is it an issue with PK ordering?

I am not an expert on this one, and I've got a different error when I intentionally created an issue with PK ordering.

However this comment in include/rocksdb/options.h

   // If set to false, IngestExternalFile() will fail if the file key range

  // overlaps with existing keys or tombstones in the DB.

  bool allow_global_seqno = true;

sounds like that is true.

 

[Guillaume Lefranc]

Hi Sergey,

this is the table structure:

CREATE TABLE `market_history_minute` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `exchange` varchar(4) NOT NULL DEFAULT '',

  `market` varchar(55) NOT NULL DEFAULT '',

  `open` decimal(20,10) DEFAULT NULL,

  `high` decimal(20,10) DEFAULT NULL,

  `low` decimal(20,10) DEFAULT NULL,

  `close` decimal(20,10) DEFAULT NULL,

  `volume` decimal(20,10) DEFAULT NULL,

  `time_start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `time_end` datetime DEFAULT NULL,

  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`exchange`,`market`,`time_start`));

Table was partitioned by monthly range on time_start / no secondary indexes.

Unfortunately I could not succeed in using bulk_load due to the errors described above.

Thanks

Guillaume

--
You received this message because you are subscribed to a topic in the Google Groups "MyRocks - RocksDB storage engine for MySQL" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/myrocks-dev/O-zRSOIztRY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to myrocks-dev+unsubscribe@googlegroups.com.

To post to this group, send email to myroc...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/myrocks-dev/CAGBnosCYMp9GwMxRT1h9KWcEFcqggL6R4vz6drbRD5NYL01V_g%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Guillaume Lefranc

signal18.io

[Sergey Petrunya]

Hi Guillaume,

I would like to know a few more details about the table definition:

-  id column has AUTO_INCREMENT attribute but it's not the primary key?

- Instead, PK is defined as :

  PRIMARY KEY (`exchange`,`market`,`time_start`));

With default MyRocks settings it will cause this error:

Unsupported collation on string indexed column test.market_history_minute#P#p0.exchange Use binary collation (latin1_bin, binary, utf8_bin).

did you resolve this by setting @@rocksdb_strict_collation_* variable to disable the warning, or by using one of the mentioned collations?

BR,

Sergei P.

[Guillaume Lefranc]

Hi Sergey,

Sorry! Just remove the ID auto increment, it was not used in the MyRocks schema.

I also used the binary collation.

Hope that helps

GL

To view this discussion on the web visit https://groups.google.com/d/msgid/myrocks-dev/CAGBnosDpo_oxbrqpJmJdWNGh0WrXjo5H1Vr27kqz-zcjEj7-LA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Guillaume Lefranc

signal18.io

[Sergey Petrunya]

Hi Guillaume,

Thanks for the info.

So I have generated a 95G csv file of random data for that DDL and loaded it. (it produced a 45G datadir).

MariaDB [test]> set rocksdb_commit_in_the_middle=1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> load data infile '/home/ubuntu/2.csv.unpack'

    -> into table market_history_minute

    -> fields terminated by ',';

Stage: 2 oQuery OK, 1000000000 rows affected (10 hours 43 min 37.06 sec)

Records: 1000000000  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> set rocksdb_commit_in_the_middle=0;

Query OK, 0 rows affected (0.00 sec)

The process's RSS was 396K at start and 1,260K at the end.

However, attaching gdb and checking malloc_info() output, I got this :

Before loading started:

<aspace type="total" size="17,403,904"/>

After loading finished:

<aspace type="total" size="17,473,536"/> 

just 70Kb more which I interpret as not (or almost nearly not) leaking memory when running with commit_in_the_middle=1.

I think there's no issue here.

You received this message because you are subscribed to the Google Groups "MyRocks - RocksDB storage engine for MySQL" group.

To unsubscribe from this group and stop receiving emails from it, send an email to myrocks-dev+unsubscribe@googlegroups.com.

To post to this group, send email to myroc...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/myrocks-dev/CAK5J%2B4vf2TcxHD-cCUgDBCnkoeUcsVAWhTiScPiPNDZ8q1UzyQ%40mail.gmail.com.