Changes (as compared to 9.33) include:

Features:
* All CREATE TABLE and ALTER TABLE GUI functionalities will now open in a non-modal tabbed interface.  The new tab appears in the SQL area (alongside QUERY tabs etc.). With the new interface some minor bugs with the old interface have been fixed as well. Also the SQL generated is cleaner as regards formatting and redundancy. Note that in this beta the old dialogs are still used by Schema Designer.
* HISTORY and INFO tabs will now also appear in the SQL Area. The rationale is that those are not related to a specific (set of) query(ies). We plan to move the DATA tab up there as well for same reason.
* When SQLyog opens, the SQL Area will new open the HISTORY tab, INFO tab, and a single QUERY tab. Other tabs belonging in this area can be opened on demand. HISTORY and INFO tab display are persisitent across session (ie. if one is closed when SQLyog closes down it will not diplay when SQLyog is opened next time – but may be opened by user, of course).

Bug fixes:
* The BIT datatype was not supported properly in text-mode.
* On COMMUNITY if the SQL Area was increased to fill all vertical space, the divider could not be dragged back.
* In ‘Execute SQL-script’ it is now optional if the complete script should abort or not if an error was encountered.  Before it always aborted. The fix does not apply to HTTP tunnel where we will still ‘abort on error’ always.
* Copying to clipboard could erroneously raise an ‘out of memory’ error.  This was a miscalculation.
* ‘Import External Data’ wizard now supports SHIFT+CLICK selection of objects.
* The connection manager GUI’s of the SJA wizards did not handle SSL certicates properly (the main program window did not suffer from this).
* If an ENUM/SET column was returned using an alias the drop-down combo in the RESULT GRID did not display.
* Underscores were not handled properly by Autocomplete.
* Syntax highlighting is now not used in INFO tab. It made sense for the CREATE statement only and was disturbing in other contexts.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(0)

This is a follow-up om my first Blog on performance_schema that appeared here:

One particular request that we had frequently from our users is to have status variables exposed per database and maybe even per table. Currently this is not possible. Not even with MySQL 5.6.3 with the P_S additions found there. I have frequently experienced that users get frustrated why ‘such a simple thing’ is not possible and I have the impression that they sometimes tend not to believe me!

Examples: the (GLOBAL) status variables ‘slow_queries’, ‘com_update’ etc. has no database/table -level information. For those MySQL users that run a lot of different applications (both standard applications – Forums software, Blogging software etc. – as well as applications dedicated to the particular business of the organization) on same server this would be extremely usable. It is not always easy to identify the reason for a sudden or periodic increase of a status variable. If the database context was exposed it would in most scenarios be possible to identify the application causing this and then it would be much easier to analyze.

It is to some extent possible to get such information from logs of course, but enabling general log has a penalty and parsing logs is complicated alone by the fact that sometimes a ‘fully qualified’ table name is used and sometimes a USE statement + a non-’fully qualified’ table name is used. The USE statement may appear in the general log Gigabytes before statements using it! Also use of log tables (for general log in particular) has penalties.

Retrieving the information from P_S could be framed like this:

SELECT database, table, query, query_time FROM performance_schema.queries WHERE slow_query = 1 and com_insert = 0 ORDER BY query_time DESC;

Also aggregation like “.. GROUP BY database ..” could be useful. If memory is a problem the P_S tables could have aggregated information only. Even that would be helpful in some cases.

I don’t know how much trouble it would be to add such instrumentation. I realize that queries (JOIN’s, UNION’s, subqueries) may reference more tables and even different tables in more databases, but even that should be possible to solve somehow (record it twice if there are two tables referenced, for instance).

I would find a feature/instrumentation like this in P_S extremely useful. More useful (for me) than much of the stuff you’ll find there now.

Comments(0)

What does FLUSH STATUS do? We all know that it will simply reset all status variables (except for ‘uptime’) to same values as immediately after a server restart. Just google it and you will find that behaviour statet on hundreds of sites on the Internet.

But NO – it is not true! Not anymore. MySQL documentation:
http://dev.mysql.com/doc/refman/5.5/en/flush.html

“This option adds the current thread’s session status variable values to the global values and resets the session values to zero. It also resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections.”

This bug report http://bugs.mysql.com/bug.php?id=22875 has some background. Before introduction of seperate SESSION and GLOBAL status variables, FLUSH STATUS really did reset (almost) all status variables to same values as immediately after a server restart.  Now it affects SESSION status variables (but why is SUPER privilege required for this?) and a few GLOBAL status variables only.

