Reducing ActiveRecord memory consumption using Apache Arrow

Transcript

1. Reducing ActiveRecord memory consumption using Apache Arrow Kenta Murata Speee

   Inc. 2019-04-20 RubyKaigi 2019

2. Contents • Background • ActiveRecord and the pluck method •

   Table data structure in Apache Arrow • Experiment and results • Future development plans • Conclusion

3. About myself • name: mrkn • full_name: Kenta Murata •

   affiliation: Speee Inc. • job: Full-time CRuby committer • hobby: CS, Math, Camera
4. None

5. Background

6. I found Apache Arrow in 2016

7. Apache Arrow • Language-agnostic, open standard in-memory format for column-

   major data (i.e. data frames) • Started in Sep 2015, publicly announced in Feb 2016 • Defragment data access among different tools and languages • Bring together databases and data science communities to collaborate on shared computational technologies

8. Before and After • Each system has own internal memory

   format • 80% computation wasted on serialization & deserialization • Similar functionality implemented in multiple projects • All systems utilize the same memory format • No overhead for cross-system communication • Projects can share functionality Without Arrow (Now) With Arrow (Future) https://arrow.apache.org/

9. 2016

10. 2018 • I started to join to the development of

    Apache Arrow • Apache Arrow was ready to use from Ruby using red-arrow.gem at that time • It’s thanks to Kouhei Suto • The purpose is for two goals: 1. Making sparse tensor library for Ruby and letting it interoperable with other system such as SciPy stack on Python 2. Making data frame library for Ruby using Apache Arrow’s data structures and operations • I speculated that Apache Arrow may improve the performance of the application of ActiveRecord as I investigated the implementation of Apache Arrow C++ library

11. How does AR manage the result of query MYSQL_RES Result

    AR::Relation Extracted mysqld • fields : An array of the information of columns • rows : An array of the records in a result from DBMS AR::Result fields rows

12. Use Apache Arrow in AR::Result MYSQL_RES Result AR::Relation Extracted mysqld

    • fields : An array of the information of columns • columns : An array of the column arrays in a result from DBMS Arrow::Table AR::Result fields columns

13. Apache Arrow’s benefit • Apache Arrow uses column-major layout •

    It has an advantage on the cache efficiency and SIMD-instruction applicability for scanning columns to filter records • The memory consumption can be much efficient than row-major layout • Almost all operations are implemented in C++ so they are faster than things written in Ruby • It is much parallelizable than things written in Ruby because these operations can perform with releasing RubyVM GIL

14. Row-major vs Column-major id name height weight 1 A 172

    67 2 B 185 78 3 C 166 60 4 D 158 62 5 E 190 75 6 F 178 69 1 A 172 67 2 B 185 78 3 C 166 60 4 D 158 62 5 E 190 75 6 F 178 69 1 2 3 4 5 6 A B C D E F 172 185 166 158 190 178 67 78 60 62 75 69 Row-major layout Column-major layout

15. Selecting height ≥ 170 id name height weight 1 A

    172 67 2 B 185 78 3 C 166 60 4 D 158 62 5 E 190 75 6 F 178 69 1 A 172 67 2 B 185 78 3 C 166 60 4 D 158 62 5 E 190 75 6 F 178 69 1 2 3 4 5 6 A B C D E F 172 185 166 158 190 178 67 78 60 62 75 69 Row-major layout Column-major layout ↑Need to scan the height column Low efficiency High efficiency

16. Apache Arrow’s benefit • Apache Arrow uses column-major layout •

    It has an advantage on the cache efficiency and SIMD-instruction applicability for scanning columns to filter records • The memory consumption can be much efficient than row-major layout • Almost all operations are implemented in C++ so they are faster than things written in Ruby • It is much parallelizable than things written in Ruby because these operations can perform with releasing RubyVM GIL

