Re: Embedded Database Recommendations?

In article <uzlont6l2.fsf@xxxxxxxxxxxxxxx>, p.kootsookos@xxxxxxxxxxxxxxx
says...
Paul Carpenter <paul@xxxxxxxxxxxxxxxxxxxxxxxxxxx> writes:

Hmm my working gateway email server uses mySQL, originally on a sub GHz
processor and 256MB of RAM, Got upgraded to a newer nano-ATX board
1GHz and 1GB RAM. System runs Apache and email server and several other
apps, some of them using the half dozen databases.

Thanks, all good information.

However the system is so lightly loaded, the real question on SELECT
speeds and memory footprint depends on

Size and complexity of database
Number of concurrent users (access to database)
Number of SELECT requests per min/per hour...
What forms of caching are being used
etc....

The database will be large (e.g. tables of 1,000,000 or more entries),
but not particularly complex.

There weill only be one application accessing the database at a time
(or at all).

Assuming only one (concurrent) connection, I wonder why the number of
select requests per time period is a factor (it clearly will be for
multiple connections).

This will determine
how much data is moving around,
so determines how fast a storage unit (and interface),
how much needs to be buffered in memory, to keep up.
Could the serving be better served by being done from a RAM copy.
Can the processor/OS/database software keep up with the expected
number of requests.
Are the vast majority of database operations ONLY Read, and the
database updated once a day/hour/week/etc...

What sort of response time is a MUST have, would be nice, or would
be great! In other words how fast *must* the data be returned.

If you only make 1 request a second, there are a hell of a lot of
solutions, whereby cost of processor/RAM and size become more of the
issue in determining what software to use.

If you make 10,000 requests a second, you better have a really fast
processor with lots of RAM, possibly with RAM copy (or RAMdisk) for
the database in live use. Here the software becomes more of an issue
that determines the host hardware/software.

I suppose what I'm looking for is how long it takes to do:

SELECT * FROM SomeTable WHERE SomeField = 'SomeValue'

So simple requests, but what is the dataset size?
Number of fields in a record?
Fixed or variable length length fields in the records?

and SomeField is the primary key. By "how long" I mean mean, standard
deviation, and [if possible] worst case times.

To read a 10 byte or 10MB record? (yes some people have fields in the
SAME database that is a large picture or other document!)

Standard deviation on a system doing NOTHING else?

What type of system are you using to benchmark this on?

The other important issue is

What else is running
How much processor time available
How much storage access time available?
(i.e. does this application have near total
allocation of storage unit or is somebody
streaming video to/from the storage unit
at the same time!)

There is NO simple answer to questions like this.

Consider this analogy

Take a deck of cards

Shuffle the deck (our database)

Now consider these requests

Find the 15th card down - very fast and easy to find

Find a heart of any size - slower but easy.

Find All the Three's - More complicated

Find the 5 of hearts - more complicated

A lot depends on how your data is organised and normalised and
many other issues, you have not given the details on.

--
Paul Carpenter | paul@xxxxxxxxxxxxxxxxxxxxxxxxxxx
<http://www.pcserviceselectronics.co.uk/> PC Services
<http://www.pcserviceselectronics.co.uk/fonts/> Timing Diagram Font
<http://www.gnuh8.org.uk/> GNU H8 - compiler & Renesas H8/H8S/H8 Tiny
<http://www.badweb.org.uk/> For those web sites you hate
.

Relevant Pages

  • Re: performance optimization?
    ... need more RAM. ... Only if your machine is IO limited by your hard drives does it make much ... I do run database software and even with that I have ... # semaphore maximum value: 32767 MAX ...
    (comp.os.linux.misc)
  • Re: out of memory
    ... read only the smaller file into a hash. ... the smaller file will fit into RAM. ... Depending upon the sorting algorithm this would be Ologor ... put your relevant data into a database and use ...
    (comp.lang.perl.misc)
  • ANN: NexusDB Breaks the 2GB database RAM cache barrier
    ... Your customer has a huge database with many gigabytes of data. ... Unfortunately, because RAM for the ... You could for instance run NexusDB Server AWE ... The best part of the NexusDB Serer AWE edition is that it is a drop-in ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: Safely timing out DBI queries
    ... I realize that this is very specific to the database, however, it may be ... The problem is killing requests that are processing for too long. ... If you want kill them safely, you may not be able to kill them until ... Toute utilisation de ce ...
    (perl.dbi.users)
  • Re: Issues with Store.exe dying during Backup (using Backup Exec 10)
    ... database is basically a big mapped file. ... fit into RAM, so eachange maps pages into RAM, more specifically the ... When buffers in RAM become ... during a brick level backup would make sense in this scenario; ...
    (microsoft.public.exchange.admin)