From the bug report I think that the change of behavior was not really planned and specified properly in advance but came as some kind of surprise – also to some people in the MySQL developer team. I agree with Konstantin Osipov’s (one of those MySQL developers that seemed surprised) comment in the bug report:

“As this behaviour got broken in 4.1, I personally consider it a regression and believe that it should be fixed as a regression. In order to fix it in line with the design of SHOW [SESSION/GLOBAL] STATUS we should implement FLUSH GLOBAL STATUS.FLUSH STATUS behaviour should be modified to not change any global data,  such as:
- key cache counters
- connection counters
- SHOW_LONG global variables.
In other words, FLUSH STATUS should behave as FLUSH SESSION STATUS and only reset session-scope status variables. [SESSION] keyword should be added to the grammar and as optional.Key cache counters, connection counters, SHOW_LONG variables and server-scope status variable (those that have session counterparts) should be reset by FLUSH GLOBAL STATUS. FLUSH SESSION STATUS should no longer require SUPER privilege (connection privilege is enough, and as long as it is checked at connection, no privilege check is necessary).FLUSH GLOBAL STATUS should require SUPER privelege.”

But what happened was that the new behaviour was simply documented and since the problem/the report has been abandoned. It is now 5 years ago. Nothing happened since. The bug report is still open with the status of ‘verified’.

The reason why I am writing this is that I just replied to a concern to a MONyog user on whether MONyog could be fooled by this when calculating counters in the “ALL TIME” timeframe. The answer is that it cannot provided the actual server has implemented the GLOBAL status variable ‘uptime_since_flush_status’. All recent servers have (only very early 5.0 and 5.1 servers (5.0.35-,  5.1.24-) that nobody should use today have not). MONyog will simply discard all collected information older than ‘uptime_since_flush_status’ even if it has not yet been purged from the MONyog embedded database.

But still: Can we soon have that SESSION|GLOBAL switch for FLUSH STATUS, please? What we have now is a mismatch both as regards the semantics and the privileges requried. And 5 years of ‘stand-still’ for a ‘verified’ bug report related to server core features is too much (even though it is probably not the only one).

Comments(0)

Update: Thank you all for making this campaign a colossal success. We are extending this offer till Dec 2nd, 23:59 PST. Many corporate users requested us to extend the offer as they require time to get official approval. Pick your copy right now! Use coupon code blkfrdy11 to get 50% off.

–

Happy Thanksgiving!

Black Friday is back with a bang. Get a flat 50% discount on all Webyog products. Yes, you read it right, a flat fifty percent discount. Hurry, this offer expires Nov 28th Dec 2nd, 23:59 PST. Use coupon code blkfrdy11 to avail the discount on any purchase. Buy Now.

Still using SQLyog Community Edition?

Upgrade to the feature rich SQLyog Professional/Enterprise/Ultimate Edition. This offer is literally too good to pass up. Compare features.

Worried about your MySQL server’s health?

Monitor it like a PRO. Get MONyog – MySQL Monitor & Advisor. Your MySQL DBA in a box! Check what it has to offer.

Want to extend your maintenance period?

Don’t think twice, head right away to Customer Area & save a flat 50%. On extension, you get free access to all future releases of the respective products for one year.

Hurry, this offer expires Nov 28th Dec 2nd, 23:59 PST. Use coupon code blkfrdy11 to avail the discount on any purchase. Buy Now.

Regards,
Chirag
Webyog, Inc.

Comments(5)

I have for the first time been spending some time trying to understand the performance_schema. It is not easy to understand everything unless you are very well-versed in server internals (what I am not) and much information available here is probably more useful for server developers and testers than for ordinary users. But anyway some of the P_S tables are reasonable simple and useful. For instance the ‘accounts’ and ‘hosts’ tables (introduced in MySQL 5.6x) are immensely useful for everybody.

The reason why I spent time with this now was (I am totally egoistic, of course) that we just released the first beta in a new MONyog release cycle where you may expose P_S contents for monitoring. Please refer this Blog.

However I have two concerns with the P_S implementation. This derives from the fact that P_S is to some extent  updateable for users having sufficient privileges. You may for instance disable/enable ‘consumers’ and decide whether information should be recorded in counts of CPU clock-cycles or microseconds etc. Also some tables may be truncated in order to discard old information and free memory.

