[Mahesh]

Ok .. Consider this example:

I have executed these statements from SQLyog:

prepare stmt1 from 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';

set @a = 3;

set @b = 4;

execute stmt1 using @a, @b;

MySQL General Log entry shows:

9 Query PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'

9 Prepare SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse

9 Query SET @a = 3

9 Query SET @b = 4

9 Query EXECUTE stmt1 USING @a, @b

9 Execute SELECT SQRT(POW(3,2) + POW(4,2)) AS hypotenuse

MONyog shows Type = Query statements in output but it does not show Type = Prepare and Execute.

Means MONyog only parses

PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'

SET @a = 3

SET @b = 4

EXECUTE stmt1 USING @a, @b

and it does not parse Type= Prepare and Execute

Prepare SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse

Execute SELECT SQRT(POW(3,2) + POW(4,2)) AS hypotenuse

** One more thing whatever client executes MONyog records it ( Type= Prepare and Execute are handled by server internally)

Anyway it's a valid request and has been added in our issue tracking system.

Thanks,

Mahesh Patil

[Mahesh]

Currently General Query log output does not show multiline queries.

It is known issue with MySQL There is no way to see where the query ends. We will have to assume that only one line applies.

Issue Report can be viewed here :

http://bugs.mysql.com/bug.php?id=48561

[Mahesh]

It considers 'Browser timezone' (From where you are accessing MONyog interface).

We need to come up with MONyog timezone where the user enters MySQL server time and then we can know the difference between browser timezone and MySQL server timezone and this difference will be added where ever we are using timezones. This should be added in tools->preferences->monyog time zone.

We have not set priority for this yet.

Thanks,

Mahesh

[Mahesh]

I have check against MySQL server v5.5.11

I think we should go ahead with screen sharing session, Please tell which timezone you are in and convinient time to have it ?

For privacy please send us mail at [email protected] mentioning the problem, we will continue conversation in ticket system.

[Mahesh]

You have enabled “Processlist sniffer” to capture real time queries also you are monitoring long running queries,hence you are getting ALERTS for each long running query.

Also are you using “Disk Info” feature frequently because it requires a exepensive query to be executed on a MySQL server “SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) …..(used for calculating Schema size, table size etc..”

I can see that you are getting alerts only for “Long Running Queries” because your server seems to be busy all the time, you can verify by connecting to server and execute “Show Full processlist“?

Also I suggest you to upgrade to latest build and give a try ?

[Mahesh]

There was an issue in IE 8 , Unable to create user, This issue has been fixed in v4.12.

Please upgrade to latest stable version of MONyog and try?

[Mahesh]

Please attach the screen shot of settings applied for new chart.

1) On top -right of chart you can see a Edit button

2) Click on it and send us the screen shot of settings.

[Mahesh]

We have released MONyog v4.5 Beta with this feature:

* In the process-list based sniffer there is now an option to alert (using mail and/or SNMP) for long-lasting LOCKs.

[Mahesh]

The Query Analyzer feature of MONyog helps you identify problem SQL. MONyog can find problem SQL by one or more of the following methods:

* Taking SHOW PROCESSLIST snapshots at regular intervals (using MONyog Sniffer)

* Using MySQL Proxy to collect profiling data (using MONyog Sniffer)

* Parsing Slow Query Log and General Query Log (using MONyog Log Parser)

There are several advantages and disadvantages of each approach.

SHOW PROCESSLIST is available in all MySQL versions and it is the easiest to setup. However, taking a snapshot of SHOW PROCESSLIST does not guarantee that all queries will be captured. Many short-lived queries can be missed between two successive snapshots. It is a quick and easy way to find long running queries.

Log parsing requires some additional setup. Also, switching on the General Query Log puts a significant amount of load on the server. You should always keep the Slow Query Log switched on. Parsing the Slow Query Log is an effective way to find bad queries.

Using MySQL Proxy gives you the most accurate information on profiling SQL. However, during profiling you have to configure your clients to connect to MySQL Proxy, which in turn connects to MySQL server. Using MySQL Proxy ensures that all queries are profiled. It helps you to find problematic queries that don't take much time, but are executed thousands of times. Eliminating such queries can significantly improve the performance of your application.

