Re: [sqlite] Fsync or fdatasync performs slowly

I've tested it in two kinds of Set-Up box. In one box, it costs about 5ms while in the other box, it costs 30ms. Disks have been removed on both of them. It's strange why the difference is so big. Appreciate for you response, thank you. On 01/20/2015 04:46 AM, Richard Hipp wrote: > On 1/19/15, R

Re: [sqlite] Fsync or fdatasync performs slowly

I've printed log out in the case of no disk, and the log shows that file descriptor that I sent to fsync as an argument is 27, and the fsync functions is called. But I don't know what will fsync do without a disk. Appreciate for your quick response and great help. On 01/19/2015 06:30 PM, Stephan B

Re: [sqlite] Whish List for 2015 (David Barrett)

On 20 Jan 2015, at 12:23am, Klaas V wrote: > On my whish-list for this list is the possibility to include attachments like > screenshots and databases. I've been active on several technical helplists over the years and from my experience I'll tell you what would happen. We'd get A) People p

Re: [sqlite] Whish List for 2015 (David Barrett)

On my whish-list for this list is the possibility to include attachments like screenshots and databases. For program more handy functions and documentation more examples. I'm pretty sure that less lazy users made functions that could be included as standard to be used for all of us.   Kind regar

Re: [sqlite] ORDER BY and LIMIT regression

On Mon, 19 Jan 2015, Richard Hipp wrote: Thank you for reporting the problem. Certainly! We always fix every problem that we are aware of in SQLite. But this problem had not been previously reported to us, and did not occur in any of the 168 million test cases that we ran prior to releasi

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, Angelo Mottola wrote: > Hello, > > I have a regression to report, that seems to have been introduced between > SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 > and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in > 3.8.8 but eventually it

Re: [sqlite] Malformatted output by sqlite3

Aha, thanks. PRINTF is the function to use here (or ROUND). I had been too optimistic to think the -0. would be the truncated output. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fsync or fdatasync performs slowly

On 1/19/15, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 01/18/2015 06:49 PM, Wei, Catherine wrote: >> takes too much time when it executes fsync or fdatasync function. > > Note that although the documentation says only the file handle is > synced, in practise many f

Re: [sqlite] Fsync or fdatasync performs slowly

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/18/2015 06:49 PM, Wei, Catherine wrote: > takes too much time when it executes fsync or fdatasync function. Note that although the documentation says only the file handle is synced, in practise many filesystems actually sync the whole filesystem

Re: [sqlite] Changing Page_size fails sometimes, retry succeeds but is messy. Ideas?

On 19 Jan 2015, at 6:04pm, Andy (KU7T) wrote: > Any ideas what I am doing wrong. Do you have to exclusively lock or wait > somewhere? You have to run VACUUM immediately after setting the new page_size. See One normally only sets the page_s

[sqlite] Advice needed for optimization options

