Re: [sqlite] SQLite vs. Oracle (parallelized)
Hello! On Friday 27 February 2009 23:57:39 Allan Edwards wrote: > You setup 2 seperate machines, sqlite on one, and Oracle on another... > they both have the exact make and model hard drives and OS. Which one > can write data faster to the hard drive? There are two strategies: 1. Rows are writing in realtime. Oracle is faster because using internal write buffers and buffers doesn't really flushing to disk immediately. 2. Rows are writing in batch mode. For example exactly 1 write transaction (with dirrerent count of rows) per second. SQLite is faster because don't have internal write buffers and doesn't write some log files and jornals. But SQLite index creating performance is not very good and using a lot of indexes may provide problems. A lot of realtime write operations (in financials and like applications) are not possible by SQLite (auto-commit mode has bad performance becouse each operation must be flushed to disk). And read SQLite performance can be more better then Oracle because SQLIte dosn't have multy-version control for rows and doesn't have client-server interprocessing overhead but can perform a lot of parallels readings in different threads. Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Here is the reality distribute your data processing across as many hard drives as you can. The key is the more parallel drives you can have working, it does NOT matter what database you utilize. If you build your broker (server process that interacts with Sqlite... or other database and controls the entire parallel processing) properly, you can add or remove hard drives with a simple configuration change. Depending on how much data load you have you could also subnet banks of servers that run your sqlite data storage to provide for optimal network communication to your sqlite data processing nodes. Trick question You setup 2 seperate machines, sqlite on one, and Oracle on another... they both have the exact make and model hard drives and OS. Which one can write data faster to the hard drive? I think you want to look into parallel data processing algorithms. Of course, to me that kind of stuff is common sense but I find most guys don't truly study their processor specifications as they should and therefore miss the low level details that would imply optimal decisions in their parallel algorithms design. For example... most people say I want to multi thread so my software runs "faster". That is not properly said. You multi thread to utilize the same computer speed but just more of the processor bandwidth per time slice. So, for example, if your system had 4 threads to schedule and each had 25% equal processing time... all threads run in 1 minute. So that would be 15 seconds per thread. If you multi thread and utilize 2 threads to process data in that same minute, you have doubled your processing time by 100% by utilizing 50% of the total 4 threads. You did not speed up the computer, you utilized more processor time per round robin thread run. So the bottom line is, when you design out your parallel algorithm, the database itself is not all that important but being able to configure as many as you need quickly IS. But knowing the speed of your drives, knowing the speed of your network, configuring and setting up to scale out to more nodes (network, desks, computers) IS the key. And of course, your broker architecture (the piece that controls the show collectively) must be written and carefully tested so it loses as little efficiency system wide as it has to make decisions in parselling work out to the various processing nodes. IF you utilize Sqlite for such an endeavor, I do believe you would be better off. Why? because Sqlite is so simple and has NO server process you control the software performance totally above the data storage below. So in essence in this way Sqlite IS a great choice. You can xcopy the db to a node and role! With database "servers" you will have to cotend with the variability of a closed source base you have no control over that takes up threads and processor time on the machines in which it runs. Less IS FOR SURE sometimes more. I have scene systems in the past that used Oracle, Informatica, etc. to do massive data processing... I mean hundreds of millions of rows per week. The reality is most of the teams that build this stuff buy a massively expensive RISC based system when you could spend the money on development of software that would "efficiently" scale a simpler solution like Sqlite across many nodes. The money is spent mostly on butt covering The technical decision was driven more by an ignorant sales person versus an in the know technology guy. -A- On Fri, Feb 27, 2009 at 2:35 PM, wrote: > Allan, > > Thanks for your reply. I'm new to SQLite, but have used a similar list > of databases (with an emphasis on server vs. client side databases) > professionally for years. My background is designing and building > enterprise BI, ETL, and data warehouse systems using databases like > Oracle, DB2, SQL Server and ETL tools like Informatica, Ab Initio, BO > Data Integrator, etc. > > My goal is to be able to offer our customers cost effective, high > performance alternatives to traditional commercial ETL solutions. We > recently re-implemented a mid-sized Oracle/Informatica based ETL system > in Python using in memory data structures (dicts) and improved the > performance by a factor of 20x. We're now looking to expand this > approach to SQLite for environments where the volume of data is greater > than available memory. > >> When you talk about performance comparisons your question really needs to be >> squared up to what the database is used for. Are you on a server, >> workstation, or embedded? How large in the database? How are the internal >> algos of the database engine lined up? What is the memory footprint? How did >> you configure what is configurable in the database? > > I'm interested in exploring whether or not SQLite can be used as an ETL > tool with large data sets (~80+ Gb). In this context, SQLite would be > run on 64-bit Intel servers with lots of RAM (16-64 Gb). The data would > be stored
Re: [sqlite] SQLite vs. Oracle (parallelized)
Hello! On Saturday 28 February 2009 00:31:37 pyt...@bdurham.com wrote: > Your approach of splitting large data sets sounds similar to what other > SQLite users with large data sets seem to be doing. At a high level, > this sounds like how one would partition data using Oracle? I'm going to > start a new thread on this topic. Oracle manage partitions automatically but SQLite databases are manages by your application. So you must write more code. But good result is that SQLite databases may be packed and removed to archive and your application can easy resolv this situation. And a lot of external programs (as example demon for network monitoring or tacacs/radius server) can store data in single databases without working with full dataset. And Oracle is using some buffers for modified data and can perform a lot of write/read transactions in parallels. SQLIte- driven application must plan write operation and use some hashes/in-memory databases as buffers and perform saves sets of records to on-disk database because write operation with single record is not effective. So I have tcl data collectors which regular save chunks of data to on-disk SQLite database. Of cource users operations by web-interface performs immediately and it's work fine because in web-applications there are a lot of read operations and a few write operations. > > With your hardware I think 100Gb dataset is not limit. > > Good news. I'm looking forward to verifying this over the next month or > so. Please mail to list or to me directly about you problems and results. I do some big systems based on SQLite now and the information about similar projects is important for me. I support debian repository with my own SQLite build with extra modules. For big projects is needed some additional functions (data compression, md5 functions, extended csv import, etc.). Debian etch: deb http://mobigroup.ru/debian/ etch main contrib non-free deb-src http://mobigroup.ru/debian/ etch main contrib non-free Debian lenny: deb http://mobigroup.ru/debian/ lenny main contrib non-free deb-src http://mobigroup.ru/debian/ lenny main contrib non-free Keyring may be installed as sudo aptitude install debian-mobigroup-keyring Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Alexey, Thank you for your reply and for sharing your success with SQLite. I'm excited by your results (60x faster). On an informal basis, we've been going back and re-benchmarking some of our old, 'traditional' (Oracle/Informatica) ETL/DW projects and we now believe the majority of these systems could be simplified and made faster by using alternative techniques based on in-memory data processing (definitely) and/or SQLite (we still need to test). Your approach of splitting large data sets sounds similar to what other SQLite users with large data sets seem to be doing. At a high level, this sounds like how one would partition data using Oracle? I'm going to start a new thread on this topic. > With your hardware I think 100Gb dataset is not limit. Good news. I'm looking forward to verifying this over the next month or so. Best regards, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Hello! On Friday 27 February 2009 23:35:50 pyt...@bdurham.com wrote: > I'm interested in exploring whether or not SQLite can be used as an ETL > tool with large data sets (~80+ Gb). In this context, SQLite would be > run on 64-bit Intel servers with lots of RAM (16-64 Gb). The data would > be stored/processed on server local SCSI drives vs. located on a SAN. > File access would be via a single process per SQLite database. The > interface language would most likely be Python. We are using some years SQLite dataset ~10 Gb on 32-bit Linux host (SATA HDD and 1 Gb RAM). PostgreSQL version of the dataset was more than 20 Gb and did work 60x slowly! It's not possible to upgrade hardware by some reasons and we did replace PostgreSQL database to SQLite because there are 50 users in system and performance was bad. AOL Server + tcl + SQLite is good for us. For each http query is used self database connection to SQLite database (for PostgreSQL we did use connection pools) and it's work fine when read operations are dominating. For dataset populating are used a batch writes by some demons and single write operations performed by users web interface. Dataset is splitted by months as single database for monthly data and these chunks can be attached when needed. With your hardware I think 100Gb dataset is not limit. Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Allan, Thanks for your reply. I'm new to SQLite, but have used a similar list of databases (with an emphasis on server vs. client side databases) professionally for years. My background is designing and building enterprise BI, ETL, and data warehouse systems using databases like Oracle, DB2, SQL Server and ETL tools like Informatica, Ab Initio, BO Data Integrator, etc. My goal is to be able to offer our customers cost effective, high performance alternatives to traditional commercial ETL solutions. We recently re-implemented a mid-sized Oracle/Informatica based ETL system in Python using in memory data structures (dicts) and improved the performance by a factor of 20x. We're now looking to expand this approach to SQLite for environments where the volume of data is greater than available memory. > When you talk about performance comparisons your question really needs to be > squared up to what the database is used for. Are you on a server, > workstation, or embedded? How large in the database? How are the internal > algos of the database engine lined up? What is the memory footprint? How did > you configure what is configurable in the database? I'm interested in exploring whether or not SQLite can be used as an ETL tool with large data sets (~80+ Gb). In this context, SQLite would be run on 64-bit Intel servers with lots of RAM (16-64 Gb). The data would be stored/processed on server local SCSI drives vs. located on a SAN. File access would be via a single process per SQLite database. The interface language would most likely be Python. > The reality is I BET and I would love to know myself Sqlite is just as fast > on inserts to the same hard drive as oracle. I would love to see such a benchmark as well. > Are you some kind of political Oracle covering biggot? LOL. No. I try to be as database agnostic as possible. > Or, do you want another chance to post something that states you are trying > to find the best data storage solution to deliver sincere value to your > client in terms of a database choice? Subtle point here ... I'm not looking for a long term data storage solution - I'm exploring the possibility of using SQLite as a way to validate, transform, and pre-aggregate raw data that would in turn be exported to raw text files and imported (in final form) to a customer's data warehouse. > Sometimes that choice is Sqlite, but in some cases it is Oracle instead. Agreed. Regards, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
"Chris Wedgwood" wrote in message news:20090226172406.ga685...@puku.stupidest.org > On Thu, Feb 26, 2009 at 09:53:18AM -0600, Jay A. Kreibich wrote: > >> I've always wondered about this... someone please correct me if I'm >> wrong, but my understanding is that there wasn't any difference >> between a left and right join except for the argument order. It >> seems like implementing right joins should be pretty easy by just >> having the parser reverse the arguments when it builds the parse >> tree. I realize that might screw with the optimizer a bit, but is >> there anything obvious I'm missing? > > When you use a right and a left join together you can't do that. > Sometimes you can restructure the queries to make use or two left > joins or a sub-query, but it's often cumbersome and not always the > same thing. > > If in general there is a way to turn something like > > select c1,c2,c3 from m left join l on l.mid=m.id right join r on > l.rid=r.id > > into something conceptually the same that only uses left joins then > perhaps the optimizer could do this for us. An equivalent query using only left joins would look something like this: select c1,c2,c3 from r left join (m left join l on l.mid=m.id) ml on ml.rid=r.id; The problem, I believe, is not in parsing right joins or replacing them with equivalent left joins, but in optimizing the resulting statement. SQLite's optimizer is fairly simple: I suspect if it sees something like the above, it'll throw its hands up in the air and resort to full table scans all around. For this reason, right joins likely have to be natively supported by the optimizer - you can't just fake them by transforming the parse tree. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Thu, Feb 26, 2009 at 09:53:18AM -0600, Jay A. Kreibich wrote: > I've always wondered about this... someone please correct me if I'm > wrong, but my understanding is that there wasn't any difference > between a left and right join except for the argument order. It > seems like implementing right joins should be pretty easy by just > having the parser reverse the arguments when it builds the parse > tree. I realize that might screw with the optimizer a bit, but is > there anything obvious I'm missing? When you use a right and a left join together you can't do that. Sometimes you can restructure the queries to make use or two left joins or a sub-query, but it's often cumbersome and not always the same thing. If in general there is a way to turn something like select c1,c2,c3 from m left join l on l.mid=m.id right join r on l.rid=r.id into something conceptually the same that only uses left joins then perhaps the optimizer could do this for us. There might be corner cases though were such a transform isn't possible. When it's vague like this I defer to people like Igor who's SQL abilities constantly amaze me. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Hello! On Thursday 26 February 2009 18:53:18 Jay A. Kreibich wrote: > I agree. The only major addition I'd like to see is full support for > referential integrity. I know you can fake it, to a degree, with > triggers, but I'd still like to see it baked into the database engine > itself. But why? I did use only triggers for referential integrity checks in PostgreSQL because trigger can return user-friendly description on national lang. As example from SQLIte-based project for russian users: CREATE TRIGGER users_update_before before update on users begin select RAISE (ABORT,'Нельзя изменять идентификатор') WHERE OLD.id != NEW.id; SELECT RAISE(ABORT, 'Указан несуществующий регион.') WHERE NEW.region_id != '' AND (select ROWID from regions where id=NEW.region_id) IS NULL; SELECT RAISE(ABORT, 'Пользователь с указанным именем уже существует! Пожалуйста, придумайте для этого пользователя другое имя.') WHERE (SELECT ROWID FROM users WHERE nickname = NEW.nickname and rowid!=NEW.rowid) IS NOT NULL; end; Standart foreign key error message is not good for users. User does not understand message such as "Foreign key error: ... key ... table...". Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Wed, Feb 25, 2009 at 10:29:59AM -0800, Chris Wedgwood scratched on the wall: > On Mon, Feb 23, 2009 at 04:28:00PM -0500, D. Richard Hipp wrote: > > > Remember: SQLite is not trying to replace Oracle. SQLite is trying > > to replace fopen(). > > Exactly. SQLite is *wonderful* for this, and I would hate to see it > compromised by adding features that really aren't necessary. > I'm very happy with how small, compact, and blisteringly fast, SQLite > is for a whole range of uses, I don't want it bloated down to make it > something it's not. I agree. The only major addition I'd like to see is full support for referential integrity. I know you can fake it, to a degree, with triggers, but I'd still like to see it baked into the database engine itself. > Oh, except right joins. If you add those I won't mind :-) I've always wondered about this... someone please correct me if I'm wrong, but my understanding is that there wasn't any difference between a left and right join except for the argument order. It seems like implementing right joins should be pretty easy by just having the parser reverse the arguments when it builds the parse tree. I realize that might screw with the optimizer a bit, but is there anything obvious I'm missing? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Mon, Feb 23, 2009 at 04:28:00PM -0500, D. Richard Hipp wrote: > Remember: SQLite is not trying to replace Oracle. SQLite is trying > to replace fopen(). Exactly. SQLite is *wonderful* for this, and I would hate to see it compromised by adding features that really aren't necessary. There are a lot of feature requests, and many of them are to make SQLite more like MySQL or Oracle --- in which case I would suggest people really should be using those. I'm very happy with how small, compact, and blisteringly fast, SQLite is for a whole range of uses, I don't want it bloated down to make it something it's not. Oh, except right joins. If you add those I won't mind :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
I must trade my works for currency. "Will Code for FOOD" : - ) I consult and architect systems professionally and really can't afford to do much free work. As much as I love to develop solutions, we are not in the Star Trek age of we just live to better others and ourselves! If we were you would find me saying "Computer, lets work up a new piece of software on Sqlite... etc. etc. etc." On Tue, Feb 24, 2009 at 7:55 AM, P Kishor wrote: > On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards > wrote: > .. >> >> I have personally written a socket based server on top of the database >> and it works very well. So I have actually scaled the database myself >> as I preached in this email. Don't be a WIMP and map shares to share >> a database... write a socket based beauty like YOURS TRULY! hehe For >> "most" solutions it is wonderful. After years and building millions >> of lines of code keeping the business delivery requirements fulfilled >> in the most simple manner seems to be the best approach for me. If >> you are the same, stay agnostic to all solutions available and run up >> a strategy that will give you the best of all worlds. And yes, at >> times you have to write a little EXTRA code to get there! : - ) >> >> Allan >> >> P.S. If somebody does decide to build enterprise Sqlite, I would love >> to throw in my 2 cents on how to write the stuff on the outside to add >> in the big dog features. I was working out tonight and while thinking >> about this I believe you could actually maintain the wonderfulness of >> the core engine, then scale the library from an outside piece of code. >> Then you can keep integrity on both sides of the fence and not make >> sqlite into sqlitetoomuch. >> > .. > > Have you considered taking the "socket based server" that you wrote, I > am assuming, on top of SQLite, and donating it to public domain/open > source, putting it on the sqlite wiki, so others may benefit? > > Who knows, with a seed like that, someone may well build a > SQLiteEnterprise (as much an oxymoron as that might be). > > -- > Puneet Kishor http://www.punkish.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > Sent from: Madison WI United States. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- W Allan Edwards 214-289-2959 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Hello! On Monday 23 February 2009 23:54:56 pyt...@bdurham.com wrote: > Are there any plans to enhance SQLite to support some of Oracle's > parallel processing or partitioning capabilities? But Oracle does not database for "cloud computing". You can't get no-installable and no-administrate Oracle instance. You can't use in-memory Oracle databases. You can't easy add new functions to Oracle. You can't [effectively] use Oracle on single SATA disk and common CPU. You can't have hundreds of Oracle databases on single host. You can't create mobile replica of oracle dataset for smartphone or PDA. ... And do you like Oracle? :-) Multi-core processor can perform operations with multiple instances of SQLite in parallel. For common web application there are hundreds of read operations on each write operation and selects performance is more important than locks on parallel write operations. For very fast write operations you must use key- value databases such as berkeleydb or tokyocabinet. But SQLite have problems with long transactions. Oracle is good for this job. Dataflow processing applications prefer in-memory databases and can be SQLite- driven. May be this is not task for SQL-databases but SQL is very useful and comfortable. May be Oracle is good for mainframes but non-administrate clusters are [more] popular now. For example mnesia database (writed on erlang) can be good for this job by it's very complex solution. So I think Oracle may be used for applications with long transactions and only if you have high-powered servers. SQLite can be used for many other causes. This preference may be lost in case SQLite will like Oracle . Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Mon, Feb 23, 2009 at 10:34:50PM -0500, pyt...@bdurham.com scratched on the wall: > Hi Billy, > > >> Are there any plans to enhance SQLite to support some of Oracle's > >> parallel processing or partitioning capabilities? > > > I realized that you're asking Richard, and not the peanut gallery, but > > I figured I might as well ask out of curiosity: why do you want to > > see these features in SQLite? > It would be great to see SQLite be able to exploit the extra processing > power of multiple cores. This is not a request for handling multiple > simultaneous transactions - it is a request to have single transactions > be processed across multiple cores. The only way to go multi-core is multi-process and/or multi-thread. Multi-process is pretty much out of the question, given the design goals of SQLite. That leaves multi-threaded. Ask Dr. Hipp about his feelings** on the efficiency and elegance of heavily threaded programming, and in specific how easy they are to test and verify. Go ahead... It's a rough week at work and I could use the entertainment. ** with which I agree. > Another interesting Oracle feature is compression. Oracle's compression > techniques not only compress data, but also speed up many types of > selects. This is one area I do think SQLite might benefit from. As processors get much faster and storage only gets a little faster, it can often be faster to read smaller chunks off disk and decompress them than it is to read the uncompressed chunk. It gets tricky with small page sizes, but on some platforms-- especially those doing direct access to slower flash devices, it might be worth it. There are a number of factors to balance, but it might be an interesting exercise, especially if you're not interested in saving file space, only improving I/O speeds. Hwaci, Inc. (the commercial side of SQLite) does offer a read-only compressed file solution. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Tue, Feb 24, 2009 at 8:19 AM, Allan Edwards wrote: > I must trade my works for currency. "Will Code for FOOD" : - ) I > consult and architect systems professionally and really can't afford > to do much free work. As much as I love to develop solutions, we are > not in the Star Trek age of we just live to better others and > ourselves! If we were you would find me saying "Computer, lets work > up a new piece of software on Sqlite... etc. etc. etc." I am not sure what to make of this. I thought you wrote that you had already "written a socket based server," so that would not mean any further work for you other than putting it on the sqlite.org website and in public domain. On the other hand, I completely understand if your hands are tied with either client-worker confidentiality, work-for-hire agreements, or even the exigencies of trying to monetize your earlier work. In open source, we have to depend on the largesse of others, who, just like us, have exactly the same number of hours in a day, and pretty much exactly the same kind of need for making ends meet. Someone has to, somewhere, decide to give away something. At some point, Richard did that with Sqlite, and we are all here as a result. Even here, many people give generously of their time and knowledge -- most of us on this list would get nowhere without Dan Kennedy, Dennis Cote, Igor Tantednik, Roger Binns and the like. The beauty of open source is that if others can't give away, we do have the freedom of trying to meet our own needs. If we don't have the technical ability to meet our own need, then we can trade our money or other resources with someone who does have that ability. Barring all of that, I would say, we are a noop. Maybe someday someone will feel a burning need for a massively parallelized, multi-core, full CPU utilizing SQLiteHeavy version of this little database, and will have the time or means to make it happen. If that happens, maybe that person will then put this SQLiteHeavy into public domain, and maybe it will become highly popular. We can only hope. > > > > On Tue, Feb 24, 2009 at 7:55 AM, P Kishor wrote: >> On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards >> wrote: >> .. >>> >>> I have personally written a socket based server on top of the database >>> and it works very well. So I have actually scaled the database myself >>> as I preached in this email. Don't be a WIMP and map shares to share >>> a database... write a socket based beauty like YOURS TRULY! hehe For >>> "most" solutions it is wonderful. After years and building millions >>> of lines of code keeping the business delivery requirements fulfilled >>> in the most simple manner seems to be the best approach for me. If >>> you are the same, stay agnostic to all solutions available and run up >>> a strategy that will give you the best of all worlds. And yes, at >>> times you have to write a little EXTRA code to get there! : - ) >>> >>> Allan >>> >>> P.S. If somebody does decide to build enterprise Sqlite, I would love >>> to throw in my 2 cents on how to write the stuff on the outside to add >>> in the big dog features. I was working out tonight and while thinking >>> about this I believe you could actually maintain the wonderfulness of >>> the core engine, then scale the library from an outside piece of code. >>> Then you can keep integrity on both sides of the fence and not make >>> sqlite into sqlitetoomuch. >>> >> .. >> >> Have you considered taking the "socket based server" that you wrote, I >> am assuming, on top of SQLite, and donating it to public domain/open >> source, putting it on the sqlite wiki, so others may benefit? >> >> Who knows, with a seed like that, someone may well build a >> SQLiteEnterprise (as much an oxymoron as that might be). >> >> -- >> Puneet Kishor http://www.punkish.org/ >> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ >> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ >> Sent from: Madison WI United States. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > W Allan Edwards > 214-289-2959 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Tue, Feb 24, 2009 at 12:25 AM, Allan Edwards wrote: .. > > I have personally written a socket based server on top of the database > and it works very well. So I have actually scaled the database myself > as I preached in this email. Don't be a WIMP and map shares to share > a database... write a socket based beauty like YOURS TRULY! hehe For > "most" solutions it is wonderful. After years and building millions > of lines of code keeping the business delivery requirements fulfilled > in the most simple manner seems to be the best approach for me. If > you are the same, stay agnostic to all solutions available and run up > a strategy that will give you the best of all worlds. And yes, at > times you have to write a little EXTRA code to get there! : - ) > > Allan > > P.S. If somebody does decide to build enterprise Sqlite, I would love > to throw in my 2 cents on how to write the stuff on the outside to add > in the big dog features. I was working out tonight and while thinking > about this I believe you could actually maintain the wonderfulness of > the core engine, then scale the library from an outside piece of code. > Then you can keep integrity on both sides of the fence and not make > sqlite into sqlitetoomuch. > .. Have you considered taking the "socket based server" that you wrote, I am assuming, on top of SQLite, and donating it to public domain/open source, putting it on the sqlite wiki, so others may benefit? Who knows, with a seed like that, someone may well build a SQLiteEnterprise (as much an oxymoron as that might be). -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
I wanted to throw out a few more points about being parallel and scalable in terms of data storage? What is the reason we want to be this way? Well, for tons of users and to mitigate risk across lots of machines. Yes, out of the box the value that Oracle provides is of a designation that satisfies these ideals for large scale systems. YET, as memory, hard drive space, processors with more cores, etc. come online, how could you get Sqlite to be SQLiteIKickOraclesButt? Well, I although I am pressed for time in development of a number of systems I don't have time to try this out but what Sqlite would need is a broker like server that could utilize it's existing features. The broker server could provide the same features as an Oracle by splitting inserts across multiple sqlites that hit multiple disks on multiple systems. The reality is that Sqlite has taken care of the nasty details of organizing data into a binary file cross platform, but you could literally take the database and write code above the engine itself that leveraged it's capabilities in such a way that it worked like a large scale database. Back years ago I was the head architect on a C based system in semiconductor. We had a crazy requirement to bust out hundreds to thousands of grey scale images from a camera source in a real time manufacturing solution. We had to store this data on the drive quickly. Before I took charge of the project we failed to get the necessary performance needed from an RDBMS on our first project time delivery limits. On the second round we bench marked all system components and figured out how we needed to architect a system from every hardware dependency. We ended up writing a buffer management library that basically was optimized to pre allocate memory of specific sizes for specific image sizes. This pre allocation allowed for the most optimal approach in moving data into memory. We then had writer threads that had thread synced access into that buffer and eventually the images would get written to an RDBMS. The reason I shot you the example above is to give you food for thought on how you might still utilize the great power of Sqlite if you don't need the massive cost or overhead of an Oracle. Sqlite is a beautiful thing due to it's simplicity. It is the most powerful database management solution on the planet for it's size (this is TOTALLY OBJECTIVE). Installing Oracle on a computer, being force fed crappy performing and unrobust java (the enterprise Oracle manager crashes on me with HUGE stack traces) too butt with Oracle makes for one nasty user experience. Yet with sexy Sqlite you can "xcopy" install that puppy and go to town. So in your thought on your desired solutions, IT MIGHT be good to think of what Sqlite offers and consider writing a separate library that could fulfill your extrea RDBMS requirements so you can take advantage of the sweetness this little data storage gym offers. I have personally written a socket based server on top of the database and it works very well. So I have actually scaled the database myself as I preached in this email. Don't be a WIMP and map shares to share a database... write a socket based beauty like YOURS TRULY! hehe For "most" solutions it is wonderful. After years and building millions of lines of code keeping the business delivery requirements fulfilled in the most simple manner seems to be the best approach for me. If you are the same, stay agnostic to all solutions available and run up a strategy that will give you the best of all worlds. And yes, at times you have to write a little EXTRA code to get there! : - ) Allan P.S. If somebody does decide to build enterprise Sqlite, I would love to throw in my 2 cents on how to write the stuff on the outside to add in the big dog features. I was working out tonight and while thinking about this I believe you could actually maintain the wonderfulness of the core engine, then scale the library from an outside piece of code. Then you can keep integrity on both sides of the fence and not make sqlite into sqlitetoomuch. On Mon, Feb 23, 2009 at 9:49 PM, wrote: > P Kishor, > >>> Most computers these days are multi-core. .. > >> One of things easy to overlook is that SQLite is not a PC-exclusive > software. About 10 million copies of SQLite run on iPhone. Who knows > how many run on other handhelds, embedded platforms, Vxworks, the > like. SQLite used to fit on a floppy and probably still does. > > Good point! > > I lost my perspective on SQLite's intended audience. > > Regards, > Malcolm > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- W Allan Edwards 214-289-2959 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Actually, Oracle boots pretty fast and once it is booted up, it is SUPER fast on insertion. I have used the following databases professionally for years * MS SQL Server * MS SQL Server Embedded * MS Access * Oracle (numerous versions) * MySQL (multiple versions) * Objectivity * PostgreSQL * Sqlite * DB2 When you talk about performance comparisons your question really needs to be squared up to what the database is used for. Are you on a server, workstation, or embedded? How large in the database? How are the internal algos of the database engine lined up? What is the memory footprint? How did you configure what is configurable in the database? The reality is that Sqlite is slower on 1 insert per transaction, but if you start a transaction, insert a slew of records, then commit, the database is very fast. The major platforms that are typically server oriented like Oracle pre allocate memory and file space so they can "cheat" for a while to be optimized. Oh wait, and index space is pre allocated. Sqlite appears to be optimized for single file access and as the docs on the website say, no server full of memory to buffer and give the appearance that it is faster than it really is on inserts in terms of hard drive write speed. The reality is I BET and I would love to know myself Sqlite is just as fast on inserts to the same hard drive as oracle. People that are political in slant toward a specific thing tend to like to make a blanket statement like Oracle is faster than Sqlite but not inform everyone else as to a specific comparison which makes the statement full of it! : - ) Are you some kind of political Oracle covering biggot? hehehe Or, do you want another chance to post something that states you are trying to find the best data storage solution to deliver sincere value to your client in terms of a database choice? Sometimes that choice is Sqlite, but in some cases it is Oracle instead. Sqlite is a very Hipp database. Allan On Mon, Feb 23, 2009 at 3:28 PM, D. Richard Hipp wrote: > > On Feb 23, 2009, at 3:54 PM, pyt...@bdurham.com wrote: > >> Dr. Hipp, >> >> When you say "SQLite is way faster than Oracle in a single-user >> applications" do you mean that SQLite can be faster than Oracle even >> when Oracle's parallel processing features are being used? For example >> Oracle's support for parallelization can speed up table loading from >> an >> external data source, certain SQL selects, and certain indexing >> operations. > > I don't run Oracle and have no way of verifying the following. But I > conjecture that from a cold start, you and launch an application that > uses SQLite, have it do a dozen or so queries, print out the answer, > and shut down, all before the Oracle server has even booted up to the > point where it will accept connections. Correct me if I am wrong. > > Perhaps Oracle will run a gazillion more transactions per second, > given enough memory and CPUs, and once you get it up and going. I > have no way of knowing. But then again, that isn't really the point > of SQLite. > > >> >> >> Are there any plans to enhance SQLite to support some of Oracle's >> parallel processing or partitioning capabilities? > > Remember: SQLite is not trying to replace Oracle. SQLite is trying > to replace fopen(). > > For people who are using Oracle as a replacement for fopen() (as > apparently Angela is) they will likely find that SQLite makes a far > superior replacement. Or to put it another way, people who are using > Oracle for a single-user application (low concurrency) will likely > find that SQLite works much better for them. It has been my > experience that old-time Oracle users are incredulous at this > statement, until they actually see a live demonstration. So I won't > try to argue the point. It is merely my observation. > > On the other hand, nobody things that SQLite is a suitable database > when you have 1000 separate connections beating on the database all at > once. > > > >> >> >> Thank you, >> Malcolm >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- W Allan Edwards 214-289-2959 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
P Kishor, >> Most computers these days are multi-core. .. > One of things easy to overlook is that SQLite is not a PC-exclusive software. About 10 million copies of SQLite run on iPhone. Who knows how many run on other handhelds, embedded platforms, Vxworks, the like. SQLite used to fit on a floppy and probably still does. Good point! I lost my perspective on SQLite's intended audience. Regards, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Mon, Feb 23, 2009 at 9:34 PM, wrote: > Hi Billy, > >>> Are there any plans to enhance SQLite to support some of Oracle's >>> parallel processing or partitioning capabilities? > >> I realized that you're asking Richard, and not the peanut gallery, but >> I figured I might as well ask out of curiosity: why do you want to >> see these features in SQLite? > > Most computers these days are multi-core. .. One of things easy to overlook is that SQLite is not a PC-exclusive software. About 10 million copies of SQLite run on iPhone. Who knows how many run on other handhelds, embedded platforms, Vxworks, the like. SQLite used to fit on a floppy and probably still does. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Hi Billy, >> Are there any plans to enhance SQLite to support some of Oracle's >> parallel processing or partitioning capabilities? > I realized that you're asking Richard, and not the peanut gallery, but > I figured I might as well ask out of curiosity: why do you want to > see these features in SQLite? Most computers these days are multi-core. Oracle has done some excellent work adding support for parallel processing of many database activities. It would be great to see SQLite be able to exploit the extra processing power of multiple cores. This is not a request for handling multiple simultaneous transactions - it is a request to have single transactions be processed across multiple cores. Oracle also supports a rich mix of partitioning features. Partitioning allows one to divide a table and/or index into logical subsets that allow additional query optimizations. Partitioning is also useful for quickly dropping a logical subset of data, eg. if you've partitioned data by month, you can quickly drop your oldest month of data by dropping its partition vs. performing a massive number of individual deletes, followed by a vacuum. Finally, partitions can also support parallelization tasks such as loading large data sets (each partition can be loaded and optionally indexed independently of others) and for building partial result sets for SQL selects (each partition can be queried independently of other partitions). Another interesting Oracle feature is compression. Oracle's compression techniques not only compress data, but also speed up many types of selects. Thinking-out-loud: I wonder if some of Oracle's parallelization and partitioning features could be emulated by creating a physical SQLite database for each logical partition; loading large logical tables quickly by using a separate process to load each 'partition specific' SQLite database, and then creating a high-level code library to translate a high-level SQL commands (insert, update, delete, select) into multiple, 'partition specific' SQLite commands that get executed in parallel. In the case of parallel selects, the intermediate results would be cached to partition specific SQLite databases and then unioned together by a master controlling process to create a logical cursor for processing. Is anyone using similar techniques with very large SQLite tables/databases? Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
Dr. Hipp and others, Thank you for your replies to my question. Regards, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
I fully agree with DRH regarding SQLITE and single user performance. If you need to replace fopen then sqlite is a really really great product. Even if you have some mild concurrency sqlite still does pretty darned good. Oracle excels when you have many users that require concurrent database changes. Oracle performs row level locking and Multi-Versioning on data blocks to achieve concurrency. Oracle can perform Parallel queries. But the best usage of parallel query is for full table scans where the entire table is read. And there are equally sized segments for scanning by the query slaves. Other wise parallel query probably won't help much. --- On Mon, 2/23/09, D. Richard Hipp wrote: > From: D. Richard Hipp > Subject: Re: [sqlite] SQLite vs. Oracle (parallelized) > To: "General Discussion of SQLite Database" > Date: Monday, February 23, 2009, 3:28 PM > On Feb 23, 2009, at 3:54 PM, pyt...@bdurham.com wrote: > > > Dr. Hipp, > > > > When you say "SQLite is way faster than Oracle in > a single-user > > applications" do you mean that SQLite can be > faster than Oracle even > > when Oracle's parallel processing features are > being used? For example > > Oracle's support for parallelization can speed up > table loading from > > an > > external data source, certain SQL selects, and certain > indexing > > operations. > > I don't run Oracle and have no way of verifying the > following. But I > conjecture that from a cold start, you and launch an > application that > uses SQLite, have it do a dozen or so queries, print out > the answer, > and shut down, all before the Oracle server has even booted > up to the > point where it will accept connections. Correct me if I am > wrong. > > Perhaps Oracle will run a gazillion more transactions per > second, > given enough memory and CPUs, and once you get it up and > going. I > have no way of knowing. But then again, that isn't > really the point > of SQLite. > > > > > > > > Are there any plans to enhance SQLite to support some > of Oracle's > > parallel processing or partitioning capabilities? > > Remember: SQLite is not trying to replace Oracle. SQLite > is trying > to replace fopen(). > > For people who are using Oracle as a replacement for > fopen() (as > apparently Angela is) they will likely find that SQLite > makes a far > superior replacement. Or to put it another way, people who > are using > Oracle for a single-user application (low concurrency) will > likely > find that SQLite works much better for them. It has been > my > experience that old-time Oracle users are incredulous at > this > statement, until they actually see a live demonstration. > So I won't > try to argue the point. It is merely my observation. > > On the other hand, nobody things that SQLite is a suitable > database > when you have 1000 separate connections beating on the > database all at > once. > > > > > > > > > Thank you, > > Malcolm > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > D. Richard Hipp > d...@hwaci.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
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Feb 23, 2009, at 3:54 PM, pyt...@bdurham.com wrote: > Dr. Hipp, > > When you say "SQLite is way faster than Oracle in a single-user > applications" do you mean that SQLite can be faster than Oracle even > when Oracle's parallel processing features are being used? For example > Oracle's support for parallelization can speed up table loading from > an > external data source, certain SQL selects, and certain indexing > operations. I don't run Oracle and have no way of verifying the following. But I conjecture that from a cold start, you and launch an application that uses SQLite, have it do a dozen or so queries, print out the answer, and shut down, all before the Oracle server has even booted up to the point where it will accept connections. Correct me if I am wrong. Perhaps Oracle will run a gazillion more transactions per second, given enough memory and CPUs, and once you get it up and going. I have no way of knowing. But then again, that isn't really the point of SQLite. > > > Are there any plans to enhance SQLite to support some of Oracle's > parallel processing or partitioning capabilities? Remember: SQLite is not trying to replace Oracle. SQLite is trying to replace fopen(). For people who are using Oracle as a replacement for fopen() (as apparently Angela is) they will likely find that SQLite makes a far superior replacement. Or to put it another way, people who are using Oracle for a single-user application (low concurrency) will likely find that SQLite works much better for them. It has been my experience that old-time Oracle users are incredulous at this statement, until they actually see a live demonstration. So I won't try to argue the point. It is merely my observation. On the other hand, nobody things that SQLite is a suitable database when you have 1000 separate connections beating on the database all at once. > > > Thank you, > Malcolm > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
> > Are there any plans to enhance SQLite to support some of Oracle's > parallel processing or partitioning capabilities? > Malcolm, I realized that you're asking Richard, and not the peanut gallery, but I figured I might as well ask out of curiosity: why do you want to see these features in SQLite? Cheers, Billy On Mon, Feb 23, 2009 at 3:54 PM, wrote: > Dr. Hipp, > > When you say "SQLite is way faster than Oracle in a single-user > applications" do you mean that SQLite can be faster than Oracle even > when Oracle's parallel processing features are being used? For example > Oracle's support for parallelization can speed up table loading from an > external data source, certain SQL selects, and certain indexing > operations. > > Are there any plans to enhance SQLite to support some of Oracle's > parallel processing or partitioning capabilities? > > Thank you, > Malcolm > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Billy Gray wg...@zetetic.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite vs. Oracle (parallelized)
Dr. Hipp, When you say "SQLite is way faster than Oracle in a single-user applications" do you mean that SQLite can be faster than Oracle even when Oracle's parallel processing features are being used? For example Oracle's support for parallelization can speed up table loading from an external data source, certain SQL selects, and certain indexing operations. Are there any plans to enhance SQLite to support some of Oracle's parallel processing or partitioning capabilities? Thank you, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users