SpirirHost Support Centre: MySQL Reference Manual p.362

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

362 MySQL Technical Reference for Version 4.0.3 Make sure that your operating system can handle the number of open le descriptors implied by the table_cache setting. If table_cache is set too high, MySQL may run out of le descriptors and refuse connections, fail to perform queries, and be very unreliable. You also have to take into account that the MyISAM table handler needs two le descriptors for each unique open table. You can in increase the number of le descriptors available for MySQL with the --open-files-limit=# startup option. See Section A.2.16 [Not enough le handles], page 636. The cache of open tables will be keept at a level of table_cache entries (default 64; this can be changed with the -O table_cache=# option to mysqld). Note that in MySQL may temporarly open even more tables to be able to execute queries. A not used table is closed and removed from the table cache under the following circum- stances: When the cache is full and a thread tries to open a table that is not in the cache. When the cache contains more than table_cache entries and a thread is no longer using a table. When someone executes mysqladmin refresh or mysqladmin flush-tables. When someone executes 'FLUSH TABLES' When the table cache lls up, the server uses the following procedure to locate a cache entry to use: Tables that are not currently in use are released, in least-recently-used order. If the cache is full and no tables can be released, but a new table needs to be opened, the cache is temporarily extended as necessary. If the cache is in a temporarily-extended state and a table goes from in-use to not-in-use state, the table is closed and released from the cache. A table is opened for each concurrent access. This means that if you have two threads accessing the same table or access the table twice in the same query (with AS) the table needs to be opened twice. The rst open of any table takes two le descriptors; each additional use of the table takes only one le descriptor. The extra descriptor for the rst open is used for the index le; this descriptor is shared among all threads. If you are opening a table with the HANDLER table_name OPEN statement, a dedicated table object is allocated for the thread. This table object is not shared by other threads an will not be closed until the thread calls HANDLER table_name CLOSE or the thread dies. See Section 6.4.2 [HANDLER], page 453. When this happens, the table is put back in the table cache (if it isn't full). You can check if your table cache is too small by checking the mysqld variable Opened_ tables. If this is quite big, even if you haven't done a lot of FLUSH TABLES, you should increase your table cache. See Section 4.5.6.3 [SHOW STATUS], page 253. 5.4.8 Drawbacks to Creating Large Numbers of Tables in the Same Database If you have many les in a directory, open, close, and create operations will be slow. If you execute SELECT statements on many dierent tables, there will be a little overhead when the

MySQL Reference Manual

Home
Hosting Plans \| Domain Registration \| About Us \| Contact Us \| Site Map Terms of Use \| Privacy Policy \| Guarantees Merchant Accounts

SpiritHost - web hosting for spiritual and education sites SpiritHit.com - Religious and Spiritual Portal Copyright © 2002 Dyntex Group, Inc. All Rights Reserved

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide