MySQL Performance with large data

------=_NextPart_000_028E_01CA6D4B.521122F0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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.


------=_NextPart_000_028E_01CA6D4B.521122F0--

Re: MySQL Performance with large data

--0015177411467ad52304792066b3
Content-Type: text/plain; charset=ISO-8859-1

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@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.
>
>

--0015177411467ad52304792066b3--

RE: MySQL Performance with large data

------=_NextPart_000_029E_01CA6D52.BF06CC00
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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@gmail.com [mailto:vegivamp@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)
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.




------=_NextPart_000_029E_01CA6D52.BF06CC00--

Re: MySQL Performance with large data

--001636d33c7cf2135f0479213b74
Content-Type: text/plain; charset=ISO-8859-1

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@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: vegivamp@gmail.com [mailto:vegivamp@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)
> 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.
>
>
>
>

--001636d33c7cf2135f0479213b74--

Re: MySQL Performance with large data

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 wr=
ote:
> First off, for 4.000.000.000 records at 1867 byte per record, you're gonn=
a
> 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 stron=
gly
> depend on the actual data: text may easily compress to a factor ten, imag=
es
> (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 maxi=
mum
> amount of spindles, too - some form of SAN or locally-attached storage bo=
xes
> with (relatively) small-capacity high-rpm disks.
>
>
>
> On Tue, Nov 24, 2009 at 5:39 PM, Manish Ranjan (Stigasoft) <
> manish.ranjan@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 bas=
ed
>> on those search conditions. The fields will be indexed as per search
>> requirements.
>>
>>
>>
>> =A0_____
>>
>> From: vegivamp@gmail.com [mailto:vegivamp@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 o=
f
>> queries is :-) The machine you've described should be able to handle qui=
te
>> a
>> bit of load, though, if well-tuned.
>>
>> On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft)
>> wrote:
>>
>> Hi,
>>
>>
>>
>> I am using MySQL 5.0.45 in production environment. One of my tables (usi=
ng
>> MyISAM Engine) is expected to have around 4 billion records and each rec=
ord
>> 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 amou=
nt
>> of data? What all things I need to consider here?
>>
>> Thank you.
>>
>>
>>
>>
>



--=20
- michael dykman
- mdykman@gmail.com

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

Larry's First Law of Language Redesign: Everyone wants the colon.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg