MySQL High Availability- P7

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

lượt xem

MySQL High Availability- P7

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL High Availability- P7: A lot of research has been done on replication, but most of the resulting concepts are never put into production. In contrast, MySQL replication is widely deployed but has never been adequately explained. This book changes that. Things are explained here that were previously limited to people willing to read a lot of source code and spend a lot of time debugging it in production, including a few late-night sessions.

Chủ đề:

Nội dung Text: MySQL High Availability- P7

  1. Indeed, there are a great many tools available to the Windows administrator. We won’t try to cover them all here, but instead we’ll focus on tools that let you monitor a Win- dows system in real time. Let’s examine some of the basic reporting tools first. The following are the most popular tools you can use to diagnose and monitor per- formance issues in Windows: • Windows Experience Index • System Health Report • Event Viewer • Task Manager • Reliability Monitor • Performance Monitor An excellent source for information about Microsoft Windows performance, tools, techniques, and documentation can be found at the Microsoft Technet website. The Windows Experience If you want a quick glance at how your system is performing compared to the expect- ations of Microsoft’s hardware performance indexes, you can run the Windows Expe- rience report. To launch the report, click Start, then select Control Panel→System and Mainte- nance→Performance Information and Tools. You will have to acknowledge the User Account Control (UAC) to continue. You can also access the System Health Report using the search feature on the Start menu. Click Start and enter “performance” in the search box, then click Performance Information and Tools. Click Advanced Tools and then click the link “Generate a sys- tem health report” at the bottom of the dialog. You will have to acknowledge the UAC to continue. Microsoft has changed the Windows Experience in Windows 7. The report is very similar to that of earlier Windows versions, but it supplies more information that you can use to judge the performance of your system. The report is run once after installation, but you can regenerate the report by clicking Update My Score. This report rates five areas of your system’s performance: processor (CPU), memory, video controller (graphics), video graphics accelerator (gaming graphics), and the pri- mary hard drive. Figure 7-24 shows an example of the Windows Experience report. Microsoft Windows Monitoring | 277 Please purchase PDF Split-Merge on to remove this watermark.
  2. Figure 7-24. The Windows Experience report There is a little-known feature of this report you may find valuable—click on the link “Learn how you can improve your computer’s performance” to get a list of best prac- tices for improving each of these scores. You should run this report and regenerate the metrics every time you change the configuration of your system. This will help you identify situations where configuration changes affect the performance of your server. The best use for this tool is to get a general impression of how your system is performing without analyzing a ton of metrics. A low score in any of the categories can indicate a performance issue. If you examine the report in Figure 7-24, for instance, you will see that the system has a very low graphics and gaming graphics score. This is not unex- pected for a Windows system running as a virtual machine or a headless server, but it might be alarming to someone who just shelled out several thousand dollars for a high- end gaming system. The System Health Report One of the unique features and diagnostic improvements in Windows Vista and Win- dows 7 is the ability to generate a report that takes a snapshot of all of the software, hardware, and performance metrics for your system. It is analogous to the System Pro- filer of Mac OS X, but also contains performance counters. To launch the System Health Report, click Start, then select Control Panel→System and Maintenance→Performance Information and Tools. Next, select Advanced Tools, then 278 | Chapter 7: Getting Started with Monitoring Please purchase PDF Split-Merge on to remove this watermark.
  3. click the link “Generate a system health report” at the bottom of the dialog. You will have to acknowledge the UAC to continue. You can also access the System Health Report using the search feature on the Start menu. Click Start and enter “performance” in the search box, then click Performance Information and Tools. Click Advanced Tools and select the link “Generate a system health report” at the bottom of the dialog. Another way to access the System Health Report is to use the search feature on the Start menu. Click Start and enter “system health report” in the search box, then click the link in the Start menu. You will have to acknowledge the UAC to continue. Figure 7-25 shows an example of the System Health Report. This report has everything—all of the hardware, software, and many other aspects of your system are documented here. Notice the report is divided into sections that you can expand or collapse for easier viewing. The following list briefly describes the in- formation displayed by each section: System Diagnostics Report The system name and the date the report was generated. Diagnostic Results Warning messages generated while the report was being run, identifying potential problem areas on your computer. Also included is a brief overview of the perform- ance of your system at the time the report was run. Software Configuration A list of all of the software installed on your system, including system security settings, system services, and startup programs. Hardware Configuration A list of the important metadata for disk, CPU performance counters, BIOS infor- mation, and devices. CPU A list of the processes running at report time and metadata about system compo- nents and services. Network Metadata about the network interfaces and protocols on your system. Disk Performance counters and metadata about all of the disk devices. Memory Performance counters for memory, including the process list and memory usage. Report Statistics General information about the system when the report was run, such as processor speed and the amount of memory installed. Microsoft Windows Monitoring | 279 Please purchase PDF Split-Merge on to remove this watermark.
  4. Figure 7-25. The System Health Report The System Health Report is your key to understanding how your system is configured and is performing at a glance. It is a static report, representing a snapshot of the system. There is a lot of detailed information in the Hardware Configuration, CPU, Network, Disk, and Memory sections. Feel free to explore those areas for greater details about your system. The best use of this tool, beyond examining the performance counters, is to save the report for later comparison to other reports when your system is performing poorly. You can save an HTML version of the report by selecting File→Save As. You can use the saved report as a baseline for performance of your system. If you generate the report several times over the course of low, medium, and high usage, you should be able to put together a general expectation for performance. These expecta- tions are important because you can use them to determine whether your performance issues are within the bounds of expectations. When a system enters a period of un- usually high load during a time when it is expected to have a low load, the users’ experience may generate complaints. If you have these reports to compare to, you can save yourself a lot of time investigating the exact source of the slowdown. 280 | Chapter 7: Getting Started with Monitoring Please purchase PDF Split-Merge on to remove this watermark.
  5. The Event Viewer The Windows Event Viewer shows all the messages logged for application, security, and system events. It is a great source of information about events that have occurred (or continue to occur) and should be one of the primary tools you use to diagnose and monitor your system. You can accomplish a great deal with the Event Viewer. For example, you can generate custom views of any of the logs, save the logs for later diagnosis, and set up alerts for specific events in the future. We will concentrate on viewing the logs. For more infor- mation about the Event Viewer and how you can set up custom reports and subscribe to events, consult your Windows help files. To launch the Event Viewer, click the Start button, then right-click Computer and choose Manage. You will have to acknowledge the UAC to continue. You can then click the Event Viewer link in the left panel. You can also launch the Event Viewer by clicking Start, typing “event viewer,” and pressing Enter. The dialog has three panes by default. The left pane is a tree view of the custom views, logfiles, and applications and services logs. The logs are displayed in the center pane, and the right pane contains the Action menu items. The log entries are sorted, by de- fault, in descending order by date and time. This allows you to see the most recent messages first. You can customize the Event Viewer views however you like. You can even group and sort events by clicking on the columns in the log header. Open the tree for the Windows logs to see the base logfiles for the applications, security, and system (among others). Figure 7-26 shows the Event Viewer open and the log tree expanded. The logs available to view and search include: Application All messages generated from user applications as well as operating system services. This is a good place to look when diagnosing problems with applications. Security Messages related to access and privileges exercised, as well as failed attempts to access any secure object. This can be a good place to look for application failures related to username and password issues. Setup Messages related to application installation. This is the best place to look for in- formation about failures to install or remove software. Microsoft Windows Monitoring | 281 Please purchase PDF Split-Merge on to remove this watermark.
  6. Figure 7-26. The Windows Event Viewer System Messages about device drivers and Windows components. This can be the most useful set of logs for diagnosing problems with devices or the system as a whole. It contains information about all manner of devices running at the system level. Forwarded Events Messages forwarded from other computers. Consult the Windows documentation about working with remote event logging. Digging through these logs can be challenging, because many of them display infor- mation that is interesting to developers and not readable by mere mortals. To make things easier, you can search any of the logs by clicking the Find operation in the Actions pane and entering a text string. For example, if you are concerned about memory issues, you can enter “memory” to filter all of the log entries for ones containing the string “memory,” which will then be shown in the center pane. Each log message falls into one of the following three categories. These apply to user processes, system components, and applications alike. Error Indicates a failure of some magnitude, such as a failed process, out-of-memory problem, or system fault. 282 | Chapter 7: Getting Started with Monitoring Please purchase PDF Split-Merge on to remove this watermark.
  7. Warning Indicates a less serious condition or event of note, such as low memory or low disk space. Information Conveys data about an event. This is generally not a problem, but it could provide additional information when diagnosing problems, such as when a USB drive was removed. To view a log, open the corresponding tree in the left pane. To view the details about any message, click on the message. The message will be displayed below the log entries, as shown in Figure 7-26. In the lower part of the center pane, you can click the General tab to see general information about the message, such as the statement logged, when it occurred, what log it is in, and the user who was running the process or application. You can click the Details tab to see a report of the data logged. You can view the information as text (Friendly View) or XML (XML View). You can also save the infor- mation for later review; the XML View is useful to pass the report to tools that recognize the format. The Reliability Monitor The most interesting monitoring tool in Windows is the Reliability Monitor. This is a specialized tool that plots the significant performance and error events that have oc- curred over time in a graph. A vertical bar represents each day over a period of time. The horizontal bar is an ag- gregate of the performance index for that day. If there are errors or other significant events, you will see a red X on the graph. Below the bar is a set of drop-down lists that contain the software installations and removals, any application failures, hardware failures, Windows failures, and any additional failures. This tool is great for checking the performance of the system over a period of time. It can help diagnose situations when an application or system service has performed cor- rectly in the past but has started performing poorly, or when a system starts generating error messages. The tool can help locate the day the event first turned up, as well as give you an idea of how the system was performing when it was running well. Another advantage of this tool is that it gives you a set of daily baselines of your system over time. This can help you diagnose problems related to changing device drivers (one of the banes of Windows administration), which could go unnoticed until the system degrades significantly. In short, the Reliability Monitor gives you the opportunity to go back in time and see how your system was performing. The best part of all? You don’t have to turn it on— it runs automatically, gleaning much of its data from the logs, and therefore automat- ically knowing your system’s history. Microsoft Windows Monitoring | 283 Please purchase PDF Split-Merge on to remove this watermark.
  8. One big source of problems on Windows is connecting and configuring hardware. We will not discuss this subject here, as it can easily fill a book in its own right. If you have problems with hardware and drivers, one excellent reference is Microsoft Windows XP Inside Out, by Ed Bott et al. (Microsoft Press). You can access the Reliability Monitor by clicking Start, typing “reliability,” and press- ing Enter or clicking on Reliability and Performance Monitor. You will have to ac- knowledge the UAC. Click Reliability Monitor in the tree pane on the left. Fig- ure 7-27 shows an example of the Reliability Monitor. In Windows 7, you can launch the Reliability Monitor by clicking Start, entering “action center” in the search box, and pressing Enter. You can then select Maintenance→View reliability report. The report differs from previous versions of Windows, but offers the same information in a tidier package. For example, instead of the drop-down lists, the new Reliability Monitor report lists known incidents in a single list. Figure 7-27. The Reliability Monitor 284 | Chapter 7: Getting Started with Monitoring Please purchase PDF Split-Merge on to remove this watermark.
  9. The Task Manager The Windows Task Manager displays a dynamic list of running processes. It has been around for a long time and has been improved over various versions of Windows. The Task Manager offers a tabbed dialog with displays for running applications, pro- cesses (this is most similar to the Linux top command), services active on the system, a CPU performance meter, a network performance meter, and a list of users. Unlike some other reports, this tool generates its data dynamically, refreshing periodically. This makes the tool a bit more useful in observing the system during periods of low performance. The reports display the same information as the System Health Report, but in a much more compact form, and are updated continuously. You can find all of the critical metrics needed to diagnose performance issues with CPU, resource-hogging processes, memory, and the network. Conspicuously missing is a report on disk performance. One of the interesting features of the Task Manager is that it shows a miniature per- formance meter in the notification area on the Start bar that gives you a chance to watch for peaks in usage. Running a dynamic performance monitoring tool consumes resources and can affect a system that already suffers poor performance. You can launch the Task Manager any time by pressing Ctrl+Alt+Del and choosing Task Manager from the menu. Figure 7-28 shows an example of the Task Manager and the process list. The Performance Monitor The Performance Monitor is the premier tool for tracking performance in a Windows system. It allows you to select key metrics and plot their values over time. It can also store the session so you can later review it and create a baseline for your system. The Performance Monitor has metrics for just about everything in your system. There are counters for many of the smaller details having to do with the basic areas of per- formance: CPU, memory, disk, and network. There are a great many other categories as well. To launch the Performance Monitor, click Start, then select Control Panel→System and Maintenance→Performance Information and Tools. Click Advanced Tools and then click the link Open Reliability and Performance Monitor near the middle of the dialog. You will have to acknowledge the UAC to continue. Click Reliability Monitor in the tree pane on the left to access the Performance Monitor feature. Microsoft Windows Monitoring | 285 Please purchase PDF Split-Merge on to remove this watermark.
  10. Figure 7-28. The Task Manager You can also launch the Performance Monitor by clicking Start, typing “reliability,” and pressing Enter or clicking on Reliability and Performance Monitor. You will have to acknowledge the UAC. Click Reliability Monitor in the tree pane on the left to access the Performance Monitor feature. Figure 7-29 shows an example of the Performance Monitor. Microsoft has two levels of metrics: objects that offer a high-level view of an area such as the processor or memory, and counters that represent a specific detail of the system. Thus, you can monitor the CPU’s performance as a whole or watch the finer details, such as percentage of time idle or the number of user processes running. Add these objects or counters to the main chart by clicking the green plus sign on the toolbar. This opens a dialog that allows you to choose from a long list of items to add to the chart. Adding the items is a simple matter of selecting the object and expanding the drop-down list on the left, then dragging the desired object to the list on the right. 286 | Chapter 7: Getting Started with Monitoring Please purchase PDF Split-Merge on to remove this watermark.
  11. Figure 7-29. The Performance Monitor You can add as many items as you want; the chart will change its axis accordingly. If you add too many items to track or the values are too diverse, however, the chart may become unreliable. It is best to stick to a few related items at a time (such as only memory counters) to give you the best and most meaningful chart. A full description of the features of the Performance Monitor is well beyond the scope of this chapter. We encourage you to investigate additional features such as Data Col- lector Sets and changing the chart’s display characteristics. There are many excellent texts that describe these features and more in great detail. The versatility of the Performance Monitor makes it the best choice for forming base- lines and recording the behavior of the system over time. You can use it as a real-time diagnostic tool. Microsoft Windows Monitoring | 287 Please purchase PDF Split-Merge on to remove this watermark.
  12. If you have used the Reliability or Performance Monitor, you may have noticed a seldom-commented-on feature called the Resource Overview. This is the default view of the Reliability and Performance Monitor. It provides four dynamic performance graphs for CPU, disk, network, and memory. Below the graphs are drop-down detail panes containing in- formation about these areas. This report is an expanded form of the Task Manager performance graphs and provides yet another point of reference for performance monitoring and diagnosis on Microsoft Windows. This brief introduction to monitoring performance on Microsoft Windows should per- suade you that the belief that Microsoft’s Windows platform is difficult to monitor and lacks sophisticated tools is a myth. The tools are very extensive (some could argue too much so) and provide a variety of views of your system’s data. Monitoring as Preventive Maintenance The techniques discussed so far give you a snapshot of the status of the system. How- ever, most would agree that monitoring is normally an automated task that samples the available statistics for anomalies. When an anomaly is found, an alert is sent to an administrator (or group of administrators) to let someone know there may be a prob- lem. This turns the reactive task of checking the system status into a proactive task. A number of third-party utilities combine monitoring, reporting, and alerts into easy- to-use interfaces. There are even monitoring and alert systems for an entire infrastruc- ture. For example, Nagios can monitor an entire IT infrastructure and set up alerts for anomalies. There are also monitoring and alert systems available either as part of or an add-on for operating systems and database systems. We will examine the Enterprise Monitor for MySQL in Chapter 13. Conclusion There are a great many references on both performance tuning and security monitoring. This chapter provides a general introduction to system monitoring. While it is not comprehensive, the material presented is an introduction to the tools, techniques, and concepts of monitoring your operating system and server performance. In the next chapter, we will take on the task of monitoring a MySQL system and discuss some common practices to help keep your MySQL system running at peak performance. 288 | Chapter 7: Getting Started with Monitoring Please purchase PDF Split-Merge on to remove this watermark.
  13. “Joel!” He knew that voice and that tone. Joel’s boss was headed his way and about to conduct another drive-by tasking. He turned to face his office door as his boss stepped through it. “Did you read Sally’s email about the slowdown?” Joel recalled that Sally was one of the employees who sent him a message asking why her application was running slowly. He had just finished checking the low-hanging fruit—there was plenty of memory and disk space wasn’t an issue. “Yes, I was just looking into the problem now.” “Make it your top priority. Marketing has a deadline to produce their quarterly sales projections. Let me know what you find.” His boss nodded once and stepped away. Joel sighed and returned to examining the reports on CPU usage while wondering how to describe technology to the nontechnical. Conclusion | 289 Please purchase PDF Split-Merge on to remove this watermark.
  14. Please purchase PDF Split-Merge on to remove this watermark.
  15. CHAPTER 8 Monitoring MySQL Joel had a feeling today was going to be a better day. Everything was going well: the performance measurements for the servers were looking good and the user complaints were down. He had successfully reconfigured the server and improved performance greatly. There was only one application still performing poorly, but he was sure the problem wasn’t hardware- or operating-system-related; more likely it was an issue with a poorly written query. Nevertheless, he had sent his boss an email message explaining his findings and that he was working on the remaining problems. Joel heard quickened footsteps approaching his office. He instinctively looked toward his door, awaiting the now-routine appearance of his boss. He was shocked as Mr. Summerson zipped by without so much as a nod in his direction. He shrugged his shoulders and returned to reading his email messages. Just then a new message appeared with “HIGH PRIORITY” in the subject line in capital letters. It was from his boss. Chiding himself for holding his breath, Joel relaxed and opened the message. He could hear his boss’s voice in his mind as he read through the message. “Joel, good work on those reports. I especially like the details you included about memory and disk performance. I’d like you to generate a similar report about the da- tabase server. I’d also like you to look into a problem one of the developers is having with a query. Susan will send you the details.” With a deep sigh, Joel once again opened his favorite MySQL book to learn more about monitoring the database system. “I hope it has something about drilling down into individual components,” he mumbled, knowing he needed to get up to speed quickly on an advanced feature of MySQL. Now that you understand how monitoring works and how to keep your host’s oper- ating systems at peak efficiency, how do you know if your MySQL servers are per- forming at their peak efficiency? Better still, how do you know when they aren’t? In this chapter, we begin with a look at monitoring MySQL, and then move on to monitoring and improving performance in your databases. We conclude with a look into best practices for improving database performance. 291 Please purchase PDF Split-Merge on to remove this watermark.
  16. What Is Performance? Before we begin discussions about database performance and general best practices for monitoring and tuning a MySQL server, it is important to define what we mean by performance. For the purposes of this chapter, good performance is defined as meeting the needs of the user such that the system performs as expediently as the user expects, whereas poor performance is defined as anything less. Typically, good performance means response time and throughput meet the users’ expectations. While this may not seem very scientific, savvy administrators know the best gauge of how well things are going is the happiness of the users. That doesn’t mean we don’t measure performance. On the contrary, we can and must measure performance in order to know what to fix, when, and how. Furthermore, if you measure performance regularly, you can even predict when your users will begin to be unhappy. Your users won’t care if you reduce your cache hit rate by 3 percent, beating your best score to date. You may take pride in such things, but metrics and numbers are meaningless when compared to the user’s experience at the keyboard. There is a very important philosophy that you should adopt when dealing with per- formance. Essentially, you should never adjust the parameters of your server, database, or storage engine unless you have a deliberate plan and a full understanding of the expectations of the change as well as the consequences. More importantly, never adjust without measuring the effects of the change over time. It is entirely possible that you can improve the performance of the server in the short run but negatively impact per- formance in the long run. Finally, you should always consult references from several sources, including the reference manuals. Now that we’ve issued that stern warning, let’s turn our attention to monitoring and improving performance of the MySQL server and databases. MySQL Server Monitoring Managing the MySQL server falls in the category of application monitoring. This is because most of the performance parameters are generated by the MySQL software and are not part of the host operating system. As mentioned previously, you should always monitor your base operating system in tandem with monitoring MySQL because MySQL is very sensitive to performance issues of the host operating system. There is an entire chapter in the online MySQL Reference Manual that covers all aspects of monitoring and performance improvement, intriguingly titled “Optimization.” See for more details. Rather than repeat the facts and rhetoric of that excellent reference, we will discuss a general ap- proach to monitoring the MySQL server and examine the various tools available. This section is an introduction to the finer details of monitoring the MySQL server. We'll start with a short discussion of how to change and monitor the behavior of the 292 | Chapter 8: Monitoring MySQL Please purchase PDF Split-Merge on to remove this watermark.
  17. system, then discuss monitoring primarily for the purposes of diagnosing performance issues and forming a performance benchmark. We will also discuss best practices for diagnosing performance issues and take a look at monitoring the storage engine sub- layer in MySQL—an area not well understood or covered by other reference sources. How MySQL Communicates Performance There are two mechanisms you can use to govern and monitor behavior in the MySQL server. You use server variables to control behavior and status variables to read behavior configuration and statistical information regarding features and performance. There are many variables you can use to configure the server. Some can be set only at startup (called startup options, which can also be set in option files). Others can be set at the global level (across all connections), the session level (for a single connection), or both the global and session levels. Session variable settings are not persistent beyond the current connec- tion and are reset when the connection is closed. You can read server variables using the following commands: SHOW [GLOBAL | SESSION] VARIABLES; You can change those variables that are not static (read-only) using the following com- mands (you can include multiple settings on a single line using a comma separator): SET [GLOBAL | SESSION] = ; SET [@@global. | @@session. | @@] = ; You can read status variables using the following commands. The first two commands display the value of all local or session scope variables (the default is session). The third command displays those variables that are global in scope. SHOW STATUS; SHOW SESSION STATUS; SHOW GLOBAL STATUS; We discuss how and when to use these commands in the next section. Performance Monitoring Performance monitoring in MySQL is the application of the previous commands— specifically, setting and reading system variables and reading status variables. The SHOW and SET commands are only two of the possible tools you can use to accomplish the task of monitoring the MySQL server. MySQL Server Monitoring | 293 Please purchase PDF Split-Merge on to remove this watermark.
  18. Indeed, there are several tools you can use to monitor your MySQL server. The tools available in the standard distributions are somewhat limited in that they are console tools and include special commands you can execute from a MySQL client (e.g., SHOW STATUS) and utilities you can run from a command line (e.g., mysqladmin). The MySQL client tool is sometimes called the MySQL monitor, but should not be confused with a monitoring tool. There are also GUI tools available that make things a little easier if you prefer or require such options. You can also download the MySQL GUI tools, which include advanced tools that you can use to monitor your system, manage queries, and migrate your data from other database systems. We begin by examining how to use the SQL commands and then discuss the MySQL Administrator GUI and Query Browser tools. We also take a look at one of the most overlooked tools available to the administrator—the server logs. Some savvy administrators may consider the server logs the first and primary tool for administering the server. Although they are not nearly as vital for performance moni- toring, they can be an important asset in diagnosing performance issues. SQL Commands All of the SQL monitoring commands are a variant of the SHOW command, which dis- plays internal information about the system and its subsystems. While there are many forms of the SHOW command, the following lists the most common SQL commands you can use to monitor the MySQL server: SHOW INDEX FROM Displays the index cardinality statistics for the specified table, which are used by the optimizer to estimate join selectivity. This command can also be very helpful when diagnosing poorly performing queries, specifically whether the query is for- mulated in such a way as to make use of the indexes available. SHOW PLUGINS Displays the list of all known plug-ins. It shows the name of the plug-in and its current status. The storage engines in newer releases of MySQL are implemented as plug-ins. Use this command to get a snapshot of the currently available plug-ins and their statuses. SHOW [FULL] PROCESSLIST Displays data for all threads (including connections) running on the system. This command resembles the process commands of the host operating system. The in- formation displayed includes connection data along with the command executing, how long it has been executing, and its current state. Like the operating system 294 | Chapter 8: Monitoring MySQL Please purchase PDF Split-Merge on to remove this watermark.
  19. command it resembles, it can diagnose poor response (too many threads), a zombie process (long running or nonresponding), or even connection issues. When dealing with poor performance or unresponsive threads, use the KILL command to termi- nate them. The default behavior is to show the processes for the current user. The FULL keyword displays all processes. You must have the global SUPER privilege to see all processes run- ning on the system. SHOW [GLOBAL | SESSION] STATUS Displays the values of all of the system variables. You will probably use this com- mand more frequently than any other. Use this command to read all of the statis- tical information available on the server. Combined with the GLOBAL or SESSION keyword, you can limit the display to those statistics that are global- or session- only. SHOW TABLE [FROM ] STATUS Displays detailed information about the tables in a given database. This includes the storage engine, collation, creation data, index data, and row statistics. You can use this command along with the SHOW INDEX command to examine tables when diagnosing poorly performing queries. SHOW [GLOBAL | SESSION] VARIABLES Displays the system variables. These are typically configuration options for the server and while they do not display statistical information, viewing the variables can be very important when determining whether the current configuration has changed or if certain options are set. Some variables are read-only and can only be changed via the configuration file or the command line on startup, while others can be changed globally or set locally. You can combine this command with the GLOBAL or SESSION keyword to limit the display to those variables that are global- or session-only. Limit the Output of SHOW Commands The SHOW commands in MySQL are very powerful. However, they often display too much information. This is especially true for the SHOW STATUS and SHOW VARIABLES commands. To see less information, you can use the LIKE clause, which permits you to view only those rows matching the pattern specified. The most common example is using the LIKE clause to see only variables for a certain subset, such as replication or logging. You can use the standard MySQL pattern symbols and controls in the LIKE clause in the same manner as a SELECT query. MySQL Server Monitoring | 295 Please purchase PDF Split-Merge on to remove this watermark.
  20. For example, the following displays the status variables that include the name “log”: mysql> SHOW SESSION STATUS LIKE '%log%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Com_binlog | 0 | | Com_purge_bup_log | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_engine_logs | 0 | | Com_show_relaylog_events | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | +--------------------------+-------+ 11 rows in set (0.11 sec) The commands specifically related to storage engines include the following: SHOW ENGINE LOGS Displays the log information for the specified storage engine. The information dis- played is dependent on the storage engine. This can be very helpful in tuning stor- age engines. Some storage engines do not provide this information. SHOW ENGINE STATUS Displays the status information for the specified storage engine. The information displayed depends on the storage engine. Some storage engines display more or less information than others. For example, the InnoDB storage engine displays dozens of status variables, while the NDB storage engine shows a few, and the MyISAM storage engine displays no information. This command is the primary mechanism for viewing statistical information about a given storage engine and can be vital for tuning certain storage engines (e.g., InnoDB). The older synonyms for the SHOW ENGINE commands (SHOW LOGS and SHOW STATUS) have been deprecated. SHOW ENGINES Displays a list of all known storage engines for the MySQL release and their statuses (i.e., whether the storage engine is enabled). This can be helpful when deciding which storage engine to use for a given database or in replication to determine if the same storage engines exist on both the master and the slave. 296 | Chapter 8: Monitoring MySQL Please purchase PDF Split-Merge on to remove this watermark.
Đồng bộ tài khoản