How to optimize the sqlite files

[dark_moon]

What are sqlite files? Pale Moon and of cource Firefox save some date in a database (*.sqlite)
So after the time the files gone bigger and the database goes inefficienty.

The files are saved in the Pale Moon profile folder. The path under windows 7 is: C:\Users\username\AppData\Roaming\Moonchild Productions\Pale Moon\Profiles\random.default
username is your windows account name
random is a random profilename with numbers and letters. For example: bxdnoc7u

You can easy optimize one sqlite file in Pale Moon (places.sqlite - read more about this file at the Mozilla Knowledge Base) with one simple command:
Menu -> web developer -> error console (or press Strg+Shift+J).
Then paste this code in the field at top:
Components.classes["@mozilla.org/browser/nav-history-service;1"]
.getService(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSimpleSQL(
"VACUUM");
and click evaluate.

But if you want to optimize all sqlite files, then you need to download first the sqlite programm (You need Precompiled Binaries For Windows -> sqlite-shell-win32-x86-3071100.zip).
Then open a cmd (press Windows+R -> type cmd and press enter), paste this code and press enter:
for %a in (C:\Users\username\AppData\Roaming\Moonchild Productions\Pale Moon\Profiles\random.default\*.sqlite) do ("Complete path to the sqlite programm\sqlite3.exe" %a vacuum

You need to change the bold text!
But if this to hard for you, you can use my batch file, which make the optimization automatically. You only need to change the path for your Pale Moon and sqlite programm once. (Just rightclick on the batch and edit it):

PaleMoon Sqlite Optimization.cmd.txt

# Read more about sqlite files at wikipedia: English SSL, German SSL
# Mozilla Developer Network - The Places database

[stravinsky]

so what does this optimization do internally ?
i mean, does it deletes the sqlite DB?

[cloudcomputingcw]

Since version 3.0, Firefox changed the way it saves your bookmarks, history, cookies, passwords and so on - it now uses SQLite databases. This has some advantages but also disadvantages because over time, the databases are growing in size and become fragmented and this affects the time Firefox needs to start. But the SQLite databases can be optimized which improves the Firefox startup time and from my experience, also improves the awasomebar performance which becomes much more responsive and faster!

Read in detail click this link
http://www.webupd8.org/2009/07/increase ... ce-by.html

I also found faqs on google.com
Read this
http://web.utk.edu/~jplyon/sqlite/SQLit ... n_FAQ.html

[dark_moon]

The optimization rebuild the database and delete unused/ old things. So you have a better performance.

[stravinsky]

i just noticed a curious thing. I closed PM and performed the abovfe steps, and my places.sqlite file got reduced to 10MB from original size of 20MB.

But the moment i started PM, the places.sqlite got increased to 20MB again !
is it a bug?

[dark_moon]

Hmm maybe try this:

Copy your places.sqlite to another place, delete the one in the profile and see if pale moon generate the same places data.
Then this is a bug.

[Moonchild]

A database is initialized at a minimum size for performance reasons; that's not a bug

Running a VACUUM on it is a good thing though, since it compacts the actually used space. This is also why I recommend the "placescleaner" add-on (deletes old history, then vacuums).

BY THE WAY: No need to re-invent the wheel: http://www.crystalidea.com/speedyfox supports Pale Moon.

[sIDcORK]

There's SQlite Manager https://code.google.com/p/sqlite-manager/ if you don't need the full program.

The easiest way I've found to manage is with the Places Maintenance add-on https://addons.mozilla.org/en-US/firefo ... e/?src=api You access it in Add-ons > Places Maintenance > Options and you can several tasks besides Vacuum.

[stravinsky]

thats a good addon.

But IMO, the effects are more noticable with a HDD. i have a SSD and cant tell the difference before and after the 'cleaning'.

[sIDcORK]

I have seen articles saying that any sort of defragging is a bad idea on SSDs.

[stravinsky]

i dont know about "any sort of defrag" , but yes, the usual defragging is definitely a bad idea on SSD/flash storage. And you wont get any faster performance.

Regarding this addon or the SQlite utility,it does a whole lot more than defragging. it cleans up the data, rebuilds indexes, checks data integrity, and deletes empty spaces in the database.

[Ryrynz]

Typical defragmentation if looking squarely at the consolidation of data doesn't improve performance with SSDs as you would expect, however free space consolidation allows TRIM operations to perform more efficiently and improves wear leveling.

[Moonchild]

i dont know about "any sort of defrag" , but yes, the usual defragging is definitely a bad idea on SSD/flash storage. And you wont get any faster performance.

Also consider that "database fragmentation" is a whole different issue than "file system fragmentation". You can have a database in a contiguous file on disk that is really slow because of internal fragmentation and not being optimized. Regardless of the disk it is residing on, optimizing the database now and then (once a fortnight/month or so) is a good thing. Also should only take a second or 2, and won't cause unnecessary wear on SSDs.
I personally use PlacesCleaner for my regular maintenance on the history/bookmarks database. More extensive maintenance isn't needed all that often but Fasterfox Lite does a good job at it -- and it keeps me from having to manually mess with sqlite tools.

[stravinsky]

however free space consolidation allows TRIM operations to perform more efficiently

never defrag a SSD
it gives ZERO performance improvement, and uses up the limited writes on a SSD.

[Moonchild]

however free space consolidation allows TRIM operations to perform more efficiently

never defrag a SSD
it gives ZERO performance improvement, and uses up the limited writes on a SSD.

Optimizing SQLite files has NOTHING to do with file system fragmentation or defragmentation.
Optimizing sqlite and other database files is strongly recommended even if it's on an SSD.

[stravinsky]

@ OP : i am talking about the NTFS/FAT File-System defragmentation on SSD. The best you can do is to change power management and disable the "disk power-off on idle". Then leave the computer idle overnight. This will allow "idle garbage-collection" to occur.

Moonchild is talking about the internal SQLite file optimization.

[stravinsky]

@ Moonchild :

which is better of the two :
1. "Places cleaner" that you use
2. "places maintainance"

[dark_moon]

Please come back to topic. This is about sqlite files and not a discussion about SSD fragmentation.
Read moonchild's post: http://forum.palemoon.org/viewtopic.php?p=6144#p6144

[Moonchild]

which is better of the two :
1. "Places cleaner" that you use
2. "places maintainance"

I'm not familiar with places maintenance so I can't compare, but I find places cleaner to be simple to use, requires no maintenance itself, and simply does the job. I found no reason to look for a different tool
It offers removal of history entries for places you have visited less than "x" number of times (configurable) while not touching the ones visited in a user configurable time frame (to prevent cleaning up of too recent history you may want to use), before compacting and optimizing. It can be automated or you can be warned that "it's time to optimize", and then it takes one click to do it when you want it (only takes a few seconds usually, anyway).