You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by "Differentiated Software Solutions Pvt. Ltd" <di...@vsnl.com> on 2000/10/11 09:06:39 UTC
Fast DB access
Hi,
We have an application where we will have to service as high as 50 queries a second.
We've discovered that most database just cannot keep pace.
The only option we know is to service queries out of flat files.
Can somebody give us pointers o n what modules are available to create flat file based database.
Specically we want a mechanism to be able service queries which can return rows where values are greater than specified value.
We are experiementing currently with dbm and DB::File. These seem to handle hashes quite comfortably. How do we handle these inequality queries.
Thanks,
Murali
Differentiated Software Solutions Pvt. Ltd.
176, Ground Floor, 6th Main,
2nd Block, RT Nagar
Bangalore - 560032
Phone : 91 80 3431470
www.diffs-india.com
Re: Fast DB access
Posted by "Differentiated Software Solutions Pvt. Ltd" <di...@vsnl.com>.
Dear Tim,
As you had rightly pointed out we have data which is not volatile. This data
gets updated once an hour by another process (cron job). Concurrency is not
really an issue, because we are not updating the data.
We're now continuing our benchmark on some scaling issues.... basically when
does dbm degenerate. We are increasing number of entries in the dbm file to
see when it will break.
Murali
Differentiated Software Solutions Pvt. Ltd.
176, Ground Floor, 6th Main,
2nd Block, RT Nagar
Bangalore - 560032
Phone : 91 80 3431470
www.diffs-india.com
----- Original Message -----
From: Tim Sweetman <ti...@aldigital.co.uk>
To: Differentiated Software Solutions Pvt. Ltd <di...@vsnl.com>
Cc: <mo...@apache.org>
Sent: Thursday, November 09, 2000 8:59 PM
Subject: Re: Fast DB access
> Hi,
>
> Firstly, thanks for bringing these results back to the mailing list...
> having seen this sort of problem previously, but without (IIRC) having
> done side-by-side comparisons between these various techniques, I'm keen
> to see what you find.
>
> "Differentiated Software Solutions Pvt. Ltd" wrote:
> > 2. Building the entire DB into a hash variable inside the mod_perl
program
> > is the fastest.... we found it to be 25 times faster than querying a
> > postgres database !!
> > 3. We have a problem rebuilding this database in the ram.... even say
every
> > 1000 requests. We tried using dbm and found it a good compromise
solution.
> > We found that it is about 8 times faster than postgres querying.
>
> I assume from this that your data changes, but slowly, and you're
> getting better performance by accepting that your data be slightly out
> of date.
>
> > 4. Another surprising finding was.... we built a denormalised db on the
> > Linux file system itself, by using the directory and file name as the
key on
> > which we wanted to search. We found that dbm was faster than this.
>
> I'm curious about how you're dealing with the concurrency aspect with
> solutions 2-3. My guess is that, for 2, you're simply storing a hash in
> the memory, which means that each Apache child has its own copy. There
> will, every 1000 requests in that child, be the overhead of querying the
> DB & rebuilding the hash.
>
> 3 presumably means having only _one_ DBMfile. Do the CGI/mod-Perl
> processes rebuild this periodically, or is this done offline by another
> process? Do the CGI/mod-Perl processes have to wait while writes are
> going on?
>
> Cheers
>
> --
> Tim Sweetman
> A L Digital
> ---- moving sideways --->
Re: Fast DB access
Posted by Tim Sweetman <ti...@aldigital.co.uk>.
Hi,
Firstly, thanks for bringing these results back to the mailing list...
having seen this sort of problem previously, but without (IIRC) having
done side-by-side comparisons between these various techniques, I'm keen
to see what you find.
"Differentiated Software Solutions Pvt. Ltd" wrote:
> 2. Building the entire DB into a hash variable inside the mod_perl program
> is the fastest.... we found it to be 25 times faster than querying a
> postgres database !!
> 3. We have a problem rebuilding this database in the ram.... even say every
> 1000 requests. We tried using dbm and found it a good compromise solution.
> We found that it is about 8 times faster than postgres querying.
I assume from this that your data changes, but slowly, and you're
getting better performance by accepting that your data be slightly out
of date.
> 4. Another surprising finding was.... we built a denormalised db on the
> Linux file system itself, by using the directory and file name as the key on
> which we wanted to search. We found that dbm was faster than this.
I'm curious about how you're dealing with the concurrency aspect with
solutions 2-3. My guess is that, for 2, you're simply storing a hash in
the memory, which means that each Apache child has its own copy. There
will, every 1000 requests in that child, be the overhead of querying the
DB & rebuilding the hash.
3 presumably means having only _one_ DBMfile. Do the CGI/mod-Perl
processes rebuild this periodically, or is this done offline by another
process? Do the CGI/mod-Perl processes have to wait while writes are
going on?
Cheers
--
Tim Sweetman
A L Digital
---- moving sideways --->
Re: Fast DB access
Posted by Perrin Harkins <pe...@primenet.com>.
On Thu, 9 Nov 2000, Differentiated Software Solutions Pvt. Ltd wrote:
> When we rebuild the hash in the RAM it takes too much time.
Did you try using Storable as the data format? It has a function to load
from files which is very fast.
- Perrin
Re: Fast DB access
Posted by "Differentiated Software Solutions Pvt. Ltd" <di...@vsnl.com>.
Hi,
When we rebuild the hash in the RAM it takes too much time.
Other questions, my collegues will answer.
Murali
Differentiated Software Solutions Pvt. Ltd.
176, Ground Floor, 6th Main,
2nd Block, RT Nagar
Bangalore - 560032
Phone : 91 80 3431470
www.diffs-india.com
----- Original Message -----
From: Perrin Harkins <pe...@primenet.com>
To: Differentiated Software Solutions Pvt. Ltd <di...@vsnl.com>
Cc: <mo...@apache.org>
Sent: Thursday, November 09, 2000 12:19 AM
Subject: Re: Fast DB access
> "Differentiated Software Solutions Pvt. Ltd" wrote:
> > 3. We have a problem rebuilding this database in the ram.... even say
every
> > 1000 requests.
>
> What problem are you having with it?
>
> > We tried using dbm and found it a good compromise solution.
> > We found that it is about 8 times faster than postgres querying.
>
> Some dbm implementations are faster than others. Depending on your data
> size, you may want to try a couple of them.
>
> > 4. Another surprising finding was.... we built a denormalised db on the
> > Linux file system itself, by using the directory and file name as the
key on
> > which we wanted to search. We found that dbm was faster than this.
>
> Did you end up with a large number of files in one directory? When
> using the file system in this way, it's a common practice to hash the
> key you're using and then split that across multiple directories to
> prevent too many files from building up in one and slowing things down.
>
> For example:
>
> "my_key" --> "dHodeifehH" --> /usr/local/data/dH/odeifehH
>
> Also, you could try using mmap for reading the files, or possibly the
> Cache::Mmap module.
>
> > We're carrying out more tests to see how scaleable is dbm.
>
> If you're using read-only data, you can leave the dbm handles persistent
> between connections. That will speed things up.
>
> You could look at BerkeleyDB, which has a built-in shared memory buffer
> and page-level locking.
>
> You could also try IPC::MM, which offers a shared memory hash written in
> C with a perl interface.
>
> > Hope these findings are useful to others.
>
> They are. Keep 'em coming.
>
> - Perrin
Re: Fast DB access
Posted by barries <ba...@slaysys.com>.
On Wed, Nov 08, 2000 at 10:49:00AM -0800, Perrin Harkins wrote:
>
> Also, you could try using mmap for reading the files, or possibly the
> Cache::Mmap module.
If you do play with mmap, note that it can lose some or all of it's
effeciency in SMP environments, or so I've read.
- Barrie
Re: Fast DB access
Posted by Perrin Harkins <pe...@primenet.com>.
"Differentiated Software Solutions Pvt. Ltd" wrote:
> 3. We have a problem rebuilding this database in the ram.... even say every
> 1000 requests.
What problem are you having with it?
> We tried using dbm and found it a good compromise solution.
> We found that it is about 8 times faster than postgres querying.
Some dbm implementations are faster than others. Depending on your data
size, you may want to try a couple of them.
> 4. Another surprising finding was.... we built a denormalised db on the
> Linux file system itself, by using the directory and file name as the key on
> which we wanted to search. We found that dbm was faster than this.
Did you end up with a large number of files in one directory? When
using the file system in this way, it's a common practice to hash the
key you're using and then split that across multiple directories to
prevent too many files from building up in one and slowing things down.
For example:
"my_key" --> "dHodeifehH" --> /usr/local/data/dH/odeifehH
Also, you could try using mmap for reading the files, or possibly the
Cache::Mmap module.
> We're carrying out more tests to see how scaleable is dbm.
If you're using read-only data, you can leave the dbm handles persistent
between connections. That will speed things up.
You could look at BerkeleyDB, which has a built-in shared memory buffer
and page-level locking.
You could also try IPC::MM, which offers a shared memory hash written in
C with a perl interface.
> Hope these findings are useful to others.
They are. Keep 'em coming.
- Perrin
Re: Fast DB access
Posted by "Differentiated Software Solutions Pvt. Ltd" <di...@vsnl.com>.
Yes. The tables were indexed.
Otherwise we might have seen even more spectacular results !!!!
Murali
----- Original Message -----
From: G.W. Haywood <ge...@www.jubileegroup.co.uk>
To: Differentiated Software Solutions Pvt. Ltd <di...@vsnl.com>
Cc: <mo...@apache.org>
Sent: Wednesday, November 08, 2000 5:44 PM
Subject: Re: Fast DB access
> Hi there,
>
> On Wed, 8 Nov 2000, Differentiated Software Solutions Pvt. Ltd wrote:
>
> > We are returning after extensive tests of various options suggested.
>
> Did you try different indexing mechanisms in your tests?
>
> 73,
> Ged.
>
Re: Fast DB access
Posted by "G.W. Haywood" <ge...@www.jubileegroup.co.uk>.
Hi there,
On Wed, 8 Nov 2000, Differentiated Software Solutions Pvt. Ltd wrote:
> We are returning after extensive tests of various options suggested.
Did you try different indexing mechanisms in your tests?
73,
Ged.
Re: Fast DB access
Posted by "Differentiated Software Solutions Pvt. Ltd" <di...@vsnl.com>.
Hi,
We are returning after extensive tests of various options suggested.
First, we are not entering into the debate about well designed DBs and
database can handle lots of queries and all that. Assume that we have an
app.(an adserver) which dbs don't support well.. i.e., fairly complex
queries to be services quickly.
Some of the things we've found are ....
1. DBD::RAM is quite slow !! We presume this is because the SQL's have to be
parsed everytime we make requests
2. Building the entire DB into a hash variable inside the mod_perl program
is the fastest.... we found it to be 25 times faster than querying a
postgres database !!
3. We have a problem rebuilding this database in the ram.... even say every
1000 requests. We tried using dbm and found it a good compromise solution.
We found that it is about 8 times faster than postgres querying.
4. Another surprising finding was.... we built a denormalised db on the
Linux file system itself, by using the directory and file name as the key on
which we wanted to search. We found that dbm was faster than this.
We're carrying out more tests to see how scaleable is dbm. Hope these
findings are useful to others.
Thanks for all the help.
Murali
Differentiated Software Solutions Pvt. Ltd.
176, Ground Floor, 6th Main,
2nd Block, RT Nagar
Bangalore - 560032
Phone : 91 80 3431470
www.diffs-india.com
----- Original Message -----
From: Francesc Guasch <fr...@etsetb.upc.es>
To: Differentiated Software Solutions Pvt. Ltd <di...@vsnl.com>
Cc: <mo...@apache.org>
Sent: Wednesday, October 11, 2000 1:56 PM
Subject: Re: Fast DB access
> > "Differentiated Software Solutions Pvt. Ltd" wrote:
> >
> > Hi,
> >
> > We have an application where we will have to service as high as 50
> > queries a second.
> > We've discovered that most database just cannot keep pace.
> >
> > The only option we know is to service queries out of flat files.
>
> There is a DBD module : DBD::Ram. If you got enough memory
> or there is not many data it could be what you need.
>
> I also have seen recently a post about a new DBD module for
> CSV files, in addition of DBD::CSV, try
>
> http://search.cpan.org
>
> --
> - frankie -
Re: Fast DB access
Posted by Francesc Guasch <fr...@etsetb.upc.es>.
> "Differentiated Software Solutions Pvt. Ltd" wrote:
>
> Hi,
>
> We have an application where we will have to service as high as 50
> queries a second.
> We've discovered that most database just cannot keep pace.
>
> The only option we know is to service queries out of flat files.
There is a DBD module : DBD::Ram. If you got enough memory
or there is not many data it could be what you need.
I also have seen recently a post about a new DBD module for
CSV files, in addition of DBD::CSV, try
http://search.cpan.org
--
- frankie -
Re: Fast DB access
Posted by Joe Schaefer <jo...@sunstarsys.com>.
"Differentiated Software Solutions Pvt. Ltd" <di...@vsnl.com> writes:
> We have an application where we will have to service as high as 50 =
> queries a second.
> We've discovered that most database just cannot keep pace.
>
> The only option we know is to service queries out of flat files.
> Can somebody give us pointers o n what modules are available to create =
> flat file based database.
> Specically we want a mechanism to be able service queries which can =
> return rows where values are greater than specified value.
> We are experiementing currently with dbm and DB::File. These seem to =
> handle hashes quite comfortably. How do we handle these inequality =
> queries.
You might look at BerkeleyDB's cursor implementation, although
50 queries / second should be do-able with mysql and optimized tables.
Also consider cacheing the results (as others have suggested),
if many of the queries are reused & not changed between queries.
--
Joe Schaefer
Re: Fast DB access
Posted by Tim Bunce <Ti...@ig.co.uk>.
On Thu, Nov 09, 2000 at 11:54:42AM -0800, Ask Bjoern Hansen wrote:
> On Wed, 11 Oct 2000, Matt Sergeant wrote:
>
> If you're always looking stuff up on simple ID numbers and
> "stuff" is a very simple data structure, then I doubt any DBMS can
> beat
>
> open D, "/data/1/12/123456" or ...
>
> from a fast local filesystem.
And it's worth pointing out that the ../1/12/.. scheme (where a prefix
of the name, 123456 in this case, is used to spread the names across
many subdirs) is essential to get good performance from most unix
filesystems when you have many names to store.
Keywords: triple inode indirection is bad (or something like that :-)
Tim.
Re: Fast DB access
Posted by Leon Brocard <ac...@astray.com>.
Matt Sergeant sent the following bits through the ether:
> Note that Theo Schlossnagel was saying over lunch at ApacheCon that if
> your filename has more than 8 characters on Linux (ext2fs) it skips from a
> hashed algorithm to a linear algorithm (or something to that affect). So
> go careful there. I don't have more details or a URL for any information
> on this though.
I've had a look at 2.2.14 and while there are some limits[1] (note,
I'm not a kernel hacker), some simple tests[2] haven't pointed to a
performance degredation. I'd suggest some larger tests on bigger
boxes, as this'd be interesting to know...
Leon
[1] such as DNAME_INLINE_LEN (16), see fs/dcache.c
[2] 2000 files, with name lengths from 2..20 (substrings of md5 stuff,
of course), both reading and writing random files
--
Leon Brocard.............................http://www.astray.com/
yapc::Europe............................http://yapc.org/Europe/
... New and improved Brocard: now with Template Toolkit!
Re: Fast DB access
Posted by clayton cottingham <cl...@marketingchallenge.com>.
Matthew Byng-Maddick wrote:
>
> On Fri, 10 Nov 2000, Les Mikesell wrote:
> [ReiserFS]
> > production just to avoid the possibility of a slow fsck after a crash,
> > but it is enormously faster at creating and deleting files too because
> > everything is indexed so it would be an ideal stash for fast changing
> > session data. If you don't trust it for the whole system you can just
> > use it on one partition for the session database. Several Linux
> > distributions include it now.
>
> As I recall, it has a problem if you manage to have a hash collision in
> your naming, IIRC it was something to do with the way in which it
> extends the hash it uses for dents. This is, of course, FUD. :) I've also
> heard bad things about his attitude...
>
> FreeBSD 4's softupdates make ufs rather wonderful too. These basically
> make UFS's write() calls asynchronous, and hence much faster. They also
> keep dependencies, and can be very good for spools....
>
> MBM
>
> --
> It is wrong always, everywhere and for everyone to believe anything upon
> insufficient evidence -- W. K. Clifford
i was told by tom lane , a developer of postgres {possible head one
afaik}
said around last year that it ran the best on freebsd /scsi
he didnt like using linux at the time because
of ext2 paging probs but now id sure like to know about linux and
reiserfs
since ive been running mandrake 7.2 on reiserfs, and postgres 7.01 seems
to run very fine
Re: Fast DB access
Posted by Matthew Byng-Maddick <mb...@colondot.net>.
On Fri, 10 Nov 2000, Les Mikesell wrote:
[ReiserFS]
> production just to avoid the possibility of a slow fsck after a crash,
> but it is enormously faster at creating and deleting files too because
> everything is indexed so it would be an ideal stash for fast changing
> session data. If you don't trust it for the whole system you can just
> use it on one partition for the session database. Several Linux
> distributions include it now.
As I recall, it has a problem if you manage to have a hash collision in
your naming, IIRC it was something to do with the way in which it
extends the hash it uses for dents. This is, of course, FUD. :) I've also
heard bad things about his attitude...
FreeBSD 4's softupdates make ufs rather wonderful too. These basically
make UFS's write() calls asynchronous, and hence much faster. They also
keep dependencies, and can be very good for spools....
MBM
--
It is wrong always, everywhere and for everyone to believe anything upon
insufficient evidence -- W. K. Clifford
Re: Fast DB access
Posted by Les Mikesell <le...@home.com>.
I think it is at least to the point where commercial code would be
released - free software never has any pressure to make claims
of stability even when they can... A lot of places are using it in
production just to avoid the possibility of a slow fsck after a crash,
but it is enormously faster at creating and deleting files too because
everything is indexed so it would be an ideal stash for fast changing
session data. If you don't trust it for the whole system you can just
use it on one partition for the session database. Several Linux
distributions include it now.
----- Original Message -----
From: "Gunther Birznieks" <gu...@extropia.com>
To: <mo...@apache.org>
Sent: Friday, November 10, 2000 12:58 AM
Subject: Re: Fast DB access
> Isn't that a beta-level filesystem?
>
> At 12:47 AM 11/10/2000 -0600, Les Mikesell wrote:
>
> >----- Original Message -----
> >From: "Tim Bunce" <Ti...@ig.co.uk>
> >
> > > > > If you're always looking stuff up on simple ID numbers and
> > > > > "stuff" is a very simple data structure, then I doubt any DBMS can
> > > > > beat
> > > > >
> > > > > open D, "/data/1/12/123456" or ...
> > > > >
> > > > > from a fast local filesystem.
> > > >
> > > > Note that Theo Schlossnagel was saying over lunch at ApacheCon that
if
> > > > your filename has more than 8 characters on Linux (ext2fs) it skips
from
> >a
> > > > hashed algorithm to a linear algorithm (or something to that
affect). So
> > > > go careful there. I don't have more details or a URL for any
information
> > > > on this though.
> > >
> > > Similarly on Solaris (and perhaps most SysV derivatives) path
component
> > > names longer than 16 chars (configurable) don't go into the inode
> > > lookup cache and so require a filesystem directory lookup.
> >
> >If you are building a new system with this scheme, try ReiserFS on
> >a Linux box. It does not suffer from the usual problems when
> >you put a large number of files in one directory and is extremely
> >fast at lookups.
> >
> > Les Mikesell
> > lesmikesell@home.com
>
> __________________________________________________
> Gunther Birznieks (gunther.birznieks@extropia.com)
> eXtropia - The Web Technology Company
> http://www.extropia.com/
>
Re: Fast DB access
Posted by Gunther Birznieks <gu...@extropia.com>.
Isn't that a beta-level filesystem?
At 12:47 AM 11/10/2000 -0600, Les Mikesell wrote:
>----- Original Message -----
>From: "Tim Bunce" <Ti...@ig.co.uk>
>
> > > > If you're always looking stuff up on simple ID numbers and
> > > > "stuff" is a very simple data structure, then I doubt any DBMS can
> > > > beat
> > > >
> > > > open D, "/data/1/12/123456" or ...
> > > >
> > > > from a fast local filesystem.
> > >
> > > Note that Theo Schlossnagel was saying over lunch at ApacheCon that if
> > > your filename has more than 8 characters on Linux (ext2fs) it skips from
>a
> > > hashed algorithm to a linear algorithm (or something to that affect). So
> > > go careful there. I don't have more details or a URL for any information
> > > on this though.
> >
> > Similarly on Solaris (and perhaps most SysV derivatives) path component
> > names longer than 16 chars (configurable) don't go into the inode
> > lookup cache and so require a filesystem directory lookup.
>
>If you are building a new system with this scheme, try ReiserFS on
>a Linux box. It does not suffer from the usual problems when
>you put a large number of files in one directory and is extremely
>fast at lookups.
>
> Les Mikesell
> lesmikesell@home.com
__________________________________________________
Gunther Birznieks (gunther.birznieks@extropia.com)
eXtropia - The Web Technology Company
http://www.extropia.com/
Re: Fast DB access
Posted by Les Mikesell <le...@home.com>.
----- Original Message -----
From: "Tim Bunce" <Ti...@ig.co.uk>
> > > If you're always looking stuff up on simple ID numbers and
> > > "stuff" is a very simple data structure, then I doubt any DBMS can
> > > beat
> > >
> > > open D, "/data/1/12/123456" or ...
> > >
> > > from a fast local filesystem.
> >
> > Note that Theo Schlossnagel was saying over lunch at ApacheCon that if
> > your filename has more than 8 characters on Linux (ext2fs) it skips from
a
> > hashed algorithm to a linear algorithm (or something to that affect). So
> > go careful there. I don't have more details or a URL for any information
> > on this though.
>
> Similarly on Solaris (and perhaps most SysV derivatives) path component
> names longer than 16 chars (configurable) don't go into the inode
> lookup cache and so require a filesystem directory lookup.
If you are building a new system with this scheme, try ReiserFS on
a Linux box. It does not suffer from the usual problems when
you put a large number of files in one directory and is extremely
fast at lookups.
Les Mikesell
lesmikesell@home.com
Re: Fast DB access
Posted by Bill Moseley <mo...@hank.org>.
At 09:20 PM 11/09/00 +0000, Tim Bunce wrote:
>On Thu, Nov 09, 2000 at 08:27:29PM +0000, Matt Sergeant wrote:
>> On Thu, 9 Nov 2000, Ask Bjoern Hansen wrote:
>> > If you're always looking stuff up on simple ID numbers and
>> > "stuff" is a very simple data structure, then I doubt any DBMS can
>> > beat
>> >
>> > open D, "/data/1/12/123456" or ...
>> >
>> > from a fast local filesystem.
>>
>> Note that Theo Schlossnagel was saying over lunch at ApacheCon that if
>> your filename has more than 8 characters on Linux (ext2fs) it skips from a
>> hashed algorithm to a linear algorithm (or something to that affect). So
>> go careful there. I don't have more details or a URL for any information
>> on this though.
>
>Similarly on Solaris (and perhaps most SysV derivatives) path component
>names longer than 16 chars (configurable) don't go into the inode
>lookup cache and so require a filesystem directory lookup.
Ok, possibly 8 chars in Linux and 16 under Solaris. Anything else to
consider regrading the maximum number of files in a given directory?
How about issues regarding file size? If you had larger files/records
would DBM or RDBMS provider larger cache sizes?
Bill Moseley
mailto:moseley@hank.org
Re: Fast DB access
Posted by Tim Bunce <Ti...@ig.co.uk>.
On Thu, Nov 09, 2000 at 08:27:29PM +0000, Matt Sergeant wrote:
> On Thu, 9 Nov 2000, Ask Bjoern Hansen wrote:
>
> > On Wed, 11 Oct 2000, Matt Sergeant wrote:
> >
> > > > > Most modern DBMS software should be able to handle 50 queries per second
> > > > > on decent hardware, provided the conditions are right. You're not going to
> > > > > get anything better with flat files.
> > > >
> > > > Hmm... I guess it all depends on what your queries look like, but you can
> > > > get better results from flat files if you put them in a percise layout.
> > > > Granted if you are talking about having a million lines in a single
> > > > flat file, then I definately agree with you.
> > >
> > > I think the limiting factors are quite a bit sooner than a million
> > > lines. What I'm trying to get across is that developers should be
> > > focussing on letting the DBMS do what a DBMS does best - queries. The DB
> > > is far better placed (and generally better developed) to do the
> > > optimisation than trying to come up with a flat file strategy that works
> > > with your system.
> >
> > If you're always looking stuff up on simple ID numbers and
> > "stuff" is a very simple data structure, then I doubt any DBMS can
> > beat
> >
> > open D, "/data/1/12/123456" or ...
> >
> > from a fast local filesystem.
>
> Note that Theo Schlossnagel was saying over lunch at ApacheCon that if
> your filename has more than 8 characters on Linux (ext2fs) it skips from a
> hashed algorithm to a linear algorithm (or something to that affect). So
> go careful there. I don't have more details or a URL for any information
> on this though.
Similarly on Solaris (and perhaps most SysV derivatives) path component
names longer than 16 chars (configurable) don't go into the inode
lookup cache and so require a filesystem directory lookup.
[As far as I recall. No URL either :-]
Tim.
Re: Fast DB access
Posted by Matt Sergeant <ma...@sergeant.org>.
On Thu, 9 Nov 2000, Ask Bjoern Hansen wrote:
> On Wed, 11 Oct 2000, Matt Sergeant wrote:
>
> > > > Most modern DBMS software should be able to handle 50 queries per second
> > > > on decent hardware, provided the conditions are right. You're not going to
> > > > get anything better with flat files.
> > >
> > > Hmm... I guess it all depends on what your queries look like, but you can
> > > get better results from flat files if you put them in a percise layout.
> > > Granted if you are talking about having a million lines in a single
> > > flat file, then I definately agree with you.
> >
> > I think the limiting factors are quite a bit sooner than a million
> > lines. What I'm trying to get across is that developers should be
> > focussing on letting the DBMS do what a DBMS does best - queries. The DB
> > is far better placed (and generally better developed) to do the
> > optimisation than trying to come up with a flat file strategy that works
> > with your system.
>
> If you're always looking stuff up on simple ID numbers and
> "stuff" is a very simple data structure, then I doubt any DBMS can
> beat
>
> open D, "/data/1/12/123456" or ...
>
> from a fast local filesystem.
Note that Theo Schlossnagel was saying over lunch at ApacheCon that if
your filename has more than 8 characters on Linux (ext2fs) it skips from a
hashed algorithm to a linear algorithm (or something to that affect). So
go careful there. I don't have more details or a URL for any information
on this though.
FWIW, I need to fix this in AxKit, since it uses MD5 hex hashes as
filenames...
--
<Matt/>
/|| ** Director and CTO **
//|| ** AxKit.com Ltd ** ** XML Application Serving **
// || ** http://axkit.org ** ** XSLT, XPathScript, XSP **
// \\| // ** Personal Web Site: http://sergeant.org/ **
\\//
//\\
// \\
Re: Fast DB access
Posted by Ask Bjoern Hansen <as...@apache.org>.
On Wed, 11 Oct 2000, Matt Sergeant wrote:
> > > Most modern DBMS software should be able to handle 50 queries per second
> > > on decent hardware, provided the conditions are right. You're not going to
> > > get anything better with flat files.
> >
> > Hmm... I guess it all depends on what your queries look like, but you can
> > get better results from flat files if you put them in a percise layout.
> > Granted if you are talking about having a million lines in a single
> > flat file, then I definately agree with you.
>
> I think the limiting factors are quite a bit sooner than a million
> lines. What I'm trying to get across is that developers should be
> focussing on letting the DBMS do what a DBMS does best - queries. The DB
> is far better placed (and generally better developed) to do the
> optimisation than trying to come up with a flat file strategy that works
> with your system.
If you're always looking stuff up on simple ID numbers and
"stuff" is a very simple data structure, then I doubt any DBMS can
beat
open D, "/data/1/12/123456" or ...
from a fast local filesystem.
> I really think that sometimes going for a flat file layout *can* be much
> > more reliable and scalable then RDBMS software. It all really depends on
> > what you plan to do with the data and what you would like to get out of
> > it.
>
> I think you chose the wrong words there. I think a flat file layout can be
> more performant than an RDBMS, but I don't think its going to be
> more reliable or scalable than an RDBMS. There are far too many locking
> issues and transaction issues necessary for the terms "reliable and
> scalable", unless you're willing to spend a few years re-coding Oracle :-)
Not if your scenario is writes on one box (possibly in only one
process) and reads on many boxes, many processes - then all that
becomes really simple.
In other words: it all depends. :-)
- ask (who is not using "flatfile databases" anywhere currently,
but think they can useful anyway)
--
ask bjoern hansen - <http://www.netcetera.dk/~ask/>
more than 70M impressions per day, <http://valueclick.com>
Re: Fast DB access
Posted by "Differentiated Software Solutions Pvt. Ltd" <di...@vsnl.com>.
We would like to add one thing to this.
Different application situations seem to require different approaches. While
RDBMS seem to support say 80% of these situations there are some situations
where we find it not good enough.
We have developed an adserver which has exactly the kind of scenario that
Sander has talked about. Lots of similar queries which are read-only....
data having to be distributed across servers and so on.... RDBMSes (in our
experience) don't seem suited for this.
Murali
Differentiated Software Solutions Pvt. Ltd.
176, Ground Floor, 6th Main,
2nd Block, RT Nagar
Bangalore - 560032
Phone : 91 80 3431470
www.diffs-india.com
----- Original Message -----
From: Sander van Zoest <sa...@covalent.net>
To: Matt Sergeant <ma...@sergeant.org>
Cc: Differentiated Software Solutions Pvt. Ltd <di...@vsnl.com>;
<mo...@apache.org>
Sent: Thursday, October 12, 2000 2:35 AM
Subject: Re: Fast DB access
> On Wed, 11 Oct 2000, Matt Sergeant wrote:
>
> > > I really think that sometimes going for a flat file layout *can* be
much
> > > more reliable and scalable then RDBMS software. It all really depends
on
> > > what you plan to do with the data and what you would like to get out
of
> > > it.
> > I think you chose the wrong words there. I think a flat file layout can
be
> > more performant than an RDBMS, but I don't think its going to be
> > more reliable or scalable than an RDBMS. There are far too many locking
> > issues and transaction issues necessary for the terms "reliable and
> > scalable", unless you're willing to spend a few years re-coding Oracle
:-)
>
> I actually think that there are times that can be all three. Notice how
> I said there are times it can be all three, it definately isn't the case
> all the time. Neither are RDBMS. ;-)
>
> Lots of places use databases for read-only queries. Having a database
> that gets lots of similar queries that are read-only makes it an
> unnecessary single point of failure. Why not use the local disk and
> use rsync to replicate the data around. This way if a machine goes down,
> the others still have a full copy of the content and keep on running.
>
> If you have a lot of data that you need to keep in sync and needs constant
> updating with a random amount of different queries then you get some real
> use out of a RDBMS.
>
> I guess I am just saying that there are a gazillions of ways of doing
things,
> and each tool has something it is good at. File systems are really good
> at serving up read-only content. So why re-invent the wheel? It all really
> depends on what content you are dealing with and how you expect to query
> it and use it.
>
> There is a reason that table optimisation and tuning databases is such
> a sought after skill. Most of these things require different things that
> all rely on the type of content and their use. These things need to be
> taken in consideration on a case by case basis.
>
> You can do things terribly using Oracle and you can do things well using
> Oracle. The same can be said about just about everything. ;-)
>
>
> --
> Sander van Zoest
[sander@covalent.net]
> Covalent Technologies, Inc.
http://www.covalent.net/
> (415) 536-5218
http://www.vanzoest.com/sander/
>
Re: Fast DB access
Posted by Sander van Zoest <sa...@covalent.net>.
On Wed, 11 Oct 2000, Matt Sergeant wrote:
> On Wed, 11 Oct 2000, Sander van Zoest wrote:
> > On Wed, 11 Oct 2000, Matt Sergeant wrote:
> > Lots of places use databases for read-only queries. Having a database
> > that gets lots of similar queries that are read-only makes it an
> > unnecessary single point of failure. Why not use the local disk and
> > use rsync to replicate the data around. This way if a machine goes down,
> > the others still have a full copy of the content and keep on running.
> What is the actual use of the flat files in this case? Wouldn't generating
> your HTML offline be better if your data is that static?
The actual use of the flat files can vary. XML in some sense is a good
example, generated HTML is another. Sometimes a csv or other format
works best. And other times, although technically not being a flat file,
a dbm file could be a good/fast alternative as well. It really depends
on how flexable you need/want to be. XML is definately becoming a
useful alternative here.
It is just that databases can create a lot of unnecessary features/overhead
that can be pre-computed ahead of time.
> > You can do things terribly using Oracle and you can do things well using
> > Oracle. The same can be said about just about everything. ;-)
> You put your point well, and my only remaining point is that I think its
> far far easier to screw up a flat file system by not taking into account
> locking issues (just look at all those perl hit-counters that did it
> wrong), and perhaps some reliability issues, than it is with a real
> database. Caveat emptor, and all that.
I still have some locking issues with the mailing list archive. *grin*
I totally agree that it is far easier to screw up a flat file system.
It might not be as flexable as you really need it to be, because it was
build for a particular query and performance on that in mind. Databases
are great and I am happy to have them. It is just that it isn't that
when you throw down the money to get Oracle, it will be the answer to
all your problems. ;-)
Sometimes an LDAP system can make more sense then an RDBMS. Other times
a distributed system based on DNS. It all depends on what you value
most, how much you control your environment and what you can live with
and what you can't.
Matt,
I am not sure if you are on Dean Gaudet's scalable mailing list
<http://archive.covalent.net/new-httpd/2000/09/0478.xml>, it is
definately a great place to see how people accomplish things with
their problem sets. In some odd way it reminds me of the old
alt.hackers days.
Cheers,
--
Sander van Zoest [sander@covalent.net]
Covalent Technologies, Inc. http://www.covalent.net/
(415) 536-5218 http://www.vanzoest.com/sander/
Re: Fast DB access
Posted by Matt Sergeant <ma...@sergeant.org>.
On Wed, 11 Oct 2000, Sander van Zoest wrote:
> On Wed, 11 Oct 2000, Matt Sergeant wrote:
>
> Lots of places use databases for read-only queries. Having a database
> that gets lots of similar queries that are read-only makes it an
> unnecessary single point of failure. Why not use the local disk and
> use rsync to replicate the data around. This way if a machine goes down,
> the others still have a full copy of the content and keep on running.
What is the actual use of the flat files in this case? Wouldn't generating
your HTML offline be better if your data is that static?
> You can do things terribly using Oracle and you can do things well using
> Oracle. The same can be said about just about everything. ;-)
You put your point well, and my only remaining point is that I think its
far far easier to screw up a flat file system by not taking into account
locking issues (just look at all those perl hit-counters that did it
wrong), and perhaps some reliability issues, than it is with a real
database. Caveat emptor, and all that.
--
<Matt/>
/|| ** Director and CTO **
//|| ** AxKit.com Ltd ** ** XML Application Serving **
// || ** http://axkit.org ** ** XSLT, XPathScript, XSP **
// \\| // ** Personal Web Site: http://sergeant.org/ **
\\//
//\\
// \\
Re: Fast DB access
Posted by Sander van Zoest <sa...@covalent.net>.
On Wed, 11 Oct 2000, Matt Sergeant wrote:
> > I really think that sometimes going for a flat file layout *can* be much
> > more reliable and scalable then RDBMS software. It all really depends on
> > what you plan to do with the data and what you would like to get out of
> > it.
> I think you chose the wrong words there. I think a flat file layout can be
> more performant than an RDBMS, but I don't think its going to be
> more reliable or scalable than an RDBMS. There are far too many locking
> issues and transaction issues necessary for the terms "reliable and
> scalable", unless you're willing to spend a few years re-coding Oracle :-)
I actually think that there are times that can be all three. Notice how
I said there are times it can be all three, it definately isn't the case
all the time. Neither are RDBMS. ;-)
Lots of places use databases for read-only queries. Having a database
that gets lots of similar queries that are read-only makes it an
unnecessary single point of failure. Why not use the local disk and
use rsync to replicate the data around. This way if a machine goes down,
the others still have a full copy of the content and keep on running.
If you have a lot of data that you need to keep in sync and needs constant
updating with a random amount of different queries then you get some real
use out of a RDBMS.
I guess I am just saying that there are a gazillions of ways of doing things,
and each tool has something it is good at. File systems are really good
at serving up read-only content. So why re-invent the wheel? It all really
depends on what content you are dealing with and how you expect to query
it and use it.
There is a reason that table optimisation and tuning databases is such
a sought after skill. Most of these things require different things that
all rely on the type of content and their use. These things need to be
taken in consideration on a case by case basis.
You can do things terribly using Oracle and you can do things well using
Oracle. The same can be said about just about everything. ;-)
--
Sander van Zoest [sander@covalent.net]
Covalent Technologies, Inc. http://www.covalent.net/
(415) 536-5218 http://www.vanzoest.com/sander/
Re: Fast DB access
Posted by Matt Sergeant <ma...@sergeant.org>.
On Wed, 11 Oct 2000, Sander van Zoest wrote:
> On Wed, 11 Oct 2000, Matt Sergeant wrote:
>
> > Most modern DBMS software should be able to handle 50 queries per second
> > on decent hardware, provided the conditions are right. You're not going to
> > get anything better with flat files.
>
> Hmm... I guess it all depends on what your queries look like, but you can
> get better results from flat files if you put them in a percise layout.
> Granted if you are talking about having a million lines in a single
> flat file, then I definately agree with you.
I think the limiting factors are quite a bit sooner than a million
lines. What I'm trying to get across is that developers should be
focussing on letting the DBMS do what a DBMS does best - queries. The DB
is far better placed (and generally better developed) to do the
optimisation than trying to come up with a flat file strategy that works
with your system.
> I really think that sometimes going for a flat file layout *can* be much
> more reliable and scalable then RDBMS software. It all really depends on
> what you plan to do with the data and what you would like to get out of
> it.
I think you chose the wrong words there. I think a flat file layout can be
more performant than an RDBMS, but I don't think its going to be
more reliable or scalable than an RDBMS. There are far too many locking
issues and transaction issues necessary for the terms "reliable and
scalable", unless you're willing to spend a few years re-coding Oracle :-)
--
<Matt/>
/|| ** Director and CTO **
//|| ** AxKit.com Ltd ** ** XML Application Serving **
// || ** http://axkit.org ** ** XSLT, XPathScript, XSP **
// \\| // ** Personal Web Site: http://sergeant.org/ **
\\//
//\\
// \\
Re: Fast DB access
Posted by Sander van Zoest <sa...@covalent.net>.
On Wed, 11 Oct 2000, Matt Sergeant wrote:
> Most modern DBMS software should be able to handle 50 queries per second
> on decent hardware, provided the conditions are right. You're not going to
> get anything better with flat files.
Hmm... I guess it all depends on what your queries look like, but you can
get better results from flat files if you put them in a percise layout.
Granted if you are talking about having a million lines in a single
flat file, then I definately agree with you.
I really think that sometimes going for a flat file layout *can* be much
more reliable and scalable then RDBMS software. It all really depends on
what you plan to do with the data and what you would like to get out of
it.
Cheers,
--
Sander van Zoest [sander@covalent.net]
Covalent Technologies, Inc. http://www.covalent.net/
(415) 536-5218 http://www.vanzoest.com/sander/
Re: Fast DB access
Posted by Matt Sergeant <ma...@sergeant.org>.
On Wed, 11 Oct 2000, Differentiated Software Solutions Pvt. Ltd wrote:
> Hi,
>
> We have an application where we will have to service as high as 50
> queries a second. We've discovered that most database just cannot keep
> pace.
>
> The only option we know is to service queries out of flat files. Can
> somebody give us pointers o n what modules are available to create
> flat file based database. Specically we want a mechanism to be able
> service queries which can return rows where values are greater than
> specified value. We are experiementing currently with dbm and
> DB::File. These seem to handle hashes quite comfortably. How do we
> handle these inequality queries.
I'd venture to suggest you look back at those RDBMS' again. What were you
using that couldn't handle 50 queries a second? What were your queries
like? How was the database optimised? Was the DB using the right indexes?
Most modern DBMS software should be able to handle 50 queries per second
on decent hardware, provided the conditions are right. You're not going to
get anything better with flat files.
--
<Matt/>
/|| ** Director and CTO **
//|| ** AxKit.com Ltd ** ** XML Application Serving **
// || ** http://axkit.org ** ** XSLT, XPathScript, XSP **
// \\| // ** Personal Web Site: http://sergeant.org/ **
\\//
//\\
// \\
Re: Fast DB access
Posted by re...@ch.twi.tudelft.nl.
Hi,
On Wed, 11 Oct 2000, Differentiated Software Solutions Pvt. Ltd wrote:
> Hi,
>
> We have an application where we will have to service as high as 50 queries a second.
> We've discovered that most database just cannot keep pace.
Are all of these queries for the same http-request? In that case you might
have a bad database-structure. You should consider changing your
database-design (do you request many fields one per query?). Otherwise you
can have memory problems, causing your database / webserver to swap a lot,
which decreases performance dramatically. If these are similar queries for
many single requests, you can take a look at the other proposed
cahcing-techniques...
> Thanks,
>
> Murali
Bye,
Remco
/----------------------------------------------------------------------\
| Remco Schaar |
| e-mail: Remco@ch.twi.tudelft.nl |
\----------------------------------------------------------------------/
South Park meets Linux:
- "Oh my God, they killed init!"
- "You bastards!"
Re: Fast DB access
Posted by "Sean D. Cook" <sd...@edutest.com>.
On Wed, 11 Oct 2000, Differentiated Software Solutions Pvt. Ltd wrote:
> Hi,
>
> We have an application where we will have to service as high as 50 queries a second.
> We've discovered that most database just cannot keep pace.
>
> The only option we know is to service queries out of flat files.
> Can somebody give us pointers o n what modules are available to create flat file based database.
> Specically we want a mechanism to be able service queries which can return rows where values are greater than specified value.
> We are experiementing currently with dbm and DB::File. These seem to handle hashes quite comfortably. How do we handle these inequality queries.
>
Something you may want to consider if you are doing large numbers of
read-only transactions on the DB, build them into a large complex data
structure and load them, pre-fork, into ram. Store them in a package
variable. It is extremely fast and scalable.
Sean Cook
Systems Analyst
Edutest.com
Phone: 804.673.2253 1.888.335.8378
email: sdc@edutest.com
__________________________________________________
Save the whales. Collect the whole set.