Jump to content


Photo

Percentage Of Full Table Scans


  • Please log in to reply
12 replies to this topic

#1 Tomer Ratz

Tomer Ratz

    Member

  • Members
  • PipPip
  • 24 posts

Posted 25 May 2011 - 08:11 AM

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?

#2 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 25 May 2011 - 09:27 AM

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





Computers make your grey hair come off ....

Peter Laursen
Webyog

#3 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 25 May 2011 - 09:39 AM

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.


Computers make your grey hair come off ....

Peter Laursen
Webyog

#4 Tomer Ratz

Tomer Ratz

    Member

  • Members
  • PipPip
  • 24 posts

Posted 25 May 2011 - 10:49 AM

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

#5 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 25 May 2011 - 11:15 AM

OK .. we'll check. What MONyog version do you use?
Computers make your grey hair come off ....

Peter Laursen
Webyog

#6 Tomer Ratz

Tomer Ratz

    Member

  • Members
  • PipPip
  • 24 posts

Posted 25 May 2011 - 11:19 AM

version 4.51
I'm uploading 2 screenshots.

Attached Files



#7 Mahesh

Mahesh

    Advanced Member

  • Admin
  • PipPipPip
  • 491 posts
  • Gender:Male
  • Location:Bangalore,India
  • Interests:MySQL, Cricket, Music..

Posted 25 May 2011 - 12:19 PM

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.

Attached Files



#8 Tomer Ratz

Tomer Ratz

    Member

  • Members
  • PipPip
  • 24 posts

Posted 25 May 2011 - 01:08 PM

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?

#9 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 25 May 2011 - 01:21 PM

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.


Computers make your grey hair come off ....

Peter Laursen
Webyog

#10 Mahesh

Mahesh

    Advanced Member

  • Admin
  • PipPipPip
  • 491 posts
  • Gender:Male
  • Location:Bangalore,India
  • Interests:MySQL, Cricket, Music..

Posted 25 May 2011 - 01:45 PM

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
<Value at 12:00:05> - <Value at 12:00:00> (this applies to all status variables)

#11 Tomer Ratz

Tomer Ratz

    Member

  • Members
  • PipPip
  • 24 posts

Posted 06 September 2011 - 09:56 AM

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
<Value at 12:00:05> - <Value at 12:00:00> (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.

#12 Mahesh

Mahesh

    Advanced Member

  • Admin
  • PipPipPip
  • 491 posts
  • Gender:Male
  • Location:Bangalore,India
  • Interests:MySQL, Cricket, Music..

Posted 07 September 2011 - 04:42 AM

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 <12:00:05>, then
MONyog considers difference between status variables <Value at 12:00:05> - <Value at 12:00:00>.

You can check yourself by executing
show global status; <take snapshot of all status variable @ 12:00:00>
and again execute the same query after 5 minutes <take snapshot of all status variable @ 12:00:05>

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

#13 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 26 November 2011 - 10:39 AM

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).
Computers make your grey hair come off ....

Peter Laursen
Webyog




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users