hi all
i just wanted to ask here if somebody has experience in pushing the mysql
limits i might have a job that needs to have a table (or a few tables)
holding about a 100 million records. that's a lot of records is there
any limitation of some kind that wouldn;t allow mysql to handle that kind
of amounts or it all depends on memory and cpu or how are the searches
- speed and otherwise - affected by such numbers?
thanks

Hi,

The limit for the table can be set when you create the table itself.
the MAX_RWS and AVG_RW_LENGTH variables (m X n matrix) will decide the
table size.

MAX_RWS limts the maximum number of rows in that table. The AVG_RW_LENGTH
variable decides the length of the row. The specified value can be used by
a single column itself or depends on the size of the columns.

Thanks
ViSolve DB Team.
Message
From: "kalin mintchev" <kalin (AT) el (DOT) net>
To: <mysql (AT) lists (DOT) mysql.com>
Sent: Monday, February 05, 2007 9:14 AM
Subject: mysql limits

hi all

i just wanted to ask here if somebody has experience in pushing the mysql
limits i might have a job that needs to have a table (or a few tables)
holding about a 100 million records. that's a lot of records is there
any limitation of some kind that wouldn;t allow mysql to handle that kind
of amounts or it all depends on memory and cpu or how are the searches
- speed and otherwise - affected by such numbers?

thanks
--

thanks my question was more like IF mysql can handle that amount of
records - about 100 million and if it's just a question of cpu power
and memory?

Hi,

The limit for the table can be set when you create the table itself.
the MAX_RWS and AVG_RW_LENGTH variables (m X n matrix) will decide the
table size.

MAX_RWS limts the maximum number of rows in that table. The
AVG_RW_LENGTH
variable decides the length of the row. The specified value can be used
by
a single column itself or depends on the size of the columns.

Thanks
ViSolve DB Team.
Message
From: "kalin mintchev" <kalin (AT) el (DOT) net>
To: <mysql (AT) lists (DOT) mysql.com>
Sent: Monday, February 05, 2007 9:14 AM
Subject: mysql limits
>
>
>hi all
>>
>i just wanted to ask here if somebody has experience in pushing the
>mysql
>limits i might have a job that needs to have a table (or a few
>tables)
>holding about a 100 million records. that's a lot of records is
>there
>any limitation of some kind that wouldn;t allow mysql to handle that
>kind
>of amounts or it all depends on memory and cpu or how are the
>searches
>- speed and otherwise - affected by such numbers?
>>
>thanks
>>
>>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>@visolve.com
>>
>
>

Hi,

It can handle. You can extend the file size also. File size limit depends
on the S. the performance depends on both the processor speed
and the memory. Table optimization,indexing will improve performance.

Thanks
ViSolve DB Team
Message
From: "kalin mintchev" <kalin (AT) el (DOT) net>
To: "ViSolve DB Team" <mysqlsupport (AT) visolve (DOT) com>
Cc: <mysql (AT) lists (DOT) mysql.com>
Sent: Monday, February 05, 2007 4:07 PM
Subject: Re: mysql limits

thanks my question was more like IF mysql can handle that amount of
records - about 100 million and if it's just a question of cpu power
and memory?
>
>
>Hi,
>>
>The limit for the table can be set when you create the table itself.
>the MAX_RWS and AVG_RW_LENGTH variables (m X n matrix) will decide the
>table size.
>>
>MAX_RWS limts the maximum number of rows in that table. The
>AVG_RW_LENGTH
>variable decides the length of the row. The specified value can be used
>by
>a single column itself or depends on the size of the columns.
>>
>Thanks
>ViSolve DB Team.
>Message
>From: "kalin mintchev" <kalin (AT) el (DOT) net>
>To: <mysql (AT) lists (DOT) mysql.com>
>Sent: Monday, February 05, 2007 9:14 AM
>Subject: mysql limits
>>
>>
hi all

i just wanted to ask here if somebody has experience in pushing the
mysql
limits i might have a job that needs to have a table (or a few
tables)
holding about a 100 million records. that's a lot of records is
there
any limitation of some kind that wouldn;t allow mysql to handle that
kind
of amounts or it all depends on memory and cpu or how are the
searches
- speed and otherwise - affected by such numbers?

thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
@visolve.com

>>
>>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: @el.net
>>
>>
>
>
>

At 09:44 PM 2/4/2007, kalin mintchev wrote:
>hi all
>
>i just wanted to ask here if somebody has experience in pushing the mysql
>limits i might have a job that needs to have a table (or a few tables)
>holding about a 100 million records. that's a lot of records is there
>any limitation of some kind that wouldn;t allow mysql to handle that kind
>of amounts or it all depends on memory and cpu or how are the searches
>- speed and otherwise - affected by such numbers?
>
>thanks

Put as much memory in the machine as possible. Building indexes for a table
of that size will consume a lot of memory and if you don't have enough
memory, building the index will be done on the hard disk where it is 100x
slower. I've had 100M row tables without too much problem. However when I
tried 500M rows the indexes could not be built (took days) because I too
little RAM.

Mike

Put as much memory in the machine as possible. Building indexes for a
table
of that size will consume a lot of memory and if you don't have enough
memory, building the index will be done on the hard disk where it is 100x
slower. I've had 100M row tables without too much problem. However when I
tried 500M rows the indexes could not be built (took days) because I too
little RAM.

thanks would you please be more specific about "to little RAM"? what
amount of memory is enough for the 500M? what about search speeds? cpu?
also what kind of tables did you use?

thanks

Mike

At 12:18 PM 2/5/2007, kalin mintchev wrote:

Put as much memory in the machine as possible. Building indexes for a
table
of that size will consume a lot of memory and if you don't have enough
memory, building the index will be done on the hard disk where it is 100x
slower. I've had 100M row tables without too much problem. However when I
tried 500M rows the indexes could not be built (took days) because I too
little RAM.
>
>thanks would you please be more specific about "to little RAM"?

I had only 1gb on a Windows XP box. I was able to put it up to 3gb and it
speeded things up quite a bit.


>what
>amount of memory is enough for the 500M?

You need enough memory to hold the entire index into memory.

>what about search speeds? cpu?
>also what kind of tables did you use?

Search speeds and CPU with MyISAM is quite good. I tried InnoDb and insert
speeds was far too slow because of its row locking versus MyISAM's table
locking. Some people have been able to fine tune InnoDb but it requires
even more RAM because InnoDb works best when the entire table fits into memory.

Mike

>
Search speeds and CPU with MyISAM is quite good. I tried InnoDb and insert
speeds was far too slow because of its row locking versus MyISAM's table
locking. Some people have been able to fine tune InnoDb but it requires
even more RAM because InnoDb works best when the entire table fits into
memory.

thanks

Mike
--