5 ms·

Any support for big data? I tried to ingest HaveIBeenPwned password hashes in PostgreSQL 11 and after that Postgres become unusable, not even a simple query cou

by lcnmrn 7y ago

Any support for big data? I tried to ingest HaveIBeenPwned password hashes in PostgreSQL 11 and after that Postgres become unusable, not even a simple query could run.

sterwill 7y ago
What was your schema? What did you index? I don't have a copy of the HiBP archive at hand, but the web site says it contains 551 million entries. If we assume the 11 GB archive expands to 30 GB, that's certainly not a small table, but it's not what I'd call "big data".
- deleted 7y ago
  [deleted]
- lcnmrn 7y ago
  'hash' column (varchar). Index created in advance slowed down the import so much it wasn't usable. I couldn't create an index after the import, too slow. I just want to find if one or multiple hashes are present in their data set. I used Clickhouse previously with large data like this and worked much better. Obliviously, I compare oranges with apples, but PostgreSQL could support a columnar data engine or some kind of index?
  - sterwill 7y ago
    If you want an index on a column (and you probably do if you're going to query over 500 million rows) you have to create it at some point. Creating it after will be more efficient in your case. So what do you mean "too slow?" How long did it take? I don't have any experience with Clickhouse, and not much with columnar databases in general, but if we're talking about a simple table with one index over one text column, I'm not sure whether it makes any difference if you store the tuples row-wise or column-wise on disk. It's an index that covers 100% of the data either way. I don't know anything about your server but it sounds like PostgreSQL just wasn't able to get much IO throughput from your storage system. Things like storage hardware, filesystem type, and kernel parameters are the big factors here.
  - anarazel 7y ago
    > I couldn't create an index after the import, too slow. What made that "too slow"? I just tested it, and it's not too bad. This is on my development environment (~3yo laptop, reasonably powerful), with all kinds of compile-time debugging enabled - but with compiler optimizations turned on. CREATE TABLE hashes(hash text, count int); BEGIN; TRUNCATE hashes; \copy hashes FROM program '7z e -so ~/tmp/pwned-passwords-sha1-ordered-by-count-v4.7z pwned-passwords-sha1-ordered-by-count-v4.txt' WITH (DELIMITER ':', FREEZE); Time: 1245024.360 ms (20:45.024) COMMIT; The bottleneck is 7z decompressing: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 24403 andres 20 0 31480 22292 4544 R 73.8 0.1 14:54.09 7z 23701 andres 20 0 4380008 11488 9024 S 27.9 0.0 5:42.04 postgres And with a bit config the PG side could be made consdierably faster. Creating an index: CREATE INDEX ON hashes(hash COLLATE "C"); Time: 709595.370 ms (11:49.595) And yes, without the COLLATE, and FREEZE above, it'd have taken a bit longer. But not that much. This is all while I also was working on the same loptop, including recompiling etc. EDIT: Formatting #2
    - jeltz 7y ago
      Was the index build with or without parallelism?
      - anarazel 7y ago
        With parallelism, default settings (i.e. 2 workers).
    - lcnmrn 7y ago
      How long does it take to search for a hash? I'm interested in both WHERE cases: hash = 'hash1' and hash in ('hash1', 'hash2').
      - anarazel 7y ago
        A few ms, with cold cache (both OS and postgres). The IN() roughly scales linearly with the number of elements, although it's often a bit better, as the upper tree levels are all in cache after the first few IN() element lookups.
coder543 7y ago
were you on an NVMe SSD? how much RAM did you have? how many CPU cores? The hardware is important. If you tried to import that data on a $5/mo DO droplet, I wouldn't be surprised by having issues.
- lcnmrn 7y ago
  Two cores CPU with hyperthreading. 16 GB of RAM. But data isn't stored in RAM, right?
  - coder543 7y ago
    Not having enough RAM can significantly degrade database performance. Also, gotta wonder if you have good indices on the data. As others have said, the amount of data you're talking about is not very big. I regularly deal with Postgres databases that are two orders of magnitude larger, and they are lightning fast.
  - volkadav 7y ago
    i don't know the size of the dataset you're talking about, but it may help to open things up to use ram more liberally in particular as the default configs are rather conservative. https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serv... may help. good luck :)
  - GordonS 7y ago
    SSD makes a huge difference too.
anarazel 7y ago
That doesn't sound normal. Those lists are pretty much tiny compared to a lot of other data stored in postgres. So there must have been something else going on. It'd be cool if you could report details of the problem you had.
wongarsu 7y ago
I've personally used Postgres with a couple of gigabytes of data without problems and without tuning. Plenty of people seem to be using Postgres for deployments in the terabyte range, and apparently some people use it for petabytes of data. The default settings of postgres are more tuned for OLTP though, while you wanted to do OLAP. At least increasing the per-session memory limit would be advisiable to get anything done in any amount of time (the default is something tiny like 64MB). In general configuring Postgres to use more RAM is important to make things fast.
- riku_iki 7y ago
  For me the biggest issue with older versions is that selects run in single thread, and don't scale well on multiple cores, and it makes processing tables with billions rows very slow. Hopefully it will be fixed with new parallelism improvements.
  - anarazel 7y ago
    That should already have been improved a lot over the last few versions. Which version did you observe this with last, and if it was PG 10 or PG11, what type of query was this?
    - riku_iki 7y ago
      I run PG10 (default on Ubuntu LTS), usually join table with billion rows with table with millions rows with table with thousand rows, and constantly observe situation when just one core is loaded by 100%, and others are idle.
      - anarazel 7y ago
        Hm, that should have gotten better with 11 (shared parallel hashjoin), but even before, there's plenty plans where parallelism is possible (by e.g. building the hashtable for a hashjoin over the smaller table(s) in each worker). Any chance parallelism is disabled?

by lcnmrn 7y ago

Any support for big data? I tried to ingest HaveIBeenPwned password hashes in PostgreSQL 11 and after that Postgres become unusable, not even a simple query could run.

sterwill 7y ago

What was your schema? What did you index? I don't have a copy of the HiBP archive at hand, but the web site says it contains 551 million entries. If we assume the 11 GB archive expands to 30 GB, that's certainly not a small table, but it's not what I'd call "big data".

deleted 7y ago
[deleted]
lcnmrn 7y ago
'hash' column (varchar). Index created in advance slowed down the import so much it wasn't usable. I couldn't create an index after the import, too slow. I just want to find if one or multiple hashes are present in their data set. I used Clickhouse previously with large data like this and worked much better. Obliviously, I compare oranges with apples, but PostgreSQL could support a columnar data engine or some kind of index?
- sterwill 7y ago
  If you want an index on a column (and you probably do if you're going to query over 500 million rows) you have to create it at some point. Creating it after will be more efficient in your case. So what do you mean "too slow?" How long did it take? I don't have any experience with Clickhouse, and not much with columnar databases in general, but if we're talking about a simple table with one index over one text column, I'm not sure whether it makes any difference if you store the tuples row-wise or column-wise on disk. It's an index that covers 100% of the data either way. I don't know anything about your server but it sounds like PostgreSQL just wasn't able to get much IO throughput from your storage system. Things like storage hardware, filesystem type, and kernel parameters are the big factors here.
- anarazel 7y ago
  > I couldn't create an index after the import, too slow. What made that "too slow"? I just tested it, and it's not too bad. This is on my development environment (~3yo laptop, reasonably powerful), with all kinds of compile-time debugging enabled - but with compiler optimizations turned on. CREATE TABLE hashes(hash text, count int); BEGIN; TRUNCATE hashes; \copy hashes FROM program '7z e -so ~/tmp/pwned-passwords-sha1-ordered-by-count-v4.7z pwned-passwords-sha1-ordered-by-count-v4.txt' WITH (DELIMITER ':', FREEZE); Time: 1245024.360 ms (20:45.024) COMMIT; The bottleneck is 7z decompressing: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 24403 andres 20 0 31480 22292 4544 R 73.8 0.1 14:54.09 7z 23701 andres 20 0 4380008 11488 9024 S 27.9 0.0 5:42.04 postgres And with a bit config the PG side could be made consdierably faster. Creating an index: CREATE INDEX ON hashes(hash COLLATE "C"); Time: 709595.370 ms (11:49.595) And yes, without the COLLATE, and FREEZE above, it'd have taken a bit longer. But not that much. This is all while I also was working on the same loptop, including recompiling etc. EDIT: Formatting #2
  - jeltz 7y ago
    Was the index build with or without parallelism?
    - anarazel 7y ago
      With parallelism, default settings (i.e. 2 workers).
  - lcnmrn 7y ago
    How long does it take to search for a hash? I'm interested in both WHERE cases: hash = 'hash1' and hash in ('hash1', 'hash2').
    - anarazel 7y ago
      A few ms, with cold cache (both OS and postgres). The IN() roughly scales linearly with the number of elements, although it's often a bit better, as the upper tree levels are all in cache after the first few IN() element lookups.

coder543 7y ago

were you on an NVMe SSD? how much RAM did you have? how many CPU cores? The hardware is important. If you tried to import that data on a $5/mo DO droplet, I wouldn't be surprised by having issues.

lcnmrn 7y ago
Two cores CPU with hyperthreading. 16 GB of RAM. But data isn't stored in RAM, right?
- coder543 7y ago
  Not having enough RAM can significantly degrade database performance. Also, gotta wonder if you have good indices on the data. As others have said, the amount of data you're talking about is not very big. I regularly deal with Postgres databases that are two orders of magnitude larger, and they are lightning fast.
- volkadav 7y ago
  i don't know the size of the dataset you're talking about, but it may help to open things up to use ram more liberally in particular as the default configs are rather conservative. https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serv... may help. good luck :)
- GordonS 7y ago
  SSD makes a huge difference too.

anarazel 7y ago

That doesn't sound normal. Those lists are pretty much tiny compared to a lot of other data stored in postgres. So there must have been something else going on. It'd be cool if you could report details of the problem you had.

wongarsu 7y ago

I've personally used Postgres with a couple of gigabytes of data without problems and without tuning. Plenty of people seem to be using Postgres for deployments in the terabyte range, and apparently some people use it for petabytes of data. The default settings of postgres are more tuned for OLTP though, while you wanted to do OLAP. At least increasing the per-session memory limit would be advisiable to get anything done in any amount of time (the default is something tiny like 64MB). In general configuring Postgres to use more RAM is important to make things fast.

riku_iki 7y ago
For me the biggest issue with older versions is that selects run in single thread, and don't scale well on multiple cores, and it makes processing tables with billions rows very slow. Hopefully it will be fixed with new parallelism improvements.
- anarazel 7y ago
  That should already have been improved a lot over the last few versions. Which version did you observe this with last, and if it was PG 10 or PG11, what type of query was this?
  - riku_iki 7y ago
    I run PG10 (default on Ubuntu LTS), usually join table with billion rows with table with millions rows with table with thousand rows, and constantly observe situation when just one core is loaded by 100%, and others are idle.
    - anarazel 7y ago
      Hm, that should have gotten better with 11 (shared parallel hashjoin), but even before, there's plenty plans where parallelism is possible (by e.g. building the hashtable for a hashjoin over the smaller table(s) in each worker). Any chance parallelism is disabled?