Thread: Hardware needed for 15,000,000 record DB?
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? Thanks so much, David
--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