The interface for this is standard SQL statements (UPDATE, TRUNCATE). IMHO this leads to two problems/concerns:

1) Server restart. If the server restarts all P_S settings are reset to default. The UPDATEs from last running instance are lost. I am  simply missing a mechanism to load the UPDATEs again automatically at startup. I do not favour  introducing a lot of new server startup options and variables (that would be around 100 I think if all cases should be covered and there already are more than enough options IMHO). One option could be a P_S configuration file (‘performance_schema.ini’) or maybe an EVENT executing at startup (“at startup” could be a useful schedule setting for an EVENT in other contexts as well – but it is not in any server version currently). Also the problem cannot be worked around an automated way currently on Windows as far as I can see, as on this platform the server is not started by a ‘script’ but by an executable (the ‘Windows Service Manager’ reading details from a registry key). On Unix-type platforms you may (I think so!) modify the server startup script adding a call to Perl/Python/whatever script performing the updates on the P_S tables.

2) Multi-user/multi-admin environment. If for instance GRANTS for ‘peter’@'thishost’ are like “GRANT CREATE USER, SELECT,UPDATE ON *.* TO ‘peter’@'thishost’ WITH GRANT OPTION;” then peter may “GRANT SELECT,UPDATE ON performance_schema.* TO ‘jimmy’@'thathost’;”. Now both peter and jimmy can UPDATE the P_S tables that are updateable. But the problem is that one of the users may do so without the knowledge of the other resulting in unexpected results. The only solution I found was LOCKING (example: “LOCK TABLES performance_schema.setup_consumers READ;“). I don’t find it fully satisfactory however, but I have no better solution with current implementation and also no usable idea for an improved implementation.  

Any thoughts?

Comments(5)

Changes (as compared to 4.72) include:

Features:
* This release adds a new major feature to MONyog: Custom SQL Counters (“CSC”) and Custom SQL Objects (“CSO”). For details refer note below and the program documentation.
* Drastically improved performance in log analysis if the option to ‘replace literals’ was selected.

Bug fixes:
*If a query contained the literal substring ‘connect’ MONyog could hang during general log analysis.

More about Custom SQL Counters:

A “Custom SQL Counter” (CSC) is based on any user-defined SQL query returning a result set. The array returned by MySQL from the SQL query populates a MONyog Object (a ”Custom SQL Object” (CSO) in this case) . This is exposed as a javascript array that may be referenced in MONyog counter definitions like any MONyog object.

The primary reason for introducing this feature is to utilize information available  in Information_Schema (as well as Performance_Schema of MySQL 5.5+) that is not exposed in the basic SHOW statements we have been using till now. In addition to the fact that – in some cases –  I_S has more information than what SHOW  returns it is also accessed using a SELECT statement what makes sorting, filtering, JOINs, use of aggregate functions etc. possible. Also MySQL ‘forks’, various plugins and third-party storage engines will often populate I_S with data not available with the ‘upstream’ MySQL server from Oracle. With this new feature you can use such information for monitoring as well.

But CSO’s are not restricted to SELECT FROM I_S and SELECT FROM P_S. Any SQL-statement  returning a result set may be specified.  Examples:
* Specially framed SHOW statements (using WHERE and LIKE clauses for instance).
* Maintenance statements (example: “CHECK TABLE” – but be aware of (engine-specific) LOCK behaviour with such statements).
* Queries on user data.  For instance if you have a support ticket system you may frame a query telling how many support tickets have been inactive for more than 24 hours.
* Also a CALL statement may be used if it returns a single (no more and no less) result set.

This build ships with 13 pre-defined CSO’s.  To understand them follow the steps:

1) Go to TOOLs tab .. Customization .. Manage Custom SQL Objects ..  Add/Edit Custom SQL Objects. You will see the 13 pre-defined CSO’s display in the left menu. As an example select the ‘DiskInfo’ item.  The User Defined SQL-query displays in the ‘SQL’ box. Sample interval and retention timeframe specific for this CSO may be changed as per your preference and you may specify for which MySQL server(s) this particular CSO should be collected.  Also note that one or more ‘Key columns’ are defined. This/they must be a column or a set of columns returning (a) unique (set of) value(s) (similar to a UNIQUE KEY in MySQL). Without defining such properly the CSO will not be usable in the following step.

