Hello, This is essentially part2 of trying to index large amounts of web data. To summarize what happened before: The initial discussion started here [1], Christian suggested some options, I dove into each of them, I realized that doing this on a low-memory system is harder than I initially thought. At Christian's suggestion, I tried to split the big db into smaller dbs and came up with a rudimentary sharding mechanism [3]. All attempts to full-text 30GB of data in BaseX, for me, resulted in OOM (do take into consideration that I only have 3.1GB of memory to allocate for BaseX).
Where to? I decided to look more into what Christian said in [2] about option 2, and to pick the exact values that I want, and to transfer them to PostgreSQL (after transferring, a GiST index would have to be built there, to allow full-text searches; PostgreSQL is picked because it uses an in-memory buffer for all large operations, and several files on disk, and if it needs to combine results that exceed the available memory, it goes to disk, but at all times it never exceeds the given amount of memory). Variant 1 (see attached script pg-import.sh) All good. So, I basically started writing XQuery that would do the following: - Open up a JDBC connection to PostgreSQL - Get me all text content from each thread page of the forum, and the db it belonged to - Create a prepared statement for one such thread page, populate the prepared statement, and execute it This ended up in OOM after around 250k records. So just to be clear, 250k lines were rows in PostgreSQL, which is nice but eventually it ended up in OOM. (Perhaps it has to do with how the GC works in Java .. I don't know) Variant 2 (see attached script pg-import2.sh) I did something similar to the above: - Open up a JDBC connection to PostgreSQL - Get all posts and for each post get the author, the date, the message content, the post id, the BaseX db name (cause we're going over all shards, and each shard is a BaseX db) - Create a prepared statement for each post with the data mentioned above, and execute it This also ended up in OOM after around 340k records (my approximation would be that there were around 3M posts in the data). To summarize, I'm tempted to believe that there might be a leak in the BaseX implementation of XQuery. I will provide in the following, the relevant versions of the software used: - BaseX 9.2.4 - java version "1.8.0_151" Java(TM) SE Runtime Environment (build 1.8.0_151-b12) Java HotSpot(TM) 64-Bit Server VM (build 25.151-b12, mixed mode) - the JVM memory param value was -Xmx3100m I would be interested to know your thoughts Thanks, Stefan [1] https://mailman.uni-konstanz.de/pipermail/basex-talk/2019-September/014715.html [2] https://mailman.uni-konstanz.de/pipermail/basex-talk/2019-October/014727.html [3] https://mailman.uni-konstanz.de/pipermail/basex-talk/2019-October/014729.html
#!/bin/bash mkdir tmp # TODO: work on the full-text part in PostgreSQL # a trigger will be required to make it work. cat << EOF > tmp/archive-schema.sql CREATE DATABASE "archive"; \c "archive" CREATE EXTENSION IF NOT EXISTS btree_gist; DROP TABLE IF EXISTS thread; CREATE TABLE IF NOT EXISTS thread ( id SERIAL PRIMARY KEY, content TEXT, label VARCHAR(300), forum VARCHAR(300) ); -- CREATE INDEX idx_thread_content ON thread USING gist(content); CREATE INDEX idx_thread_label ON thread(label); CREATE UNIQUE INDEX idx_thread_uniq_label_forum ON thread(label,forum); EOF LD_LIBRARY_PATH="" /share/Public/builds/prefix/bin/psql -U postgres -d postgres < tmp/archive-schema.sql cat << 'EOF' > tmp/import.xq let $conn-string:="jdbc:postgresql://localhost:5432/archive?user=postgres&password=postgres" let $pgconn := sql:connect($conn-string) let $dbs:=fn:filter(db:list(), function($x){ matches($x,"linuxquestions-shard-") }) for $db in fn:reverse(fn:sort($dbs)) for $x in db:open($db) let $label:=$x/fn:base-uri() let $content:=$x//*[matches(@id,"post_message_")]/text() let $params := <sql:parameters> <sql:parameter type='string'>{ $label }</sql:parameter> <sql:parameter type='string'>{ $content }</sql:parameter> <sql:parameter type='string'>{ $db }</sql:parameter> </sql:parameters> let $prep:=sql:prepare($pgconn, "INSERT INTO thread(label,content,forum) VALUES(?,?,?)") return try { sql:execute-prepared($prep,$params) } catch * { 'Error [' || $err:code || ']: ' || $err:description || '--' || $params } EOF /share/Public/builds/basex/bin/basexclient -U admin -P admin tmp/import.xq
#!/bin/bash mkdir tmp # TODO: work on the full-text part in PostgreSQL # a trigger will be required to make it work. # # DONE: more detailed content extraction. cat << EOF > tmp/archive-schema.sql CREATE DATABASE "archive"; \c "archive" CREATE EXTENSION IF NOT EXISTS btree_gist; DROP TABLE IF EXISTS thread2; CREATE TABLE IF NOT EXISTS thread2 ( id SERIAL PRIMARY KEY, date VARCHAR(300), author VARCHAR(300), post_id VARCHAR(300), doc_uri VARCHAR(300), content TEXT, basex_db VARCHAR(300) ); CREATE INDEX idx_thread2_date ON thread2(date); CREATE INDEX idx_thread2_author ON thread2(author); CREATE INDEX idx_thread2_post_id ON thread2(post_id); CREATE INDEX idx_thread2_doc_uri ON thread2(doc_uri); CREATE INDEX idx_thread2_basex_db ON thread2(basex_db); CREATE UNIQUE INDEX idx_thread2_doc_uri ON thread2(doc_uri); EOF LD_LIBRARY_PATH="" /share/Public/builds/prefix/bin/psql -U postgres -d postgres < tmp/archive-schema.sql cat << 'EOF' > tmp/import.xq let $conn-string:="jdbc:postgresql://localhost:5432/archive?user=postgres&password=postgres" let $pgconn := sql:connect($conn-string) let $dbs:=fn:filter(db:list(), function($x){ matches($x,"linuxquestions-shard-") }) for $db in fn:reverse(fn:sort($dbs)) for $x in db:open($db) let $doc_uri:=$x/fn:base-uri() let $elems:=$x//table[matches(@id,"post\d+")] for $elem in $elems let $date:=$elem/tr[1]/td[1]/text() let $author:=$elem/tr[2]/td[1]/div[1]/a//text() let $post_id:=$elem/@id/string() let $content:=$elem//*[matches(@id,"post_message_")]/text() let $params := <sql:parameters> <sql:parameter type='string'>{ $date }</sql:parameter> <sql:parameter type='string'>{ $author }</sql:parameter> <sql:parameter type='string'>{ $post_id }</sql:parameter> <sql:parameter type='string'>{ $doc_uri }</sql:parameter> <sql:parameter type='string'>{ $content }</sql:parameter> <sql:parameter type='string'>{ $db }</sql:parameter> </sql:parameters> let $prep:=sql:prepare($pgconn, "INSERT INTO thread2(date,author,post_id,doc_uri,content,basex_db) VALUES(?,?,?,?,?,?)") return try { sql:execute-prepared($prep,$params) } catch * { 'Error [' || $err:code || ']: ' || $err:description || '--' || $params } EOF /share/Public/builds/basex/bin/basexclient -U admin -P admin tmp/import.xq