|
|
|
|
| |
For table types that represent data and indexes in separate files (such as ISAM and MyISAM), a table's size limit is reached when any of its individual files hits its own size limit. The table's indexing characteristics will affect which file this will be. For a table with no or few indexes, it is likely that the data file will reach its size limit first. For a heavily indexed table, the index file may hit the limit first.
The presence of an AUTO_INCREMENT column implicitly limits the number of rows a table may have. For example, if the column is TINYINT UNSIGNED, the maximum value it may hold is 255, so that also becomes the maximum number of rows the table may hold. Larger integer types allow more rows. More generally, placing any unique index on a table limits its row count to the maximum number of unique values in the index.
To determine the actual table size you can achieve, you must consider all applicable factors. The effective maximum table size likely will be determined by the smallest of those factors. Suppose you want to create a ISAM table. MySQL will allow the data and index files to reach 4GB. But if your operating system imposes a size limit on files of 2GB, that will be the effective limit for the table files. On the other hand, if your system has large file support, files can be bigger than 4GB and then the determining factor on table size will be MySQL's internal 4GB limit.
With respect to InnoDB tables, one point to keep in mind is that all such tables must fit within the InnoDB tablespace. If you have a single InnoDB table, it can be as large as the tablespace. But if, as is more likely, you have many InnoDB tables, they all share the same space and thus each is constrained in size not only by the size of the tablespace but also by how much of the tablespace is allocated to other tables. Any individual InnoDB table can grow as long as the tablespace is not full. Conversely, when the tablespace fills up, no InnoDB table can grow any larger until you add another component to the tablespace to make it bigger. (As of MySQL 3.23.50, you can make the last tablespace component auto-extending, so that it will grow as long as it does not exceed the file size limit of your system and disk space is available. See Chapter 11 for details on tablespace configuration.)
Implications of Data Directory Structure for System Performance
The structure of the MySQL data directory is easy to understand because it uses the hierarchical structure of the file system in such a natural way. At the same time, this structure has certain performance implications, particularly regarding operations that open the files that represent database tables.
One consequence of the data directory structure is that for table handlers that represent each table using multiple files, an open table can require multiple file descriptors, not just one. The server caches descriptors intelligently, but a busy server can easily use up lots of them while servicing many simultaneous client connections or executing complex queries that reference several tables. This can be a problem, because file descriptors are a scarce resource on many systems, particularly those that set the default per-process descriptor limit fairly low.
Another effect of representing each table by its own files is that table-opening time increases with the number of tables. Operations that open tables map onto the file-opening operations provided by the operating system and, as such, are bound by the efficiency of the system's directory-lookup routines. Normally this isn't much of an issue, but it is something to consider if you'll need large numbers of tables in a database. For example, a MyISAM table is represented by three files. If you want to have 10,000 MyISAM tables, your database directory will contain 30,000 files. With that many files, you may notice a slowdown due to the time taken by file-opening operations. (Linux ext2 and Solaris file systems are subject to this problem.) If this is cause for concern, you might want to think about using a type of file system that is highly efficient at dealing with large numbers of files. For example, ReiserFS exhibits good performance even with large numbers of small files. If that is not possible, it may be necessary to reconsider the structure of your tables in relation to the needs of your applications and reorganize your tables accordingly. Ask whether or not you really require so many tables; sometimes applications multiply tables needlessly. An application that creates a separate table per user results in many tables, all of which have identical structures. If you wanted to combine the tables into a single table, you might be able to do so by adding another column identifying the user to which each row applies. If this significantly reduces the number of tables, the application's performance improves
|
|
|
|
|
|
| Link Partners: Asia florist, Flowers to India, Hong kong flowers, Site submit, Cheap web hosting, China florist, Japan florist |
|
