Home / server / Questions / 244966
Accepted
Nuno
Asked: 2011-03-09 15:59:13 +0800 CST

MySQL Storage Limit - How to avoid problems

  • 772

I use MyISAM tables in MySQL, and I would like to know which methods are used to avoid problems with file-size limits and row limits.
How do big sites (such as Facebook) deal with those limits?

mysql

2 Answers

  1. Best Answer

    Was there a particular file/row limitation you were referring to? According to the MySQL 5.0 MyIASM documentation it supports up to 2^64 rows and file sizes up to 2^63 bytes (on systems that support files that large). The 'table is full' error page also refers to several size limitations imposed by a variety of run and compile time options and how to increase them.

    But even besides that, users at the bleeding edge, like Facebook, can generally afford more luxuries than most of us can. If, for example, MySQL didn't support a database size they needed they could hire someone to modify the MySQL source such that it did, or hire Sun/Oracle directly to do the seem. In fact, the very reason MySQL currently has such large row/file size may be due to large clients like Facebook.

    • 3

  2. Sites like FaceBook, Slashdot and LiveJournal have many servers and use a variety of partitioning, sharding and caching techniques that in practice result in tables being split across servers long before data size limits usually rear their heads.

    For less busy circumstances, the absolute data size is a function of the average row size multiplied by the row count. It is possible to adjust the estimates MyISAM uses to calculate that if you have rows with very few columns. In addition, commands like SHOW TABLE STATUS show a few metrics about the data usage of MyISAM tables. Those numbers is how experiences administrators estimate if the data parameters need changing.

    • 1