[sqlite] SQlite.NET.chm
When I download SQlite.NET.chm from the internet, and open it, I can see the table of content but not the actual content. It appears to be due to a block caused by downloading from the internet. Googling came up with this: https://community.spiceworks.com/topic/1961503-solved-windows-10-chm-help-files-showing-up-blank However, my company has apparently set our pc's so we can neither see nor change the block status of a file from the internet. As a work-around, I used cygwin's cp command to copy the file, and the copy is not blocked. Since .chm is a compiled html file, I would imagine the concern is that there can be embedded (and malicious) scripts in the html. Would it be possible to provide documentation in a safer format, such as a .pdf? Ray Rizzuto Susquehanna International Group (610)747-2336 (W) (856)617-1638 (C) Sent from Outlook IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multi-thread access to a db
Actually, my use of the db is much simpler, and I don't need the databases at all after the program completes. The db is essentially a backing store for in-memory orders. I.e. I persist the order to the db and discard the in-memory order when it looks likely I won't need it (to avoid out of memory crash), and only very occasionally reload it back into memory from the db. The app runs from start of business to end of business. The db is discarded at end of day since its contents aren't the end product of the app. The app basically converts input order-related events from one wire format into another, and sends the output format over the network to another system. I suspect this is quite different from most uses of sqlite. -Original Message- From: Ken [mailto:kennethinbox-sql...@yahoo.com] Sent: Thursday, July 09, 2009 3:06 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] multi-thread access to a db Batching the orders and writing more data as one transaction will certainly yield better throughput, but at the risk of some data loss until the data is committed to disk. It sounds like you are building some type of OLTP/ Transaction logging system. Another good idea here is to also implement or at least think about some form of an archiving system. For instance not only would you have one db per thread (hashed) but maybe daily you switch to a brand new database file set. Maybe a naming convention such as MMDDYY_HashID.db would also be useful? --- On Thu, 7/9/09, Rizzuto, Raymond wrote: > From: Rizzuto, Raymond > Subject: Re: [sqlite] multi-thread access to a db > To: "General Discussion of SQLite Database" > Date: Thursday, July 9, 2009, 12:28 PM > Right now I index off the order > id. I can look into options for indexing - you are > correct that it is more likely that I'll need to read an > order I recently wrote than one that is older. > However, since reading is ~.2% of the accesses to the db, > all db work accounts for 2% of the cpu usage, it may not be > worth optimizing in that area. > > In my system, order codes are unique, and orders go to a > specific thread based on a hash of that id. Therefore, > the only thing I need to do is create a unique db file, > probably based on the thread id, and each thread's logic for > writing/reading is unchanged - just which db is different. > > Currently I archive orders individually, at the time I > determine the order shouldn't be needed. I could add > logic to do that in a batch in the future, but that would > require some extensive changes elsewhere in the logic, so > I'll try some of the other suggestions first. > > -Original Message- > From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] > Sent: Thursday, July 09, 2009 1:08 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] multi-thread access to a db > > > On 9 Jul 2009, at 2:58pm, Rizzuto, Raymond wrote: > > > I have 4 servers, all with 4 cores. This is to > handle a volume of > > 10-20 million orders per day. > > > > Most of the work load (~90%) is unrelated to the > database. In fact, > > I added the database just to allow me to offload > orders out of > > memory when they look done so that the app doesn't run > out of > > memory. It is a 32-bit app, so it typically dies > when it hits ~2.5 > > gig. Moving to a 64-bit app was not an option. > > Okay, you're way ahead of most of my recommendations and > obviously > understand what you're doing. > > > In approx .2% of the orders, I guess wrong, and have > to read the > > order back in from the db to process additional > activity. I could > > remove indices to improve write performance, however > the hit on read > > performance might outweigh the gain. Since the > processing is > > supposed to be in near-realtime, the delay in that > case might be > > excessive. Still, it may be worth trying that. > > Since it takes very little extra coding to test the effect > on speed it > might be worth experimenting with unusual index > methods. If you read > the database only for two operations, both of which need > all records > to do with a particular order, it might be worth > experimenting with > having no primary key, just one index on the 'order number' > column. > One DBMS I used to use was famously faster if you used > 'DESC' in > indices, since records you were normally looking for were > more likely > to be recent records than extremely old ones, and the > search algorithm > worked faster that way. > > > I am using begin/commit around the writes since each > order can > > require between 1
Re: [sqlite] multi-thread access to a db
Right now I index off the order id. I can look into options for indexing - you are correct that it is more likely that I'll need to read an order I recently wrote than one that is older. However, since reading is ~.2% of the accesses to the db, all db work accounts for 2% of the cpu usage, it may not be worth optimizing in that area. In my system, order codes are unique, and orders go to a specific thread based on a hash of that id. Therefore, the only thing I need to do is create a unique db file, probably based on the thread id, and each thread's logic for writing/reading is unchanged - just which db is different. Currently I archive orders individually, at the time I determine the order shouldn't be needed. I could add logic to do that in a batch in the future, but that would require some extensive changes elsewhere in the logic, so I'll try some of the other suggestions first. -Original Message- From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] Sent: Thursday, July 09, 2009 1:08 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] multi-thread access to a db On 9 Jul 2009, at 2:58pm, Rizzuto, Raymond wrote: > I have 4 servers, all with 4 cores. This is to handle a volume of > 10-20 million orders per day. > > Most of the work load (~90%) is unrelated to the database. In fact, > I added the database just to allow me to offload orders out of > memory when they look done so that the app doesn't run out of > memory. It is a 32-bit app, so it typically dies when it hits ~2.5 > gig. Moving to a 64-bit app was not an option. Okay, you're way ahead of most of my recommendations and obviously understand what you're doing. > In approx .2% of the orders, I guess wrong, and have to read the > order back in from the db to process additional activity. I could > remove indices to improve write performance, however the hit on read > performance might outweigh the gain. Since the processing is > supposed to be in near-realtime, the delay in that case might be > excessive. Still, it may be worth trying that. Since it takes very little extra coding to test the effect on speed it might be worth experimenting with unusual index methods. If you read the database only for two operations, both of which need all records to do with a particular order, it might be worth experimenting with having no primary key, just one index on the 'order number' column. One DBMS I used to use was famously faster if you used 'DESC' in indices, since records you were normally looking for were more likely to be recent records than extremely old ones, and the search algorithm worked faster that way. > I am using begin/commit around the writes since each order can > require between 1 and 5 writes. If you're writing orders in big batches, and if your previous statements about crash-recovery are true, then it might be worth putting begin/commit just around each batch of orders instead of each individual order. You might want to take it even further: by analogy with a non-SQL DBMS, I once wrote a logging program which did a COMMIT only just before a SELECT was needed, or when the logging program was quit. However, I don't know how SQLite acts if you have thousands of uncommitted changes: it might get slower if you have that many. > Ken made a suggestion that I create separate db's for each thread. > Since the threads don't share data, that approach would work for me, > and eliminate all contention. Each db would have the same schema, > of course. If you don't share data, that might be good. You'd need to work out a a system for order codes, of course, so that you can work out which dataset a particular order is in. Simon. IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multi-thread access to a db
I did some measurements and calculations, and writing to the db accounts for 2% of the processing in my system. Of course, that low percentage is due to the high transaction rate I got with asynch+exclusive. With 5 threads, I compute that contention would occur 20% of the time. That isn't too horrendous, but I think splitting the db to a per thread db makes sense so that I can scale to more threads. We'll probably migrate to machines with 8 cores, and increase the number of threads, which will make the contention unacceptable. -Original Message----- From: Rizzuto, Raymond Sent: Thursday, July 09, 2009 12:44 PM To: 'kennethinbox-sql...@yahoo.com'; 'General Discussion of SQLite Database' Subject: RE: [sqlite] multi-thread access to a db Each thread has its own data. I.e. a thread only ever needs to read data that it wrote. I considered using the asynch io module described at http://www.sqlite.org/asyncvfs.html so that I could have a worker thread, but was dissuaded by the author, who suggested the asynch pragma instead. The worker thread that decides to move an order from memory to db might in the very next instance find it needs to restore the order to memory. There would need to be some interlock to ensure that the data was not deleted till it was stored, or that it could be retrieved from the db thread's work queue. I believe the asynch io moduel supported that. At this point, I am relatively happy with the performance I get when I have both asynch and exclusive selected. In a simple (non-multithreaded) test, I was able to write 10,000 records of 1k length each in 1 second. My main concern is to make sure that I code my multithreaded application to work correctly with sqlite in the asynch+exclusive mode, and that I don't do anything to degrade the performance from what I saw in my simple test. Ray -Original Message- From: Ken [mailto:kennethinbox-sql...@yahoo.com] Sent: Thursday, July 09, 2009 10:33 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] multi-thread access to a db Additional considerations: 1. Does the re-reading of data occur cross thread? If so you'll need some way to identify the DB that contains the data. 2. Consider using either a disk array or multiple disk drives, one for each db file. You probably should do some load testing at volume to determine optimal configurations for you write patterns. 3. You indicate that the I/O is minimal. Why not create one thread that handles the Database work load. All the other threads could pass the work to the DbWorker thread. This would also eliminate DB contention, but would cause contention at the OS Mutex/Semaphore layer, which should be faster than disk based contention. Implementation of course could be done via a Shared Memory segment (if unix based) and some locking mechanisms. For a really slick high performance LL implemenation consider using an Unrolled Linked List. They are incredibly fast and provide 3-5 times faster performance than a simple LL, especially on multicore cpu's with large L1,L2 cache lines. HTH --- On Thu, 7/9/09, Rizzuto, Raymond wrote: > From: Rizzuto, Raymond > Subject: Re: [sqlite] multi-thread access to a db > To: "General Discussion of SQLite Database" > Date: Thursday, July 9, 2009, 8:58 AM > I have 4 servers, all with 4 > cores. This is to handle a volume of 10-20 million > orders per day. > > Most of the work load (~90%) is unrelated to the > database. In fact, I added the database just to allow > me to offload orders out of memory when they look done so > that the app doesn't run out of memory. It is a 32-bit > app, so it typically dies when it hits ~2.5 gig. > Moving to a 64-bit app was not an option. > > In approx .2% of the orders, I guess wrong, and have to > read the order back in from the db to process additional > activity. I could remove indices to improve write > performance, however the hit on read performance might > outweigh the gain. Since the processing is supposed to > be in near-realtime, the delay in that case might be > excessive. Still, it may be worth trying that. > > I am using begin/commit around the writes since each order > can require between 1 and 5 writes. > > Ken made a suggestion that I create separate db's for each > thread. Since the threads don't share data, that > approach would work for me, and eliminate all > contention. Each db would have the same schema, of > course. > > Ray > > -Original Message- > From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] > Sent: Wednesday, July 08, 2009 5:12 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] multi-thread access to a db > > > On 8 Jul 2009, at 9:28pm, Rizzuto, Raymond wrote: > > > If I remove the lock
Re: [sqlite] multi-thread access to a db
Each thread has its own data. I.e. a thread only ever needs to read data that it wrote. I considered using the asynch io module described at http://www.sqlite.org/asyncvfs.html so that I could have a worker thread, but was dissuaded by the author, who suggested the asynch pragma instead. The worker thread that decides to move an order from memory to db might in the very next instance find it needs to restore the order to memory. There would need to be some interlock to ensure that the data was not deleted till it was stored, or that it could be retrieved from the db thread's work queue. I believe the asynch io moduel supported that. At this point, I am relatively happy with the performance I get when I have both asynch and exclusive selected. In a simple (non-multithreaded) test, I was able to write 10,000 records of 1k length each in 1 second. My main concern is to make sure that I code my multithreaded application to work correctly with sqlite in the asynch+exclusive mode, and that I don't do anything to degrade the performance from what I saw in my simple test. Ray -Original Message- From: Ken [mailto:kennethinbox-sql...@yahoo.com] Sent: Thursday, July 09, 2009 10:33 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] multi-thread access to a db Additional considerations: 1. Does the re-reading of data occur cross thread? If so you'll need some way to identify the DB that contains the data. 2. Consider using either a disk array or multiple disk drives, one for each db file. You probably should do some load testing at volume to determine optimal configurations for you write patterns. 3. You indicate that the I/O is minimal. Why not create one thread that handles the Database work load. All the other threads could pass the work to the DbWorker thread. This would also eliminate DB contention, but would cause contention at the OS Mutex/Semaphore layer, which should be faster than disk based contention. Implementation of course could be done via a Shared Memory segment (if unix based) and some locking mechanisms. For a really slick high performance LL implemenation consider using an Unrolled Linked List. They are incredibly fast and provide 3-5 times faster performance than a simple LL, especially on multicore cpu's with large L1,L2 cache lines. HTH --- On Thu, 7/9/09, Rizzuto, Raymond wrote: > From: Rizzuto, Raymond > Subject: Re: [sqlite] multi-thread access to a db > To: "General Discussion of SQLite Database" > Date: Thursday, July 9, 2009, 8:58 AM > I have 4 servers, all with 4 > cores. This is to handle a volume of 10-20 million > orders per day. > > Most of the work load (~90%) is unrelated to the > database. In fact, I added the database just to allow > me to offload orders out of memory when they look done so > that the app doesn't run out of memory. It is a 32-bit > app, so it typically dies when it hits ~2.5 gig. > Moving to a 64-bit app was not an option. > > In approx .2% of the orders, I guess wrong, and have to > read the order back in from the db to process additional > activity. I could remove indices to improve write > performance, however the hit on read performance might > outweigh the gain. Since the processing is supposed to > be in near-realtime, the delay in that case might be > excessive. Still, it may be worth trying that. > > I am using begin/commit around the writes since each order > can require between 1 and 5 writes. > > Ken made a suggestion that I create separate db's for each > thread. Since the threads don't share data, that > approach would work for me, and eliminate all > contention. Each db would have the same schema, of > course. > > Ray > > -Original Message- > From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] > Sent: Wednesday, July 08, 2009 5:12 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] multi-thread access to a db > > > On 8 Jul 2009, at 9:28pm, Rizzuto, Raymond wrote: > > > If I remove the locking_mode=exclusive, I don't get > those errors. > > > > I'd appreciate any advice on how I can get the best > performance > > using multiple threads in my application, given that: > > > > > > 1. I need maximum performance. > > Spend at least five grand on a fast water-cooled box. > Use a version > of Unix/Linux compiled without support for anything you > don't need, > like printing. Create your database file on a RAM > disk. Write your > application as a command-line app, and don't run the GUI. > > > That is also why I need multiple threads > > May not help if they're all constantly accessing the > database. In > fact contention for access can slow the process down:
Re: [sqlite] multi-thread access to a db
I have 4 servers, all with 4 cores. This is to handle a volume of 10-20 million orders per day. Most of the work load (~90%) is unrelated to the database. In fact, I added the database just to allow me to offload orders out of memory when they look done so that the app doesn't run out of memory. It is a 32-bit app, so it typically dies when it hits ~2.5 gig. Moving to a 64-bit app was not an option. In approx .2% of the orders, I guess wrong, and have to read the order back in from the db to process additional activity. I could remove indices to improve write performance, however the hit on read performance might outweigh the gain. Since the processing is supposed to be in near-realtime, the delay in that case might be excessive. Still, it may be worth trying that. I am using begin/commit around the writes since each order can require between 1 and 5 writes. Ken made a suggestion that I create separate db's for each thread. Since the threads don't share data, that approach would work for me, and eliminate all contention. Each db would have the same schema, of course. Ray -Original Message- From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] Sent: Wednesday, July 08, 2009 5:12 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] multi-thread access to a db On 8 Jul 2009, at 9:28pm, Rizzuto, Raymond wrote: > If I remove the locking_mode=exclusive, I don't get those errors. > > I'd appreciate any advice on how I can get the best performance > using multiple threads in my application, given that: > > > 1. I need maximum performance. Spend at least five grand on a fast water-cooled box. Use a version of Unix/Linux compiled without support for anything you don't need, like printing. Create your database file on a RAM disk. Write your application as a command-line app, and don't run the GUI. > That is also why I need multiple threads May not help if they're all constantly accessing the database. In fact contention for access can slow the process down: you have seven threads, five of which are perpetually blocked. There's no one-size- fits-all solution to fast database access, it depends on the patterns of when data is available for writing, and how important the order the data was available is when you read. Sometimes you pile up all your data to be written into a text file, and another process (on a different computer ?!) works through the text file and does the writing. > 2. All threads need to write to the same db > 3. No other application needs access to the db > 4. I don't care about durability, just fast insert times since > reads are much less frequent. Use BEGIN TRANSACTION and COMMIT properly. This may be more important than multi-threading. It has a huge result. If reads are /really/ rare, it might be worth removing all indices on your database, and only creating an index just before you need to read, or even just executing the SELECT without any indices. Simon. IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multi-thread access to a db
Ken, Yes, I know contention is possible. My application spends most of its time doing non-db operations - it is likely that locking contention isn't currently a big factor, but that will change as I increase the # of threads. Since each thread reads and writes its own data, I can definitely use your suggestion of multiple databases. In my testing of performance of 3.6.x, I found that asynch + exclusive mode was 60x faster than just asynch. I'm not sure if that is representative, or just specific to my environment, but that is a key advantage for my environment. I did change my app last night to share the db connection between threads, and have per-thread prepared statements, and that seemed to work fairly well. I need to do more testing, however. I think mysql, postgress or oracle would be overkill for this application. I was considering using a flat file initially, but sqlite saves me a lot of coding to achieve the same results. Thanks for the suggestions. Ray -Original Message- From: Ken [mailto:kennethinbox-sql...@yahoo.com] Sent: Wednesday, July 08, 2009 6:40 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] multi-thread access to a db Ray, Using multiple threads you will have locking contention on the database. Only one thread is allowed to write at a time. If you need concurrent writing then create multiple databases or maybe look into a different DB platform like mysql, postgress or oracle. --- On Wed, 7/8/09, Rizzuto, Raymond wrote: > From: Rizzuto, Raymond > Subject: Re: [sqlite] multi-thread access to a db > To: "sqlite-users@sqlite.org" > Date: Wednesday, July 8, 2009, 3:28 PM > If I remove the > locking_mode=exclusive, I don't get those errors. > > I'd appreciate any advice on how I can get the best > performance using multiple threads in my application, given > that: > > > 1. I need maximum performance. That is also > why I need multiple threads > 2. All threads need to write to the same db > 3. No other application needs access to the db > 4. I don't care about durability, just fast insert > times since reads are much less frequent. > > Ray > > > From: Rizzuto, Raymond > Sent: Wednesday, July 08, 2009 3:27 PM > To: 'sqlite-users@sqlite.org' > Subject: multi-thread access to a db > > I have an application where I have 7 threads. > Each thread opens its own db connection object, but the > connections are to the same db. I am seeing sporadic > insert failures when a thread attempts to insert into the > db. sqlite3_errmsg returns this message: > > database is locked > > I am using sqlite3 version 3.6.1. I use the following > two pragmas to get the best insert performance (the db is > used exclusively by this application, and I don't need to > have the DB recover after an os crash or power fail): > > sqlite3_exec(result->db, > "pragma synchronous=off;", 0, 0, &zErrMsg); > sqlite3_exec(result->db, > "pragma locking_mode=exclusive;", 0, 0, &zErrMsg); > > I am using threading mode "multi-thread". > > Does mode=exclusive mean that the first thread that opens > and writes to the db locks out all other threads? > > Ray > > > > Ray Rizzuto > raymond.rizz...@sig.com > Susquehanna International Group > (610)747-2336 (W) > (215)776-3780 (C) > > > > > IMPORTANT: The information contained in this email and/or > its attachments is confidential. If you are not the intended > recipient, please notify the sender immediately by reply and > immediately delete this message and all its attachments. Any > review, use, reproduction, disclosure or dissemination of > this message or any attachment by an unintended recipient is > strictly prohibited. Neither this message nor any attachment > is intended as or should be construed as an offer, > solicitation or recommendation to buy or sell any security > or other financial instrument. Neither the sender, his or > her employer nor any of their respective affiliates makes > any warranties as to the completeness or accuracy of any of > the information contained herein or that this message or any > of its attachments is free of viruses. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use
Re: [sqlite] multi-thread access to a db
If I remove the locking_mode=exclusive, I don't get those errors. I'd appreciate any advice on how I can get the best performance using multiple threads in my application, given that: 1. I need maximum performance. That is also why I need multiple threads 2. All threads need to write to the same db 3. No other application needs access to the db 4. I don't care about durability, just fast insert times since reads are much less frequent. Ray ____ From: Rizzuto, Raymond Sent: Wednesday, July 08, 2009 3:27 PM To: 'sqlite-users@sqlite.org' Subject: multi-thread access to a db I have an application where I have 7 threads. Each thread opens its own db connection object, but the connections are to the same db. I am seeing sporadic insert failures when a thread attempts to insert into the db. sqlite3_errmsg returns this message: database is locked I am using sqlite3 version 3.6.1. I use the following two pragmas to get the best insert performance (the db is used exclusively by this application, and I don't need to have the DB recover after an os crash or power fail): sqlite3_exec(result->db, "pragma synchronous=off;", 0, 0, &zErrMsg); sqlite3_exec(result->db, "pragma locking_mode=exclusive;", 0, 0, &zErrMsg); I am using threading mode "multi-thread". Does mode=exclusive mean that the first thread that opens and writes to the db locks out all other threads? Ray Ray Rizzuto raymond.rizz...@sig.com Susquehanna International Group (610)747-2336 (W) (215)776-3780 (C) IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multi-thread access to a db
I have an application where I have 7 threads. Each thread opens its own db connection object, but the connections are to the same db. I am seeing sporadic insert failures when a thread attempts to insert into the db. sqlite3_errmsg returns this message: database is locked I am using sqlite3 version 3.6.1. I use the following two pragmas to get the best insert performance (the db is used exclusively by this application, and I don't need to have the DB recover after an os crash or power fail): sqlite3_exec(result->db, "pragma synchronous=off;", 0, 0, &zErrMsg); sqlite3_exec(result->db, "pragma locking_mode=exclusive;", 0, 0, &zErrMsg); I am using threading mode "multi-thread". Does mode=exclusive mean that the first thread that opens and writes to the db locks out all other threads? Ray Ray Rizzuto raymond.rizz...@sig.com Susquehanna International Group (610)747-2336 (W) (215)776-3780 (C) IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.6 vs 3.5
I was testing performance with 3.5.9 and 3.6.1 versions, which we have installed on our systems. With 3.5.9, writing 10,000 records of an int primary key and a 1k blob took 60 seconds when I specified both pragma synchronous=off; and pragma locking_mode=exclusive;. That time dropped to 1 second with 3.6.1. Does that seem reasonable? Ray Rizzuto raymond.rizz...@sig.com Susquehanna International Group (610)747-2336 (W) (215)776-3780 (C) IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search in archive
My apologies for being unclear. I meant the archive of all the sqlite-users messages. I prefer not to ask a question that has already been answered. The link http://www.mail-archive.com/sqlite-users@sqlite.org/ suggested by Adam DeVita does has such a function. I was accessing the archives via http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/, which doesn't have that functionality. -Original Message- From: Swithun Crowe [mailto:swit...@swithun.servebeer.com] Sent: Friday, June 19, 2009 3:16 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] search in archive Hello KN On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond" KN wrote: KN KN > Is it possible to have a search feature for the KN > archive? KN KN Which archive? I think Raymond means the sqlite-users archive. You could download all the txt.gz files, cat them together and then grep for what you want to find. You wouldn't have the threads, but it might be easier than checking all the threads via the web site. Perhaps it is possible to import the files into an email client which can recreate the messages and threads. Swithun. IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] shared library versions
I noticed that the sqlite.so (SLES 9 32-bit) is numbered .0.8.6 in 3.5.9 and 3.6.15 versions. This caused me some issues when I tried running an application build against 3.6.15, but the .so that it found was from 3.5.9. It partially worked, depending on the methods I called. Part of the reason for my question is that I may need to have both 3.5.9 and 3.6.15 libraries on a production system because our existing apps were built against 3.5.9, but I am adding an app built against 3.6.15. Recompiling the existing applications is undesirable at this time due to the retest cycle it would incure. I would appreciate any input on how sqlite is handing versioning so I can determine a deployment plan. Ray Ray Rizzuto raymond.rizz...@sig.com Susquehanna International Group (610)747-2336 (W) (215)776-3780 (C) IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] search in archive
Is it possible to have a search feature for the archive? I.e. rather than having to do a linear search through 18 archives for an answer to a question, have a google-like search across all of the archives? Ray Rizzuto raymond.rizz...@sig.com Susquehanna International Group (610)747-2336 (W) (215)776-3780 (C) IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asynchronous I/O Module For SQLite
Thanks for the answers. I did give compiling with 3.5.9 a try, but it looks like the changes in vfs are too great: sqlite3async.c: In function `asyncOpen': sqlite3async.c:1019: warning: initialization from incompatible pointer type sqlite3async.c: In function `asyncAccess': sqlite3async.c:1182: error: too many arguments to function sqlite3async.c: At top level: sqlite3async.c:1290: warning: initialization from incompatible pointer type sqlite3async.c:1291: warning: initialization from incompatible pointer type sqlite3async.c:1292: warning: initialization from incompatible pointer type sqlite3async.c:1293: warning: initialization from incompatible pointer type sqlite3async.c:1294: warning: initialization from incompatible pointer type sqlite3async.c:1295: warning: initialization from incompatible pointer type sqlite3async.c:1296: warning: initialization from incompatible pointer type sqlite3async.c:1297: warning: initialization from incompatible pointer type sqlite3async.c:1299: warning: initialization from incompatible pointer type I was able to build and use the asynch i/o module with 3.6.15 without too much difficulty. I need to re-verify this, but the 3.6.15 version seems to be faster than the 3.5.9 version. Also, I get the best performance (without the asynch io module coded in) is with these two pragmas together: sqlite3_exec(db, "pragma synchronous=off;", 0, 0, &zErrMsg); sqlite3_exec(db, "pragma locking_mode=exclusive;", 0, 0, &zErrMsg); Since I only plan on using this db from a single process, and don't need to handle the case of power failure or other hardware issues, this combo seems to make the most sense. My only concern is to get data out of memory as quickly as possible, and be able to restore it back to memory. The latter is a very infrequent occurrence - less than 1% of the time. Ray -Original Message- From: Dan [mailto:danielk1...@gmail.com] Sent: Thursday, June 18, 2009 3:00 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Asynchronous I/O Module For SQLite On Jun 18, 2009, at 11:45 PM, Rizzuto, Raymond wrote: > Simon, > > I appreciate your input, but I don't think I have provided enough > details on the constraints my application has to meet for you to > evaluate whether asynch i/o is a good solution or not for me. > > I don't think it is practical for me to get into that level of > detail, even if I wanted to. I really just want some clarification > on how to use asynch i/o module, as described on > http://sqlite.org/asyncvfs.html > . My questions are very simple: > > 1) sqlite3async.c and sqlite3async.h aren't in the amalgamation's. > I can get them from sqlite-3.6.15.tar.gz, but I wanted to verify > that this was the correct procedure, and that those 2 files are all > I need. That's all you need. > 2) Since these two files are compiled into the app, and seem to > stand apart from the core sqlite3, I was curious if I could use them > with sqlite3 version 3.5.9 since that version is already in use in > my organization. Might very well work on unix systems. I think there were some incompatibilities with the windows OS layer that were fixed more recently than 3.5.9 though. Dan. > If no one can answer those questions, I'll just have to give it a > try, and see if that meets my needs or not. It may be that sqlite3, > with or without the asynch i/o module, isn't the right choice for my > needs. > > Ray > > -Original Message- > From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] > Sent: Thursday, June 18, 2009 12:10 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Asynchronous I/O Module For SQLite > > > On 18 Jun 2009, at 2:15pm, Rizzuto, Raymond wrote: > >> I'm actually memory limited, which is why I am looking at moving >> infrequently needed object to persistent store with sqlite3. I like >> the idea of using the asynchronous i/o module so that the writing >> can be done in a background thread, but still allow the main thread >> to retrieve data that has been written or is in queue to be written. > > Aren't those two things in conflict ? Caching and multiple concurrent > threads are two features which chew up a great deal of memory: a > cache /is/ memory, and each independent thread requires its own > working-memory. If you're trying to minimise memory-usage you tend to > have no caching and single threads. > > Simon. > > > IMPORTANT: The information contained in this email and/or its > attachments is confidential. If you are not the intended recipient, > please notify the sender immediately by reply and immediately delete > this message and all its attachments. Any review, use, reproduction, > disclosure or
Re: [sqlite] Asynchronous I/O Module For SQLite
Simon, I appreciate your input, but I don't think I have provided enough details on the constraints my application has to meet for you to evaluate whether asynch i/o is a good solution or not for me. I don't think it is practical for me to get into that level of detail, even if I wanted to. I really just want some clarification on how to use asynch i/o module, as described on http://sqlite.org/asyncvfs.html. My questions are very simple: 1) sqlite3async.c and sqlite3async.h aren't in the amalgamation's. I can get them from sqlite-3.6.15.tar.gz, but I wanted to verify that this was the correct procedure, and that those 2 files are all I need. 2) Since these two files are compiled into the app, and seem to stand apart from the core sqlite3, I was curious if I could use them with sqlite3 version 3.5.9 since that version is already in use in my organization. If no one can answer those questions, I'll just have to give it a try, and see if that meets my needs or not. It may be that sqlite3, with or without the asynch i/o module, isn't the right choice for my needs. Ray -Original Message- From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] Sent: Thursday, June 18, 2009 12:10 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Asynchronous I/O Module For SQLite On 18 Jun 2009, at 2:15pm, Rizzuto, Raymond wrote: > I'm actually memory limited, which is why I am looking at moving > infrequently needed object to persistent store with sqlite3. I like > the idea of using the asynchronous i/o module so that the writing > can be done in a background thread, but still allow the main thread > to retrieve data that has been written or is in queue to be written. Aren't those two things in conflict ? Caching and multiple concurrent threads are two features which chew up a great deal of memory: a cache /is/ memory, and each independent thread requires its own working-memory. If you're trying to minimise memory-usage you tend to have no caching and single threads. Simon. IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asynchronous I/O Module For SQLite
I tried the pragma synchronous=off. It has no significant effect on db on an nfs mounted file system, but does improve access to a db on a local file system. I still would like to compare the pragma to the asynchronous i/o module, if the latter is still supported. -Original Message- From: D. Richard Hipp [mailto:d...@hwaci.com] Sent: Thursday, June 18, 2009 9:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Asynchronous I/O Module For SQLite On Jun 18, 2009, at 9:15 AM, Rizzuto, Raymond wrote: > I'm actually memory limited, which is why I am looking at moving > infrequently needed object to persistent store with sqlite3. I like > the idea of using the asynchronous i/o module so that the writing > can be done in a background thread, but still allow the main thread > to retrieve data that has been written or is in queue to be written. You do not need the async I/O package for this. Simply set "PRAGMA synchronous=OFF" and it will behave as you desire (assuming your operating system implements a filesystem cache). > > > My main questions are about that module. I am a bit confused about > it since it is not distributed in the main amalgamations. I wanted > to make sure that I can just pull the 2 files from the source > tarball, or if there are other files I would also need. > > Additionally, since asynchronous i/o seems to sit apart from the > main sqlite3, I was wondering if it would be possible to use with an > older (3.5.9) version of sqlite3 that we already use in house. D. Richard Hipp d...@hwaci.com IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asynchronous I/O Module For SQLite
I'm actually memory limited, which is why I am looking at moving infrequently
needed object to persistent store with sqlite3. I like the idea of using the
asynchronous i/o module so that the writing can be done in a background thread,
but still allow the main thread to retrieve data that has been written or is in
queue to be written.
My main questions are about that module. I am a bit confused about it since it
is not distributed in the main amalgamations. I wanted to make sure that I can
just pull the 2 files from the source tarball, or if there are other files I
would also need.
Additionally, since asynchronous i/o seems to sit apart from the main sqlite3,
I was wondering if it would be possible to use with an older (3.5.9) version of
sqlite3 that we already use in house.
Ray
-Original Message-
From: D. Richard Hipp [mailto:d...@hwaci.com]
Sent: Wednesday, June 17, 2009 7:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Asynchronous I/O Module For SQLite
On Jun 17, 2009, at 7:18 PM, Rizzuto, Raymond wrote:
> I am interested in using sqlite3 for an application where I don't
> have a requirement that data persists after the using process ends.
> The db is used as a backing store for infrequently used records.
So use an in-memory database. Open with sqlite3_open(":memory:",
&db). Or if your content is too big for memory, use a temporary
database whose name is the empty string: sqlite3_open("", &db).
D. Richard Hipp
d...@hwaci.com
IMPORTANT: The information contained in this email and/or its attachments is
confidential. If you are not the intended recipient, please notify the sender
immediately by reply and immediately delete this message and all its
attachments. Any review, use, reproduction, disclosure or dissemination of this
message or any attachment by an unintended recipient is strictly prohibited.
Neither this message nor any attachment is intended as or should be construed
as an offer, solicitation or recommendation to buy or sell any security or
other financial instrument. Neither the sender, his or her employer nor any of
their respective affiliates makes any warranties as to the completeness or
accuracy of any of the information contained herein or that this message or any
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Asynchronous I/O Module For SQLite
I am interested in using sqlite3 for an application where I don't have a requirement that data persists after the using process ends. The db is used as a backing store for infrequently used records. Performance requirements are ~1500 "transactions" per second. I would like to use the asynch i/o module since it sounds like it would meet my requirements. However, I have a few questions: - The files for asynch i/o aren't in the amalgamations, but only in the tarball of the complete source tree. Can I just pull out the 2 files I need from that, and use it with the installed sqlite3? - We are currently using sqlite 3.5.9 in house. Can I use the asynchronous i/o module with that version? Thanks for any assistance, Ray Ray Rizzuto raymond.rizz...@sig.com Susquehanna International Group (610)747-2336 (W) (215)776-3780 (C) IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
