[Sayan Chaliha]

Hi Marc,

Glad to know 🙂 … Query optimization is very dependent on the type of application, how the tables are organized, what types of queries are being used (like SELECTs, sub-SELECTS, JOINs, etc). and the like. It can actually get quite difficult to figure out what generic MySQL parameters can be tuned to speed up query execution. But good you figured it out! Was MONyog of any help while tuning your server? Do you have any suggestions for improvement?

[Sayan Chaliha]

Hi Marc,

Yes, the e-mails sent by MONyog were missing the date header. It's been fixed, and will feature in the next release of MONyog (scheduled for mid next week). In case you need the fix immediately please let us know and we'll give you a special build.

I'll get back to you on the cache sizes…

[Sayan Chaliha]

Hi Marc,

Here are some quick thoughts to add to my co-worker's:

'marcm' wrote:

time taken 11
SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA

This particular query is executed by MONyog when you open the “Disk Info” page. It is definitely heavy on the server but is only executed on-demand, once, when you open/refresh the “Disk Info” page. If you use the Disk Info feature of MONyog sparingly, this query shouldn't be executed much on the server.

'marcm' wrote:

long_query_time = 1

A long_query_time of 1 sec is not generally recommended for most applications. What it will do is log all queries taking longer than 1 sec in MySQL's slow query log. Setting it to 1 sec for a database server serving as a back-end to an actively used application would be almost equivalent to enabling the general query log–the implication is lot of extra processing on MySQL. A real-world application will have billions of rows of data and queries might generally average more than 1 sec to execute. MySQL's default is 10 secs. You can set it to whatever suits you, but 1 sec is a little unrealistic :).

The reason why you're getting so many alerts is related to long_query_time… In MONyog's sniffer options (see screen-shot) you can set the time a query takes to execute for it to be recognized by MONyog as “long running”. Now if you've set it to something like 1 sec, and as it seems most queries take longer than a second to execute, MONyog will keep sending you email alerts on them!

As for the sent time of the emails, it's probably a missing header. We'll look into it and let you know.

From your system configuration and memory usage, it's actually almost a given that MySQL will get slow after a while. Physical memory usage of applications is 100%, and MySQL is already using around 750 MB of virtual memory out of which around 400 MB is resident in physical memory, which means lot memory swaps. Once MySQL's caches fill up, it will need more memory. Two things you could do here: free up system resources and leave them for MySQL; and tune cache sizes.

Tuning cache size is not an exact science. No one can give you a definite value and say “this is optimal”. You should look at the Monitors/Advisors on various cache sizes for tips on how to tune them.

[Sayan Chaliha]

Hi Marc,

Yes, you do need to configure MONyog to able access it. Open MONyog's interface, in the 'List of Servers' page click on 'Register a new server' (or edit an existing registration). In the page that appears next select 'SSH Server Details' on the left and fill in the details. I would've given you a screen-shot of it, but for some reason the forum software isn't letting me upload the image. So do come back here if you're unable to get it working… by that time I'm sure the sys admin would have that fixed.

You wouldn't need to configure a port specifically for MONyog. Since SSH by default runs on port 22, you can just fill that in while editing server details.

[Sayan Chaliha]

Hey Marc,

How you doing? As regards to your problem, do you have SSH enabled for the system? MONyog has a limitation. It still needs SSH access to the server that needs to be monitored, even if it is running on that very same server. I'll make a note of this in our internal issue tracker, and discuss it further with the MONyog dev team.

[Sayan Chaliha]

Hi Ben,

When you delete a registered server, all the data for that server should be completely removed. I'd suggest that you open a support ticket here so that support engineers will be better able to help you (note that all information that you share in the forums is public while every bit of communication in the support system is kept private).

In the meantime here's a workaround:

— Make a note of the server ID (the 4-digit number)

— Stop MONyog

— In the data folder of MONyog you'll find a folder with the ID as the name — delete it

— Restart MONyog and you shouldn't see that connection again

Before you do delete the data, take a backup. It would definitely help in debugging the issue.

[Sayan Chaliha]

Hey Ben,

Glad that its working for you. Btw, which version of MONyog are you using? Usually not being able to shut MONyog down could mean something's terribly wrong.

[Sayan Chaliha]

Hi Ben,

There is a high possibility that sniffer.data could have gotten corrupted somehow. Otherwise VACUUM should've worked just fine. You could try the following:

1. Disable Sniffer for that particular server.

2. Open sniffer.data using the SQLite3 command line (your Linux box should have it pre-installed):

Code:

sqlite3

3. Next, do an integrity check:

Code:

PRAGMA integrity_check;

4. If the integrity check passes, try running vacuum:

Code:

VACUUM;

Let me know how it goes.

[Sayan Chaliha]

Just one more point to add to Peter's reply: You can check MONyog's documentation for an example script (written in Perl) — section called “The MONyog API”. The script accepts command line parameters (so the same script can be scheduled with different parameters at different times). We use this feature ourselves for our production servers.

[Sayan Chaliha]

Hi Piere,

MONyog needs SSH access to fetch the error log… As of now, it isn't making use of any of the Amazon RDS APIs. It connects to RDS instances as if it were normal MySQL instances. With the query logs however there's a workaround: If you enable logging in MySQL tables instead of disk files, MONyog will be able to retrieve information from them.

Perhaps in the future we will extend MONyog to specifically cater to SaaS databases.

[Sayan Chaliha]

Hi Mario,

Looks like you've run into some kind of bug. Could you please create a support ticket at http://www.webyog.com/support? Once you do that, the dev team will assist you with the problem and resolve it ASAP. You might also be asked to upload the core dump to one of our FTP servers (you'll receive details once you've created a ticket).

[Sayan Chaliha]

Hi Xeonfeng,

We've just released MONyog 4.0 GA. This version of MONyog features different packaging, and it's tested to work even on the latest version of Ubuntu. Go ahead and give it try.

[Sayan Chaliha]

Hey Marc,

Glad that you're liking MONyog! You're always welcome here with any questions you may have. You can also create a support ticket here if you think you've run into problems with MONyog, and rest assured, we'll try our best and address those issues within 24 working hours!

Also please feel free to give us any feedback you may have while using MONyog (you can mail them to [email protected]).

[Sayan Chaliha]

[attachment=1404:slowlog.JPG]

[Sayan Chaliha]

Hey Marc,

Depending on your MySQL server version, you can enable your slow query log from within MONyog's interface (check attached screen-shot; this feature requires MySQL versions 5.1.6 or above).

If you're using an older version of the server, you could add the following to your my.cnf/my.ini:

Code:

slow_query_log=ON
slow_query_log_file= log_slow_queries=ON
long_query_time=
log_queries_not_using_indexes=

As for guidelines on where to store the slow query log file, you should check MySQL's documentation; specifically: http://dev.mysql.com…log-tables.html

Using NOW() is okay. However, you should know that NOW() returns the time when the query began execution, and not the current system time. To get the current system time use SYSDATE() instead.

PS: The screen-shot attachment failed… I'll try and upload it again.

[Author]

Posts