Subject: Re: Email not searchable in our archives

From: Bruce Momjian <[email-masked]>
Date: Tue, 11 Mar 2008 17:35:06 -0400 (EDT)

bruce wrote:
> Would someone please fine out why the attached email from Heikki is not
> appearing in a search of our archives?  I tried the subject and a line
> from the email and neither came up as a hit:

OK, it has been 24 hours since I reported some emails are not being
archived and no one has even responded they are looking at the problem.

Are we unable to manage our own archive search?  If we can't, I will
start linking to another archive from the TODO list.  Right now, every
time I need a URL for the TODO list I have to troll through the archives
by date until I find the email.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 11 Mar 2008 19:02:25 -0300

> archived and no one has even responded they are looking at the problem.

I can fix the problem by providing URLs with message ids.  Would that
help?  You can find out the Message-Id trivially from the original
email, and the URL would take you to the main message page complete with
thread links and all.

I haven't done it yet because I noticed that I'd need to create a
directory with thousands of files and I'm not sure how is it going to
work.  I've been trying to generate something of the form
msgid/f/e/[email-masked]
(i.e. creating subdirs for the first letters) but apparently Mhonarc
doesn't let me do that.

Perhaps I should just try without the subdir and see if it works.

Thoughts?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---

From: "Dave Page" <[email-masked]>
Date: Tue, 11 Mar 2008 22:04:58 +0000

>  by date until I find the email.

The people that know that code well all have day jobs and limited
spare time. I don't think it's unreasonable for them to take more than
24 hours to respond.

Google seems to work fine on our archives, so there is no need to link
elsewhere.

-- 
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 11 Mar 2008 18:09:49 -0400 (EDT)

> Google seems to work fine on our archives, so there is no need to link

Does Google link _into_ our archives ---  ah, that does work and is a
good work-around.  Thanks.

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 11 Mar 2008 18:11:27 -0400 (EDT)

> email, and the URL would take you to the main message page complete with

I have been pasting the email subject line into the search and usually
it is the first hit (when search works).

> Perhaps I should just try without the subdir and see if it works.

I am thinking we need the searches to actually work.  I can find the
emails eventually, and using Google with site:archives.postgresql.org
works pretty well for the time being.

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 11 Mar 2008 18:12:42 -0400 (EDT)

> Google seems to work fine on our archives, so there is no need to link

OK, but also consider I am not the only one who is doing searches, and I
have no idea how long it has been broken.  I am now finding 80% of
emails missing for June, 2008, so it is a massive issue, not just a few
emails.

---

From: "Joshua D. Drake" <[email-masked]>
Date: Tue, 11 Mar 2008 15:20:12 -0700

On Tue, 11 Mar 2008 19:02:25 -0300
 
> Perhaps I should just try without the subdir and see if it works.

I am wondering if we should bail out of Mhonarc all together. Do we
actually need it? We have the actual mbox files right? Couldn't we
build our own parser for whatever?

As a note, mailman also uses mbox files. We could try its archive
generation capability. I am not suggesting we move to mailman, just
that we use and existing tool that may work better to generate the
archives themselves.

Either way, this is certainly not urgent, although it is important.

Joshua D. Drake

-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director |  PostgreSQL political pundit

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 11 Mar 2008 18:26:04 -0400 (EDT)

Joshua D. Drake wrote:
-- Start of PGP signed section.
> Either way, this is certainly not urgent, although it is important.

FYI, I have set up a custom Google search site for
archives.postgresql.org:

	http://www.google.com/coop/cse?cx=008259951665801127283%3A7jpbk6al2qu

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 11 Mar 2008 21:51:24 -0300

> it is the first hit (when search works).

I am not saying we should continue to have a broken search -- I only say
that I can fix your particular use case.  If you're not interested in it
I can easily push the issue down in my TODO list.

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 11 Mar 2008 20:56:41 -0400 (EDT)

> I can easily push the issue down in my TODO list.

I don't feel it is right that you have to push up a TODO item just
because the search is broken.

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 11 Mar 2008 22:03:46 -0300

Joshua D. Drake wrote:

> generation capability.

Mailman archives are just as crappy, if not crappier.  And they know it.
It's based on Hypermail; I note that Hypermail's latest version happened
on 2003.  The Mailman guys are rethinking the issue; see 
http://wiki.list.org/display/DEV/ModernArchiving

Somebody suggests Lurker as one alternative:
http://lurker.sourceforge.net/

It is a very different interface.  Perhaps we could try it as an
experiment.  I have seen the Debian lists under it and it feels really
martian.

