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

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

[sqlite] testing - please ignore

just a test to see if this comes back to me via the list ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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 would cut the i/o ops in half and might be a noticeable improvement. A memory copy would be a lot

Re: [sqlite] Tracing latencies

Run this program (change the diff threshold if you want) on the strace log file. This will only show the calls that take "too long" and the time involved. #include #include main(int argc, char *argv[]) { char buf[65535]; double t1=0,t2; FILE *fp=fopen(argv[1],"r");

[sqlite] to find table exists or not

Hi all, I just want to find whether table exists or not in a database. Is it correct query? select distinct tbl_name from sqlite_master where tbl_name = 'abc'; Is there any other better way to find whether table exists or not. Thanks in advance. Regards,

Re: [sqlite] to find table exists or not

On 5 Dec 2012, at 2:33pm, Durga D wrote: > I just want to find whether table exists or not in a database. > > > Is it correct query? > > > select distinct tbl_name from sqlite_master where tbl_name = 'abc'; This may return zero lines or many. Because indexes and

Re: [sqlite] to find table exists or not

Why do you only want to see if the table is there? You can always do DROP TABLE IF EXISTS ... CREATE TABLE ... to replace the definition or CREATE TABLE IF NOT EXISTS ... to keep the old definition. -Ursprüngliche Nachricht- Von: Durga D [mailto:durga.d...@gmail.com] Gesendet:

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 would cut the i/o ops in half and might be a

Re: [sqlite] Tracing latencies

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 a while it gets lucky. Alignment sure isn't deliberate in this. It appears the first page

Re: [sqlite] Tracing latencies

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 a while it gets lucky. Alignment sure

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

[sqlite] Custom collation method works for comparison, not for ORDER BY

Hello. I've coded up a custom collation method, and am seeing what looks like strange behaviour. The method is intended to sort IPv4 addresses before IPv6 addresses, and IPv6 addresses before other text strings. It's just a little wrapping around

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] Custom collation method works for comparison, not for ORDER BY

Niall O'Reilly wrote: > I've coded up a custom collation method, and am seeing > what looks like strange behaviour. The method is > intended to sort IPv4 addresses before IPv6 addresses, > and IPv6 addresses before other text strings. > > select rowid, * from foo order by

Re: [sqlite] Custom collation method works for comparison, not for ORDER BY

On 05/12/12 21:12, Clemens Ladisch wrote: > Do these queries give the correct result? > > select '100A' collate ipaddress < '127.0.0.1'; > select '100A' collate ipaddress < ' ABCD'; > > I.e., does the collation function actually work? Thanks for the helpful suggestions.

Re: [sqlite] FW: How to add SQLite SDK/dll to WP 8 project

Hi, Suresh Official SQLite is not supported on .NET managed-code for Windows Phone 8 currently. Use LINQ to SQL. (Local Database) http://code.msdn.microsoft.com/wpapps/Local-Database-Sample-57b1614c Or use another library like 'csharp-sqlite'. http://code.google.com/p/csharp-sqlite/ Official

[sqlite] Best practices for Valgrind

Dear SQLite folks, using Debian Sid/unstable with SQLite 3.7.14.1-1, running the WebKit based browser Midori 0.4.7+ from master branch (7567058e) [1] under Valgrind to analyze WebKit 1.8.1 though, I see a lot of the following. ==31797== 64,008 bytes in 1 blocks are possibly lost in loss

Re: [sqlite] Best practices for Valgrind

On Wed, Dec 5, 2012 at 6:26 PM, Paul Menzel < paulepan...@users.sourceforge.net> wrote: > Dear SQLite folks, > > > using Debian Sid/unstable with SQLite 3.7.14.1-1, running the WebKit > based browser Midori 0.4.7+ from master branch (7567058e) [1] under > Valgrind to analyze WebKit 1.8.1 though,

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

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 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] Custom collation method works for comparison, not for ORDER BY

On 12/06/2012 06:11 AM, Niall O'Reilly wrote: On 05/12/12 21:12, Clemens Ladisch wrote: Do these queries give the correct result? select '100A' collate ipaddress< '127.0.0.1'; select '100A' collate ipaddress< ' ABCD'; I.e., does the collation function actually work?