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.
RE: MySQL Performance with large data
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.
Re: MySQL Performance with large data
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.
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 vegiv...@tuxera.be 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.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. -- - michael dykman - mdyk...@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=arch...@jab.org
