Re: [sqlite] Tracing latencies

On Fri, Dec 7, 2012 at 3:28 PM, Keith Chew wrote: > > We can see the times taken to do the syscalls are fast, so what is > causing the delay between the seek and write? I had a look at the > code, and found the method seekAndWrite(), which looks pretty straight > forward. The onl

Re: [sqlite] Tracing latencies

Hi Richard On Fri, Dec 7, 2012 at 2:08 AM, Richard Hipp wrote: > If that does cure your latencies, it also adds another problem, in that it > does nothing to prevent two or more processes, or even two threads within > the same process, from trying to use the database at the same time and > trippi

Re: [sqlite] Tracing latencies

On Thu, Dec 6, 2012 at 3:52 PM, Keith Chew wrote: > I am guess it may have something to do with the locking of the files. > sqlite could be getting blocked by the filesystem when trying to > obtain the lock before writing to the file... This is a stab in the dark... I had a look at t

Re: [sqlite] Tracing latencies

On Thu, Dec 6, 2012 at 1:01 PM, Keith Chew wrote: > I used sar and iostat to profile what is happening at the disk level, > and below are the results. You can see that the TPS on the disk is > very small, only 6-7 tps, and both mysql and sqlite profiles show very > close patterns, e

Re: [sqlite] Tracing latencies

On Thu, Dec 6, 2012 at 9:16 AM, Keith Chew wrote: > Thank you so much for all the input. I tried 2 things, using only a > single connection (as suggested by Clemen) and to change the page size > to 32K. Single connection did not reduce the average latency, but did > help in reducin

Re: [sqlite] Tracing latencies

Hi All > That hypothesis could be easily tested by examining the corresponding strace > output for mysql... I don't know if sqlite3's unaligned log writes are > actually slow (didn't see this info in any of Keith's messages), but if they > are that would suggest the hypothesis is false; it would r

Re: [sqlite] Tracing latencies

Hi Dan On Wed, Dec 5, 2012 at 6:38 PM, Dan Kennedy wrote: > If it's not fsync() then IO delays are normally caused by read(). > You could try [strace -T -eread ...] to check. > > Are SELECT statements fast on the same database? How large is the > database compared to the machines memory? Ah, do

Re: [sqlite] Tracing latencies

On Wed, Dec 5, 2012 at 1:14 PM, Keith Chew wrote: > Since there is no fsync (I know this using grep on the strace ouput), > it is hard to tell what to look for in the strace. The output is very > noisy, so it makes it hard to go through them. > > Does anyone know any handy flag

Re: [sqlite] Tracing latencies

Hi Michael On Wed, Dec 5, 2012 at 12:02 PM, Black, Michael (IS) wrote: > Can you re-run your strace as "strace -tt" and look at the timings to help > pinpoint it? Since there is no fsync (I know this using grep on the strace ouput), it is hard to tell what to look for in the strace. The output

Re: [sqlite] Tracing latencies

On Wed, Dec 5, 2012 at 11:10 AM, Keith Chew wrote: > The strange thing is that I am setting sqlite it to use WAL, > autocheckpoint off and synchronous off. Even in this setup, I still > see > 350ms transactions times for less than 3 TPS. A bit hard to > believe, so I am now doing a

Re: [sqlite] Tracing latencies

Hi Michael On Wed, Dec 5, 2012 at 10:57 AM, Black, Michael (IS) wrote: > Could it be waitiing on the prior transaction though? > > Since disk I/O lies it might be syncing the last transaction causing the new > one to wait longer. Yes, that is possible. In the application, it opens 5 connections

Re: [sqlite] Tracing latencies

On Wed, Dec 5, 2012 at 10:56 AM, Richard Hipp wrote: >> It is not an indexing issue. For one of the UPDATE SQLs, it is >> updating a table with only 1 record in it. And this takes > 350ms... >> > > Can you provide specifics: The schema and the UPDATE statement? Schema and SQL are below: CREATE

Re: [sqlite] Tracing latencies

