|< Day Day Up >|
This section looks at how setting MySQL server variables can impact your MySQL-based application performance. First, to get a better idea of what activity is happening on your server, don't forget to consult the com status counters. These hold counts of the number of times various types of statements have been run. Figure 10.7 shows an example of a MySQL Administrator custom graph that was created to monitor transactions as well as INSERT/UPDATE/DELETE operations.
Figure 10.7. Monitoring statement counts via the MySQL Administrator.
The MySQL Administrator offers a tremendous amount of potential information, especially when various statistics are placed side by side into a graphical report.
MySQL connections claim system resources for all tables that they access. The table_cache parameter lets you dictate how many tables can be opened at one time for all applications that are using the database server. This is a very important parameter that you should carefully plan. Setting it too low means that MySQL needs to execute a series of expensive juggling maneuvers to satisfy all requests for table access. Specifically, it will close open tables so that requested tables may be opened. On the other hand, an excessive value means that you might run into operating system specific resource constraints.
Fortunately, MySQL offers a very worthwhile diagnostic the opened_tables status variable. Keep an eye on its value. A high number, especially if the server hasn't been running very long, means that MySQL is continually undertaking the expensive table juggling described above to satisfy these requests. Try raising the number in small increments, and then monitor opened_tables to see if your changes are having a positive impact. Recognize that you never want this number to exceed the open_files_limit variable, which apprises MySQL of the total number of file descriptors that your operating system will allow it to consume at any one time.
The MySQL query optimizer was reviewed in Chapter 6, "Understanding the MySQL Optimizer." One helpful capability of the optimizer is its capacity to take suggestions from application developers (in the form of special SQL directives) as well as parameters set by database administrators.
As you saw earlier, on occasion the optimizer might incorrectly choose table scans (that is, reading all rows in a table) rather than using an available index, especially if the cardinality of the index is low. However, by setting the max_seeks_for_key variable to a low number, the optimizer assumes that it is efficient to use the index after all. Before you change this setting, however, be certain to run EXPLAIN on your query and determine if any other factors could be at play.
For complex, multitable queries, it's possible that the optimizer could take longer to generate a query plan than to get the data itself. To help the optimizer from straying down the wrong path, leave the optimizer_prune_level variable to its default of one; this tells the optimizer to abandon work on unfinished query plans that start to appear inefficient rather than continuing to build the complete query plan.
The optimizer_search_depth variable provides guidance about whether the optimizer should pick a multitable query plan quickly, or evaluate all join permutations. In most cases, leave this value at zero unless you are having serious performance problems with a query. This setting means that the optimizer does its best to balance the benefits between a rapid versus comprehensive query plan search.
Chapter 6 explored the most recent enhancements to the filesort algorithm. This modified algorithm generally helps to elevate performance by retrieving results from the intermediate sorted collection, not the underlying table. However, this does introduce the potential for reduced performance if the row size is very large; precious buffer space can be crowded out by these big rows rather than just the key and row address information stored by the earlier incarnation of the algorithm.
To protect yourself from this possibility, try setting a limit by tuning the max_length_for_sort_data. If your result row size meets or exceeds this value, MySQL reverts to the earlier filesort algorithm.
Finally, the range_alloc_block_size parameter has the same purpose as other allocation block size variables already discussed, but in the context of memory management for enhancing faster range-based access to the database.
You reviewed the intricacies of MySQL concurrency management in Chapter 8, "Advanced SQL Tips," and Chapter 9, "Developing High Speed Applications." This section spends some time examining a collection of server variables that you can use to control several of these concurrency behaviors.
The discussion on improving INSERT performance discussed the optional DELAYED keyword that you can specify when loading rows into your tables. This directive instructs MySQL to allow other threads that are reading from a table to continue before it allows the INSERT to proceed. You have control over several facets of this behavior, as follows:
As you have already seen, MySQL issues table locks under a number of conditions. Although they usually have minimal impact on others, they do have the potential to impact concurrency if not freed quickly enough. One way to get these locks freed sooner is to set the SQL_BUFFER_RESULT variable to one.
Under this setting, MySQL creates a temporary table to hold the results of a query, thereby freeing the base table lock more quickly. Before you go down this path, however, remember that there is a cost associated with creating temporary tables, so be careful to measure the performance differences introduced by this change.
|< Day Day Up >|