I don't want to lose Mhonarc, at least not for the moment.  It is
powerful and customizable and has served us reasonably well for a very
long time.  (Longer than most of us, actually.)

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 11 Mar 2008 22:05:13 -0300

> because the search is broken.

Well, then it means somebody else has to push up the "fix the search"
TODO item ;-)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---

From: Tom Lane <[email-masked]>
Date: Tue, 11 Mar 2008 21:12:23 -0400

> long time.  (Longer than most of us, actually.)

Agreed.  One of the problems with moving off it is that if we change to
something else that breaks mbox files at different points, we will
invalidate archive URLs.  We went there once already by accident and it
was not fun.

The From-line problem is minor anyway.  I think the real issue is why
the heck is our text search missing some old mail?  AFAIK no one has a
clue where that problem is, so it's premature to blame any particular
component.

			regards, tom lane

---

From: "Marc G. Fournier" <[email-masked]>
Date: Tue, 11 Mar 2008 22:26:10 -0300

Alvaro, have you checked with the mhonarc folks about the From-line issue?

- --On Tuesday, March 11, 2008 22:03:46 -0300 Alvaro Herrera 
<[email-masked]> wrote:

> Sent via pgsql-www mailing list ([email-masked])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-www

- ----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email . [email-masked]                              MSN . [email-masked]
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664

---

From: "Dave Page" <[email-masked]>
Date: Wed, 12 Mar 2008 09:05:06 +0000

>  build our own parser for whatever?

As I've mentioned a number of times, I've spent quite a bit of time
doing that already - I just don't have the spare cycles to finish
right now. We currently have a parser/archiver that will incrementally
archive messages from the (growing) mboxes into a database, and a web
frontend which resolves many of the problems with the current
archives.

There are optimisation/performance issues to be solved, as well as
some PHP crashes that seem to manifest themselves only on the FreeBSD
production server. The mime handling could also use some improvement
to properly reconstruct multi-part messages, but in reality I'm not
sure we ever have any where that's actually an issue.

If anyone else wants to pickup where I've left off, please let me know.

---

From: "Dave Page" <[email-masked]>
Date: Wed, 12 Mar 2008 09:11:16 +0000

>  invalidate archive URLs.  We went there once already by accident and it

The replacement archives system I've been working on takes it's data
from the existing monthly mboxes, and although it uses it's own URL
scheme, it does understand and accept the old URLs. That (and fixing
the thread-breaks-over-a-month issue) we pretty much my top
requirements.

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 12 Mar 2008 10:05:38 -0300

> Alvaro, have you checked with the mhonarc folks about the From-line issue?

Nope, not yet.

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 12 Mar 2008 10:09:02 -0300

> archived and no one has even responded they are looking at the problem.

I'm looking at the problem.  On a quick glance it is obvious that
there's something bogus going on -- the search database only contains
472 emails for May 2007, but Mhonarc reports 1187.

I have to go chase something at the bank right now, I'll update you
later.

---

From: "Dave Page" <[email-masked]>
Date: Wed, 12 Mar 2008 14:14:58 +0000

>  472 emails for May 2007, but Mhonarc reports 1187.

Confirmed. I added a test mode to a copy of the archives indexer, and
running that it claims it would index a further 715 messages, which
would give us a total of 1187.

So I guess the next step is to try running out of test mode to see if
the data actually makes it into the index now, but I didn't want to do
that and stomp on any testing you're doing.

---

From: "Dave Page" <[email-masked]>
Date: Wed, 12 Mar 2008 15:25:00 +0000

>  that and stomp on any testing you're doing.

OK, so running it properly has added those missing 715 messages. I
think we need to run a full index run which should restore any missing
pages, but before we do that, I'd kinda like to gather any ideas on
why this has happened before removing any evidence.

My best guess is simply that the indexer failed for some time and
noone noticed for a few weeks. By the time it was re-run, some
messages that it had missed were outside the timeframe that an
incremental crawl would have picked up (the current, plus last month).
Thoughts?

Stefan; any thoughts on how we might monitor that the indexer has been
running correctly? I assume that should be fairly easy if we have it
drop a timestamp someplace?

---

From: Stefan Kaltenbrunner <[email-masked]>
Date: Wed, 12 Mar 2008 16:30:59 +0100

> why this has happened before removing any evidence.

hmm weird ...

> drop a timestamp someplace?

yes - iirc there is even some discussion on that on pmt - will work 
something out for that in the next days.

Stefan

---

From: Magnus Hagander <[email-masked]>
Date: Wed, 12 Mar 2008 16:32:26 +0100

> drop a timestamp someplace?