Now enable it and click ‘Save’ when you are done with all. This query now executes on the MySQL server(s) where you defined and enabled it and a MONyog object named  ”DiskInfo’ will populate and be exposed for counter definitions.

We will here further enable a few more CSO’s:  enable the ‘Table_Size’, Database_Size’, ‘Data_Types’ and ‘Storage_Engine’ pre-defined CSO’s.

2) Go to  go to TOOLS tab .. Customization .. Manage Groups and enable the ‘Disk Info’ Group. This pre-defined group contains pre-defined CSC’s using the CSO’s you enabled in step 1).

3) Now go to Monitors/Advisors page, select the ‘Disk Info’ group that now displays at the bottom. You will see 5 new counters in that group that in various ways reference the CSO’s that we just enabled (click the counter name and next ‘Customize’ as usually to see the javascript code). You may customize those further as you would do with any counter in MONyog.

Note that some of the predefined CSO’s require specific server versions and/or configurations (like the ‘InnoDB plugin’ with MySQL 5.1 (or MySQL 5.5+), ndb_cluster enabled and even in one case the server must a PerconaServer build).  If this requirement is not met, the SQL query may return NULL for specific values or it may  simply return an error.  In such cases MONyog will display ‘n/a’ for CSC’s based on non-populated CSO’s.

Also note that as the sample interval and retention frame setting for CSO’s are independent of the setting for built-in SHOW-based counters and also independent of each others, every CSO is handled by a seperate thread  by MONyog internally. Also every retrival of a CSO will open a new connection to MySQL, retrive the result and close the connection after data have been retrieved (and MONyog itself will of course report these connections in Dashboard and in Monitors/Advisors .. Connection History .. Attempts). This implementation is chosen in order both to avoid ‘bottlenecks’ in a single connection (as MySQL unfortunately does not allow parallel queries in a single connection) and in order to reduce the number of open connections at any time as much as possible.

How many CSC’s/CSO’s you may have enabled will depend on the capabilities of your system and the number of servers registered. A lot of CSO’s collected with a short sample interval from a lot of MySQL servers will obviously create additional load (I/O and network traffic in particular).  A rough guideline is that with a large number of servers and 10 or more CSO’s enabled you should not use sample intervals lower than a minute on an ‘average’ system. If you have only a few CSO’s and/or a few MySQL servers you will be able to use lower sample intervals.  We are currently benchmarking this and will publish details as soon as we have and when this new feature has been optimized as much as possible.

We have one significant benchmark currently though: With 500 MySQL servers monitored from a single MONyog instance, 10 of the predefined CSO’s enabled to all MySQL servers with a sample interval of 5 minutes it runs fine causing almost insignificant additional load on a 64 bit Linux box with same hardware configuration as described as our primary test system for MONyog  here in this Blog.

Stay tuned at this Blog.  Likely more information about this (examples, benchmarks) will appear here soon.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(1)

Changes (as compared to 9.32) include:

Bug fixes:
* Fixed two regression bugs in Autocomplete introduced in 9.31: 1) a performance regression if the option to “Show suggestion as you type in SQL editor” was enabled. 2) Table alias support was broken.
* The Autocomplete popup windows opened by Ctrl+Enter and Ctrl+Space could ‘pop down’ where it should ‘pop up’ resulting in the content being partly invisible. This bug was introduced in 9.31.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(0)

Introduction:

If you have been using MONyog you will know that any metrics/counters may be identified with either of the ‘states’ STABLE, WARNING and CRITICAL. For those metric where it is relevant MONyog ships with settings for this that are (to the extend possible) commonly agreed ‘best practices’ by MySQL DBA’s. Metrics in a WARNING or CRITICAL state display with a (yellow or red) ‘marker’ indicating that this metric is not in a STABLE state. For metrics in a CRITICAL state further an alert can be sent (as a mail alert and/or a SNMP trap).

If the ‘best practices’ we have implemented in MONyog do not fit your environment or use case, it can easily be customized. Open the MONyog ‘Monitors/Advisor’ page, click on the name of the metric you want to adjust (it is a link). A small popup opens. As an example let us take the ‘Connection usage’ in the ‘Current connections’ group. In the popup you will see the settings for this metric. They are defined like this

Warning = 75
Critical = 85

