 Risorse Disponibili: |
| Pagina Prodotto >> | Download Adesso >> | |
| Guida Prodotto >> | Acquista Adesso >> |
| Ulteriori Risorse:: |
| Contatta il Supporto >> | Contatta un Adetto alle Vendite >> |
HoneyMonitor's SQL-based Monitoring System FAQ |
 |
The following FAQs answer some of the most common questions about the HoneyMonitor's SQL-based Monitoring System.
Should you have any other question, do not hesitate to contact us at support@honeysoftware.com.
Monitoring System
1. What type of MySQL™ Server can I monitor with your software?
2. What versions of MySQL™ Server can I monitor with your software?
3. What kind of monitoring system does your software implement?
4. What is an SQL-based monitoring system?
5. How does your SQL-based monitoring system work?
6. Does your monitoring system work on real time?
7. What does your monitoring system monitor?
8. What kind of output does your monitoring system produce?
9. How do I use your software to monitor my MySQL™ Servers?
10. Can I create a custom Performance Report?
11. Does your monitoring system send me an email if something is going wrong with a specific variable or ratio?
12. How do I install your SQL-based monitoring system?
13. Is your monitoring system compatible with MySQL™ Replication?
14. Is your monitoring system compatible with MySQL™ Cluster?
15. My MySQL™ Server is running in a virtualized environment. Any issue?
16. I am running more than one MySQL™ Server on the same machine. Any issue?
17. Can I use your SQL-based monitoring system on a sandbox-ed Server or a sandbox-ed Cluster?
18. How do I check if my monitoring system is working?
19. I checked the installation of the monitoring system, all tests passed but no monitoring data is being stored. What should I do?
20. I got a thread stack size error. What should I do?
21. Can I change the frequency of the data acquisition of your monitoring system?
22. Can I pause the data acquisition of your monitoring system?
23. How much load does your SQL-based monitoring system add to the MySQL™ Server?
24. How much disk space does your monitor system require?
25. How can I see how much disk space is the monitoring system using on my Server?
26. Can I delete or purge old monitoring data?
27. How do I uninstall your SQL-based monitoring system?
1. What type of MySQL™ Servers can I monitor with your software?
You can use our software to monitor all kind of server environments: single servers, master servers, slaves servers and cluster SQL nodes.
Our monitoring system is independent from the platform your MySQL™ Server is running on: you can monitor MySQL™ whether it is running on Linux, (Open)Solaris™, Windows™ or Mac™ OS.
Please take a look at the figure below for further details.
2. What versions of MySQL™ Server can I monitor with your software?
In general you can use our software to monitor any version of MySQL™ beginning with version 5.1.x. From the Cluster side, any Cluster version from 6.2 is supported, included versions 6.3 and 7.
Please note that versions above are those required by the monitoring system. All other features of HoneyMonitor (like server administration, database administration or code development) are compatible with all MySQL™ versions beginning with 3.23.
To learn more about the MySQL™ versions supported by the monitoring system, please see the section "Supported MySQL™ Versions" of the HoneyMonitor's Reference Manual, or feel free to contact us for specific inquiries on supported versions.
Please take a look at the figures below for further details.
 |
 |
3. What kind of monitoring system does your software implement?
HoneyMonitor implements an SQL-based monitoring system.
4. What is an SQL-based monitoring system?
There are different ways to monitor a MySQL™ server.
In the SQL-based approach, all the job is done by a set of stored functions, stored procedures, scheduled events and triggers which store the monitoring data on a standard table (e.g. MyISAM or InnoDB) on your Server.
The monitoring system is installed on each of your MySQL™ Servers from a remote Windows™ client (HoneyMonitor) and the installation process as well as the whole monitoring system do not require Agents running on your Servers nor Web Servers or external repository databases.
An SQL-based monitoring system runs, in fact, from within your MySQL™ Server, with no need of any additional layer between your Server and the HoneyMonitor client. As a result, the installation / maintenance process of the monitoring system is highly simplified: no need of installing and updating any other software than the MySQL™ Server itself.
An additional advantage of using such an SQL-based monitoring system is that all the monitoring data (e.g. the data used by HoneyMonitor to show you the time trend of the performance metrics) reside on your Server, hence you can see, select over or delete them as well as do whatever kind of custom action you prefer, whenever you want.
We will refer to the monitoring system implemented by HoneyMonitor as "SQL-based monitoring system" or simply "monitoring system" or "audit system".
Please take a look at the figure below for further details.
5. How does your SQL-based monitoring system work?
Our SQL-based monitoring system works by reading and analyzing from within your MySQL™ Server a set of values which can be very useful to determine the health of your MySQL™ Server. Once they are calculated, monitoring data are hence stored in your MySQL™ Server.
At a recurring interval of time - whose length can be customized by you in accordance to your needs - a scheduled event reads the values of the status variables of your MySQL™ Server, as well as those of the system variables, and, starting from these values, it calculates several kinds of performance ratios. When all performance metrics have been calculated, the scheduled event stores their values, as well as the raw values of MySQL™ status and system variables, in a standard MySQL™ table (status_system_dpm_table) which is included in the audit database (hs_audit_schema), installed on your MySQL™ Server by the SQL-based monitoring system.
An advantage of storing in the table not only the derived metrics but also the raw data, is the added possibility, if you need, to check the "configuration" of your Server for a given set of performance metrics. This can be very useful if you want to identify a performance problem or simply go back to a past "configuration" of your Server. Furthermore, having all the raw data stored on your Server gives you the possibility to "cut" specific set of information you could be interested in, or calculate different ratios than those calculated and stored by the monitoring system itself.
All these activities are performed by the Server at Server side and are completely "transparent" to you.
Once you connect to your MySQL™ Server using the HoneyMonitor client, you can take advantage of its intuitive GUI to inspect the monitoring data, for instance by creating Performance Reports which can include one or more charts correlated to one of more variables or performance ratios. This gives you the possibility to see how a particular metric is changing over time, for example in response of a particular action which has been performed on your Server (or a particular 3°-party application which started using your Server).
Furthermore, you can use the HoneyMonitor GUI to customize the properties and options of the monitoring system as well as having "aggregate views" of several properties or set of variables of your server. In this context, the "Server Properties" Window, the "Replication Monitor" and the "Cluster Monitor", which are included in HoneyMonitor, can help you too.
At any time you can change the time interval for the data acquisition or pausing the audit Event so that no other data will be stored until you will enabled the audit Event once again.
As a "Populating" Event takes care of storing monitoring data on your Server, so a "Pruning" Event takes care of deleting old monitoring data. You can set the interval for data pruning or simply choose not to prune any data, using the intuitive HoneyMonitor's interface.
You can also see some statistics on your audit database, for instance the number of records contained in the audit table or the total amount of disk space used by this table.
Please take a look at the figure below for further details.
6. Does your monitoring system work on real time?
Well, it depends on what we mean for real time.
Monitoring data are collected and stored on your server at a fixed interval of time, say every 1 minute or every 5 minutes (you can customize this frequency). Once some monitoring have been stored, you can execute a SELECT query over them to see how changed a specific variable or performance ratio in the past (say in the past 2 hours or 2 months). This is what happen when you use one of the built-in performance reports included in HoneyMonitor (or use your own customized report).
If you want to have just a look at the variables right now, you probably would open the "Performance Tuning Monitor" (and hit the "Refresh button" whenever you want) rather than create a performance report. When you click the "Refresh" button of the "Performance Tuning Monitor", all the variables and performance ratios are calculated on that instant of time (hence they are not read from the audit table via a SELECT statement but, actually, calculated).
We have couple of feature requests opened on which we are working on to improve your experience in the visualization of monitoring data. Stay tuned donc, and please feel free to share your thoughts on how we can improve our software: we will be happy to hear from you!
Please take a look at the figure below for further details, and, to learn more on the "Performance Tuning Monitor", please see the section "Performance Tuning Monitor" of HoneyMonitor's Reference Manual.
7. What does your monitoring system monitor?
HoneyMonitor is very extensive in what it monitors.
First of all it allows you to monitor a set of general information about the server, included (but not limited) to all kinds of queries per second ratios, table cache, thread cache, connections, temporary tables and query cache.
Furthermore, HoneyMonitor allows you to monitor specific variables of the most common table engines like MyISAM Key Buffers or InnoDB Buffer Pools. Functionalities for the monitoring of the Maria and Falcon engines are included too, even if they are under rapid evolution, since this engine didn't reach the GA stage yet.
Support for other storage engines is planned for future versions of HoneyMonitor. Please feel free to send us the list of engines you use the most so that we can priorize our future requests and add support for your preferred storage engines soon.
No query analysis and inspection features are included at the moment; we plan to add them in future versions of our software.
For further information, please take a look at the list of all variables and ratios monitored. For each of those you can create a customized performance report which better fits your needs (and of course you can even preview the standard built-in reports included in HoneyMonitor).
Further details and screenshots are included in the "Complete List of Audit Reports" section of the HoneyMonitor's Reference Manual.
8. What kind of output does your monitoring system produce?
The output of our SQL-based monitoring system is a standard MySQL™ table which includes some monitoring data, calculated by a set of stored functions, and stored in that table by a scheduled event, at a recurring time interval.
HoneyMonitor, then, provides you an intuitive GUI for accessing and visualizing these data, plus more other functionalities. Please see the next FAQ for further information on how to use HoneyMonitor in order to monitor your MySQL™ Servers.
9. How do I use your software to monitor my MySQL™ Servers?
With HoneyMonitor, you can monitor different aspects of your MySQL™ Servers.
As per Performance Monitoring, the included "Performance Tuning Monitor" allows you to see and, when possible, edit the values of hundreds of system variables, status variables and performance ratios. The "Performance Tuning Monitor" is organized in several tabs, thus helping you maintain a logic vision of all the server's aspects which can be monitored. Furthermore, each tab highlights, and allows you to edit, the key variables on which you can leverage to improve the performance ratios related to the specific aspect treated by that tab. Visual alerts, then, help you identify most common performance problems (especially for storage engines).
Once you install on your MySQL™ Server, via our Client HoneyMonitor, with no actions required on your server machine, our SQL-based monitoring system, using the "Performance Tuning Monitor" or the top menus of our software, you can also create several Performance Reports which contain the time trend charts of the most important performance metrics, having in this way the possibility to see how these variables and ratios are changing over time. 90+ built-in performance charts are already included in HoneyMonitor, and you have also the possibility to create your own Performance Reports.
Plase use the top menu "Auditing" for any action you would perform on the SQL-based monitoring system - included installation, uninstallation and report creation.
Furthermore, through the "Replication Monitor" and the "Cluster Monitor", our software gives you the possibility to administer and monitor the status of your MySQL™ Replication and Cluster in a very fast and intuitive way.
For further information on our monitoring system, please see the other FAQs of this category. To learn more on the "Performance Tuning Monitor", please see the section "Performance Tuning Monitor" of the HoneyMonitor's Reference Manual.
For further information on the Replication and Cluster features included in HoneyMonitor, please visit the parts "Replication Administration" and "Cluster Administration" of the HoneyMonitor's Reference Manual.
10. Can I create a custom Performance Report?
Yes, sure.
Please use the Auditing / Reports / Custom Report menu. You can also edit the templates of the built-in Performance Reports.
Further information on how to create a customized Performance Report can be found on this article: "Step by Step Guide on How to Create a Customized Performance Report using HoneyMonitor".
11. Does your monitoring system send me an email if something is going wrong with a specific variable or ratio?
Sorry but no.
At this moment we do not implement this features. We plan to add it in future versions of our software.
12. How do I install your SQL-based monitoring system?
Installing our SQL-based monitoring system is very simple process and requires only 1 or 2 minutes.
Installation process is the same for all kind of servers (single servers, masters, slaves, cluster SQL nodes) and it is performed client side, via HoneyMonitor, using the "Audit Installation Wizard". No specific actions are required on your server machines since our SQL-based monitoring system requires the installation of just a database (hs_audit_schema).
When installing the monitoring system on master/slave environments or cluster environments you can decide to install it in all nodes of your environment or only in some specific nodes.
Monitoring data are of course machine specific, so if you install our monitoring system on a master or on a cluster SQL node, data will not be "replicated" or "copied" into the slaves or in the other nodes.
To learn more on the installation process, please see the section "Audit Installation Wizard" of the HoneyMonitor's Reference Manual.
13. Is your monitoring system compatible with MySQL™ Replication?
Yes, sure.
All the queries executed by HoneyMonitor to set-up, configure or delete the SQL-based monitoring system will not be replicated on your slaves since HoneyMonitor (as well as the monitoring system itself) executes always a "SET @@session.`sql_log_bin` = 0;" query before executing any other query correlated with the monitoring system.
All monitoring data are server specific. You can install the SQL-based monitoring system on a master and on one or all slaves of your replication environment: simply install the monitoring system on each slave you want to monitor.
More details, if any, can be found in the section "Replication Issues" of the HoneyMonitor's Reference Manual.
14. Is your monitoring system compatible with MySQL™ Cluster?
Yes, sure.
When you install the monitoring system on a SQL node, using the "Audit Installation Wizard", please make sure you select for the audit table the MyISAM or InnoDB storage engine. Of course storage engine of the audit table should be different than NDBCLUSTER because all monitoring data are node-specific and must reside outside the cluster.
You can decide to monitor one or more SQL nodes: simply install the monitoring system on each node you want to monitor.
More details, if any, can be found in the section "Cluster Issues" of the HoneyMonitor's Reference Manual.
15. My MySQL™ Server is running in a virtualized environment. Any issue?
No, absolutely.
It is all transparent. To install / configure / use / uninstall our SQL-based monitoring system, you can follow exactly all the same steps you would follow with a standard, local or remote, MySQL™ Server.
16. I am running more than one MySQL™ Server on the same machine. Any issue?
No, absolutely.
You can install our SQL-based monitoring system on one or more MySQL™ servers running on the same machine. The monitoring systems of the different servers will not interact each other just because they are SQL-based, running from within the MySQL™ Servers themself.
17. Can I use your SQL-based monitoring system on a sandbox-ed Server or a sandbox-ed Cluster?
Yes, sure.
There is absolutely no difference from this point of view between a sandbox-ed server (or sandbox-ed cluster) and a standard server (or a standard cluster).
18. How do I check if my monitoring system is working?
Please open the "Check Audit Installation" Window by clicking on the Auditing / Check Audit Installation menu. Some status icons will alert you if some tests did not passed and you will have the possibility to re-install some of the SQL-based monitoring system's components to fix the issues, if any.
19. I checked the installation of the monitoring system, all tests passed but no monitoring data is being stored. What should I do?
If the "Check Audit Installation" Window tells you all the tests passed but no audit data is being stored, there should be a problem with the execution of the scheduled event or with the insertion of the audit data in the audit table.
Please check the error log of your Server and do contact us. We will be happy to understand what is going wrong on your Server and help you fix the problem.
20. I got a thread stack size error. What should I do?
If your monitoring system is correctly installed, all post-installation tests passed but no data is being stored on the audit table and the error log of the Server reports a "thread stack size" error during the execution of the "Populating" event of our monitoring system, then an increase of the thread_stack variable of your server would be required.
Unfortunately this require a server restart. To change the value of the thread_stack variable, please change the configuration file of your server:
# The stack size of each thread (default: 192K)
thread_stack=256K
or start the server specifying a bigger stack at command line level, like in the following example:
mysqld -O thread_stack=256K
Please note that this is a general issue of MySQL™ and it could be caused by the stored functions included in our SQL-based monitoring system like by any other stored procedure or function you may have. Our SQL-based monitoring system, in general, does not require any server restart.
21. Can I change the frequency of the data acquisition of your monitoring system?
Yes, sure.
Please use the "Audit Options" Window. To open it, click on the Auditing / Audit Options menu.
22. Can I pause the data acquisition of your monitoring system?
Yes, sure.
Once you install the monitoring system, you can enable or pause data acquisition at any time.
To learn more on how to enable the data acqusition of the monitoring system, please see the "Enabling Audit" section of the HoneyMonitor's Reference Manual, while to learn more on how to pause the data acquisition of the monitoring system, please see the section "Pausing Audit ".
23. How much load does your SQL-based monitoring system add to the MySQL™ Server?
Monitoring systems necessarily introduce some overhead. We performed some benchmarks using the free tool sysbench (http://sysbench.sourceforge.net/) on different kind of environments (single servers, master servers, slaves servers, cluster SQL nodes) and different scenarios (read-only, read-write, increasing number of threads).
The result of our analysis on our systems can be summarized as follow:
- on read-only systems (slaves), overhead introduced by our monitoring system has a mean value of 1.5%
- on read-write systems (masters) overhead has a mean value of 0% (it means that in some cases, servers with the audit system installed outperformed)
- on read-write cluster systems (2 nodes, monitoring system installed on all nodes), overhead has a mean value of 1.5%
It should be noted that these benchmarks have been performed on a standard 64-bit linux machine where all servers were sandbox-ed servers (we used the MySQL™ Sandbox and the Cluster Sandbox). We used MySQL™ version 5.1.35 for the master / slave environment and MySQL™ Cluster version 7.0.6 for cluster environment.
Results on "real", servers can be a bit different. We invite you to perform some benchmarks and share the result with us.
An important point it should be pointed out is the following. The SQL-based monitoring system acts from within the MySQL™ Server, using a set of stored functions and scheduled events. You can decide the frequency of the acquisition of the monitoring data and this parameter of course will influence the overhead on the MySQL™ Server.
In our tests we used a frequency of 1/6 (i.e. 1 event execution every 6 seconds) and runs which lasted 1 minute, so that, for each run ,about 10 records were being inserted in the audit table. A normal value for the frequency would be 1 event execution every 1, 2, ... or 10 minutes - indeed this is highly influeced by the context - but to choose such an low frequency our runs should have been much longer than just a minute.
So in our benchmark every 6 seconds the "Populating" event was collecting the status and system information from our MySQL™ Server (using the queries SHOW VARIABLES and SHOW STATUS), calculating the performance ratios and storing all the values in the audit table.
In general, the frequency of the data acquisition could be determined on the basis of the following factors:
- maximum acceptable overhead introduced by the monitoring system
- desired "granularity" of the performance data
Of course if your granularity is x minutes, than you will not be able to determine how your server's performance evolves within a specific interval of x-minutes, because you can only ready values every x minutes. Obviously if there is a sinusoidal trend whose frequency is higher than 1/x, than you won't be able to see it.
In most of the cases a 1 to 10 minutes granularity (i.e a frequency of between 1/60 and 1/600) is acceptable.
Please also note that the monitoring system can be paused at any time, so if you think it is causing performance degradation, just pause it for a while and check if you see any improvement. We always suggest our users to perform some quick benchmarks the first time they install our monitoring system - especially for H.A. environments like replication and cluster - to be sure all is working as expected.
Given that the monitoring system works by storing a record every, say, 5 minutes, you can understand why on read-write systems the overhead added from a TPS (Transactions per Seconds) point of view was near to zero. A write-frequency of 1 record every 5 minutes is orders of magnitude lover than a common TPS value.
Please also note that monitoring data are actually stored on your MySQL™ Server. This gives you the advantage that monitoring data are always available to you - on your server rathern than on a external centralized repository server - but on the same time, this means that the monitoring system actually uses some space of your disk. Of course you can prune old data and decide to automate this process via the "Audit Installation Wizard" or the "Audit Options" Window. However, disk space used is normally orders of magnitude lower than the disk space required by all the other databases.
Please see the next FAQ to know how much data space does our SQL-based monitoring system require.
24. How much disk space does your monitor system require?
Disk space is required essentially to store records in the audit table and it varies on the engine you have chosen for this table.
The more records the audit table contains, the more space is required, hence the frequency of the data acquisition and of the data pruning deeply influence the space used by the monitoring system.
In most common situations, total amount of disk space required by the monitoring system is orders of magnitude lower than the space required by all the other databases.
Please see the next FAQ to learn how to calculate the space actually being used by the monitoring system on your Server.
25. How can I see how much disk space is the monitoring system using on my Server?
Please open the "Audit Statistics" Window by clicking on the Auditing / Audit Statistics menu. Disk space as well as other statistical data are reported in that Window.
26. Can I delete or purge old monitoring data?
Yes, sure.
To save disk space you can delete old monitoring data. Since your monitoring data reside on a standard MySQL™ table, you can execute a standard DELETE query over this table, like in the following example, which can be used to delete all monitoring data older than value 'your-date-here':
DELETE
FROM `hs_audit_schema`.`status_system_dpm_table`
WHERE `hs_audit_schema`.`status_system_dpm_table`.`g_timestamp` < 'your-date-here';
The SQL-based monitoring system takes care of pruning old monitoring data automatically. Using the scheduled event status_system_dpm_pruning you can decide how often you want to delete data and how much old should be the data which has to be deleted.
You can specify the time interval of data pruning during the installation of the monitoring system, using the "Audit Installation Wizard", or later, at any time, using the "Audit Options" Window.
Of course you can also decide not to prune any data.
By default, the "Pruning" Event deletes data older than 6 months; you can change this parameter using the ALTER EVENT syntax or through the "Event Editor" included in HoneyMonitor.
For instance, if you want the event to delete all data older than XYZ months, please change 6 in XYZ in the following line of the event:
...
`hs_audit_schema`.`status_system_dpm_table`.`g_timestamp` < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 6 MONTH);
...
If for example you set a time interval of 1 month for the "Pruning" event execution, every month the scheduled event will delete all monitoring data older than XYZ months.
27. How do I uninstall your SQL-based monitoring system?
Uninstalling our SQL-based monitoring system is very simple process and requires only 1 to 5 seconds.
Uninstallation process can be performed client side, via HoneyMonitor, using the Auditing / Uninstall Audit System menu. Alternatively, you can use the DROP DABASESE statement to delete the hs_audit_schema database.
No specific actions are required on your server machines since it is necessary to delete just the audit database.
