4 ms·

Hm. I'm fully aware that I'm currently turning into a bearded DBA. And I may be just misreading the article and I probably don't understand the article. But, I

by tetha 6y ago

Hm. I'm fully aware that I'm currently turning into a bearded DBA. And I may be just misreading the article and I probably don't understand the article. But, I started being somewhat confused by something: > Fortunately, I had access to a large-memory (24 T) SGI system with 512-core Intel Xeon (2.5GHz) CPUs. All the IO is memory (/dev/shm) bound ie. the data is read from and written to /dev/shm. > The total data size is 329GB. At first glance, that's an awful lot of hardware for a ... decently sized but not awfully large dataset. We're dealing with datasets that size at 32G or 64G of RAM, just a wee bit less. The article presents a lot more AWK knowledge than I have. I'm impressed by that. I acknowledge that. But I'd probably put all of that into a postgres instance, compute indexes and rely on automated query optimization and parallelization from there. Maybe tinker with pgstorm to offload huge index operations to a GPU. A lot of the shown scripting would be done by postgres, the parallelization is done automatically based on indexes, while eliminating the string serializations. I do agree with the underlying sentiment of "We don't need hadoop". I'm impressed that AWK goes so far. I'd still recommend postgres in this case as a first solution. Maybe I just work with too many silly people at the moment.

VHRanger 6y ago
If you're dealing with such a static single-machine dataset, why not go for SQLite instead of PostGres? There'd be much less setup overhead
- mongol 6y ago
  I was thinking the same thing first, but does SQLite parallelize work it does?
- twoodfin 6y ago
  How good is SQLite’s parallelism these days?
  - doteka 6y ago
    It’s alright, concurrent reads should not be a problem at least. Most drivers implement some features to make it easier.
- tetha 6y ago
  Sqlite is single-writer for a single database. I mean. Of course, VHRanger, only your team will need to write to the database, and your team will make sure that only one person on your team will write to it. Eh. I've been there too many times. Oh, but yes, your team will also figure out the fallout if things to wrong. Ah.. Ok. Maybe those are enterprise concerns: Sqlite doesn't scale regarding multiple users reading and writing. Of course it's a read only dataset, but do you know the bouquet of views and derived tables data scientists create around a read-only dataset? Hah. Oh and of course these are not critical, but if they get lost, shit hits the fan because it takes multiple weeks to rebuild them. I've been in that swamp enough times to just install postgres and stop caring. Takes me 2 more hours now, but avoids weeks of discussions in the future.
  - deleted 6y ago
    [deleted]
  - Volt 6y ago
    Remember that we're comparing this to awk…
- Scarbutt 6y ago
  There's no overhead. apt-get install postgres create user create database
throwaway_pdp09 6y ago
That is a shagload of hardware. I had to work in MSSQL with ~100GB database and we only had, I think, 12 cores in those servers (or maybe it was just 6 + SMT). Avoiding IO is the key, so having enough mem to cache the working set is essential. Those are massive, massive machines.
- hobs 6y ago
  lmao I wish, I was running several TB databases on servers with less than 128GB of ram (thankfully we werent processing the entire set) but that amount of ram is eye popping for the dataset, many data warehouse implementations I am familiar with on MSSQL have 10TB datasets with significantly less ram than this :)
  - D2187645 6y ago
    On the opposite spectrum, I have have over estimated our hardware requirements with 3x 128GB machines with 32 cores each, and the uncompressed dataset is 200 mb on disk.
MuffinFlavored 6y ago
> rely on automated query optimization and parallelization from there. I know you said "automated parallelization" but... in newer versions of Postgres, what does it take to trigger some of the automatic query parallelization?
- tomnipotent 6y ago
  > what does it take to trigger some of the automatic query parallelization Table scans, index scans (b-tree & bitmap only I believe), joins, and aggregations. There may be some limitations with joins, such as hash joins duplicating hashes across processes or merges requiring separate sorts.
  - Frost1x 6y ago
    Does this work outside of a shared memory environments? I haven't had to fuss with postgres for several years but last time I used it, this was non-trivial.
    - tomnipotent 6y ago
      Postgres uses separate processes under the hood and thus shared memory to combine results.
gjulianm 6y ago
Yeah, I work with awk quite a lot and I find weird that they needed so many resources. Although I agree that for this problem Postgres looks like a better option, I wouldn't discard awk or other Unix tools. It is way faster than people think it is, easy to use and solves pretty well quite a lot of use cases, such as pre-processing records before database ingestion, aggregations, some simple queries on temporary data...
- tuatoru 6y ago
  Agreed. If you only have 64 MB of RAM to process your 329 GB, AWK's your tool. As you say, it's faster than many people think.
- ketanmaheshwari 6y ago
  OP here -- thank you for your comment. Just wanted to say that the resources were not needed. I simply had access to them and were not being used much at the time so thought why not try them.
timClicks 6y ago
One thought ... the author talks a lot about working in an "HPC environment". Most HPC sites don't allow services such as a database to be run.
flukus 6y ago
Some of awks stengths are iterative development, pattern matching and integration with other tools. A databases strengths are data consistency, concurrency (multiple readers/writers), transactions, efficiency. So the problem itself is playing to awks strengths and doesn't require the strengths of a database. With a database you'd usually have much more analysis upfront, work out the appropriate data types, work out what is nullable, work out the exceptions to the rules, work out your indexes, etc which isn't great for this type of quick and dirty problem. It also looks like most scripts are culling the dataset on input instead of at processing time which is more efficient. I don't think it's really displayed here because everything is run through swift, but it can also compose much better with other tools, a database frequently won't have cli tools that integrate well (not sure about postgres). There are 322 source files being globbed for instance, move that to a make file and you can automatically re-run without wasting time on data already processed. If it was in a database you'd have to track source files and manage changes somehow. > At first glance, that's an awful lot of hardware for a ... decently sized but not awfully large dataset. We're dealing with datasets that size at 32G or 64G of RAM, just a wee bit less. Note that it doesn't require that much memory, it's just using it to boost performance because it's available, this could have been developed on a dual core 4GB laptop outputting to spinning rust and all that would change is the directory it's output to (and running time), but the same machine may choke on database queries over that much data.
n_t 6y ago
I think the first line itself (High Performance Computing) reveales that this is a special purpose computer (read - real supercomputer). 512 cores and 24TiB of memory further confirm that. However, this is just a small sized supercomputer (unless that's the limit of resources a user can reserve). I don't know the constraints on this particular SGI machine, but many regular apps cannot be run or rendered useless in HPC domain even when using Linux. @ketanmaheshwari, which supercomputer is this?
- ketanmaheshwari 6y ago
  Thank you for your comment. This is an SGI UV300 system similar to this one: https://www.hpcwire.com/2016/05/11/tgac-installs-largest-sgi-uv-300-supercomputer-life-sciences/ https://www.hpcwire.com/2016/05/11/tgac-installs-largest-sgi... I believe databases like postgres etc. would probably run on this system. I did not choose that route because I wanted to see how far I can go with Awk.
afiodorov 6y ago
Alternatively one can load the data into Google BigQuery and start running queries without indexing the data. It's not free though, however can save a lot of time. BigQuery supports import of JSON and CSV files from Google Cloud Storage and it can even infer the schema.
ketanmaheshwari 6y ago
Thank you for your comment. I hear you when you say postgres would probably be faster. However, I imagine there would be more work if I chose postgres and the whole development and testing paradigm would change. First, I would need to figure the right schema and populate the database. Second, it would need some creative SQL acrobatics that I would probably not be comfortable with. Third, it would probably be hard to perform quick tests at a small scale that I can perform easily with text files. Fourth, the solution would probably be hard to port elsewhere where postgres is not available. Most Unix systems have awk available. Fifth, programming the postgres db in a higher level language would require connector api libs which would be additional effort. Note that this is not a production work -- it started simply as a hack to see how far I can go without getting into serious rabbitholes and giving up. Surprisingly, with Awk I went really far and never fell into any rabbithole so to speak.

by tetha 6y ago

VHRanger 6y ago

If you're dealing with such a static single-machine dataset, why not go for SQLite instead of PostGres? There'd be much less setup overhead

mongol 6y ago
I was thinking the same thing first, but does SQLite parallelize work it does?
twoodfin 6y ago
How good is SQLite’s parallelism these days?
- doteka 6y ago
  It’s alright, concurrent reads should not be a problem at least. Most drivers implement some features to make it easier.
tetha 6y ago
Sqlite is single-writer for a single database. I mean. Of course, VHRanger, only your team will need to write to the database, and your team will make sure that only one person on your team will write to it. Eh. I've been there too many times. Oh, but yes, your team will also figure out the fallout if things to wrong. Ah.. Ok. Maybe those are enterprise concerns: Sqlite doesn't scale regarding multiple users reading and writing. Of course it's a read only dataset, but do you know the bouquet of views and derived tables data scientists create around a read-only dataset? Hah. Oh and of course these are not critical, but if they get lost, shit hits the fan because it takes multiple weeks to rebuild them. I've been in that swamp enough times to just install postgres and stop caring. Takes me 2 more hours now, but avoids weeks of discussions in the future.
- deleted 6y ago
  [deleted]
- Volt 6y ago
  Remember that we're comparing this to awk…
Scarbutt 6y ago
There's no overhead. apt-get install postgres create user create database

throwaway_pdp09 6y ago

That is a shagload of hardware. I had to work in MSSQL with ~100GB database and we only had, I think, 12 cores in those servers (or maybe it was just 6 + SMT). Avoiding IO is the key, so having enough mem to cache the working set is essential. Those are massive, massive machines.

hobs 6y ago
lmao I wish, I was running several TB databases on servers with less than 128GB of ram (thankfully we werent processing the entire set) but that amount of ram is eye popping for the dataset, many data warehouse implementations I am familiar with on MSSQL have 10TB datasets with significantly less ram than this :)
- D2187645 6y ago
  On the opposite spectrum, I have have over estimated our hardware requirements with 3x 128GB machines with 32 cores each, and the uncompressed dataset is 200 mb on disk.

MuffinFlavored 6y ago

> rely on automated query optimization and parallelization from there. I know you said "automated parallelization" but... in newer versions of Postgres, what does it take to trigger some of the automatic query parallelization?

tomnipotent 6y ago
> what does it take to trigger some of the automatic query parallelization Table scans, index scans (b-tree & bitmap only I believe), joins, and aggregations. There may be some limitations with joins, such as hash joins duplicating hashes across processes or merges requiring separate sorts.
- Frost1x 6y ago
  Does this work outside of a shared memory environments? I haven't had to fuss with postgres for several years but last time I used it, this was non-trivial.
  - tomnipotent 6y ago
    Postgres uses separate processes under the hood and thus shared memory to combine results.

gjulianm 6y ago

Yeah, I work with awk quite a lot and I find weird that they needed so many resources. Although I agree that for this problem Postgres looks like a better option, I wouldn't discard awk or other Unix tools. It is way faster than people think it is, easy to use and solves pretty well quite a lot of use cases, such as pre-processing records before database ingestion, aggregations, some simple queries on temporary data...

tuatoru 6y ago
Agreed. If you only have 64 MB of RAM to process your 329 GB, AWK's your tool. As you say, it's faster than many people think.
ketanmaheshwari 6y ago
OP here -- thank you for your comment. Just wanted to say that the resources were not needed. I simply had access to them and were not being used much at the time so thought why not try them.

timClicks 6y ago

One thought ... the author talks a lot about working in an "HPC environment". Most HPC sites don't allow services such as a database to be run.

flukus 6y ago

Some of awks stengths are iterative development, pattern matching and integration with other tools. A databases strengths are data consistency, concurrency (multiple readers/writers), transactions, efficiency. So the problem itself is playing to awks strengths and doesn't require the strengths of a database. With a database you'd usually have much more analysis upfront, work out the appropriate data types, work out what is nullable, work out the exceptions to the rules, work out your indexes, etc which isn't great for this type of quick and dirty problem. It also looks like most scripts are culling the dataset on input instead of at processing time which is more efficient. I don't think it's really displayed here because everything is run through swift, but it can also compose much better with other tools, a database frequently won't have cli tools that integrate well (not sure about postgres). There are 322 source files being globbed for instance, move that to a make file and you can automatically re-run without wasting time on data already processed. If it was in a database you'd have to track source files and manage changes somehow. > At first glance, that's an awful lot of hardware for a ... decently sized but not awfully large dataset. We're dealing with datasets that size at 32G or 64G of RAM, just a wee bit less. Note that it doesn't require that much memory, it's just using it to boost performance because it's available, this could have been developed on a dual core 4GB laptop outputting to spinning rust and all that would change is the directory it's output to (and running time), but the same machine may choke on database queries over that much data.

n_t 6y ago

I think the first line itself (High Performance Computing) reveales that this is a special purpose computer (read - real supercomputer). 512 cores and 24TiB of memory further confirm that. However, this is just a small sized supercomputer (unless that's the limit of resources a user can reserve). I don't know the constraints on this particular SGI machine, but many regular apps cannot be run or rendered useless in HPC domain even when using Linux. @ketanmaheshwari, which supercomputer is this?

ketanmaheshwari 6y ago
Thank you for your comment. This is an SGI UV300 system similar to this one: https://www.hpcwire.com/2016/05/11/tgac-installs-largest-sgi-uv-300-supercomputer-life-sciences/ https://www.hpcwire.com/2016/05/11/tgac-installs-largest-sgi... I believe databases like postgres etc. would probably run on this system. I did not choose that route because I wanted to see how far I can go with Awk.

afiodorov 6y ago

Alternatively one can load the data into Google BigQuery and start running queries without indexing the data. It's not free though, however can save a lot of time. BigQuery supports import of JSON and CSV files from Google Cloud Storage and it can even infer the schema.

ketanmaheshwari 6y ago

Thank you for your comment. I hear you when you say postgres would probably be faster. However, I imagine there would be more work if I chose postgres and the whole development and testing paradigm would change. First, I would need to figure the right schema and populate the database. Second, it would need some creative SQL acrobatics that I would probably not be comfortable with. Third, it would probably be hard to perform quick tests at a small scale that I can perform easily with text files. Fourth, the solution would probably be hard to port elsewhere where postgres is not available. Most Unix systems have awk available. Fifth, programming the postgres db in a higher level language would require connector api libs which would be additional effort. Note that this is not a production work -- it started simply as a hack to see how far I can go without getting into serious rabbitholes and giving up. Surprisingly, with Awk I went really far and never fell into any rabbithole so to speak.