464331 - PRAGMA cache_size for places succeeding, but not taking effect?

Status: RESOLVED FIXED

(Firefox :: Bookmarks & History, defect)

Description

[Seth Spitzer]

PRAGMA cache_size for places succeeding, but not taking effect?

You know how Firefox has code to set the cache_size (using PRAGMA cache_size) as a percentage of the machine's physical memory, right?

http://mxr.mozilla.org/firefox/source/toolkit/components/places/src/nsNavHistory.cpp#760

Playing around with the SQLite Manager add-on, I'm seeing my cache_size is 2000, which seems low. 

Interestingly, that's the value of PRAGMA default_cache_size.  (Is that a new PRAGMA?)

It appears that the call to "PRAGMA cache_size=x" succeeds, but doesn't really take effect.

Do you guys see the same thing?   Could this have regressed or changed in some version of SQLite?  In something I'm working on, using PRAGMA default_cache_size (instead of PRAGMA cache_size) seems to work.

Sorry to throw this stuff over the wall at you.

I'm seeing this with Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3

I haven't tried fx 3.1 beta yet.

Comment 1

[Seth Spitzer]

> I haven't tried fx 3.1 beta yet.

that might not be possible with the add on, as Fx 3.1 beta might be doing "PRAGMA locking_mode = EXCLUSIVE".

Comment 2

[Seth Spitzer]

I was expecting something more like 31457 (not 2000)

(2GB physical ram * .06) / page size of 4096

Comment 3

[Marco Bonardo [:mak]]

this sqlite checkin could be related:
http://www.sqlite.org/cvstrac/chngview?cn=5274
fixed for sqlite 3.6.0 while branch is on 3.5.9

Comment 4

[Shawn Wilsher :sdwilsh]

So, we have SQLite 3.5.9 on 1.9.0.  That's going to change with bug 464299.  Looking at http://www.sqlite.org/cvstrac/chngview?cn=5274, it seems like this may have been fixed on SQLite 3.6.0.

Comment 5

[Seth Spitzer]

Thanks for the research, Marco.

My lame workaround was to do "PRAGMA default_cache_size = X" instead.

Comment 6

[Shawn Wilsher :sdwilsh]

On Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.0.5pre) Gecko/2008111204 GranParadiso/3.0.5pre, which contains the new SQLite, I get 31457 back. (2 gigs of ram)

Comment 7

[Seth Spitzer]

from http://www.sqlite.org/pragma.html:

If you are doing UPDATEs or DELETEs  that change many rows of a database and you do not mind if SQLite uses more memory, you can increase the cache size for a possible speed improvement.

so marking as a perf regression.

> GranParadiso/3.0.5pre, which contains the new SQLite, I get
> 31457 back. (2 gigs of ram)

shawn, thanks for testing!

sounds like bug #464299 (the sqlite upgrade to 3.6.4) has fixed this bug.

do you want to mark it as fixed?

Comment 8

[Shawn Wilsher :sdwilsh]

sure

Comment 9

[Seth Spitzer]

shawn for 1.9.0.5, if you are staying at 3.5.9, you might want to take my lame workaround (see comment #5)

Comment 10

[Shawn Wilsher :sdwilsh]

I'm not sure it meets branch criteria...

Comment 11

[Samuel Sidler (old account; do not CC)]

It's not, sadly. And we're mostly frozen for that release anyway (taking one or to more blockers). We'll try and get a new sqlite in 1.9.0.6 to fix this.

Comment 12

[Shawn Wilsher :sdwilsh]

This is fixed on 1.9.1 and mozilla-central (upgraded sqlite), but I'm not sure it's ever going to be fixed for 1.9.0.x.

Comment 13

[Gervase Markham [:gerv]]

Bug 451915 - move Firefox/Places bugs to Firefox/Bookmarks and History. Remove all bugspam from this move by filtering for the string "places-to-b-and-h".

In Thunderbird 3.0b, you do that as follows:
Tools | Message Filters
Make sure the correct account is selected. Click "New"
Conditions: Body   contains   places-to-b-and-h
Change the action to "Delete Message".
Select "Manually Run" from the dropdown at the top.
Click OK.

Select the filter in the list, make sure "Inbox" is selected at the bottom, and click "Run Now". This should delete all the bugspam. You can then delete the filter.

Gerv