huge places.sqlite

User Help for Mozilla Firefox
shoofy
Posts: 385
Joined: November 13th, 2004, 4:56 pm

huge places.sqlite

Post by shoofy »

I've recently been noticing some huge slowdowns when using Firefox, where it basically locks up almost every time I load a page and then it sits and chugs away for minutes at a time with very high disk IO. The awesomebar also frequently just doesn't do anything anymore. I realized that this was likely an issue with Places, so I looked, and sure enough my places.sqlite is about 170MB, which means that it's probably not being kept in memory.

I tried compacting it using the Sqlite Browser, but it only cut off about 2MB and didn't solve nay of the problems. I also tried backing up the places.sqlite file and clearing my history, but that also had minimal effects. I can't think of anything else that could be causing the file size to be that enormous. Is there any way to get this back to a reasonable size without losing all my history and other places information?
User avatar
dickvl
Posts: 54145
Joined: July 18th, 2005, 3:25 am

Re: huge places.sqlite

Post by dickvl »

If you delete (or rename to be able to undo the delete) places.sqlite then Firefox will rebuild the bookmarks from the most recent JSON backup in the bookmarkbackups folder. So check if there is a JSON backup. You will lose the History if you delete places.sqlite.

To be safe you should also make a HTML backup of the bookmarks and check that it has all the bookmarks by opening the HTML file in Firefox (File > Open File) before deleting places.sqlite.

See Profile Folder - Firefox
"Application Data" in XP and "AppData" in Vista are hidden folders

Show hidden files and folders
shoofy
Posts: 385
Joined: November 13th, 2004, 4:56 pm

Re: huge places.sqlite

Post by shoofy »

I'd rather not lose all my history though. Since the Awesomebar came around I haven't been bookmarking very much, since it's so easy to just pull things out of history.
whgoes
Guest

Re: huge places.sqlite

Post by whgoes »

If you set Firefox to keep less history, eventually some of the history will be marked as expired. Then you can vacuum the places.sqlite and it should shrink.
http://kb.mozillazine.org/Browser.history_expire_days

If you enjoy keeping that much history, though, how much memory does your computer have? For many computers, upgrading the memory is a viable option and not very expensive.
User avatar
dickvl
Posts: 54145
Joined: July 18th, 2005, 3:25 am

Re: huge places.sqlite

Post by dickvl »

In the Library you can copy sites from the history window and paste them into a bookmarks folder if you wish to keep them. That should work.

Then you can also make a (HTML) backup if you value the history that much.
shoofy
Posts: 385
Joined: November 13th, 2004, 4:56 pm

Re: huge places.sqlite

Post by shoofy »

That still doesn't really help, because what makes the Awesomebar awesome is how it learns what sites you are looking for when you type certain strings and it keeps track of how many times you have visited a site. Just making it a bookmark would lose all of that I'm guessing. Also, at least in theory, Firefox clears out history items that haven't been touched in a certain period of time (180 days by default). Making the sites bookmarks would keep them around forever and cause a huge amount of added bloat. I have a suspicion that the problem I'm seeing is that Firefox isn't actually clearing out old history items.
User avatar
alterna
Posts: 3993
Joined: January 16th, 2007, 8:27 am
Location: Big Apple

Re: huge places.sqlite

Post by alterna »

Perhaps not retaining the favicon data would reduce places.sqlite size.
"So it goes" - Kurt Vonnegut, Jr.
User avatar
dfoulkes
Posts: 22525
Joined: June 28th, 2008, 10:31 pm
Location: Mesquite, Nevada

Re: huge places.sqlite

Post by dfoulkes »

If you think FF is keeping stuff past 180 or older open up your bookmarks mgr, click History at top and if visit date isn't part of your list heading ...add it via the view/show columns option and then click on the visit date to see what you have....that is, if you haven't already done that.
As you can see she's (The CAT) always alert and on the prowl for Meoware !!
User avatar
dickvl
Posts: 54145
Joined: July 18th, 2005, 3:25 am

Re: huge places.sqlite

Post by dickvl »

You can check these prefs on the about:config page (open that page via the location bar like a web site).
browser.history_expire_days (MozillaZine KB) (180)
browser.history_expire_days_min (MozillaZine KB) (90)

browser.history_expire_sites (MozillaZine KB) (40000)
browser.history_expire_visits (MozillaZine KB) (20000)
shoofy
Posts: 385
Joined: November 13th, 2004, 4:56 pm

Re: huge places.sqlite

Post by shoofy »

whgoes wrote:If you set Firefox to keep less history, eventually some of the history will be marked as expired. Then you can vacuum the places.sqlite and it should shrink.
http://kb.mozillazine.org/Browser.history_expire_days

If you enjoy keeping that much history, though, how much memory does your computer have? For many computers, upgrading the memory is a viable option and not very expensive.

I think that this may actually be the problem. I have browser.history_expire_days set at 180, but looking inside the places.sqlite, there are over 300000 entries in the moz_places table. There is no way I've gone to that many sites in the last 180 days. The moz_historyvisits table, which actually holds the dates of all the visits (and does in fact only go back 180 days) only has about 35000 entries. I also tried reducing the days and restarting Firefox, but it didn't change anything. It seems like Firefox isn't removing old entries from the places table when they expire from the history table.

What do you mean by "vacuum the places.sqlite?"

Perhaps not retaining the favicon data would reduce places.sqlite size.

How would I accomplish this?
shoofy
Posts: 385
Joined: November 13th, 2004, 4:56 pm

Re: huge places.sqlite

Post by shoofy »

dfoulkes wrote:If you think FF is keeping stuff past 180 or older open up your bookmarks mgr, click History at top and if visit date isn't part of your list heading ...add it via the view/show columns option and then click on the visit date to see what you have....that is, if you haven't already done that.

It's not showing items older than 180 days in the History window, but the database seems to be holding them in other places. Somehow there are 10 times too many entries in the moz_places table, unless I'm misunderstanding how the database works.
whgoes
Guest

Re: huge places.sqlite

Post by whgoes »

I thought you were already vacuuming since you mentioned the Sqlite Browser (I think it is the same as compacting?). It can also be done manually, with the sqlite command line utility.
Download sqlite-3_6_6_2.zip from http://www.sqlite.org/download.html, and then issue the command from the command line, with sqlite.exe in your path or copied to your profile directory
sqlite places.sqlite "vacuum;"
But I think compact in sqlite browser is the same command, anyone know for sure?

If you turn off favicons, that should stop them from going in the places.sqlite:
http://kb.mozillazine.org/Browser.chrome.favicons
http://kb.mozillazine.org/Browser.chrome.site_icons
User avatar
alterna
Posts: 3993
Joined: January 16th, 2007, 8:27 am
Location: Big Apple

Re: huge places.sqlite

Post by alterna »

shoofy wrote:
Perhaps not retaining the favicon data would reduce places.sqlite size.

How would I accomplish this?


To prevent favicons:

Type about:config into locationbar and go
scroll to settings
right click and either toggle or modify

browser.chrome.favicons false
browser.chrome.image_icons.max_size 0
browser.chrome.site_icons false

(Note: This eliminates ALL favicons. I have just text, and hide the default bookmark icons and tab favicons with userChrome.css. Very clean look)

To cleanse bookmarks of favicons:

export bookmarks as html
Library > Import and Backup > Export html to file
(Save a copy as backup in file)

File > Open File, browse to html > Open in browser

cleanse with this bookmarklet
(Create new bookmark, enter javascript in location)

Code: Select all

javascript:(function(){ var ls=document.getElementsByTagName('*'); for (var i=0; i<ls.length; i++) {l=ls[i]; l.removeAttribute('id'); l.removeAttribute('last_charset'); l.removeAttribute('icon'); l.removeAttribute('last_modified'); l.removeAttribute('last_visit'); l.removeAttribute('add_date'); l.removeAttribute('personal_toolbar_folder');} alert('Cleanup Complete - Save as Web Page, Complete')})();


After running bookmarklet
Save Page as web page complete

rename to bookmarks.html

Library > Import and Backup > Import Bookmarks html

Then delete old bookmarks, drag new toolbar items to toolbar and organize, etc
"So it goes" - Kurt Vonnegut, Jr.
shoofy
Posts: 385
Joined: November 13th, 2004, 4:56 pm

Re: huge places.sqlite

Post by shoofy »

Ok if it's the same as compacting then I have tried it. I'm now trying to see what happens if I just wipe out all the entries in the moz_favicons table and set the favicon_id in all the entries in moz_places to null and then compacting. I'll post back when it finishes. There were over 1000 favicons stored, but I don't know how much actual space that was taking up. I do like having favicons, so I don't think I want to disable them permanently, but maybe there's a better way.

How big is the places.sqlite file for other people? Am I wrong in assuming that something is wrong when it grows to 170MB?

By the way, all of the messing around I've been doing inside the places.sqlite is on a copy, so no need to worry about messing it up beyond repair.
User avatar
alterna
Posts: 3993
Joined: January 16th, 2007, 8:27 am
Location: Big Apple

Re: huge places.sqlite

Post by alterna »

I have 972 bookmarks, and a decent history, and have a places.sqlite of 4.8 MB
"So it goes" - Kurt Vonnegut, Jr.
Locked