Re: [boost] [trac] Website performance, and disabling the code browser?

Subject: Re: [boost] [trac] Website performance, and disabling the code browser?
From: Dean Michael Berris (mikhailberis_at_[hidden])
Date: 2010-11-28 20:07:49

On Mon, Nov 29, 2010 at 8:09 AM, Phil Endecott
<spam_from_boost_dev_at_[hidden]> wrote:
> Dean Michael Berris wrote:
>>
>> On Mon, Nov 29, 2010 at 4:31 AM, Artyom <artyomtnk_at_[hidden]> wrote:
>>>
>>> It is quite big misconception that DB is bottle neck.
>>
>> Sorry, but SQLite is not fast enough if you have multiple processes
>> trying to perform the same query on the same database. The reason is
>> because of the database locking mechanism that the SQLite library
>> enforces on the database file.
>
> My understanding was that SQLite only did locking for writes, i.e. it's
> possible for multiple threads to be reading at the same time. Â In fact this
> is FAQ #5 at http://www.sqlite.org/faq.html#q5 :
>
> Â  Â "SQLite allows multiple processes to have the database file open at
> Â  Â once, and for multiple processes to read the database at once. When
> Â  Â any process wants to write, it must lock the entire database file
> Â  Â for the duration of its update. But that normally only takes a few
> Â  Â milliseconds. Other processes just wait on the writer to finish then
> Â  Â continue about their business."
>
> Is trac doing writes for things that should only be reads, perhaps?
>

What I understand is that the Python implementation is not only
inefficient this way, because it just doesn't leave the DB locking to
the C library. If you look at the relevant part in the Python SQLite
DB API adapter implementation:

"When a database is accessed by multiple connections, and one of the
processes modifies the database, the SQLite database is locked until
that transaction is committed. The timeout parameter specifies how
long the connection should wait for the lock to go away until raising
an exception. The default for the timeout parameter is 5.0 (five
seconds)."

Note that a COMMIT has to succeed in order for the other reader
processes to get a chance to perform the read -- also writes on large
enough SQLite DB's that are normalized (i.e. having a lot of tables
and performing joins with lots of integrity constraints) means that
your other processes can wait in the order of seconds -- especially
when you're also serving static web pages and generating dynamic
pages, with a disk spinning at 5400 or even 7200 rpm. The reason for
this is SQLite's design which uses a single file for the whole
database. Deletions are particularly hairy, and when doing joins to
yield thousands of rows (as in the case of Boost), you run into a
perfect storm.

Especially now that we're doing a bug sprint, I don't see SQLite being
scalable compared to say MySQL/PgSQL which are optimized to do
compute-intensive and highly concurrent queries.

>
> (in another post:)
>>
>> I like the idea of disabling the code browser.
>
> I don't; to me an occasional error message is better than not working at
> all.
>

Not working at all -- you mean the code browser right? I think Trac's
value right now is really the issue tracking and the Wiki, not
necessarily the code browser.

It should be trivial so serve the Subversion repository -- or a mirror
of it -- for online viewing without having to use Trac's SVN browser.

-- 
Dean Michael Berris
deanberris.com