Hi,
Thanks, but I already checked the manual about these aspects, and I'm doing
heavy tests for a while about the performance of MySQL (with InnoDB tables)
with big databases. By the way, the indexing seems to be working on bigint
fields, so probably the size of the int field is not a limit.
However, I'm more interested in practical experience with huge databases. How
effective is MySQL (with InnoDB) working with tables containing millions or
rather billions of rows? How about the response time of queries, which return a
few dozens of rows from these big tables when using two, three or more table
joins? What is a suggester hardware when we're expecting the database to grow
extremely large? How stable is MySQL and the InnoDB engine with terrabytes
behind? Etc, etc, etc...
(Of course we assume that the database structure is very well designed and
using optimal indexing.)
So, if anybody has practical eperience about these issues, please give me some
advice.
Thank you,
Daniel
On Sat, 09 Apr 2005 12:50:42 -0400, Michael Stassen wrote:
>
> On Apr 9, 2005, at 8:05 AM, olli wrote:
>
>> hi,
>> if your table is indexed, i think it can theoretically hold
>> 4.294.967.295 rows, because that's the maximum for an unsigned
>> integer value in mysql and indexing doesn't work with bigint
>> types as far as i know. but, i'm not really sure about that.
>>
>
> I would suggest checking the manual, where answers are readily
> available, rather than speculating.
>
> MySQL limits tables to 8 million terabytes, so table size is
> currently limited by your OS/filesystem, in practice. Since most
> tables aren't that big, the default structure for a MyISAM table
> limits you to 4Gb in size. You can change this at table creation
> (preferred), or later with an ALTER statement, using the
> AVG_ROW_LENGTH and MAX_ROWS options. You can check the maximum
> size of an already defined table with SHOW TABLE STATUS.
>
> References:
> <http://dev.mysql.com/doc/mysql/en/table-size.html>
> <http://dev.mysql.com/doc/mysql/en/full-table.html>
> <http://dev.mysql.com/doc/mysql/en/create-table.html>
> <http://dev.mysql.com/doc/mysql/en/show-table-status.html>
>
>> Am 09.04.2005 um 11:42 schrieb Daniel Kiss:
>>
>>> Hi All,
>>>
>>> I would like to know how big is the biggest database that can
>>> be handled effectively by MySQL/InnoDB.
>>>
>>> Like physical size, number of tables, number of rows per table,
>>> average row length, number of indexes per table, etc.
>>>
>>> Practically, what if I have a master/detail table-pair, where
>>> the master contains about 30 million rows, the detail in
>>> average contains 50 row for each master row, so about 1.5
>>> billion rows in total?
>>>
>>> I know that the performace heavily relies on the hardware, but
>>> let's assume that the MySQL server runs on a high-end machine
>>> with about 2GB of RAM.
>>>
>
> Performance is largely determined by db design, indexing, and how
> your queries are written. For example, a full table scan of your
> detail table would look at 50 times more rows than an index lookup
> of just the rows which match the one master row in question.
> Quadrupling your disk speed wouldn't help much relative to adding
> the index. In other words, all else being equal, faster hardware
> gives faster performance, but optimizing your table structure,
> indexes, and queries has a bigger impact on performance. Tuning
> your server parameters to match your usage and resources can also
> make a big difference.
>
> If you haven't yet chosen your "high-end machine with about 2GB of
> RAM", you should know that "Which hardware is best?" is a FAQ on
> this list, so you should be able to find numerous suggestions by
> searching the list archives <http://lists.mysql.com/mysql>.
>
>>> Does anyone have experience with extremely large databases?
>>>
> You may find Jeremy Zawodny's book, "High Performance MySQL",
> useful. <http://www.oreilly.com/catalog/hpmysql/>
>
>>> Thank you,
>>> Daniel
>>>
> Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
