Re: [sqlite] beat 120,000 inserts/sec
On Apr 9, 2005, at 8:49 AM, Al Danial wrote: I did try SYNCHRONOUS=off but that didn't seem to have an effect; I'll study the docs to make sure I've got it right. This isn't surprising. fsync() is largely a no-op on just about any operating system. It doesn't actually guarantee that the bytes are written to the platter (though it will likely impact queueing and the like within the kernel). smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] beat 120,000 inserts/sec
There are also pragmas to control page size and in-memory caching. You will want to play with those, as well. If SQLite is in the middle of a transaction and you load it up with commands, it will create a journal file in /tmp/ to start pages that don't fit in the in-memory page cache (or something like that). As such, the pragmas can hugely affect performance. You will likely find that tuning the engine for particular roles -- bulk loading, queries, random updates -- will likely be useful. See the SQLite documentation for more information. b.bum smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] No password to protect the sqlite database file
On Apr 2, 2005, at 5:32 PM, liigo wrote: Thanks, but why not sqlite add a password to the database file? There isn't much value in adding a password required to access the file. The user could still easily see all of the data stored within the SQLite file. SQLite is an embedded database file. By its very nature, the user will have access to the raw database file. This is very different than a client/server database like Sybase or Oracle. With those, the server can be walled off from the user quite effectively. Now, of course, you could use an encryption extension -- of which several are available (see sqlite.org for more info) -- to encrypt the data in the database with a password. It is likely that this would cause a potentially significant performance hit. b.bum smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] test errors on OSX
On Apr 1, 2005, at 11:10 AM, Stephen C. Gilardi wrote: For several months now, I've gotten similar errors when I run the test on my PowerBook G4. For me, the set of tests that fail is different from test run to test run, but they do seem to be concentrated in the "*ioerr" tests for the most part. I've run the test several times on a G5 PowerMac and I have never seen the problem. I know at least some of the official Mac OS X testing for sqlite is done on a G5. That may explain why this hasn't been seen a lot by the development team. I've enclosed the results of two test runs from today's cvs sources checked out into a new empty directory on my PowerBook G4. A similar run on a G5 PowerMac succeeded. In all cases, this is Mac OS X 10.3.8 with all current updates. The intermittent nature of the failure suggests to me that it is some kind of timing-related problem. I'll be happy to work with anyone to try to debug this or test possible fixes. This is actually due to a bug in SQLite's Makefile.in for the testfixture target. In particular, it recompiles most of the library and then links in a fashion such that many of the modules -- the .o files -- are effectively linked twice. Because they are linked twice and twolevel namespaces are enabled, the modules use of static variables becomes very, very confused. Effectively, you end up with two instances of a static variable with the different instances being used in different contexts, depending on which codepath is followed. The fix is to NOT disable twolevel namespaces, but to fix SQLite's Makefile.in to not hoark the linking of the testfixture. Once this is fixed, all of the unit tests pass all of the time on Mac OS X. Having run said unit tests approximately 8 bazillion times as a part of my day job, I'm 100% confident that this is the case. ;-) The good news is that this bug only affects the testfixture, not the library itself. b.bum
Re: [sqlite] SQLite v3.1.4 - Mac OS X 10.3.8 compile issues
On Mar 11, 2005, at 8:09 AM, Eric Hochmeister wrote: I just noticed that a new version of SQLite 3.1.4 was up, so I downloaded it and can't seem to get it to compile. I have successfully been using previous versions of SQLite (3.1.3, 3.0.8, etc.) and this is the first time I've received an issue compiling. Does anyone have any ideas? I'm using Mac OS X 10.3.8. Brief note. In the patch, search for F_FULLSYNC and replace it with F_FULLFSYNC. http://www.sqlite.org/cvstrac/chngview?cn=2372
Re: [sqlite] sluggish operation on os x?
On Feb 21, 2005, at 11:44 AM, Curtis King wrote: On 21-Feb-05, at 11:11 AM, [EMAIL PROTECTED] wrote: OK -- so, you are willing to accept the risk of non-recoverable database corruption in the event of power outage or other kinds of catastrophic system failure (including the plug being pulled on a FireWire drive without it being properly unmounted)? I.e. that risk is perceived to be acceptably small that the performance hit is not justifiable? The performance hit is much larger than the risk, so in some cases, no FireWire drives and there is an UPS, the risk is acceptable for the performance gain. To ask the question an other way since FreeBSD, Linux, Solaris, etc do not support F_FULLFSYNC is it safe to run any kind of database on them ;) FreeBSD/Linux/Solaris are most often run in less hostile environments -- cages, racks, UPS'd, etc... Mac OS X has to deal with a very hostile computing environment -- lots of sleep/wake, power loss, drives being hot plugged (often without proper unmounting), etc... But, agreed, it should be an option. b.bum
Re: [sqlite] sluggish operation on os x?
On Feb 21, 2005, at 9:54 AM, James Berry wrote: On Feb 21, 2005, at 9:40 AM, Curtis King wrote: I noticed this as well, so I profiled my call and found sync was taking forever. I removed the following fcntl call, rc = fcntl(fd, F_FULLFSYNC, 0);. Performance was back to normal. Here are some comments about F_FULLFSYNC, off the darwin list just two days ago. They mention why it's there, but don't mention how slow the performance might be... It is a trade off between guaranteed data integrity and performance. If there happen to be a bunch of other apps writing to the disk when you do a SQLite transaction, then all of that data has to be flushed to the disk. As Domnic said, fsync() does not guarantee that the bytes hit the platter on any system. Pull the plug after a COMMIT and you are very likely going to see only part of the pages written. You can also use the 'synchronous' pragma to control the number of F_FULLSYNCs executed during any single transaction. By default, it will be three-- probably too excessive. The best way to guarantee maximal performance is to bunch up your INSERT and UPDATE statements into transactions as much as possible. This is often true regardless of the presence of F_FULLSYNC. Note that this situation only arises in the case of catastrophic system failure such as a power failure or kernel panic. b.bum
Re: [sqlite] Python bindings for SQLite 3?
On Feb 17, 2005, at 9:53 AM, H. Wade Minter wrote: I'm playing around with some Python stuff, and was wondering if there were any reasonably stable bindings for SQLite 3? I've got an existing SQLite 3 database that I want to work against, so I'd rather not drop back to SQLite 2? I have been using Roger Binn's Another Python SQLite Wrapper. It is a direct wrapper of the SQLite APIs while still "bringing up" the API to Python's level of abstraction. It has worked flawlessly for me. http://www.rogerbinns.com/apsw.html
Re: [sqlite] sqlite & multithreading
On Feb 7, 2005, at 10:21 AM, Alex Chudnovsky wrote: Correct me if I am wrong but I was under the impression that having 2 separate connections to database (and while on subject I noticed that making connection via ADO.NET takes significant time measured in seconds rather than in milliseconds) will result in locking issues that I found I could not recover from, ie reconnecting to database fails as it is still locked. You should always be able to recover from a deadlock situation by rolling back the transaction on all but one of the connections-- thread or task-- that is attempting a commit. You can prevent deadlock situations by beginning a transaction at a particular lock level. There is quite a lot of discussion of this in the list archives -- have a search for "exclusive and reserved". My solution was to use single connection in a C# wrapper around ADO.NET that would queue requests using primitive locking to avoid actual locking of database and it can recover from "library called out of sequence" errors. SQLite specifically does not support multithreaded execution upon a single SQLite connection. It will result in 'out of sequence' errors and SQLITE_MISUSE result codes. I don't believe that recovery from such errors is really supported. Now I have two questions: 1) is it better using multiple connections to the same database (surely locks will be issue?) Yes and Yes, locks will be an issue. Locking errors can be recovered from, though. http://sqlite.org/lockingv3.html 2) a friend of mine voiced opinion that it is a good idea to open source my wrapper (C# .NET on top of ADO.NET), is there a need in it? If one does not already exist, then I'm certain that the community would welcome such a contribution quite warmly and you will benefit from having lots of peer review! b.bum
Re: RE(1): [sqlite] SQLite Advocacy
On Jan 31, 2005, at 9:31 AM, [EMAIL PROTECTED] wrote: Not true at all. In fact, from experience, the Linux OS is much more full of holes than Windows. It appears most hate Microsoft so thier OS gets the most virus and hackers. All I can say is we independently did a test with Linux and Windows we isntalled a default OS and put it on the net without a firewall. Windows was never hacked, but Linux was hacked in a day and they took root access to the point where we could not get back in. That is incorrect and counter to recent tests that show that an unpatched Windows system will survive less than an hour whereas an unpatched Linux system will generally survive for 3 months. Once "hacked", the level of ownership is irrelevant as any standard rootkit will "own" the box to the level you describe with a single installation command. It isn't a case of "hate". The sheer volume of unpatched Windows systems running on wide open broadband connections makes for an extremely attractive-- lucrative, even-- set of systems to take over for the purposes of spamming and distributed denial of service attacks. See: http://www.schneier.com/blog/archives/2005/01/linux_security.html b.bum
Re: [sqlite] few questions...
On Jan 23, 2005, at 8:11 AM, Jason Morehouse wrote: We are currently using mysql. What is the comparison to opening a database with sqlite vs connecting to the daemon with mysql? Our current box has seen 300+ connections to the sql server at at once. Can we expect that having 300 databases open with sqlite wont be a problem? SQLite and MySQL are at opposite ends of the spectrum when it comes to how multiple connections are managed. MySQL uses a central daemon that arbitrates all connections. By doing so, it can manage the connections on a relatively fine grained level, allowing multiple simultaneous readers and writers (as long as, I would assume, the writers aren't all scribbling on the same table). With this flexibility comes considerable complexity in that you have to administrate yet another service on the computer and manage connection information, etc... SQLite takes the very simple approach of equating opening a database connection with opening a file. As such, it is extremely efficient in that no data has to pass "over the wire" and very simple in that there is no administrative overhead.There is significantly less overhead in opening a SQLite database file than there is in opening a client/server connection. Since there isn't a connection, there isn't really a notion of multiple connections either. Instead, SQLite allows multiple clients-- threads or processes-- to open the database file. SQLite arbitrates read/write access through the use of BSD level advisory locks. As such, SQLite allows multiple simultaneous readers and only one writer. When a writer is actively writing to the database, it blocks all other readers. Unless you redesign your application to not be focused on a multiple connection model, it is unlikely that you will see any benefit-- performance or otherwise-- to moving to SQLite. I am assuming that your inquiry was related to performance issues that you are currently experiencing with MySQL? Can you give us an idea of how large of a working set (i.e. how much data is in active play, in general) you have, what the transaction rate is, and how large the overall data set is? b.bum
[sqlite] tcl_install ignores DESTDIR
The 'tcl_install' target of Makefile.in ignores the DESTDIR that may have been specified as a part of the build. The tclinstaller.tcl script should install the sqlite3 tcl hook in DESTDIR/LIBDIR such that a build-for-packaging doesn't inadvertently shove things into the installed system. The target from Makefile.in: tcl_install:libtclsqlite3.la tclsh $(TOP)/tclinstaller.tcl $(VERSION) b.bum (who hasn't written a line of Tcl in more than a decade -- have to fix that) smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] 3.1 vs. 3.0 file compatibility.
On Jan 21, 2005, at 2:49 PM, Dan Kennedy wrote:
If the 3.1 file is created with the auto-vacuum option enabled
("PRAGMA auto_vacuum = 1;"), then the database will appear read-only
to 3.0 clients. That's the only incompatibility.
OK -- can auto-vacuum be turned on within a database that was created
with 3.0?
b.bum
smime.p7s
Description: S/MIME cryptographic signature
[sqlite] 3.1 vs. 3.0 file compatibility.
Under what circumstances is a file written by 3.1 incompatible with a file written by 3.0? thanks, b.bum smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] possible workaround for bug #301
On Jan 19, 2005, at 10:43 AM, Will Leshner wrote: Bug #301 says that, because fcntl isn't supported for remove volumes on OS X, SQLite can't acquire a lock, which, I think, pretty much means you can't use SQLite databases that are on AFP or SMB volumes on OS X. What would happen if I simply made the calls in os.c that acquire locks NOOPs? I realize you could never expect to share a database with such a crippled version of SQLite, but if you knew that there was only going to be one reader/writer, would it work? fcntl() is supported on removable volumes on Mac OS X -- it works fine for removable media. fcntl() is not supported for AppleShare or Samba. SQLite fails gracefully in that it will report SQLITE_BUSY for any database on those volumes. If you were to guarantee that there is only one reader and only one writer-- not just one process, but a single thread and only one sqlite3_open() against any one file-- accessing the SQLite database, then it should work fine. Risky, certainly. b.bum
Re: [sqlite] Mac OS X and SQLite Locking
On Tue, 4 Jan 2005, Steve Milner wrote: Hello List, I am having a problem with SQLite erroring out on Mac OS X. I am using 3.0.8. The problem happens when it tries to access a SAMBA/CIFS shared database. In my testing Windows to Linux and Linux to Windows worked fine, but Mac to Windows fails with the following: Traceback (most recent call last): File "dbclassqascript.py", line 14, in ? db.query(sys.argv[2]) File "/Volumes/TUX-NET;W/database.py", line 25, in query cu.execute(query) File "/System/Library/Frameworks/Python.framework/Versions/2.3/lib/ python2.3/site-packages/sqlite/main.py", line 244, in execute self.rs = self.con.db.execute(SQL) _sqlite.OperationalError: database is locked All help is greatly apreciated! On unix, SQLite requires that the database reside upon a filesystem that supports BSD style advisory locks via the fcntl() API. The samba filesystem does not support that style of locking API on Mac OS X. b.bum
Re: [sqlite] VACUUM function problem
On Dec 28, 2004, at 11:44 AM, D.W. wrote: Is there a function that does what I have described? [described... 'compact' the primary keys of a database so that there are no unused gaps after a row or rows have been deleted] No. And, generally, you really don't want to do that. The primary keys are generally used to create relationships between tables. That is, the primary key of a row in table A will appear in fields of row or rows in table B (thus called a foreign key). If you were to go and change the primary keys in table A, it would also require changing all of the foreign keys to A in table B (and all other tables). This can be significantly expensive and is one of the reasons why it is generally preferable to keep primary keys meaningless beyond being a unique and unchanging row identifier. If you need a field in table A that acts as a sort of numeric counter of the rows of that table, then create a field to do exactly that and don't use it as a key. in general, I have seen few schemas that actually use such a column of data that requires every number to be consecutive. If order of insertion is important, most schemas use a creation date column. The database representation is generally an INT containing the number of seconds since some significant date; Jan 1, 1970 or 2000 being the most common.With that, you can always sort on said column to determine insert order and the "index" of a particular row in the overall table can generally be implied by the query itself, though not always.
Re: [sqlite] Large memory usage not freed?
On Nov 4, 2004, at 3:42 PM, D. Richard Hipp wrote: If we exit the process and then reopen the database in a new process, all that memory is not reallocated. Are you sure you have that right? What OS are you running? Also, how are you checking to see if the memory was deallocated in the first place? The kernel may still indicate that the process's virtual size has grown to some huge size, even though -- internally -- the process isn't actually using all of the memory. Sometimes the system won't reap the unused pages. In general, if your app is memory and performance sensitive, you will need to balance the use of BEGIN/END transaction with the # of individual statements in any one transaction. Writing to the disk has a huge performance impact, but caching all of those statements / state changes in memory prior to the END TRANSACTION requires a lot of memory. Often, there is a balance between the two that is appropriate to your app. If not, you'll just have to get faster disks or more memory. b.bum
