[WebSQLDatabase] Adding a vacuum() call
Hi, We (Chromium) would like to add a vacuum() call on the Database object. The idea is to allow the SQL engine to vacuum/compress/clean-up/do some potentially expensive maintenance work on the respective database. In SQLite's case, in particular, the vacuum() call would issue a VACUUM command (which cannot run in a transaction, hence the web app developers do not have access to it currently), which would make SQLite compact the database by reusing the empty space on fragmented pages. We think this call is necessary, because fragmented databases could lead to apps prematurely reaching their quota limits. Letting the UAs deal with this problem is not always an option. For example, big apps like Gmail could always be "on" (loaded as soon as the browser comes up and closed only when the browser shuts down), which means the UAs would never have an opportunity to clean-up/compact the databases of those apps without blocking them. In these cases, we believe the apps should be responsible (and should have the opportunity) to clean up their own databases when they feel that it is the best time to do so. So we were wondering if it makes sense to add this call to the WebSQLDatabases spec too. The call would look like this: interface Database { // the methods and properties currently in the spec void vacuum(in optional SQLVacuumErrorCallback errorCallback, in optional SQLVoidCallback successCallback); }; [Callback=FunctionOnly, NoInterfaceObject] interface SQLVacuumErrorCallback { void handleEvent(in SQLError error); }; And the steps would be the following: 1. If vacuum() is called inside a transaction on the same database, jump to the "in case of error" steps below. 2. Queue a task to vacuum, compress, clean-up or do any other necessary maintenance work on the given database. 3. If the operation succeeds and a successCallback is provided, queue a task to invoke the successCallback. 4. If the clean-up operation fails in any way, jump to the "in case of error" steps below. In case of error: 1. In an errorCallback is provided, queue a task to invoke the errorCallback. Thanks, Dumi
[WebSQLDatabase] Adding a vacuum() call
(reposting from the right email account) hi joao, thanks for your comments! > I would argue about having something a bit more generic for naming like > "defragment()". > that's totally fine with me. > I don't see how the callbacks are useful though. Vacuum works > transparently, its effects are not visible, and what should the page do in > case of error ? > i was thinking of something like: db.defragment(errorCallback, successCallback); showAPrettyImageAndAskTheUserToWait(); function errorCallback(error) {} function successCallback() { getRidOfThePrettyImageAndRestartTheApp(); } just like you, i'm not sure if the error callback is useful at all, but i thought i'd add it to make the defragment() call look more like a transaction. maybe we don't need it. > Given that an operation like vacuum would be disk IO intensive, would it be > good to give the webpage control over a feature that can have significant > performance concerns ? > first, i think it's better to give big, always-on apps access to a potentially expensive clean-up call, than not give them any way at all to defragment their databases. second, the app which called defragment() is the only one that should notice the performance overhead (in practice, other web apps might notice it too because of slower IO, but this is no different from having an external, non-web app doing some intensive IO work and slowing down all web apps). and third, most apps will probably never need to use this call; it is mostly intended for apps with big databases that are always open on a user's PC. those apps would usually know when they can call defragment() without slowing down the user (for example, they could do it when the app is idle). > While computers benefit from good file IO performance, that's not quite > true in many mobile devices. So, the API would be more like an hint ? How > can the webpage know the level of fragmentation of the data file ? > on mobile devices i'm imagining the quotas and DBs are pretty small, in which case defragmenting databases probably wouldn't save much space anyway. so maybe those platforms should decide that they never need to do any kind of maintenance on their DBs and make defragment() a no-op? i don't think a webapp can figure out the level of fragmentation (maybe we need an API to expose some quota/DB size information to the webapp?). however, it could use other criteria to decide when to call defragment(): once every couple of weeks, when the system is idle, etc. > Sqlite supports incremental vacuum http://www.sqlite.org/pragma.html so > this kind of feature should be left for sqlite to handle for itself. > in addition to what jeremy said, having auto-vacuum on is going to add some performance overhead to every transaction. this is not acceptable to some apps. thanks, dumi
Re: [WebSQLDatabase] Adding a vacuum() call
On Fri, 05 Mar 2010 03:22:00 +0100, Dumitru Daniliuc wrote: Hi, We (Chromium) would like to add a vacuum() call on the Database object. [...] I would argue about having something a bit more generic for naming like "defragment()". I don't see how the callbacks are useful though. Vacuum works transparently, its effects are not visible, and what should the page do in case of error ? Given that an operation like vacuum would be disk IO intensive, would it be good to give the webpage control over a feature that can have significant performance concerns ? While computers benefit from good file IO performance, that's not quite true in many mobile devices. So, the API would be more like an hint ? How can the webpage know the level of fragmentation of the data file ? Sqlite supports incremental vacuum http://www.sqlite.org/pragma.html so this kind of feature should be left for sqlite to handle for itself.
Re: [WebSQLDatabase] Adding a vacuum() call
On Fri, Mar 5, 2010 at 2:32 AM, João Eiras wrote: > On Fri, 05 Mar 2010 03:22:00 +0100, Dumitru Daniliuc > wrote: > > Hi, >> >> We (Chromium) would like to add a vacuum() call on the Database object. >> > > [...] > > I would argue about having something a bit more generic for naming like > "defragment()". > > I don't see how the callbacks are useful though. Vacuum works > transparently, its effects are not visible, and what should the page do in > case of error ? > > Given that an operation like vacuum would be disk IO intensive, would it be > good to give the webpage control over a feature that can have significant > performance concerns ? > I'm sure a UA could always ignore it and/or vacuum whenever it wants...but the drawback is that if you start vacuuming and then the app tries to open the database, you might get some serious jank in the web app. > While computers benefit from good file IO performance, that's not quite > true in many mobile devices. So, the API would be more like an hint ? How > can the webpage know the level of fragmentation of the data file ? > > Sqlite supports incremental vacuum http://www.sqlite.org/pragma.html so > this kind of feature should be left for sqlite to handle for itself. > >From that page: """ Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse. """
Re: [WebSQLDatabase] Adding a vacuum() call
hi joao, thanks for your comments! > I would argue about having something a bit more generic for naming like > "defragment()". > that's totally fine with me. > I don't see how the callbacks are useful though. Vacuum works > transparently, its effects are not visible, and what should the page do in > case of error ? > i was thinking of something like: db.defragment(errorCallback, successCallback); showAPrettyImageAndAskTheUserToWait(); function errorCallback(error) {} function successCallback() { getRidOfThePrettyImageAndRestartTheApp(); } just like you, i'm not sure if the error callback is useful at all, but i thought i'd add it to make the defragment() call look more like a transaction. maybe we don't need it. > Given that an operation like vacuum would be disk IO intensive, would it be > good to give the webpage control over a feature that can have significant > performance concerns ? > first, i think it's better to give big, always-on apps access to a potentially expensive clean-up call, than not give them any way at all to defragment their databases. second, the app which called defragment() is the only one that should notice the performance overhead (in practice, other web apps might notice it too because of slower IO, but this is no different from having an external, non-web app doing some intensive IO work and slowing down all web apps). and third, most apps will probably never need to use this call; it is mostly intended for apps with big databases that are always open on a user's PC. those apps would usually know when they can call defragment() without slowing down the user (for example, they could do it when the app is idle). > While computers benefit from good file IO performance, that's not quite > true in many mobile devices. So, the API would be more like an hint ? How > can the webpage know the level of fragmentation of the data file ? > on mobile devices i'm imagining the quotas and DBs are pretty small, in which case defragmenting databases probably wouldn't save much space anyway. so maybe those platforms should decide that they never need to do any kind of maintenance on their DBs and make defragment() a no-op? i don't think a webapp can figure out the level of fragmentation (maybe we need an API to expose some quota/DB size information to the webapp?). however, it could use other criteria to decide when to call defragment(): once every couple of weeks, when the system is idle, etc. > Sqlite supports incremental vacuum http://www.sqlite.org/pragma.html so > this kind of feature should be left for sqlite to handle for itself. > in addition to what jeremy said, having auto-vacuum on is going to add some performance overhead to every transaction. this is not acceptable to some apps. thanks, dumi
Re: [WebSQLDatabase] Adding a vacuum() call
I don't see how the callbacks are useful though. Vacuum works transparently, its effects are not visible, and what should the page do in case of error ? i was thinking of something like: db.defragment(errorCallback, successCallback); showAPrettyImageAndAskTheUserToWait(); function errorCallback(error) {} function successCallback() { getRidOfThePrettyImageAndRestartTheApp(); } just like you, i'm not sure if the error callback is useful at all, but i thought i'd add it to make the defragment() call look more like a transaction. maybe we don't need it. True, but this is a kind of operation that could very well just run on the background, with a single optional callback when it's done (the webpage can't do anything if an error is detected anyway). The user agent would need to queue any subsequent transactions if a vacuum is running. I would consider it as an hint, and after all webpages that own references to the underlying data files are closed, would do a vacuum. So, if you have many tabs on gmail, and that a single gmail instance tries to do multiple vacuums, it would equiv to one single vacuum operation. Sqlite supports incremental vacuum http://www.sqlite.org/pragma.html so this kind of feature should be left for sqlite to handle for itself. in addition to what jeremy said, having auto-vacuum on is going to add some performance overhead to every transaction. this is not acceptable to some apps. I perused through the docs, and unfortunately auto-vaccum seems not like the ideal solution. -- João Eiras Core Developer, Opera Software ASA, http://www.opera.com/
Re: [WebSQLDatabase] Adding a vacuum() call
On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: > > I don't see how the callbacks are useful though. Vacuum works >>> transparently, its effects are not visible, and what should the page do >>> in >>> case of error ? >>> >>> >> i was thinking of something like: >> >> db.defragment(errorCallback, successCallback); >> showAPrettyImageAndAskTheUserToWait(); >> >> function errorCallback(error) {} >> function successCallback() { >> getRidOfThePrettyImageAndRestartTheApp(); >> } >> >> just like you, i'm not sure if the error callback is useful at all, but i >> thought i'd add it to make the defragment() call look more like a >> transaction. maybe we don't need it. >> >> > True, but this is a kind of operation that could very well just run on the > background, with a single optional callback when it's done (the webpage > can't do anything if an error is detected anyway). ok, so let's drop the errorCallback: vacuum([optional] successCallback); > The user agent would need to queue any subsequent transactions if a vacuum > is running. I would consider it as an hint, and after all webpages that own > references to the underlying data files are closed, would do a vacuum. So, > if you have many tabs on gmail, and that a single gmail instance tries to do > multiple vacuums, it would equiv to one single vacuum operation. what do we do if some databases are opened for the entire life of the browser? for example, i open my browser which has myfavoriteapp.com set as its homepage. myfavoriteapp.com immediately opens a DB, and i only close that app when i close the browser. when would the browser vacuum myfavoriteapp's DBs in this case? i think it's ok for the UA to vacuum some DBs automatically when it thinks it's a good time to do so; however, if a platform supports the vacuum/defrag call (i.e. if it doesn't treat it is a no-op), then i think a vacuum call coming from the app should be immediately scheduled (yes, the subsequent transactions would have to wait for the vacuuming to finish running). in some cases, the apps know better than the UA when to vacuum their DBs. by the way, we should probably agree on a name for this call. which one do you prefer? vacuum, defragment, defrag, something else? i don't have a strong opinion. dumi
Re: [WebSQLDatabase] Adding a vacuum() call
On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc wrote: > > > On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: > >> >> I don't see how the callbacks are useful though. Vacuum works transparently, its effects are not visible, and what should the page do in case of error ? >>> i was thinking of something like: >>> >>> db.defragment(errorCallback, successCallback); >>> showAPrettyImageAndAskTheUserToWait(); >>> >>> function errorCallback(error) {} >>> function successCallback() { >>> getRidOfThePrettyImageAndRestartTheApp(); >>> } >>> >>> just like you, i'm not sure if the error callback is useful at all, but i >>> thought i'd add it to make the defragment() call look more like a >>> transaction. maybe we don't need it. >>> >>> >> True, but this is a kind of operation that could very well just run on the >> background, with a single optional callback when it's done (the webpage >> can't do anything if an error is detected anyway). > > > ok, so let's drop the errorCallback: vacuum([optional] successCallback); > > >> The user agent would need to queue any subsequent transactions if a vacuum >> is running. I would consider it as an hint, and after all webpages that own >> references to the underlying data files are closed, would do a vacuum. So, >> if you have many tabs on gmail, and that a single gmail instance tries to do >> multiple vacuums, it would equiv to one single vacuum operation. > > > what do we do if some databases are opened for the entire life of the > browser? for example, i open my browser which has myfavoriteapp.com set as > its homepage. myfavoriteapp.com immediately opens a DB, and i only close > that app when i close the browser. when would the browser vacuum > myfavoriteapp's DBs in this case? > > i think it's ok for the UA to vacuum some DBs automatically when it thinks > it's a good time to do so; however, if a platform supports the vacuum/defrag > call (i.e. if it doesn't treat it is a no-op), then i think a vacuum call > coming from the app should be immediately scheduled (yes, the subsequent > transactions would have to wait for the vacuuming to finish running). in > some cases, the apps know better than the UA when to vacuum their DBs. > > by the way, we should probably agree on a name for this call. which one do > you prefer? vacuum, defragment, defrag, something else? i don't have a > strong opinion. > I think vacuum is fine since the spec is already tied to the SQLite SQL dialect. collectGarbage() is another possibility Go with whatever you think is most clear and accurate though. J
Re: [WebSQLDatabase] Adding a vacuum() call
joao, it looks like we mostly agree on this feature, so i was wondering if we could formally agree on a spec. here's what i propose: 1. name: vacuum. to the best of my knowledge, all current WebSQLDatabases implementations use SQLite, and in SQLite the command is called VACUUM. so it seems to me that we might as well call the new function vacuum(). what do you think? 2. spec: no need for an error callback. interface Database { // the methods and properties currently in the spec void vacuum(in optional SQLVoidCallback successCallback); }; 3. what the call should do: the purpose of this call is to allow apps to vacuum/compact/defragment/clean up their databases whenever they see fit. a call to vacuum() could take a considerable amount of time (especially on larger or highly fragmented databases); therefore, it is not recommended for web apps to call this method during periods of high activity. how to process a vacuum() call: 1. if the UA does not support this call (mobile browsers?), jump to step 3. 2. queue up a task to vacuum/compact/defragment/clean up the database. 3. if the task succeeds, and a success callback is provided, queue up a task to invoke the success callback; in all other cases (the task failed, or no success callback was provided), do nothing: proceed to the next task in the queue. does this seem acceptable? we (google engineers interested in this) feel that UAs should either not implement the vacuum() call, or they should respect it (rather than taking it as a hint). it is ok for UAs to keep track of things like system idleness or databases closing to do more vacuuming that the apps asked for, if they want to. however, we feel that a vacuum() request by an app should not be postponed, simply because sometimes apps know better than UAs when the best time to vacuum is (it might be nice to give apps more information on how fragmented their databases are, but that's a separate discussion). thanks, dumi 2010/3/9 Jeremy Orlow > On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc wrote: > >> >> >> On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: >> >>> >>> I don't see how the callbacks are useful though. Vacuum works > transparently, its effects are not visible, and what should the page do > in > case of error ? > > i was thinking of something like: db.defragment(errorCallback, successCallback); showAPrettyImageAndAskTheUserToWait(); function errorCallback(error) {} function successCallback() { getRidOfThePrettyImageAndRestartTheApp(); } just like you, i'm not sure if the error callback is useful at all, but i thought i'd add it to make the defragment() call look more like a transaction. maybe we don't need it. >>> True, but this is a kind of operation that could very well just run on >>> the background, with a single optional callback when it's done (the webpage >>> can't do anything if an error is detected anyway). >> >> >> ok, so let's drop the errorCallback: vacuum([optional] successCallback); >> >> >>> The user agent would need to queue any subsequent transactions if a >>> vacuum is running. I would consider it as an hint, and after all webpages >>> that own references to the underlying data files are closed, would do a >>> vacuum. So, if you have many tabs on gmail, and that a single gmail instance >>> tries to do multiple vacuums, it would equiv to one single vacuum operation. >> >> >> what do we do if some databases are opened for the entire life of the >> browser? for example, i open my browser which has myfavoriteapp.com set >> as its homepage. myfavoriteapp.com immediately opens a DB, and i only >> close that app when i close the browser. when would the browser vacuum >> myfavoriteapp's DBs in this case? >> >> i think it's ok for the UA to vacuum some DBs automatically when it thinks >> it's a good time to do so; however, if a platform supports the vacuum/defrag >> call (i.e. if it doesn't treat it is a no-op), then i think a vacuum call >> coming from the app should be immediately scheduled (yes, the subsequent >> transactions would have to wait for the vacuuming to finish running). in >> some cases, the apps know better than the UA when to vacuum their DBs. >> >> by the way, we should probably agree on a name for this call. which one do >> you prefer? vacuum, defragment, defrag, something else? i don't have a >> strong opinion. >> > > I think vacuum is fine since the spec is already tied to the SQLite SQL > dialect. > > collectGarbage() is another possibility > > Go with whatever you think is most clear and accurate though. > > J >
Re: [WebSQLDatabase] Adding a vacuum() call
On Fri, 12 Mar 2010 01:08:41 +0100, Dumitru Daniliuc wrote: joao, it looks like we mostly agree on this feature, so i was wondering if we could formally agree on a spec. here's what i propose: 1. name: vacuum. to the best of my knowledge, all current WebSQLDatabases implementations use SQLite, and in SQLite the command is called VACUUM. so it seems to me that we might as well call the new function vacuum(). what do you think? I'm fine with it but... 2. spec: no need for an error callback. interface Database { // the methods and properties currently in the spec void vacuum(in optional SQLVoidCallback successCallback); }; 3. what the call should do: the purpose of this call is to allow apps to vacuum/compact/defragment/clean up their databases whenever they see fit. a call to vacuum() could take a considerable amount of time (especially on larger or highly fragmented databases); therefore, it is not recommended for web apps to call this method during periods of high activity. how to process a vacuum() call: 1. if the UA does not support this call (mobile browsers?), jump to step 3. 2. queue up a task to vacuum/compact/defragment/clean up the database. 3. if the task succeeds, and a success callback is provided, queue up a task to invoke the success callback; in all other cases (the task failed, or no success callback was provided), do nothing: proceed to the next task in the queue. does this seem acceptable? we (google engineers interested in this) feel that UAs should either not implement the vacuum() call, or they should respect it (rather than taking it as a hint). it is ok for UAs to keep track of things like system idleness or databases closing to do more vacuuming that the apps asked for, if they want to. however, we feel that a vacuum() request by an app should not be postponed, simply because sometimes apps know better than UAs when the best time to vacuum is (it might be nice to give apps more information on how fragmented their databases are, but that's a separate discussion). I unfortunately don't agree with this last sentence :) Web pages do not have a way to know the level of fragmentation, and the last thing user wants is a web page calling vacuum() before each transaction, *just to be safe*, because of potential fragmentation. But the API is good I think. thanks, dumi
Re: [WebSQLDatabase] Adding a vacuum() call
Instead of calling back on success only, maybe call back on completion regardless of success or failure. This way the caller would know when the potentially lengthy operation was done, regardless of the outcome. 2010/3/11 Dumitru Daniliuc > joao, > > it looks like we mostly agree on this feature, so i was wondering if we > could formally agree on a spec. here's what i propose: > > 1. name: vacuum. to the best of my knowledge, all current WebSQLDatabases > implementations use SQLite, and in SQLite the command is called VACUUM. so > it seems to me that we might as well call the new function vacuum(). what do > you think? > > 2. spec: no need for an error callback. > > > interface Database { > // the methods and properties currently in the spec > void vacuum(in optional SQLVoidCallback successCallback); > }; > > 3. what the call should do: the purpose of this call is to allow apps to > vacuum/compact/defragment/clean up their databases whenever they see fit. a > call to vacuum() could take a considerable amount of time (especially on > larger or highly fragmented databases); therefore, it is not recommended for > web apps to call this method during periods of high activity. > > how to process a vacuum() call: > >1. if the UA does not support this call (mobile browsers?), jump to >step 3. >2. queue up a task to vacuum/compact/defragment/clean up the database. >3. if the task succeeds, and a success callback is provided, queue up a >task to invoke the success callback; in all other cases (the task failed, > or >no success callback was provided), do nothing: proceed to the next task in >the queue. > > does this seem acceptable? we (google engineers interested in this) feel > that UAs should either not implement the vacuum() call, or they should > respect it (rather than taking it as a hint). it is ok for UAs to keep track > of things like system idleness or databases closing to do more vacuuming > that the apps asked for, if they want to. however, we feel that a vacuum() > request by an app should not be postponed, simply because sometimes apps > know better than UAs when the best time to vacuum is (it might be nice to > give apps more information on how fragmented their databases are, but that's > a separate discussion). > > thanks, > dumi > > > 2010/3/9 Jeremy Orlow > > On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc wrote: >> >>> >>> >>> On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: >>> I don't see how the callbacks are useful though. Vacuum works >> transparently, its effects are not visible, and what should the page >> do in >> case of error ? >> >> > i was thinking of something like: > > db.defragment(errorCallback, successCallback); > showAPrettyImageAndAskTheUserToWait(); > > function errorCallback(error) {} > function successCallback() { > getRidOfThePrettyImageAndRestartTheApp(); > } > > just like you, i'm not sure if the error callback is useful at all, but > i > thought i'd add it to make the defragment() call look more like a > transaction. maybe we don't need it. > > True, but this is a kind of operation that could very well just run on the background, with a single optional callback when it's done (the webpage can't do anything if an error is detected anyway). >>> >>> >>> ok, so let's drop the errorCallback: vacuum([optional] successCallback); >>> >>> The user agent would need to queue any subsequent transactions if a vacuum is running. I would consider it as an hint, and after all webpages that own references to the underlying data files are closed, would do a vacuum. So, if you have many tabs on gmail, and that a single gmail instance tries to do multiple vacuums, it would equiv to one single vacuum operation. >>> >>> >>> what do we do if some databases are opened for the entire life of the >>> browser? for example, i open my browser which has myfavoriteapp.com set >>> as its homepage. myfavoriteapp.com immediately opens a DB, and i only >>> close that app when i close the browser. when would the browser vacuum >>> myfavoriteapp's DBs in this case? >>> >>> i think it's ok for the UA to vacuum some DBs automatically when it >>> thinks it's a good time to do so; however, if a platform supports the >>> vacuum/defrag call (i.e. if it doesn't treat it is a no-op), then i think a >>> vacuum call coming from the app should be immediately scheduled (yes, the >>> subsequent transactions would have to wait for the vacuuming to finish >>> running). in some cases, the apps know better than the UA when to vacuum >>> their DBs. >>> >>> by the way, we should probably agree on a name for this call. which one >>> do you prefer? vacuum, defragment, defrag, something else? i don't have a >>> strong opinion. >>> >> >> I think vacuum is fine since the spec is already tied to the SQLite SQL >> dialect. >> >> collectGarbage() is
Re: [WebSQLDatabase] Adding a vacuum() call
sounds good to me: interface Database { // the methods and properties currently in the spec void vacuum(in optional SQLVoidCallback completionCallback); }; "... upon completion, queue up a task to invoke the completionCallback, if one was provided." dumi On Thu, Mar 11, 2010 at 4:20 PM, Michael Nordman wrote: > Instead of calling back on success only, maybe call back on completion > regardless of success or failure. This way the caller would know when the > potentially lengthy operation was done, regardless of the outcome. > > 2010/3/11 Dumitru Daniliuc > > joao, >> >> it looks like we mostly agree on this feature, so i was wondering if we >> could formally agree on a spec. here's what i propose: >> >> 1. name: vacuum. to the best of my knowledge, all current WebSQLDatabases >> implementations use SQLite, and in SQLite the command is called VACUUM. so >> it seems to me that we might as well call the new function vacuum(). what do >> you think? >> >> 2. spec: no need for an error callback. >> >> >> interface Database { >> // the methods and properties currently in the spec >> void vacuum(in optional SQLVoidCallback successCallback); >> }; >> >> 3. what the call should do: the purpose of this call is to allow apps to >> vacuum/compact/defragment/clean up their databases whenever they see fit. a >> call to vacuum() could take a considerable amount of time (especially on >> larger or highly fragmented databases); therefore, it is not recommended for >> web apps to call this method during periods of high activity. >> >> how to process a vacuum() call: >> >>1. if the UA does not support this call (mobile browsers?), jump to >>step 3. >>2. queue up a task to vacuum/compact/defragment/clean up the database. >>3. if the task succeeds, and a success callback is provided, queue up >>a task to invoke the success callback; in all other cases (the task >> failed, >>or no success callback was provided), do nothing: proceed to the next task >>in the queue. >> >> does this seem acceptable? we (google engineers interested in this) feel >> that UAs should either not implement the vacuum() call, or they should >> respect it (rather than taking it as a hint). it is ok for UAs to keep track >> of things like system idleness or databases closing to do more vacuuming >> that the apps asked for, if they want to. however, we feel that a vacuum() >> request by an app should not be postponed, simply because sometimes apps >> know better than UAs when the best time to vacuum is (it might be nice to >> give apps more information on how fragmented their databases are, but that's >> a separate discussion). >> >> thanks, >> dumi >> >> >> 2010/3/9 Jeremy Orlow >> >> On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc wrote: >>> On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: > > I don't see how the callbacks are useful though. Vacuum works >>> transparently, its effects are not visible, and what should the page >>> do in >>> case of error ? >>> >>> >> i was thinking of something like: >> >> db.defragment(errorCallback, successCallback); >> showAPrettyImageAndAskTheUserToWait(); >> >> function errorCallback(error) {} >> function successCallback() { >> getRidOfThePrettyImageAndRestartTheApp(); >> } >> >> just like you, i'm not sure if the error callback is useful at all, >> but i >> thought i'd add it to make the defragment() call look more like a >> transaction. maybe we don't need it. >> >> > True, but this is a kind of operation that could very well just run on > the background, with a single optional callback when it's done (the > webpage > can't do anything if an error is detected anyway). ok, so let's drop the errorCallback: vacuum([optional] successCallback); > The user agent would need to queue any subsequent transactions if a > vacuum is running. I would consider it as an hint, and after all webpages > that own references to the underlying data files are closed, would do a > vacuum. So, if you have many tabs on gmail, and that a single gmail > instance > tries to do multiple vacuums, it would equiv to one single vacuum > operation. what do we do if some databases are opened for the entire life of the browser? for example, i open my browser which has myfavoriteapp.com set as its homepage. myfavoriteapp.com immediately opens a DB, and i only close that app when i close the browser. when would the browser vacuum myfavoriteapp's DBs in this case? i think it's ok for the UA to vacuum some DBs automatically when it thinks it's a good time to do so; however, if a platform supports the vacuum/defrag call (i.e. if it doesn't treat it is a no-op), then i think a vacuum call coming from the app should be immediately scheduled (yes, the subsequent transac
Re: [WebSQLDatabase] Adding a vacuum() call
joao, if i understand correctly, you basically want to have an automated system implemented in the browser that decides when to vacuum databases (or at least make sure it doesn't happen too often). and the vacuum() calls would be just one of the parameters that the system takes into account. i think having such a system is fine, but doesn't need to be spec'ed. at the same time, i think it's worth having a spec'ed vacuum() call that gives the app some guarantees. in particular, i would like the completionCallback to be a signal that the database is in a good shape and ready for more work. so how about this: 1. if the UA doesn't support the vacuum() call, go to step 3. 2. queue up a task to vacuum the database. if the UA decides that vacuuming is not needed at this time, the vacuuming task scheduled in this step could be a no-op. 3. when the vacuuming task completes, queue up a task to invoke the completion callback, if one was specified. i think this spec should allow you to treat the vacuum() call as a hint, while also guaranteeing that: 1. the UA believes the database is in a good shape when the completion callback is called. 2. the completion callback is called as soon as UA believes the DB is in a good shape. what do you think? thanks, dumi On Thu, Mar 11, 2010 at 4:13 PM, João Eiras wrote: > On Fri, 12 Mar 2010 01:08:41 +0100, Dumitru Daniliuc > wrote: > > joao, >> >> it looks like we mostly agree on this feature, so i was wondering if we >> could formally agree on a spec. here's what i propose: >> >> 1. name: vacuum. to the best of my knowledge, all current WebSQLDatabases >> implementations use SQLite, and in SQLite the command is called VACUUM. so >> it seems to me that we might as well call the new function vacuum(). what >> do >> you think? >> > > I'm fine with it but... > > >> 2. spec: no need for an error callback. >> >> interface Database { >> // the methods and properties currently in the spec >> void vacuum(in optional SQLVoidCallback successCallback); >> }; >> >> 3. what the call should do: the purpose of this call is to allow apps to >> vacuum/compact/defragment/clean up their databases whenever they see fit. >> a >> call to vacuum() could take a considerable amount of time (especially on >> larger or highly fragmented databases); therefore, it is not recommended >> for >> web apps to call this method during periods of high activity. >> >> how to process a vacuum() call: >> >> 1. if the UA does not support this call (mobile browsers?), jump to step >> 3. >> 2. queue up a task to vacuum/compact/defragment/clean up the database. >> 3. if the task succeeds, and a success callback is provided, queue up a >> >> task to invoke the success callback; in all other cases (the task >> failed, or >> no success callback was provided), do nothing: proceed to the next task >> in >> the queue. >> >> does this seem acceptable? we (google engineers interested in this) feel >> that UAs should either not implement the vacuum() call, or they should >> respect it (rather than taking it as a hint). it is ok for UAs to keep >> track >> of things like system idleness or databases closing to do more vacuuming >> that the apps asked for, if they want to. however, we feel that a vacuum() >> request by an app should not be postponed, simply because sometimes apps >> know better than UAs when the best time to vacuum is (it might be nice to >> give apps more information on how fragmented their databases are, but >> that's >> a separate discussion). >> >> > I unfortunately don't agree with this last sentence :) Web pages do not > have a way to know the level of fragmentation, and the last thing user wants > is a web page calling vacuum() before each transaction, *just to be safe*, > because of potential fragmentation. > > But the API is good I think. > > thanks, >> dumi >> >> >>
Re: [WebSQLDatabase] Adding a vacuum() call
On Fri, 12 Mar 2010 04:07:21 +0100, Dumitru Daniliuc wrote: joao, if i understand correctly, you basically want to have an automated system implemented in the browser that decides when to vacuum databases (or at least make sure it doesn't happen too often). and the vacuum() calls would be just one of the parameters that the system takes into account. i think having such a system is fine, but doesn't need to be spec'ed. at the same time, i think it's worth having a spec'ed vacuum() call that gives the app some guarantees. in particular, i would like the completionCallback to be a signal that the database is in a good shape and ready for more work. so how about this: 1. if the UA doesn't support the vacuum() call, go to step 3. 2. queue up a task to vacuum the database. if the UA decides that vacuuming is not needed at this time, the vacuuming task scheduled in this step could be a no-op. 3. when the vacuuming task completes, queue up a task to invoke the completion callback, if one was specified. i think this spec should allow you to treat the vacuum() call as a hint, while also guaranteeing that: 1. the UA believes the database is in a good shape when the completion callback is called. 2. the completion callback is called as soon as UA believes the DB is in a good shape. what do you think? Looks better, and more flexible. I wonder if sqlite has a way to query the number of pages in the freelist ? Probably something like 10% of pages in the freelist would be a good threshold to allow a vacuum. thanks, dumi
Re: [WebSQLDatabase] Adding a vacuum() call
Looks better, and more flexible. I wonder if sqlite has a way to query the number of pages in the freelist ? Probably something like 10% of pages in the freelist would be a good threshold to allow a vacuum. Oh and btw, read only transactions should disallow vacuum, I think/hope :)
Re: [WebSQLDatabase] Adding a vacuum() call
On Thu, Mar 11, 2010 at 7:17 PM, João Eiras wrote: > On Fri, 12 Mar 2010 04:07:21 +0100, Dumitru Daniliuc > wrote: > > joao, >> >> if i understand correctly, you basically want to have an automated system >> implemented in the browser that decides when to vacuum databases (or at >> least make sure it doesn't happen too often). and the vacuum() calls would >> be just one of the parameters that the system takes into account. i think >> having such a system is fine, but doesn't need to be spec'ed. at the same >> time, i think it's worth having a spec'ed vacuum() call that gives the app >> some guarantees. in particular, i would like the completionCallback to be >> a >> signal that the database is in a good shape and ready for more work. so >> how >> about this: >> >> >> 1. if the UA doesn't support the vacuum() call, go to step 3. >> 2. queue up a task to vacuum the database. if the UA decides that >> >> vacuuming is not needed at this time, the vacuuming task scheduled in >> this >> step could be a no-op. >> 3. when the vacuuming task completes, queue up a task to invoke the >> >> completion callback, if one was specified. >> >> i think this spec should allow you to treat the vacuum() call as a hint, >> while also guaranteeing that: >> 1. the UA believes the database is in a good shape when the completion >> callback is called. >> 2. the completion callback is called as soon as UA believes the DB is in a >> good shape. >> >> what do you think? >> > > Looks better, and more flexible. > thanks. is there anything else you'd like to change? i'm pushing for this so hard because i'd like to implement this feature, but i don't want to start before all interested parties agree on a spec. > I wonder if sqlite has a way to query the number of pages in the freelist ? > Probably something like 10% of pages in the freelist would be a good > threshold to allow a vacuum. > you can get the number of free pages with PRAGMA freelist_count ( http://www.sqlite.org/pragma.html). unfortunately, looking only at this number would result in a lot of false negatives. for example, in theory, you could have a DB with 1000 2KB pages, and each page could have only one useful byte written on it. so PRAGMA freelist_count would return 0, even though doing a vacuuming would shrink your DB from ~2MB to ~1KB. but i agree that it could be one of the parameters to take into account.
Re: [WebSQLDatabase] Adding a vacuum() call
I like the completion callback idea. Also like the notion of some sort of protection from "over-eager vacuum-calling syndrome". :DG< On Thu, Mar 11, 2010 at 4:20 PM, Michael Nordman wrote: > Instead of calling back on success only, maybe call back on completion > regardless of success or failure. This way the caller would know when the > potentially lengthy operation was done, regardless of the outcome. > > 2010/3/11 Dumitru Daniliuc >> >> joao, >> >> it looks like we mostly agree on this feature, so i was wondering if we >> could formally agree on a spec. here's what i propose: >> >> 1. name: vacuum. to the best of my knowledge, all current WebSQLDatabases >> implementations use SQLite, and in SQLite the command is called VACUUM. so >> it seems to me that we might as well call the new function vacuum(). what do >> you think? >> >> 2. spec: no need for an error callback. >> >> interface Database { >> // the methods and properties currently in the spec >> void vacuum(in optional SQLVoidCallback successCallback); >> }; >> >> 3. what the call should do: the purpose of this call is to allow apps to >> vacuum/compact/defragment/clean up their databases whenever they see fit.. a >> call to vacuum() could take a considerable amount of time (especially on >> larger or highly fragmented databases); therefore, it is not recommended for >> web apps to call this method during periods of high activity. >> >> how to process a vacuum() call: >> >> if the UA does not support this call (mobile browsers?), jump to step 3. >> queue up a task to vacuum/compact/defragment/clean up the database. >> if the task succeeds, and a success callback is provided, queue up a task >> to invoke the success callback; in all other cases (the task failed, or no >> success callback was provided), do nothing: proceed to the next task in the >> queue. >> >> does this seem acceptable? we (google engineers interested in this) feel >> that UAs should either not implement the vacuum() call, or they should >> respect it (rather than taking it as a hint). it is ok for UAs to keep track >> of things like system idleness or databases closing to do more vacuuming >> that the apps asked for, if they want to. however, we feel that a vacuum() >> request by an app should not be postponed, simply because sometimes apps >> know better than UAs when the best time to vacuum is (it might be nice to >> give apps more information on how fragmented their databases are, but that's >> a separate discussion). >> >> thanks, >> dumi >> >> >> 2010/3/9 Jeremy Orlow >>> >>> On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc wrote: On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: > >>> I don't see how the callbacks are useful though. Vacuum works >>> transparently, its effects are not visible, and what should the page >>> do in >>> case of error ? >>> >> >> i was thinking of something like: >> >> db.defragment(errorCallback, successCallback); >> showAPrettyImageAndAskTheUserToWait(); >> >> function errorCallback(error) {} >> function successCallback() { >> getRidOfThePrettyImageAndRestartTheApp(); >> } >> >> just like you, i'm not sure if the error callback is useful at all, >> but i >> thought i'd add it to make the defragment() call look more like a >> transaction. maybe we don't need it. >> > > True, but this is a kind of operation that could very well just run on > the background, with a single optional callback when it's done (the > webpage > can't do anything if an error is detected anyway). ok, so let's drop the errorCallback: vacuum([optional] successCallback); > > The user agent would need to queue any subsequent transactions if a > vacuum is running. I would consider it as an hint, and after all webpages > that own references to the underlying data files are closed, would do a > vacuum. So, if you have many tabs on gmail, and that a single gmail > instance > tries to do multiple vacuums, it would equiv to one single vacuum > operation. what do we do if some databases are opened for the entire life of the browser? for example, i open my browser which has myfavoriteapp.com set as its homepage. myfavoriteapp.com immediately opens a DB, and i only close that app when i close the browser. when would the browser vacuum myfavoriteapp's DBs in this case? i think it's ok for the UA to vacuum some DBs automatically when it thinks it's a good time to do so; however, if a platform supports the vacuum/defrag call (i.e. if it doesn't treat it is a no-op), then i think a vacuum call coming from the app should be immediately scheduled (yes, the subsequent transactions would have to wait for the vacuuming to finish running). in some cases, the apps know better than the UA when to vacuum their DBs. by the way, we should p
Re: [WebSQLDatabase] Adding a vacuum() call
Some things to consider: (1) VACUUM is never "necessary", though can sometimes reduce the size of the database file and provide a performance improvement if the database is large and fragmented. (2) Use PRAGMA freelist_count to estimate how much VACUUM will shrink a database. (3) VACUUM requires O(N) time where N is the size of the database file, and can require up to 2N temporary space while processing. Our recommendation is that the use of VACUUM be discouraged. The performance improvement only comes up with very large databases that are highly fragmented. Where space reclamation is an issue, AUTO_VACUUM would be more appropriate. -Shane On Thu, Mar 11, 2010 at 7:33 PM, Dimitri Glazkov wrote: > I like the completion callback idea. > > Also like the notion of some sort of protection from "over-eager > vacuum-calling syndrome". > > :DG< > > On Thu, Mar 11, 2010 at 4:20 PM, Michael Nordman > wrote: > > Instead of calling back on success only, maybe call back on completion > > regardless of success or failure. This way the caller would know when the > > potentially lengthy operation was done, regardless of the outcome. > > > > 2010/3/11 Dumitru Daniliuc > >> > >> joao, > >> > >> it looks like we mostly agree on this feature, so i was wondering if we > >> could formally agree on a spec. here's what i propose: > >> > >> 1. name: vacuum. to the best of my knowledge, all current > WebSQLDatabases > >> implementations use SQLite, and in SQLite the command is called VACUUM.. > so > >> it seems to me that we might as well call the new function vacuum(). > what do > >> you think? > >> > >> 2. spec: no need for an error callback. > >> > >> interface Database { > >> // the methods and properties currently in the spec > >> void vacuum(in optional SQLVoidCallback successCallback); > >> }; > >> > >> 3. what the call should do: the purpose of this call is to allow apps to > >> vacuum/compact/defragment/clean up their databases whenever they see > fit.. a > >> call to vacuum() could take a considerable amount of time (especially on > >> larger or highly fragmented databases); therefore, it is not recommended > for > >> web apps to call this method during periods of high activity. > >> > >> how to process a vacuum() call: > >> > >> if the UA does not support this call (mobile browsers?), jump to step 3. > >> queue up a task to vacuum/compact/defragment/clean up the database. > >> if the task succeeds, and a success callback is provided, queue up a > task > >> to invoke the success callback; in all other cases (the task failed, or > no > >> success callback was provided), do nothing: proceed to the next task in > the > >> queue. > >> > >> does this seem acceptable? we (google engineers interested in this) feel > >> that UAs should either not implement the vacuum() call, or they should > >> respect it (rather than taking it as a hint). it is ok for UAs to keep > track > >> of things like system idleness or databases closing to do more vacuuming > >> that the apps asked for, if they want to. however, we feel that a > vacuum() > >> request by an app should not be postponed, simply because sometimes apps > >> know better than UAs when the best time to vacuum is (it might be nice > to > >> give apps more information on how fragmented their databases are, but > that's > >> a separate discussion). > >> > >> thanks, > >> dumi > >> > >> > >> 2010/3/9 Jeremy Orlow > >>> > >>> On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc > wrote: > > > On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: > > > >>> I don't see how the callbacks are useful though. Vacuum works > >>> transparently, its effects are not visible, and what should the > page > >>> do in > >>> case of error ? > >>> > >> > >> i was thinking of something like: > >> > >> db.defragment(errorCallback, successCallback); > >> showAPrettyImageAndAskTheUserToWait(); > >> > >> function errorCallback(error) {} > >> function successCallback() { > >> getRidOfThePrettyImageAndRestartTheApp(); > >> } > >> > >> just like you, i'm not sure if the error callback is useful at all, > >> but i > >> thought i'd add it to make the defragment() call look more like a > >> transaction. maybe we don't need it. > >> > > > > True, but this is a kind of operation that could very well just run > on > > the background, with a single optional callback when it's done (the > webpage > > can't do anything if an error is detected anyway). > > ok, so let's drop the errorCallback: vacuum([optional] > successCallback); > > > > > The user agent would need to queue any subsequent transactions if a > > vacuum is running. I would consider it as an hint, and after all > webpages > > that own references to the underlying data files are closed, would do > a > > vacuum. So, if you have many tabs on gmail, and that a single gmail > instance > > trie
Re: [WebSQLDatabase] Adding a vacuum() call
thanks for your comments, shane. a few questions: > (1) VACUUM is never "necessary", though can sometimes reduce the size of > the database file and provide a performance improvement if the database is > large and fragmented. > large and fragmented databases is exactly the use case that made us think about a vacuum() call. > (2) Use PRAGMA freelist_count to estimate how much VACUUM will shrink a > database. > if i understand the docs correctly, PRAGMA freelist_count will only return the number of empty pages. so if we had many pages with very little data on each one, vacuuming the database would considerably reduce its size, but PRAGMA freelist_node would return 0. is this correct? is there a better way to estimate the amount of space we'd get back if we vacuumed the database? in particular, is there a way to figure out how fragmented each page is? > (3) VACUUM requires O(N) time where N is the size of the database file, and > can require up to 2N temporary space while processing. > i'm not so worried about the space: hard drives are much much bigger than any DB a web app will have. the time it takes to vacuum a DB though is something that UAs would probably want to take into account. do you have any data on how long it takes to vacuum a fragmented database of 1MB? 10MB? 100MB? 1GB? i'm not looking for a benchmark, just some approximate data (even though it would be great if you had a benchmark that you could share with us). > Our recommendation is that the use of VACUUM be discouraged. The > performance improvement only comes up with very large databases that are > highly fragmented. Where space reclamation is an issue, AUTO_VACUUM would > be more appropriate. > the spec i proposed in my previous email allows UAs to ignore vacuum() calls whenever they feel like it, so the UAs could just decide that it's not worth vacuuming small databases. in cases of apps like offline gmail though, the databases will be large, and i would guess that after a while they'd be pretty fragmented, due to constantly deleting older emails and inserting newer ones, so some kind of vacuuming would be needed. AUTO_VACUUM: if i understand the docs correctly, auto-vacuum does not go inside pages. it only automatically removes the pages that are entirely free. is that correct? if so, then it seems to me that auto-vacuuming doesn't really solve the fragmentation problem. also, what's the performance overhead of having auto-vacuum on? and do we have any control over when it kicks in? going back to my offline gmail example, i think it would be much better to have the app call vacuum() when the user writes an email and the app is otherwise idle, for example, then to have auto-vacuuming cause user-visible delays when the app is syncing with the server or doing some other intensive work. thanks, dumi > -Shane > > > > On Thu, Mar 11, 2010 at 7:33 PM, Dimitri Glazkov wrote: > >> I like the completion callback idea. >> >> Also like the notion of some sort of protection from "over-eager >> vacuum-calling syndrome". >> >> :DG< >> >> On Thu, Mar 11, 2010 at 4:20 PM, Michael Nordman >> wrote: >> > Instead of calling back on success only, maybe call back on completion >> > regardless of success or failure. This way the caller would know when >> the >> > potentially lengthy operation was done, regardless of the outcome. >> > >> > 2010/3/11 Dumitru Daniliuc >> >> >> >> joao, >> >> >> >> it looks like we mostly agree on this feature, so i was wondering if we >> >> could formally agree on a spec. here's what i propose: >> >> >> >> 1. name: vacuum. to the best of my knowledge, all current >> WebSQLDatabases >> >> implementations use SQLite, and in SQLite the command is called VACUUM. >> so >> >> it seems to me that we might as well call the new function vacuum(). >> what do >> >> you think? >> >> >> >> 2. spec: no need for an error callback. >> >> >> >> interface Database { >> >> // the methods and properties currently in the spec >> >> void vacuum(in optional SQLVoidCallback successCallback); >> >> }; >> >> >> >> 3. what the call should do: the purpose of this call is to allow apps >> to >> >> vacuum/compact/defragment/clean up their databases whenever they see >> fit.. a >> >> call to vacuum() could take a considerable amount of time (especially >> on >> >> larger or highly fragmented databases); therefore, it is not >> recommended for >> >> web apps to call this method during periods of high activity. >> >> >> >> how to process a vacuum() call: >> >> >> >> if the UA does not support this call (mobile browsers?), jump to step >> 3. >> >> queue up a task to vacuum/compact/defragment/clean up the database.. >> >> if the task succeeds, and a success callback is provided, queue up a >> task >> >> to invoke the success callback; in all other cases (the task failed, or >> no >> >> success callback was provided), do nothing: proceed to the next task in >> the >> >> queue. >> >> >> >> does this seem acceptable? we (google engineers i
Re: [WebSQLDatabase] Adding a vacuum() call
I admit to not being super familiar with the spec as it currently stands, but I find the idea that we would add something like this fairly unappealing. I'm not familiar with any other database API that asks the application programmer to some sort of GC as part of the application. I almost feel like if you're going to add this, you should drop any pretense of calling this a generic SQL interface, and just call it the "WebSQLLite spec". -- Dirk 2010/3/9 Jeremy Orlow : > On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc wrote: >> >> >> On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: >>> > I don't see how the callbacks are useful though. Vacuum works > transparently, its effects are not visible, and what should the page do > in > case of error ? > i was thinking of something like: db.defragment(errorCallback, successCallback); showAPrettyImageAndAskTheUserToWait(); function errorCallback(error) {} function successCallback() { getRidOfThePrettyImageAndRestartTheApp(); } just like you, i'm not sure if the error callback is useful at all, but i thought i'd add it to make the defragment() call look more like a transaction. maybe we don't need it. >>> >>> True, but this is a kind of operation that could very well just run on >>> the background, with a single optional callback when it's done (the webpage >>> can't do anything if an error is detected anyway). >> >> ok, so let's drop the errorCallback: vacuum([optional] successCallback); >> >>> >>> The user agent would need to queue any subsequent transactions if a >>> vacuum is running. I would consider it as an hint, and after all webpages >>> that own references to the underlying data files are closed, would do a >>> vacuum. So, if you have many tabs on gmail, and that a single gmail instance >>> tries to do multiple vacuums, it would equiv to one single vacuum operation. >> >> what do we do if some databases are opened for the entire life of the >> browser? for example, i open my browser which has myfavoriteapp.com set as >> its homepage. myfavoriteapp.com immediately opens a DB, and i only close >> that app when i close the browser. when would the browser vacuum >> myfavoriteapp's DBs in this case? >> >> i think it's ok for the UA to vacuum some DBs automatically when it thinks >> it's a good time to do so; however, if a platform supports the vacuum/defrag >> call (i.e. if it doesn't treat it is a no-op), then i think a vacuum call >> coming from the app should be immediately scheduled (yes, the subsequent >> transactions would have to wait for the vacuuming to finish running). in >> some cases, the apps know better than the UA when to vacuum their DBs. >> >> by the way, we should probably agree on a name for this call. which one do >> you prefer? vacuum, defragment, defrag, something else? i don't have a >> strong opinion. > > I think vacuum is fine since the spec is already tied to the SQLite SQL > dialect. > collectGarbage() is another possibility > Go with whatever you think is most clear and accurate though. > J
Re: [WebSQLDatabase] Adding a vacuum() call
This is not an issue unique to SQLite. MySQL has several options for this, including "OPTIMIZE TABLE foo". SQL Server has a SHRINK database option among others. Oracle has several options including "ALTER TABLE foo SHRINK SPACE". I think regardless of the underlying storage engine chosen, there will be extreme situations which stress any kind of automatic space and performance management algorithms and you'll find users asking for just such interfaces -- be it called vacuum(), optimize(), defragment(), shrink(), etc. -Shane On Fri, Mar 12, 2010 at 4:31 PM, Dirk Pranke wrote: > I admit to not being super familiar with the spec as it currently > stands, but I find the idea > that we would add something like this fairly unappealing. I'm not > familiar with any other > database API that asks the application programmer to some sort of GC > as part of the > application. I almost feel like if you're going to add this, you > should drop any pretense of > calling this a generic SQL interface, and just call it the "WebSQLLite > spec". > > -- Dirk > > 2010/3/9 Jeremy Orlow : > > On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc > wrote: > >> > >> > >> On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: > >>> > > I don't see how the callbacks are useful though. Vacuum works > > transparently, its effects are not visible, and what should the page > do > > in > > case of error ? > > > > i was thinking of something like: > > db.defragment(errorCallback, successCallback); > showAPrettyImageAndAskTheUserToWait(); > > function errorCallback(error) {} > function successCallback() { > getRidOfThePrettyImageAndRestartTheApp(); > } > > just like you, i'm not sure if the error callback is useful at all, > but > i > thought i'd add it to make the defragment() call look more like a > transaction. maybe we don't need it. > > >>> > >>> True, but this is a kind of operation that could very well just run on > >>> the background, with a single optional callback when it's done (the > webpage > >>> can't do anything if an error is detected anyway). > >> > >> ok, so let's drop the errorCallback: vacuum([optional] successCallback); > >> > >>> > >>> The user agent would need to queue any subsequent transactions if a > >>> vacuum is running. I would consider it as an hint, and after all > webpages > >>> that own references to the underlying data files are closed, would do a > >>> vacuum. So, if you have many tabs on gmail, and that a single gmail > instance > >>> tries to do multiple vacuums, it would equiv to one single vacuum > operation. > >> > >> what do we do if some databases are opened for the entire life of the > >> browser? for example, i open my browser which has myfavoriteapp.com set > as > >> its homepage. myfavoriteapp.com immediately opens a DB, and i only > close > >> that app when i close the browser. when would the browser vacuum > >> myfavoriteapp's DBs in this case? > >> > >> i think it's ok for the UA to vacuum some DBs automatically when it > thinks > >> it's a good time to do so; however, if a platform supports the > vacuum/defrag > >> call (i.e. if it doesn't treat it is a no-op), then i think a vacuum > call > >> coming from the app should be immediately scheduled (yes, the subsequent > >> transactions would have to wait for the vacuuming to finish running). in > >> some cases, the apps know better than the UA when to vacuum their DBs. > >> > >> by the way, we should probably agree on a name for this call. which one > do > >> you prefer? vacuum, defragment, defrag, something else? i don't have a > >> strong opinion. > > > > I think vacuum is fine since the spec is already tied to the SQLite SQL > > dialect. > > collectGarbage() is another possibility > > Go with whatever you think is most clear and accurate though. > > J > >
Re: [WebSQLDatabase] Adding a vacuum() call
On Fri, 12 Mar 2010 22:31:15 +0100, Dirk Pranke wrote: I admit to not being super familiar with the spec as it currently stands, but I find the idea that we would add something like this fairly unappealing. I'm not familiar with any other database API that asks the application programmer to some sort of GC as part of the application. I almost feel like if you're going to add this, you should drop any pretense of calling this a generic SQL interface, and just call it the "WebSQLLite spec". It isn't a generic SQL spec, it is already a "WebSQLite" spec. But I'm still not sure what the rationale is for this functionality. In particular, because the sense I got from the TPAC meeting was that nobody except Apple is especially keen on this spec as a long-term solution. So working on such low-level functionality doesn't seem very worthwhile - although if people are seriously going to implement it then knowing that will at least allow ongoing interoperability for those who choose to implement it. cheers Chaals -- Dirk 2010/3/9 Jeremy Orlow : On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc wrote: On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: I don't see how the callbacks are useful though. Vacuum works transparently, its effects are not visible, and what should the page do in case of error ? i was thinking of something like: db.defragment(errorCallback, successCallback); showAPrettyImageAndAskTheUserToWait(); function errorCallback(error) {} function successCallback() { getRidOfThePrettyImageAndRestartTheApp(); } just like you, i'm not sure if the error callback is useful at all, but i thought i'd add it to make the defragment() call look more like a transaction. maybe we don't need it. True, but this is a kind of operation that could very well just run on the background, with a single optional callback when it's done (the webpage can't do anything if an error is detected anyway). ok, so let's drop the errorCallback: vacuum([optional] successCallback); The user agent would need to queue any subsequent transactions if a vacuum is running. I would consider it as an hint, and after all webpages that own references to the underlying data files are closed, would do a vacuum. So, if you have many tabs on gmail, and that a single gmail instance tries to do multiple vacuums, it would equiv to one single vacuum operation. what do we do if some databases are opened for the entire life of the browser? for example, i open my browser which has myfavoriteapp.com set as its homepage. myfavoriteapp.com immediately opens a DB, and i only close that app when i close the browser. when would the browser vacuum myfavoriteapp's DBs in this case? i think it's ok for the UA to vacuum some DBs automatically when it thinks it's a good time to do so; however, if a platform supports the vacuum/defrag call (i.e. if it doesn't treat it is a no-op), then i think a vacuum call coming from the app should be immediately scheduled (yes, the subsequent transactions would have to wait for the vacuuming to finish running). in some cases, the apps know better than the UA when to vacuum their DBs. by the way, we should probably agree on a name for this call. which one do you prefer? vacuum, defragment, defrag, something else? i don't have a strong opinion. I think vacuum is fine since the spec is already tied to the SQLite SQL dialect. collectGarbage() is another possibility Go with whatever you think is most clear and accurate though. J -- Charles McCathieNevile Opera Software, Standards Group je parle français -- hablo español -- jeg lærer norsk http://my.opera.com/chaals Try Opera: http://www.opera.com
Re: [WebSQLDatabase] Adding a vacuum() call
On Fri, Mar 12, 2010 at 4:15 PM, Dumitru Daniliuc wrote: > thanks for your comments, shane. a few questions: > > >> (1) VACUUM is never "necessary", though can sometimes reduce the size of >> the database file and provide a performance improvement if the database is >> large and fragmented. >> > > large and fragmented databases is exactly the use case that made us think > about a vacuum() call. > > >> (2) Use PRAGMA freelist_count to estimate how much VACUUM will shrink a >> database. >> > > if i understand the docs correctly, PRAGMA freelist_count will only return > the number of empty pages. so if we had many pages with very little data on > each one, vacuuming the database would considerably reduce its size, but > PRAGMA freelist_node would return 0. is this correct? is there a better way > to estimate the amount of space we'd get back if we vacuumed the database? > in particular, is there a way to figure out how fragmented each page is? > > Without looking at the specific page structure on the database, there's not a good way to figure this out. However, the B-Tree balancing algorithm used by SQLite will attempt to merge pages with neighbors when there space utilization drops below certain thresholds. Minimum average fill for intkey leaves is 50%. For other pages I think it's 66%. > (3) VACUUM requires O(N) time where N is the size of the database file, and >> can require up to 2N temporary space while processing. >> > > i'm not so worried about the space: hard drives are much much bigger than > any DB a web app will have. the time it takes to vacuum a DB though is > something that UAs would probably want to take into account. do you have any > data on how long it takes to vacuum a fragmented database of 1MB? 10MB? > 100MB? 1GB? i'm not looking for a benchmark, just some approximate data > (even though it would be great if you had a benchmark that you could share > with us). > > VACUUM is largely io-bound and so the speed greatly depends on speed of the underlying filesystem. We generally use the rule of thumb of 1 second per MB on basic systems. > Our recommendation is that the use of VACUUM be discouraged. The >> performance improvement only comes up with very large databases that are >> highly fragmented. Where space reclamation is an issue, AUTO_VACUUM would >> be more appropriate. >> > > the spec i proposed in my previous email allows UAs to ignore vacuum() > calls whenever they feel like it, so the UAs could just decide that it's not > worth vacuuming small databases. in cases of apps like offline gmail though, > the databases will be large, and i would guess that after a while they'd be > pretty fragmented, due to constantly deleting older emails and inserting > newer ones, so some kind of vacuuming would be needed. > > AUTO_VACUUM: if i understand the docs correctly, auto-vacuum does not go > inside pages. it only automatically removes the pages that are entirely > free. is that correct? if so, then it seems to me that auto-vacuuming > doesn't really solve the fragmentation problem. also, what's the performance > overhead of having auto-vacuum on? and do we have any control over when it > kicks in? going back to my offline gmail example, i think it would be much > better to have the app call vacuum() when the user writes an email and the > app is otherwise idle, for example, then to have auto-vacuuming cause > user-visible delays when the app is syncing with the server or doing some > other intensive work. > > When combined with the basic page merging mentioned above, AUTO_VACUUM does a very good job, and the performance overhead is minimal. There's no control over when it kicks in. > thanks, > dumi > > > >> -Shane >> >> >> >> On Thu, Mar 11, 2010 at 7:33 PM, Dimitri Glazkov >> wrote: >> >>> I like the completion callback idea. >>> >>> Also like the notion of some sort of protection from "over-eager >>> vacuum-calling syndrome". >>> >>> :DG< >>> >>> On Thu, Mar 11, 2010 at 4:20 PM, Michael Nordman >>> wrote: >>> > Instead of calling back on success only, maybe call back on completion >>> > regardless of success or failure. This way the caller would know when >>> the >>> > potentially lengthy operation was done, regardless of the outcome. >>> > >>> > 2010/3/11 Dumitru Daniliuc >>> >> >>> >> joao, >>> >> >>> >> it looks like we mostly agree on this feature, so i was wondering if >>> we >>> >> could formally agree on a spec. here's what i propose: >>> >> >>> >> 1. name: vacuum. to the best of my knowledge, all current >>> WebSQLDatabases >>> >> implementations use SQLite, and in SQLite the command is called >>> VACUUM. so >>> >> it seems to me that we might as well call the new function vacuum(). >>> what do >>> >> you think? >>> >> >>> >> 2. spec: no need for an error callback. >>> >> >>> >> interface Database { >>> >> // the methods and properties currently in the spec >>> >> void vacuum(in optional SQLVoidCallback successCallback); >>> >> }; >>> >> >>> >> 3. what the
Re: [WebSQLDatabase] Adding a vacuum() call
You are of course correct that every database has administrative commands like this; I probably overreacted in my wording, and I apologize for that. However, this is exactly the sort of request that makes people unhappy with this spec. While other implementations have SQL DDL for doing things like this, they don't typically expose a single-purpose function, and so you'd be trying to cram other implementations similar-but-not-identical functionality into this name. What do we do? I think there is general consensus that you can't hope to standardize on a SQL dialect as part of this spec. That seems to leave you with three options: expose only a limited set of functionality, expose a generic DBC API and accept that you will write implementation-specific code using the generic APIs (although it would be possible to write more generic APIs on top of that), or accept that this is a SQLite-specific spec and call it that accordingly, to keep from confusing people. You all are probably correct that, especially in SQLite's case, you'll need application-level access to the advanced features (and I say this not because SQLite is somehow inferior but because AFAIK it doesn't have background threads and the self-tuning more complex DBs tend to have), so option #1 probably won't make anyone happy. So, I suggest either you do #2 or #3. Don't just try to gradually had specific hooks onto #1 and pretend this API is something it's not. -- Dirk On Fri, Mar 12, 2010 at 7:52 PM, Shane Harrelson wrote: > This is not an issue unique to SQLite. > > MySQL has several options for this, including "OPTIMIZE TABLE foo". > SQL Server has a SHRINK database option among others. > Oracle has several options including "ALTER TABLE foo SHRINK SPACE". > > I think regardless of the underlying storage engine chosen, there will be > extreme situations which stress any kind of automatic space and performance > management algorithms and you'll find users asking for just such interfaces > -- be it called vacuum(), optimize(), defragment(), shrink(), etc. > > -Shane > > > On Fri, Mar 12, 2010 at 4:31 PM, Dirk Pranke wrote: >> >> I admit to not being super familiar with the spec as it currently >> stands, but I find the idea >> that we would add something like this fairly unappealing. I'm not >> familiar with any other >> database API that asks the application programmer to some sort of GC >> as part of the >> application. I almost feel like if you're going to add this, you >> should drop any pretense of >> calling this a generic SQL interface, and just call it the "WebSQLLite >> spec". >> >> -- Dirk >> >> 2010/3/9 Jeremy Orlow : >> > On Mon, Mar 8, 2010 at 8:47 PM, Dumitru Daniliuc >> > wrote: >> >> >> >> >> >> On Mon, Mar 8, 2010 at 3:39 AM, João Eiras wrote: >> >>> >> > I don't see how the callbacks are useful though. Vacuum works >> > transparently, its effects are not visible, and what should the page >> > do >> > in >> > case of error ? >> > >> >> i was thinking of something like: >> >> db.defragment(errorCallback, successCallback); >> showAPrettyImageAndAskTheUserToWait(); >> >> function errorCallback(error) {} >> function successCallback() { >> getRidOfThePrettyImageAndRestartTheApp(); >> } >> >> just like you, i'm not sure if the error callback is useful at all, >> but >> i >> thought i'd add it to make the defragment() call look more like a >> transaction. maybe we don't need it. >> >> >>> >> >>> True, but this is a kind of operation that could very well just run on >> >>> the background, with a single optional callback when it's done (the >> >>> webpage >> >>> can't do anything if an error is detected anyway). >> >> >> >> ok, so let's drop the errorCallback: vacuum([optional] >> >> successCallback); >> >> >> >>> >> >>> The user agent would need to queue any subsequent transactions if a >> >>> vacuum is running. I would consider it as an hint, and after all >> >>> webpages >> >>> that own references to the underlying data files are closed, would do >> >>> a >> >>> vacuum. So, if you have many tabs on gmail, and that a single gmail >> >>> instance >> >>> tries to do multiple vacuums, it would equiv to one single vacuum >> >>> operation. >> >> >> >> what do we do if some databases are opened for the entire life of the >> >> browser? for example, i open my browser which has myfavoriteapp.com set >> >> as >> >> its homepage. myfavoriteapp.com immediately opens a DB, and i only >> >> close >> >> that app when i close the browser. when would the browser vacuum >> >> myfavoriteapp's DBs in this case? >> >> >> >> i think it's ok for the UA to vacuum some DBs automatically when it >> >> thinks >> >> it's a good time to do so; however, if a platform supports the >> >> vacuum/defrag >> >> call (i.e. if it doesn't treat it is a no-op), then i think a vacuum >> >> call >> >> coming from the app should be immediately scheduled (yes, the
Re: [WebSQLDatabase] Adding a vacuum() call
shane, i was hoping you could clarify a few things about AUTO_VACUUM: > However, the B-Tree balancing algorithm used by SQLite will attempt to > merge pages with neighbors when there space utilization drops below certain > thresholds. Minimum average fill for intkey leaves is 50%. For other pages I > think it's 66%. > according to http://www.sqlite.org/pragma.html#pragma_auto_vacuum: "Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does." so the way i understand these statements is: 1. sqlite always attempts to merge mostly-empty pages. the (auto-)vacuum settings have no effect on that. 2. AUTO_VACUUM only moves pages around and deletes the empty ones. it does not try to repack individual pages. however, because of #1, page repacking happens anyway (to some extent), when AUTO_VACUUM is on. am i right? and one more question: "Auto-vacuuming is only possible if the database stores some additional information that allows each database page to be traced backwards to its referer. Therefore, auto-vacuuming must be turned on before any tables are created. It is not possible to enable or disable auto-vacuum after a table has been created." what happens if we create a database without AUTO_VACUUM on, insert some data, save it to a file, then turn on AUTO_VACUUM and try to open that database again? will sqlite add the missing information? will AUTO_VACUUM be silently turned off for that database? will we get an error when we try to open/read from/write to that database? anything else that we need to be aware of in this case? thanks, dumi
Re: [WebSQLDatabase] Adding a vacuum() call
On Wed, 17 Mar 2010 01:50:00 +0100, Dumitru Daniliuc wrote: shane, i was hoping you could clarify a few things about AUTO_VACUUM: Perhaps this is a bit out of scope of this mailing list ?
Re: [WebSQLDatabase] Adding a vacuum() call
After getting more details on how AUTO_VACUUM and page repacking work in SQLite, we (Chromium/WebKit) have decided take the AUTO_VACUUM route and not push for adding a vacuum() call to the WebSQLDatabases spec at this time. Many thanks to everybody who contributed to the discussion. dumi On Tue, Mar 16, 2010 at 5:03 PM, João Eiras wrote: > On Wed, 17 Mar 2010 01:50:00 +0100, Dumitru Daniliuc > wrote: > > shane, i was hoping you could clarify a few things about AUTO_VACUUM: >> >> > Perhaps this is a bit out of scope of this mailing list ? > > >