I admint to having a ticket on pmt to get that set up.

Actually, it might be better to look into the actual database, and find the
latest email indexed? If it's older than <nn> something is wrong. It oculd
be the archives that's wrong and not indexer of course, but the point is
we'll get notified and someone can look into it.

Do you think we need to track it on a per-list basis, or just check for the
latest timestamp across all lists?

//Magnus

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Mar 2008 11:41:21 -0400

> incremental crawl would have picked up (the current, plus last month).

That would explain a contiguous range of messages that were not indexed,
but is that what we have?  I think the thing to do before you destroy
the old index is make a list of which messages were indexed and which
weren't.

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Mar 2008 12:06:57 -0400

"Dave Page" <[email-masked]> writes:
> On Wed, Mar 12, 2008 at 3:41 PM, Tom Lane <[email-masked]> wrote:
>> but is that what we have?

> Looking at the debug output, the messages that were missed were all contiguous:

OK, that seems to support your theory.  Might as well go ahead and
reindex.  +1 for getting some monitoring in there somewhere.

			regards, tom lane

---

From: "Dave Page" <[email-masked]>
Date: Wed, 12 Mar 2008 16:43:09 +0000

>  reindex.  +1 for getting some monitoring in there somewhere.

Yeah. Tried that - on the first attempt it died with a deadlock:

----------------
[search@community2 search]$ php archives.php -f
Indexing list 42 (atlpug)
Indexing list 43 (lapug)
Indexing list 41 (pdxpug)
Indexing list 40 (persianpug)
PHP Warning:  pg_execute(): Query failed: ERROR:  deadlock detected
DETAIL:  Process 9245 waits for ShareLock on transaction 378698;
blocked by process 9297.
Process 9297 waits for ShareLock on transaction 378690; blocked by process 9245.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."lists" x WHERE
"id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x" in
/home/search/portal/tools/search/classes/SearchDB.class.php on line 60
#0  SearchDB::mydie(Query failed: 0.94209700 1205339104
) called at [/home/search/portal/tools/search/classes/SearchDB.class.php:61]
#1  SearchDB::ExecutePrepared(0.94209700 1205339104, Array ([0] =>
0,[1] => 1183059217,[2] => PostgreSQL & our ML,[3] => Mohsen
Pahlevanzadeh <[email-masked]>,[4] =>

Ø³Ù„Ø§Ù… Ø¨Ø± Ø¯ÙˆØ³ØªØ§Ù† ÙØ§Ø±Ø³ÛŒ Ø²Ø¨Ø§Ù†ØŒ
Ø§ÛŒÙ† Ø§ÛŒÙ…ÛŒÙ„ Ø±Ùˆ Ø²Ø¯Ù… ØªØ§ Ú†Ù†Ø¯ ØªØ§ Ù†Ú(c)ØªÙ‡ Ø±Ùˆ ÛŒØ§Ø¯
Ø¢ÙˆØ± Ø¨Ø´Ù…:
Û±. Ù‚Ø±Ø§Ø±Ù‡ ÙØ§Ø±Ø³ÛŒ Ù†ÙˆØ´ØªÙ‡ Ø¨Ø´Ù‡.
Û². Ù‚Ø±Ø§Ø±Ù‡ Ø¬Ø§ÛŒÛŒ Ø¨Ø´Ù‡ ØªØ§ Ú(c)Ø§Ø±Ø¨Ø±Ø§ÛŒ pg Ø¯ÛŒÚ¯Ù‡
Ù†Ø±Ù† Ø±Ùˆ ML Ù‡Ø§ÛŒ Ø²Ø¨Ø§Ù†â€ŒÙ‡Ø§ÛŒ Ø¯ÛŒÚ¯Ù‡ Ù¾Ø³Øª
Ø¨Ú¯Ø°Ø§Ø±Ù†Ø¯.

Û³. ØªØ±ÙˆÛŒØ¬ DB Ù‡Ø§ÛŒ FOSSÛŒ Ø¯Ø± Ø§ÛŒØ±Ø§Ù†.
Û´. Ø§Ú¯Ø± Ø¨Ø´Ù‡ ØªØ±Ø¬Ù…Ù‡ Doc Ø§ÙˆÙ† Ø¨Ù‡ ÙØ§Ø±Ø³ÛŒ
--Ù…Ø­Ø³Ù†
--
-------------------------
Mohsen Pahlevanzadeh
email address : mohsen ( at ) pahlevanzadeh ( dot ) org
web site : http://pahlevanzadeh.org
IRC IM : m_pahlevanzadeh
yahoo IM : linuxorbsd
----------------------------