17. I performed a PoC experiment • Making rough implementation of

    a new subclass of AR::Result, called ArrowResult class, that uses Arrow::Table • Making rough implementation of the new connection adapter that generates ArrowResult object as the result of a query • Comparing execution time and memory consumption of pluck method between the original and the Arrow versions

18. Why pluck? • pluck method is simple • It just

    converts AR::Result to Array of Array • It is easy to understand • It is easy to appeal the superiority of Apache Arrow

19. The result of PoC • I already reported at Rails

    Developer Meetup on 22 Mar 2019: • The execution time was not increased • The memory consumption was EXTREMELY reduced!! • It’s amazing!!! I didn’t expected such the result. But …

20. The PoC Code was Broken!!!

21. The reported result is wrong

22. I fixed the code and perform the experiments again for

    today

23. The new right result • The execution time was much

    reduced • The memory consumption was increased • And the additional results: • pluck method got highly parallelizable than before

24. ActiveRecord and the pluck method

25. ActiveRecord classes and modules AR::ConnectionAdapter Mysql2::Client, Pg::Connection, etc. AR::Relation AR::Base

    creates query AR::Result query DBMS Mysql2::Result,
 PG::Result, etc. query AR::Calculations (module) include pluck is here! result

26. What I tweaked for the experiments AR::ConnectionAdapter Mysql2::Client, Pg::Connection, etc.

    AR::Relation AR::Base creates query AR::Result query DBMS Mysql2::Result,
 PG::Result, etc. query AR::Calculations (module) include result

27. Breaking down pluck method columns a SQL string a Mysql2::Result

    an AR::Result an Array of records build SQL query fetch cast values args return value these stages occupy almost execution time
 and almost consumed memory

28. Focusing 3 stages query fetch cast_values a SQL string a

    Mysql2::Result an AR::Result an Array of records Mysql2::Client#query Mysql2::Result#to_a AR::Result#cast_values running without GIL (waiting I/O, etc.) => other threads can run simultaneously

29. Table data structure in Apache Arrow

30. Arrow::Table • A Table object consists of a Schema object

    and a vector of Column objects • A Schema consists of a vector of Field, and a Field consists of its data type and its name • A Column consists of a Field object and a ChunkedArray object • The field of the column at a certain index is same as the field at the same index in the schema • Each column has the same length Schema Table vector<Column> ↑Field := (data_type, name) f1 f2 f3 f4 f5 f1 f2 f3 f4 f5 ↑Column := (field, chunked_array) ChunkedArray ChunkedArray ChunkedArray ChunkedArray ChunkedArray same field

31. ChunkedArray • ChunkedArray consists of multiple arrays with the same

    value type • Each array has a contiguous buffer for storing values • Each array can have different length • It allows us to treat multiple arrays as a single array • It allows us to build a table by divide and conquer method in parallel ChunkedArray a1 a2 a3 a4 a5 type type type type type Buffer Buffer Buffer Buffer Buffer Arrays

32. Experiment and results

33. The Experiment Setting • Comparing the execution time and the

    memory consumption between the two versions of pluck method: original vs arrow • In the arrow version, the result of the query is divided multiple batches and they are processed in parallel •

34. Table definition and query statement CREATE TABLE IF NOT EXISTS

    mysql2_test ( null_test VARCHAR(10), bit_test BIT, tiny_int_test TINYINT, small_int_test SMALLINT, medium_int_test MEDIUMINT, int_test INT, big_int_test BIGINT, float_test FLOAT(10,3), float_zero_test FLOAT(10,3), double_test DOUBLE(10,3), decimal_test DECIMAL(10,3), decimal_zero_test DECIMAL(10,3), date_test DATE, date_time_test DATETIME, timestamp_test TIMESTAMP, time_test TIME, year_test YEAR(4), char_test CHAR(10), varchar_test VARCHAR(10), binary_test BINARY(10), varbinary_test VARBINARY(10), tiny_blob_test TINYBLOB, tiny_text_test TINYTEXT, blob_test BLOB, text_test TEXT, medium_blob_test MEDIUMBLOB, medium_text_test MEDIUMTEXT, long_blob_test LONGBLOB, long_text_test LONGTEXT, enum_test ENUM('val1', 'val2'), set_test SET('val1', 'val2') ) DEFAULT CHARSET=utf8 Mysql2Test.limit(n).pluck( :int_test, :double_test, :varchar_test, :text_test, )

35. Changes of 3 stages query fetch cast_values SQL Mysql::Result AR::Result

    Array SQL Mysql::Result ArrowResult (Arrow::Table) Array Mysql2::Client#query Mysql2::Client#query Mysql2::Result#to_a Mysql2::Result#to_arrow AR::Result#cast_values Arrow::Table#raw_records Original version Arrow version

36. Mysql2::Result#to_arrow In [1]: client = Mysql2::Client.new(host: 'localhost', username: 'root', database:

    'test') result = client.query('select * from ruby_committers', as: :array) records = result.to_a records[0..2] Out[1]: [[1, "matz", "3db12e8b236ac8f88db8eb4690d10e4a3b8dbcd4", 1998-01-16 21:13:05 +0900], [2, "aamine", "6f4751f5f63d177534f34e0c278967883e11c035", 1999-12-18 00:00:13 +0900], [3, "shugo", "6d36661c5eb87831cc60fa416470c8459d2a16c0", 1999-12-20 12:27:48 +0900]] In [2]: result = client.query('select * from ruby_committers', as: :array) table = result.to_arrow Out[2]: #<Arrow::Table:0x7fb850b2beb0 ptr=0x7fb8512b9f10> id svn first_commit 0 1 matz 3db12e8b236ac8f88db8eb4690d10e4a3b8dbcd4 1 2 aamine 6f4751f5f63d177534f34e0c278967883e11c035 2 3 shugo 6d36661c5eb87831cc60fa416470c8459d2a16c0 : : : : The result of Mysql2::Result#to_a is an array of records. The result of Mysql2::Result#to_arrow is an Arrow::Table.

37. Arrow::Table#raw_records In [3]: records2 = table.raw_records records2[0..2] Out[3]: [[1, "matz",

    "3db12e8b236ac8f88db8eb4690d10e4a3b8dbcd4", 1998-01-16 21:13:05 +0900], [2, "aamine", "6f4751f5f63d177534f34e0c278967883e11c035", 1999-12-18 00:00:13 +0900], [3, "shugo", "6d36661c5eb87831cc60fa416470c8459d2a16c0", 1999-12-20 12:27:48 +0900]] In [4]: records == records2 Out[4]: true The result of Arrow::Table#raw_records is an array of records. This equals to the results of Mysql2::Result#to_a.

38. Execution time Execution time [sec] Total number of records fetched

    from DB

39. Ratio of Arrow / Original Actual batch size for 0

    Total records Batch size 1000 42 2000 84 3000 125 5000 209 10000 417 20000 834 30000 1250 50000 2084

40. Maximum RSS Maximum RSS [MB] Total number of records fetched

    from DB

41. Ratio of Arrow / Original Actual batch size for 0

    Total records Batch size 1000 42 2000 84 3000 125 5000 209 10000 417 20000 834 30000 1250 50000 2084

42. Best batch sizes for total records ←Better Worse→ ←Better Worse→

    Actual batch size for 0 Total records Batch size 1000 42 2000 84 3000 125 5000 209 10000 417 20000 834 30000 1250 50000 2084 ** This may depends on machine spec **

43. Breaking down the execution time query fetch cast_values SQL Mysql::Result

    AR::Result Array SQL Mysql::Result Arrow::Table Array Mysql2::Client#query Mysql2::Client#query Mysql2::Result#to_a Mysql2::Result#to_arrow AR::Result#cast_values Arrow::Table#raw_records Original version Arrow version

