# Why is Ruby+MySQL memory usage way better than C, Perl, Python? **URL:** \ **Category:** Ruby\ **Created:** [February 8, 2010, 10:28pm UTC](https://www.ruby-forum.com/t/why-is-ruby-mysql-memory-usage-way-better-than-c-perl-python/183140 "2010-02-08T22:28:17Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![weliwarmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/a587f6/32.png) [@weliwarmer](https://www.ruby-forum.com/u/weliwarmer)\ **Post date:** [February 8, 2010, 10:28pm UTC](https://www.ruby-forum.com/t/why-is-ruby-mysql-memory-usage-way-better-than-c-perl-python/183140/1 "2010-02-08T22:28:17Z")
I’ve got a table that consists of about 2.6 millions rows, each row containing 5 columns (a short string, 3 integers, and a decimal fraction). A program needs to read the whole table. At first, programming without brain fully engaged, I just grabbed it in Perl using the DBI function selectall\_arrayref, and then looped through the results. That uses about 1.3 gig of memory. Perl data structures are not efficient when it comes to memory. This approach is reading the whole table (which is a mere 130 MB according to MySQL) into a Perl data structure (array of rows, each row an array of columns), which I then scan to build my data structures. Recoding to not be so brain dead, instead using prepare and execute, cuts the memory usage to about 270 MB. That makes sense, as the table is now being loaded into some data structure maintained by the C library that implements the Perl DBI driver for mysql. Out of curiosity, I decided to try this in other languages. Python is the same as Perl. 270 MB when I execute the store\_result() function to grab the data from the database. Same in C. 270 MB used when I grab the results with mysql\_store\_result(). This all makes sense, assuming the Perl and Python database drivers are using the C mysql library. So then I tried Ruby: require “mysql” my = Mysql.new(…) st = my.prepare(“SELECT \* from my\_table”) st.execute while row = st.fetch do … end That only uses about 130 MB of memory!? The above was on a 64-bit machine. On a 32-bit machine, the numbers are 185 MB for C, Perl, and Python, and 103 MB for Ruby. How is Ruby able to do so much better on memory usage? I did not test speed, but I think the Ruby version also ran a little faster than the others. ---
**Author:** ![weliwarmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/a587f6/32.png) [@weliwarmer](https://www.ruby-forum.com/u/weliwarmer)\ **Post date:** [February 8, 2010, 10:48pm UTC](https://www.ruby-forum.com/t/why-is-ruby-mysql-memory-usage-way-better-than-c-perl-python/183140/2 "2010-02-08T22:48:47Z")
On 02/08/2010 10:18 PM, Tim S. wrote: > table (which is a mere 130 MB according to MySQL) into a Perl data > grab the data from the database. > st = my.prepare(“SELECT \* from my\_table”) > How is Ruby able to do so much better on memory usage? > > I did not test speed, but I think the Ruby version also ran a little > faster than the others. It appears that you employ two fundamentally different approaches: the description of your Perl and Python versions sounds as if you load the _whole table_ in one data structure and only then iterate or process it. The Ruby code fetches a single row at a time (or at most a few rows if there is caching going on behind the scenes). Well, if my assessment is correct, you have your reason. 🙂 Kind regards robert ---
**Author:** ![weliwarmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/a587f6/32.png) [@weliwarmer](https://www.ruby-forum.com/u/weliwarmer)\ **Post date:** [February 9, 2010, 12:30am UTC](https://www.ruby-forum.com/t/why-is-ruby-mysql-memory-usage-way-better-than-c-perl-python/183140/3 "2010-02-09T00:30:39Z")
In article [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid), Robert K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > That only uses about 130 MB of memory!? > > description of your Perl and Python versions sounds as if you load the > > _whole table_ in one data structure and only then iterate or process it. > > The Ruby code fetches a single row at a time (or at most a few rows if > > there is caching going on behind the scenes). Well, if my assessment is > > correct, you have your reason. 🙂 Generally, low level mysql libraries provide two ways to get results from the server after a query is executed. In the C library, these are called mysql\_store\_results and mysql\_use\_results. The former reads the entire results set into a data structure. The program then typically calls a fetch\_row function that iterates over the rows in that results set. The latter (use\_results) is also used with a row iteration function, but only fetches the rows from the server as the program calls the row iterator. The execute function in the Ruby mysql library appears to use the store\_results method. It is during the execution of execute, for instance, that the memory usage rises from something insignificant to the final 130 MB. If it were using the use\_results method, memory usage would not noticeably rise while reading the results. Anyway, in all four (Ruby, Perl, Python, C) I am using the store\_results form, so as to read the whole table into memory.