Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

enter image description here

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?

share|improve this question

3 Answers 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.

share|improve this answer
    
+1, great info on SHOW GLOBAL vs SHOW. –  ceejayoz Apr 16 '12 at 21:13
    
+1 indeed on SHOW GLOBAL vs SHOW –  user64204 Apr 17 '12 at 8:55

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.

share|improve this answer
    
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? –  user64204 Apr 16 '12 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. –  John Gardeniers Apr 16 '12 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 '12 at 21:12
    
@ceejayoz, and yet it pretty consistently gets it wrong. –  John Gardeniers Apr 16 '12 at 23:58
    
@JohnGardeniers For example...? –  ceejayoz Apr 17 '12 at 1:32
up vote 0 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.