[Mahesh]

Code:

Thank you- 5 minutes is a bit too slow to react to problems on our social networking site. We are now using 1 minute as the interval and have seen performance improve, though still slow to “load up” the monitor/advisor page initially. Regarding load MonYOG puts on our database: I am not sure I understand your concern about a 10 second interval on a production server- is monyog performing selects or simply monitoring the process list? Given the hardware specs I posted, would you imagine a 10 second interval is seriously taxing the system?

— It's upto your requirement what data collection time you want to set depending on application.

Considering your environment and fact that sniffer is disabled too, I think 10 second interval should not be a problem

as MONyog just gets stats from server using Show global status and show global variables.

Monitors/Advisors page initially takes time to show counters value because MONyog resolves those counters dynamically and shows in page later you can see there is no delay in showing stats for any counter, only during first time you will see a message like “Getting details..” for few seconds.

Also getting processlist information is involved only if you enable Sniffer –> Processlist option, otherwise MONyog won't collect processlist information.

What I meant is it's of course that 1 second load is GREATER THAN 10 seconds GREATER THAN 1 minute etc..

Still considering your environment 1 seconds interval also MONyog can run without any problem.

Basically MONyog is a very light weight app also we have done some benchmarking and here is detail about it:

Code:

OS Spec: Linux 2.6.9-67.ELsmp #1 SMP x86_64 x86_64 GNU/Linux

System Spec:
CPU model name : Intel(R) Core(TM)2 Duo CPU E8400 @ 3.00GHz
stepping : 10
cpu MHz : 2992.388
Total Memory : 256MB

After Analysis of Zero, One , Two, Five and Ten Server.

Average rate has been calculated as:

CPU remains constant to 0

Only MYSQL is Enabled:

Average Memory used in %/Server = 0.5/server

Average Virtual Memory used/Server = 10m/Server

Resident memory used/Server = n/a

Only MySQL and Sniffer is enabled:

Average Memory used in %/Server = 1.0/server

Average Virtual Memory used/Server = 25m/Server

Resident memory used/Server = 1.5m/server

Only MySQL, Sniffer and OS is enabled:

Average Memory used in %/Server = 1.0/server

Average Virtual Memory used/Server = 32m/Server

Resident memory used/Server = 2.2m/server

Only MySQL, Sniffer, OS and notification is enabled:

Average Memory used in %/Server = 1.0/server

Average Virtual Memory used/Server = 32m/Server

Resident memory used/Server = 2.2m/server

Now Average report when Only Sniffer, OS and Notification is enabled at a time:

Only MYSQL is Enabled:

Average Memory used in %/Server = 0.5/server

Average Virtual Memory used/Server = 10m/Server

Only MYSQL and Sniffer is Enabled:

Average Memory used in %/Server = 1.0/server

Average Virtual Memory used/Server = 30m/Server

Resident memory used/Server = 1.5m/server

Only MYSQL and OS is Enabled:

Average Memory used in %/Server = 0.6/server

Average Virtual Memory used/Server = 21m/Server

Resident memory used/Server = 1m/server

Only MYSQL and Notification is Enabled:

Average Memory used in %/Server = 0.6/server

Average Virtual Memory used/Server = 11m/Server

Resident memory used/Server = 1m/server

(I think you might get some idea from this report)

Code:

I see. No, we are not refreshing, because each page refresh takes a great deal of time to load all of the counters in monitors/advisors and we prefer to watch the counters update automatically.

— As I have mentioned earlier initially on page load MONyog shows “Getting details for server…” after that do not have to refresh screen multiple times you will see updates automatically after specified data collection, you can verify by click on chart as you see chart gets updated.

If any further question please get back to us

Thanks,

Mahesh

[Mahesh]

As mentioned in previous post that for DELTA diffrence between each status variable value is considered:

For Percentage of full table scans status variables used are :

(Handler_read_rnd_next + Handler_read_rnd) / (Handler_read_rnd_next + Handler_read_rnd + Handler_read_first + Handler_read_next + Handler_read_key + Handler_read_prev)

DELTA takes difference between all status variables between last two collections.

For example if data collection interval is 5 minutes and you are viewing Monitors page DELTA context at , then

MONyog considers difference between status variables – .

You can check yourself by executing

show global status;

and again execute the same query after 5 minutes

Now make a note of all variables difference value and put in formula and calculate.

[Mahesh]

which application parameters might decrease the disk i/o ops ?

— I think you here you can increase Data collection interval to 5 minutes

which is default?

Also why you want to have Data collection = 10 second on a Production server ?

If you increase to 5 minutes definitely you will see significant decrease on Disk I/o operations.

Also another point is consistently viewing Monitors/Advisers page, Refreshing it frequently?

You can of course want to see stats about your server at any time,viewing less frequently or refreshing page frequently might decrease Disk I/O operations.

MONyog resolves each counter when you are viewing on Monitors/Adviser page.

We are working on optimizing resolving MONyog counters.

[Mahesh]

