doing an "Vaccum" / "Compact" function on the SQLite files of the profile
improves the performance at lot
Do you have the new security features
Tell me if the site I'm visiting is a suspected attack side
Tell me if the stie I'm viviting is a suspected forgery
active?
Then first change the preference
urlclassifier.updatecachemax from -1 to 104857600
(via using about:config).
The following SQLite file are part of your profile:
Verzeichnis von S:\mof-3000\mozilla\firefox\Profiles\5eqjlbn3.default
26.06.08 2.28 7168 0 content-prefs.sqlite
26.06.08 11.39 20480 0 cookies.sqlite
26.06.08 2.30 10240 0 downloads.sqlite
26.06.08 11.39 9216 0 formhistory.sqlite
26.06.08 2.31 2048 0 permissions.sqlite
26.06.08 11.39 3837952 0 places.sqlite
26.06.08 2.31 2048 0 search.sqlite
26.06.08 11.37 34660352 0 urlclassifier3.sqlite
8 Datei(en) 38549504 Byte belegt
Verzeichnis von
S:\mof-3000\mozilla\firefox\Profiles\5eqjlbn3.default\OfflineCache
22.06.08 19.46 4096 0 index.sqlite
1 Datei(en) 4096 Byte belegt
As you see, the file urlclassifier3.sqlite ist currenty 34 MB big.
This file is growing from 0 ( at creation of the profile )
to about 56 MB (before Vaccum/Ccmpact ) at my installation.
There is clear a need to "Vaccum" this file after creation of
a profile and first few surf sessions.
Hwoto to shrink / vacuum / compact the SQLite file.
1. Manual Way
1.1 Get the application SQLiteBrowse from Hobbes
http://hobbes.nmsu.edu/cgi-bin/h-search?key=SQLiteBrowser&pushbutton=Search
Install the application on your system
1.2 shut down Firefox 3.0
1.3 save your current profile
1.4 Start the SQLite Browser
Open DB S:\mof-3000\mozilla\firefox\Profiles\5eqjlbn3.default\
urlclassifier3.sqlite
select menu Menue "File/Compact Database"
function processes at my installation about 1 min
Than the meassage box " compact done" is display with okay bottom
caveat: at the current version of SQLiteBrowser 1.3b
the cursor is still at "clock - Busy indicator -
but click to bottom okay works on my installation
Close the DB.
Repeat the procedure for the rest of the SQLite files of your profile
at my installation after 3 day of surfing,
the vacuum / compact function shrinks the
file urlclassifier3.sqlite from 56 MB to 34 MB
1.5 Do a test with Firefox 3.0
performance of your system? :-)
2. automatic way to vacuum the SQLite file a request
I like to do this via a command file or program execution on request.
To built this fuction, I do need your help!
I have not found a binary of SQLite executable (at ff 3.0 SQLite
release level)
The RxSql package 2.5 is at SQLite 3.3.x
The SQLite is currently at SQLite 3.5.9 according project
kind regards
Rainer
References:
==============
Bug 439340 – Background cleanup actions block desktop on OS/2
https://bugzilla.mozilla.org/show_bug.cgi?id=439340
Firefox 3.0: Blockien des Systems durch minutenlange hohe HDD I/O
Aktivität" - Workaround
http://de.os2.org/forum/diskussion/index.php3?all=116554
http://sqlitebrowser.sourceforge.net/index.html
http://www.sqlite.org/lang_vacuum.html
http://rexxsql.sourceforge.net/
What the heck is stored in this db that requires 34MB?
Are all files Firefox 3 receives put in there?
Is it the new cache mechanism?
>> the vacuum / compact function shrinks the
>> file urlclassifier3.sqlite from 56 MB to 34 MB
>
> What the heck is stored in this db that requires 34MB?
It's the Google-supplied list of malware sites (phishing, etc.).
here are the data about the groth behaviour of the SQLite file in my Profile
History: Size of SQLite File "urlclassifier3.sqlite" in my Firefox 3.0
profile
2008-06-20 12.25 Profile created
2008-06-22 19.46 51.064.832
2008-06-26 00.15 53.800.960 before frist time SQLite Function Vacuum
2008-06-26 00.48 34.660.352 after Vacuum ,
Vacuum execution time: 60 sec - about
2008-06-26 11.37 34.660.352
2008-06-28 17.03 34.660.352 before Vacuum
2008-06-28 17.51 29.278.208 after Vacuum
Vacuum execution time: 45 sec
2008-06-29 08.40 29.458.432 current
Conclusion:
Shortly after profile creation and first few surf session
a Vaccum of urlclassifier3.sqlite is strongly, highly recommend
and than on a regular schedule
Rainer
I have the opinion that this needs to be a feature of the program
startup. Any chance you could fill a bug to see if the change would fly
past the powers that are? This would affect SeaMonkey also.
--
Bill
Thanks a Million!
Can you actually _measure_ a performance gain from before vacuuming?
> I have the opinion that this needs to be a feature of the program
> startup. Any chance you could fill a bug to see if the change would fly
> past the powers that are? This would affect SeaMonkey also.
That won't be happening, I'm pretty sure that hardly anybody will want
to delay loading the application by 60s or more just to clean out a
database.
--
Please | Official Warpzilla Ports: http://www.mozilla.org/ports/os2/
reply in |
newsgroup | Enhanced OS/2 builds: http://pmw-warpzilla.sf.net/
Steve's Warpzilla Tips: http://www.os2bbs.com/os2news/Warpzilla.html
Does anybody else see this behavior?
Just out of curiosity: at the office I have FF3 on Windows Server
2003, and it works smoothly. I checked to see if it creates a huge
urlclassifier3.sqlite, but the is _no_trace_ of that file. There is a
urlclassifier2.sqlite instead. Why?
Thank you
Bye
Cris
Hello Chirs,
please check your profile under security on the office maschine,
both feature of 2. and 3 Box are probably not active
Tell me if the site I'm visiting is a suspected attack side
Tell me if the stie I'm visiting is a suspected forgery
so no SQLite files for this feature are generated
Kind Regards
Rainer
No. But if you start if from the command line, do you see any output?
Maybe redirect it, like this
firefox.exe > output.log 2>&1
and then look at the log file.
If you are running any plugins (prime suspects: Flash and Java, as
always) you might want to try without, and see if that helps.
> Just out of curiosity: at the office I have FF3 on Windows Server
> 2003, and it works smoothly. I checked to see if it creates a huge
> urlclassifier3.sqlite, but the is _no_trace_ of that file. There is a
> urlclassifier2.sqlite instead. Why?
Don't know, same for me on Linux and Windows at work, although the
relevant settings are the same as on OS/2 at home.
--
Please Enhanced OS/2 builds of Mozilla applications
reply in http://pmw-warpzilla.sf.net/
newsgroup
> 2. automatic way to vacuum the SQLite file a request
>
> I like to do this via a command file or program execution on request.
>
> To built this fuction, I do need your help!
>
> I have not found a binary of SQLite executable (at ff 3.0 SQLite
> release level)
>
>
> The RxSql package 2.5 is at SQLite 3.3.x
>
> The SQLite is currently at SQLite 3.5.9 according project
I have finally packaged SQLite 3.5.9, it's now on Hobbes. (Currently in
/pub/imcoming, should be in /pub/os2/dev/database/ soon.) That should
be fine to work with the Firefox files and script whatever you want to
do. But don't expect support from me on that! I just maintain the OS/2
version but don't have time to actually use it.
--
Please | Official Warpzilla Ports: http://www.mozilla.org/ports/os2/
reply in |
newsgroup | Enhanced OS/2 builds: http://pmw-warpzilla.sf.net/
Hallo Peter,
thanks for the new port.
The following example have been tested with old SQLite 3.4.2 port.
The vacuum command does work, but do not physical shrink the DB file.
I will test the example with the new port 3.5.9
Here is the example code:
======================
File: s:\SQLite342.cmd
======================
rem mode 80,50
rem
rem SET LIBPATHSTRICT=T
rem
SET BEGINLIBPATH=S:\download\os2\SQLite\V3-4-2\sqlite-3.4.2\dll;
rem
S:\download\os2\SQLite\V3-4-2\sqlite-3.4.2\bin\SQLite3.exe %1 %2 %3 %4 %5 %6
===================
File: s:\sql-in.txt
===================
-- ***********************************
-- File S:\sql-in.txt
-- SQL Statemets for Vacuum a Database
-- ***********************************
.echo on
PRAGMA legacy_file_format;
PRAGMA temp_store_directory;
PRAGMA cache_size;
PRAGMA database_list;
--
PRAGMA main.freelist_count;
--
vacuum;
--
PRAGMA main.freelist_count;
.quit
============================================================
File: SQLite-VACUUM-the-DB-s-of-Firefox-3-0-User-Profile.cmd
============================================================
rem Example for Vacuum the SQLite Files of a user profile
rem
rem File: S:\SQLite-VACUUM-the-DB-s-of-Firefox-3-0-User-Profile.cmd
rem
rem 2008-07-07 11.0 V1.0 Rainer D. Stroebel
rem
rem
rem Test the command file:
rem
rem S:\SQLite-VACUUM-the-DB-s-of-Firefox-3-0-User-Profile.cmd
>consol.txt 2>&1
rem
set Path_to_User_Profile_SQLite_Files=P:\SQLite-Test-DBs
rem ************************
rem create the test bases
rem ************************
del %Path_to_User_Profile_SQLite_Files%\*.sqlite
copy S:\mof-profile-save\mof-3000-2008-06-26-00-15\*.sqlite
%Path_to_User_Profile_SQLite_Files%\*.*
rem Does not work, the complete path to the DBs has to be defined
direct not relative!!
rem Set USER_HOME=P:\
set SQLite_DB_01=%Path_to_User_Profile_SQLite_Files%\content-prefs.sqlite
set SQLite_DB_02=%Path_to_User_Profile_SQLite_Files%\cookies.sqlite
set SQLite_DB_03=%Path_to_User_Profile_SQLite_Files%\downloads.sqlite
set SQLite_DB_04=%Path_to_User_Profile_SQLite_Files%\formhistory.sqlite
set SQLite_DB_05=%Path_to_User_Profile_SQLite_Files%\permissions.sqlite
set SQLite_DB_06=%Path_to_User_Profile_SQLite_Files%\places.sqlite
set SQLite_DB_07=%Path_to_User_Profile_SQLite_Files%\search.sqlite
set SQLite_DB_08=%Path_to_User_Profile_SQLite_Files%\urlclassifier3.sqlite
P:
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_01% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_02% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_03% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_04% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_05% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_06% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_07% .exit
call s:\sqlite342.cmd -batch -init s:\sql-in.txt %SQLite_DB_08% .exit
s:
=====================================================
consol output for the call for urlclassifier3.sqlite
=====================================================
P:\]call s:\sqlite342.cmd -batch -init s:\sql-in.txt
P:\SQLite-Test-DBs\urlclassifier3.sqlite .exit
[P:\]rem mode 80,50
[P:\]rem
[P:\]rem SET LIBPATHSTRICT=T
[P:\]rem
[P:\]SET BEGINLIBPATH=S:\download\os2\SQLite\V3-4-2\sqlite-3.4.2\dll;
[P:\]rem
[P:\]S:\download\os2\SQLite\V3-4-2\sqlite-3.4.2\bin\SQLite3.exe -batch
-init s:\sql-in.txt P:\SQLite-Test-DBs\urlclassifier3.sqlite .exit
-- Loading resources from s:\sql-in.txt
PRAGMA legacy_file_format;
1
PRAGMA temp_store_directory;
PRAGMA cache_size;
2000
PRAGMA database_list;
0|main|P:\SQLite-Test-DBs\urlclassifier3.sqlite
--
PRAGMA main.freelist_count;
1451
--
vacuum;
--
PRAGMA main.freelist_count;
0
.quit
[P:\]s:
=== profile from first sessiones with FF 3.0, without any vacuum
executed on the profile
Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415
Verzeichnis von P:\SQLite-Test-DBs
7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..
20.06.08 21.49 7168 0 content-prefs.sqlite
26.06.08 0.16 20480 0 cookies.sqlite
25.06.08 14.31 12288 0 downloads.sqlite
25.06.08 17.51 9216 0 formhistory.sqlite
20.06.08 13.16 2048 0 permissions.sqlite
26.06.08 0.16 3948544 0 places.sqlite
20.06.08 12.26 2048 0 search.sqlite
26.06.08 0.15 53800960 0 urlclassifier3.sqlite
10 Datei(en) 57802752 Byte belegt
1050610 K Byte frei
[S:\]
======================= after the vacumm
[P:\SQLite-Test-DBs]dir P:\SQLite-Test-DBs\*.*
Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415
Verzeichnis von P:\SQLite-Test-DBs
7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..
7.07.08 12.30 7168 0 content-prefs.sqlite
7.07.08 12.30 18432 0 cookies.sqlite
7.07.08 12.30 10240 0 downloads.sqlite
7.07.08 12.30 9216 0 formhistory.sqlite
7.07.08 12.30 2048 0 permissions.sqlite
7.07.08 12.30 3661824 0 places.sqlite
7.07.08 12.30 2048 0 search.sqlite
7.07.08 12.31 34660352 0 urlclassifier3.sqlite
10 Datei(en) 38371328 Byte belegt
1070041 K Byte frei
[P:\SQLite-Test-DBs]
It does look okay!!
Now here are the updated sample code to 3.5.9:
=============================================
======================
File: s:\SQLite359.cmd
======================
rem mode 80,50
rem
SET LIBPATHSTRICT=T
rem
SET BEGINLIBPATH=S:\download\os2\SQLite\V3-5-9;S:\download\os2\libc063;
rem
S:\download\os2\SQLite\V3-5-9\SQLite3.exe %1 %2 %3 %4 %5 %6
rem 2008-07-07 11.00 V1.0 Rainer D. Stroebel
rem 2008-07.07 12.15 V1.0.1 SQLite342.cmd --> SQLite359,cmd
rem
rem Test the command file:
rem
rem S:\SQLite-VACUUM-the-DB-s-of-Firefox-3-0-User-Profile.cmd
>consol.txt 2>&1
rem
set Path_to_User_Profile_SQLite_Files=P:\SQLite-Test-DBs
set SQLite-CMD=s:\SQLite359.cmd
rem ************************
rem create the test bases
rem ************************
del %Path_to_User_Profile_SQLite_Files%\*.sqlite
copy S:\mof-profile-save\mof-3000-2008-06-26-00-15\*.sqlite
%Path_to_User_Profile_SQLite_Files%\*.*
rem Does not work, the complete path to the DBs has to be defined
direct not relative!!
rem Set USER_HOME=P:\
set SQLite_DB_01=%Path_to_User_Profile_SQLite_Files%\content-prefs.sqlite
set SQLite_DB_02=%Path_to_User_Profile_SQLite_Files%\cookies.sqlite
set SQLite_DB_03=%Path_to_User_Profile_SQLite_Files%\downloads.sqlite
set SQLite_DB_04=%Path_to_User_Profile_SQLite_Files%\formhistory.sqlite
set SQLite_DB_05=%Path_to_User_Profile_SQLite_Files%\permissions.sqlite
set SQLite_DB_06=%Path_to_User_Profile_SQLite_Files%\places.sqlite
set SQLite_DB_07=%Path_to_User_Profile_SQLite_Files%\search.sqlite
set SQLite_DB_08=%Path_to_User_Profile_SQLite_Files%\urlclassifier3.sqlite
dir %Path_to_User_Profile_SQLite_Files%\*.*
P:
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_01% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_02% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_03% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_04% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_05% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_06% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_07% .exit
call %SQLite-CMD% -batch -init s:\sql-in.txt %SQLite_DB_08% .exit
dir %Path_to_User_Profile_SQLite_Files%\*.*
s:
>
> I have finally packaged SQLite 3.5.9, it's now on Hobbes. (Currently in
> /pub/imcoming, should be in /pub/os2/dev/database/ soon.) That should
> be fine to work with the Firefox files and script whatever you want to
> do. But don't expect support from me on that! I just maintain the OS/2
> version but don't have time to actually use it.
Hello Peter,
the new port does work better than old 3.4.2.
Please have a look at the realisation of the
PRAGMA temp_store_directory;
at/with/ in ? the port.
excerpt from sqlite doc:
======================================================================
PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory-name';
Query or change the setting of the "temp_store_directory" - the
directory where files used for storing temporary tables and indices are
kept. This setting lasts for the duration of the current connection only
and resets to its default value for each new connection opened.
When the temp_store_directory setting is changed, all existing
temporary tables, indices, triggers, and viewers are immediately
deleted. In practice, temp_store_directory should be set immediately
after the database is opened.
The value directory-name should be enclosed in single quotes. To
revert the directory to the default, set the directory-name to an empty
string, e.g., PRAGMA temp_store_directory = ''. An error is raised if
directory-name is not found or is not writable.
The default directory for temporary files depends on the OS. For
Unix/Linux/OSX, the default is the is the first writable directory found
in the list of: /var/tmp, /usr/tmp, /tmp, and current-directory. For
Windows NT, the default directory is determined by Windows, generally
C:\Documents and Settings\user-name\Local Settings\Temp\. Temporary
files created by SQLite are unlinked immediately after opening, so that
the operating system can automatically delete the files when the SQLite
process exits. Thus, temporary files are not normally visible through ls
or dir commands.
============================================================================
It does not use the OS/2 default dir value of env TMP
Example:
[P:\SQLite-Test-DBs]S:\download\os2\SQLite\V3-5-9\SQLite3.exe -batch
-init s:\sql-in.txt P:\SQLite-Test-DBs\urlclassifier3.sqlite .exit
-- Loading resources from s:\sql-in.txt
PRAGMA legacy_file_format;
1
PRAGMA temp_store_directory;
PRAGMA cache_size;
2000
PRAGMA database_list;
0|main|P:\SQLite-Test-DBs\urlclassifier3.sqlite
--
PRAGMA main.freelist_count;
1451
--
vacuum;
--
PRAGMA main.freelist_count;
0
.quit
[P:\SQLite-Test-DBs]
[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe -version -init
sql-in-test.txt p:\SQLite-Test-DBs\dow
nload.sqlite
-- Loading resources from sql-in-test.txt
PRAGMA legacy_file_format;
1
PRAGMA temp_store_directory;
PRAGMA temp_store_directory='P:\TMP';
PRAGMA temp_store_directory;
P:\TMP
PRAGMA cache_size;
2000
PRAGMA database_list;
0|main|P:\SQLite-Test-DBs\download.sqlite
--
PRAGMA main.freelist_count;
0
--
vacuum;
--
PRAGMA main.freelist_count;
0
.quit
3.5.9
[S:\]
Problem is minor;
Inital value is not set in OS/2 port
> It does not use the OS/2 default dir value of env TMP
What makes oyu believe that ?
--
Ruediger "Rudi" Ihle [S&T Systemtechnik GmbH, Germany]
http://www.s-t.de
Please remove all characters left of the "R" in my email address
Hallo Ruediger,
PRAGMA temp_store_directory;
PRAGMA temp_store_directory='P:\TMP';
PRAGMA temp_store_directory;
P:\TMP
The first
PRAGMA temp_store_directory;
does not replay with an value of the OS/2 env variable:
it reply no value set!!
additional data to the test:
============================
Run an dir command during execution:
no sqlites file in P:\TMP
but found a journal file in the DB driectory:
Should this jounnal file not be located in the tmp dir?
OS/2 Strg+Esc = Fensterliste HELP = Hilfe
[L:\]dir P:\sqlite-test-DBs\*.*
Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415
Verzeichnis von P:\sqlite-test-DBs
7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..
7.07.08 13.38 7168 0 content-prefs.sqlite
7.07.08 13.38 18432 0 cookies.sqlite
7.07.08 13.38 10240 0 downloads.sqlite
7.07.08 13.38 9216 0 formhistory.sqlite
7.07.08 13.38 2048 0 permissions.sqlite
7.07.08 13.38 3661824 0 places.sqlite
7.07.08 13.38 2048 0 search.sqlite
7.07.08 13.39 34660352 0 urlclassifier3.sqlite
7.07.08 13.53 32822904 0 urlclassifier3.sqlite-journal
11 Datei(en) 71194232 Byte belegt
1002013 K Byte frei
[L:\]dir P:\sqlite-test-DBs\*.*
Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415
Verzeichnis von P:\sqlite-test-DBs
7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..
7.07.08 13.38 7168 0 content-prefs.sqlite
7.07.08 13.38 18432 0 cookies.sqlite
7.07.08 13.38 10240 0 downloads.sqlite
7.07.08 13.38 9216 0 formhistory.sqlite
7.07.08 13.38 2048 0 permissions.sqlite
7.07.08 13.38 3661824 0 places.sqlite
7.07.08 13.38 2048 0 search.sqlite
7.07.08 13.39 34660352 0 urlclassifier3.sqlite
7.07.08 13.53 34732168 0 urlclassifier3.sqlite-journal
11 Datei(en) 73103496 Byte belegt
1000428 K Byte frei
[L:\]dir P:\sqlite-test-DBs\*.*
Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415
Verzeichnis von P:\sqlite-test-DBs
7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..
7.07.08 13.38 7168 0 content-prefs.sqlite
7.07.08 13.38 18432 0 cookies.sqlite
7.07.08 13.38 10240 0 downloads.sqlite
7.07.08 13.38 9216 0 formhistory.sqlite
7.07.08 13.38 2048 0 permissions.sqlite
7.07.08 13.38 3661824 0 places.sqlite
7.07.08 13.38 2048 0 search.sqlite
7.07.08 13.53 34660352 0 urlclassifier3.sqlite
10 Datei(en) 38371328 Byte belegt
1069891 K Byte frei
[L:\]dir P:\sqlite-test-DBs\*.*
Datenträger, Laufwerk P, hat den Namen VL_P_HPFS_T.
Datenträgernummer ist A9CF:4415
Verzeichnis von P:\sqlite-test-DBs
7.07.08 10.22 <DIR> 0 .
7.07.08 10.22 <DIR> 0 ..
7.07.08 13.38 7168 0 content-prefs.sqlite
7.07.08 13.38 18432 0 cookies.sqlite
7.07.08 13.38 10240 0 downloads.sqlite
7.07.08 13.38 9216 0 formhistory.sqlite
7.07.08 13.38 2048 0 permissions.sqlite
7.07.08 13.38 3661824 0 places.sqlite
7.07.08 13.38 2048 0 search.sqlite
7.07.08 13.53 34660352 0 urlclassifier3.sqlite
10 Datei(en) 38371328 Byte belegt
1069891 K Byte frei
[L:\]
just to ducment it more clearly:
here ist the input file:
-- ***********************************
-- File S:\sql-in-test.txt
-- SQL Statemets for Vacuum a Database
-- ***********************************
.echo on
PRAGMA legacy_file_format;
PRAGMA temp_store_directory;
PRAGMA temp_store_directory='P:\TMP';
PRAGMA temp_store_directory;
PRAGMA cache_size;
PRAGMA database_list;
--
PRAGMA main.freelist_count;
--
vacuum;
--
PRAGMA main.freelist_count;
.quit
Please compare it to the documented results
> The first
>
> PRAGMA temp_store_directory;
>
> does not replay with an value of the OS/2 env variable:
>
> it reply no value set!!
If temp_store_directory is not explicitely set by a PRAGMA
directive, it is NULL. What you see is the normal behavior.
Not just on OS/2, but on any platform SQLite is running on.
However, I just discovered that the OS/2 implementation will
ignore temp_store_directory completely :-(. That's is really
a bug which we should fix eventually.
> no sqlites file in P:\TMP
No wonder. temp_storage_directory is used for temporary tables. Probably
you didn't do any operations that created such tables or didn't look at
the directory at the "right" time.
Also it appears that temp_storage_directory is ignored in OS/2 anyway...
> Should this jounnal file not be located in the tmp dir?
No. From th docs:
The rollback journal is always located in the same directory as the
database
file and has the same name as the database file except with the 8
characters
"-journal" appended.
Note, that a journal is not a temporary table. So even if we fix the
temp_storage_directory thing, the journal would still be where it is
now. However, on some platforms the file might get created with the
"hidden" attribute so that users normally don't see it ;-)
> However, I just discovered that the OS/2 implementation will
> ignore temp_store_directory completely :-(. That's is really
> a bug which we should fix eventually.
Thanks for the hint. I have found the missing lines.
--
Please Enhanced OS/2 builds of Mozilla applications
reply in http://pmw-warpzilla.sf.net/
newsgroup
Please have a look about the paragraph with the default value.
The question is: Witch value is the default value for the OS/2 port?
Null? Does make no sense to me.
I just rereading the doc again.
I do not think, the -journal file created by the vacuum command is a
real journal file.
From the doc of the vaccum command.
The VACUUM command cleans the main database by copying its contents to a
temporary database file and reloading the original database file from
the copy. This eliminates free pages, aligns table data to be
contiguous, and otherwise cleans up the database file structure.
Does the doc is syncron to the current implementation?
I think, the physical implementation of the Vacuum command is just copying
the datatbase to an database with the journal suffix.
After succesfully done this, the old file is deleted and the
-journal file is renamed to the original file name.
If the operation failed, the "journal" file is just deleted.
The rename methode requries: the file has to be in the same directory.
If a temp dir is used, additional I/O would be required.
If you have a big DB, that could be a performace grain,
if you have the temp file on a different phyical drive.
Even if you have to copy the file back.
SQLit does not expect to run onf configurations with 2 physical drives,
so the rename methode is implemented.
What do I learn:
================
The jorunal files are part of the same dir as the DB.
The vacuum command requieres free disk space greater or equal the size
of the DB to be sure. ( on the same drive )
The actual required size is the size of the new compacted DB.
> Thanks for the hint. I have found the missing lines.
Be sure to have a look at the character set stuff. I suspect,
that the temp_store_directory is already UTF8 while the
environment variables are not...
NULL is the default value for temp_store_directory on any platform.
The meaning of that is: "The user has not specified something
special, so let's use a platform-specific value that is hard-
coded into the library itself". This platform-specific default
is *NOT* reported back via "PRAGMA temp_store_directory".
In case of OS/2 the environment variables "TEMP", "TMP" and
"TMPDIR" are checked (in that order). If none of them is defined,
the root directory of the current drive is assumed to be the
directory for temporary tables.
Okay, that information is very valueable to understand the working of
the port :-)
PRAGMA temp_store_directory
The default directory for temporary files for the OS/2 port is set
according the following rules:
The environment variables "TEMP", "TMP" and "TMPDIR" are checked (in
that order). If none of them is defined, the root directory of the
current drive is assumed to be the directory for temporary tables
NULL is the default value for temp_store_directory on any platform.
If the value ist NULL, the system uses the default directory.
Suggestion:
Polish my english and add the information to the readme of the port and
may be to the SQLite Documenation.
I try to be conform to the text on the syntax description
>
> I have finally packaged SQLite 3.5.9, it's now on Hobbes. (Currently in
> /pub/imcoming, should be in /pub/os2/dev/database/ soon.) That should
> be fine to work with the Firefox files and script whatever you want to
> do. But don't expect support from me on that! I just maintain the OS/2
> version but don't have time to actually use it.
Hello Peter, hello Rüdiger,
thanks for the solving of the PRAGMA temp_store_directory Question.
From my test with the 3.4.2 port I learn the Sqlite3 does not use
the current directory and does not user the USER_HOME.
I had to address the DB with the "Full" Path.
This behaviour change with new port positivly.
Now the current directory is used,
The port does not user the env USER_HOME.
This is different to the Linux port.
======================================================
Can following info be add to the Readme of the port?
======================================================
SQLite3.exe [OPTIONS] FILENAME [SQL]
FILENAME
If no path with FILENAME is specified, the OS/2 port uses the current
directory.
It doe not make use of USER_HOME env. variable
==End of text for the readme =========================
Example of the system behaviour:
[S:\]del download.sqlite
[S:\]Set USER_HOME=P:\SQLite-Test-DBs
[S:\]sqlite359 download.sqlite
[S:\]rem mode 80,50
[S:\]rem
[S:\]SET LIBPATHSTRICT=T
[S:\]rem
[S:\]SET BEGINLIBPATH=S:\download\os2\SQLite\V3-5-9;S:\download\os2\libc063;
[S:\]rem
[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe download.sqlite
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .database
seq name file
--- ---------------
----------------------------------------------------------
0 main S:\download.sqlite
sqlite> .quit
[S:\]
A new database "download.sqlite" is created in the current directory
(looking at the source of 3.5.7 since that is what I have here)
The only references to the home directory are in shell.c (not counting
tk/tcl stuff) For OS/2 it tries %USERPROFILE% then %HOME% then falls
back on C:\
I don't see any reference to USER_HOME
Dave
> From my test with the 3.4.2 port I learn the Sqlite3 does not use
> the current directory and does not user the USER_HOME.
It _does_ use the current directory, unless you give a path. That is
expected behavior for any application.
> I had to address the DB with the "Full" Path.
>
> This behaviour change with new port positivly.
>
> Now the current directory is used,
>
> The port does not user the env USER_HOME.
>
> This is different to the Linux port.
In what way? It ignores USER_HOME just like on Linux.
> ======================================================
> Can following info be add to the Readme of the port?
> ======================================================
>
> SQLite3.exe [OPTIONS] FILENAME [SQL]
>
> FILENAME
>
> If no path with FILENAME is specified, the OS/2 port uses the current
> directory.
> It doe not make use of USER_HOME env. variable
Do you expect it to write %USER_HOME%\FILENAME instead of FILENAME?
Why?
I do find the current behavour okay!
Based on my testing with 3.4.2 port, current directory does not work on
that port.
I had to address the File with "full" path to recognise the DB.
Done some google and found an hint, Linux works with USER_HOME value
for SQLite3. Test it with 3.4.2 - it does not work.
Now the new port does work with the current dir as default.
That is big improvement to the 3.4.2 port!
I do see no need for the USER_HOME on an OS/2 Port.
Just to document the fact I suggest the Readme addition.
> Based on my testing with 3.4.2 port, current directory does not work on
> that port.
Maybe. But we are talking about 3.5.9.
> Done some google and found an hint, Linux works with USER_HOME value
> for SQLite3.
Done some source code search and found absolutely no reference to
USER_HOME. Not on Linux and not on any other supported platform.
As David stated: The shell program (not the engine itself) is
investigating %USERPROFILE%, %HOME% and the combination of
%HOMEDRIVE% and %HOMEPATH% to identfy the user's home directory.
If none of the above is defined, it falls back to C:\.
If the "-init" parameter is not present on the command line, the
shell tries to load and execute a file named ".sqliterc" from the
home directory. When running in interactive mode, the home directory
is also used to maintain a command history (file ".sqlite_history")
on some platforms. But I don't think we have that enabled in OS/2...
In no case the home directory is used to locate a database file.
> Does the doc is syncron to the current implementation?
I don't know, but isn't documentation always behind ;-).
> I think, the physical implementation of the Vacuum command is just copying
> the datatbase to an database with the journal suffix.
>
> After succesfully done this, the old file is deleted and the
> -journal file is renamed to the original file name.
>
> If the operation failed, the "journal" file is just deleted.
I don't fully understand the details, but I can assure you it's
definitively more complex than this...
>
> Howto to shrink / vacuum / compact the SQLite files.
>
>
> 1. Manual Procedure
>
-----> snip
>
> 2. Automatic Procdures to Vacuum the SQLite file a request
>
> I like to do this via a command file or program execution on request.
>
Automatic procedure to Vacuum the SQLite files on request
Status of the implementation: ( as of 2008-07-08 20.00 Rainer D. Stroebel )
The prerequisites for the solution are meet. Peter Weilbacher has ported
the current SQLite 3.5.9 version and released the package on Hobbes.
Thanks - a happy user :-)
The procedure is implemented on my system and tested.
A Lock (Semaphore) is realised to prevent the start of the browser
during maintenance
and the start of the maintenance during an active browser session.
n generations of Backup of the Profile are automaticly maintained
N = 4 is realised in code, can be adapted by adding two line of code
for each additional generation
n generations of logs of a maintenance session are retaining
N = 3 are currently administrated
Todo
More clean up of the code, write a installation guide and publish the
solution.
> Subject: FF 3.0 Perforance Issue: Vaccum the SQLite Files in the Profile
What produced this malformed Subject header? Was it by design that it has a
trailing CRLF pair or is this a bug in the posting agent
User-Agent: Mozilla/5.0 (OS/2; U; Warp 4.5; de-AT; rv:1.7.7) Gecko/20050425
It's showing up in Pronews with half the header included in the body of the
article because the subject line is followed by a blank one.
--
Trevor Hemsley, Brighton, UK
Trevor dot Hemsley at ntlworld dot com
Hello Trevor,
may be the trailing CRLR signe had come to the header field of the
message by pasting the line to the field.
Mozilla 1.7.7 does display the thread normally.
>> User-Agent: Mozilla/5.0 (OS/2; U; Warp 4.5; de-AT; rv:1.7.7)
>> Gecko/20050425
>
> Mozilla 1.7.7 does display the thread normally.
May I ask why you are using a version that is more than 3 years old?
Yes, this actually looks like a bug in Pronews from what I can see. The subject
line as retrieved from Giganews has a single blank on the next line and this
should really be treated as a continuation of the subject line. Pronews
misidentifies it as an "end of headers" line when it isn't.
Just useing the news reader function,
the last Mozilla version before starting with Firefox.
Your are right, this component/function does require an update.
It is currently a low priortiy project on my angenda.
Do you see any reason to push it up?
Requirements: Execution Time and HDD Disk Space
Current Size:
Total size of my Firefox 3.0 User Profile is about 56 MB
Size after Vaccum function
2008-07-09 12.02 4603904 0 places.sqlite
2008-07-08 12.02 30810112 0 urlclassifier3.sqlite
Performance on a Thinkpad T23 1.13 GHz - HPFS :
Start of Job Systemzeit: 12.02.13,48
End of Job Systemzeit: 12.03.00,51
Execution Time of Job : 00.00.47,03
The execution time is the "empty" execution time.
This is done on a well physical organized, compacted SQLite DB s.
The vacuum job has been run for testing of the Vacuum and Backup
Function of the procedure several times to day.
Disk requirement of the Procedure:
1. Temporary working space for the vacuum function
on the same drive as the profile
max size from any of SQLite files =
about 31 MB
2. Disk space for 4 generations of User Profile Backup
56 MB x 4 = 224 MB
Name created for the the Project / Package :-)
Firefox 3.0 User Profile Save and SQLite Vacuum UPSSV
Not sure what newsgroups you are reading but if it's more than this one
then you should be aware that Mozilla 1.7.7 has probably about 50
critical security problems in the MailNews area that are all fixed in
the latest Thunderbird 2.0.0.x and SeaMonkey 1.1.x releases.
Thanks for the hint.
Moved the Thunderbird installation up on my to do list.
Okay, lets talk about the "shell" SQLite3 and not about the engine
What does the values %USERPROFILE%, %HOME%, %HOMEDRIVE% and %HOMEPATH%
and Drive "C" are relevant for the Current OS/2 port.
I just have an example:
My environment:
---------------
1. My installation does not have a C drive :-)
2. The shell SQLite3.exe is found by searching the Path env.
3. The DLL are found via the BeginLibDef setting.
4. The SQLite3.exe does work, it displays the version no according the
-version option.
5. The File form the -init is not found a the example.
6. The DPath does give access to the file, the shell does not evaluate
the DPath.
Test call and output:
---------------------
[S:\MOF-3000]SQLite3.exe -bail -batch -version -init
UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\content-prefs.sqlite
3.5.9
[S:\MOF-3000]
additional test cases:
case 0: working as expected, -init filename
---------------------------------------------
result: The file is found, addressed with "absolute" path
[S:\MOF-3000]SQLite3.exe -bail -batch -version -init
s:\UPSSV-v-1-0\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\downloads.sqlite
-- Loading resources from
s:\UPSSV-v-1-0\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
PRAGMA legacy_file_format;
1
case 1: set USERPROFILE=S:\UPSSV-V-1-0
---------------------------------------
result: -init filename filename in dir S:\UPSSV-V-1-0 not found
[S:\MOF-3000]set USERPROFILE=S:\UPSSV-V-1-0
[S:\MOF-3000]
[S:\MOF-3000]set USERPROFILE
USERPROFILE=S:\UPSSV-V-1-0
[S:\MOF-3000]
[S:\MOF-3000]rem
[S:\MOF-3000]SQLite3.exe -bail -batch -version -init
UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\content-prefs.sqlite
3.5.9
[S:\MOF-3000]
Case 2: set HOME=S:\UPSSV-V-1-0
----------------------------------
result: -init filename filename in dir S:\UPSSV-V-1-0 not found
[S:\MOF-3000]set HOME=S:\UPSSV-V-1-0
[S:\MOF-3000]
[S:\MOF-3000]set HOME
HOME=S:\UPSSV-V-1-0
[S:\MOF-3000]
[S:\MOF-3000]rem
[S:\MOF-3000]SQLite3.exe -bail -batch -version -init
UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\search.sqlite
3.5.9
Case 3: set current directory to the dir containing the File
-------------------------------------------------------------
restult: works
S:\MOF-3000]s:
[S:\MOF-3000]cd \UPSSV-V-1-0
[S:\UPSSV-V-1-0]rem
[S:\UPSSV-V-1-0]SQLite3.exe -bail -batch -version -init
UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
S:\MoF-3000\Mozilla\Firefox\Profiles\5eqjlbn3.default\content-prefs.sqlite
-- Loading resources from
UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
PRAGMA legacy_file_format;
1
conclusio:
Does it make sense for the OS/2 port to request the OS/2 DPath
functionality?
Additional test cases:
Using the sqlite3 shell command .read filename
------------------------------------
case 1. filename with absolute path
-------------------------------------
result: does not work --> "/" in path ignored
[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .help
.bail ON|OFF Stop after hitting an error. Default OFF
---> snip
.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.schema ?TABLE? Show the CREATE statements
---> snip
.width NUM NUM ... Set column widths for "column" mode
sqlite> .echo ON
sqlite> .read
s:\UPSSV-v-1-0\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
.read s:\UPSSV-v-1-0\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
can't open
"s:UPSSV-v-1-0UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL"
sqlite>
-------------------------------------------
case 2. filename found in current directory
--------------------------------------------
result: Works
[S:\upssv-v-1-0]S:\download\os2\SQLite\V3-5-9\SQLite3.exe
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .read UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
PRAGMA legacy_file_format;
1
PRAGMA temp_store_directory;
PRAGMA cache_size;
2000
PRAGMA database_list;
0|main|
PRAGMA main.freelist_count;
0
vacuum;
PRAGMA main.freelist_count;
0
.quit
sqlite>
> What does the values %USERPROFILE%, %HOME%, %HOMEDRIVE% and %HOMEPATH%
> and Drive "C" are relevant for the Current OS/2 port.
If you do *NOT* specify "-init filename", the shell tries to load
and execute a file named ".sqliterc" from what it thinks is the
user's home directory. In other words: you can have an init script,
that is executed automatically whenever SQLite3.exe is started.
That's it. Not more and not less.
> Using the sqlite3 shell command .read filename
> ...
> result: does not work --> "/" in path ignored
Wrong. "\" is handled in a special way, not "/".
You have two choices:
1.) use forward slashes as path separator (.read C:/yourpath/yourfile)
2.) use double backslash as path separator (.read
C:\\yourpath\\yourfile)
Test 1: use forward slashes as path separator
----------------------------------------------
[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .read
s:/upssv-v-1-0/UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
PRAGMA legacy_file_format;
1
PRAGMA temp_store_directory;
PRAGMA cache_size;
2000
PRAGMA database_list;
0|main|
PRAGMA main.freelist_count;
0
vacuum;
PRAGMA main.freelist_count;
0
.quit
sqlite>
Test 2: use double backslash as path separator
----------------------------------------------
[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .read
s:\\upssv-v-1-0\\UPSSV-Sub1-User-Profile-SQLite-init-Option-File.SQL
okay,
done a test with the ".sqliterc" file in the current directory
[S:\]type .sqliterc
.show
.quit
[S:\]sqlite359
[S:\]S:\download\os2\SQLite\V3-5-9\SQLite3.exe
SQLite version 3.5.9
Enter ".help" for instructions
sqlite>
.show is not executed
the test cases of the my previous posting show
%USERPROFILE%, %HOME% does not work
Do I do something wrong at the test cases?
> done a test with the ".sqliterc" file in the current directory
Why that ???
".sqliterc" has to be in the user's home directory. For OS/2
that means %HOME% or %USERPROFILE% or %HOMEDRIVE%\%HOMEPATH%.
The fallback to C:\ in case none of the above is defined doesn't
work, which is a bug in both OS/2 and Windows. However, it's
questionable anyway...
> On Tue, 8 Jul 2008 20:09:46 UTC in mozilla.dev.ports.os2, Rainer Stroebel
> <RainerxxS...@xxt-online.de> wrote:
>
> > may be the trailing CRLR signe had come to the header field of the
> > message by pasting the line to the field.
> >
> > Mozilla 1.7.7 does display the thread normally.
>
> Yes, this actually looks like a bug in Pronews from what I can see. The subject
> line as retrieved from Giganews has a single blank on the next line and this
> should really be treated as a continuation of the subject line. Pronews
> misidentifies it as an "end of headers" line when it isn't.
But turns out to be one of those fixes that spreads and spreads and hits a whole
bunch of stuff... :-(
I think it's now fixed and will try to get a build released sooner rather than
later but need to use it for a while and make sure this hasn't had knock-on
effects in other places.