To use the MONyog Query Analyzer functionality for a specific server, the server 'general query log' or 'slow query log' details must be configured in 'Connection Settings' or a 'query sniffer' must be enabled for that server.

Using the above tools to find problem SQL is almost always a post-mortem excercise. In certain situations you may want real-time notifications for long-running queries. MONyog can continuously monitor queries in real-time and send notifications (on mail or SNMP) for queries that take more than a specified amount of time to execute. You can also specify an option to kill such queries instantly.

Note: The MONyog Sniffer taking snapshots of SHOW PROCESSLIST is different from the the Processlist feature in that the Sniffer retains the information retrieved in a database for generation of reports and further analysis, whereas the Processlist feature just displays that information as is, without manipulating or storing it.

After identifying the problematic queries you have to make sure that proper index technique has been applied, proper datatypes has been used etc..

BTW: SQLyog (MySQL GUI another popular product from Webyog) does have a feature named “Query Profiler” which will help you to profile queries.

[Mahesh]

I have attached a screenshot of enabling Mail Alert for “object changes detected..”

GoTo “MOnitors/Advisors” page select “Commands and Schema changes” group from left — Click on “Object changes detected” counter — Click on “Customize” link — Select “View Advanced” option and — In Mail Alert section Select “Yes” to get an Alert from MONyog.

Please let me know if you need any help.

[Mahesh]

Goto Dashboard Page — Click on “Add new chart” link (Top right of Dashboard page)and make necessary changes as shown in screenshot attached here with.

BTW: We have feature request to add charts automatically in Dashboard page.

[Mahesh]

Also there is a feature named “Query Analyzer”, using this feature you can see detailed report of slow queries.

MONyog reads the slow log file and generates report accordingly in a detailed manner.

Goto — Register server/ Edit Server page — Query Analyzer settings — Give valid path for slow log — Save the connection details.

Select a server — Goto Query Analyzer page — Click On Analyze ( Your detailed report will be generated)

You can change the time interval by using “Change Filter” option.

For more detail please refer to MONyog help file.

http://webyog.com/doc/MONyog/Log_Analyzer_introduction.htm

[Mahesh]

1) Is it possiable to have a chart that show slow queries over time so say for instance if the server starts running slow or even stops at a certain time we can check to see if there was any slow queries ran at that time?

— You can view chart for “NO. of slow queries running” for last 15 data collection interval.

Goto Monitors/Advisors Page — Select MySQL Logs Group — See the chart for “No. of slow queries”

Also if you want to see historical report of “No. of slow queries”, you can view it by selecting History/Trend Analysis tab in Monitors/Advisors page. ( Chart will be generated based on interval).

2) Also can MONyog use the error_logs and access_logs to create charts or reports?

— MONyog does have feature called “Error log monitoring”.

Using this feature user will get notification if there is any error gets logged in error log file also user can see the historical report of errors logged in error log file.

Goto – Monitors/Advisors page — Select MySQl Logs Group — See “Recent entries in Error log” and “Recent entries of type error”

(You have to fill up valid details in “MySQL Error log settings” in Register/Edit Server)

What do you mean by Access_logs ?

MySQL server has these log files mentioned below:

1) Error log — Problems encountered starting, running, or stopping mysqld

2) General query log — Established client connections and statements received from clients

3) Binary log — All statements that change data (also used for replication)

4) Relay log — Data changes received from a replication master server

5) Slow query log — All queries that took more than long_query_time seconds to execute or did not use indexes

Please let us know if you need any help.

Regards,

Mahesh

[Mahesh]

We have this feature request already in our Issue tracker.

“To add an alertable monitoring option for 'long lasting locks' in the Processlist-based sniffer interface.”

BTW: Currently we are logging long running queries which reaches threshold values (default :10 second) in Sniffer also you will get notification of the query details.

(Please check Sniffer Settings — Processlist option for more details)

[Mahesh]

HI,

You can contact to our sales team at '[email protected]' they will assist you regarding this.

[Author]

Posts