Re: [sqlite] web server use of SQLite
Thank you all so much for your data. It will really help me a lot. Looks like I'll be able to use SQLite in ways I had originally anticipated would be out of scope. Well done On Mon, 2003-11-24 at 09:06, D. Richard Hipp wrote: > D. Richard Hipp wrote: > > > > Over the past 30 days, the www.sqlite.org website has seen 6 > > separate 10-minute bursts of activity with 25 hits/day rates > > and many 1-minute bursts in the 45 hits/day range. Rates in > > excess of 20 hits/day have been sustained for a couple of > > hours on one event. (Many of the 1-minute and 10-minute burst > > records can be found within that two-hour episode.) > > > > These figures only count hits that accessed the database at least > > once. Images and static pages are not counted. > > > > More data: > > I duplicated the www.sqlite.org website on my desktop then started > hammering on it using multiple instances of "wget -r". I ran the > test for several minutes. > > During this test, every hit involved multiple SELECTs and at least > one UPDATE against a single sqlite database file. The database engine > was easily able to handle a rate of over 250 (2.5M) hits/day or > about 30 hits/second. The database did not appear to be the limiting > factor in this test - other components (such as the customized web > server that www.sqlite.org uses and the exec()-ing of RCS commands) > seemed to be what kept it from going even faster. > > So I'm going to revise my earlier estimates of SQLite's abilities > for use with websites. I now believe that SQLite is appropriate > for use as the primary database on websites that get up to 1 million > hits per day. -- Vania Smrkovski http://www.pandorasdream.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
> It looks to me that several users are (a) in a uniprocess environment, > and (b) inventing their own SQLite db access synchronization code. An > SQLite fine grained lock manager for threads in a single process would > address these same issues, with better concurrency as well. > I'm all for finer grained lock management as long as it doesn't mean the loss of some important aspects of SQLite that I particularly like. For example, I remember back when I was doing something in MS SQL, the only way to get speeds that I could deal with, was to use pure transactions, which was fine. But I found that a transaction had to complete before I could get result sets back. While I can understand this in a finer grained lock type of SQL, it became somewhat a task to work around the fact that if any SQL errored in the transaction, you had no real way of figuring out which line errored in the transaction. Now, this was many years ago, and my understanding of the API might not have been up to par, but I recall working with a DBA that said to me, "Just make sure all your SQL statements are perfect before throwing them into a transaction.". This made me feel like I had to work around the database, where really I wanted the database to work around my code. The transaction was not a set number of statements, basically what was being done was there was a collection of SQL statements over 200ms, then a commit, so the transaction was actually very dynamic. And from what I can recall, I had a terrible time figuring out which SQL statement in the transaction actually caused the error, not to mention no result sets were returned because of that one error. In the end I had to ditch this method of doing things and port a lot of code into tedious compiled SQL functions, which really made me feel like I had to work around the database. Now this was many years ago, but that was one miserable SQL experience that I hope never to repeat. Jay Macaulay - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
At 16:21 24/11/2003, Doug Currie wrote: It looks to me that several users are (a) in a uniprocess environment, and (b) inventing their own SQLite db access synchronization code. An SQLite fine grained lock manager for threads in a single process would address these same issues, with better concurrency as well. Are others in the position of having to create their own SQLite db access synchronization code? Yes, we have to do that. It's made a bit harder by the fact that the DB file could be accessed by other software so we have to handle busy states as well as trying to stop them with our own synchronisation code. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Protecting the database (revisited)
I remember someone talking about something that could be changed at compile time to prevent other sqlite DLLs from seeing the database file as valid.. I tried to find the original message but wasn't able to.. Can someone clue me in? I know it wont offer a whole lot of protection but I'm just wanting to make it a little hard(er) to open the database if you're not me! Thanks! __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Postscript on double-emails
Bert and I have exchanged emails on this privately and the resolution is that he actually *WAS* subscribed twice to the list. And so in an odd way, everyone was correct: he *was* getting two copies of everything, but the listserver was not misconfigured or misbehaving. I'll re-extend my original offer: if you believe you're getting two copies of postings to the list, forward me the headers [all of them] for two "matching" messages you received and I'll be happy to try to help sort out what's going on... /Bernie\ -- Bernie Cosell Fantasy Farm Fibers mailto:[EMAIL PROTECTED] Pearisburg, VA --> Too many people, too few sheep <-- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Concurrency in SQLite
Hi - I wanted to answer some of the survey questions about concurrency in SQLite. Finer-grained locking would not help our particular application. We have a multi-threaded application where there are many reader threads and a single writer thread. It's not a big deal for us if the readers read uncommitted data. A pragma to allow 'dirty' reads in a read-only connection would really help concurrency in our case. My favorite features of SQLite are the small size, code portability, small memory footprint, and that it is easily embedded. (Plus the source code is some of the best I've ever seen from a commenting and clarity standpoint.) With some minor tuning of the DB parameters, our schema and queries, we've been able to make it fast enough for our needs, so I'd be sad to see changes in database locking make it harder to embed the database or make it less portable. As for the questions: > * What SQL are you running that takes more than a fraction of a second to complete? We have a number of queries that take a few seconds to run. They tend to involve tables with 100,000 or more rows, where the query criteria matches about 1000 of them, sorts and returns, say, the most recent 50 of those 1000 rows, via an SQL LIMIT clause. As a non-sequiter, we experimented with a number of btree page sizes and we found 4k to be the fastest for our particular application. It seems that modern OS's can read 4k in about the same amount of time that they can read 1k from disk. > * Are you holding transactions open for an extended period of time? Why? We do hold transactions open for an extended period of time - our application acquires data from disparate sources, many of them over the net and stores metadata in the database. To make inserting a large number of rows faster, we wrap them in a transaction. We haven't experimented yet with committing periodically to make the application more live while it is gathering data. > * How many processes do you have trying to access the database at once? One process with multiple threads. We have one writer thread and any number of reader threads. > * How do you currently handle SQLITE_BUSY replies? Do you use the > sqlite_busy_handler() or sqlite_busy_timeout() APIs? We use the sqlite_busy_handler() APIs. > * How large are your databases? In the hundreds of megabytes. > * Do you ever put database files on a shared filesystem? No. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
It looks to me that several users are (a) in a uniprocess environment, and (b) inventing their own SQLite db access synchronization code. An SQLite fine grained lock manager for threads in a single process would address these same issues, with better concurrency as well. Jay said: > All database access is handled in a single thread which > synchronizing DB access so that only one SQL command can happen at a > time. Benjamin said: > The most important change for me is one that I introduced into my > copy: Blocking locks. These are important because there is only an > instant between the last transaction closing and the next beginning. > In this scenareo the poll-based locking mechnism currently used by > sqlite is just not lucky enough to try at that instant. Only > blocking locks with their operating-system support are sufficient to > ensure that the readers get in at all. I also have a situation where > I have multiple writers on the database that can run into the same > problem. Allan said: > We don't ever hit the busy timeout handler. We can't since we > synchronize in our data access layer. Doug said: > .. due to the coarse grained locking, I ended up serializing access > to SQLite in my own uni-process multi-thread bottleneck. Are others in the position of having to create their own SQLite db access synchronization code? e - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] web server use of SQLite
D. Richard Hipp wrote: Over the past 30 days, the www.sqlite.org website has seen 6 separate 10-minute bursts of activity with 25 hits/day rates and many 1-minute bursts in the 45 hits/day range. Rates in excess of 20 hits/day have been sustained for a couple of hours on one event. (Many of the 1-minute and 10-minute burst records can be found within that two-hour episode.) These figures only count hits that accessed the database at least once. Images and static pages are not counted. More data: I duplicated the www.sqlite.org website on my desktop then started hammering on it using multiple instances of "wget -r". I ran the test for several minutes. During this test, every hit involved multiple SELECTs and at least one UPDATE against a single sqlite database file. The database engine was easily able to handle a rate of over 250 (2.5M) hits/day or about 30 hits/second. The database did not appear to be the limiting factor in this test - other components (such as the customized web server that www.sqlite.org uses and the exec()-ing of RCS commands) seemed to be what kept it from going even faster. So I'm going to revise my earlier estimates of SQLite's abilities for use with websites. I now believe that SQLite is appropriate for use as the primary database on websites that get up to 1 million hits per day. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] web server use of SQLite
OK, Sqlite will scale just as well on a single processor machine with few drives as any big RDBMS. If Sqlite was to take a turn to compete in areas it does not already dominate clearly, it would be in scale. If sqlite could take advantage of multiple processors and drives that would be huge, but again that adds a ton of complexity to the architecture of the database. As time goes on and AMD and Intel start pumping out 64-bit processors, we are going to see some really scalable Sqlite databases with NO additional complexities added to the code other than what you are already doing. So my point here is I see the database staying SIMPLE and FAST, and as hardware allows you can run much larger dbs. Now, one thing to think about is grid computing. Ironically, Oracle, Secret (SQL) Server (as I call it), and the other major vendors are moving to a simpler model of distributing across multiple machines. Trying to scale their databases to expensive hardware is going out because it is so much cheaper to go out and get multiple mass produced machines and put them together to get massive parallel processing (and run Suse or Red Crap(oops I mean hat). If you were to head these other vendors off at the pass on this one, you would add the ability to simply distribute a database across machine boundaries. Some sort of Distributed Transaction Coordinator like in Secret Server would make Sqlite (as I put my finger on my mouth as in Austin Powers) 1 MILLION DOLLARS! Thanks, Allan -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Monday, November 24, 2003 7:13 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] web server use of SQLite D. Richard Hipp wrote: > > The www.sqlite.org website is run off of a single SQLite database. > Back before I implemented bandwidth throttling, the site would > sometimes get 5 hits/day to pages that used the database. > Over the past 30 days, the www.sqlite.org website has seen 6 separate 10-minute bursts of activity with 25 hits/day rates and many 1-minute bursts in the 45 hits/day range. Rates in excess of 20 hits/day have been sustained for a couple of hours on one event. (Many of the 1-minute and 10-minute burst records can be found within that two-hour episode.) These figures only count hits that accessed the database at least once. Images and static pages are not counted. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] web server use of SQLite
Vania Smrkovski wrote: Have you or anyone encountered issues regarding my other questions, that of using SQLite as a web-served DB system? How do the recently discussed issues of concurrency and the locking of DB tables and files affect the use of a web site delivered SQLite DB system, and is the strategy of using multiple DB files (which is what I understood D. Richard Hipp's suggestion to be) effective enough if one central table were to have hundreds of thousands of records? The www.sqlite.org website is run off of a single SQLite database. Back before I implemented bandwidth throttling, the site would sometimes get 5 hits/day to pages that used the database. If your website gets fewer than 10 hits per day, I think SQLite should work ok (depending on what you are doing, of course). For more than 100 hits per day, you should probably use a client/server database engine of some kind. For values in between 100K and 1M, I don't really have enough experience to say. Probably it would depend on how heavily you are using the database. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] web server use of SQLite
Darren, Thanks for the response. I knew about this issue as a security hole, which is why one of my options was to place the DB files out of the entire web doc tree. Happily, you took something that I had to learn the hard way from tech notes and experience and codified it in a way that confirmed and clarified a few points for me. PHP has experienced security problems along these lines in the past, and it now discourages use of PHP as a CGI and recommends using it as a server module. However, in my design architecture, I was finding it somewhat desirable that at least some DB files exist in the application directory. I suppose, though, that I could find ways around that. Have you or anyone encountered issues regarding my other questions, that of using SQLite as a web-served DB system? How do the recently discussed issues of concurrency and the locking of DB tables and files affect the use of a web site delivered SQLite DB system, and is the strategy of using multiple DB files (which is what I understood D. Richard Hipp's suggestion to be) effective enough if one central table were to have hundreds of thousands of records? This is an area that I find difficult to test since PHP5 is still only in beta, so I can't put it on a public server and get a hundred thousand of my closest friends to try to hit the site at the same time But since some of you seem to be using it with other languages, maybe you have encountered the situation and found solutions? Thanks! V On Sun, 2003-11-23 at 23:57, Darren Duncan wrote: > At 9:04 PM -0500 11/23/03, Vania Smrkovski wrote: > > Speaking to the first question, regarding security of data, aside from > >some of the obvious, like putting the DB files out of the web document > >tree, are there any ways to configure SQLite to keep the DB files in > >some non-readable format? I had hoped to use an architecture where each > >web application had a SQLite DB in the same folder for session and > >storage of data useful only to that app, and to have a general SQLite DB > >file elsewhere for all apps to use, but was somewhat startled when I > >tried accessing the SQLite file directly in my browser and got almost > >perfectly readable data dumped to my screen I was hoping that I > >might have overlooked a configuration switch that would make the file > >unreadable, but have not found this yet > > What you describe is a general website design flaw and/or gaping security hole on > your part and has nothing specifically to do with SQLite. > > The fact is that any individual files on your web server which are not supposed to > be directly invoked or downloaded by a client web browser (or robot) should not be > inside any public directory. You should store all of these files, including any > SQLite databases for sessions, somewhere else on your server. No one can download > your database if there is no web address that corresponds to it, can they? > > For my part, I set up my web applications (all written in Perl 5 currently) like > this (simplified for illustration): > > /users/me > /users/me/private_files > /users/me/private_files/mylib1.pm > /users/me/private_files/mylib2.pm > /users/me/private_files/mydata1.txt > /users/me/private_files/mydata2.db > /users/me/private_files/mymain.pl > /users/me/public_files > /users/me/public_files/default.pl -> soft link -> ../private_files/mymain.pl > /users/me/public_files/myimage1.gif > /users/me/public_files/myimage2.gif > > So only items in public_files can be invoked or downloaded. No one can see the > items in private_files, which includes the source code of my Perl library files, > which don't execute. The main program, mymain, is very minimalist and doesn't > contain any sensitive data; it mainly just calls a function in one of my libraries > that does the real work; this also means if the web server has a bad day and becomes > misconfigured, making the perl script a source-downloadable file, no one is seeing > anything important. > > The catch with the above approach is that you have to explicitely change the current > working directory or use path references to files. You can't simply say "open this > file in my current directory", since the "current" directory is the public folder. > > -- Darren Duncan > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] -- Vania Smrkovski http://www.pandorasdream.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] database is locked
[EMAIL PROTECTED] wrote:
[DQL statements ("SELECT ...") lock the whole database when using PySQLite.]
Hi,
I've thought about this whole issue and made a few changes to my local
working copy of PySQLite over the weekend, which will go into the next
release (during the next days):
The most important change is that even in transactional mode, a BEGIN
will only be sent immediately before the first DML statement
(INSERT/UPDATE/DELETE). DQL statements (SELECT) will not trigger the
sending of a BEGIN any more.
This means that read-operations won't block other connections to the
database any more, which will make PySQLite a lot more usable for web
applications.
Still, your DA certainly needs an additional .commit() somewhere,
otherwise this problem wouldn't appear at all. Also be sure to set the
busy timeout in the connect call. timeout=2.0 would be a reasonable
value, normally.
I can send you a snapshot of my PySQLite working copy tomorrow, and I
could also take a look at your DA, if you want to.
-- Gerhard
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] database is locked
-- Initial Header --- >From : "Gerhard Häring" [EMAIL PROTECTED] To : "sqlite-users" [EMAIL PROTECTED] Cc : Date : Fri, 21 Nov 2003 16:43:26 +0100 Subject : Re: [sqlite] database is locked > [EMAIL PROTECTED] wrote: > > Hi all, > > > > I'm trying SQLite with Zope > > You're using PySQLite, right? right. > > > and it works fine, but when I use the browser's > > [Stop] button to interrupt the request it gives me the message: > > > > "database is locked" [...] > > This happens when you have a connection that is within a transaction (i. > e. you haven't issued .commit() or .rollback() on the connection object, > yet) and you're trying to open a second connection to the same database. I'm trying to read a table, I'm not writting it; My form have a button wich sends the command: SELECT * from table1 if I push it more than once it ...(or if I push another button with a select on other table), the response is: Zope has encountered an error while publishing this resource. Error Type: OperationalError Error Value: database is locked at this point I have to close the database connection and reopen it again. > > The solution is to assure that you always .commit() or .rollback(). I > don't know where to put that in a ZOPE database adapter, perhaps you > should check the sources of a different ZOPE DA. > > But it's hard to tell without knowing your sources and without sufficent > knowledge about Zope database adapters ;-) > > Or are you using PySQLite not from within a DA, but otherwise? Yes, I'm using PySQLite from DA. Jo > > -- Gerhard > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
G'day, "D. Richard Hipp" <[EMAIL PROTECTED]> 24/11/2003 03:22 AM To: [EMAIL PROTECTED] cc: Subject:[sqlite] Concurrency in SQLite > Please, give me some examples of the kinds of things you are > doing which could benefit from improved concurrency. > * Are you holding transactions open for an extended period > of time? Why? This is my situation. I have a large amount of data flowing into a database which shows historical records (maybe a couple of thousand inserts per second). Queries are much rarer. To keep inserts efficient I hold transactions open for one second at a time. The most important change for me is one that I introduced into my copy: Blocking locks. These are important because there is only an instant between the last transaction closing and the next beginning. In this scenareo the poll-based locking mechnism currently used by sqlite is just not lucky enough to try at that instant. Only blocking locks with their operating-system support are sufficient to ensure that the readers get in at all. I also have a situation where I have multiple writers on the database that can run into the same problem. If you could ensure that readers could still read the untouched version of database blocks while a writer is working on "dirty" version of the same blocks I wouldn't have any problems as far as reading is going. Writing would still be problem, though. It's not the amount of concurrency that's a problem for me. One at a time is fine. It's just the ability to schedule the accesses that do happen very tightly together that I care about. >* How many processes do you have trying to access the database > at once? Usually at most two or three. > * How do you currently handle SQLITE_BUSY replies? Do you use > the sqlite_busy_handler() or sqlite_busy_timeout() APIs? The problem with both of these apis is that they use timers beetween attempts. If I could put a blocking lock on the database in the busy handler, allow the database access to occur, then get called back to unlock the database, it would be almost as good as the current blocking lock situation. > * How large are your databases? Usually less than a gig :) > * Do you ever put database files on a shared filesystem? No. Benjamin. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
