Re: [sqlite] Sporadic freezes of sqlite
I'm using sqlite as an embedded engine in the application acting like server for other applications. Pavel On Sun, Apr 26, 2009 at 4:17 PM, John Stanton wrote: > Sqlite does not use a server. It is embedded in the application. > > Pavel Ivanov wrote: >> Unfortunately these two options are not for me. >> I'm not in control of servers hardware, so my application should work >> on the given servers and nobody will adjust them for my application. >> And about first option, I believe what you say is to use in-memory >> database for intensive operations. But all my application consists of >> these intensive operations alone. So that you can reasonably argue >> that I should reject the idea of on-disk database and work totally in >> memory. And i can agree with you. But there's a couple of requirements >> that make things difficult. And the main of it is application should >> have some durability and survive power outages, crashes and reboots. >> "Some" because I can sacrifice for example everything that was written >> up to 5 minutes before power outage, but everything else should stay. >> And at this point all idea of in-memory database is ruined and I have >> to cope somehow with problems of frequent writings to disk. >> >> >> Pavel >> >> On Fri, Apr 24, 2009 at 6:13 AM, January Weiner >> wrote: >> I have an application written using sqlite. It writes into the database very intensively. And I noticed that it works nice and very fast but from time to time it just freezes for several seconds (I've registered freezes up to 8 secs). After some tracing of sqlite code >>> I had the same problem. Also, it was increasing with database size. >>> Depending on your environment, work procedure and whether you want >>> speed or security, there are two things that work beautifully for me: >>> >>> 1) do the intentsive work on a db copy that sits on a ramdisk (or >>> tmpfs filesystem). I do that if I have to create a new database or >>> rebuild this from scratch, and since the process is supervised, there >>> is not really a danger of data loss. >>> >>> 2) for normal operation, I use a software RAID from flash disks, which >>> is not as fast (for data transfer) as a hard drive or SSD, but it is >>> has a response time better by an order of magnitude (at least) than >>> even a good hard drive. >>> >>> j. >>> >>> -- >>> -Dr. January Weiner 3 -+--- >>> Inst. of Bioinformatics, UKM, Univ. of Muenster | Von-Esmarch-str. 54 >>> (+49) (251) 83 53002 | D48149 Münster >>> http://www.compgen.uni-muenster.de/ | Germany >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sporadic freezes of sqlite
Sqlite does not use a server. It is embedded in the application. Pavel Ivanov wrote: > Unfortunately these two options are not for me. > I'm not in control of servers hardware, so my application should work > on the given servers and nobody will adjust them for my application. > And about first option, I believe what you say is to use in-memory > database for intensive operations. But all my application consists of > these intensive operations alone. So that you can reasonably argue > that I should reject the idea of on-disk database and work totally in > memory. And i can agree with you. But there's a couple of requirements > that make things difficult. And the main of it is application should > have some durability and survive power outages, crashes and reboots. > "Some" because I can sacrifice for example everything that was written > up to 5 minutes before power outage, but everything else should stay. > And at this point all idea of in-memory database is ruined and I have > to cope somehow with problems of frequent writings to disk. > > > Pavel > > On Fri, Apr 24, 2009 at 6:13 AM, January Weiner > wrote: > >>> I have an application written using sqlite. It writes into the >>> database very intensively. And I noticed that it works nice and very >>> fast but from time to time it just freezes for several seconds (I've >>> registered freezes up to 8 secs). After some tracing of sqlite code >>> >> I had the same problem. Also, it was increasing with database size. >> Depending on your environment, work procedure and whether you want >> speed or security, there are two things that work beautifully for me: >> >> 1) do the intentsive work on a db copy that sits on a ramdisk (or >> tmpfs filesystem). I do that if I have to create a new database or >> rebuild this from scratch, and since the process is supervised, there >> is not really a danger of data loss. >> >> 2) for normal operation, I use a software RAID from flash disks, which >> is not as fast (for data transfer) as a hard drive or SSD, but it is >> has a response time better by an order of magnitude (at least) than >> even a good hard drive. >> >> j. >> >> -- >> -Dr. January Weiner 3 -+--- >> Inst. of Bioinformatics, UKM, Univ. of Muenster | Von-Esmarch-str. 54 >> (+49) (251) 83 53002| D48149 Münster >> http://www.compgen.uni-muenster.de/ | Germany >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused
P Kishor wrote: > On Fri, Apr 24, 2009 at 5:27 AM, John Stanton wrote: > >> Sqlte provides COMMIT and ROLLBACK unlike Berkeley. If you can get by >> without the advanced features of Sqlite, then use Berkely and take >> advantage of its simplicity and faster execution. >> > > BDB does support transactions... > > http://www.oracle.com/technology/documentation/berkeley-db/xml/gsg_xml_txn/cxx/usingtxns.html > > In fact, BDB acts as the (a) transactional layer in MySQL (the > alternative is InnoDB). > > Of course, other advantages of SQLite still apply. A key-value > metaphor can only be pushed so far. > > Guess what make BDB run faster - no transactions. If you are not using transactions BDB mght be for you. Personally as a long time user of BDB in various ways I have a very poor opinion of it. It has a very different application to Sqlite. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question about using SQLite with Windows Forms application (VS 2005 C++)
Vinnie pisze: >> From: wiktor >> Subject: [sqlite] Newbie question about using SQLite with >> Windows Forms application (VS 2005 C++) >> I'm trying to build a win form application that uses >> sqlite. I have problems with making it work. I would like >> to have the sqlite source included in my project (as .h file >> or dll) - sth similar to (but done by a function) >> http://www.sqlite.org/quickstart.html. As I have read on >> internet sources it shall be possible. >> > > I have never used Windows Forms but from what I understand it is a user > interface toolkit for .NET. So you will need to access SQLite from .NET. > There are a few ways of going about this. > > Here is one .NET wrapper for SQLite: > http://www.phpguru.org/static/SQLite.NET.html > > The SQLite website has some instructions for building SQLite with Visual > Studio .NET: > http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet > > Hope this helps! > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Hi Vinnie, thank you for your links. Unfortunately, I am not familiar with .NET and looking for sht more C++ like ;) Here is the solution which I was provided elsewhere: http://support.microsoft.com/kb/920770 and after installing the hotfix you shall use this link: http://news.thedigitalmachine.com/2007/03/28/set-up-sqlite-static-lib-and-a-c-wrapper-in-15-minutes/comment-page-1/#comment-12136 In my machine everything works fine. Since, I am a begginer in sqlite, can you tell me what is the role of wrappers? From what I have read at sqlite.org it is possible to fetch the data from database by executing sqlite_open, sqlite_exec and sqlite_close.Am I wrong? Regards, Wiktor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] corrupt database recovery
Cool solution. On Sun, Apr 26, 2009 at 6:39 AM, Jim Wilcoxson wrote: > You could do a binary search to find the highest accessible rowid: > > select rowid where rowid = 2147483647 (fails) > select rowid where rowid = 1073741824 (fails) > ... > select rowid where rowid = 65536 (fails) > select rowid where rowid = 32768 (works!) > select rowid where rowid = 49152 (works!) > > Within 32 selects, you will find the highest accessible rowid. Then > do an insert from select * where rowid <= XXX. > > Jim > > On 4/25/09, Gene wrote: >> You are exactly right John...that is indeed what the code looks >> like...except we have over 25 columns (it's a flat table). >> >> We've already fixed the bad code, but there are some customers who have old >> versions...it didn't break very often with the old code, but it does still >> did. >> >> I haven't tried a select Min or max on the row id but a select count(*) >> returns an error...that's how I know I need to do the row by row recovery >> method. Select * from mytable also returns an error. >> >> The tables usually have tens of thousands of rows, sometimes over a couple >> hundred thousand but that's rare. >> >> What seems to work is that I do a select * from myTable where rowId = 'X' >> incing X until I get an error. After I get the error, every row higher then >> X also returns an error. So as soon as I get an error, I stop trying to >> recover more rows. > > -- > Software first. Software lasts! > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Jim Dodgen j...@dodgen.us ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] corrupt database recovery
You could do a binary search to find the highest accessible rowid: select rowid where rowid = 2147483647 (fails) select rowid where rowid = 1073741824 (fails) ... select rowid where rowid = 65536 (fails) select rowid where rowid = 32768 (works!) select rowid where rowid = 49152 (works!) Within 32 selects, you will find the highest accessible rowid. Then do an insert from select * where rowid <= XXX. Jim On 4/25/09, Gene wrote: > You are exactly right John...that is indeed what the code looks > like...except we have over 25 columns (it's a flat table). > > We've already fixed the bad code, but there are some customers who have old > versions...it didn't break very often with the old code, but it does still > did. > > I haven't tried a select Min or max on the row id but a select count(*) > returns an error...that's how I know I need to do the row by row recovery > method. Select * from mytable also returns an error. > > The tables usually have tens of thousands of rows, sometimes over a couple > hundred thousand but that's rare. > > What seems to work is that I do a select * from myTable where rowId = 'X' > incing X until I get an error. After I get the error, every row higher then > X also returns an error. So as soon as I get an error, I stop trying to > recover more rows. -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sort order with umlauts
"Lukas Haase" wrote in message news:gt0078$n7...@ger.gmane.org > Now I have the problem that I have also words with umlauts. Now they > are sorted this way: > > But I need the umlauts treated as their respective vovels, i.e.: http://sqlite.org/c3ref/create_collation.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] corrupt database recovery
Hi Gene, On Apr 25, 2009, at 3:47 PM, Gene wrote: > Every now and again, we have a database that gets corrupt in the > field (bad > coding on our end, not sqlite). How do you corrupt a database with bad coding? Just curious... -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sort order with umlauts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Lukas Haase wrote: > Now I have the problem that I have also words with umlauts. To get correct sorting and case conversion then the best method is to use the ICU SQLite extension. You can read about it at http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt If you don't care too much about correctness, locales etc then search the archives of this mailing list where various people have posted approaches that are smaller/faster than ICU at the expense of accuracy and coverage. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkn0B2QACgkQmOOfHg372QSdnACg5UL6GSZfDQ5rm2htvlvS5BID CxcAn39S75/PpKRYX9fB4YQtxxZsVPN4 =nfC5 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
