2:29 p.m.
Hi all,
out of curiosity I tried to replicate a comparison between two python
libraries, namely pandas and py-polars. I also added gretl to the horse
race.
There two simple tasks to do:
1) Load a 360MB csv file with two columns and 26 million rows.
2) Sort by one of the columns.
The summary is as follows:
1) Py-polars takes about 2.5 seconds to load a 360MB large csv file as a
data frame. Pandas is about 5 times slower while Gretl needs 36 seconds
for this.
2) Sorting 26 million records takes py-polars about 4.6 seconds and
hence is just slightly faster than Pandas with 5.8 sec. -- ok, still
20% percent. Gretl needs about 14 seconds for the same task.
I am not too bothered with gretl's performance -- okay, reading a csv
may be faster but still: how often do you load such a big file?
The repo and readme can be accessed here:
https://github.com/atecon/gretl_pandas_pypolars
There is one thing which is weird though: at the end of the README.md
you see that I tried to store the loaded csv data set as a gdtb file.
But after an hour of computation I gave up... Maybe worth to look at.
Best,
Artur
2:35 p.m.
On Sat, 12 Dec 2020, Artur Tarassow wrote:
Hi all,
out of curiosity I tried to replicate a comparison between two python
libraries, namely pandas and py-polars. I also added gretl to the horse race.
There two simple tasks to do:
1) Load a 360MB csv file with two columns and 26 million rows.
2) Sort by one of the columns.
Here are some comments on Artur's experiment.
Loading speed: The CSV file in question has two columns, "author"
(strings) and "n" (integers). Since "author" is not recognized by
gretl as indicating observation markers the first column was being
treated as a string-valued variable, hence requiring a numeric
coding and a hash table. Since the author strings are actually
unique IDs (that is, observation markers) the hash table was huge,
and the work setting it up a huge waste of cycles. Things go quite a
lot better if you rename the first column as "obs".
Sorting a dataset: This was not optimized for a huge number of
observations. We were allocating more temporary storage than
strictly necessary and moreover, at some points, calling malloc()
per observation when it was possible to substitute a single call to
get a big chunk of memory. Neither of these points were much of an
issue with a normal-size dataset but they became a serious problem
in the huge case. That's now addressed in git.
Memory usage: gretl stores all numeric data, including the coding
for string-valued variables, as doubles. So when the CSV file was
read as-is, besides storing the strings we were also storing two
doubles per observation, instead of a single int ("n"). That's 16-4
= 12 extra bytes per observation, an additional 297 MB. Even once
the first column is changed to "obs" the data expand by 100 MB in
memory. If we truly want to handle data of this sort and magnitude,
we'd have to introduce more economical internal storage types for
data that don't really need 8 bytes per value. That would be a hefty
redesign.
Writing gdtb: Artur gave up on this as taking far too long. I
suspect the big problem here is zip compression. A gdtb file is a
zipfile containing numeric data in binary plus metadata in XML. I
wonder, Artur, are you building gretl using libgsf? On running the
configure script you'll see a line of output that says
Use libgsf for zip/unzip: <yes or no>
If you're not using libgsf you might find that using it helps.
However, gdtb is not designed for a dataset like this. I guess you'd
want an uncompressed pure-binary format for best read and write
speed and minimal memory consumption.
Allin
12:37 a.m.
On Sun, 13 Dec 2020, Allin Cottrell wrote:
I wonder, Artur, are you building gretl using libgsf?
Speaking of which: I was NEVER able to have ./configure set things up so
as to haf libgsf enabled, but I always though "oh, what the hell". Tonight
I decided to have a good look at it and I believe that in config.ac we
need two more lines, as in:
diff --git a/configure.ac b/configure.ac
index f6d061049..91303df14 100644
--- a/configure.ac
+++ b/configure.ac
@@ -271,6 +271,8 @@ AC_ARG_WITH(gsf,
if test "x${withval}" = "xno"
then
try_gsf=no
+else
+ try_gsf=yes
fi,
try_gsf=yes)
Am I wrong, Allin?
-------------------------------------------------------
Riccardo (Jack) Lucchetti
Dipartimento di Scienze Economiche e Sociali (DiSES)
Università Politecnica delle Marche
(formerly known as Università di Ancona)
r.lucchetti(a)univpm.it
http://www2.econ.univpm.it/servizi/hpp/lucchetti
-------------------------------------------------------
2:34 a.m.
On Mon, 14 Dec 2020, Riccardo (Jack) Lucchetti wrote:
On Sun, 13 Dec 2020, Allin Cottrell wrote:
> I wonder, Artur, are you building gretl using libgsf?
Speaking of which: I was NEVER able to have ./configure set things up so as
to haf libgsf enabled, but I always though "oh, what the hell". Tonight I
decided to have a good look at it and I believe that in config.ac we need two
more lines, as in:
diff --git a/configure.ac b/configure.ac
index f6d061049..91303df14 100644
--- a/configure.ac
+++ b/configure.ac
@@ -271,6 +271,8 @@ AC_ARG_WITH(gsf,
if test "x${withval}" = "xno"
then
try_gsf=no
+else
+ try_gsf=yes
fi,
try_gsf=yes)
Am I wrong, Allin?
You're right. The status at the moment is that you should get
try_gsf=yes if you leave it to automatic detection -- but,
paradoxically, not if you give --with-gsf. So that needs fixing.
In fact, is now fixed in git.
However, with libgsf-dev/devel installed it has worked OK for me to
date with no reference to gsf in my configure script, leaving it to
auto. That's why I hadn't noticed the problem.
Allin
9:38 a.m.
Am 13.12.20 um 15:35 schrieb Allin Cottrell:
On Sat, 12 Dec 2020, Artur Tarassow wrote:
> Hi all,
>
> out of curiosity I tried to replicate a comparison between two python
> libraries, namely pandas and py-polars. I also added gretl to the
> horse race.
>
> There two simple tasks to do:
> 1) Load a 360MB csv file with two columns and 26 million rows.
> 2) Sort by one of the columns.
Thanks for the thorough look at this and reply on this, Allin. I am
going to update the github summary page on this tomorrow or so.
Here are some comments on Artur's experiment.
Loading speed: The CSV file in question has two columns, "author"
(strings) and "n" (integers). Since "author" is not recognized by
gretl
as indicating observation markers the first column was being treated as
a string-valued variable, hence requiring a numeric coding and a hash
table. Since the author strings are actually unique IDs (that is,
observation markers) the hash table was huge, and the work setting it up
a huge waste of cycles. Things go quite a lot better if you rename the
first column as "obs".
Wow, this has reduced reading time from 34 to 12 seconds! Very useful to
know.
Sorting a dataset: This was not optimized for a huge number of
observations. We were allocating more temporary storage than strictly
necessary and moreover, at some points, calling malloc() per observation
when it was possible to substitute a single call to get a big chunk of
memory. Neither of these points were much of an issue with a normal-size
dataset but they became a serious problem in the huge case. That's now
addressed in git.
As I already wrote you privately: This change is a boost as sorting time
got reduced from 14 to 7.5 seconds. Thanks for this!
By the way, does this increased speed in sorting also affect the
aggregate() function?
Memory usage: gretl stores all numeric data, including the coding for
string-valued variables, as doubles. So when the CSV file was read
as-is, besides storing the strings we were also storing two doubles per
observation, instead of a single int ("n"). That's 16-4 = 12 extra bytes
per observation, an additional 297 MB. Even once the first column is
changed to "obs" the data expand by 100 MB in memory. If we truly want
to handle data of this sort and magnitude, we'd have to introduce more
economical internal storage types for data that don't really need 8
bytes per value. That would be a hefty redesign.
I think you mentioned this 'issue' a while ago on the mailing list when
I had to deal with some other large data set which consumed a lot of memory.
Writing gdtb: Artur gave up on this as taking far too long. I suspect
the big problem here is zip compression. A gdtb file is a zipfile
containing numeric data in binary plus metadata in XML. I wonder, Artur,
are you building gretl using libgsf? On running the configure script
you'll see a line of output that says
Use libgsf for zip/unzip: <yes or no>
If you're not using libgsf you might find that using it helps.
Yes, I already use libgsf as I have the appropriate dev-library installed.
However, gdtb is not designed for a dataset like this. I guess
you'd
want an uncompressed pure-binary format for best read and write speed
and minimal memory consumption.
I guess you're right but how can I store data as a pure-binary format
without any compression? I can't find anything on this in the help text.
By the way, I stored the csv file as a gdt file via "store
<FILENAME>.gdt". To my surprise, the gdt file is 843 MB large and hence
almost 2.5 times larger than the csv.
Also, trying to open the file consumes massive RAM here on my Ubunut
20.04 machine: RAM increases from 1GB before starting to read the file
to 15GB (and actually slightly more but the swap file already starts to
increase).
Thanks,
Artur
4:41 p.m.
On Mon, 14 Dec 2020, Artur Tarassow wrote:
[...]
Am 13.12.20 um 15:35 schrieb Allin Cottrell:
> Sorting a dataset: This was not optimized for a huge number of
> observations. We were allocating more temporary storage than strictly
> necessary and moreover, at some points, calling malloc() per observation
> when it was possible to substitute a single call to get a big chunk of
> memory. Neither of these points were much of an issue with a normal-size
> dataset but they became a serious problem in the huge case. That's now
> addressed in git.
As I already wrote you privately: This change is a boost as sorting time got
reduced from 14 to 7.5 seconds. Thanks for this!
By the way, does this increased speed in sorting also affect the
aggregate() function?
Right now it's specific to the case of sorting an entire dataset,
but it would be worth taking a look at the aggregate case too.
> gdtb is not designed for a dataset like this. I guess you'd
want
> an uncompressed pure-binary format for best read and write speed
> and minimal memory consumption.
I guess you're right but how can I store data as a pure-binary format without
any compression? I can't find anything on this in the help text.
Well, no. That's not a format that gretl has offered to date. But
see below.
By the way, I stored the csv file as a gdt file via "store
<FILENAME>.gdt".
To my surprise, the gdt file is 843 MB large and hence almost 2.5 times
larger than the csv.
That shouldn't be suprising if you've ever looked "inside" a gdt
file. The CSV file just contains the data. The structured XML file
contains per-observation tags. With a single data series (mostly
smallish integers), the tags occupy more bytes than the data.
Also, trying to open the file consumes massive RAM here on my
Ubunut 20.04 machine: RAM increases from 1GB before starting to
read the file to 15GB (and actually slightly more but the swap
file already starts to increase).
Libxml2 is having to allocate a ton of memory to parse the entire
document. XML is just not the way to go for this volume of data.
There's an experiment ("proof of concept") in git which might be of
interest. I've enabled reading and writing "pure binary" data files,
with ".gbin" suffix. Here are my initial test scripts.
Read the original data and write gbin:
<hansl>
set stopwatch
# users.csv has first column named "obs"
open users.csv
printf "load time %gs\n", $stopwatch
summary n --simple
set stopwatch
dataset sortby n
printf "sort time %gs\n", $stopwatch
summary n --simple
set stopwatch
store users.gbin
printf "gbin store time %gs\n", $stopwatch
</hansl>
The times I'm seeing are:
read csv: 9.95s
sort by n: 6.09s
write gbin: 1.40s
Then restart and read the gbin:
<hansl>
set stopwatch
open users.gbin
printf "gbin open time %gs\n", $stopwatch
summary n --simple
</hansl>
I see an open time of 1.76s.
As I said, it's at proof-of-concept stage. String-valued series are
not handled and only minimal metadata are conveyed (variable names,
observation markers if present, basic time-series info).
Allin
9 p.m.
Am 14.12.20 um 17:41 schrieb Allin Cottrell:
On Mon, 14 Dec 2020, Artur Tarassow wrote:
[...]
> Am 13.12.20 um 15:35 schrieb Allin Cottrell:
>
>> Sorting a dataset: This was not optimized for a huge number of
>> observations. We were allocating more temporary storage than
>> strictly necessary and moreover, at some points, calling malloc()
>> per observation when it was possible to substitute a single call to
>> get a big chunk of memory. Neither of these points were much of an
>> issue with a normal-size dataset but they became a serious problem
>> in the huge case. That's now addressed in git.
>
> As I already wrote you privately: This change is a boost as sorting
> time got reduced from 14 to 7.5 seconds. Thanks for this!
>
> By the way, does this increased speed in sorting also affect the
> aggregate() function?
Right now it's specific to the case of sorting an entire dataset, but
it would be worth taking a look at the aggregate case too.
Ok, thanks.
>> gdtb is not designed for a dataset like this. I guess
you'd want an
>> uncompressed pure-binary format for best read and write speed and
>> minimal memory consumption.
>
> I guess you're right but how can I store data as a pure-binary format
> without any compression? I can't find anything on this in the help text.
Well, no. That's not a format that gretl has offered to date. But see
below.
> By the way, I stored the csv file as a gdt file via "store
> <FILENAME>.gdt". To my surprise, the gdt file is 843 MB large and
> hence almost 2.5 times larger than the csv.
That shouldn't be suprising if you've ever looked "inside" a gdt file.
The CSV file just contains the data. The structured XML file contains
per-observation tags. With a single data series (mostly smallish
integers), the tags occupy more bytes than the data.
I see. I guess that's a case where a json format may be beneficial as it
should not have so many tags if I am right. But I am pretty sure that
switching from xml to json would involve complex changes under the hood.
Just one note: If in future the topic on large/ big data should become
(more) relevant for the Gretl project, the parquet (open-source) file
format may be of interest (https://parquet.apache.org/). This is a
widely used format nowadays in the big data context. Here is another
reference: https://en.wikipedia.org/wiki/Apache_Parquet
Ah, I just found out about the xmlreader API for Libxml2
(http://xmlsoft.org/xmlreader.html). Instead of loading the whole
document in memory and to expose it as a tree, xmlreader allows one to
"go through" the document stream which may be a more efficient way to
go. But still, this does not solve the basic issue of structured xml
files for large data as you said.
> Also, trying to open the file consumes massive RAM here on my
Ubunut
> 20.04 machine: RAM increases from 1GB before starting to read the
> file to 15GB (and actually slightly more but the swap file already
> starts to increase).
Libxml2 is having to allocate a ton of memory to parse the entire
document. XML is just not the way to go for this volume of data.
There's an experiment ("proof of concept") in git which might be of
interest. I've enabled reading and writing "pure binary" data files,
with ".gbin" suffix. Here are my initial test scripts.
Read the original data and write gbin:
<hansl>
set stopwatch
# users.csv has first column named "obs"
open users.csv
printf "load time %gs\n", $stopwatch
summary n --simple
set stopwatch
dataset sortby n
printf "sort time %gs\n", $stopwatch
summary n --simple
set stopwatch
store users.gbin
printf "gbin store time %gs\n", $stopwatch
</hansl>
The times I'm seeing are:
read csv: 9.95s
sort by n: 6.09s
write gbin: 1.40s
Then restart and read the gbin:
<hansl>
set stopwatch
open users.gbin
printf "gbin open time %gs\n", $stopwatch
summary n --simple
</hansl>
I see an open time of 1.76s.
As I said, it's at proof-of-concept stage. String-valued series are
not handled and only minimal metadata are conveyed (variable names,
observation markers if present, basic time-series info).
That's already great, and very helpful to see how well such binary
formats perform.
Thanks for the technical deep-dive Allin!
Artur
12:32 p.m.
Am 14.12.20 um 17:41 schrieb Allin Cottrell:
On Mon, 14 Dec 2020, Artur Tarassow wrote:
[...]
> Am 13.12.20 um 15:35 schrieb Allin Cottrell:
>
>> Sorting a dataset: This was not optimized for a huge number of
>> observations. We were allocating more temporary storage than strictly
>> necessary and moreover, at some points, calling malloc() per
>> observation when it was possible to substitute a single call to get a
>> big chunk of memory. Neither of these points were much of an issue
>> with a normal-size dataset but they became a serious problem in the
>> huge case. That's now addressed in git.
>
> As I already wrote you privately: This change is a boost as sorting
> time got reduced from 14 to 7.5 seconds. Thanks for this!
>
> By the way, does this increased speed in sorting also affect the
> aggregate() function?
Right now it's specific to the case of sorting an entire dataset, but it
would be worth taking a look at the aggregate case too.
Hi all,
I've finalized the little documentation of this little project including
Allin's response to the first version:
https://github.com/atecon/gretl_pandas_pypolars
Best,
Artur
1526
days inactive
1535
days old
7 comments
3 participants
participants (3)
-
Allin Cottrell -
Artur Tarassow -
Riccardo (Jack) Lucchetti