How to optimize the sqlite files

Post your tutorials for using Pale Moon or performing related tasks here.
Note: Not for "how do I...?" Questions!
Forum rules
Tutorials and Howtos should only relate to Pale Moon, and not to third party applications. e.g.: Don't post a Howto for configuring your firewall.
If you have a question how to do something, you should use one of the support boards, not this board. It is meant for people to document and post instructions.
Locked
dark_moon

How to optimize the sqlite files

Post by dark_moon » 2012-05-13, 10:27

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
You need to remove the .txt !
(1.92 KiB) Downloaded 629 times
# Read more about sqlite files at wikipedia: English SSL, German SSL
# Mozilla Developer Network - The Places database

stravinsky

Re: How to optimize the sqlite files

Post by stravinsky » 2012-05-14, 04:52

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

cloudcomputingcw

Re: How to optimize the sqlite files

Post by cloudcomputingcw » 2012-05-14, 06:36

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

Re: How to optimize the sqlite files

Post by dark_moon » 2012-05-14, 15:45

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

stravinsky

Re: How to optimize the sqlite files

Post by stravinsky » 2012-05-22, 03:46

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

Re: How to optimize the sqlite files

Post by dark_moon » 2012-05-22, 16:00

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.

User avatar
Moonchild
Pale Moon guru
Pale Moon guru
Posts: 27432
Joined: 2011-08-28, 17:27
Location: 58°2'16"N 14°58'31"E
Contact:

Re: How to optimize the sqlite files

Post by Moonchild » 2012-05-22, 16:43

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.
"There will be times when the position you advocate, no matter how well framed and supported, will not be accepted by the public simply because you are who you are." -- Merrill Rose
Image

sIDcORK
Fanatic
Fanatic
Posts: 231
Joined: 2012-07-17, 22:27
Location: UK

Re: How to optimize the sqlite files

Post by sIDcORK » 2012-07-25, 13:15

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

Re: How to optimize the sqlite files

Post by stravinsky » 2012-07-25, 13:57

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
Fanatic
Fanatic
Posts: 231
Joined: 2012-07-17, 22:27
Location: UK

Re: How to optimize the sqlite files

Post by sIDcORK » 2012-07-25, 15:07

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

stravinsky

Re: How to optimize the sqlite files

Post by stravinsky » 2012-07-25, 16:04

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

Re: How to optimize the sqlite files

Post by Ryrynz » 2012-07-27, 06:51

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.

User avatar
Moonchild
Pale Moon guru
Pale Moon guru
Posts: 27432
Joined: 2011-08-28, 17:27
Location: 58°2'16"N 14°58'31"E
Contact:

Re: How to optimize the sqlite files

Post by Moonchild » 2012-07-27, 07:23

stravinsky wrote: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.
"There will be times when the position you advocate, no matter how well framed and supported, will not be accepted by the public simply because you are who you are." -- Merrill Rose
Image

stravinsky

Re: How to optimize the sqlite files

Post by stravinsky » 2012-07-27, 16:39

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.

User avatar
Moonchild
Pale Moon guru
Pale Moon guru
Posts: 27432
Joined: 2011-08-28, 17:27
Location: 58°2'16"N 14°58'31"E
Contact:

Re: How to optimize the sqlite files

Post by Moonchild » 2012-07-27, 21:24

stravinsky wrote:
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.
"There will be times when the position you advocate, no matter how well framed and supported, will not be accepted by the public simply because you are who you are." -- Merrill Rose
Image

stravinsky

Re: How to optimize the sqlite files

Post by stravinsky » 2012-07-28, 02:52

@ 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

Re: How to optimize the sqlite files

Post by stravinsky » 2012-07-28, 03:05

@ Moonchild :

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

dark_moon

Re: How to optimize the sqlite files

Post by dark_moon » 2012-07-28, 07:05

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

User avatar
Moonchild
Pale Moon guru
Pale Moon guru
Posts: 27432
Joined: 2011-08-28, 17:27
Location: 58°2'16"N 14°58'31"E
Contact:

Re: How to optimize the sqlite files

Post by Moonchild » 2012-07-28, 09:32

stravinsky wrote: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).
"There will be times when the position you advocate, no matter how well framed and supported, will not be accepted by the public simply because you are who you are." -- Merrill Rose
Image

Locked