PlanetMysql.ru — информация о СУБД MySQL

IMPORTANT note:
* This release requires a new registration code. Neither the 5.1x nor the pre-5.1x key will work with this. Registered customers will get the new code from our Customer Portal. Please have the new code available before installing. Until MONyog is registered with new keys, it will not be collecting data from your servers .

Features:
* Added filter for including or excluding specific hosts and(or) users in slow log analysis.
* Added and modified a few Monitors related to Replication, InnoDB and Security. Most of this utilizes metrics exposed by MySQL from version 5.5 .
* Added a preconfigured Custom SQL Object (CSO) for Percona Servers exposing the most written and most read tables.
* There is now session-wide persistence while sorting columns in Query Analyzer.
* Improved error messages in Query Analyzer and Wayback machine.
* Usability enhancements, GUI fixes and internal optimizations.

Bug fixes:
* MONyog was not working while behind Apache configured as a reverse proxy. This bug was introduced in MONyog 5.0.

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

PlanetMySQL Voting: Vote UP / Vote DOWN

IMPORTANT note:
* This release requires a new registration code. Neither the 5.1x nor the pre-5.1x key will work with this. Registered customers will get the new code from our Customer Portal. Please have the new code available before installing. Until MONyog is registered with new keys, it will not be collecting data from your servers .

Features:
* Added filter for including or excluding specific hosts and(or) users in slow log analysis.
* Added and modified a few Monitors related to Replication, InnoDB and Security. Most of this utilizes metrics exposed by MySQL from version 5.5 .
* Added a preconfigured Custom SQL Object (CSO) for Percona Servers exposing the most written and most read tables.
* There is now session-wide persistence while sorting columns in Query Analyzer.
* Improved error messages in Query Analyzer and Wayback machine.
* Usability enhancements, GUI fixes and internal optimizations.

Bug fixes:
* MONyog was not working while behind Apache configured as a reverse proxy. This bug was introduced in MONyog 5.0.

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

PlanetMySQL Voting: Vote UP / Vote DOWN

I would like to share this story based on a true event about the temporary files behavior in MySQL.

MONyog reports this error to my already full mailbox several times a day :

 

1 – Catch the query (if you can) !

 

I don’t have access to the client logs but I would like to know which query is involved in this error.

Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :

•  Enable the query sniffer in MONyog (based on processlist) : Edit server -> Advanced settings -> Sniffer settings

 

while [ 1 = 1 ]; do { date;ls -artlh /database/tmp ; df -h /database/tmp;
  lsof | grep mysql | grep /database/tmp;sleep 1; }; done

 

4 – What next ?

 

The killer question : why did you have only 6.5 GB for your temporary space ?
Answer : Why not :-)

You have to consider this behavior to set your temporary space properly, specially if this space is a ramdisk filesystem.

Tschüs !

PS : I let the experts explains why these two additional files are created (hint : look at the Exta column)

PlanetMySQL Voting: Vote UP / Vote DOWN

Hi,

We are delighted to announce the release of MONyog 5.0. This release is very special. This is not one of those bug fix / feature / functionality releases. This is about complete makeover for MONyog. Since the first release of MONyog, we have been continually adding features and fixing bugs. Less did we concentrate on looks and holistic usability. We kept on adding features with existing layout and put reorganizing logically connected elements to the back-burner. We thought it’s time to address these issues and took it up with MONyog 5.0.

• The design is modern. Easy to say in a line, but took months to come up with new looks as the software was not built to incorporate new design elements.
• MONyog is now more usable. We got into the shoes of a user and redesigned the flow, top down.
  • All added servers are accessible from a left panel, which is available across the software.
  • Adding a server is now easy, as user is prompted with only necessary fields. Rest of the settings are grouped in a separate tab.
  • Notifications show up in the top right corner – the right place to draw user’s attention.
  • Feature tabs are logically reordered.
  • Results of Query Analyser are paginated.
  • Customization options are now logically grouped into different tabs.
  • Many more changes to enhance user experience.

Refer the following release notes for details:

• MONyog 5.0 RC Release Notes
• MONyog 5.0 Beta2 Release Notes
• MONyog 5.0 Beta1 Release Notes

MONyog customers can download the latest installer from Webyog’s Customer Portal.

To evaluate MONyog, please download the 30-day trial.

We are very excited about this release, and hope that you will like it. We would love to hear from you.

Cheers,
Team MONyog

PlanetMySQL Voting: Vote UP / Vote DOWN

In this previous Blog I described an example on how to build a Custom Object in MONyog. But there is one problem you may encounter and that is, that when querying Information_Schema and Performance_Schema, it can sometimes be difficult or impossible to be sure to have a unique column (or a unique set of columns)  in the result set as neither of those generally guarantee uniqueness with the ‘raw’ data from the tables alone (no PRIMARY/UNIQUE KEY in the tables). And a unique column (or a unique set of columns) is required for a MONyog Custom SQL Object.

