Instead of delete and then insert, can you somehow just keep track of which rows are to be deleted, and when new rows come in replace if you can and otherwise insert?
A little more bookkeeping, but it might save the space you need. Gerry On Feb 7, 2014 10:57 PM, "Raheel Gupta" <raheel...@gmail.com> wrote: > Hi, > > Sir, the 32 TB size is not always going to be reached. > The Database is going to be used to store blocks of a Block Device like > /dev/sda1 > The size can reach 3-4 TB easily and would start from atleast 20-100 GB. 32 > TB of data though impractical as of today will be possible in 2-3 years. > The issue happens when I delete the rows and new rows are inserted at the > end of the database the size of the database exceeds that of the actual > block device size even though many pages are having free space. > Hence I am simply trying to optimize the utilization of the free space > available. > > I would have loved to use the page size of 2KB which would give me a > practical size of 4TB. But that would have this hard limit of 4TB. > So I have two possible options which I am trying to help me solve this > issue : > 1) Either make the page size to 2KB and increase the maximum page count to > 2^64 which will be more than sufficient. > 2) Improve the free space utilization of each page when the page size is > 64KB. > > I hope this makes sense. > > > > On Sat, Feb 8, 2014 at 12:54 AM, RSmith <rsm...@rsweb.co.za> wrote: > > > A database that is geared for 32TB size and you are concerned about > rather > > insignificant space wasted by the page size that is needed to reach the > > 32TB max size... does not make any sense unless you are simply paranoid > > about space. Removing the gaps in the table space when deleting a row > (or > > rows) will render a delete query several magnitudes slower. > > > > If it IS that big of a concern, then maybe use standard files rather than > > SQLite to save data in? If the SQL functionality is a must, you can use > > vacuum as often as is needed to clear unused space - but beware, 1 - > Vacuum > > takes some processing to re-pack a DB, especially a near 32TB one... in > the > > order of minutes on a computer I would guess, and much much more on > > anything else. 2 - a 32TB DB will need up to 64TB total free disk space > to > > be sure to vacuum correctly - so having issues with it taking up maybe > 40TB > > for 32TB of data is in itself an irrelevant concern. Even large queries, > > temporary tables etc will all need additional interim space for the sorts > > of queries that might be requested of a 32TB data-set. > > > > The real point being: if you do not have at least 64TB free on whatever > > that 32TB DB will sit, you are doing it wrong, and if you do have that > much > > free, you can ignore the 25% wasted deletion space problem. > > > > If the problem is simply your own pedanticism (at least I can sympathise > > with that!) then it's simply a case of "Welcome to efficient databasing", > > but if it is a real space deficit, then I'm afraid you will have to > re-plan > > or reconsider either the max allowable DB, or the physical layer's space > > availability - sorry. > > > > > > > > On 2014/02/07 20:35, Raheel Gupta wrote: > > > >> Hi, > >> > >> I use a page size of 64 KB. But my row consists of 2 columns that is : > >> i - Auto Increment Integer, > >> b - 4096 Bytes of BLOB data > >> > >> Now for the sake of calculation, lets say 16 rows fit in a page and my > >> table has 10000 rows when I start. > >> > >> Now, lets say I delete some data which is not in sequence i.e. it can be > >> deleted as per data which is not in use. To create such a hypothetical > >> situation for explaining this to you, here is a simple query : > >> DELETE from TABLE where i%4 = 0; > >> > >> As you may see that there is now 25% data deleted in each page. > >> > >> Now even if I do insert another 2500 rows (25% of original size) my > >> database size reaches 125% of the original size when I inserted the > 10000 > >> rows initially. > >> > >> Hence there is significant space wastage. Anyway i can improve that ? > >> It would be nice if the database size would be close to the original > size > >> after deleting 25% and adding some new 25% data. > >> > >> I know you would recommend to use smaller page sizes. Ideally 2KP page > >> size > >> is good but then, the number of pages is restricted to a max of 2^32 > which > >> will restrict the total database size to 4TB only. I need the max size > to > >> be capable of atleast 32TB. > >> > >> > >> > >> On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs <dfgri...@gmail.com> > >> wrote: > >> > >> Can you write more about how this is causing you a problem? Most users > >>> don't experience this as a problem > >>> On Feb 7, 2014 10:30 AM, "Raheel Gupta" <raheel...@gmail.com> wrote: > >>> > >>> SQLite's tables are B-trees, sorted by the rowid. Your new data will > >>>>> probably get an autoincremented rowid, which will be appended at the > >>>>> > >>>> end > >>> > >>>> of the table. > >>>>> > >>>>> A page gets reorganized only when about 2/3 is free space. > >>>>> > >>>>> Anyway to make this ratio to lets say 1/3 ? > >>>> _______________________________________________ > >>>> sqlite-users mailing list > >>>> sqlite-users@sqlite.org > >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >>>> > >>>> _______________________________________________ > >>> sqlite-users mailing list > >>> sqlite-users@sqlite.org > >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >>> > >>> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
