First off, for 4.000.000.000 records at 1867 byte per record, you're gonna need more storage than that (over 1.6 terabyte if I did my maths right) , unless you're using compressed tables - then your requirements will strongly depend on the actual data: text may easily compress to a factor ten, images (blobs?) almost not. Compressed tables will also speed up your I/O, in exchange for some more CPU load.
On such a dataset, table scans are going to be geologically slow, so yes, good indexes will be your saviour :-) For speed, I'd also recommend that you get a RAID-10 setup. Go for a maximum amount of spindles, too - some form of SAN or locally-attached storage boxes with (relatively) small-capacity high-rpm disks. On Tue, Nov 24, 2009 at 5:39 PM, Manish Ranjan (Stigasoft) < manish.ran...@stigasoft.com> wrote: > Thank you Johan. > > > > The table will be read only. There will be two steps - first to get the > count using search conditions and then to get data from some columns based > on those search conditions. The fields will be indexed as per search > requirements. > > > > _____ > > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De > Meersman > Sent: Tuesday, November 24, 2009 9:56 PM > To: Manish Ranjan (Stigasoft) > Cc: mysql@lists.mysql.com > Subject: Re: MySQL Performance with large data > > > > The amount and type of data is less the issue than the amount and type of > queries is :-) The machine you've described should be able to handle quite > a > bit of load, though, if well-tuned. > > On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft) > <manish.ran...@stigasoft.com> wrote: > > Hi, > > > > I am using MySQL 5.0.45 in production environment. One of my tables (using > MyISAM Engine) is expected to have around 4 billion records and each record > will have 1867 bytes of data. All fields in this table are of character > data > type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space > available and quad core processor. > > My question is whether MySQL will be able to handle queries on this amount > of data? What all things I need to consider here? > > Thank you. > > > >
