Percentage Of Full Table Scans

[Tomer Ratz]

Hi

To calculate “Percentage of full table scans” you use the formula:

(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)

But this formula is useless in heavy-loaded systems because “Handler_read_rnd_next” is reset to zero when reached the value of 2^32 (4G).

In that case the percentage drops and resuming its climb up again.

The value in my system is usually 20-45 when in around 45% the Handler_read_rnd_next counter is reseta nd the value returns to be around 20%.

When I look in the graph near this monitor I see that the real percentage is around 80% (with a single drop to 1% when the counter resets, and then back to ~80%).

How do you calculate these 80% full table scans?

[peterlaursen]

There is no other way I can see! MONyog cannot 'invent' what the server does not expose.

If MySQL uses a 32 bit integer and not a 64 bit integer for stroign this variable it is an issue with the MySQL server. On what platform do you run MySQL and is the server a 32bit or 64bit server? If you use a 32bit server it may hep to use a 64bit one instead. But I cannot guarantee this.

We already have posted this (verified) bug report to MySQL: http://bugs.mysql.com/bug.php?id=42698

[peterlaursen]

The formula used is

(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)

You can see all formulas used in the 'Monitors/Advisors' page when clicking the specific counter.

[Tomer Ratz]

So maybe I've hit a bug in Monyog, because the result I get is corrrect from the formula, but the graph show a whole different result.

The result is around 20-40% but the graph shows 80%.

[peterlaursen]

OK .. we'll check. What MONyog version do you use?

[Tomer Ratz]

version 4.51

I'm uploading 2 screenshots.

[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.

[Tomer Ratz]

Can you please help me with the way you calculate the graph using deltas?

Do you poll every minute and divide by time, or something like this?

[peterlaursen]

You probably know that MONyog has its own embedded (SQLite) database.

Every time we fetch data from the server (as you have defined the 'sample interval' in server registration) a row is added here for each value that differs from what that value was before. The calculation is written as a SELECT query against the embedded database. Those queries are very complex (using SUBQUERIES, JOINs on derived tables, aggregates etc.).

In the MONyog documentation you will find an discription of the MONyog database Schema' and álso a query example.

[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)

[Tomer Ratz]

'Mahesh' wrote:

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)

Thank you for your response.

I got it already, though I would really like to know how you calculate a delta for Percentage Of Full Table Scans, as it's a rather complicated formula, not just a reduction.

[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.

[peterlaursen]

The DELTA values are not handled by a query to MySQL but by queries to MONyog's embedded (SQLite) database (and with possible 'sanitation' in the MONyog C++ code before sending to the HTTP interface).

[Author]

Posts