Re: [sqlite] SQLite and Columnar Databases

2007-12-15 Thread John Stanton
[EMAIL PROTECTED] wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: --- [EMAIL PROTECTED] wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: The reason why I asked is that I haven't had much luck with sqlite3 performance for databases larger than the size of RAM on my machine regardless of PRAGMA

Re: [sqlite] SQLite and Columnar Databases

2007-12-15 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote: > The problem is when inserting into large database that is > indexed, the values being indexed are randomly distributed. > So with each insert, SQLite has to seek to a new random > place in the file to insert the new index entry there. > It does not matter that pages

Re: [sqlite] SQLite and Columnar Databases

2007-12-15 Thread drh
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > The reason why I asked is that I haven't had much luck with sqlite3 > > > performance for databases larger than the size of RAM on my machine > > > regardless of PRAGMA settings. >

Re: [sqlite] SQLite and Columnar Databases

2007-12-15 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > The reason why I asked is that I haven't had much luck with sqlite3 > > performance for databases larger than the size of RAM on my machine > > regardless of PRAGMA settings. > > This is probably do to the cache locality

Re: [sqlite] SQLite and Columnar Databases

2007-12-14 Thread Michael Scharf
This is probably do to the cache locality problem. We know how to fix this, Joe. Would you like to have a go at it? What is the fix to the "cache locality problem"? Michael -- http://MichaelScharf.blogspot.com/ -

RE: [sqlite] SQLite and Columnar Databases

2007-12-14 Thread Tom Briggs
> This model is completely removed from how the data is physically > stored, eg whether in rows first or in columns first, and the > physical store is determined just by the DBMS behind the scenes, and > hence is an implementation detail. The DBMS can arrange how it likes > in order to

Re: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread drh
Joe Wilson <[EMAIL PROTECTED]> wrote: > The reason why I asked is that I haven't had much luck with sqlite3 > performance for databases larger than the size of RAM on my machine > regardless of PRAGMA settings. > This is probably do to the cache locality problem. We know how to fix this, Joe.

RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Darren Duncan
At 7:59 AM -0500 12/13/07, Tom Briggs wrote: > Something I will say about this, for people who don't know, is that this columnar thing is strictly an implementation detail. While I think that this is an oversimplification. That's somewhat like saying that the way you use a sledge

Re: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread drh
Dennis Cote <[EMAIL PROTECTED]> wrote: > > Richard, > > How was the current default page size of 1K determined? Was there any > testing to see if larger page sizes would be beneficial for general use? > Or is it just a historical circumstance (i.e. an arbitrarily chosen > value that has been

Re: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Dennis Cote
[EMAIL PROTECTED] wrote: Whenever you start a new transaction, SQLite has to allocate and clear a bitmap used to record which pages have changes in the database file. The size of this bitmap is proportional to the size of the database file. The size of the bitmap is 256 bytes per megabyte of

Re: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Dennis Cote
Samuel R. Neff wrote: That also brings up the simple solution in that if you know you're db is going to be in the 200GiB range, declare a larger page size before creating the DB. My thoughts exactly. Dennis Cote

RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Samuel R. Neff
products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, December 13, 2007 12:21 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite and Columnar Databases Joe

RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Joe Wilson
TED] > > Sent: Thursday, December 13, 2007 11:51 AM > > To: sqlite-users@sqlite.org > > Subject: RE: [sqlite] SQLite and Columnar Databases > > > > --- Tom Briggs <[EMAIL PROTECTED]> wrote: > > >For clarity, my definition of small is about 200GB, so

Re: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread drh
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- Tom Briggs <[EMAIL PROTECTED]> wrote: > >For clarity, my definition of small is about 200GB, so I'm not > > selling SQLite short here... > > Are you able to get decent performance out of sqlite3 for a 200GB database? > > How much RAM do you have

RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Tom Briggs
Thursday, December 13, 2007 11:51 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLite and Columnar Databases > > --- Tom Briggs <[EMAIL PROTECTED]> wrote: > >For clarity, my definition of small is about 200GB, so I'm not > > selling SQLite short here...

RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Joe Wilson
--- Tom Briggs <[EMAIL PROTECTED]> wrote: >For clarity, my definition of small is about 200GB, so I'm not > selling SQLite short here... Are you able to get decent performance out of sqlite3 for a 200GB database? How much RAM do you have on such a machine?

RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Tom Briggs
> Sent: Wednesday, December 12, 2007 6:12 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] SQLite and Columnar Databases > > Hello, > > There seems to be a high interest in columnar databases recently. > > Is there any plan of supporting data organization as a >

RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Tom Briggs
> Something I will say about this, for people who don't know, is that > this columnar thing is strictly an implementation detail. While I think that this is an oversimplification. That's somewhat like saying that the way you use a sledge hammer is no different than how you use a claw

Re: [sqlite] SQLite and Columnar Databases

2007-12-12 Thread Darren Duncan
At 4:42 AM +0530 12/13/07, Yuvaraj Athur Raghuvir wrote: Hello, There seems to be a high interest in columnar databases recently. Is there any plan of supporting data organization as a columnar database in SQLite? What are the challenges here? Regards, Yuva Something I will say about this, for

[sqlite] SQLite and Columnar Databases

2007-12-12 Thread Yuvaraj Athur Raghuvir
Hello, There seems to be a high interest in columnar databases recently. Is there any plan of supporting data organization as a columnar database in SQLite? What are the challenges here? Regards, Yuva