Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Clemens Eisserer]

Hi Stephen,

> *First*, buy a bulk amount of cheap, inexpensive USB keys and start
> throwing your data at the key instead of your OS's card.

Don't see how that will change things: Once the SD card fails, data
logging will fail too - same is true for the usb key.

> *Second*, instead of writing to the SD card 8640 times a day, why not once
> every hour?

This is exactly what I am looking for by reducing the frequency of fsync.
This way the data will stay in the page cache without beeing flused to
the device, sans any ugly workarounds in my software.

Regards, Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Clemens Eisserer]

Hi Richard,

> In WAL mode, with synchronous=NORMAL (the default), fsync() only happens on
> a checkpoint operation.  Whether or not checkpoint is "very seldom" depends
> on a number of factors, but seems likely to be the case in your scenario.

Thanks a lot for your answer.
Just to make sure, with auto-checkpointing turned off, which other
factors can cause a checkpoint to be created (if not manually)?

Thanks, Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[Clemens Eisserer]

Hi,

I would like to use sqlite for storing temperature data acquired every
10s running on my raspberry pi.
As my first SD card died within a week with this workload, I am
looking for opportunities to reduce write operations triggered by
fsyncs to flash.
For me loosing 1h of data at a power failure isn't an issue, however
the DB shouldn't be corrupt afterwards.

I found the pragma "synchronous", which when set to "NORMAL" does seem
to do exactly what I am looking for - when sqlite is used in WAL mode.
Am I right that with this configuration, fsync is only executed very seldomly?

> In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized 
> before each checkpoint
> and the database file is synchronized after each completed checkpoint and the 
> WAL file header is synchronized
> when a WAL file begins to be reused after a checkpoint, but no sync 
> operations occur during most transactions.

Thank you in advance, Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How is the page-cache filled?

[Clemens Eisserer]

Hi,

I would like to create a version of sqlite, which includes a few
changes to the way pages are loaded and written to disk.

* Which version of the source should I use? The amalgamized source
isn't really useful, however for all other source-archives its stated
its not recommended using.

* Where are pages read and written? I found the page-cache, but not
the functions that read/write those pages to disk. A small hint would
be really helpful :)

Thanks a lot, Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Howto use max() function?

[Clemens Eisserer]

HI there!
I have just migrated to my application from hsqldb to SQLite-2.8 using
the jdbc-driver from christian werner.
However I have a problem with the following statement which is used very
oftern to increment indexes.
I know this is not a cool way to archieve this, however I must be 100%
fully SQL conform so I simply "synchronized" the method so that no
multithreding problems can occur.
I use the following code:
select max("+keyName+") from "+tableName+"
rs.next();
return rs.getInt(keyName);
This worked with HSQLDB, whith SQLite I get the following error:
java.sql.SQLException: column CALKEY not found
   at
SQLite.JDBC2x.JDBCResultSetMetaData.findColByName(JDBCResultSetMetaData.java:189)
   at SQLite.JDBC2x.JDBCResultSet.findColumn(JDBCResultSet.java:52)
   at SQLite.JDBC2x.JDBCResultSet.getInt(JDBCResultSet.java:145)
   at
palme.communication.logicElements.GeneralLogic.getMaxID(GeneralLogic.java:69)
   at
palme.communication.logicElements.Division.replyInsertDivision(Division.java:84)
   at
palme.communication.ClientRepresentation.readRequest(ClientRepresentation.java:136)
   at
palme.communication.PalmeSocketInstance.run(PalmeSocketInstance.java:47)
   at java.lang.Thread.run(Thread.java:534)
any ideas how the same thing could be done using 100% standard-SQL?
Thanks a lot, lg Clemens