Application Control

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.

Resources

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.

Optimizer Control

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.

Concurrency

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.

Delayed Insert

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:

• Number of threads to service INSERT DELAYED statements The max_delayed_threads parameter instructs MySQL to create no more than this many threads to handle delayed inserts. Setting it to zero completely disables delayed inserts, whereas a small number means that some delayed insert requests will actually be handled as standard INSERT statements.

• Queue size You can configure the queue size for each table by tuning the delayed_queue_size variable. A larger value means that MySQL allows more rows to build up in this queue before delaying any new INSERT DELAYED requests.

• Checking for waiting queries MySQL uses the delayed_insert_limit setting to determine how frequently to check for waiting queries while an INSERT DELAYED operation is under way.

  For example, if this value is set to 100, MySQL processes 100 delayed insert rows and then checks for any queries that are waiting for access. If it finds any, it allows the queries to complete and only then continues inserting any other delayed rows.

  Use this variable to help set the balance between the needs of your delayed inserts and those of your queries.

• Thread termination You can control how many seconds the dedicated insert delayed thread remains alive and waits for new delayed insert rows by setting the delayed_insert_timeout variable. Keep several factors in mind if you decide to change the value from its default of 300, including the balance of insert and query operations, client connectivity speed, and delayed insert volume.

Unlocking Tables

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.