2

We're running InnoDB as a MySQL engine and using phpMyadmin to administer our database. Under StatusQuery statistics, phpMyadmin gives us the following:

phpMyAdmin Screenshot

We would like to know where these figures come from because we would like to create a Munin graph showing the evolution of these statistics over time.

When we run the SHOW STATUS; query here is what we get:

Innodb_rows_deleted 247555
Innodb_rows_inserted    822911
Innodb_rows_read    694934413
Innodb_rows_updated 15048

As you can see there is a substantial difference although both were taking almost at the same time.

Q: Do you know where phpMyadmin gets its values from?

3 Answers 3

3

Please note the MySQL Documentation on SHOW STATUS

Looking at the numbers, phpmyadmin must be using SHOW GLOBAL STATUS; because it bring up statistics from the time MySQL was started up.

When you use SHOW STATUS;, that brings back session-level statistics which go wawy when the DB Connections terminates.

2
  • +1, great info on SHOW GLOBAL vs SHOW.
    – ceejayoz
    Apr 16, 2012 at 21:13
  • +1 indeed on SHOW GLOBAL vs SHOW
    – Max
    Apr 17, 2012 at 8:55
0

As RolandoMySQLDBA mentioned, the first thing to bear in mind is the difference between SHOW GLOBAL STATUS and SHOW STATUS (see documentation). Regarding the values displayed by phpMyadmin, they are obtained from the Com_* variables as confirmed by source code:

server_status.php

    $sql = "SELECT concat('Com_', variable_name), variable_value
    FROM data_dictionary.GLOBAL_STATEMENTS
    [...]

    // For the percentage column, use Questions - Connections, because
    // the number of connections is not an item of the Query types
    // but is included in Questions. Then the total of the percentages is 100.
    $name = str_replace(array('Com_', '_'), array('', ' '), $name);

After comparison, it appears that phpMyAdmin is displaying the correct values (i.e. matching what MySQL is reporting).

-1

The best data is obtained by asking MySQL itself. It's also far easier to implement that trying to interface with phpMyAdmin. How phpMyAdmin gets its numbers is anyone's guess but I wouldn't trust them.

8
  • I agree, I guess I could have a look at the source code of phpMyAdmin to find out, but in the end I'm only interested into what MySQL is actually reporting. Are there any other InnoDB`-related variables than the 4 aforementioned I should be paying attention to you think?
    – Max
    Apr 16, 2012 at 10:18
  • @user64204, I suggest you have a look at some of the MySQL "tuning" scripts and use them to help you determine what might be most relevant in your particular situation. You might also gain something from what they can tell you about your system. Apr 16, 2012 at 10:23
  • C'mon, phpMyAdmin gets its numbers from internal MySQL functions just like everything else does. It's just a web client for MySQL.
    – ceejayoz
    Apr 16, 2012 at 21:12
  • @ceejayoz, and yet it pretty consistently gets it wrong. Apr 16, 2012 at 23:58
  • @JohnGardeniers For example...?
    – ceejayoz
    Apr 17, 2012 at 1:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .