List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:November 24 2009 5:45pm
Subject:Re: MySQL Performance with large data
View as plain text  
I second that RAID 10 with as many spindles as you can get
recommendation..  for any kind of load, even read-only load, you are
going to need it.

Also, that 8G of RAM is paltry for the kind of dataset you propose.
As already noted, the particulars will come down to the types and
frequency of the queries (not to mention expected performance targets)
but 4x64 CPUs churning that kind of data could really take advantage
of a lot more RAM.

 - michael dykman


On Tue, Nov 24, 2009 at 12:25 PM, Johan De Meersman <vegivamp@stripped> wrote:
> 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.ranjan@stripped> 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: vegivamp@stripped [mailto:vegivamp@stripped] On Behalf Of Johan De
>> Meersman
>> Sent: Tuesday, November 24, 2009 9:56 PM
>> To: Manish Ranjan (Stigasoft)
>> Cc: mysql@stripped
>> 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.ranjan@stripped> 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.
>>
>>
>>
>>
>



-- 
 - michael dykman
 - mdykman@stripped

"May you live every day of your life."
    Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.
Thread
MySQL Performance with large dataManish Ranjan \(Stigasoft\)24 Nov
  • Re: MySQL Performance with large dataJohan De Meersman24 Nov
    • RE: MySQL Performance with large dataManish Ranjan \(Stigasoft\)24 Nov
      • Re: MySQL Performance with large dataJohan De Meersman24 Nov
        • Re: MySQL Performance with large dataMichael Dykman24 Nov