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.

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

Re: [sqlite] Tracing latencies

On Thu, Dec 6, 2012 at 12:14 AM, Keith Chew wrote: > 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 > >

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

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

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

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

Re: [sqlite] Tracing latencies

On 05/12/2012 1:25 PM, Dan Kennedy wrote: On 12/06/2012 12:47 AM, Black, Michael (IS) wrote: I'm a bit confused with "always aligned". None of the lseeks in this log are 1024 aligned. And I just ran a test with the 3.7.15.1 latest amalgamation and most of these seeks are not aligned. Once in

Re: [sqlite] Tracing latencies

sqlite.org Subject: EXT :Re: [sqlite] Tracing latencies On 12/05/2012 09:03 PM, Black, Michael (IS) wrote: Hmmm...looking at that strace sequencing is there some reason those 24-byte and 1024-byte writes can't be combined? The 1024-byte write is occurring at the end boundary of the 24-byte. That

Re: [sqlite] Tracing latencies

To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Tracing latencies On 12/05/2012 09:03 PM, Black, Michael (IS) wrote: > Hmmm...looking at that strace sequencing is there some reason those 24-byte > and 1024-byte writes can't be combined? The 1024-byte write is occurring at &g

Re: [sqlite] Tracing latencies

Discussion of SQLite Database Subject: EXT :Re: [sqlite] Tracing latencies Hi Dan On Wed, Dec 5, 2012 at 6:38 PM, Dan Kennedy<danielk1...@gmail.com> wrote: If it's not fsync() then IO delays are normally caused by read(). You could try [strace -T -eread ...] to check. Are SELECT statement

Re: [sqlite] Tracing latencies

12 2:11 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Tracing latencies Hi Dan On Wed, Dec 5, 2012 at 6:38 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > If it's not fsync() then IO delays are normally caused by read(). > You could try [strace -T -eread

Re: [sqlite] Tracing latencies

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Chew [keith.c...@gmail.com] Sent: Wednesday, December 05, 2012 2:11 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Tracing latencies Hi Dan On Wed

Re: [sqlite] Tracing latencies

Keith Chew wrote: > In the application, it opens 5 connections and put them in a pool. Please note that SQLite has no write concurrency (actual writing to the DB file requires an exclusive lock), and that there is also no synchronization between multiple connections -- when one connection does

Re: [sqlite] Tracing latencies

On 12/05/2012 03:11 PM, Keith Chew wrote: 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

Re: [sqlite] Tracing latencies

On 5 Dec 2012, at 8:11am, Keith Chew wrote: > Ah, do you think the read on the OS level is starving the writes? > There are not many DB selects from the app, but there are other read > IO activities happening in the background. Still it doesn't make > sense, because WAL

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

Re: [sqlite] Tracing latencies

On 12/05/2012 09:22 AM, Keith Chew wrote: 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

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

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

Re: [sqlite] Tracing latencies

users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Chew [keith.c...@gmail.com] Sent: Tuesday, December 04, 2012 5:00 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Tracing latencies On Wed, Dec 5, 2012 at 11:10 AM, Keith Chew <keith.c...@gma

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

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

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

Re: [sqlite] Tracing latencies

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Chew [keith.c...@gmail.com] Sent: Tuesday, December 04, 2012 3:45 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Tracing latencies On Wed

Re: [sqlite] Tracing latencies

On Tue, Dec 4, 2012 at 4:45 PM, Keith Chew wrote: > 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

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

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

Re: [sqlite] Tracing latencies

On Tue, Dec 4, 2012 at 2:30 PM, Keith Chew wrote: > 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

[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: