[sqlite] windows network and wal mode
Zaumseil Ren? wrote: > I have a separate table for each parameter with time stamp and value. > The time stamp is used as "integer primary key asc". > [...] > It is also possible to go back in time and then start from there. > Currently I remove all values from the tables after the specified time. > New values can be added after this. > With larger backtrack intervals I have performance problems. > > Can I speed up the deletion? How exactly are you doing the deletion? Are you using a single transaction? > I will only read the data with a second program on another computer. > I have read it is not possible to use sqlite in wal mode over a network. > Is it so in my scenario? If "on another computer" means that you are using a network file system (and not copying the DB files to the other computer's local disk), then WAL will not work correctly. > Would it be possible if I can stop the writing for the duration of the > read? Updates of memory-mapped files are not guaranteed to be seen on remote computers (this might corrupt data). But if everything happens on a single computer, it should work. Using WAL mode appears not to be a good idea, but I guess you need the read/write concurrency. In that case, consider using a client/server database. > Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche ... This e-mail contains public information intended for any subscriber of this mailing list and for anybody else who bothers to read it; it will be copied, disclosed and distributed to the public. If you think you are not the intended recipient, please commit suicide immediately. These terms apply also to any e-mails quoted in, referenced from, or answering this e-mail, and supersede any confidentiality notices in those e-mails. Additionally, confidentiality notices in those e-mails will incur legal processing fees of $42 per line; you have agreed to this by reading this confidentiality notice. Regards, Clemens
[sqlite] windows network and wal mode
Ad 1) You may be able to speed up deletion if you can partition your logging tables by time, e.g. each table holds the changes within a certain time frame or a fixed number of changes. When you jump back in time, dropping the tables created after the target timestamp is faster than deleting the individual records. This will, however, require that your application handle reading data from a sequence of files. Ad 2) Network filesystem locking is notoriously broken. SQLite depends on file locks being reliable. -Urspr?ngliche Nachricht- Von: Zaumseil Ren? [mailto:RZaumseil at kkg.ch] Gesendet: Donnerstag, 02. April 2015 07:44 An: 'sqlite-users at mailinglists.sqlite.org' Betreff: [sqlite] windows network and wal mode Hi there, I have used sqlite to store parameter changes with a time stamp in a simulation environment. Because of the number of parameters (2000+) and the max. change rate up to 50ms I have a separate table for each parameter with time stamp and value. The time stamp is used as "integer primary key asc". So is no need of an internal rowid. The database is opened from one process in wal mode with the following settings: PRAGMA synchronous = 1 PRAGMA journal_mode = WAL PRAGMA wal_autocheckpoint = 1 PRAGMA page_size = 4096 PRAGMA cache_size = 5000 Only one process will write to the database. It is also possible to go back in time and then start from there. Currently I remove all values from the tables after the specified time. New values can be added after this. With larger backtrack intervals I have performance problems. Question 1: Can I speed up the deletion? Question 2: I will only read the data with a second program on another computer. I have read it is not possible to use sqlite in wal mode over a network. Is it so in my scenario? Would it be possible if I can stop the writing for the duration of the read? Is there another solution? Thank you Rene Kernkraftwerk Goesgen-Daeniken AG CH-4658 Daeniken, Switzerland Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] windows network and wal mode
Hi there, I have used sqlite to store parameter changes with a time stamp in a simulation environment. Because of the number of parameters (2000+) and the max. change rate up to 50ms I have a separate table for each parameter with time stamp and value. The time stamp is used as "integer primary key asc". So is no need of an internal rowid. The database is opened from one process in wal mode with the following settings: PRAGMA synchronous = 1 PRAGMA journal_mode = WAL PRAGMA wal_autocheckpoint = 1 PRAGMA page_size = 4096 PRAGMA cache_size = 5000 Only one process will write to the database. It is also possible to go back in time and then start from there. Currently I remove all values from the tables after the specified time. New values can be added after this. With larger backtrack intervals I have performance problems. Question 1: Can I speed up the deletion? Question 2: I will only read the data with a second program on another computer. I have read it is not possible to use sqlite in wal mode over a network. Is it so in my scenario? Would it be possible if I can stop the writing for the duration of the read? Is there another solution? Thank you Rene Kernkraftwerk Goesgen-Daeniken AG CH-4658 Daeniken, Switzerland Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
