Heads up: native fulltext search on pgsql

Development-related discussion, including bundled plugins
User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Heads up: native fulltext search on pgsql

Postby fox » 04 Aug 2015, 14:09

Report here if I broke EVERYTHING.

Why: because its faster by multiple orders of magnitude.

Stemming is supported. Stemming language can be set per feed and in the search dialog for specific query (this is the best I could figure out so far given the limitations I'm dealing with).

You don't need to change default language to search for w/e unicode stuff, it's only for word stemming.

Also, you will need to reindex your old stuff. This unfortunately is a pretty slow process. Stemming is currently hardcoded to English when importing old data.

Code: Select all

./update.php --gen-search-idx

nameless
Bear Rating Master
Bear Rating Master
Posts: 126
Joined: 28 Aug 2013, 20:33

Re: Heads up: native fulltext search on pgsql

Postby nameless » 04 Aug 2015, 18:24

This is probably gonna annoy some user on shared hosting :mrgreen:
Not that anyone here would care :lol:

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Heads up: native fulltext search on pgsql

Postby fox » 04 Aug 2015, 18:46

tbf i'm sure they are all using mysql

nameless
Bear Rating Master
Bear Rating Master
Posts: 126
Joined: 28 Aug 2013, 20:33

Re: Heads up: native fulltext search on pgsql

Postby nameless » 04 Aug 2015, 18:49

thats probably true
btw. update.php is processing more articles than it initially told me. is that something thats supposed to happen?

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Heads up: native fulltext search on pgsql

Postby fox » 04 Aug 2015, 18:57

anything is possible with my garbage coding

nameless
Bear Rating Master
Bear Rating Master
Posts: 126
Joined: 28 Aug 2013, 20:33

Re: Heads up: native fulltext search on pgsql

Postby nameless » 04 Aug 2015, 19:28

well i did breake some things, not all.
ttrss is now slow as fuck and the system tab in preferences keeps telling me an error occurred.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Heads up: native fulltext search on pgsql

Postby fox » 04 Aug 2015, 19:36

you probably shouldn't run reindex in the background if your server can't handle it

nameless
Bear Rating Master
Bear Rating Master
Posts: 126
Joined: 28 Aug 2013, 20:33

Re: Heads up: native fulltext search on pgsql

Postby nameless » 04 Aug 2015, 19:40

i dunno, its a pretty big machine.
i still got two cores almost idling also theres still lots of ram unused.

edit: anyways i will the have the indexer running for some time. i still might go back to a backup tomorrow
is there a reason why the indexer did processing for 500 articles at once when i started and now dropped to one article?

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Heads up: native fulltext search on pgsql

Postby sleeper_service » 04 Aug 2015, 22:10

for what it's worth:

Code: Select all

$ ./update.php --gen-search-idx                               
[18:58:45/26841] Lock: update.lock
Generating search index (stemming set to English)...
Articles to process: 49472.
Processed 500 articles...
Processed 1000 articles...
Processed 1500 articles...
Processed 2000 articles...
Processed 2500 articles...
...
Processed 49000 articles...
Processed 49472 articles...
All done.

about 8 minutes.

pcause
Bear Rating Master
Bear Rating Master
Posts: 144
Joined: 23 Aug 2013, 19:52

Re: Heads up: native fulltext search on pgsql

Postby pcause » 04 Aug 2015, 22:23

sleeper_service wrote:for what it's worth:

Code: Select all

$ ./update.php --gen-search-idx                               
[18:58:45/26841] Lock: update.lock
Generating search index (stemming set to English)...
Articles to process: 49472.
Processed 500 articles...
Processed 1000 articles...
Processed 1500 articles...
Processed 2000 articles...
Processed 2500 articles...
...
Processed 49000 articles...
Processed 49472 articles...
All done.

about 8 minutes.


What kind of system are you running on?

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Heads up: native fulltext search on pgsql

Postby fox » 04 Aug 2015, 23:51

it took me maybe about 15 minutes on a shitty vm with about 200k articles

it should be running by 500 idk (if there's enough of them left without indexes)

nameless
Bear Rating Master
Bear Rating Master
Posts: 126
Joined: 28 Aug 2013, 20:33

Re: Heads up: native fulltext search on pgsql

Postby nameless » 05 Aug 2015, 01:23

i let i run and now server storage quota exceeded.
wtf :D

Filesystem 1K-blocks Used Available Use% Mounted on
/dev/vzfs 50000000 50000000 0 100% /

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Heads up: native fulltext search on pgsql

Postby fox » 05 Aug 2015, 01:51

keep us informed

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Heads up: native fulltext search on pgsql

Postby sleeper_service » 05 Aug 2015, 02:20

pcause wrote:
sleeper_service wrote:for what it's worth:

Code: Select all

$ ./update.php --gen-search-idx                               
[18:58:45/26841] Lock: update.lock
Generating search index (stemming set to English)...
Articles to process: 49472.
Processed 500 articles...
Processed 1000 articles...
...
Processed 49472 articles...
All done.

about 8 minutes.


What kind of system are you running on?

might have been closer to 6 minutes..
twin dual core opteron cpus, 16g memory. big pile of reasonably fast disks.

nameless
Bear Rating Master
Bear Rating Master
Posts: 126
Joined: 28 Aug 2013, 20:33

Re: Heads up: native fulltext search on pgsql

Postby nameless » 05 Aug 2015, 13:38

fox wrote:keep us informed

I just looked through last nights monitoring.
CPU usage was moderated but exploded to a 100% when update.php started processing 1 article instead of 500.
My ttrss database filled approximately 35 gigabyte of storage on my server.

I rolled back to a backup. Gonna try this again tonight.


Return to “Development”

Who is online

Users browsing this forum: No registered users and 2 guests