Innodb + Large data set

--001636ed6a632b47c80471f2f5ed
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi everyone,
Is there any forseeable issue with having an extremely large data set, say 1
TB in size for a single database and doing a SELECT * FROM tbl WHERE
where are super restrictive (in that they return
only a few rows since only a few match) and the proper indexes are in place?

This is on the InnoDB engine.

I was curious if there are any problems where doing a read on a large
dataset has huge problems down the road. Let's assume the server is a quad
core with 4 GB of RAM. Surely it shouldn't have a *huge* effect?

Sincerely,
Suhail Doshi

--001636ed6a632b47c80471f2f5ed--

Re: Innodb + Large data set

--000e0cd75fa22bf5650471f32acb
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Tue, Aug 25, 2009 at 3:16 PM, Suhail Doshi wrote:

> Hi everyone,
> Is there any forseeable issue with having an extremely large data set, say
> 1
> TB in size for a single database and doing a SELECT * FROM tbl WHERE
> where are super restrictive (in that they
> return
> only a few rows since only a few match) and the proper indexes are in
> place?
>
> This is on the InnoDB engine.
>
> I was curious if there are any problems where doing a read on a large
> dataset has huge problems down the road. Let's assume the server is a quad
> core with 4 GB of RAM. Surely it shouldn't have a *huge* effect?
>
> Sincerely,
> Suhail Doshi
>

Maybe this article will help give you some idea;

http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-cou ld-be-slow-with-large-tables/

--
Muhammad Subair

--000e0cd75fa22bf5650471f32acb--

Re: Innodb + Large data set

At 03:16 AM 8/25/2009, you wrote:
>Hi everyone,
>Is there any forseeable issue with having an extremely large data set, say 1
>TB in size for a single database and doing a SELECT * FROM tbl WHERE
> where are super restrictive (in that they return
>only a few rows since only a few match) and the proper indexes are in place?
>
>This is on the InnoDB engine.
>
>I was curious if there are any problems where doing a read on a large
>dataset has huge problems down the road. Let's assume the server is a quad
>core with 4 GB of RAM. Surely it shouldn't have a *huge* effect?
>
>Sincerely,
>Suhail Doshi

Suhail,
I think your biggest problem is getting the 1TB of data inserted into
an InnoDb table. It is going to take an extremely long time. Why does it
have to be InnoDb? Are you running transactions on the table the same time
you're reading from it? If you don't need transactions or RI, may I
suggest creating several MyISAM tables and then use a Merge table on that.
It has worked really well for me. I have each table holding 1 year's worth
of data. This allows me to rebuild any one table quite quickly and I can
have separate processes loading data for each year simultaneously without
locking problems. Once the data is loaded, I can then run a query on the
merge table to pull data from all of the tables.

Also there are other 3rd party engines that may be better depending on
what type of data you are storing.
Take a look at InfoBright at
http://www.mysql.com/news-and-events/generate-article.php?id =1180


Mike


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