,[5] => PostgreSQL & our ML,[6] =>

Ø³Ù„Ø§Ù… Ø¨Ø± Ø¯ÙˆØ³ØªØ§Ù† ÙØ§Ø±Ø³ÛŒ Ø²Ø¨Ø§Ù†ØŒ
Ø§ÛŒÙ† Ø§ÛŒÙ…ÛŒÙ„ Ø±Ùˆ Ø²Ø¯Ù… ØªØ§ Ú†Ù†Ø¯ ØªØ§ Ù†Ú(c)ØªÙ‡ Ø±Ùˆ ÛŒØ§Ø¯
Ø¢ÙˆØ± Ø¨Ø´Ù…:
Û±. Ù‚Ø±Ø§Ø±Ù‡ ÙØ§Ø±Ø³ÛŒ Ù†ÙˆØ´ØªÙ‡ Ø¨Ø´Ù‡.
Û². Ù‚Ø±Ø§Ø±Ù‡ Ø¬Ø§ÛŒÛŒ Ø¨Ø´Ù‡ ØªØ§ Ú(c)Ø§Ø±Ø¨Ø±Ø§ÛŒ pg Ø¯ÛŒÚ¯Ù‡
Ù†Ø±Ù† Ø±Ùˆ ML Ù‡Ø§ÛŒ Ø²Ø¨Ø§Ù†â€ŒÙ‡Ø§ÛŒ Ø¯ÛŒÚ¯Ù‡ Ù¾Ø³Øª
Ø¨Ú¯Ø°Ø§Ø±Ù†Ø¯.

Û³. ØªØ±ÙˆÛŒØ¬ DB Ù‡Ø§ÛŒ FOSSÛŒ Ø¯Ø± Ø§ÛŒØ±Ø§Ù†.
Û´. Ø§Ú¯Ø± Ø¨Ø´Ù‡ ØªØ±Ø¬Ù…Ù‡ Doc Ø§ÙˆÙ† Ø¨Ù‡ ÙØ§Ø±Ø³ÛŒ
--Ù…Ø­Ø³Ù†
--
-------------------------
Mohsen Pahlevanzadeh
email address : mohsen ( at ) pahlevanzadeh ( dot ) org
web site : http://pahlevanzadeh.org
IRC IM : m_pahlevanzadeh
yahoo IM : linuxorbsd
----------------------------

)) called at [/home/search/portal/tools/search/classes/ArchiveIndexer.class.php:114]
#2  ArchiveIndexer->IndexSinglePage(40, persianpug, 2007, 6, 0) called
at [/home/search/portal/tools/search/classes/ArchiveIndexer.class.php:62]
#3  ArchiveIndexer->IndexMonth(40, persianpug, 2007, 6) called at
[/home/search/portal/tools/search/classes/ArchiveIndexer.class.php:40]
#4  ArchiveIndexer->Index(1, , -1, -1) called at
[/home/search/portal/tools/search/archives.php:28]
Query failed: 0.94209700 1205339104
----------------

Running it again now and it's up to 4000+ messages indexed  (ie. ones
that weren't already indexed) and is still working on pgsql-advocacy.
We'll see how it goes....

---

From: Stefan Kaltenbrunner <[email-masked]>
Date: Wed, 12 Mar 2008 17:55:53 +0100

> reindex.  +1 for getting some monitoring in there somewhere.

yeah we will work on that and add some new ones to our current list of 
354 active checks ;-)

Stefan

---

From: "Dave Page" <[email-masked]>
Date: Wed, 12 Mar 2008 16:56:58 +0000

>  yeah we will work on that and add some new ones to our current list of

One thing that crosses my mind - perhaps we should run a full index
once per week to try to catch this sort of thing in the future?

BTW, up to 13500 messages now...

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 12 Mar 2008 13:16:23 -0400 (EDT)

> once per week to try to catch this sort of thing in the future?

Seems running it weekly would mean failures would disappear and not be
diagnosed.

---

From: "Dave Page" <[email-masked]>
Date: Wed, 12 Mar 2008 17:22:40 +0000

>  Seems running it weekly would mean failures would disappear and not be

There is that - but then at least the index should be up to date
within 7 days at all times regardless of any corner cases that we
might otherwise not notice for some time.

BTW, the reindexing just finished - it added 31,269 messages that were
previously missing :-(

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 12 Mar 2008 14:09:27 -0400 (EDT)

> BTW, the reindexing just finished - it added 31,269 messages that were

31k emails.  Wow.

Thanks, I just checked an email that use to be missing and it is there
now.  Thanks!