I have a single threaded application, which at its heart is while (true) { open a database // any one of thousands, so I can’t just keep it open do a select close the database } With that part unchanged, does anyone have any optimization suggestions? I am compiling the am

Re: [sqlite] When to run ANALYZE

> On 19 Jan 2015, at 4:11pm, Andy (KU7T) wrote: > > ANALYZE > > ANALYZE sqlite_master > > > > To my app at timely intervals. We are also running in WAL mode. Do I need to > do a WAL checkpoint (pragma wal_checkpoint) before running the index recalc > or not? You don't even need the ANALYZE

[sqlite] Changing Page_size fails sometimes, retry succeeds but is messy. Ideas?

Hi, I determined that I would like to upgrade my page_size from 1k to 4k during start up time if it is not done yet. At the time I would like to do that, no other db access is occurring yet. Still, I see failures that often the page_size returned at the end in the verification step is still the

[sqlite] When to run ANALYZE

Hi, I added a call to both: ANALYZE ANALYZE sqlite_master To my app at timely intervals. We are also running in WAL mode. Do I need to do a WAL checkpoint (pragma wal_checkpoint) before running the index recalc or not? Thanks Andy ___

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, Simon Slavin wrote: > > On 19 Jan 2015, at 3:10pm, Richard Hipp wrote: > >> It is a very >> complex problem. In particular, the sample query works fine as long >> as the number of columns in the result set is not exactly 60. Adding >> or removing a single column of result gives the

Re: [sqlite] ORDER BY and LIMIT regression

On 19 Jan 2015, at 3:10pm, Richard Hipp wrote: > It is a very > complex problem. In particular, the sample query works fine as long > as the number of columns in the result set is not exactly 60. Adding > or removing a single column of result gives the correct answer. I would love to know the

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, RSmith wrote: > Might this not be a "reverse_unordered_selects" pragma or compile option > going wrong, or at least the code making it work getting > somehow hooked in the new versions for this query? > It looks like a bug. If you update to the latest trunk check-in and set: .t

Re: [sqlite] ORDER BY and LIMIT regression

Might this not be a "reverse_unordered_selects" pragma or compile option going wrong, or at least the code making it work getting somehow hooked in the new versions for this query? I have seen similar things when using that pragma (but of course that was intended). Just a thought... On 2015/

Re: [sqlite] ORDER BY and LIMIT regression

Ignore my previous email on this subject. We are able to get different results from 3.8.6 and 3.8.8. Unclear yet if the one or the other is incorrect. On 1/19/15, Richard Hipp wrote: > On 1/19/15, Angelo Mottola wrote: >> Hello, >> >> I have a regression to report, that seems to have been intr

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, Angelo Mottola wrote: > Hello, > > I have a regression to report, that seems to have been introduced between > SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 > and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in > 3.8.8 but eventually it

Re: [sqlite] Compiling error on Cygwin on Windows 8.1: 3.8.7.4 and 3.8.8

2015-01-19 11:01 GMT+01:00 Frank Ho : > Hi, > > I compiled the SQLite on the Cygwin 1.7.33 running on a Windows 8.1, here's > the error: > > .libs/sqlite3.o: In function `sqlite3ThreadProc': > ../sqlite-autoconf-3080800/sqlite3.c:22471: undefined reference to > `_endthreadex' > .libs/sqlite3.

[sqlite] ORDER BY and LIMIT regression

Hello, I have a regression to report, that seems to have been introduced between SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in 3.8.8 but eventually it wasn’t). In our application we can have SQ

[sqlite] Compiling error on Cygwin on Windows 8.1: 3.8.7.4 and 3.8.8

Hi, I compiled the SQLite on the Cygwin 1.7.33 running on a Windows 8.1, here's the error: .libs/sqlite3.o: In function `sqlite3ThreadProc': ../sqlite-autoconf-3080800/sqlite3.c:22471: undefined reference to `_endthreadex' .libs/sqlite3.o: In function `sqlite3ThreadCreate': ../sqlite

Re: [sqlite] Malformatted output by sqlite3

On 2015/01/19 12:52, Stephan Buchert wrote: (Prompt erased for easier paste and copy): CREATE TABLE satpos(msec INTEGER, latitude REAL, longitude REAL); INSERT INTO satpos VALUES (86386217,-0.0318895369716216,-167.689719869132); INSERT INTO satpos VALUES (86386716,-2.93238037697483e-06,-167.690

Re: [sqlite] Huge WAL log

I'll add the results from the latest test that was running for the last 2 hours: This time I did not use intermediate commits. Instead, I dropped the table indexes before the bulk insert started and re-created them at the end. The results after a few tables (most of them small, a few contained 50

Re: [sqlite] Malformatted output by sqlite3

Works as specified. The .width command sets the output width of a column to a certain number of characters; output that is shorter is padded on the right (or the left, if the width is negative); output that is too long is truncated. Use the printf() function to define the format of real numbe

Re: [sqlite] Huge WAL log

Dan Kennedy-4 wrote > Is it correct that you have a single transaction inserting lots of data > into a table with multiple indexes on it? Something like 1GB? Depends on. It is the best option from the application point of view. Other solutions introduce additional risks. Apparently, the answer is

[sqlite] Malformatted output by sqlite3

(Prompt erased for easier paste and copy): CREATE TABLE satpos(msec INTEGER, latitude REAL, longitude REAL); INSERT INTO satpos VALUES (86386217,-0.0318895369716216,-167.689719869132); INSERT INTO satpos VALUES (86386716,-2.93238037697483e-06,-167.690497310632); INSERT INTO satpos VALUES (86387217

Re: [sqlite] Fsync or fdatasync performs slowly

On Mon, Jan 19, 2015 at 11:23 AM, Wei, Catherine wrote: > Thank you for your answer very much. But I have removed the disk from > my set-up box, the data will be saved in memory and the system doesn't > know there's no disk, it will still call fsync. What do you think in > this case? > man fsyn

Re: [sqlite] Fsync or fdatasync performs slowly

Thank you for your answer very much. But I have removed the disk from my set-up box, the data will be saved in memory and the system doesn't know there's no disk, it will still call fsync. What do you think in this case? Appreciate your quick response and great help. On 01/19/2015 05:25 PM, Simon

Re: [sqlite] Page_size increase from 1k to 4k made my "REPLACE INTO" slower. why?

From what little you reveal I assume this is some kind of datalogging application. I also assume there will be a primary key (call ?) and also suspect that there are a number of secondary indices for data retrieval. Since you make no mention of transactions, I must infer that you are using auto

Re: [sqlite] Fsync or fdatasync performs slowly

On Mon, Jan 19, 2015 at 3:49 AM, Wei, Catherine wrote: > Hi, I'm running a software in a set-up box with linux system and find > that every time when I commit transaction, sqlite takes too much time > when it executes fsync or fdatasync function. What could be the possible > reasons? > FWIW, fsy

Re: [sqlite] Fsync or fdatasync performs slowly

On 19 Jan 2015, at 5:38am, Wei, Catherine wrote: > The time i'm looking at is the duration that function fsync or fdatasync > executes, it's about 30ms. I don't know wether it's related to linux > kernel or something related. I've tested it in another kind of set-up > box and the duration of fsy

[sqlite] Page_size increase from 1k to 4k made my "REPLACE INTO" slower. why?

Hi, I am using System.Data.SQLite, Version=1.0.92.0. I read that increasing the page_size to 4k on modern OS is a good thing and should speed things up. However, I have a particular query that takes substantially longer. I tried to make sure that nothing else is changed, so I am a little puzzle