Thread: Re: [NOVICE] Hardware needed for 15,000,000 record DB?

--On dimanche 21 avril 2002 19:25 -0400 pdg@stratos.net wrote:

> Hello,  :-)
>
> Can someone help me spec out the hardware needed for a simple web-based
> database app?
>
> Basically, that application needs to lookup a single row by its primary
> key. This would be fairly straightforward, except that the table needs to
> contain up to 15 million records.  Each row will contain approximately 25
> variable length CHAR fields with perhaps a total of 3 to 4 kilobytes of
> data per row.
>
> Updates will be done nightly via some sort of a batch process.
>
> What kind of hardware would be needed for this sort of application? The
> queries are not complex, it's just a lot of data.
>
> Would a midrange Celeron processor with 256 MB RAM be sufficient? How
> would backups work for a database this large?
>
> Is PostgreSQL even the best database engine for this app? Perhaps MySQL?
> Or maybe a Microsoft solution?

well, I have a server with a database over 7GB, and :

backup=> select count(*) from file;
  count
----------
 19430605
(1 row)

backup=> explain analyze select * from file where id_file = 29000000;
NOTICE:  QUERY PLAN:

Index Scan using file_pkey on file  (cost=0.00..3.18 rows=1 width=117)
(actual time=0.60..0.61 rows=1 loops=1)
Total runtime: 0.77 msec

and it's an old poor :
hda: IBM-DTLA-307030, ATA DISK drive

ps: the select count(*) took a long time, but I believe that was because
there was a batch running and feeding the db :)
--
Mathieu Arnold

Hello,


> well, I have a server with a database over 7GB, and :
>
> backup=> select count(*) from file;
>   count
> ----------
>  19430605
> (1 row)
>
> backup=> explain analyze select * from file where id_file = 29000000;
> NOTICE:  QUERY PLAN:
>
> Index Scan using file_pkey on file  (cost=0.00..3.18 rows=1 width=117)
> (actual time=0.60..0.61 rows=1 loops=1)
> Total runtime: 0.77 msec
>
> and it's an old poor :
> hda: IBM-DTLA-307030, ATA DISK drive
>
> ps: the select count(*) took a long time, but I believe that was because
> there was a batch running and feeding the db :)

Thanks for the information.

Though, as a newbie, I am not quite sure what all the above performance
numbers actually mean.  :)

What sort of CPU/Memory configuration are you running this server on?

Thanks again,
David

--On lundi 22 avril 2002 03:41 -0400 pdg@stratos.net wrote:

> Hello,
>
>
>> well, I have a server with a database over 7GB, and :
>>
>> backup=> select count(*) from file;
>>   count
>> ----------
>>  19430605
>> (1 row)
>>
>> backup=> explain analyze select * from file where id_file = 29000000;
>> NOTICE:  QUERY PLAN:
>>
>> Index Scan using file_pkey on file  (cost=0.00..3.18 rows=1 width=117)
>> (actual time=0.60..0.61 rows=1 loops=1)
>> Total runtime: 0.77 msec
>>
>> and it's an old poor :
>> hda: IBM-DTLA-307030, ATA DISK drive
>>
>> ps: the select count(*) took a long time, but I believe that was because
>> there was a batch running and feeding the db :)
>
> Thanks for the information.
>
> Though, as a newbie, I am not quite sure what all the above performance
> numbers actually mean.  :)
>
> What sort of CPU/Memory configuration are you running this server on?

well, it means that I got an entry from my database (which have 19,430,605
entries) in 0.77 msec using the primary key.
the server is a pIII 600 with 256M of ram, but there is a lot of other
things running on.
The main things about database is the hard drive speed, with the turtle
I've got in this server, it is *slow*, just get a scsi3 hard drive and
it'll run like smoothly, the second thing, is the memory/cpu speed, but it
really only gets useful if you have very complex queries.

--
Mathieu Arnold