Optimizing the mysqld Variables
|
|
To look at what the existing values for the mysqld variables are, you can use mysqladmin from the command line:
% mysqladmin -uroot -pg00r002b variables;
or when connected to MySQL:
mysql> SHOW VARIABLES +----------------------+---------------------------------+ | Variable_name | Value + +----------------------+---------------------------------+ | back_log | 50 | | basedir | /usr/local/mysql-max-4.0.1- | | | alpha-pc-linux-gnu-i686 | | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 32768 | | bdb_home | /usr/local/mysql/data/ | | bdb_max_lock | 10000 | | bdb_logdir | | | bdb_shared_data | OFF | | bdb_tmpdir | /tmp/ | | bdb_version | Sleepycat Software: Berkeley DB | | | 3.2.9a: (December 23, 2001) | | binlog_cache_size | 32768 | | character_set | latin1 | | character_sets | latin1 big5 czech euc_kr gb2312 | | | gbk latin1_de sjis tis620 ujis | | | dec8 dos german1 hp8 koi8_ru | | | latin2 swe7 usa7 cp1251 danish | | | hebrew win1251 estonia | | | hungarian koi8_ukr win1251ukr | | | greek win1250 croat cp1257 | | | latin5 | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /usr/local/mysql/data/ | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_ | | | timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | ft_min_word_len | 4 | | ft_max_word_len | 254 | | ft_max_word_len_for_ | | | sort | 20 | | ft_boolean_syntax | + -><()~*:""&| | | have_bdb | YES | | have_innodb | YES | | have_isam | YES | | have_raid | NO | | have_symlink | YES | | have_openssl | NO | | init_file | | | innodb_additional_ | | | mem_pool_size | 1048576 | | innodb_buffer_pool | |_ | size | 8388608 | | innodb_data_file_path| ibdata1:64M | | innodb_data_home_dir | | | innodb_file_io_ | | | threads | 9 | | innodb_force_recovery| 0 | | innodb_thread_ | | | concurrency | 8 | | innodb_flush_log_at_ | | | trx_commit | OFF | | innodb_fast_shutdown | OFF | | innodb_flush_method | | | innodb_lock_wait_ | | | timeout | 1073741824 | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_ | | | size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_ | | | group | 2 | | innodb_log_group_ | | | home_dir | ./ | | innodb_mirrored_log_ | | | groups | 1 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 16773120 | | language | /usr/local/mysql-max-4.0.1-alpha| | | -pc-linux-gnu-i686/share/mysql/| | | english/ | | large_files_support | ON | | locked_in_memory | OFF | | log | ON | | log_update | OFF | | log_bin | ON | | log_slave_updates | OFF | | log_long_queries | ON | | long_query_time | 20 | | low_priority_updates | OFF | | lower_case_table_ | | | names | 0 | | max_allowed_packet | 1047552 | | max_binlog_cache_size| 4294963200 | | max_binlog_size | 1073741824 | | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | max_user_connections | 0 | | max_tmp_tables | 32 | | max_write_lock_count | 4294967295 | | myisam_bulk_insert_ | | | tree_size | 8388608 | | myisam_max_extra_ | | | sort_file_size | 256 | | myisam_max_sort_ | | | file_size | 2047 | | myisam_recover_ | | | options | OFF | | myisam_sort_buffer_ | | | size | 8388608 | | net_buffer_length | 7168 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | open_files_limit | 0 | | pid_file | /usr/local/mysql/data/host.pid | | port | 3306 | | protocol_version | 10 | | record_buffer | 131072 | | record_rnd_buffer | 131072 | | rpl_recovery_rank | 0 | | query_buffer_size | 0 | | query_cache_limit | 1048576 | | query_cache_size | 0 | | query_cache_startup_ | | | type | 1 | | safe_show_database | OFF | | server_id | 1 | | slave_net_timeout | 3600 | | skip_external_locking| ON | | skip_networking | OFF | | skip_show_database | OFF | | slow_launch_time | 2 | | socket | /tmp/mysql.sock | | sort_buffer | 524280 | | sql_mode | 0 | | table_cache | 64 | | table_type | MYISAM | | thread_cache_size | 0 | | thread_stack | 65536 | | transaction_isolation| READ-COMMITTED | | timezone | SAST | | tmp_table_size | 33554432 | | tmpdir | /tmp/ | | version | 4.0.1-alpha-max-log | | wait_timeout | 28800 |
Also important when tuning is the information supplied by the server itself. You can view this from the command line with the following:
% mysqladmin extended-status
or when connected to the server:
mysql> SHOW STATUS +--------------------------+----------+ | Aborted_clients | 142 | | Aborted_connects | 5 | | Bytes_received | 9005619 | | Bytes_sent | 15444786 | | Connections | 794 | | Created_tmp_disk_tables | 1 | | Created_tmp_tables | 716 | | Created_tmp_files | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 27 | | Handler_read_first | 1534 | | Handler_read_key | 608840 | | Handler_read_next | 652228 | | Handler_read_prev | 164 | | Handler_read_rnd | 14143 | | Handler_read_rnd_next | 1133372 | | Handler_update | 90 | | Handler_write | 131624 | | Key_blocks_used | 6682 | | Key_read_requests | 2745899 | | Key_reads | 6026 | | Key_write_requests | 63925 | | Key_writes | 63790 | | Max_used_connections | 20 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 64 | | Open_files | 128 | | Open_streams | 0 | | Opened_tables | 517 | | Questions | 118245 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 2300 | | Select_range_check | 0 | | Select_scan | 642 | | Slave_running | OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 8 | | Sort_merge_passes | 0 | | Sort_range | 3582 | | Sort_rows | 16287 | | Sort_scan | 806 | | Table_locks_immediate | 82957 | | Table_locks_waited | 2 | | Threads_cached | 0 | | Threads_created | 793 | | Threads_connected | 1 | | Threads_running | 1 | | Uptime | 1662790 | +--------------------------+----------+
The list of variables and status information grows longer with each new release. Your version will probably have more than this, and you should read the latest documentation to see exactly what these extras do. A full explanation of those currently in use is given later in this chapter, in Table 13.2.
Most MySQL distributions come with four sample configuration files:
my-huge.cnf For systems with more than 1GB memory that are mostly dedicated to MySQL.
my-large.cnf For systems with at least 512MB memory that are mostly dedicated to MySQL.
my-medium.cnf For systems with at least 32MB memory dedicated entirely to MySQL or with at least 128MB on a machine that serves multiple purposes (such as a dual web/database server).
my-small.cnf For systems with less than 64MB memory where MySQL cannot take up too much of the resources.
These files can usually be found in /usr/share/doc/packages/MySQL/ (an RPM installation), /usr/local/mysql-max-4.x.x-platform-operating-system-extra/support-files/ (Unix binary installation), or C:\mysql\ (Windows).
| Warning | On Windows, the .cnf extension can conflict with FrontPage and NetMeeting. |
As a starting point, I suggest replacing your my.cnf file (or my.ini) with one of these configurations, choosing the configuration closest to the your needs.
Choosing the right configuration for your system will get you a large step of the way toward optimality, but to achieve optimal usage requires fine-tuning the configuration for your system and usage specifics. You'll see some of the variables in the following sections.
Optimizing table_cache
The table_cache variable is one of the most useful variables to adjust. Every time MySQL accesses a table, if there is space in the cache, that table is placed there. It's faster to access the table in memory than the table on disk. You can see whether you need to increase the value of your table_cache by examining the value of open_tables at peak times (one of the extended status values you saw with SHOW STATUS or mysqladmin variables). If you find that open_tables is at the same value as your table_cache, and the value of opened_tables (another extended status value) is increasing, you should increase the table_cache if you have enough memory.
| Note | The number of open tables can be higher than the number of tables in your databases. MySQL is multithreaded, and there may be many queries running at a time, each of which may open a table. |
Look at the following three scenarios, all during peak hours.
Scenario 1 This scenario is taken from a live server that's not particularly busy:
table_cache - 512 open_tables - 103 opened_tables - 1723 uptime - 4021421 (measured in seconds)It looks like the table_cache is set too high in this case. The server has been up for a long time (if the server had just come up you wouldn't know if the table_cache would be reached soon or if the opened_tables would soon begin to increase). The number of opened tables is reasonably low, and the number of open tables is well below what it could be, considering that this is a peak time.
Scenario 2 This scenario is taken from a development server:
table_cache - 64 open_tables - 64 opened_tables - 431 uptime - 1662790 (measured in seconds)Here, although the open_tables is at its maximum, the number of open_tables is reasonably low, considering that the server has been up a while. There is probably not much benefit to be gained from upping the table_cache.
Scenario 3 This scenario is taken from an underperforming live server:
table_cache - 64 open_tables - 64 opened_tables - 22423 uptime - 19538The table_cache in this instance is set too low. The open_tables is at its maximum, and the number of opened_tables is high, even though the uptime is less than six hours. If your system has spare memory available, you should increase the table_cache.
| Warning | Don't blindly set the table_cache to a high value. If you do not need a high value, keep the value of the table_cache to something reasonable. If it is set too high, you may run out of file descriptors, and consequently see unreliable performance or connections being refused. |
Optimizing key_buffer_size
The key_buffer_size affects the size of the index buffers, which in turn affects the speed of index handling, in particular index reads. The higher the value, the more of the indexes MySQL can hold in memory, which is much faster to access than from disk. A suggested rule of thumb is to set it from between a quarter to half of the available memory on your server (if your server is dedicated to MySQL). You can get a good idea how to adjust the key_buffer_size by comparing the key_read_requests and key_reads status values. The ratio of key_reads to key_read_requests should be as low as possible, with 1:100 being about the highest acceptable limit (1:1000 is better, 1:10 is terrible). The key_reads value indicates how many times the key needs to be read from disk, which is what you want to avoid by setting the key buffer to as high a value as possible.
The following scenarios examine two possibilities.
Scenario 1 A healthy situation:
key_buffer_size - 402649088 (384M) key_read_requests - 597579931 key_reads - 56188Scenario 2 Alarm bells ringing:
key_buffer_size - 16777216 (16M) key_read_requests - 597579931 key_reads - 53832731
Scenario 1 reflects a healthy situation. The ratio is over 1:10000, but alarm bells should be ringing in scenario 2, where the ratio is about a worrying 1:11. As a solution, you should increase the key_buffer_size to as much as the memory allows. A hardware upgrade is necessary if you don't have enough memory to cater for this.
The ratio of key_writes to key_write_requests can also be a useful one to look at. It's usually close to 1 if you do mainly inserts and updates of one record at a time, but if you often insert or update large volumes of data at a time, you would want this lower. Using INSERT DELAYED statements will also reduce this ratio.
Dealing with Too Many Connections
A common, and sometimes easily fixable, error that can occur when systems get too busy is the Too many connections error. When the number of threads_connected goes beyond the number of max_connections often, it's time to make a change. If the queries are being handled smoothly, the solution can be as simple as increasing the value of max_connections.
Most applications should make use of persistent connections rather than ordinary connections (for example, in PHP, using the pconnect() function rather than the connect() function). Persistent connections remain open even after the query has finished running, which, on busy servers, means that the next query does not have to take any resources to connect again. Maintaining a large number of persistent, but unused, connections is less resource intensive than rapidly connecting, disconnecting, and reconnecting in quick succession.
| Note | Persistent connections cannot be used in CGI mode, and are affected by the KeepAlive settings in the Apache web server. |
This scenario examines a web server under heavy load that uses persistent connections:
max_connections - 250 max_used_connections - 210 threads_connected - 202 threads_running - 1
It may look like MySQL is wasting resources in this scenario, but in this case it's simply that the 202 threads_connected are persistent, based upon the number of instances of the web server, and are hardly taking up any resources. Only one thread is actually running, so the database is probably not taking much strain. If the threads_connected gets ever closer to the max_connections without any problems, you may even want to increase the max_connections to avoid exceeding the connections limit. You can see how close the connections have ever gotten to maximum by looking at the max_used_connections value. If this is close, or equal to the max_connections, it's certainly time to make allowances for an increase.
Personally, I've always found persistent connections to be better, though there are some reports that, because the MySQL connection overhead is much lighter than other databases (such as Oracle, where you have to use persistent connections in most cases), it makes little difference or even penalizes performance. The best suggestion is to test performance on your own systems.
| Warning | When testing, make sure to test properly under load. There are some documents on the Web with all kinds of erroneous comparisons between persistent and nonpersistent connections. |
In a system such as the previous scenario, a climbing threads_running value is often an indicator that the database server is not handling the load. Examining the process list can help identify the queries causing the blockage. What follows is a portion of the output from a database server just before it crashed. The number of threads_connected continued increasing until the server could handle it no longer and fell over. The processlist output helped to identify the problematic queries:
% mysqladmin processlist; Id User Host Db Command Time State Info 6464 mysql websrv2… news Sleep 590 6482 mysql websrv2… news Sleep 158 6486 mysql websrv2… news Sleep 842 7549 mysql websrv2… news Sleep 185 8126 mysql websrv2...news Sleep 349 9938 mysql websrv2...news Sleep 320 1696 mysql websrv2...news Sleep 100 4143 mysql websrv2...news Sleep 98 5071 mysql websrv2...news Sleep 843 5135 mysql websrv2...news Sleep 155 92707 mysql zubat... news Sleep 530 93014 mysql zubat... news Query 13 Locked select s_id from arts where a_id = 'E232625' 93060 mysql zubat... news Sleep 190 93096 mysql zubat... news Query 171 Copying to tmp table select distinct arts.a_id, arts.headline1, nartsect.se_id, arts.mdate, arts.set_ 93153 mysql zubat... news Sleep 207 93161 mysql zubat... news Query 30 Locked SELECT DISTINCT arts.a_id FROM arts,keywordmap WHERE arts.s_id in (1) AND arts. 93165 mysql zubat... news Query 36 Locked select arts.name, arts.headline1, arts.se_id, n_blurb.blurb, slook.name as sectna 93204 mysql zubat... news Query 31 Locked select arts.name, arts.headline1, arts.se_id, n_blurb.blurb, slook.name as sectna 93205 mysql zubat... news Query 156 Copying to tmp table select distinct arts.a_id, arts.headline1, nartsect.se_id, arts.mdate, arts.set_ 93210 mysql zubat... news Query 50 Locked select arts.a_id, arts.headline1, nartfpg.se_id, nartfpg.se_id, arts.mdate, nartfpg.p 93217 mysql zubat... news Query 38 Locked select arts.name, arts.headline1, arts.se_id, n_blurb.blurb, slook.name as sectna 93222 mysql zubat... news Query 8 Locked select arts.name, arts.headline1, arts.se_id, n_blurb.blurb, slook2.name as sectn 93226 mysql zubat... news Query 39 Locked select arts.name, arts.headline1, arts.se_id, n_blurb.blurb, slook.name as sectna 93237 mysql zubat... news Query 33 Locked select arts.a_id, arts.headline1, nartfpg.se_id, nartfpg.se_id, arts.mdate, nartfpg.p 93244 mysql zubat... news Query 99 Copying to tmp table select distinct arts.a_id, arts.headline1, nartsect.se_id, arts.mdate, arts.set_ 93247 mysql zubat... news Query 64 Locked select s_id from arts where a_id='32C436' 93252 mysql zubat... news Query 120 Copying to tmp table select distinct arts.a_id, arts.headline1, nartsect.se_id, arts.mdate, arts.set_ 93254 mysql zubat... news Sleep 47 93256 mysql zubat... news Sleep 171 93257 mysql zubat... news Query 176 Copying to tmp table select distinct arts.a_id, arts.headline1, nartsect.se_id, arts.mdate, arts.set_ 93261 mysql zubat... news Sleep 349 93262 mysql zubat... news Sleep 1 93263 mysql zubat... news Query 153 Copying to tmp table select distinct arts.a_id, arts.headline1, nartsect.se_id, arts.mdate, arts.set_ 93267 mysql zubat... news Query 27 Locked select arts.name, arts.headline1, arts.se_id, n_blurb.blurb, slook.name as sectna 93276 mysql zubat... news Query 29 Locked select arts.name, arts.headline1, arts.se_id, n_blurb.blurb, slook.name as sectna 93278 mysql zubat... news Query 183 Copying to tmp table select distinct arts.a_id, arts.headline1, nartsect.se_id, arts.mdate, arts.set 93280 mysql zubat... news Sleep 36 93285 mysql zubat... news Sleep 10 93284 mysql zubat... news Query 49 Locked select arts.name, arts.headline1, arts.se_id, n_blurb.blurb, slook.name as sectna
Of the two web servers indicated in the list, websrv2 is behaving normally (all its threads are completed, and the connections are sleeping), but zubat has problem queries piling up.
There are many queries, and this is only a small portion of the whole list, but the query you should examine in this case is the one beginning like this:
select distinct arts.a_id, arts.headline1, nartsect.se_id, arts.mdate, arts.set ...
Notice how the status for all of these queries is Copying to tmp table, and the others were Locked. In this case, the problem was that a developer had made a change to the query so that it no longer used an index. Chapter 4, "Indexes and Query Optimization," discusses this topic in more detail.
Routinely examining the processlist output can help identify queries that are slow even before they cause something as drastic as the server to fail.
The slow_queries value is another good one you should examine. If it creeping up all the time, it probably indicates a problem. A well-tuned system should have as few slow queries as possible. Some complex joins may be unavoidably slow, but it's more likely that slow queries are just badly optimized.
Optimizing the delayed_queue_size and back_log Variables
As discussed in Chapter 4, INSERT DELAYED frees the client but does not process the query immediately if there is anything else in the queue. Instead, MySQL waits for a gap so the inserts can be processed. The delayed_queue_size plays a role here. If the variable is set to its default value, 1,000, this means that after 1,000 delayed statements are in the queue, the client will no longer be freed and will have to wait. Having so many queued queries is not usually healthy, but if your system is one where a large number of inserts are made at a similar time, and you find clients having to wait even though you're using INSERT DELAYED, you should increase the delayed_queue_size.
Another variable that helps manage short bursts of activity is the back_log variable. If a system receives a large number of connection requests in a short space of time, MySQL will count those it has not yet processed as part of the backlog. As soon as the back_log limit is reached, any more requests that would be queued are instead refused. If your system is one that gets large numbers of connection requests in short bursts, and you find that some are getting refused for this reason, you should increase the back_log value. If your system is just busy, and the requests are a constant stream, increasing the back_log on its own will not do much. It gives your server breathing space to handles short bursts, but it does not help with an overloaded system.
Optimizing the sort_buffer Variable
The sort_buffer variable has already been discussed as far as it pertains to speeding up the operations of myisamchk (in Chapter 10, "Basic Administration") but it can also be a useful variable to fine-tune for everyday operations. If you do lots of sorting normally (frequently using ORDER BY on large tables, for example), the sort_buffer is a useful one to change. Each thread that performs a sort allocates a buffer of sort_buffer size. The my-huge.cnf configuration file (for systems with at least 1GB memory) defaults the sort_buffer to 256M for myisamchk, and 2M for mysqld. Although you want the mysqld figure to be able to handle large sorts, if you have many simultaneous connections performing ORDER BY clauses, because each is assigned a sort_buffer, you can run into memory problems.
Configuring InnoDB Tables
To get InnoDB tables running smoothly, it is even more critical to configure the variables correctly than with MyISAM tables. The most important is the innodb_data_file_path, which specifies the space available to the tables (data and indexes). It specifies one or more data files, as well as allocating a size to them. You should make the last data file auto extend (only the last data file can do this). So, instead of simply running out of space when all the space is taken, the auto extended data file will grow (in chunks of 8MB) to accommodate the extra data. For example:
innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend
Here the two data files are placed on different disks (called disk1 and disk2). The data will first be placed in ibdata1, until the 900MB limit is reached, and then will be placed into ibdata2. Once the 50MB limit is reached, ibdata2 will automatically extend in 8MB chunks.
If a disk becomes physically full, you'll need to add another data file on another disk, which requires some manual work for configuration. To do this, look at the physical size of the final data file and round it down to the nearest megabyte. Set this data file size specifically, and add the new data file definition. For example, if the disk2 specified previously fills up with ibdata2 at 109MB, you'll use something like the following definition:
innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ ibdata3:500M:autoextend
You'll need to restart the server for the changes to take effect.
Introducing the mysqld Options
Table 13.1 describes the mysqld options.
| Option | Description |
|---|---|
| --ansi | MySQL not only has a number of extensions but also a number of differences to standard ANSI (standard SQL, as defined by the American National Standards Institute) behavior. If this is set, MySQL will run in ANSI mode (the changes this causes are discussed later in this chapter in the section "Running MySQL in ANSI Mode"). |
| -b, --basedir=path | The path to the base directory or the MySQL installation directory. Other paths are usually taken relative to this. |
| --big-tables | Allows large result sets by saving all temporary sets on file when memory is not sufficient. |
| --bind-address=IP | The Internet Protocol (IP) address or hostname to which to bind MySQL. |
| --character-sets-dir=path | The directory where the character sets are located. |
| --chroot=path | For security purposes, you can start MySQL in a chroot environment with this option. This causes MySQL to run in a subset of the directories, hiding the full directory structure. This does, however, limit the usage of LOAD DATA INFILE and SELECT ... INTO OUTFILE. |
| --core-file | This option causes a core file to be written if mysqld dies unexpectedly. Some systems may require you to specify a --core-file-size. Some systems may also not write a core file if the --user option is used. |
| -h, --datadir=path | Path to the data directory. |
| --debug[...]= | If MySQL is configured with --with-debug, this option can be used to generate a trace file of what mysqld does. |
| --default-character-set=charset | Sets the default character set (the default is latin1). |
| --default-table-type=type | Sets the default table type (tables of this type are created if no table type is specified in the CREATE statement). By default, this will be MyISAM. |
| --delay-key-write-for-all-tables | With this option, MySQL does not flush the key buffers between writes for any MyISAM table. |
| --des-key-file=filename | The default keys are read from this file. This is used by the DES_ENCRYPT() and DES_DECRYPT() functions. |
| --enable-external-locking | This option enables system locking. It should not be used on systems where the locked daemon does not work fully. (This applied to Linux, although this may no longer be the case with newer versions.) |
| --enable-named-pipe | On Windows NT/2000/XP, this option enables support for named pipes. |
| -T, --exit-info | A bit mask of different flags used in debugging. Not suggested you use this unless you know what you're doing! |
| --flush | This option ensures MySQL flushes all changes to disk after each SQL statement. Usually the operating system handles this. You should not need to use this unless you're having problems. |
| -?, --help | Displays a help list and exits. |
| --init-file=file | Tells MySQL to execute SQL statements contained in this file when it startsup. |
| -L, --language=... | This option sets the language to be used for client error messages. Can be the language or the full path to the language file. |
| -l, --log[=file] | Connections and queries will be logged to the specified file. |
| --log-isam[=file] | This option logs all changes to MyISAM or ISAM files to the specified file (only used when debugging these table types). |
| --log-slow-queries[=file] | Logs all queries that take longer than the value of the variable long_query_time (in seconds) to execute to the slow query log. |
| --log-update[=file] | Logs all updates to the specified update log. Instead use --log-bin. |
| --log-bin[=file] | Logs all updates to the specified binary update log. |
| --log-long-format | Logs more information. If the slow query log is being used (--log-slow-queries), any queries that do not use an index are logged there as well. |
| --low-priority-updates | If this option is used, all inserts, updates, and deletes will have a lower priority than selects. Where you don't want this to apply to all queries, you can use SET OPTION SQL_LOW_PRIORITY_UPDATES=1 to apply it to a specific thread or LOW_PRIORITY ... to apply it to a specific INSERT, UPDATE, or DELETE query. |
| --memlock | Locks mysqld into memory. This option is only available if your system supports the mlockall() function (as Solaris does). You'd normally only use this if the operating system is having problems and mysqld is swapping to disk. You can see if --memlock has been used by looking at the value of the locked_in_memory variable. |
| --myisam-recover [=option[,option...]]] | The available options are DEFAULT, BACKUP, FORCE, QUICK, or "". If this is set to anything but "", when MySQL starts it will check tables to see if they are marked as crashed or not closed properly. If so, it will run a check on the table and attempt to repair corrupted tables. If the BACKUP option is used, MySQL will create a backup copy of the .MYD data file if any changes are made during the course of the repair (giving it the extension .BAK). The FORCE option forces the repair even if data is to be lost, and the QUICK option does not check the rows if there are no delete blocks in the data. Allerrors will be noted in the error log, so you can see what happened. Setting the BACKUP and FORCE options together allow MySQL to recover automatically from many problems (with the backup in case things go wrong). DEFAULT is the same as not giving any options. |
| --pid-file=path | Specifies the path to the pid (process id) file. |
| -P, --port=... | The port number that MySQL uses to listen for TCP/IP connections. |
| -o, --old-protocol | Specifies that MySQL use the ancient 3.20 protocol for compatibility with some equally ancient clients. |
| --one-thread | Specifies that MySQL only use one thread. You only want to use this for debugging! |
| -O, --set-variable var=option | Sets a variable to allow you to optimize it. The full list of variables follows this table, in Table 13.2. |
| --safe-mode | Skips some optimizing stages. This option implies the --skip-delay-key-write option. |
| --safe-show-database | Not used in any but the earliest versions of MySQL 4. If set, users who do not have any privileges having anything to do with a database will not see that database listed when they perform a SHOW DATABASES statement (the SHOWDATABASES privilege removes the need for this). |
| --safe-user-create | This option adds to security by not allowing users to create new users (with the GRANT statement) unless they have INSERT privilege on the mysql.user table or one of the columns in that table. |
| --skip-concurrent-insert | Nullifies concurrent inserts (where selects and inserts can be performed at the same time on optimized tables). You should not need to do this unless debugging. |
| --skip-delay-key-write | Causes MySQL to ignore the delay_key_write option for all tables. |
| --skip-grant-tables | This option starts MySQL without the privilege tables (giving everyone fullaccess). Never use this unless you have to (such as when you, as root, have forgotten the password). Once you've finished, run mysqladmin flush-privileges or mysqladmin reload to start using the privilege tables again. |
| --skip-host-cache | Hostnames are usually cached, but you can force MySQL to query the DNS server for every connect instead. This will slow down connection speeds. |
| --skip-external-locking | Disables system locking. This has important consequences for myisamchk; see "Analyzing Tables with myisamchk" in Chapter 10. |
| --skip-name-resolve | MySQL does not resolve hostnames, so all Host column values in the privilege tables must be a specific IP (or localhost). Hostnames are not resolved. This option can improve connection speeds if you have many hosts or slow DNS. |
| --skip-networking | This option causes MySQL to allow only local connections. It will not listen for TCP/IP (Transmission Control Protocol/Internet Protocol) connections at all. This is a good security measure if possible. |
| --skip-new | MySQL uses ISAM as a default table type and does not use some oftheoptions that were new in version 3.23. It also implies --skip-delay-key-write. This option should not be needed anymore,unless its behaviorchanges. |
| --skip-symlink | This option ensures that one cannot delete or rename files to which a symlinked file in the data directory points. You should use it if you are not using symlinks as a security measure to ensure no one can drop or rename a file outside of the mysqld data directory. |
| --skip-safemalloc | This options speeds up performance as it avoids checking for overruns for each memory allocation and memory freeing (these checks are done when MySQL is configured with --with-debug=full). |
| --skip-show-database | If set, the SHOW DATABASES statement does not return results unless the client has the PROCESS privilege. (The SHOW DATABASES privilege, introduced in early versions of MySQL 4, removes the need for this.) |
| --skip-stack-trace | Does not use stack traces (which is useful if running mysqld under a debugger). Some systems require this option to get a core file. |
| --skip-thread-priority | Disables the use of thread priorities for a faster response time. |
| --socket=path | The path to the socket file for use for local connections (instead of the default, usually /tmp/mysql.sock). |
| --sql-mode=option[,option[, option...]] | The various differences between ANSI standard and MySQL can be set using these options. They are REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, SERIALIZE, ONLY_FULL_GROUP_BY, or "" (to reset). Using them all is the same as the --ansi option. See the discussion later in this chapter, "Running MySQL in ANSI Mode," for what each difference is. |
| --temp-pool | This option should only be needed when an operating system leaks memory when creating large numbers of new files with different names (as happened with some versions of Linux). Instead, MySQL will use a small set of names for temporary files. |
| --transaction-isolation= { READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE } | Sets the default transaction isolation level. See the discussion in Chapter 4. |
| -t, --tmpdir=path | The directory used to store temporary files and tables. It's useful to make itdifferent to your usual temporary space if that is too small to hold temporary tables. |
| -u, --user= [user_name | userid] | Supplies a username to run MySQL as. When starting mysqld as root, this option must be used. |
| -V, --version | Displays version information and exits. |
| -W, --warnings | Warnings will be displayed in the error file. |
Exploring the mysqld Variables
When running mysqladmin variables from the command line, or SHOW VARIABLES from the mysql prompt, a long list of variables will be displayed. Most often, they relate to an option that you can set in the configuration file. Table 13.2 explains the variables displayed. Depending on your system setup and version, you may not have all these options—or, more likely, as MySQL keeps adding to them, you may have more.
| Variable | Description |
|---|---|
| ansi_mode | MySQL not only has a number of extensions but also a number of differences to standard ANSI behavior. If this is set, MySQL will run in ANSI mode (the changes this causes are discussed later in this chapter, in the section "Running MySQL in ANSI Mode"). |
| back_log | The number of queued connection requests MySQL can have waiting before it starts refusing connections. This is the same as the size of the listen queue for incoming TCP/IP connections, which is also limited by the operating system. The lower of back_log and the operating system limit will apply. See the operating system documentation (for example, man listen on Unix) for more information. |
| basedir | The path to the base directory or the MySQL installation directory. |
| bdb_cache_size | The size of the buffer allocated cache data and indexes for BDB tables. If yoursystem does not use BDB tables, use the --skip-bdb option to avoid wasting memory. |
| bdb_lock_detect | The Berkeley lock detect, which can be one of DEFAULT, OLDEST, RANDOM, or YOUNGEST. |
| bdb_log_buffer_size | The size of the buffer for BDB logs. If your system does not use BDB tables, use the --skip-bdb to avoid wasting memory. |
| bdb_home | The base directory for BDB tables, which should be the same as --datadir. |
| bdb_max_lock | The maximum number of locks that can be applied to a BDB table. Increase this if your transactions are likely to be long or your queries require many rows to be examined. Errors such as bdb: Lock table is out of available locks or Got error 12 from ... indicate a need to increase the value. The default value is 10000. |
| bdb-no-recover | If set, MySQL does not start BDB in recover mode. Usually you should only set this if there is corruption in the BDB logs that prevents a successful startup. |
| bdb-no-sync | If set, MySQL will not synchronously flush the logs. |
| bdb_logdir | The directory containing the BDB logs. |
| bdb_shared_data | If set, MySQL starts BDB in multiprocess mode, meaning that DB_PRIVATE isnot used. |
| bdb_tmpdir | The directory location for BDB temporary files. |
| binlog_cache_size | The cache size for transactions to be written to the binary log. If the transactions are large and take more than the default 32KB cache, you should increase this. |
| character_set | The character set to use when no other is specified (usually latin1). |
| character_sets | The full list of supported character sets. If you are compiling MySQL from source and know you are never going to use them, you can compile MySQL not to support the extra character sets. |
| concurrent_inserts | If active (by default it is), you can insert into MyISAM tables at the same time as querying, giving a performance gain (as long as the table contains no gaps from previously deleted records. You can ensure this by regularly optimizing the tables). The --safe or --skip-new options nullify this. |
| connect_timeout | The time in seconds MySQL waits for packets before it times out with a Bad handshake. The default is 5 seconds. This helps to prevent denial of service attacks where many bad connection attempts are made in order to prevent legitimate users from connecting. |
| datadir | The directory where the data is stored. |
| delay_key_write | If active (the default), MySQL will not flush the key buffer for a table on every index update for tables with the DELAY_KEY_WRITE option. Rather, it will only be flushed when the table is closed. This increases the speed of key writes, but it also increases the chance of corruption, so you should regularly check the tables. You can specify that the DELAY_KEY_WRITE option is default for all tables by using the --delay-key-write-for-all-tables option. The --safe or --skip-new options nullifies this option. |
| delayed_insert_limit | After inserting delayed_insert_limit rows, MySQL checks to see if there are any SELECT statements pending, and processes these, before continuing with the remaining INSERT DELAYED statements. The default is 100 rows. |
| delayed_insert_timeout | This determines how long an INSERT DELAYED thread should wait for INSERT statements before terminating. |
| delayed_queue_size | The number of rows allocated for the INSERT DELAYED queue. If this limit is reached, clients performing INSERT DELAYED will wait until there is space. |
| flush | If set, MySQL flushes all changes to disk after each SQL statement. Usually the operating system handles this. Defaults to OFF as you should not need to use this unless you're having problems. |
| flush_time | The time in seconds between automatic flushes (where the tables are closed and synchronized to disk). This is usually set to 0 unless you're running a system with very little resources or Windows 95/98/Me. |
| ft_min_word_len | The minimum length of words to be included in a FULLTEXT index. After changing this value, any FULLTEXT indexes will need to be rebuilt. The default is 4. |
| ft_max_word_len | The maximum length of words to be included in a FULLTEXT index. After changing this value, any FULLTEXT indexes will need to be rebuilt. The default is 254. |
| ft_max_word_len_sort | Words of this length or less are inserted into the FULLTEXT index with the fast index re-creation when the index is rebuilt. Words longer than this length are inserted into the index the slow way. You are unlikely to want to change the default (20) unless the words in your index are of unusual average lengths. If the value is set too high, the process will be slower as the temporary files will be bigger, and fewer keys will be in one sort block. If the value is too low, too many words will be inserted the slow way. |
| ft_boolean_syntax | The list of operators supported by the MATCH ... AGAINST(... IN BOOLEAN MODE) statement (+ -><()~*:""&|). |
| have_innodb | Set to YES if MySQL supports InnoDB tables or DISABLED if the --skip-innodb option is used. |
| have_bdb | Set to YES if MySQL supports BDB tables or DISABLED if the --skip-bdb option is used. |
| have_raid | Set to YES if MySQL supports the RAID (Redundant Array of Inexpensive Disks) option. |
| have_openssl | Set to YES if MySQL supports SSL (Secure Sockets Layer) encryption between client and server. |
| init_file | The name of a file containing SQL statements to be executed when the server starts (by default none is specified). |
| innodb_data_home_dir | The home directory for InnoDB data files and used as the common part of the path. If this is not mentioned, it will be the same as datadir. By setting this to an empty string, you can use absolute file paths in innodb_data_file_path. |
| innodb_data_file_path | The paths to individual data files and their sizes. The innodb_data_home_dir part of the path is added to this to give the full path. File sizes are specified in megabytes (M) or gigabytes (G). Can be larger than 4GB on operating systems that support big files, and the sum of the sizes should be at least 10MB. |
| innodb_mirrored_log_groups | Specifies the number of identical copies of log groups to keep for the database. Currently this should be 1. |
| innodb_log_group_home_dir | Specifies the directory path to InnoDB log files. |
| innodb_log_files_in_group | Specifies the number of log files in the log group. 3 is the suggested value (logsare written in rotation). |
| innodb_log_file_size | Specifies the size in megabytes of each log file in a log group. Suggested values are from 1MB to 1/innodb_log_files_in_group of the innodb_buffer_pool_size. A high value saves disk input/output (I/O) because less flush activity is needed, but slows recovery after a crash. The total size of the log files should not be more than 4GB on 32-bit computers. |
| innodb_log_buffer_size | Specifies the size of the buffer used to write logs. A suggested value is 8MB. The larger the buffer, the less disk I/O because then transactions do not need to be written to disk until they are committed. |
| innodb_flush_log_at_trx_commit | If set, logs are flushed to disk as soon as the transaction is committed (and are therefore permanent and able to survive a crash). This should not normally beset to anything but ON if your transactions are important. You can set this to OFF if performance is more critical and you want to reduce disk I/O at the cost of safety. |
| innodb_log_arch_dir | Specifies the directory where the logs are to be archived. Currently this should be the same as innodb_log_group_home_dir because log archiving is not currently used. |
| innodb_log_archive | If set, InnoDB log files will be archived. Currently, MySQL recovers using its own log files, so this should be set to OFF. |
| innodb_buffer_pool_size | Specifies the size in bytes of the memory buffer used to cache table indexes and data. The larger this is, the better the performance because less disk I/O is then required. Up to 80 percent of memory is suggested on a dedicated database server because any larger may cause operating system paging. |
| innodb_additional_mem_pool_size | Specifies the size in bytes of a memory pool used to store information about the internal data structures. 2MB is a possibility, but if you have many tables, make sure there is enough memory allocated; otherwise MySQL will use operating system memory (you can see the warnings in the error log if this occurs and increase the value). |
| innodb_file_io_threads | The number of file I/O threads in InnoDB. The suggested value is 4, but it is suspected Windows may benefit from a higher setting. |
| innodb_lock_wait_timeout | The time in seconds an InnoDB transaction waits for a lock before rolling back. InnoDB detects deadlocks automatically in its own lock tables, but if they come from outside (such as a LOCK TABLES statement), deadlock may arise, in which case this value is used. |
| innodb_flush_method | Flushing method. The default is fdatasync, and the alternative is O_DSYNC. Usually fdatasync is faster, though on some versions of Linux and Unix it has proven to be slower. |
| interactive_timeout | The time in seconds that the server waits for any activity on an interactive connection (one using the CLIENT_INTERACTIVE option when connecting) before closing it. The wait-timeout option applies to ordinary connections. The default is 28800. |
| join_buffer_size | The size in bytes of the buffer used for full joins (joins where no index is used).The buffer is allocated to each full join. Increasing this will make full joins faster, although the best way to speed up a join is by adding appropriate indexes. |
| key_buffer_size | The size in bytes of the buffer used for index blocks. This is discussed fully inthe section "Optimizing Key Buffer Size." |
| language | The location of the language file used for error messages. |
| large_file_support | ON if MySQL was compiled with support for large files. The default is ON. |
| locked_in_memory | ON if MySQL was locked in memory (in other words, if mysqld was started with the --memlock option). The default is OFF. You normally only want this ON if the operating system is having problems and mysqld is swapping to disk. |
| log | ON if logging of all queries is enabled. |
| log_update | ON if update logging is enabled (you should rather use the binary log for this). |
| log_bin | ON if binary update logging is enabled. |
| log_slave_updates | ON if updates from a slave are logged. |
| long_query_time | The time in seconds that defines a slow query. Queries that take longer than this will cause the slow queries counter to be incremented and will be logged in the slow query log file if slow query logging is enabled. |
| lower_case_table_names | Set to 1 if table names are stored in lowercase and are case insensitive. The default is 0. |
| max_allowed_packet | The maximum allowable size in bytes of one packet of data. The message buffer is initialized to the size specified by net_buffer_length, but it can grow up to this size. If you use large BLOB or TEXT columns, set this to the size of the largest column. |
| max_binlog_cache_size | The largest amount of memory in bytes a multistatement transaction can usewithout throwing the error: "Multistatement transaction required more than max_binlog_cache_size bytes of storage. |
| max_binlog_size | As soon as the current binary log exceeds this size, the logs will be rotated and a new one created. |
| max_connections | The maximum number of connections allowed. See the earlier discussion, "Dealing with Too Many Connections." |
| max_connect_errors | The maximum number of times a host can attempt a connection that becomes interrupted before the host is blocked from any further connections. This limit is imposed to reduce the possibility of denial of service attacks. Hosts can be unblocked by running FLUSH HOSTS. |
| max_delayed_threads | The maximum number of threads that can handle INSERT DELAYED statements. Once this is reached, further INSERT statements will be ordinary inserts and not make use of the DELAYED attribute. |
| max_heap_table_size | Maximum size in bytes that HEAP tables can become. |
| max_join_size | Joins that MySQL determines will return more rows than this limit will return an error. This prevents users from accidentally (or maliciously) running huge queries that could take return many millions of rows and take up too many resources. |
| max_sort_length | When sorting BLOB or TEXT fields, only up to this number of bytes will be used. For example, if this is set to 1024, only the first 1024 characters will beused in sorting. |
| max_user_connections | Determines the maximum number of connections a single user can haveactive. The default, 0, indicates there is no limit (except for max_connections). |
| max_tmp_tables | The maximum number of temporary tables a client can keep open at the same time. (At the time of this writing, this is not used; check the latest documentation.) |
| max_write_lock_count | If this many consecutive write locks occur, MySQL will allow a number of read locks to run. |
| myisam_bulk_insert_tree_size | When MySQL inserts in bulk (for example, LOAD DATA INFILE...), it uses a tree-like cache to speed up the process. This is the maximum size in bytes ofthe cache for each thread. The default is 8MB, and setting it to 0 disables this feature. The cache is only used when adding to a table that is not empty. |
| myisam_recover_options | The available options are DEFAULT, BACKUP, FORCE, QUICK, or OFF. If this is set to anything but OFF, when MySQL starts it will check tables to see if they are marked as crashed or not closed properly. If so, it will run a check on the table and attempt to repair corrupted tables. If the BACKUP option is used, MySQL will create a backup copy of the .MYD data file if any changes are made during the course of the repair (giving it the extension .BAK). The FORCE option forces the repair even if data is to be lost, and the QUICK option does not check the rows if there are no delete blocks in the data. All errors will be noted in the error log, so you can see what happened. Setting the BACKUP and FORCE options together allow MySQL to recover automatically from many problems (with the backup in case things go wrong). |
| myisam_sort_buffer_size | The size in bytes of the buffer allocated when sorting or repairing an index. |
| myisam_max_extra_sort_file_size | When MySQL creates an index, it subtracts the key cache size from the size of the temporary table it would use with fast index creation. If the difference is larger than this amount (specified in megabytes), MySQL uses the key cache method. |
| myisam_max_sort_file_size | The maximum size (in megabytes) of the temporary file MySQL creates when creating or repairing indexes. If this size would be exceeded, MySQL uses the slower key cache method to create or repair the index. |
| net_buffer_length | The size in bytes that the communication buffer is set to between queries. Tosave memory is systems with low memory, set this to the expected length of SQL statements sent by clients. It is automatically enlarged to the size of max_allowed_packet if the statement exceeds this length. |
| net_read_timeout | Time in seconds that MySQL waits for data from a connection before aborting the read. If no data is expected, the net_write_timeout is used, and slave_net_timeout is used for the master/slave connection. |
| net_retry_count | The number of times to retry a read on a communication port before aborting. |
| net_write_timeout | The number of seconds to wait for a block to be written to a connection beforeaborting. |
| open_files_limit | MySQL uses this value to reserve file descriptors. Increase this if you get the error Too many open files. Usually this is set to 0, in which case MySQL uses the larger of max_connections*5 or max_connections + table_cache*2. |
| pid_file | The path to the pid file. |
| port | The port number that MySQL uses to listen for TCP/IP connections. |
| protocol_version | The protocol version that MySQL uses. |
| record_buffer | MySQL allocates a buffer of this size in bytes for each thread that performs a sequential scan (where records are read in order, one after another). If you do many sequential scans, you may want to increase this value. |
| record_rnd_buffer | A buffer of this size in bytes is allocated when reading rows in nonsequential order (for example, after a sort), and rows read through this avoid disk seeks. If not set, it will be the same as the record_buffer. |
| query_buffer_size | The initial size in bytes allocated to the query buffer. It should be sufficient for most queries; otherwise it should be increased. |
| query_cache_limit | The limit in bytes for the query cache. Results larger than this will not be cached. The default is 1MB. |
| query_cache_size | The size in bytes allocated to the query cache (where results are stored from old queries). 0 indicates the cache is disabled. |
| query_cache_startup_type | This can be one of 0, 1, or 2. 0 (off) means MySQL does not cache or retrieve results. 1 (on) means MySQL caches all results unless they come with SQL_NO_CACHE. 2 (demand) means that only queries with SQL_CACHE are cached. |
| safe_show_database | Not used in any but the earliest versions of MySQL 4. If set, users who do not have any privileges having anything to do with a database will not see that database listed when they perform a SHOW DATABASES statement (the SHOW DATABASES privilege removes the need for this). |
| server_id | The ID of the server. Important for replication purposes to identify servers. |
| skip_external_locking | Disables system locking if ON. Has important consequences for myisamchk (see Chapter 10 for information on analyzing tables with myisamchk). |
| skip_networking | Is ON if MySQL allows only local connections. |
| skip_show_database | If set, the SHOW DATABASES statement does not return results unless the client has the PROCESS privilege. (The SHOW DATABASES privilege, introduced in early versions of MySQL 4, removes the need for this.) |
| slave_net_timeout | Time in seconds MySQL waits for more data from a master/slave connection before the read aborts. |
| slow_launch_time | Time in seconds before the launch of a thread increments the slow_launch_threads counter. |
| socket | The path to the Unix socket used by the server. |
| sort_buffer | The size in bytes allocated to the buffer used by sorts. See the discussion earlier in this chapter titled "Optimizing the sort_buffer variable." |
| table_cache | The number of open tables for all threads. See the discussion earlier in this chapter titled "Optimizing table_cache." |
| table_type | The default table type (usually MyISAM). |
| thread_cache_size | The number of threads kept in a cache for reuse. New threads are taken from the cache if any are available, while a client's threads are placed in the cache when disconnecting if space is available. If you have lots of new connections, you can increase this value to improve performance. Systems with good thread implementation normally don't benefit much from this. You can see its efficiency by comparing the Connections and Threads_created status variables. |
| thread_concurrency | On Solaris systems, MySQL uses this value to determine whether to call thethr_setconcurrency() function, which assists the threads system inknowing the number of threads that should be running at the same time. |
| thread_stack | The size in bytes of the stack for each thread. The behavior of the crash-me benchmark depends on this value. |
| timezone | The server time zone. |
| tmp_table_size | The maximum size for a temporary table in memory. If it becomes larger, itwill automatically become a MyISAM table on disk. If you perform lots of queries that result in large temporary tables (such as with complex GROUP BY clauses) and have enough memory, increase this value. |
| tmpdir | The directory used to store temporary files and tables. |
| version | The server version number. |
| wait_timeout | The time in seconds MySQL waits for activity on a connection before closing it. The interactive_timeout applies to interactive connections. |
Exploring All the Status Variables
Status variables are reset every time the server restarts. They allow you to monitor the behavior of your server and to identify potential bottlenecks, problems, and improvements you can make. Table 13.3 contains a comprehensive list.
| Value | Description |
|---|---|
| Aborted_clients | Indicates the number of connections that were aborted because for some reason the client did not close the connection properly. This could happen ifthe client does not call the mysql_close() function before exiting, the wait_timeout or interactive_timeout limits have been exceeded, orthe client closed in the middle of a transfer. |
| Aborted_connects | Indicates the number of failed attempts to connect to MySQL server that failed. This could occur because the client tried to connect with the wrong password, does not have permission to connect, takes longer than connect_timeout seconds to obtain a connect packet, or the packet doesn't contain the correct information. |
| Bytes_received | The number of bytes that has been received from all clients. |
| Bytes_sent | The number of bytes that has been sent to all clients. |
| Com_[statement] | One of these variables exists for each kind of statement. The value indicates the number of times this statement has been executed. |
| Connections | The number of attempts to connect to the MySQL server. |
| Created_tmp_disk_tables | The number of implicit temporary tables on disk that were created while executing statements. |
| Created_tmp_tables | The number of implicit temporary tables in memory that were created whileexecuting statements. |
| Created_tmp_files | The number of temporary files created by mysqld. |
| Delayed_insert_threads | The number of delayed insert handler threads currently in use. |
| Delayed_writes | The number of records written by an INSERT DELAYED statement. |
| Delayed_errors | The number of records written by an INSERT DELAYED where an error occurred. The most usual error is a duplicate key. |
| Flush_commands | The number of FLUSH statements executed. |
| Handler_commit | The number of internal COMMIT commands. |
| Handler_delete | The number of times a row was deleted from a table. |
| Handler_read_first | The number of times the first entry of an index was read, usually indicating a full index scan (for example, assuming indexed_col is indexed, the statement SELECT indexed_col from tablename results in a full index scan). |
| Handler_read_key | The number of requests where an index is used when reading a row. You'll want this to increase quickly, as it indicates your queries are using indexes. |
| Handler_read_next | The number of requests to read the next row in order of an index. This would get increased if you are doing a full-index scan or querying an index based ona range constant. |
| Handler_read_prev | The number of requests to read the previous row in index order. This would be used by an SELECT fieldlist ORDER BY fields DESC type of statement. |
| Handler_read_rnd | The number of requests to read a row based on a fixed position. Queries that require the results to be sorted would increment this counter. |
| Handler_read_rnd_next | The number of requests to read the next row in the data file. You would usually not want this to be high because it means that queries are not making use of indexes and have to read from the data file. |
| Handler_rollback | The number of internal ROLLBACK commands. |
| Handler_update | The number of requests to update a record in a table. |
| Handler_write | The number of requests to insert a record into a table. |
| Key_blocks_used | The number of blocks used in the key cache. |
| Key_read_requests | The number of requests causing a key block to be read from the key cache. The Key_reads:Key_read_requests ratio should be no higher than 1:100 (i.e., 1:10 is bad). |
| Key_reads | The number of physical reads causing a key block to be read from disk. The Key_reads:Key_read_requests ratio should be no higher than 1:100 (again, 1:10 is bad). |
| Key_write_requests | The number of requests causing a key block to be written to the cache. |
| Key_writes | The number of times there has been a physical write of a key block to disk. |
| Max_used_connections | The maximum number of connections in use at any one time. See the connections discussion earlier in this chapter ("Dealing with Too ManyConnections"). |
| Not_flushed_key_blocks | The number of key blocks in the key cache that have changed but have not yetbeen flushed to disk. |
| Not_flushed_delayed_rows | The number of records currently in INSERT DELAY queues waiting to be written. |
| Open_tables | The number of tables that are currently open. See the table cache discussion earlier in this chapter ("Optimizing table_cache"). |
| Open_files | The number of files that are currently open. |
| Open_streams | The number of streams that are open. These are mostly used for logging. |
| Opened_tables | The number of tables that have been opened. See the table cache discussion earlier in this chapter ("Optimizing table_cache"). |
| Qcache_queries_in_cache | The number of queries in the cache. |
| Qcache_inserts | The number of queries added to the cache. |
| Qcache_hits | The number of times the query cache has been accessed. |
| Qcache_not_cached | The number of queries that were not cached (due to being too large, or because of the QUERY_CACHE_TYPE). |
| Qcache_free_memory | The amount memory still available for the query cache. |
| Qcache_total_blocks | The total number of blocks in the query cache. |
| Qcache_free_blocks | The number of free memory blocks in the query cache. |
| Questions | Total number of queries initiated. |
| Rpl_status | The status of failsafe replication. (This is only used by later versions of MySQL4.) |
| Select_full_join | The number of joins that have been performed without using indexes. You donot want this to be high. |
| Select_full_range_join | The number of joins performed using a range search on the reference table. |
| Select_range | The number of joins performed using ranges on the first table (a large number here is normally fine). |
| Select_range_check | The number of joins performed without indexes that checked for indexes after each row. |
| Select_scan | The number of joins performed that did a full scan of the first table. |
| Slave_open_temp_tables | The number of currently open temporary tables held by a slave. |
| Slave_running | ON or OFF. Is ON if the server is a slave connected to a master. |
| Slow_launch_threads | The number of threads that took more than slow_launch_time to create. |
| Slow_queries | The number of queries that took more than long_query_time. |
| Sort_merge_passes | The number of merge passes performed during a sort. If this becomes too large, you should increase the sort_buffer. |
| Sort_range | The number of sorts performed with ranges. |
| Sort_rows | The number of sorted rows. |
| Sort_scan | The number of sorts performed by scanning the table. |
| ssl_[variables] | Contents of various variables used by SSL. This is not implemented in early versions of MySQL 4. |
| Table_locks_immediate | The number of times a table lock was immediately acquired. |
| Table_locks_waited | The number of times a table lock was not immediately acquired. A high value is usually a symptom of performance problems. You'll need to optimize by improving your queries and indexes, using another table type, splitting your tables, or using replication. |
| Threads_cached | The number of threads currently in the thread cache. |
| Threads_connected | The number of currently open connections. |
| Threads_created | The number of threads created to handle connections. |
| Threads_running | The number of active (not sleeping) threads. |
| Uptime | The time in seconds the server has been running. |
|
|
