5 ms·

I use recursive queries in SQL to search persistent suffix arrays (mainly when there is a LOT OF DATA). You can do string search hundreds of gigabytes of data w

by koeng 6y ago

I use recursive queries in SQL to search persistent suffix arrays (mainly when there is a LOT OF DATA). You can do string search hundreds of gigabytes of data within ~0.004s with SQLite on cheap SSD and pretty much no RAM - my goal is to be able to be able to search and align on all genetic information we have so far (about ~9 trillion base pairs in total) If anyone knows how to increase INSERT speeds on Postgres, I would appreciate. I can get about 100K per second with COPY or putting the inserts in a transaction. Have tried some more mirrored NVME drives and more RAM with ZFS, as well as dropping some unimportant indexes, but I'm still a little limited. Any thoughts?

mulmen 6y ago
Why ZFS? I thought copy-on-write filesystems were discouraged for database workloads. I know it is possible but are you getting other benefits from ZFS?
- noctune 6y ago
  For OLTP, COW is horrible. For OLAP it's usually fine. I've had good results with Postgres and ZFS with LZ4 compression.
- koeng 6y ago
  I have a single physical server for running all my virtual machines. ZFS is nice for everything else, and it just so happens that my database virtual machine takes up most of the SSD space on that computer. I guess I should have a dedicated machine if I really need to improve insert speeds though.
  - mulmen 6y ago
    Ah ok, pragmatic. Makes sense. Have you done things like disable constraints and indexing during loading?
    - koeng 6y ago
      I've disabled all constraints, but I haven't removed indexing during loading. I'll look into doing that, with adding the indexing later
      - mulmen 6y ago
        I don’t often work with Postgres itself but I do work with a derivative (Redshift) at $dayjob. Indexes don’t exist. Constraints are present in the syntax and the planner relies on them but they are never enforced. Our ETL layer deals with checking constraints at load time. Load performance is good. I’ve never run a rows/minute metric because it’s never been a problem.
        mulmen 6y ago
        Coming back to this just to provide a datapoint. I just loaded 10m rows into a table in less than one minute using Redshift. The data is encoded (compressed) at insert time but is not sorted until a vacuum runs.
site-packages1 6y ago
With inserts you’ll see great results from goosing the transaction array multiplexer. Just make sure you’re aware of piping the commit hash to the properly isolate the down range effects on the memcpy and you’ll avoid corruption issues. This has worked for me anyways.
- fabian2k 6y ago
  This comment is entirely random gibberish, not sure why you're posting that. I initially suspected a GPT-3 bot or something like that, but the comment history didn't support that.
  - ComputerGuru 6y ago
    GPT doesn’t make the kind of blatant grammatical mistakes the commenter did.
    - site-packages1 6y ago
      To be fair, I typed this quickly on a phone.
liminal 6y ago
Came across this upcoming webinar on the topic: https://www.timescale.com/webinar/5-ways-to-improve-yourpostgresql-insert-performance https://www.timescale.com/webinar/5-ways-to-improve-yourpost...
vii 6y ago
Assuming you're doing bulk inserts, and can restart the insert on any failure, one tactic is to disable all consistency and durability storage features during the insert. This heavily reduces the IO operations needed and the need to wait for them to complete. That's assuming that iops are the resource that you are constrained on. Steps: - use ext2 or even tmpfs as the filesystem, this disables journaling and COW features of ZFS; mount options async,nobarrier - set tables to be UNLOGGED to disable Postgres WAL This got things fast enough for my last use-case. But next ideas, as at some point you then become CPU/memory bound - you could try sharding by partitioning the table - another trick is to use SQLite as a backing store for inserts (it is quite fast if you turn off all logging) and then query with Postgres via a FDW https://github.com/pgspider/sqlite_fdw https://github.com/pgspider/sqlite_fdw
- koeng 6y ago
  How fast did it go for your last use-case? Disabling all the consistency and durability storage features is dreadful, to say the least, but I'd definitely be open to trying it if it means I can get it done quicker. The algorithm I'm working with uses single-cores but is linear-time and in my experience is at least 10x-100x faster than the IO. Memory is actually the biggest problem with the method, which is why I need to use a database to back it to disk.
  - prpl 6y ago
    It's relatively safe to do so for a one-time bulk load, especially if you have a way to verify consistency what you've loaded for integrity reasons (foreign keys, for example, might be a single query and a join). If it's not a one-time load, then you are right, it's dreadful. If, for example, you get a 3-5x speedup without checks (not unheard of), you can do it twice, verify the copies are the same, and get a 2x speedup.
rarrrrrr 6y ago
Is there a reason you don't wish to batch inserts into transaction(s)? Otherwise, you're asking the database to commit (fsync to disk) between each insert statement. Other ideas: - Have multiple processes inserting in parallel (this interacts with the commit_delay and commit_siblings settings in postgresql.conf) - Use unlogged tables (but table is truncated on db startup) - Put fsync=off and synchronous_commit=off in postgresql.conf (but db may be corrupted in a crash) - switching to BRIN indexes might be an option, but depends on data
- koeng 6y ago
  I do batch the inserts into a large transaction, and usually optimize for the transaction to commit in approximately ~10s. Not sure if it is optimal, but I like to monitor it like that. BRIN indexes actually work for some of the data types I'm working with! Thank you for pointing those out!
jarym 6y ago
COPY is the fastest method to insert data in Postgres. As another poster mentioned, setting table to UNLOGGED will help also. Finally, you can drop ALL indexes and recreate them after import (been hit and miss in my testing but on occasion has helped).
- koeng 6y ago
  How has dropping and recreating indexes been hit or miss? I've been thinking about doing exactly that
  - jarym 6y ago
    In some cases it did not appear to give me an improvement in data load time - I didn't do a rigorous test though which is why I said it might be 'hit and miss'.
chrisshroba 6y ago
I'm new to postgres and having trouble visualizing the recursive query you're describing. Would you be willing to share an example query (or something that illustrates the point on fake data)?
- koeng 6y ago
  The query that I have working is an abomination (it works though, https://github.com/Koeng101/poly/blob/polyStore_v0.1/store/construct.go#L244-L332 https://github.com/Koeng101/poly/blob/polyStore_v0.1/store/c...) The basic idea is that I am using SQL to back a suffix array (https://en.wikipedia.org/wiki/Suffix_array https://en.wikipedia.org/wiki/Suffix_array). The suffix array has all suffixes of a string (for "bats", it would have ["bat", "at", "t"] sorted in alphabetical order ["at", "bat", "t"] except storing the index of the suffix [1, 0, 2]. To search for a string, you go halfway in the suffix array and check if the suffix is lexicographically equal to, larger than, or smaller than your input. If it is not equal to, halve in the right direction. If the string is there, eventually you'll hit it.

by koeng 6y ago

mulmen 6y ago

Why ZFS? I thought copy-on-write filesystems were discouraged for database workloads. I know it is possible but are you getting other benefits from ZFS?

noctune 6y ago
For OLTP, COW is horrible. For OLAP it's usually fine. I've had good results with Postgres and ZFS with LZ4 compression.
koeng 6y ago
I have a single physical server for running all my virtual machines. ZFS is nice for everything else, and it just so happens that my database virtual machine takes up most of the SSD space on that computer. I guess I should have a dedicated machine if I really need to improve insert speeds though.
- mulmen 6y ago
  Ah ok, pragmatic. Makes sense. Have you done things like disable constraints and indexing during loading?
  - koeng 6y ago
    I've disabled all constraints, but I haven't removed indexing during loading. I'll look into doing that, with adding the indexing later
    - mulmen 6y ago
      I don’t often work with Postgres itself but I do work with a derivative (Redshift) at $dayjob. Indexes don’t exist. Constraints are present in the syntax and the planner relies on them but they are never enforced. Our ETL layer deals with checking constraints at load time. Load performance is good. I’ve never run a rows/minute metric because it’s never been a problem.
      - mulmen 6y ago
        Coming back to this just to provide a datapoint. I just loaded 10m rows into a table in less than one minute using Redshift. The data is encoded (compressed) at insert time but is not sorted until a vacuum runs.

site-packages1 6y ago

With inserts you’ll see great results from goosing the transaction array multiplexer. Just make sure you’re aware of piping the commit hash to the properly isolate the down range effects on the memcpy and you’ll avoid corruption issues. This has worked for me anyways.

fabian2k 6y ago
This comment is entirely random gibberish, not sure why you're posting that. I initially suspected a GPT-3 bot or something like that, but the comment history didn't support that.
- ComputerGuru 6y ago
  GPT doesn’t make the kind of blatant grammatical mistakes the commenter did.
  - site-packages1 6y ago
    To be fair, I typed this quickly on a phone.

liminal 6y ago

Came across this upcoming webinar on the topic: https://www.timescale.com/webinar/5-ways-to-improve-yourpostgresql-insert-performance https://www.timescale.com/webinar/5-ways-to-improve-yourpost...

vii 6y ago

Assuming you're doing bulk inserts, and can restart the insert on any failure, one tactic is to disable all consistency and durability storage features during the insert. This heavily reduces the IO operations needed and the need to wait for them to complete. That's assuming that iops are the resource that you are constrained on. Steps: - use ext2 or even tmpfs as the filesystem, this disables journaling and COW features of ZFS; mount options async,nobarrier - set tables to be UNLOGGED to disable Postgres WAL This got things fast enough for my last use-case. But next ideas, as at some point you then become CPU/memory bound - you could try sharding by partitioning the table - another trick is to use SQLite as a backing store for inserts (it is quite fast if you turn off all logging) and then query with Postgres via a FDW https://github.com/pgspider/sqlite_fdw https://github.com/pgspider/sqlite_fdw

koeng 6y ago
How fast did it go for your last use-case? Disabling all the consistency and durability storage features is dreadful, to say the least, but I'd definitely be open to trying it if it means I can get it done quicker. The algorithm I'm working with uses single-cores but is linear-time and in my experience is at least 10x-100x faster than the IO. Memory is actually the biggest problem with the method, which is why I need to use a database to back it to disk.
- prpl 6y ago
  It's relatively safe to do so for a one-time bulk load, especially if you have a way to verify consistency what you've loaded for integrity reasons (foreign keys, for example, might be a single query and a join). If it's not a one-time load, then you are right, it's dreadful. If, for example, you get a 3-5x speedup without checks (not unheard of), you can do it twice, verify the copies are the same, and get a 2x speedup.

rarrrrrr 6y ago

Is there a reason you don't wish to batch inserts into transaction(s)? Otherwise, you're asking the database to commit (fsync to disk) between each insert statement. Other ideas: - Have multiple processes inserting in parallel (this interacts with the commit_delay and commit_siblings settings in postgresql.conf) - Use unlogged tables (but table is truncated on db startup) - Put fsync=off and synchronous_commit=off in postgresql.conf (but db may be corrupted in a crash) - switching to BRIN indexes might be an option, but depends on data

koeng 6y ago
I do batch the inserts into a large transaction, and usually optimize for the transaction to commit in approximately ~10s. Not sure if it is optimal, but I like to monitor it like that. BRIN indexes actually work for some of the data types I'm working with! Thank you for pointing those out!

jarym 6y ago

COPY is the fastest method to insert data in Postgres. As another poster mentioned, setting table to UNLOGGED will help also. Finally, you can drop ALL indexes and recreate them after import (been hit and miss in my testing but on occasion has helped).

koeng 6y ago
How has dropping and recreating indexes been hit or miss? I've been thinking about doing exactly that
- jarym 6y ago
  In some cases it did not appear to give me an improvement in data load time - I didn't do a rigorous test though which is why I said it might be 'hit and miss'.

chrisshroba 6y ago

I'm new to postgres and having trouble visualizing the recursive query you're describing. Would you be willing to share an example query (or something that illustrates the point on fake data)?

koeng 6y ago
The query that I have working is an abomination (it works though, https://github.com/Koeng101/poly/blob/polyStore_v0.1/store/construct.go#L244-L332 https://github.com/Koeng101/poly/blob/polyStore_v0.1/store/c...) The basic idea is that I am using SQL to back a suffix array (https://en.wikipedia.org/wiki/Suffix_array https://en.wikipedia.org/wiki/Suffix_array). The suffix array has all suffixes of a string (for "bats", it would have ["bat", "at", "t"] sorted in alphabetical order ["at", "bat", "t"] except storing the index of the suffix [1, 0, 2]. To search for a string, you go halfway in the suffix array and check if the suffix is lexicographically equal to, larger than, or smaller than your input. If it is not equal to, halve in the right direction. If the string is there, eventually you'll hit it.