[sqlite] llvm/ppc compile bug
Just a note to share a problem I ran into recently: Compiling sqlite 3.6.22 with -arch ppc -Os on the llvm that ships with Xcode 3.2.1, the sqlite3AtoF function appears to have an infinite loop. If you compile the sqlite3 command line tool in this way, just executing "select round(1234);" will cause it to hang. This version of llvm reports itself as i686-apple-darwin10-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5646) (LLVM build 2206) I haven't checked to see if there's a newer llvm which fixes this. Apple dudes: this is bugreporter #7599241. Hope this saves someone out there some trouble. :) -D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parallelizing an update
arg sorry I miss your LRP (Long Running Process), On Tue, Feb 2, 2010 at 11:55 PM, Sylvain Pointeau < sylvain.point...@gmail.com> wrote: > Please just don't forget that sqlite lock the file at each update, > running multiple processes will not improve at all the speed, I think it > will be even worst. > The only improvement you can do is to group your update in a single > transaction, and avoid to run one process (sqlite3) for just 1 update. > > On Tue, Feb 2, 2010 at 10:23 PM, Robert Citek wrote: > >> On Tue, Feb 2, 2010 at 3:13 PM, John Elrick >> wrote: >> > Robert Citek wrote: >> >> Are there some white papers or examples of how to do updates in >> >> parallel using sqlite? >> > >> > I could be misunderstanding your requirements, but this sounds a little >> > like Map Reduce: >> > >> > http://labs.google.com/papers/mapreduce.html >> >> Not sure, but quite possibly. I'm reading up more on mapreduce. >> >> > The only point I'd question is your assertion that you could speed up >> > the overall time by running more than one long running process at the >> > same time. You *might* be able to do so up to the limit of the cores in >> > the machine or by distributing the load over many machines, however, the >> > implication to me of a long running process is something that is >> > consuming large amounts of CPU time. >> >> What I mean is that long_running_process (LRP) takes a long time to >> run relative to updating a record in a sqlite database. LRP's >> bottleneck could be CPU or I/O or network lag or something else. I'm >> also assuming that if multiple LRPs are running, then they will >> negligibly compete with each other for resources. For example, if the >> LRP is CPU-limited and the machine has only 4 cores, then there will >> be at most 4 LRPs running at any given time. >> >> > It is possible that running >> > multiple processes per processor could actually increase the total >> > amount of time due to process swap overhead. >> >> The ideal would be to have a general framework that works on a single >> CPU, multiple-CPUs/Cores, and multiple machines. >> >> A google search for mapreduce led to this project: >> >> http://github.com/erikfrey/bashreduce >> >> I'll probably give that a try if for no other reason than to get more >> familiar with mapreduce. >> >> Thanks, John. >> >> Regards, >> - Robert >> ___ >> 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] parallelizing an update
Please just don't forget that sqlite lock the file at each update, running multiple processes will not improve at all the speed, I think it will be even worst. The only improvement you can do is to group your update in a single transaction, and avoid to run one process (sqlite3) for just 1 update. On Tue, Feb 2, 2010 at 10:23 PM, Robert Citek wrote: > On Tue, Feb 2, 2010 at 3:13 PM, John Elrick > wrote: > > Robert Citek wrote: > >> Are there some white papers or examples of how to do updates in > >> parallel using sqlite? > > > > I could be misunderstanding your requirements, but this sounds a little > > like Map Reduce: > > > > http://labs.google.com/papers/mapreduce.html > > Not sure, but quite possibly. I'm reading up more on mapreduce. > > > The only point I'd question is your assertion that you could speed up > > the overall time by running more than one long running process at the > > same time. You *might* be able to do so up to the limit of the cores in > > the machine or by distributing the load over many machines, however, the > > implication to me of a long running process is something that is > > consuming large amounts of CPU time. > > What I mean is that long_running_process (LRP) takes a long time to > run relative to updating a record in a sqlite database. LRP's > bottleneck could be CPU or I/O or network lag or something else. I'm > also assuming that if multiple LRPs are running, then they will > negligibly compete with each other for resources. For example, if the > LRP is CPU-limited and the machine has only 4 cores, then there will > be at most 4 LRPs running at any given time. > > > It is possible that running > > multiple processes per processor could actually increase the total > > amount of time due to process swap overhead. > > The ideal would be to have a general framework that works on a single > CPU, multiple-CPUs/Cores, and multiple machines. > > A google search for mapreduce led to this project: > > http://github.com/erikfrey/bashreduce > > I'll probably give that a try if for no other reason than to get more > familiar with mapreduce. > > Thanks, John. > > Regards, > - Robert > ___ > 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] parallelizing an update
On Tue, Feb 2, 2010 at 3:13 PM, John Elrick wrote: > Robert Citek wrote: >> Are there some white papers or examples of how to do updates in >> parallel using sqlite? > > I could be misunderstanding your requirements, but this sounds a little > like Map Reduce: > > http://labs.google.com/papers/mapreduce.html Not sure, but quite possibly. I'm reading up more on mapreduce. > The only point I'd question is your assertion that you could speed up > the overall time by running more than one long running process at the > same time. You *might* be able to do so up to the limit of the cores in > the machine or by distributing the load over many machines, however, the > implication to me of a long running process is something that is > consuming large amounts of CPU time. What I mean is that long_running_process (LRP) takes a long time to run relative to updating a record in a sqlite database. LRP's bottleneck could be CPU or I/O or network lag or something else. I'm also assuming that if multiple LRPs are running, then they will negligibly compete with each other for resources. For example, if the LRP is CPU-limited and the machine has only 4 cores, then there will be at most 4 LRPs running at any given time. > It is possible that running > multiple processes per processor could actually increase the total > amount of time due to process swap overhead. The ideal would be to have a general framework that works on a single CPU, multiple-CPUs/Cores, and multiple machines. A google search for mapreduce led to this project: http://github.com/erikfrey/bashreduce I'll probably give that a try if for no other reason than to get more familiar with mapreduce. Thanks, John. Regards, - Robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parallelizing an update
Robert Citek wrote:
> Are there some white papers or examples of how to do updates in
> parallel using sqlite?
>
> I have a large dataset in sqlite that I need to process outside of
> sqlite and then update the sqlite database. The process looks
> something like this:
>
> sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
> while read rowid item ; do
> status=$(long_running_process "${item}" )
> sqlite3 sample.db "update foo set status=${status} where rowid=${rowid} ;"
> done
>
> Because long_running_process takes a long time, I could speed up the
> overall time by running more than one long_running_process at the same
> time. One way to do this would be to segment the data and run a
> separate process on each segment. For the update each process would
> collect the status data "outside" of the sample.db, e.g in a separate
> database. When all the processes have finished, the parent process
> would attach the separate databases and update the original database.
> When all is done, the parent process would clean up the ancillary
> databases.
>
I could be misunderstanding your requirements, but this sounds a little
like Map Reduce:
http://labs.google.com/papers/mapreduce.html
The only point I'd question is your assertion that you could speed up
the overall time by running more than one long running process at the
same time. You *might* be able to do so up to the limit of the cores in
the machine or by distributing the load over many machines, however, the
implication to me of a long running process is something that is
consuming large amounts of CPU time. It is possible that running
multiple processes per processor could actually increase the total
amount of time due to process swap overhead.
FWIW,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update/Delete problem
On Tue, 2 Feb 2010 09:20:29 +0100, Carsten Peyk wrote: >Hi, > > I have tried closing and opening the applications, > but the problem is still there. I have tried to > commit the changes for SQLLite Database Browser, > but SQLLite is responding with the message "No > active transactions". I'm sure that it is the same > database, because I rename it and tried starting > Spiceworks, whoch immediately created another .db > file besides the one I renamed. I think you're > right that is has something to do with transaction > isolation, but I don't know how to resolve it. In some database browsers, you can edit cells in the data grid without updating the database at the same time. They usually have some "Update" button, which writes changed rows back. They may even ignore any changes when you close the application before clicking that button. Some database browsers might only be able to update the database from edits in the data grid when your table contains a primary key. I don't know how Spiceworks and SQLite Database Browser behave in that respect, but perhaps this help you analyse a bit further. >Carsten -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parallelizing an update
On Tue, Feb 02, 2010 at 09:23:36AM +0100, Sylvain Pointeau wrote:
> On Mon, Feb 1, 2010 at 5:16 PM, Nicolas Williams
> wrote:
> > Now to parallelize this:
> >
> > function par_updates {
> I would be very interested to see some benchmark, just to see.
Feel free to write the relevant program, schema, SQL statements and run
benchmarks against it. What performance you get will depend on: what
the long-running processes are doing (are they CPU bound or I/O bound?)
and what CPU or I/O resources you have.
Note: I didn't test par_updates(); I've written that sort of script
many times before, so the gist of it is right.
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] journal files
Actually, I believe the entire 28 byte header is zeroed, not just the first 4 bytes. See the zeroJournalHdr() function in pager.c for details. -Shane On Tue, Feb 2, 2010 at 7:09 AM, Pavel Ivanov wrote: > What do you want to see in journal files? You can execute 'PRAGMA > journal_mode = persist' and all information in journal file except > first 4 bytes will be left on disk for you. Is it enough? > > Pavel > > On Tue, Feb 2, 2010 at 7:00 AM, rishabh wrote: > > > > hey, > > > > I am coding for an application wherein i need to check the journal files > as > > in i dont want it to get deleted after the commit. how to go about it? > where > > in the Sqlite3.c code can i edit it. > > > > also, is it possible to customize the sqlite code for the journal file a > bit > > as per my needs? > > > > thanx > > -- > > View this message in context: > http://old.nabble.com/journal-files-tp27419280p27419280.html > > Sent from the SQLite mailing list archive at Nabble.com. > > > > ___ > > 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] journal files
What do you want to see in journal files? You can execute 'PRAGMA journal_mode = persist' and all information in journal file except first 4 bytes will be left on disk for you. Is it enough? Pavel On Tue, Feb 2, 2010 at 7:00 AM, rishabh wrote: > > hey, > > I am coding for an application wherein i need to check the journal files as > in i dont want it to get deleted after the commit. how to go about it? where > in the Sqlite3.c code can i edit it. > > also, is it possible to customize the sqlite code for the journal file a bit > as per my needs? > > thanx > -- > View this message in context: > http://old.nabble.com/journal-files-tp27419280p27419280.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > 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] journal files
hey, I am coding for an application wherein i need to check the journal files as in i dont want it to get deleted after the commit. how to go about it? where in the Sqlite3.c code can i edit it. also, is it possible to customize the sqlite code for the journal file a bit as per my needs? thanx -- View this message in context: http://old.nabble.com/journal-files-tp27419280p27419280.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update/Delete problem
I never said that it has something to do with transaction isolation - if you have no transactions active then there's nothing to isolate. Again: if after restart of applications they still see their own changes and don't see other's changes then you definitely working with different databases. BTW, the best way to check what is actually in the database and what is made up by applications in some way is to look into the database with command line utility which is downloadable from SQLite's site. Pavel On Tue, Feb 2, 2010 at 3:20 AM, Carsten Peyk wrote: > Hi, > > I have tried closing and opening the applications, but the problem is still > there. I have tried to commit the changes for SQLLite Database Browser, but > SQLLite is responding with the message "No active transactions". I'm sure > that it is the same database, because I rename it and tried starting > Spiceworks, whoch immediately created another .db file besides the one I > renamed. I think you're right that is has something to do with transaction > isolation, but I don't know how to resolve it. > > Carsten > > > -Oprindelig meddelelse- > Fra: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > På vegne af Pavel Ivanov > Sendt: 1. februar 2010 19:01 > Til: General Discussion of SQLite Database > Emne: Re: [sqlite] Update/Delete problem > > Are you sure that Spiceworks or SQLLite Database Browser commit your > changes? What happens if you close those applications and restart it - > will they see their own changes? If they will and they still won't see > other application's changes then you can be sure that you're looking > at different databases. > > Pavel > > On Mon, Feb 1, 2010 at 12:34 PM, Carsten Peyk wrote: >> Hi, >> >> I'm completely new to SQLLite, and I'm very sorry if I ask a trivial >> question. >> >> I'm using Spiceworks which again uses SQLLite as backend database. I've made >> some updates and deletions to the records using the freeware SQLLite >> Database Browser application. If I the open Spiceworks application the >> changes are not reflected. Furthermore changes made in the Spiceworks >> application are not reflected when opening the database in SQLLite Database >> Browser application. I'm sure that it is the same database that I'm looking >> at. I can open the database in either of the two applications, and they show >> different results. Somehow it seems that the changes I made are still >> pending - uncommitted. Is this possible, and what can I do to "reset" any >> changes I've made. >> >> Thanks. >> >> Kind regards >> Carsten >> Before printing, think about the environment >> >> >> >> >> ___ >> 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] parallelizing an update
I would be very interested to see some benchmark, just to see.
On Mon, Feb 1, 2010 at 5:16 PM, Nicolas Williams
wrote:
> On Sat, Jan 30, 2010 at 10:36:56AM +0100, Sylvain Pointeau wrote:
> > echo "begin transaction" >> update.sql
> >
> > sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
> > while read rowid item ; do
> > status=$(long_running_process "${item}" )
> > echo "update foo set status=${status} where rowid=${rowid} ;" >>
> update.sql
> > done
> >
> > echo "commit transaction" >> update.sql
> >
> > sqlite3 sample.db < update.sql
>
> More aesthetic (since it seems to matter :)
>
> (
> echo "begin transaction"
> sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
>while read rowid item ; do
>status=$(long_running_process "${item}" )
>echo "update foo set status=${status} where rowid=${rowid} ;"
> done
> echo "commit transaction"
> ) | sqlite3 sample.db
>
> Now to parallelize this:
>
> function par_updates {
>typeset -i n
>n=$1
>shift
>(
>trap "((n++))" CHLD
>echo "begin transaction"
> sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
>while read rowid item
>do
> while ((n == 0))
>do
>sleep 1
>done
>(echo "update foo set status=$("$@") where rowid=$rowid;") &
>((n--))
>done
>echo "commit transaction"
>) | sqlite3 sample.db
> }
>
> You should run this like so:
>
> par_updates 20 long_running_program [arguments]
>
> It should run up to 20 processes to generate the updates. And it should
> batch all updates into one transaction.
>
> I've not tested this, but it should work with recent versions of ksh93
> and bash. (Older versions of ksh93 might not handle the SIGCHLD trap
> correctly, IIRC.)
>
> Nico
> --
> ___
> 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] Update/Delete problem
Hi, I have tried closing and opening the applications, but the problem is still there. I have tried to commit the changes for SQLLite Database Browser, but SQLLite is responding with the message "No active transactions". I'm sure that it is the same database, because I rename it and tried starting Spiceworks, whoch immediately created another .db file besides the one I renamed. I think you're right that is has something to do with transaction isolation, but I don't know how to resolve it. Carsten -Oprindelig meddelelse- Fra: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] På vegne af Pavel Ivanov Sendt: 1. februar 2010 19:01 Til: General Discussion of SQLite Database Emne: Re: [sqlite] Update/Delete problem Are you sure that Spiceworks or SQLLite Database Browser commit your changes? What happens if you close those applications and restart it - will they see their own changes? If they will and they still won't see other application's changes then you can be sure that you're looking at different databases. Pavel On Mon, Feb 1, 2010 at 12:34 PM, Carsten Peyk wrote: > Hi, > > I'm completely new to SQLLite, and I'm very sorry if I ask a trivial question. > > I'm using Spiceworks which again uses SQLLite as backend database. I've made > some updates and deletions to the records using the freeware SQLLite Database > Browser application. If I the open Spiceworks application the changes are not > reflected. Furthermore changes made in the Spiceworks application are not > reflected when opening the database in SQLLite Database Browser application. > I'm sure that it is the same database that I'm looking at. I can open the > database in either of the two applications, and they show different results. > Somehow it seems that the changes I made are still pending - uncommitted. Is > this possible, and what can I do to "reset" any changes I've made. > > Thanks. > > Kind regards > Carsten > Before printing, think about the environment > > > > > ___ > 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