A common way to add a unique row-identifer to a result set is like this (note the use of  ”assignment operator” (“:=”) as different from “comparison operator” (“=”) ):

SET @t = 0;
SELECT @t:=@t+1 AS uniq, id FROM thetable ORDER BY uniq;

.. but using this is not possible with MONyog. Only a single SQL-query can define a MONyog Custom Object (currently – we may add an option for multiple queries later though). But there is a simple workaround: a SUBQUERY in the FROM-clause can be used to reduce the above two queries to a single query:

SELECT @t:=@t+1 AS uniq, id FROM (SELECT @t:=0) AS derived, thetable ORDER BY uniq;

Actually you can add one more subquery SELECTING from `mytable`and use an ORDER BY here to ensure that the lowest value of `id` (the column you are retrieving in this simple example) will also have the lowest value of the unique identifer you created. Doing so may ease building the javascript-part of a Custom Counter as there will be less conditions to test for and sorting can be avoided (data returned are simply ‘better prepared for scripting’). Such an example (with an AGGREGATE and a GROUP BY and a little formatting added as well) could be like this:

SELECT @t:=@t+1 AS uniq, id, COUNT(id)
FROM
(SELECT @t:=0) AS derived1,
(SELECT id FROM thetable ORDER BY id) AS derived2
GROUP BY id
ORDER BY uniq;

MySQL documentation has a reservation though. It says “As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.” But this reservation  seems not to apply if the assignment is a “SUBQUERY in the FROM-clause”.  It is a still a separate statement as far a I can understand – and also I have not been able to provoke an unexpected behaviour with the construction no matter what SQL syntax I tried (JOINs, GROUP BY .. HAVING etc.).

(And I did not invent this myself.  The tip appears as a user comment in the MySQL documentation)

PlanetMySQL Voting: Vote UP / Vote DOWN

[UPDATE 2011/01/11] : New poll added, vote for the best GUI client tool ! (And continue to vote for other polls)
And thanks again for your involvement. It’s time to vote again… 

First of all, I wish you a happy new year.
Many things happened last year, it was really exciting to be involved in the MySQL ecosystem.
I hope this enthusiasm will be increased this year, up to you !

To start the year, I propose the MySQL+ Community Awards 2011
It will only take 5 minutes to fill out these polls.
Answer with your heart first and then with your experience with some of these tools or services.

Polls will be closed January 31, so, vote now !
For “other” answers, please,  let me a comment with details.

Don’t hesitate to submit proposal for tools or services in the comments.
And, please, share these polls !

 

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

Happy 2012 !
Cédric

This article is obviously not sponsored !
(MySQL is a trademark of Oracle Corporation and/or its affiliates)

Sources :

• Oracle products, tools and services : http://www.mysql.com
• SkySQL products, tools and services : http://www.skysql.com
• Percona products, tools and services : http://www.percona.com
• FromDual products, tools and services : http://www.fromdual.com
• PalominoDB products, tools and services : http://palominodb.com/
• Pythian products, tools and services : http://www.pythian.com/
• Blue Gecko products, tools and services : http://www.bluegecko.net/
• Zabbix : http://www.zabbix.com/
• MySQL Cacti templates : http://code.google.com/p/mysql-cacti-templates/
• Nagios : http://www.nagios.org/
• r1soft CDP for MySQL : http://www.r1soft.com/linux-cdp/cdp-enterprise-edition/mysql/ 
• mylvmbackup tool : http://www.lenzg.net/mylvmbackup/
• mydumper tool : http://www.mydumper.org/
• ZRM tools : http://www.zmanda.com
• Galera replication : http://codership.com/products/galera_replication
• Tungsten replicator : http://code.google.com/p/tungsten-replicator/
• DRBD : http://www.drbd.org/
• ShoonerSQL : http://www.schoonerinfotech.com/products/schoonersql
• ScaleBase : http://www.scalebase.com/
• common-schema : http://code.google.com/p/common-schema/
• innotop : http://code.google.com/p/innotop/
• MySQL Tuner : https://launchpad.net/mysqltuner/2.0/mysqltuner-2.1-rev17
• mycheckpoint : http://code.openark.org/forge/mycheckpoint
• MariaDB : http://mariadb.org/
• TokuDB : http://www.tokutek.com/
• InfiniDB : http://infinidb.org/
• Drizzle : https://launchpad.net/drizzle
• Amazon SimpleDB : http://aws.amazon.com/fr/simpledb//180-2635771-0721067/
• mongoDB : http://www.mongodb.org/
• Cassandra : http://cassandra.apache.org/
• CouchDB : http://couchdb.apache.org/
• Tokyo Cabinet : http://fallabs.com/
• Riak : http://basho.com/
• Hadoop : http://hadoop.apache.org/
• Tarantool : http://tarantool.org/
• memcached : http://memcached.org/
• MySQL Sandbox : http://mysqlsandbox.net/
• openark kit : http://code.openark.org/forge/openark-kit
• Redis : http://redis.io/
• Ganglia : http://ganglia.sourceforge.net/
• HandlerSocket : https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL is developing rapidly.  And today it does not only happen along a single track but in multiple directions simultaneously.

