<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">I take it you're trying to do with from a PHP script run through apache?
</pre>
</blockquote>
Its via a php CLI script.<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">
Flushing updates to a large database can take a while - it's easy to
update 4 of the 5 tables, but for the postlist table we need to amend
an entry for each term which indexed the document, which is potentially
a lot of entries widely spread through the table.
Not that the cost here scales sublinearly - i.e. it's a lot less than 100
times as expensive to delete 100 documents and flush them in one go than
to delete and flush one document at a time. Perhaps that's something
you can bear in mind when designing how deletions are handled?
</pre>
</blockquote>
Definitely, not using flushes anywhere, just letting a script do all
updates and deletes in a row, and flushing on its own. Should I make it
so all deletes are done by themselves, ie without updates interwoven?<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">From my laymens point of view, it seems that the xapian delete document
function freezes up the OS on a filesystem level. Is this a correct
assessment?
</pre>
</blockquote>
<pre wrap=""><!---->
No. My best guess is you're probably either suffering from a lot of
swapping, or just disk I/O overload. Or there may be issues to do with
this running inside Apache too.
</pre>
</blockquote>
Apache is not involved here, but swapping and disk i/o are both likely
pof:s here, especially since the machine is quite busy with serving a
pretty large site at the same time.<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">
Calling WritableDatabase::delete_document() doesn't do much work at all.
The postlist table updates are saved up for when you call
WritableDatabase::flush() (either explicitly, or implicitly as happens
when you close a database, or after 1000 updates). Some memory is
require to buffer up these changes, but I doubt that's causing you
to swap unless you're really tight on memory.
A flush can require a lot of I/O, so the other tasks apache is doing are
also I/O bound, you could cause these to run more slowly such that more
try to run at once and things get slow. But this is not really Xapian's
fault - the server is just overloaded. You either need to add more RAM
to improve caching and reduce I/O, beef up the disk subsystem so I/O
requests complete sooner, or move work off to another server.
</pre>
</blockquote>
Yep, moving to a different machine that can be dedicated to this is
probably my best shot.<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">As for running this inside Apache, if I were designing something like
this, I'd lean towards having the web interface lodge a request with a
index management process which does the real work behind the scenes
(this could be as simple as saving a file in a directory saying to
delete document N, and having the index management process scan the
directory for new files). Then the index management process can batch
up multiple requests, and different users can make requests at the same
time without locking issues.
</pre>
</blockquote>
Its free-standing php cli scripts doing this behind the scenes job for
me, and it also uses a bunch of tables in a mysql db to know what it
should do - add, update or delete documents in the xapian index.<br>
I know the mysql server (other machine) is not the bottleneck, but
rather it must be that the server that I run the scripts on is simply
overloaded, just as you say.<br>
In fact I know this is the case many times as when I kill other
resource hogging scripts the xapian indexer speeds up. However it did
not do anything at all for when I was trying to delete documents the
other day. But that of course was because I was passing the docid as a
string, not an int. Smart eh? :p<br>
<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<blockquote type="cite">
<pre wrap="">I just read a post somewhere on the net about how you can use term names
to ID and delete items out of an xapian index instead of using a
document_id. Is this faster? (would seem strange if it was).
</pre>
</blockquote>
<pre wrap=""><!---->
Using a term will be slower. Currently the internal implementation is
to just open the postlist for the term and then call delete_document for
each docid but there's scope for optimising this in the future. But
even with this optimised I expect it will be faster to delete by docid.
</pre>
</blockquote>
Thats what I thought. Cool.<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">
Hmm, if you're calling from PHP, make sure that you're passing a PHP
integer to delete_document, not a PHP string. If you pass a string
you'll be calling the "delete_document by term" variant. If you
want to make sure, you can force a value to be an integer by using:
intval($docid)
</pre>
</blockquote>
Yep that was the root of a long session of bug-hunting the other night.<br>
Can't believe I didn't figure it out sooner though, being that I had
already made sure docids are passed as int:s to the update function.<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">Is the answer to my question here to split the data into multiple databases?
</pre>
</blockquote>
<pre wrap=""><!---->
It might help, since you say this worked with smaller databases, but I'd
try moving other work off the server first. Your database isn't that
enormous.
</pre>
</blockquote>
Can't do that until I have set up a new machine, but the multiple-db
patch is already done and implemented so will see how that pans out.
The index is rebuilding right now. Exciting :)<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">Technically I know how to do it, but not logically. How many databases
should I aim for - ie, should I aim for them not to be over a certain
size, contain a certain amount of documents, or something else?
</pre>
</blockquote>
<pre wrap=""><!---->
It depends on the spec of the server really. When I'm building gmane's
index from scratch, I build a number of databases of 1 million documents
each and then merge them at the end, as I found that was fastest (though
I didn't do extensive trials).
</pre>
</blockquote>
I tried with 250k docs per sub-db this time. But I have made it so I
can adjust this limit without rebuilding the entire db, so it can
change later.<br>
<br>
Question: how do you merge the sub-db:s in the end, for the search
functions?<br>
<br>
The machine I use is a dual p3 1200 mhz xeon, and 3.5 gig ram.<br>
It is quite overworked serving a busy website, various stats-generating
scripts, and other misc scripts. Moving the xapian stuff to a different
server is my next step.<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">Should I distribute documents sequentially into them, or randomly, or
use some other scheme?
</pre>
</blockquote>
<pre wrap=""><!---->
If you ever want to search a particular subset, you could put that in
its own database (or databases). And if you need to remove old
documents periodically (e.g. anything over a year old) doing it by
date makes sense. Otherwise it's probably rather arbitrary.
</pre>
</blockquote>
Yeah documents could stay in the index for a day or for 5 years, so no
need for me to think about that then.<br>
<br>
Thank you so much for your help Olly, very much appreciated.<br>
<br>
Alec<br>
<blockquote cite="mid20061204185315.GZ21562@survex.com" type="cite">
<pre wrap="">
Cheers,
Olly
</pre>
</blockquote>
</body>
</html>