On Wed, Dec 5, 2012 at 10:28 AM, Keith Chew wrote: > > I wonder what could be causing sqlite to hang so long? Will try to > remove all indexes to see if that narrows things down. It is not an indexing issue. For one of the UPDATE SQLs, it is updating a table with only 1 record in it.

Re: [sqlite] Tracing latencies

On Wed, Dec 5, 2012 at 9:33 AM, Keith Chew wrote: > Any other ideas for me to trace this at a lower level would be greatly > appreciated. I think I will need to get deeper into linux's block > layer stuff, so perhaps this is not the correct place to ask the > question, but I

Re: [sqlite] Tracing latencies

Hi Richard On Wed, Dec 5, 2012 at 9:05 AM, Richard Hipp wrote: > The $dir will contain initial database contents and scripts of SQL > statements that were run. You can rerun those SQL statement using the > command-line shell to find slow ones, then do things like EXPLAIN QUERY > PLAN to figure o

[sqlite] Tracing latencies

Hi I have an existing application, which I am experimenting with replacing MySQL with sqlite. I have 2 boxes, one running MySQL, the other sqlite. Execution times for every insert/update/delete SQLs is measured, and a warn log is printed if it takes > 250ms to perform the SQL. OS configuration:

Re: [sqlite] WAL and updates

> Thanks for this Simon, but unfortunately this is legacy code, which has to > be used for a while. I just did an update to check for column change (we > have a column to tell us the record has changed, so we do not need to check > every column), and the WAL file size growth has dropped significant

Re: [sqlite] WAL and updates

> > > > Okay, if you're doing this set up updates to do synchronisation then > you're going about it a very poor way. We've written a lot on this list > about the problems with synchronisation over the years and you'll find that > your way isn't going to be efficient. > > Instead of keeping a list

Re: [sqlite] WAL and updates

Hi Ok, thanks for the all the suggestions, I will find a workaround. The reason I am asking is that I am using sqlite to perform data synchronisation between a server and client, and after a day, the WAL file size can grow to 3GB, quite a bit. I will look at improving the SQL update to reduce some

Re: [sqlite] WAL and updates

Hi Richard > > UPDATE user SET user_name='Keith' WHERE user_name!='Keith'; > > > The example I provided was simplified only to explain the scenario. In a production environment, there are over 40-50 columns, and the suggested workaround above is impractical. Regards Keith ___

[sqlite] WAL and updates

Hi I found that when an update SQL is issued against a table, the WAL file gets updated with the new record, even though the record is exactly the same as the current record. Eg user table has 1 record with user_name = 'Keith'. Issuing this SQL will increase the WAL file: update user set user_na

Re: [sqlite] WAL, synchronous=1 and durability

Hi Dan > In both NORMAL mode, we do the following: > > 1. Write a bunch of transactions into the WAL file. > 2. fsync() the WAL file. > 3. Copy the data from the WAL file into the database file. > 4. fsync() the database file. > > If a power failure occurs at any time, the next process to

Re: [sqlite] WAL, synchronous=1 and durability

Hi Jay > > In WAL mode that's only half incorrect. Your description of NORMAL > seems correct, but FULL should be fully durable. The WAL file may > need to be checkpointed on startup, but the if the commit happens in > WAL/FULL, you should have full durability. > This is the reason for m

Re: [sqlite] WAL, synchronous=1 and durability

Hi Jay > http://www.sqlite.org/pragma.html#pragma_synchronous > > When synchronous is FULL (2), the SQLite database engine will use > the xSync method of the VFS to ensure that all content is safely > written to the disk surface prior to continuing. This ensures > that an

Re: [sqlite] WAL, synchronous=1 and durability

Hi Simon Thank you for your reply. > > Probably worth us knowing whether you've read the 'Performance > Considerations' section of > > > Yes, I have read that, along with all the threads/posts I could find from the Internet. The thing that I cannot fully underst

[sqlite] WAL, synchronous=1 and durability

Hi Using journal_mode=WAL and synchronous=2, it is my understanding (from the docs and my own power loss testing) that sqlite will maintain durability on a power loss. My question is, if sqlite can guarantee durability with synchronous=2, why can't it (from reading the docs and other posts) do th