This is due to both server ‘forks‘ as well as various plugins and 3rd party storage engines available.   Each of those will often add  information that can be fetched from the server – typically in the form of additional Information_Schema tables. This content can now also be utilized for monitoring MySQL servers with MONyog. MONyog 4.8  implements monitoring based on Custom SQL Objects.  It is basically just a SQL query. Any query returning a result set can be used for monitoring.  MONyog will collect results from the query and you may define counters on it exactly as you want.

It is really very simple.  In this example we will utilize the `accounts` table that was added to the `performance_schema` database in MySQL 5.6.3 (but you could also use the ‘user statisitics plugin’ originally published as a Google patch and now distributed with MariaDB) . Basically only two steps are required:

First step: Navigate to TOOLs tab .. Customization .. Manage Custom SQL Objects .. Add/Edit Custom SQL Objects and here enter the query. Note that you must also define a unique column (or a unique set of columns).

Also define the sample interval and the retention timeframe for this particular query and on what MySQL servers the query should be executed.  MONyog will now start collecting the data as you have specified.

Second step: Navigate to Monitors/Advisors tab .. Add new Monitor/Advisor. The result of the query you defined in first step is exposed as a javascript array to the MONyog javascript engine. Enter the  javascript defining the content, display, formatting etc. of the new counter in order to make it display as you want. Add an advisor text as well  if you want.

You have now created a Custom SQL Counter based on the Custom SQL Object Object that was defined in first step. You may create a new counter group for the new counter what we have done here:

.. but in this particular case it would also make perfect sense to add the new counter to the existing ‘current connections’ group. Also note that if you want to define WARNING and CRITICAL thresholds on a counter and alert on it, the SQL and/or javascript must be framed in such a way that the counter returns a plain number.

We would like to hear your feedback – and not at least how you use this in your environment. What are you monitoring now that you did not before?

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

PlanetMySQL Voting: Vote UP / Vote DOWN

We are delighted to announce the release of MONyog 4.8 GA. This release is all about customization – to be precise Custom SQL Objects (CSO) and Custom SQL Counters (CSC).

The above diagram shows how Monitors & Advisors (MySQL Counters) are populated. MONyog has a repository of SQL Queries which are executed in regular interval of time & the results are stored in SQLite database of MONyog. These results are exposed as JavaScript objects and are referenced to populate Monitors and Advisors (MySQL Counters).

With CSO, you can add your own SQL queries to this repository & customise counters based on that. Discussed below are some cases on how useful CSOs can be.

• Maintenance statements, for eg. CHECK TABLE, REPAIR TABLE etc.
• You could query on Information Schema to monitor the size of a table. You can also configure it to alert when it crosses a threshold.
• Keep yourself updated with the worst performing indexes.
• Third party MySQL forks have some non standard tables. For eg. you can query on Statistics table of Percona MySQL fork to fetch information about unused index.
• You can monitor MySQL clusters too, by querying Ndbinfo Schema.

Refer the following posts for detailed release notes:

• MONyog 4.8 Beta 2 Released
• MONyog 4.8 Beta 1 Released

MONyog customers can download the latest installer from Webyog’s Customer Portal.

To evaluate MONyog, please download the 30-day trial.

We are very excited about this release, and hope you like it. We would love to hear from you.

Regards,
Team MONyog

PlanetMySQL Voting: Vote UP / Vote DOWN

Regardless of the monitoring tool you use to monitor your databases, it can be better to monitor this tool.
No, it’s not a joke ! Do you think you can have a benefit with a monitoring tool not connected to your servers ? ( without being alerted )

I choose to talk about MONyog here but this can apply to all existing monitoring tools.
I just want to share the message, the tool does not matter, so, do it !

So, let me explain how to control if you have fresh data with MONyog.
With MONyog it’s easy because it’s an agentless monitoring tool.

There are two ways to check that :

Per server general info :

 

 

 

 

 

 

For each server, you can find the last data updated date in the Monitors/Advisors tab.
The MySQL Availability in the dashboard is also usefull.

But it’s a per server information, so, that can be boring if you have a lot of databases servers.

General log file :

 

The default location for this file is MONyog_PATH/MONyog.log on your MONyog server.
You can find usefull informations there about MySQL, SFTP or SSH connexion failures for all your databases servers, in one place.

This log can also be used to detect client side errors if you don’t have access to the application servers.

Hope that can help, tell us what is the best method to do that with others monitoring tools like MySQL Enterprise Monitor.

Have a nice week-end.

PlanetMySQL Voting: Vote UP / Vote DOWN

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.

PlanetMySQL Voting: Vote UP / Vote DOWN