You may enjoy reading up on Btrees here:
http://en.wikipedia.org/wiki/B-tree
-Ursprüngliche Nachricht-
Von: Raheel Gupta [mailto:raheel...@gmail.com]
Gesendet: Montag, 10. Februar 2014 10:49
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Free Page Data usage
>>
>El lun, 10/2/14, Simon Slavin escribió:
>
> Asunto: Re: [sqlite] Free Page Data usage
> Para: "General Discussion of SQLite Database"
> Fecha: lunes, 10 de febrero, 2014 12:34
>
>
> On 10 Feb 2014, at 11:29am,
On 10 Feb 2014, at 11:29am, Raheel Gupta wrote:
>>> 64-bit page numbers would not be backwards compatible.
>
> It might be possible to implement it in backwards compatible mode. I guess
> SQlite has some free flags in its superblock. Maybe we can use a single
> byte to mark that this is a 64 bi
Hi,
If I were to implement it for my use only, any heads up where I could start.
I do know that PgNo is a variable used everywhere. Will changing that help ?
>> 64-bit page numbers would not be backwards compatible.
It might be possible to implement it in backwards compatible mode. I guess
SQlite
Raheel Gupta wrote:
>If only the number of pages could be increased somehow. Does anyone think
>its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned
>Integer.
The 32-bit page number is part of the file format.
64-bit page numbers would not be backwards compatible.
Regards,
Cle
rsprüngliche Nachricht-
> Von: Raheel Gupta [mailto:raheel...@gmail.com]
> Gesendet: Montag, 10. Februar 2014 07:14
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Free Page Data usage
>
> Hi,
>
> I tried the same database I had and used a 2KB page size.
>
This thread has already gone through that discussion ;)
-Ursprüngliche Nachricht-
Von: Dominique Devienne [mailto:ddevie...@gmail.com]
Gesendet: Montag, 10. Februar 2014 10:32
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Free Page Data usage
On Mon, Feb 10, 2014 at 10
On Mon, Feb 10, 2014 at 10:13 AM, Hick Gunter wrote:
> You can choose the source of fragmentation: loosing close to 1 row per
> page (better in bigger pages) or having ununsed space due to nonadjacent
> deletes (better in smaller pages)
>
For the latter, you do have http://www.sqlite.org/lang_va
]
Gesendet: Montag, 10. Februar 2014 07:14
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Free Page Data usage
Hi,
I tried the same database I had and used a 2KB page size.
It works much faster and also the pages are reused immediattly to the extent of
95%.
If only the number of
Hi,
I tried the same database I had and used a 2KB page size.
It works much faster and also the pages are reused immediattly to the
extent of 95%.
If only the number of pages could be increased somehow. Does anyone think
its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned
Inte
On 9 Feb 2014, at 10:45am, RSmith wrote:
> On 2014/02/09 12:06, Raheel Gupta wrote:
>> Sir, I have only one auto increment primary key.
>> Since the new rows will always have a higher number will the pages
>> which have some free space by deleting rows with lower numbered keys never
>> be reused
On 2014/02/09 12:06, Raheel Gupta wrote:
Hi,
Sir, I have only one auto increment primary key.
Since the new rows will always have a higher number will the pages
which have some free space by deleting rows with lower numbered keys never
be reused ? e.g. If row with ROWID "1" was deleted and free
Hi,
Sir, I have only one auto increment primary key.
Since the new rows will always have a higher number will the pages
which have some free space by deleting rows with lower numbered keys never
be reused ? e.g. If row with ROWID "1" was deleted and freed, will it not
be used to store the NEW row
On 2014/02/08 19:30, Raheel Gupta wrote:
@Simon, Sir I dont want to rearrange the data.
I will try to explain more.
All my rows have the exact same size. They will not differ in size.
My problem is due to the fact that I use 64kB page size.
My rows are exactly 8 Bytes + 4096 Bytes.
Now for the
On Fri, Feb 7, 2014 at 7:39 AM, Raheel Gupta wrote:
> Hi,
>
> My Page size is 64KB and I store around 4KB of row data in one row.
> I store around 1 rows in one table and the database size reaches 42MB.
>
> Now, I am facing a peculiar problem. When I delete just 2-3 rows, that page
> is not r
On Sat, Feb 8, 2014 at 11:51 AM, Simon Slavin wrote:
>
> While a database is in use it might use perhaps 100 pages for a particular
> table. Almost every one of those pages will have a little space free:
> anything from 1 byte to most of the page, depending on how much space each
> row takes up.
On 8 Feb 2014, at 5:30pm, Raheel Gupta wrote:
> I will try to explain more.
> All my rows have the exact same size. They will not differ in size.
> My problem is due to the fact that I use 64kB page size.
> My rows are exactly 8 Bytes + 4096 Bytes.
Your very specific use of SQLite is not every
@Simon, Sir I dont want to rearrange the data.
I will try to explain more.
All my rows have the exact same size. They will not differ in size.
My problem is due to the fact that I use 64kB page size.
My rows are exactly 8 Bytes + 4096 Bytes.
Now for the purpose of ease in calculation lets assume
On 8 Feb 2014, at 11:24am, Raheel Gupta wrote:
> I dont want to repack the DB sir.
> When a page becomes free I want to make sure that page is used up first and
> then new pages are created.
Just to explain that this would be extremely inefficient because a new row that
you write to a database
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" wrote:
> Hi,
>
>
>> No matter what size you make the pages, a delete function is never going
to re-pack the db
I dont want to repack the DB sir.
When a page becomes free I want to make sure that page is used up first and
then new pages are created.
VACUUM is not what I want to do.
I think that free pages are used
Hi Raheel,
It does make sense what you would like to do, but your concern does not make sense. You say you are "trying to optimize the
utilization of the free space available" but give no indication why, it certainly does not seem that space is a problem.
I do understand the urge to optimize v
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 issu
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 row
On 7 Feb 2014, at 6:35pm, Raheel Gupta wrote:
> 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 1
> rows initially.
>
> Hence there
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 1 rows when I start.
Now, lets say I delete some data which is not in sequence i
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" wrote:
> >
> > SQLite's tables are B-trees, sorted by the rowid. Your new data will
> > probably get an autoincremented rowid, which will be appended
>
> 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 ?
Raheel Gupta wrote:
> When I delete just 2-3 rows, that page is not reused for the new data
> which will be inserted in the future.
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 g
29 matches
Mail list logo
