MySQL Forums
Forum List  »  Newbie

Re: logical OR on two fields in a table in a database
Posted by: Jay Pipes
Date: June 30, 2005 12:28PM

Erin,

OK, I finally had a chance to sit down and write a response to your post... but before I get started, I want to put a disclaimer out there. I'm not a MySQL AB developer, so you should take all this with a grain of salt, so to speak. I'll get into the details because I can see that you're truly interested in this stuff and feel you can keep up. If a MySQL AB developer wants to chime in and correct any mistakes in my post, please do so; we're all continually learning...

It's important to note that the table cache, and how it works, may be changed during future releases (according to some of the comments in the source code, particularly in the comments for the query cache code). Also, a quick note: the table_cache ONLY refers to MyISAM tables, not InnoDB.

The table cache global variable is defined in /sql/sql_base.cc as:

HASH open_cache;

The table cache stores, in a HASH construct, the st_table struct for table that has been opened by *any* THD object (internally, this is the code construct which represents a single user connection thread) requesting data from a table, temp table, or derived table. This st_table struct (typedef'd as TABLE in the source code) contains, essentially, a cache of the information contained in the table's .frm description file. This information includes data that you would expect to find in the .frm file, including a list of the Fields contained in the table, the format of the rows, etc.

When a connection (THD) issues a request, a linked list of table identifiers is built, based on the tables in the joins. This list is then used to compare against the st_table structs contained in the open_cache.records member variable. If the table is in the cache, and the st_table struct is not marked for refresh (meaning: the table definition has not changed or a database flush hasn't occurred), the .frm file is *not* loaded and read. This, obviously, is a *good* thing, as every time a .frm file is loaded, a file descriptor is created and the file is read into memory.

if the table is not found in open_cache.records, the .frm file is loaded and an st_table struct is inserted into the table cache for future THD objects to use.

So, the table cache can contain a lot of these opened st_table structs, depending on how many tables you have in your application. If the table cache runs out of room, then tables are removed from the open_cache using the hash_delete function, and the new, needed tables, are brought in. When this happens, you will see that the Open_tables and Opened_tables status variables start to get out of alignment. This is the first indicator that you may need to bump up the table cache size, because what's happening is the cache doesn't have enough room to fit all the st_table structs in memory that is consistently needed by the applications.

So how does this particular situation affect file descriptors? Well, if the table cache is running out of room, and a lot of "swapping" is occurring (meaning: tables closing, and .frm files are being read and pulled in, then being closed as other tables are needed), file descriptor usage can increase because of the number of times the .frm files are being loaded and closed by the table cache.

*However*, if you don't see the Opened_tables and Open_tables status variables diverging much, and you are having problems with the number of file descriptors, the size of the table cache is not the main culprit, because the table cache only stores the st_table struct for the .frm file information, and all the tables are being read into the table cache properly and efficiently. When a .frm file is read into the st_table struct and inserted into the table cache, the .frm file is then immediately closed and the file descriptor destroyed. So, the table cache is not the real culprit unless you're having a lot of swapping going on in the table cache.

BUT,

You have to remember that each THD object will open its own copy of the .MYD file for each table in the request. These are all file descriptors, managed by the MyISAM storage engine. So, if you have a consistent 50 user connections, each averaging 10 tables joined in the request, then you will consitently be using 1000 file descriptors for the .MYD files. This is *on top* of the file descriptors which are *shared by all THD objects* for the .MYI file of *each* opened MyISAM table in your database. Therefore, if you have 1000 tables that have been opened in your database, there is an additional 1000 file descriptors opened by the MyISAM storage engine being used to read and write to the index leaves.

So, what exactly happened when you set the table cache so high is tough to say. You may indeed have been in a situation where swapping was occurring in the table cache, and file descriptors were indeed being used frequently. However, if you set the table cache size to a high size, the file descriptors would have been used for only a short time (while the st_table was loaded and inserted into the open_cache HASH for all your table openings). After that, those file descriptors would have been destroyed.

It may have been a simple situation of the admin incorrectly setting the open files limit too low by accident. To check how many file descriptors are opened by MySQL (Open_files), along with the Open_tables and Opened_tables variables, you can use:

SHOW STATUS LIKE 'Open%';

When you say that "Queries aren't being cached!", remember that the table cache has nothing to do with whether a *query* is cached, but just the st_table struct for the .frm file associated with the tables used in the query. The Query cache is a different beast, and should definitely be turned on for the server.

To check if the query cache is turned on, do:

SHOW VARIABLES LIKE 'Q%';

If you see that the query_cache_type is "ON", but that the query_cache_size is "0", then the query_cache is not actually on, and no query results are being cached. To turn it on, you must first give the query_cache_size some room:

SET GLOBAL QUERY_CACHE_SIZE=3200000;

which will give you around 32M of room for query blocks to be inserted into the query cache. You can then monitor if the queries are being cached effectively using:

SHOW STATUS LIKE 'Qc%';

If at any point, you notice that the Qcache_low_mem_prunes variable is increasing at a significant rate, you should consider bumping up the query_cache_size variable.

If you want to learn more about the table cache, and how it is implemented, check out the following source code files and dig your way through there. Seeing how things are implemented might give you some insight into how the variables interrelate...

/sql/sql_base.cc (The implementation of the table cache)
/sql/sql_cache.cc (The implementation of the query cache)
/sql/sql_class.h (Defintiion of the THD object)
/sql/table.h (Definition of the st_table struct)

Again, MySQL AB developers, please chip in if I've missed some important points or made errors...

Cheers,

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.