44. Breaking down the execution time Data transformation stage (query →

    fetch → cast_values) Execution time [msec]

45. GIL handling on each stage query fetch cast_values SQL Mysql::Result

    AR::Result Array SQL Mysql::Result Arrow::Table Array Mysql2::Client#query Mysql2::Client#query Mysql2::Result#to_a Mysql2::Result#to_arrow AR::Result#cast_values Arrow::Table#raw_records Original version Arrow version

46. GIL handling on each stage query fetch cast_values SQL Mysql::Result

    AR::Result Array SQL Mysql::Result Arrow::Table Array Mysql2::Client#query Mysql2::Client#query Mysql2::Result#to_a Mysql2::Result#to_arrow AR::Result#cast_values Arrow::Table#raw_records Original version Arrow version with GIL without GIL without GIL without GIL with GIL with GIL

47. Using Arrow can release GIL for longer time • We

    can run other threads preemptively during the stages from the query to the fetch if we use Arrow version • We can speculate if we perform this benchmark in multiple threads, Arrow version may be faster than the original version • Let’s try with 10 threads

48. Multi-threaded execution time

49. Multi-threaded execution time

50. Breaking down the memory consumption • Use massif tool of

    valgrind to investigate how heap is used • Visualize it by massif-visualizer tool • Compare the memory consumption between arrow and original versions

51. Massif result of the arrow version 64.1MB 41.5MB The size

    of the resultant Arrow::Table The size of the builders of column arrays Building Arrow::Table shrinks memory usage The size of the working memory of MySQL client 43.4MB ≒ 40MB

52. Massif result of the original version ≒ 40MB 43.4MB

53. Memory consumption summary Memory Consumption Original Arrow MySQL Client 43MB

    43MB Array Builders 0 64MB Arrow::Table 0 42MB Ruby objects 40MB 40MB These can be vanished if MySQL returns Array::Table.

54. Summary of Experiments • Arrow version has advantages about both

    speed and parallelism on the case for processing large amount of data obtained from DB • These advantages are important especially for batch jobs running on Sidekiq workers • The increase of memory consumption is at most x2 • The memory consumption can be reduced if MySQL returns Arrow::Table object instead of MYSQL_RES object

55. We should let MySQL support Apahe Arrow’s data format

56. Future development plan

57. Apache Arrow • Language-agnostic, open standard in-memory format for column-

    major data (i.e. data frames) • Started in Sep 2015, publicly announced in Feb 2016 • Defragment data access among different tools and languages • Bring together databases and data science communities to collaborate on shared computational technologies

58. We should let MySQL support Apahe Arrow’s data format

59. We should let any DBMSs support Apahe Arrow’s data format

60. ARROW-3193

61. ARROW-1106

62. ARROW-3102

63. ARROW-3155

64. We should let any DBMSs support Apahe Arrow’s data format

65. Welcome to
 Red Data Tools project

66. The other perspective

67. Making Red Arrow a solid data frame library

68. Using data frame in ActiveRecord • Replacing AR::Result with Arrow::Table

    and rewriting the operation of AR::Relation using Arrow’s operation will improve the performance of all operations of Active Record • e.g. we can do the following things without issuing query to DBMS: • We can pick up certain records in an AR::Relation using SQL statement • We can join the records in multiple AR::Relation objects • etc.

69. Making Red Arrow as a solid data frame library •

    Making Arrow::Table object like pandas.DataFrame • Making Arrow::Column object like pandas.Series • etc. • We’ve already started this work.

70. Welcome to
 Red Data Tools project

71. Conclusions

72. Conclusion • We performed experiments to replace the internal data

    of AR::Result with Arrow::Table object • This reduced the execution time of pluck method • This improved the parallelizability of pluck method • The memory consumption was increased x2-x3 • But it can be reduced by letting MySQL support to return results in Arrow format