| Question 1: | Using programs available from MySQL AB, there are three ways to access server status information. What are they? |
| Question 2: | Using programs available from MySQL AB, there are three ways to access server system variables. What are they? |
| Question 3: | What can you learn from server status information and server system variables? Give an example for each type of variable. |
| Question 4: | Assume that you've just installed MySQL, but you're not sure where the installation and data directories are located. However, you can connect to the server using mysql. What SQL statement or statements would you issue to find out that information? |
| Question 5: | In your MySQL option file, you want to have the following settings for the server:
The installation directory should be set to D:\mysql. The data directory should be set to D:\mysql\data. The key buffer size should be 24MB.
What are the appropriate entries in your option file? |
| Question 6: | You can set server parameters (system variables) in a number of ways:
With options in MySQL option files With options on the command line at server startup With a SET GLOBAL statement With a SET SESSION (or SET LOCAL) statement
Assume that you choose to set server parameters using options in MySQL option files. What's the lifetime of the settings? What's the scope within which each setting applies? How would you set the sort_buffer_size variable to a value of 512000? |
| Question 7: | Assume that you choose to set server parameters using options on the command line at server startup. What's the lifetime of the settings? What's the scope within which each setting applies? How would you set the sort_buffer_size variable to a value of 512000? |
| Question 8: | Assume that you choose to set server parameters using a SET GLOBAL statement. What's the lifetime of the settings? What's the scope within which each setting applies? How would you set the sort_buffer_size variable to a value of 512000? |
| Question 9: | Assume that you choose to set server parameters using a SET SESSION (or SET LOCAL) statement. What's the lifetime of the settings? What's the scope within which each setting applies? How would you set the sort_buffer_size variable to a value of 512000? |
| Question 10: | What SQL statement will display the status variables that tell you how many times the server has executed each type of SQL statement? |
| Question 11: | You want to see the errors reported by the server at startup. Under Windows, how would you direct that output to your terminal rather than to the error log? |
| Question 12: | You want to see the errors reported by the server at startup. Under Unix, how would you direct that output to your terminal rather than to the error log? |
| Question 13: | What kinds of errors might the server report in its diagnostic output? |
| Question 14: | Besides the status variables, what other sources of information are available for checking server load and performance? |
| Question 15: | How would you disable the InnoDB and the BDB storage engines? Why might you want to do that? |
| Question 16: | Name three server parameters that affect the performance of SELECT statements. |
| Question 17: | What might the following session listing indicate about server configuration? If further analysis would point out there is in fact a problem, how could you identify and solve it?
mysql> SHOW STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 110 |
+---------------+-------+
mysql> SELECT * FROM faq LIMIT 1;
+----------------+----------------------
| cdate | question ...
+----------------+----------------------
| 20030318160028 | What is the name of t ...
+----------------+----------------------
mysql> SHOW STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 111 |
+---------------+-------+
mysql> SELECT * FROM t LIMIT 1;
+------+
| i |
+------+
| 6 |
+------+
mysql> SHOW STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 112 |
+---------------+-------+
|
| Question 18: | What does key cache efficiency mean? |
| Question 19: | What values are necessary to calculate the key cache efficiency? |
| Question 20: | How would you retrieve the values needed to calculate the key cache efficiency? |
| Question 21: | How can you improve key cache efficiency for better server performance? |
| Question 22: | How could you determine the number of clients that connected successfully? |
| Question 23: | How could you determine the number of clients that tried to connect, but failed? |
| Question 24: | How could you determine the number of clients that closed their connections properly? |
| Question 25: | How could you determine the number of clients that either aborted their connections or were killed? |
| Question 26: | Name MySQL's per-client buffers. |
| Question 27: | How would you determine the sizes of MySQL's per-client record buffers? |
| Question 28: | How would you determine the size of MySQL's per-client join buffer? |
| Question 29: | How would you determine the size of MySQL's per-client sort buffer? |
| Question 30: | How would you determine the size of MySQL's per-client communications buffer? |
| Question 31: | The server's query cache can speed up SELECT queries. How would you check the server system variables for the query cache? |
| Question 32: | Using the server system variables for the query cache, how could you determine whether the query cache is enabled? |
| Question 33: | The server's query cache can speed up SELECT queries. How would you determine the extent to which the query cache is effective? |
| Question 34: | When running two servers on the same host, you'll have to make sure that they don't share resources they need exclusively. What are those resources? |
| Question 35: | Assume that you want to run two MySQL servers on one Windows machine (NT family). You've set up two system services called MySQL_Development and MySQL_Production. What would the absolute minimum of entries in the C:\Windows\my.ini option file look like for those two servers? |
| Question 36: | How many masters can a replication slave have? |
| Question 37: | How many slaves can a replication master have? |
| Question 38: | How does a master replication server communicate database changes to slave servers? |
| |
| 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. |
