Re: [sqlite] Is this an in-memory database too
> It would usually make more sense to do the following: > > BEGIN TRANSACTION; > ... inserts,updates,deletes > COMMIT; > > You get pretty much the same affect, but it is safe. Setting yes, I am aware of this, of course. what I am saying is : since there *is* a cache, and since we *can* modify its paging behavior, we should also have an explicit way to flush it. actually, I have been doing some tests, and "flushing" the cache is not accurate. it seems that sqlite *does* write to "disk", even if the OS does not do so immediatelly. it is the actual OS call to flush the *OS* cache that does not happen in sync=off mode. so, what I am talking about is for an explicit command to do this. it is a single OS call, but it requires a file handle, which should *not* be exposed from the sqlite DLL. I can cheat and get it from the opaque database state pointer, but this would be version-dependent, hence I am asking for an "official" method to do it.
Re: [sqlite] Is this an in-memory database too
> Yes, I was a trifle optimistic with my estimates > > On an operating system with a sensible I/O scheduler (I cannot say > whether or not windows qualifies) it should normally take about > two complete rotations of the disk platter to complete a write. > Obviously a large change would take more than that, but the > common case will usually work in two rotations. > ok. fact is, we have to live with windows. so, let me raise a matter that I mentioned some messages back. I have a feature request, which should be trivial for you to implement, but will take hours for me, since I can barely read C, and not write it, except for trivial cases. case is as follows: could the "pragma synchronous" statement do a full disk flush when it executes, in addition to changing the operating mode? rationale: assume that I can live with the database being in a high-risk state, for a small window of time. I would like to be able to do this: pragma synchronous=off ... inserts,updates,deletes pragma synchronous=full this I *can* do, now, but I dont think that this sequence will flush the in-memory caches when it ends. so, if the pragma did this automatically, the above sequence would make sense. Is this something you could/would add ? alternatively, a PRAGMA FLUSH statement would be fine,too.
Re: [sqlite] Is this an in-memory database too
mike cariotoglou wrote: > - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: > <[EMAIL PROTECTED]> Sent: Thursday, October 07, 2004 7:10 PM Subject: > Re: [sqlite] Is this an in-memory database too > >> Yes. As many different processes as you want can read the database at the >> same time. Only one process at a time can write, but since writes usually >> take a millisecond or less, that is not normally a problem. > > well, this is not actually as true as it should be. true, writes are fast per > se. however, if you operate at a synchronous level of anything more than 0, > the whole operation of inserting a record,say, is quite slow, at least in > windows. > Yes, I was a trifle optimistic with my estimates On an operating system with a sensible I/O scheduler (I cannot say whether or not windows qualifies) it should normally take about two complete rotations of the disk platter to complete a write. Obviously a large change would take more than that, but the common case will usually work in two rotations. With a 7200RPM disk drive, that translates into about 17ms. This figure (17ms per transaction) is consistent with measurements done on my Linux desktop. See http://www.sqlite.org/speed.html. Test 1 does 1000 inserts without using a transaction and does so at a rate of about 77 inserts/second, which is about 13ms per transaction. That's actually a little faster than my back-of- the-envelope calculation above. Perhaps my disk platter spins at more than 7200RPM. Or maybe some updates are happening with fewer than two complete disk rotations. Probably my cheap IDE disk drive is lying to the disk controller by telling it that sectors have been committed to the disk surface before they really have been - a common bug in IDE disk drives. But I digress: The point is that it isn't too hard to get 50 transactions per second which means that a process will normally not need to wait more than about 20ms to get read access to the database - assuming the writers cooperate by not holding locks longer than they really need them. > > PS what does the "D." stand for ? > The "D." stands for my first name. I go by my middle name, "Richard". :-) -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Is this an in-memory database too
- Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 07, 2004 7:10 PM Subject: Re: [sqlite] Is this an in-memory database too > Yes. As many different processes as you want can read the database > at the same time. Only one process at a time can write, but since > writes usually take a millisecond or less, that is not normally a > problem. The writer process does its writing, then when it is done > the other reader processes go back to doing their reading. > well, this is not actually as true as it should be. true, writes are fast per se. however, if you operate at a synchronous level of anything more than 0, the whole operation of inserting a record,say, is quite slow, at least in windows. These are my findings: with sync=none : everything is extremely fast, but no practical way to flush the disk file buffer with sync<>none a single transaction has a largish overhead, which relates to the flushing of the disk file. for example, the following may take hundreds of milliseconds: insert into foo values(somevalues) and also, begin insert into foo values(somevalues) commit however, the following will take almost the same time : begin insert into foo values(somevalues) insert into foo values(somevalues) insert into foo values(somevalues) insert into foo values(somevalues) insert into foo values(somevalues) insert into foo values(somevalues) insert into foo values(somevalues) commit in other words, the time is not consumed by the INSERTS, but by the END (implicit or explicit). since the locks are held for the duration of the transaction, a reader will be blocked by a minimum time of one single flush overhead, which is NOT a millisecond or so. so, at least in windows, there is no such thing as a fast WRITE, unless one chooses to sacrifice data integrity. bulk operations are fast, however, because they can be bracketed by a transaction, which disables the per-statement flushing. I wish there was a way around this. DRH, care to comment ? PS what does the "D." stand for ?
Re: [sqlite] Is this an in-memory database too
Another novice question: Does this run in its separate process or is directly linked into the application requiring access to the stored data. I don't want a crash in any of my embedded modules to crash the database as well. On Thu, 07 Oct 2004 07:44:43 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > > R S wrote: > > I was trying to find info for the same, but couldn't. Do let me know. > > > > SQLite normally operates off of disk. It reads a little from the > disk as it can get by with, but once it reads from the disk it > caches information in memory in case it has to reuse it later. > The size of the memory cache is limited by default to 2MB, but > you can change this at runtime using a pragma. > > If, however, you open the special database file named ":memory:" > it will create an empty in-memory only database that you can use > for temporary storage. > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > >
Re: [sqlite] Is this an in-memory database too
R S wrote: I was trying to find info for the same, but couldn't. Do let me know. SQLite normally operates off of disk. It reads a little from the disk as it can get by with, but once it reads from the disk it caches information in memory in case it has to reuse it later. The size of the memory cache is limited by default to 2MB, but you can change this at runtime using a pragma. If, however, you open the special database file named ":memory:" it will create an empty in-memory only database that you can use for temporary storage. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Is this an in-memory database too
Cory Nelson wrote: nope, runs entirely off disk. On Wed, 6 Oct 2004 23:18:12 -0700, R S <[EMAIL PROTECTED]> wrote: I was trying to find info for the same, but couldn't. Do let me know. Ummm actually SQLite can be an in-memory database. Check this out: http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase jake
Re: [sqlite] Is this an in-memory database too
nope, runs entirely off disk. On Wed, 6 Oct 2004 23:18:12 -0700, R S <[EMAIL PROTECTED]> wrote: > I was trying to find info for the same, but couldn't. Do let me know. > -- Cory Nelson http://www.int64.org
