Blog

The wish list for secure and effective monitoring of MySQL servers

  • ravikiran.iyengar
  • June 16th, 2016
  • MONyog, MySQL
  •  All you need for monitoring MySQL servers

    Data security is paramount for individuals and corporations alike. Today, it is inevitable not to notice the importance levied on hosted MySQL like RDS, Aurora, etc. Managing MySQL servers is becoming a daunting task, given the exponential growth in data and the number of users with each working days.

    Below are the key aspects that need to be part of your wishlist.

    1) Leveraging Performance Schema(P_S) and SYS Schema

    Performance Schema was introduced in MySQL 5.5.3 and further upgraded through version 5.7. It has its own storage engine, provides access to low-level server execution information while minimizing the impact on server performance. There is a trade-off between the time you can save by using Performance Schema and the load it adds to the server. It can optimize server performance and save time when used in an efficient manner.

    You can also target queries that run less than a second using events_statements_summary_by_digest from Performance Schema with little-to-no overhead that helps us get the fast running queries that were not available before.

    How is it important?

    • It takes minimum disk storage since it employs the use of temporary tables or views
    • As all allocation is carried out during the server startup, there is no further memory re-allocation or sizing after the startup

    Performance Schema on MySQL contains queries executed on server along with the following information:

    • Number of rows sent and examined
    • Number of temporary tables created on disk
    • Number of temporary tables created on memory
    • Number of joins performed and the type of join
    • Whether sorting happened and find the kind of sorting
    • Whether index was used and implemented

    Starting from MySQL version 5.7.7 and above the sys Schema was introduced with an objective to help the DBAs understand and use the data collected by Performance Schema. In later versions, MySQL introduced progress reporting for long running transactions. Using performance schema at an enterprise level can help you in effective monitoring.

    2) Suitable monitoring of Indexes

    There are various reasons why DBAs use indexes:

    • Help reduce the amount of data the server captures while looking for specific fragments of data
    • Make inputs and outputs sequential
    • Reduce the number of temporary tables and sorting that the server has to undertake

    However, indexing can have its set of drawbacks if not done in the right manner. One of the major causes that hamper the performance of MySQL is either the overuse, underuse or no use of indexes. Although there are different ways to deal with each of the above cases regarding index usage, the first step comprises of finding the culprit and the nature of problem related to indexing.

    You can monitor various aspects of Index usage in the ways listed using PERFORMANCE_SCHEMA.

    Performance_schema implements tables as shown below:

    • Table_io_waits_summary_by_table
    • Table_io_waits_summary_by_index_usage

    These tables give an account if the IO and usage activity of the indexes.

    Using the SYS Schema:

    It provides a favorable view making it easy to identify unused indexes:

    ~ mysql> select * from sys.schema_unused_indexes;

    The above example shows indexes which haven’t been used since the last restart of MySQL when PERFORMANCE_SCHEMA statistics are reset at startup time.

    3) Reliable and concise Configuration Management

    At an enterprise level, a few hours of downtime can cost millions of dollars. DBAs, at times, can lose track of server configurations when they are working simultaneously on numerous servers.

    Thus, a reliable configuration management system is needed that helps you check 100s of servers at a given point of time. It is important because there are servers that share the same amount of load but perform differently due to change in configurations.

    It becomes imperative to seek and find a MySQL management tool that provides the option to compare and track system configurations on multiple servers.

    4) Query Analyses

    “I am looking to eliminate or optimize problematic queries!” – The most common issue faced by thousands of DBAs around the world. Eliminating this requires systems that provide for a macro and micro view of the queries. These systems further provide filtering options that help to optimize the query logs.

    5) MySQL Server Logs

    Referring the appropriate logs is an essential element in spotting the problems. MySQL server provides extensive information about the various aspects through critical logs such as error log, slow query log, general query log, etc. These logs help to spot problems that one may encounter during starting, running or stopping of MySQL – queries that take more time than mentioned to execute and more (it’s not a complete sentence).

    Though by default, these logs are disabled, these logs can be enabled depending upon the requirement of the user to track the happenings on the server.

    6) Bash Scripts for alerts

    Monitoring is incomplete without an effective alerting system for proactive actions. Setting up alerts helps the user in receiving reports that showcase how variables and corresponding values appear on the server. Sometimes an admin needs to keep a check on any particular action undertaken on the database. It can be handled by using appropriate scripts that send the admin a notification over email that alerts him about the action.

    While none of this can be overlooked, it is important to find the right monitoring tool for your servers. Managing the performance of your MySQL servers can help in a quick analysis of your problems, save time in monitoring and increase the overall productivity.

    You can give our monitoring tool MONyog a spin. Considered by thousands of corporates as the most secure and scalable MySQL monitoring tool, MONyog ensures optimum performance of your MySQL servers.

    MONyog_CTA

     

    SQLyog MySQL GUI 12.2.4 Released

  • peter_laursen
  • May 27th, 2016
  • Releases, SQLyog
  • SQLyog MySQL GUI 12.2.3 Released

  • peter_laursen
  • May 26th, 2016
  • Releases, SQLyog
  • Changes as compared to 12.2.2 include:

    Features:

    * User Manager filter: when some characters are typed for user in User Manager first match with same starting characters will be selected.

    Bug Fixes:

    * “Ignore Definer” did not work if “Drop Table if exist” and “Ignore Definer” both were ticked in cases where only procedures and/or functions were copied to another database.
    * The result of a multiline string in text mode displayed empty in case where the string started with a line break.
    * For “wide” BIT(n) types “0″-bits (even significant ones) could be truncated away due to a bug in a string conversion algorithm.

    Miscellaneous:

    * The Plink executable shipped with the 64 bit installer is now a 64 bit binary. Before it was 32 bit.
    * The width of the dividers between the various panes of the SQLyog main window has been increased for better operability of resizing. This will be useful with touchscreens in particular.

    Downloads: https://www.webyog.com/product/downloads
    Purchase: https://www.webyog.com/shop

    SQLyog MySQL GUI 12.2.2 Released

  • peter_laursen
  • May 5th, 2016
  • Releases, SQLyog
  • Changes as compared to 12.2.1 include:

    Features:

    * The ‘date picker’ now has a GUI control to insert current date.
    * When running multiple statements in the editor and an error occured, SQLyog would continue to next statement.  There is now an option to ‘halt on error’ in such cases.

    Bug Fixes:

    * The `sys` database of MySQL 5.7 was erroneously backed up when choosing  ‘all databases’ in the Scheduled Backup tool.
    * Table-level comments were truncated at 490 characters.
    * The ‘date picker ‘ calender could display distorted in DATA tab in some cases.

    Miscellaneous:

    MySQL username max-length in SQLyog connection settings has been increased from 16 to 32 characters, as MySQL 5.7 now allows for.

    Downloads: https://www.webyog.com/product/downloads
    Purchase: https://www.webyog.com/shop

    MONyog MySQL Monitor 6.53 Has Been Released

  • peter_laursen
  • April 28th, 2016
  • MONyog, Releases
  • Changes (as compared to 6.52) include:

    Bug Fixes:

    * “Count” column did show empty value in Processlist based Real-time.
    * Performance_Schema based Real Time failed if global sql_mode included “ONLY_FULL_GROUP_BY”.
    * Clicking on locked queries were not showing its corresponding locking query and vice versa for Performance_Schema based Real Time.
    * The counter “General Log Enabled?” was showing incorrect value for MySQL 5.6.1+ versions.
    * In rare cases, MONyog would show only time with no date in x-axis if the chart was plotted for multiple days.
    * Editing LDAP group was not always working as expected.
    * With RDS servers MONyog could fail to detect log files and would display the error “log file not present”. It happened because of changes in the XML-response of Amazon’s REST API introduced since we started supporting logs with RDS.

    Miscellaneous:

    * The default filter for “show queries only” in processlist now includes ‘Command=Execute’. Also ‘Command=Execute’ is now included in processlist-based Real Time and Query Analyzer interfaces.

    Downloads: https://www.webyog.com/product/downloads
    Purchase: https://www.webyog.com/shop

    New Query Analysis Features in MONyog

  • Chetan
  • March 7th, 2016
  • MONyog, MySQL
  • While looking to optimize your MySQL, you are needing to spot slow/bad queries at a glance and get deep insights about them with ease.

    The latest update of MONyog brings new easier ways to find problem SQL in Real-Time, Wayback Machine and sniffer based Query Analyser using Performance Schema. Here is what’s new:

    Query Execution Status and Full Table Scan Count
    View success/failure status of every query executed and also the number of ‘full table scans’ for queries in a single glance.
    Query Execution Status and Full Table Scan Count

    The latest update also has an option to switch between Performance Schema and Processlist in Real-Time making it easy for you to enable/disable Performance Schema or Processlist.

    Get the latest update now to enjoy these features.Existing customers can download MONyog 6.52 from Customer Area. To evaluate MONyog 6.52, please download a 14-day trial.

    We’re excited about this update. We would love to know your feedback and thoughts. Drop a comment to share your feedback.

    MONyog MySQL Monitor 6.52 Has Been Released

  • peter_laursen
  • March 2nd, 2016
  • MONyog, Releases
  • Changes (as compared to 6.51) include:

    Features:

    * Added an option to choose data collection mode for Real-Time, Users can now choose between ‘Performance schema’ or ‘Processlist’. Old versions used ‘Processlist’ what we replaced with ‘Performance schema’ recently for servers running with needed P_S instruments enabled.  However due to the aggregation and truncation of queries recorded  in P_S we had several requests that both options should be there.
    * Information whether a query is performing a full table scan available in Query Analyzer, Wayback Machine and Real-time. This information will help to identify queries which are not using Indexes. Note: this is available only for Performance Schema mode.
    * Added a new monitor group ‘sys schema’. It contains various CSOs (Custom SQL Objects) utilizing the “sys schema” database introduced in MySQL 5.7.7.
    * Added an option to either have a fixed length height or dynamic (full screen) height for Processlist in MONyog GUI. We have been using each in earlier versions, but it seems impossible to make everyone happy with a single solution. Now user can choose what display/design that fits him best.

    Bug Fixes:

    * The MONyog API failed to set the error log path.
    * With MySQL 5.7+ Query Analyzer was displaying seemingly random values for the column “first seen” and “last seen” when using server logs (slow query log, general query log) as data source. This happened  because of change of Timestamp format in the logs in MySQL 5.7.
    * The filter “doesn’t contain” was not working properly in Real-time.
    * Fixed an issue where UDOs were displaying incorrect values after running for some time.
    * While editing the server using MONyog API, SSH settings were automatically set to disabled (even if it was enabled earlier).
    * For RDS/Aurora servers, “Apply the above settings to all the servers with same tags” (Edit server -> Advanced settings) was not working.
    * MONyog was giving the error “Log file not present” if either one of the slow query log or general query log was disabled for RDS servers.
    * Fixed Javascript errors and optimized the charts for Real-time page for IE8 browser.

    Miscellaneous:

    * The Y-axis in Charts had just “values” written, now it shows relevant units like percentage, seconds etc. and option is provided to customize the Y-axis value.

    Downloads: https://www.webyog.com/product/downloads
    Purchase: https://www.webyog.com/shop

    SQLyog MySQL GUI 12.2.1 Released

  • peter_laursen
  • February 25th, 2016
  • Releases, SQLyog
  • Changes as compared to 12.2.0 include:

    Bug Fixes:

    * Updates from RESULT tab could fail with MySQL 4.1 servers. The recent code changes in order to support MySQL 5.7 Virtual Columns overlooked that there is no Information_Schema database in MySQL 4.1 and a required condition could not be verifed due to this.
    * In rare cases “copy database to a different host” option could generate incorrect statements and copying would fail with a ‘columns count mismatch’ error. Also this was introduced with code changes in order to support Virtual Columns in MySQL. Virtual columns did not need to be present in source for the error to occur.
    * Fixed a crash when copying table data to clipboard.

    Miscellaneous:

    * Installer packages are now signed with a SHA2-hashed (and not SHA1 as before) certificate. Since recently Internet Explorer 11 has issued a security warning when installing from installer packages using a SHA1-hash with a build timestamp from Jan 1st 2016 or later. We were not aware of this policy-change by Microsoft when releasing SQLyog 11.1.9 and 12.2.0.

    Downloads: https://www.webyog.com/product/downloads
    Purchase: https://www.webyog.com/shop

    SQLyog MySQL GUI 12.2.0 Released

  • peter_laursen
  • February 12th, 2016
  • Releases, SQLyog
  • Changes as compared to 12.1.9 include:

    Bug Fixes:

    * Virtual Columns of MySQL 5.7 were not handled properly in ‘copy database to other host’.
    * 12.1.9 introduced a sluggishness when moving around the data GRID. In some cases it could be so bad that the program seemed to “hang” completely.

    Miscellaneous:

    * Fixed inconsistent versioning. Earlier we could sometimes use format like “11.19″ and sometimes “12.1.9″. We now consistently use the latter – like “12.2.0″ with this release.

    Downloads: https://www.webyog.com/product/downloads
    Purchase: https://www.webyog.com/shop

    SQLyog MySQL GUI 12.19 Released

  • peter_laursen
  • February 9th, 2016
  • Releases, SQLyog
  • Changes as compared to 12.18 include:

    Features:

    * Virtual Columns support for MySQL 5.7 onwards (Virtual Columns with MariaDB were already supported). Please see note below for details.
    * SSL and SSH-tunnel are now not mutually exclusive. A SSL connection may now be established through an SSH-tunnel.  This is in particular useful with various sorts of “private clouds” where connection to the cloud requires SSH and privacy of the cloud is enforced by using SSL for internal communication in the cloud.
    * Multiple files (.sql, .schemaxml, .queryxml) may now be opened in one operation in main editor window.

    Bug Fixes:

    * When importing from some (not commonly used) external data sources using a WHERE-clause a SPACE was missing before the “WHERE” keyword causing an error. This is actually an issue with ODBC-drivers affected, but this release has a workaround.
    * In CREATE/ALTER TABLE dialog a default value could be specified for BLOB/TEXT columns. The server does not permit this and doing so will result in a server error. Now the GUI prevents it.
    * Fixed minor bugs with the Virtual Columns support for MariaDB.

    Miscellaneous:

    * SQLite has been upgraded to version 3.10.0. This results in performance improvements (in autocomplete in particular).

    Downloads: https://www.webyog.com/product/downloads
    Purchase: https://www.webyog.com/shop

     

    An additional note about Virtual Columns support:

    There is a very important difference between Virtual Columns in MariaDB and MySQL 5.7 as described here. With the MySQL implementation from 5.7.8 both SQLyog backup/restore, Data Sync and ‘copy to other’ would fail with a server error if tables contained virtual Columns (it worked with MariaDB). The most serious issue was, of course, that backups from tables with Virtual Columns could not be restored – simply because when you found out, it may have been too late already.

    With this release all DML statements generated by SQLyog have been modified to consider Virtual Columns where needed – and also will consider the particularites of MariaDB and MySQL implementations.  However sync tools will not work with tables with Virtual Columns between MySQL and MariaDB as the syntax of the DDL differs.

    We believe that SQLyog - except for ‘mysqldump’ from 5.7.9 –  is the first tool/client that will work with Virtual Columns of both MariaDB and MySQL.

    Page 1 of 4812345...102030...Last »