Learning Mysql

max_connections variable, you need to remember that clients can include application web pages that interact with the database. This variable affects the number of people who can concurrently load the database-enabled web pages each request to load such a page counts as a separate connection. Of course, these connections are short, typically lasting only a few seconds while the page is generated and served to the web browser.
Some of the more important variables control how memory and files are managed.
MySQL databases are stored in files in the data directory, and the server needs to open and close these files. However, opening and closing files is a relatively slow operation,
so the fewer times we need to do this, the better. The MySQL server variable table_cache specifies the maximum number of tables that can be open at once. The larger this number, the fewer times we need to close open files and open closed ones.
You also need to consider how the max_connections value influences the value you choose for table_cache
. If you allow 100 concurrent connections, and your application has queries that perform join operations on three tables, then your table_cache should beat least 300. Note that operating systems impose their own limitations on the maximum number of files that can beheld open by any program, as well as for the whole system overall, so you may run into operating system limits if you set some MySQL
variable values too high.
We mentioned earlier that opening and closing files is a relatively costly process. It’s also far more costly to access files on disk than to access memory if the server can keep most of what it needs handy in memory, things will generally be much faster. In Keys and Indexes in Chapter 6, we explored how an index can help MySQL to quickly find data in a large table, just as an index page allows us to quickly find text in a book. When data is requested from a database table that has an index, the server first looks up the data location using the index file, then reads the data from the appropriate location in the table file. This means that the server has to access the disk twice if it can keep the index file in memory, it has to read the disk only once to fetch the data, which is much more efficient.
The MySQL server variable key_buffer_size controls the amount of memory set aside for MyISAM table indexes. The default value is 8 MB, but you can set it to any value up to 4 GB. Of course, you should actually have the required amount of memory on your system, and you should leave enough memory for the operating system and other processes. If you’re using a dedicated MySQL server, you might want to set this value as high as 20 to 40 percent of total system memory.
Some queries can’t use an existing index. For example, entries in a telephone directory are typically sorted by surname, then by given name. We can easily find all the people with a surname starting with the letter S but to find all the people with a first name starting with Swed need to look at every entry in the directory. For such operations,
a thread needs to read through all the data in a database table, which involves lots of disk reads. It’s faster to read a small number of large data chunks, so it’s good to allocate a large value for the read_buffer_size for such whole-of-table operations. Similarly, the

Download 4.24 Mb.

Share with your friends: