Re: [sqlite] Client/Server Environment
Hello, "Clay Dowling" <[EMAIL PROTECTED]> 09/11/2004 02:39 AM Please respond to sqlite-users To: [EMAIL PROTECTED] cc: Subject:Re: [sqlite] Client/Server Environment > Richard Boehme said: > > Hi there. How well does SQLite do in a client/server environment where > > the SQLite database is handling a batch of 100 inserts and the same > > number of queries a every 0.5 seconds? The inserts would be batched into > > a transaction. The queries would most likely not. > I'm not trying to run down SQLite here, but don't you think that this kind > of load justifies a pretty heavy duty database? SQLite is a disk based > database, not a client server database. For that kind of throughput, the > traditional choice has been a large scale client server database, and > there's been a pretty good reason why that's been so. If you are talking about scaling the application to a large number of machines to try and balance load, the big end databases may suit you. If you are talking about putting a big iron database to work on a single server and single disk array to get higher performance than sqlite... forget it. My figures are a little out of date now, but I looked for a high performance database for my own application a couple of years back and found that sqlite blew the popular alternatives[1] out of the water in data throughput. Since that time SQLite has become faster for many functions while other databases continue to get fatter. As with any performance-sensitive application you must profile the alternatives you are considering on your own target hardware with your own projected target load to get a reliable indication as to which will perform. I would certainly not discount sqlite based on a mailing list post. Try it out and see. I was pleasantly surprised, and suspect you will be also. Please report back, also. Your experience may be a useful data point for someone else in your position in future. >100 separate > clients trying to access the same file on disk is bound to cause a fiew > problems. The capacity of the database driver is probably the least of > your worries. 100 readers does not equal 100 separate clients trying to access the same file on disk. It may be equal to 100 clients trying to access the same piece of cache memory. That's certainly something you can do more than 100 times in half a second. The quality of your operating system will have some impact here, but I believe that even Microsoft can manage to do this properly these days. The main difference between the way sqlite handles performance and the way other databases do is that SQLite relies on the well-worn file operations of your operating system more than other databases. They try to implement these operations themselves. In bygone days this was a good thing because OS file handling was often poor. These days, It's a good thing. It keeps you lean and allows the operating system to do the heavy lifting in making you perform. If sqlite is going to be a problem under the proposed loading, it will most likely be related to locking and possible delays introduced by clients having to sleep to get their locks in. As presented, though, I suspect that won't be a huge problem. It would largely depend on the size and time-cost of the queries being performed. Benjamin [1] Mysql, postgres, sapdb, oracle.
Re: [sqlite] PATCH ATTACHED: Re: sqlite assert failure in 2.8.15
[EMAIL PROTECTED] wrote: This presented itself with the following scenario: - sqlite_open() some unimportant database file - set PRAGMA SYNCHRONOUS=OFF - do some things that cause pPg->needSync to be true for some set of pages. this will also have set pPager->needSync to be true. - ATTACH some more important database file - set PRAGMA SYNCHRONOUS=ON because now data integrity is important When sqlitepager_commit() is later called, it will fail the assert because sqlitepager_set_cachesize() which re-enabled synchronous mode, reset the pager structure's needSync (pPager->needSync = 0) but did not actually flush the pages to the journal (i.e. pPager->pAll->needSync is still 1). Thanks for tracking this down. I have not analyzed this situation closely. But I wonder if a simpler (and safer) solution to this problem would be to disallow the changing of PRAGMA SYNCHRONOUS in the middle of a transaction? Am I correct in understanding that the problem only occurs if you do something like this: PRAGMA synchronous=off; BEGIN; -- do some things PRAGMA synchronous=on; -- possibly do other things COMMIT; Changing the synchronous setting in the middle of a transaction seems dubious and risky to me. Your patch may well fix the immediate problem but I worry that there may be other undetected problems that remain unresolved. Disallowing a synchronous change in the middle of a transaction seems to be both safer and easier. Or am I not understanding the problem correctly? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Single row insert failure
Roderick A. Anderson wrote:
So here is the issue. Inserting a single row into a SQLite2 database
doesn't work using perl on a Windows system.
I have a data in a MS SQL Server database I need to process on a Linux
box (and getting ODBC to work isn't an option at this time) so my cheat
is to pull the data and put it in the SQLite db mount the share and
access the data.
This was part of a new process so I wanted to test it using a single
row. I'm sure I have something missing here but it works when I run
this and inserting multiple rows.
Here is the code.
Which can't be the actual code you're using. Please copy and paste your
actual code rather than retyping it.
use DBI;
my $dbh1 = DBI->connect( "dbi:ODBC:my.dsn", "user", "password" );
my $dbh2 = DBI->connect( "dbi:SQLite2:dbname=reports.db", "", "" );
my $sth1;
my $sth2;
$reportsdb->do( "DELETE FROM table1" );
Nowhere have you defined $reportsdb; this shouldn't run.
$sth1 = $dbh1->prepare(
"INSERT INTO table1 " .
"( field1, field2, field3, field4 ) " .
" VALUES ( ?, ?, ?, ? )" );
You're preparing an insert statement for the ODBC database, not the
SQLite database.
$sth2 = $dbh2->prepare( "SELECT field1, field2, field3, field4 FROM
table1 WHERE field1 = 'XXX'" );
$sth2->execute();
And you're attempting to fetch records from the SQLite database, which
you (unsuccessfully) tried to empty earlier.
while ( my @report = $sth2->fetchrow_array() ) {
# Just to see what is being selected
print join(' : ', @report) . "\n";
$sth1->execute( $report[0], $report[1], $report[2], $report[3] );
# Changed this as part of the testing. Works for mutiple rows
# $sth1->execute( @report );
What do you mean by "multiple rows" here?
}
$dbh1->disconnect;
$dbh2->disconnect;
After running this I looked at the reports.db with notepad, less, and
strings. No data, just the table definition.
Of course, since you never executed an insert statement on it.
Is there something I'm missing? The concern is there could be just a
single row in the MS SQL Server database.
I think you've misfocused and come up with a red herring; the problem
doesn't appear to have anything to do with the number of rows fetched.
Re: [sqlite] Crashing on some computers
On Nov 8, 2004, at 10:51 AM, Clay Dowling wrote: Tomas Franzén said: On 2004-11-09, at 16.42, b.bum wrote: Are you statically linking SQLite or using a dylib? I don't know. That's not a good sign, is it? ;-) I have compiled SQLite and added libsqlite.o and sqlite.h to the project items in XCode. ldd is your friend here. It will show you if you have an external dependency on any libraries, not just SQLite. On OS X, there's no ldd: 'otool -L' does the same thing, though. Wish they'd provided an alias for the six months it took me to learn that. :) -D
[sqlite] Single row insert failure
New subscriber butI did check the archives first and did not find
reference to this (or used the totally wrong search terms). There was
one thread to something similar but it didn't pan out.
So here is the issue. Inserting a single row into a SQLite2 database
doesn't work using perl on a Windows system.
I have a data in a MS SQL Server database I need to process on a Linux
box (and getting ODBC to work isn't an option at this time) so my cheat
is to pull the data and put it in the SQLite db mount the share and
access the data.
This was part of a new process so I wanted to test it using a single
row. I'm sure I have something missing here but it works when I run
this and inserting multiple rows.
Here is the code.
use DBI;
my $dbh1 = DBI->connect( "dbi:ODBC:my.dsn", "user", "password" );
my $dbh2 = DBI->connect( "dbi:SQLite2:dbname=reports.db", "", "" );
my $sth1;
my $sth2;
$reportsdb->do( "DELETE FROM table1" );
$sth1 = $dbh1->prepare(
"INSERT INTO table1 " .
"( field1, field2, field3, field4 ) " .
" VALUES ( ?, ?, ?, ? )" );
$sth2 = $dbh2->prepare( "SELECT field1, field2, field3, field4 FROM
table1 WHERE field1 = 'XXX'" );
$sth2->execute();
while ( my @report = $sth2->fetchrow_array() ) {
# Just to see what is being selected
print join(' : ', @report) . "\n";
$sth1->execute( $report[0], $report[1], $report[2], $report[3] );
# Changed this as part of the testing. Works for mutiple rows
# $sth1->execute( @report );
}
$dbh1->disconnect;
$dbh2->disconnect;
After running this I looked at the reports.db with notepad, less, and
strings. No data, just the table definition.
Is there something I'm missing? The concern is there could be just a
single row in the MS SQL Server database.
TIA,
Rod
--
---
[This E-mail scanned for viruses by Declude Virus]
Re: [sqlite] Crashing on some computers
On Nov 9, 2004, at 10:48 AM, Tomas Franzén wrote: I don't know. That's not a good sign, is it? ;-) I have compiled SQLite and added libsqlite.o and sqlite.h to the project items in XCode. Do you mean libsqlite.a? Do you have libsqlite3.dylib (or something like it) in /usr/local/lib? ... alternatively, try otool -L on the compiled binary produced by Xcode. If you are dynamically linking SQLite, try building it statically (see the list archives -- I posed how to do this a few days ago). It may be that you are running into a dynamic linking issue. b.bum
Re: [sqlite] Crashing on some computers
Tomas Franzén said: > On 2004-11-09, at 16.42, b.bum wrote: >> Are you statically linking SQLite or using a dylib? > > I don't know. That's not a good sign, is it? ;-) > I have compiled SQLite and added libsqlite.o and sqlite.h to the > project items in XCode. ldd is your friend here. It will show you if you have an external dependency on any libraries, not just SQLite. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] Client/Server Environment
Richard Boehme said: > This is an extreme case -- records would usually be updated every minute > or so. Usually there might be 10 computers trading information (about > 100 records every few minutes), for example. I was extrpolating an > extreme test case where the system is very heavily used. > > In such a situation, we would have some server process which feeds the > queries to SQLite. Since I've now also read your inquiries in the Joel On Software forum, I might be able to answer your questions better, especially since a client server database won't do. The correct answer is that you'll need to try it out and see if SQLite can keep up. Your application architecture and the nature of the data and queries is going to have a pretty significant influence on the result. I would build a working prototype to prove that the model is good, then try flooding the system to see how much it can handle. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] Crashing on some computers
On 2004-11-09, at 16.42, b.bum wrote: Are you statically linking SQLite or using a dylib? I don't know. That's not a good sign, is it? ;-) I have compiled SQLite and added libsqlite.o and sqlite.h to the project items in XCode. Thanks! Tomas Franzén Lighthead Software http://www.lightheadsw.com/
Re: [sqlite] Client/Server Environment
This is an extreme case -- records would usually be updated every minute or so. Usually there might be 10 computers trading information (about 100 records every few minutes), for example. I was extrpolating an extreme test case where the system is very heavily used. In such a situation, we would have some server process which feeds the queries to SQLite. Thanks. Richard Boehme
Re: [sqlite] Client/Server Environment
On Tue, Nov 09, 2004 at 10:45:35AM -0500, Richard Boehme wrote: > Hi there. How well does SQLite do in a client/server environment > where the SQLite database is handling a batch of 100 inserts and the > same number of queries a every 0.5 seconds? The inserts would be > batched into a transaction. The queries would most likely not. > > Thanks for the help. Sounds like SQLite could handle it just fine. I don't know what you mean with client/server here. Do you plan to develop a custom server process that clients talk to that does all the database access? -- Gerhard signature.asc Description: Digital signature
Re: [sqlite] Client/Server Environment
Richard Boehme said: > Hi there. How well does SQLite do in a client/server environment where > the SQLite database is handling a batch of 100 inserts and the same > number of queries a every 0.5 seconds? The inserts would be batched into > a transaction. The queries would most likely not. I'm not trying to run down SQLite here, but don't you think that this kind of load justifies a pretty heavy duty database? SQLite is a disk based database, not a client server database. For that kind of throughput, the traditional choice has been a large scale client server database, and there's been a pretty good reason why that's been so. 100 separate clients trying to access the same file on disk is bound to cause a fiew problems. The capacity of the database driver is probably the least of your worries. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] Pocket PC - SQLite - eMbedded Visual C++
User Aaron Planell Lopez wrote:: [...] But when I execute the program in my Pocket PC show me the next message: 'proyecto1' is not a valid application for Pocket PC [...] What is the sqlite project configuration ? Do you try to compile it as a static lib or DLL ? I suppose that you compiled it as a DLL but you didn't uploda that DLL on your Pocket PC. So, sqlite DLL can not be found on your device and you get such message. Check it. Greets -- Mateusz Łoskot, mateusz (at) loskot (dot) net Registered Linux User #220771, Debian (Sarge)
[sqlite] Client/Server Environment
Hi there. How well does SQLite do in a client/server environment where the SQLite database is handling a batch of 100 inserts and the same number of queries a every 0.5 seconds? The inserts would be batched into a transaction. The queries would most likely not. Thanks for the help. Richard Boehme
Re: [sqlite] Crashing on some computers
On Nov 9, 2004, at 6:46 AM, Tomas Franzén wrote: I have built a Carbon (Mac OS X) bundle that contains SQLite code. It works great on my two Macs, and on a few other people's boxes. However, on some people's Macs, it crashes on sqlite_open. Has anyone had these kind of problems? Are you statically linking SQLite or using a dylib? b.bum
[sqlite] Crashing on some computers
Hi, I have built a Carbon (Mac OS X) bundle that contains SQLite code. It works great on my two Macs, and on a few other people's boxes. However, on some people's Macs, it crashes on sqlite_open. Has anyone had these kind of problems? Here's the relevant part of the crash log: Thread 0 Crashed: 0 com.apple.carbonbundletemplate 0x0470b510 sqlite_open + 0x110 (main.c:498) 1 com.apple.carbonbundletemplate 0x0470a47c Input + 0x278 (main.c:52) Maybe I have forgot something important? :-) Thanks! Tomas Franzén Lighthead Software http://www.lightheadsw.com/
