| |
| Answer 1: | You can access server status information using any of these methods:
With the mysql client program:
mysql> SHOW STATUS;
With the mysqladmin program:
shell> mysqladmin extended-status
With the MySQLCC graphical client: Open a connection to the server. Double-click Server Administration in the MySQL Servers panel. Choose the Status tab in the Administration Panel window that appears.
|
| |
| Answer 2: | You can access server system variables using any of these methods:
With the mysql client program:
mysql> SHOW VARIABLES;
With the mysqladmin program:
shell> mysqladmin variables
With the MySQLCC graphical client: Open a connection to the server. Double-click Server Administration in the MySQL Servers panel. Choose the Variables tab in the Administration Panel window that appears.
|
| |
| Answer 3: | The server system variables provide configuration details of the server. For example, you can determine the location of the data directory like this:
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /usr/local/mysql/data/ |
+---------------+------------------------+
The server status variables provide information about the activities of the server. For example, you can check how many clients are connected to the server:
mysql> SHOW STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 17 |
+-------------------+-------+
See sections A.1.45, "SHOW VARIABLES," and A.1.42, "SHOW STATUS." |
| |
| Answer 4: | The server stores the base installation and data directory locations in its basedir and datadir system variables, which you can display using the SHOW VARIABLES statement:
mysql> SHOW VARIABLES LIKE 'basedir'; SHOW VARIABLES LIKE 'datadir';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| basedir | c:\mysql\ |
+---------------+-----------+
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| datadir | c:\mysql\data\ |
+---------------+----------------+
See section A.1.45, "SHOW VARIABLES." |
| |
| Answer 5: | The entries should be made in the [mysqld] option group like this:
[mysqld]
basedir = D:/mysql
datadir = D:/mysql/data
key_buffer_size = 24M
See section A.4, "Server System Variables." |
| |
| Answer 6: | Each time the server is started, it reads settings in option files. Settings specified in these files therefore pertain to each invocation of the server. For any given invocation, the settings have global scope and persist until the server shuts down. Example:
[mysqld]
sort_buffer_size=512000
See section A.4, "Server System Variables." |
| |
| Answer 7: | When the server is invoked with parameter settings specified on the command line, those settings pertain only to that particular invocation. The settings have global scope and persist until the server is shut down. Example:
shell> mysqld --sort_buffer_size=512000
See section A.4, "Server System Variables." |
| |
| Answer 8: | Using SET GLOBAL requires the SUPER privilege. You would specify a global option like this:
mysql> SET GLOBAL sort_buffer_size=512000;
As the statement indicates, the scope is global; it applies to any clients that connect after the variable is set. An alternative syntax for setting the option value is as follows:
mysql> SET @@global.sort_buffer_size=512000;
See sections A.4, "Server System Variables," and A.1.30, "SET." |
| |
| Answer 9: | Using SET SESSION (or SET LOCAL) sets the variable to a value that applies only to the current connection (and thus not to other connected users). This is called a session scope. Example:
mysql> SET SESSION sort_buffer_size=512000;
An alternative syntax is as follows:
mysql> SET @@session.sort_buffer_size=512000;
See sections A.4, "Server System Variables," and A.1.30, "SET." |
| |
| Answer 10: | To find out how many times the server has executed each type of statement, examine the status variables that begin with Com:
mysql> SHOW STATUS LIKE 'Com%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 3 |
... ...
| Com_show_status | 26 |
| Com_show_innodb_status | 1 |
| Com_show_tables | 3 |
| Com_show_variables | 18 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
+------------------------+-------+
This will not list the number of SELECT statements that were processed using the query cache. You can obtain that number from the Qcache_hits status variable:
mysql> SHOW STATUS LIKE 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 11019 |
+---------------+-------+
See section A.1.42, "SHOW STATUS." |
| |
| Answer 11: | Under Windows, you would start the server with the --console option:
shell> mysqld --console
|
| |
| Answer 12: | Under Unix, you would start the server directly (that is, without using a startup script such as mysqld_safe or mysql.server):
shell> mysqld
The server will send its diagnostics to the standard error output location (normally your terminal). |
| |
| Answer 13: | The errors reported by the server include the following:
Unrecognized startup options Failure to open its network interfaces (TCP/IP port, Windows named pipe, Unix socket file) Storage engine initialization failure Failure to find SSL certificate or key files Inability of the server to change its user ID Problems related to replication
|
| |
| Answer 14: | In addition to the server's status variables, sources of server load and performance information include the following:
The error log provides information also about errors that aren't fatal but might affect server performance (such as aborted connections). The slow query log provides information about queries that take a long time to perform. By default, a long time is defined as more than 10 seconds. If the --log-long-format option is specified, the slow query log also includes queries that do not use indexes. The STATUS command of the mysql client programs displays some statistical information about the server load; for example, the total number of queries (called Questions), the average number of queries per second, and the number of tables that were opened (called Opens).
|
| |
| Answer 15: | To disable the InnoDB and BDB storage engines, you could either put the settings in a MySQL option file or start the server with the appropriate options. In your option file, you would have these lines:
[mysqld]
skip-bdb
skip-innodb
The preceding is the preferred way to disable the storage engines. To disable them only for the next time the server starts, you would use the options on the command line rather than putting them in an option file:
shell> mysqld --skip-bdb --skip-innodb
Disabling an unneeded storage engine reduces the server's memory requirements because it need not allocate buffers and other data structures associated with the engine. Note that there is no provision for disabling the MyISAM storage engine.
It's also possible to disable the InnoDB and BDB storage engines entirely by compiling the server without them. |
| |
| Answer 16: | key_buffer_size affects index-related operations, sort_buffer_size affects sorting operations (ORDER BY, GROUP BY), and join_buffer_size affects join performance.
See section A.4, "Server System Variables." |
| |
| Answer 17: | The session listing seems to indicate that Opened_tables is incremented with each table that is accessed. This does not necessarily indicate there is a problem because these queries could have been the first ones that accessed those two tables since the server started. However, if you find Opened_tables being incremented steadily even when using tables that have been opened before, it might indicate a table cache that's too small. To see the size of the cache, check the value of the table_cache variable:
mysql> SHOW VARIABLES LIKE 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 1 |
+---------------+-------+
That output shows an extremely small value that is definitely too low. You can set it higher while the server is running by issuing a SET statement:
mysql> SET GLOBAL table_cache=64;
mysql> SHOW VARIABLES LIKE 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 64 |
+---------------+-------+
But more likely you would set the value in an appropriate option file. Then the setting would take effect each time you restart the server.
See section A.4, "Server System Variables." |
| |
| Answer 18: | The key cache efficiency provides information about the number of index reads from the cache, relative to index reads that need to be done from disk. The value should be as close to 1 as possible, so a value of .9 is good, but a value of .99 is much better. |
| |
| Answer 19: | The key cache efficiency is calculated from two status variables using this formula:
1 - (Key_reads / Key_read_requests)
See section A.4, "Server System Variables." |
| |
| Answer 20: | To retrieve the values necessary for calculating key cache efficiency, you would issue this statement:
mysql> SHOW STATUS LIKE 'key_read%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Key_read_requests | 280944 |
| Key_reads | 5827 |
+-------------------+--------+
The values shown in this example give this efficiency:
1 - (5827 / 280944) = .98
That value is close to 1 and is reasonably efficient.
See sections A.4, "Server System Variables," and A.1.42, "SHOW STATUS." |
| |
| Answer 21: | To improve server performance, check the value of key_buffer_size. If the value is small and memory is available, increase the key buffer size. The following example sets the size to 16MB:
mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| key_buffer_size | 512000 |
+-----------------+--------+
mysql> SET GLOBAL key_buffer_size=16777216;
mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
See sections A.4, "Server System Variables," and A.1.45, "SHOW VARIABLES." |
| |
| Answer 22: | The information can be determined using status information available from SHOW STATUS. The number of successful connections can be calculated as the number of connection attempts minus the number of unsuccessful connection attempts:
mysql> SHOW STATUS LIKE 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 18220 |
+---------------+-------+
mysql> SHOW STATUS LIKE 'Aborted_connects';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_connects | 6 |
+------------------+-------+
In this case, it is 18220-6, or 18214 successful connections made.
See section A.1.42, "SHOW STATUS." |
| |
| Answer 23: | The information can be determined using status information available from SHOW STATUS. To get the number of unsuccessful connection attempts, you would use this statement:
mysql> SHOW STATUS LIKE 'Aborted_connects';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_connects | 6 |
+------------------+-------+
See section A.1.42, "SHOW STATUS." |
| |
| Answer 24: | The information can be determined using status information available from SHOW STATUS. The number of connections that were closed properly can be calculated as the number of connection attempts, minus the number of unsuccessful attempts, minus the number of aborted clients:
mysql> SHOW STATUS LIKE 'Connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Connections | 18222 |
+-----------------+-------+
mysql> SHOW STATUS LIKE 'Aborted_connects';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Aborted_connects | 99 |
+-----------------+-------+
mysql> SHOW STATUS LIKE 'Aborted_clients';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Aborted_clients | 4 |
+-------------------+-------+
In this case, it is 18222-99-4, or 18119 connections properly closed.
See section A.1.42, "SHOW STATUS." |
| |
| Answer 25: | The information can be determined using status information available from SHOW STATUS. To get the number of connections that were improperly closed (aborted), you would use this statement:
mysql> SHOW STATUS LIKE 'Aborted_clients';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Aborted_clients | 99 |
+-----------------+-------+
See section A.1.42, "SHOW STATUS." |
| |
| Answer 26: | The MySQL server uses these per-client buffers:
Record buffers are used for sequential table scans (read_buffer_size) and when reading rows in sorted order after a sort, usually with the ORDER BY clause (read_rnd_buffer_size). The join buffer is used to perform table joins. The sort buffer is used for sorting operations. The communications buffer is used for exchanging information with the client. It begins with a size of net_buffer_length, but the server expands it up to a size of max_allowed_packet as necessary.
See section A.4, "Server System Variables." |
| |
| Answer 27: | Record buffers are used for sequential table scans (read_buffer_size), and when reading rows in sorted order after a sort, usually with the ORDER BY clause (read_rnd_buffer_size). To get their sizes, you would issue this statement:
mysql> SHOW VARIABLES LIKE 'read%buffer_size';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
See sections A.4, "Server System Variables," and A.1.45, "SHOW VARIABLES." |
| |
| Answer 28: | The join buffer is used to perform table joins. To get its size, you would issue this statement:
mysql> SHOW VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 131072 |
+------------------+--------+
See sections A.4, "Server System Variables," and A.1.45, "SHOW VARIABLES." |
| |
| Answer 29: | The sort buffer is used for sorting operations. To get its size, you would issue this statement:
mysql> SHOW VARIABLES LIKE 'sort_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| sort_buffer_size | 524280 |
+------------------+--------+
See sections A.4, "Server System Variables," and A.1.45, "SHOW VARIABLES." |
| |
| Answer 30: | The communications buffer is used for exchanging information with the client. It begins with a size of net_buffer_length, but the server expands it up to a size of max_allowed_packet as necessary. To get the sizes of these buffers, issue the following statements:
mysql> SHOW VARIABLES LIKE 'net_buffer_length';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
+------------------+--------+
mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
See sections A.4, "Server System Variables," and A.1.45, "SHOW VARIABLES." |
| |
| Answer 31: | You can display the server variables that show how the query cache is configured by issuing this statement:
mysql> SHOW VARIABLES LIKE 'query%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
+-------------------+----------+
See sections A.4, "Server System Variables," and A.1.45, "SHOW VARIABLES." |
| |
| Answer 32: | If query_cache_type is not OFF and the value of query_cache_size is greater than zero, the query cache is enabled.
See section A.4, "Server System Variables." |
| |
| Answer 33: | To check the server's usage of the query cache, examine the appropriate status variables:
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_queries_in_cache | 179 |
| Qcache_inserts | 7598 |
| Qcache_hits | 11546 |
| Qcache_lowmem_prunes | 21 |
| Qcache_not_cached | 5511 |
| Qcache_free_memory | 67099960 |
| Qcache_free_blocks | 77 |
| Qcache_total_blocks | 505 |
+-------------------------+----------+
See sections A.4, "Server System Variables," and A.1.42, "SHOW STATUS." |
| |
| Answer 34: | The following resources cannot be shared among MySQL servers:
The network interfaces (TCP/IP port, Windows named pipe, or Unix socket file) The Windows service name The log files The Unix process ID file The InnoDB tablespace files
In addition, although it's sometimes possible for servers to share a data directory, doing so isn't recommended. |
| |
| Answer 35: | Running two MySQL servers on one host under Windows requires settings for at least the TCP/IP port and the data directory. An example might look like this:
[MySQL_Development]
port=3306
datadir=C:/mysql_1/data
[MySQL_Production]
port=3307
datadir=C:/mysql_2/data
By assigning a different port for each server, you make sure that they listen on different ports (otherwise, the second server wouldn't even start). By assigning a different datadir value, you make sure that the servers use different databases. If nothing else is specified, this will also make sure that the servers use different log files and InnoDB tablespace files because those will be created in the respective data directories by default. |
| |
| Answer 36: | In MySQL, a replication slave can only have one master. |
| |
| Answer 37: | In MySQL, a master can have an unlimited number of replication slaves, although in practice the number is limited by the max_connections variable. If that variable's value is low compared to the number of slaves and if there are many other concurrent connections to the server, a slave might have to wait a very long time for a replication connection. |
| |
| Answer 38: | The master replication server communicates changes to slaves using the binary log. The master writes statements that change data into the binary log, and sends those statements to replication slaves that connect to it. |