To change the settings click ‘Customize’ in the popup and next ‘View Advanced’ to see all options. It will open in a new browser tab. If you are willing to allow the number of current connection to reach 90% of the max_connections allowed before the state should change to CRITICAL  just enter “90″ as the value for the threshold of the CRITICAL state and save.

Differentiating thresholds for different MySQL servers:

What we described above will have effect for all MySQL servers monitored. It was also meant to be an introduction only.  The real  question to be dealt with here is different however: Is it possible to have different ‘state’ settings for different MySQL servers?

The answer is YES, and it is very simply to do actually. Say you monitor 2 servers that you in MONyog have named “Testserver” and “Production” respectively, and that you want a CRITICAL setting of “90″ for “Testserver” and “80″ for “Production”. Then the CRITICAL setting is not a constant (applying for all MySQL servers) anymore, but a condition needs to be added. MONyog exposes connection details to different MySQL servers monitored as Javascript objects that can be referenced with a simple if/case-condition. And it is very simple to work with. Just replace the constant with a (javascript-)function like this

function()
{
if(MONyog.Connections.ConnectionName == “Testserver”) return 90;
if(MONyog.Connections.ConnectionName == “Production”) return 80;
}

Below a (reduced) screenshot of values entered in the customizations page:

.. and now save. That is all. Now the CRITICAL condition for this metric is defined differently for the MySQL servers “Testserver” and “Production”.

Note that everything happens centrally at the MONyog level and there is nothing to do on the MySQL servers monitored. And also there is no need for any other program or utility (such as a database client or whatever) to do this. MONyog is agent-less and self-contained.

The objects that can be referenced in the customization interface are listed in the MONyog documentation here along with the explanation of the ‘MONyog Object Model’: http://www.webyog.com/doc/MONyog/MONyog_object_model.htm

In the above image we have defined different WARNING and CRITICAL values for another metric (‘percentage of max_allowed reached’ in the ‘Connection history’ group) for two servers: For both servers WARNING is set at 84% and CRITICAL at “Testserver” is set at at 90% and at “Production” CRITICAL is set at 87%. You will notice the red and yellow ‘markers’ displaying accordingly.

(There is one more detail you should be aware of: MONyog will not necessarily alert using mail or SNMP first time a CRITICAL value has been reached. There is a setting for this as well. The default setting is that the CRITICAL condition must have been in existence for 5 consecutive data retrievals from MySQL. This setting is specific for every MySQL server registered in MONyog and is available  in the MONyog ‘Register servers’ page for each server, but the value can be overridden for an individual metric by setting the “RetryOverride” value from the customizations page. Please be aware of this when testing or you may become confused why you don’t get the alerts you’d expect)

Download the MONyog whitepaper: http://www.webyog.com/en/whitepapers/MONyogWhitePaper.pdf
Read the MONyog documentation online: http://www.webyog.com/doc/index.php
Download MONyog TRIAL: http://webyog.com/en/downloads.php
Purchase MONyog: http://webyog.com/en/buy.php

Comments(0)

Changes (as compared to 9.31) include:

Bug fixes:
* Typing using the numerical keypad typed nothing in the SQL editor.  This bug was introduced in 9.31.
* With Japanese interface enabled some favorites items could fail to display.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(0)

Changes (as compared to 9.3) include:

Features:
* Improved the handling of (built-in and stored) functions and stored procedures in Autocomplete. This involves 1) a stored program is now handled also if a ‘fully qualified routine name’ is not used. 2) introduced a Ctrl+Shift+Space keyboard shortcut that will display the parameter-list of a routine when the cursor is positioned inside it. 3) while writing a routine call the parameter list will highlight the current parameter. Also note that we have also reversed the behavior of Ctrl+Space and Ctrl+Enter keyboard shortcuts for Autocomplete – this in order to comply with most IDE’s and advanced editors.

Bug fixes:
* Schema Sync could generate an incorrect ALTER TABLE statement with an A TIMESTAMP .. ON UPDATE CURRENT_TIMESTAMP column on source.  This bug was introduced in 8.4.
* On Wine the status line (at the button of the program window) did not display. This was introduced in 9.3.
* The Keyboard CTRL+SPACE did nothing on Wine. Now CTRL+SPACE and CTRL+ENTER beve identically on Windows and Wine
* On Wine the autocomplete popup window painted with a frame  hiding some details.
* When Japanese was selected for the program interface ‘Copy Database/Table’ returned an error. Also this was introduced in 9.3.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(3)