Re: [sqlite] Reading across processes on Solaris
Joe Wilson <[EMAIL PROTECTED]> wrote: > > I'll continue investigating and try to get to the bottom of this. Thanks > > for the sanity-check. > > Might there be some autocommit/sql statement batching difference for the > driver or your app on different platforms? No; one of the beauties of developing in Python is that it minimizes the amount of OS-specific code you need to write. I have to apologize for the misdiagnosis. I accepted my colleague's diagnosis of "writes from writer not appearing in view of database from reader on Solaris only" at face value. After some hours of attempting to reproduce the problem, it now appears they were showing up all along. So: sqlite list 1, list subscriber 0. Thanks again, Joe. Charles -- --- Charles Cazabon <[EMAIL PROTECTED]> --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Index creation
I did two things: 1. Catted the file to /dev/null 2. Reduced cache_size from 1 down to 2000 I don't know what actions enabled the creation of the index. Before I did these two things, the index would not create. Joe Wilson wrote: > > --- Joe Wilson <[EMAIL PROTECTED]> wrote: >> > improved dramatically. So I attempted the creation of the index off >> hours on >> > the production system, and after 4 hours no index. I can't detect any >> > activity at all. The journal file and the .db file just sit at the same >> size >> > for 4 hours. Why is this failing? It seems like it is just sitting >> there >> > doing nothing. When I created the test index, I noticed the journal >> file >> > changing and the .db file changing during the 2.5 hours to create. On >> the >> > production .db file, nothing is happening. I have all associated >> processes >> > killed that ineract with the db file, so I know it is not locked. >> >> I assume that the copied "test" database was indexed immediately after >> its >> creation. If this was the case then the entire file may have been in the >> OS >> cache resulting in very quick indexing. Try running "wc prod.db" or >> "cat prod.db >/dev/null" and then creating the indexes on prod.db to see >> what happens. > > The original poster confirmed that cat'ting the file to /dev/null reduced > index > creation time to 2.5 hours on the original database file. > > Could some optional heuristic be incorporated into SQLite's pager to do > something > similar for such large transactions and/or queries? > > > > > > > Need Mail bonding? > Go to the Yahoo! Mail Q for great tips from Yahoo! Answers users. > http://answers.yahoo.com/dir/?link=list=396546091 > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Index-creation-tf3451503.html#a9783538 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading across processes on Solaris
> I'd have thought so, too. I've confirmed fdatasync shows up in the symbols in > the compiled sqlite library, and that two instances of the `sqlite3` SQL shell > don't show the problem. Unfortunately, two minimal Python programs don't show > the problem either. > > That points to the application code -- except that I only see this behaviour > on Solaris, and there's no OS-specific code in the database-related portions > of the application. I'll continue investigating and try to get to the bottom > of this. Thanks for the sanity-check. Might there be some autocommit/sql statement batching difference for the driver or your app on different platforms? If all else fails, try truss on the "failing" Solaris processes and compare its output to strace for the same "correct" application on Linux. Sucker-punch spam with award-winning protection. Try the free Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/features_spam.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading across processes on Solaris
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- Charles Cazabon <[EMAIL PROTECTED]> wrote: > > > > On most platforms, the reader sees new data appear in the database > > periodically as the writer creates new records. But on Solaris, the > > reader never sees any updates -- it only ever sees whatever data was in > > the database when the reader first opened it, even though the writer is > > continuing to insert new data periodically. [...] > > I thought that SQLite's use of fdatasync on Solaris should be enough to > synchronize reads and writes from various processes: > > The fdatasync() function forces all currently queued I/O > operations associated with the file indicated by file > descriptor fildes to the synchronized I/O completion state. I'd have thought so, too. I've confirmed fdatasync shows up in the symbols in the compiled sqlite library, and that two instances of the `sqlite3` SQL shell don't show the problem. Unfortunately, two minimal Python programs don't show the problem either. That points to the application code -- except that I only see this behaviour on Solaris, and there's no OS-specific code in the database-related portions of the application. I'll continue investigating and try to get to the bottom of this. Thanks for the sanity-check. Charles -- --- Charles Cazabon <[EMAIL PROTECTED]> --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] testing with single source file
[EMAIL PROTECTED] wrote: [...] In past releases of SQLite, we have made available a ZIP archive with preprocessed source files. In the future, we may change this so that instead of a ZIP archive full of individual files, we ship a single "sqlite3.c" source file which contains all of the source file in a single translation unit. By this mode of delivery, we hope to enable users to see the performance improvements we are seeing in our red bars. I just want to thanks for adding the "sqlite3.c" target to the makefile! All tests run without problems compiled with a sqlite3.c produced from CVS head ! Few notes: 1. I had to change the declaration of these two functions in test7.c: void sqlite3_server_start(void); void sqlite3_server_stop(void); 2. I compiled everything using "-Wall -O3"; fixed few compiler warnings related to unused or not initialized variables (probably irrelevant). 3. I didn't used -DSQLITE_MEMDEBUG, so all malloc and vtab_err tests were skipped. 4. System used: Linux, i686, gcc 3.4.2, glibc 2.3.3, Pentium4/3.00GHz/1MB L2. Cheers, -Iulian - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading across processes on Solaris
--- Charles Cazabon <[EMAIL PROTECTED]> wrote: > I'm using sqlite (through the pysqlite wrapper, but this behaviour seems > unrelated to the wrapper) in an application on various platforms. One process > create an sqlite database and starts writing data to it; another process opens > the sqlite database and reads from it. > > On most platforms, the reader sees new data appear in the database > periodically as the writer creates new records. But on Solaris, the reader > never sees any updates -- it only ever sees whatever data was in the database > when the reader first opened it, even though the writer is continuing to > insert new data periodically. > > I've seen similar behaviour with non-database files on Solaris -- writes to a > file across processes aren't seen by the reader unless the reader supplies the > O_RSYNC flag to the open(2) call. It seems to be a Solaris peculiarity, as I > don't see this behaviour on Linux, *BSD, or other commercial Unices. > > I've looked at the sqlite source code, and it does not appear to be supplying > the O_RSYNC or O_SYNC flags to open(2). I thought that SQLite's use of fdatasync on Solaris should be enough to synchronize reads and writes from various processes: The fdatasync() function forces all currently queued I/O operations associated with the file indicated by file descriptor fildes to the synchronized I/O completion state. The functionality is as described for fsync(3C) (with the symbol _XOPEN_REALTIME defined), with the exception that all I/O operations are completed as defined for synchronised I/O data integrity completion. As far as I know, this ought to have the same effect as O_RSYNC: O_RSYNC If this flag is set, reading the data will block until any pending writes which affect the data are complete. Consider the situation where we want to read a block of data, which another process is updating. If this flag is not set, it is indeterminate whether the data returned will be that which is on the disk, or that which is scheduled to be written. Can you confirm that the pysqlite wrapper that you're using compiled sqlite with fdatasync? nm your_python_sqlite_wrapper.so | grep fdatasync or nm sqlite3.so | grep fdatasync Sucker-punch spam with award-winning protection. Try the free Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/features_spam.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading across processes on Solaris
--- Charles Cazabon <[EMAIL PROTECTED]> wrote: > I'm using sqlite (through the pysqlite wrapper, but this behaviour seems > unrelated to the wrapper) in an application on various platforms. One process > create an sqlite database and starts writing data to it; another process opens > the sqlite database and reads from it. > > On most platforms, the reader sees new data appear in the database > periodically as the writer creates new records. But on Solaris, the reader > never sees any updates -- it only ever sees whatever data was in the database > when the reader first opened it, even though the writer is continuing to > insert new data periodically. > > I've seen similar behaviour with non-database files on Solaris -- writes to a > file across processes aren't seen by the reader unless the reader supplies the > O_RSYNC flag to the open(2) call. It seems to be a Solaris peculiarity, as I > don't see this behaviour on Linux, *BSD, or other commercial Unices. > > I've looked at the sqlite source code, and it does not appear to be supplying > the O_RSYNC or O_SYNC flags to open(2). > > So, my questions are: > > 1) Has anyone else run into this issue? Is there a known way to work around > it? > > 2) Should the open(2) call be modified to provide the O_RSYNC flag? Would > this have nasty side effects? One would think that if such an issue existed in sqlite under Solaris someone would have reported it long ago - but you never know. I don't have access to Solaris at the moment, but you might try testing with 2 instances of the commandline shell, sqlite3, to make sure it's not a python driver issue. If you have any trouble building sqlite3 from sources, just post any compile issue to the list. Make sure you are running sqlite3 on a database file on a _local_ file system. Perform updates/inserts/deletes in one sqlite3 instance and see what happens when you do an appropriate select from the other sqlite3 instance. If it is proven that Solaris sqlite3 does indeed require the O_RSYNC flag in its open, I'm sure it could be put into os_unix.c with an appropriate #ifdef. Need Mail bonding? Go to the Yahoo! Mail Q for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list=396546091 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Reading across processes on Solaris
Greetings, I searched the web and the list archives but found nothing relevant to this problem, so here goes... I'm using sqlite (through the pysqlite wrapper, but this behaviour seems unrelated to the wrapper) in an application on various platforms. One process create an sqlite database and starts writing data to it; another process opens the sqlite database and reads from it. On most platforms, the reader sees new data appear in the database periodically as the writer creates new records. But on Solaris, the reader never sees any updates -- it only ever sees whatever data was in the database when the reader first opened it, even though the writer is continuing to insert new data periodically. I've seen similar behaviour with non-database files on Solaris -- writes to a file across processes aren't seen by the reader unless the reader supplies the O_RSYNC flag to the open(2) call. It seems to be a Solaris peculiarity, as I don't see this behaviour on Linux, *BSD, or other commercial Unices. I've looked at the sqlite source code, and it does not appear to be supplying the O_RSYNC or O_SYNC flags to open(2). So, my questions are: 1) Has anyone else run into this issue? Is there a known way to work around it? 2) Should the open(2) call be modified to provide the O_RSYNC flag? Would this have nasty side effects? I appreciate any responses. I'm subscribed to the list, so I don't need to be cc'd. Charles -- --- Charles Cazabon <[EMAIL PROTECTED]> --- - To unsubscribe, send email to [EMAIL PROTECTED] -