Why so many page writes to database file on inserts and updates?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Why so many page writes to database file on inserts and updates?

Mark Allan

Can someone out there advise me as to why SQlite needs to perform so many writes to disk when executing an insert.
The insert consists of 6 columns. There is a total of 5 indexes on the table. The average size of one of these records is around 800 bytes. We have an SQlite page size of 1024 bytes. But it seems that 10 pages are being written to.

This is one example of many. In another instance where we need to modify only 1 column and 1 record we see 2 page writes.

Can anyone please advise as to why SQlite needs to update so many pages?

I ask this as we are using a Flash filing system and we wish to improve the performance of writing records with SQlite. It would be helpful to know why SQlite needs to update so much of the database file on each update.

Thanks in advance

Mark
Reply | Threaded
Open this post in threaded view
|

Re: Why so many page writes to database file on inserts and updates?

D. Richard Hipp
"Mark Allan" <[hidden email]> wrote:
> Can someone out there advise me as to why SQlite needs to perform
> so many writes to disk when executing an insert. The insert consists
> of 6 columns. There is a total of 5 indexes on the table. The average
> size of one of these records is around 800 bytes. We have an SQlite
> page size of 1024 bytes. But it seems that 10 pages are being written to.
>

The table itself and all indices are stored separately from
one another.  So right away there are 6 page that need updating
with any change.  Then each page must be written twice -
once to the rollback journal and once to the main database.
This is necessary so that updates are atomic and so that
an unexpected crash or power loss does not corrupt the
database file.  So for inserting into a table with 5
indices, I count 12 pages that need to be written, not 10.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: Why so many page writes to database file on inserts and updates?

Mark Allan
In reply to this post by Mark Allan
Thanks alot for this information.

We have disabled the writing of data to the rollback journal file before writing to the database (could this account for 10 writes instead of 12?). We did this to speed up the write to the database, which it has. It would seem from your email that this could be a bad idea and open up the possibility of database corruption. We really need to limit the amount of writes made to the flash as it is a NOR flash chip and write speed is quite slow. Is it possible to create this journal file in RAM somehow? Assuming that the journal file will not be too much bigger than one page (1024 bytes) as we are running on an embedded system and are a little limited for RAM.

Thanks again for your help

Mark

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> Sent: 04 October 2005 11:47
> To: [hidden email]
> Subject: Re: [sqlite] Why so many page writes to database file on
> inserts and updates?
>
>
> "Mark Allan" <[hidden email]> wrote:
> > Can someone out there advise me as to why SQlite needs to perform
> > so many writes to disk when executing an insert. The insert consists
> > of 6 columns. There is a total of 5 indexes on the table.
> The average
> > size of one of these records is around 800 bytes. We have an SQlite
> > page size of 1024 bytes. But it seems that 10 pages are
> being written to.
> >
>
> The table itself and all indices are stored separately from
> one another.  So right away there are 6 page that need updating
> with any change.  Then each page must be written twice -
> once to the rollback journal and once to the main database.
> This is necessary so that updates are atomic and so that
> an unexpected crash or power loss does not corrupt the
> database file.  So for inserting into a table with 5
> indices, I count 12 pages that need to be written, not 10.
> --
> D. Richard Hipp <[hidden email]>
>
>