If you want to have different threshold values for each server, you have to write condition in Critical/Warning section.

This can be easily done in MONyog :

Let me explain an example :

Consider a counter “Seconds behind master”

For first register server named “TestServer” you want “Critical” value as 1000 and for second register server name “Production” you want “Critical” value as 800.

How to make this changes in MONyog:

1) Click on “Seconds behind master” counter

2) Click on “Customize”

3) Click on “View Advanced”

4) Now clear “Critical” field and fill with this function.

function()

{

if(MONyog.Connections.ConnectionName == “Testserver”)

return 1000;

if(MONyog.Connections.ConnectionName == “Production”)

return 800;

}

5) Now Save the changes.

In this way you can customize MONyog counter per server basis. ( Please refer screen shot)

[Mahesh]

Advantage: You can compare graphs side-by-side for multiple MySQL instance on a Dashboard.

[Mahesh]

We will discuss about this request in our next meeting and update about it.

Regards,

Mahesh

[Mahesh]

Can you please share crash dumps avialable from /usr/local/MONyog/bin along with MONyog.log file.

You can upload to our FTP server:

HostName: gateway.webyog.com

UserName: ftpadmin

Password: webyog123

Also /var/lib/mysql/li258-203.pid' '0' access mode: SFTP server: Permission denied

This means SSH user does not have permission to read PID file, An easy way to ensure this is to add the SSH user to the 'mysql' user group.

I am not sure this permission issue is related to crash.

[Mahesh]

That is at server level view.

Click on “Server Name” ..you will see Databases under it and their Data and index length respectively ..similarly you can further view at table level too.

[Mahesh]

1. How to select the database to be monitored as I have a no of databases on that server but wish to monitor only one.

— MONyog monitors your MySQL server, it does not monitor databases separately.. However MONyog has a feature named “Disk Info” which will let you know the individual Database size

2. How to monitor my DB's performance and later tuning it

— MONyog is already shipped with 200+ best practices advisers, You check for critical/Warning alerts and try to minimize those.. to achieve best performance of your MySQL server.

Also you can find out problematic queries using “Query Analyser” feature, Later you can optimize those by adding indexes, changing data types on a table.

3. How to analyze slow queries.

— MONyog reads slow query log and shows the output in human readable format on QA page.

You have to enable slow query log option in MySQL by configuring my.cnf/my.ini file.

Please read this http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Then you have to specify file path while registering a server in MONyog in “QA settings”.

I suggest you to go through online documentation of MONyog OR refer Help.

http://webyog.com/doc/

[Mahesh]

Ok .. let me explain.

1) For “ALL Time” we are calculating based on current value of each status variable by executing

show global status;

2) For Delta we are considering difference between the values of the variables from last two data collections,

For example, consider this:

At 12:00:00 MONyog has collected data as:

Variable_name Value

---

---

Handler_commit 2552241

Handler_delete 1781614

Handler_discover 0

Handler_prepare 8

Handler_read_first 37670

Handler_read_key 556973055

Handler_read_next 285178627

Handler_read_prev 0

Handler_read_rnd 162380478

Handler_read_rnd_next 789906322

Handler_rollback 25

Handler_savepoint 0

Handler_savepoint_rollback 0

Handler_update 246574243

Handler_write 199241798

and at 12:00:05 MOnyog has collected data as:

Variable_name Value

---

---

Handler_commit 2552241

Handler_delete 1781614

Handler_discover 0

Handler_prepare 8

Handler_read_first 37670

Handler_read_key 556973055

Handler_read_next 385195627

Handler_read_prev 0

Handler_read_rnd 162380478

Handler_read_rnd_next 789906322

Handler_rollback 25

Handler_savepoint 0

Handler_savepoint_rollback 0

Handler_update 246574243

Handler_write 199241798

DELTA will show “Percentage of refused connection” value based on difference of each and every variable as

– (this applies to all status variables)

[Mahesh]

Hi,

We have verified these values at our end by executing a query using a MySQL command line client

show global status like 'handler%';

Also in our case in Monitors/Advisors page Chart value always matches with “Percentage of full table scan” advisor (please see the screen shot)

Note: Chart is generated based on Delta values, I think you have selected “All time” option hence there is a difference between values shows in chart and counter value displayed. Try by selecting “Delta” option and compare the result.

If you still have a problem then we will have a screen sharing session using GoTo meeting tool.

[Mahesh]

We will start now itself. Can you please create a ticket for privacy by sending an email to “[email protected]” ?

(It is better to do screen sharing session in ticket)

[Mahesh]

We will check this at our end and update the status

I suggest we can arrange a screen sharing session using GoToMeeting tool ? Can you please tell which timezone you are in and suitable time to have screen sharing session?

[Mahesh]

No.. currently there is no way to produce report from QA in an Automated way.

You have to go to QA tab — Analyze and then export report manually.

[Mahesh]

I have replied in ticket for privacy, you can join meeting any time today.

[Author]

Posts