libraries/engines/innodb.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * @version $Id$
   5  * @package phpMyAdmin-Engines
   6  */
   7
   8 /**
   9  *
  10  * @package phpMyAdmin-Engines
  11  */
  12 class PMA_StorageEngine_innodb extends PMA_StorageEngine
  13 {
  14     /**
  15      * @uses    $GLOBALS['strInnoDBDataHomeDir']
  16      * @uses    $GLOBALS['strInnoDBDataHomeDirDesc']
  17      * @uses    $GLOBALS['strInnoDBDataFilePath']
  18      * @uses    $GLOBALS['strInnoDBAutoextendIncrement']
  19      * @uses    $GLOBALS['strInnoDBAutoextendIncrementDesc']
  20      * @uses    $GLOBALS['strInnoDBBufferPoolSize']
  21      * @uses    $GLOBALS['strInnoDBBufferPoolSizeDesc']
  22      * @uses    PMA_ENGINE_DETAILS_TYPE_NUMERIC
  23      * @uses    PMA_ENGINE_DETAILS_TYPE_SIZE
  24      * @return  array
  25      */
  26     function getVariables()
  27     {
  28         return array(
  29             'innodb_data_home_dir' => array(
  30                 'title' => $GLOBALS['strInnoDBDataHomeDir'],
  31                 'desc'  => $GLOBALS['strInnoDBDataHomeDirDesc'],
  32             ),
  33             'innodb_data_file_path' => array(
  34                 'title' => $GLOBALS['strInnoDBDataFilePath'],
  35             ),
  36             'innodb_autoextend_increment' => array(
  37                 'title' => $GLOBALS['strInnoDBAutoextendIncrement'],
  38                 'desc'  => $GLOBALS['strInnoDBAutoextendIncrementDesc'],
  39                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
  40             ),
  41             'innodb_buffer_pool_size' => array(
  42                 'title' => $GLOBALS['strInnoDBBufferPoolSize'],
  43                 'desc'  => $GLOBALS['strInnoDBBufferPoolSizeDesc'],
  44                 'type'  => PMA_ENGINE_DETAILS_TYPE_SIZE,
  45             ),
  46             'innodb_additional_mem_pool_size' => array(
  47                 'title' => 'innodb_additional_mem_pool_size',
  48                 'type'  => PMA_ENGINE_DETAILS_TYPE_SIZE,
  49             ),
  50             'innodb_buffer_pool_awe_mem_mb' => array(
  51                 'type'  => PMA_ENGINE_DETAILS_TYPE_SIZE,
  52             ),
  53             'innodb_checksums' => array(
  54             ),
  55             'innodb_commit_concurrency' => array(
  56             ),
  57             'innodb_concurrency_tickets' => array(
  58                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
  59             ),
  60             'innodb_doublewrite' => array(
  61             ),
  62             'innodb_fast_shutdown' => array(
  63             ),
  64             'innodb_file_io_threads' => array(
  65                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
  66             ),
  67             'innodb_file_per_table' => array(
  68             ),
  69             'innodb_flush_log_at_trx_commit' => array(
  70             ),
  71             'innodb_flush_method' => array(
  72             ),
  73             'innodb_force_recovery' => array(
  74             ),
  75             'innodb_lock_wait_timeout' => array(
  76                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
  77             ),
  78             'innodb_locks_unsafe_for_binlog' => array(
  79             ),
  80             'innodb_log_arch_dir' => array(
  81             ),
  82             'innodb_log_archive' => array(
  83             ),
  84             'innodb_log_buffer_size' => array(
  85                 'type'  => PMA_ENGINE_DETAILS_TYPE_SIZE,
  86             ),
  87             'innodb_log_file_size' => array(
  88                 'type'  => PMA_ENGINE_DETAILS_TYPE_SIZE,
  89             ),
  90             'innodb_log_files_in_group' => array(
  91                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
  92             ),
  93             'innodb_log_group_home_dir' => array(
  94             ),
  95             'innodb_max_dirty_pages_pct' => array(
  96                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
  97             ),
  98             'innodb_max_purge_lag' => array(
  99             ),
 100             'innodb_mirrored_log_groups' => array(
 101                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
 102             ),
 103             'innodb_open_files' => array(
 104                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
 105             ),
 106             'innodb_support_xa' => array(
 107             ),
 108             'innodb_sync_spin_loops' => array(
 109                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
 110             ),
 111             'innodb_table_locks' => array(
 112                 'type'  => PMA_ENGINE_DETAILS_TYPE_BOOLEAN,
 113             ),
 114             'innodb_thread_concurrency' => array(
 115                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
 116             ),
 117             'innodb_thread_sleep_delay' => array(
 118                 'type'  => PMA_ENGINE_DETAILS_TYPE_NUMERIC,
 119             ),
 120          );
 121     }
 122
 123     /**
 124      * @return  string  SQL query LIKE pattern
 125      */
 126     function getVariablesLikePattern()
 127     {
 128         return 'innodb\\_%';
 129     }
 130
 131     /**
 132      * @uses    $this->support
 133      * @uses    PMA_ENGINE_SUPPORT_YES
 134      * @uses    $GLOBALS['strBufferPool']
 135      * @uses    $GLOBALS['strInnodbStat']
 136      * @return  array   detail pages
 137      */
 138     function getInfoPages()
 139     {
 140         if ($this->support < PMA_ENGINE_SUPPORT_YES) {
 141             return array();
 142         }
 143         $pages = array();
 144         $pages['Bufferpool'] = $GLOBALS['strBufferPool'];
 145         $pages['Status'] = $GLOBALS['strInnodbStat'];
 146         return $pages;
 147     }
 148
 149     /**
 150      * returns html tables with stats over inno db buffer pool
 151      *
 152      * @uses    PMA_DBI_fetch_result()
 153      * @uses    PMA_formatNumber()
 154      * @uses    PMA_formatByteDown()
 155      * @uses    $GLOBALS['strBufferPoolUsage']
 156      * @uses    $GLOBALS['strTotalUC']
 157      * @uses    $GLOBALS['strInnoDBPages']
 158      * @uses    $GLOBALS['strFreePages']
 159      * @uses    $GLOBALS['strDirtyPages']
 160      * @uses    $GLOBALS['strDataPages']
 161      * @uses    $GLOBALS['strPagesToBeFlushed']
 162      * @uses    $GLOBALS['strBusyPages']
 163      * @uses    $GLOBALS['strLatchedPages']
 164      * @uses    $GLOBALS['strBufferPoolActivity']
 165      * @uses    $GLOBALS['strReadRequests']
 166      * @uses    $GLOBALS['strWriteRequests']
 167      * @uses    $GLOBALS['strBufferReadMisses']
 168      * @uses    $GLOBALS['strBufferWriteWaits']
 169      * @uses    $GLOBALS['strBufferReadMissesInPercent']
 170      * @uses    $GLOBALS['strBufferWriteWaitsInPercent']
 171      * @uses    join()
 172      * @uses    htmlspecialchars()
 173      * @uses    PMA_formatNumber()
 174      * @return  string  html table with stats
 175      */
 176     function getPageBufferpool()
 177     {
 178         // rabus: The following query is only possible because we know
 179         // that we are on MySQL 5 here (checked above)!
 180         // side note: I love MySQL 5 for this. :-)
 181         $sql = '
 182              SHOW STATUS
 183             WHERE Variable_name LIKE \'Innodb\\_buffer\\_pool\\_%\'
 184                OR Variable_name = \'Innodb_page_size\';';
 185         $status = PMA_DBI_fetch_result($sql, 0, 1);
 186
 187         $output = '<table class="data" id="table_innodb_bufferpool_usage">' . "\n"
 188                 . '    <caption class="tblHeaders">' . "\n"
 189                 . '        ' . $GLOBALS['strBufferPoolUsage'] . "\n"
 190                 . '    </caption>' . "\n"
 191                 . '    <tfoot>' . "\n"
 192                 . '        <tr>' . "\n"
 193                 . '            <th colspan="2">' . "\n"
 194                 . '                ' . $GLOBALS['strTotalUC'] . "\n"
 195                 . '                : ' . PMA_formatNumber(
 196                         $status['Innodb_buffer_pool_pages_total'], 0)
 197                 . '&nbsp;' . $GLOBALS['strInnoDBPages']
 198                 . ' / '
 199                 . join('&nbsp;',
 200                     PMA_formatByteDown($status['Innodb_buffer_pool_pages_total'] * $status['Innodb_page_size'])) . "\n"
 201                 . '            </th>' . "\n"
 202                 . '        </tr>' . "\n"
 203                 . '    </tfoot>' . "\n"
 204                 . '    <tbody>' . "\n"
 205                 . '        <tr class="odd">' . "\n"
 206                 . '            <th>' . $GLOBALS['strFreePages'] . '</th>' . "\n"
 207                 . '            <td class="value">'
 208                 . PMA_formatNumber($status['Innodb_buffer_pool_pages_free'], 0)
 209                 . '</td>' . "\n"
 210                 . '        </tr>' . "\n"
 211                 . '        <tr class="even">' . "\n"
 212                 . '            <th>' . $GLOBALS['strDirtyPages'] . '</th>' . "\n"
 213                 . '            <td class="value">'
 214                 . PMA_formatNumber($status['Innodb_buffer_pool_pages_dirty'], 0)
 215                 . '</td>' . "\n"
 216                 . '        </tr>' . "\n"
 217                 . '        <tr class="odd">' . "\n"
 218                 . '            <th>' . $GLOBALS['strDataPages'] . '</th>' . "\n"
 219                 . '            <td class="value">'
 220                 . PMA_formatNumber($status['Innodb_buffer_pool_pages_data'], 0) . "\n"
 221                 . '</td>' . "\n"
 222                 . '        </tr>' . "\n"
 223                 . '        <tr class="even">' . "\n"
 224                 . '            <th>' . $GLOBALS['strPagesToBeFlushed'] . '</th>' . "\n"
 225                 . '            <td class="value">'
 226                 . PMA_formatNumber($status['Innodb_buffer_pool_pages_flushed'], 0) . "\n"
 227                 . '</td>' . "\n"
 228                 . '        </tr>' . "\n"
 229                 . '        <tr class="odd">' . "\n"
 230                 . '            <th>' . $GLOBALS['strBusyPages'] . '</th>' . "\n"
 231                 . '            <td class="value">'
 232                 . PMA_formatNumber($status['Innodb_buffer_pool_pages_misc'], 0) . "\n"
 233                 . '</td>' . "\n"
 234                 . '        </tr>';
 235
 236             // not present at least since MySQL 5.1.40
 237             if (isset($status['Innodb_buffer_pool_pages_latched'])) {
 238                 $output .= '        <tr class="even">'
 239                 . '            <th>' . $GLOBALS['strLatchedPages'] . '</th>'
 240                 . '            <td class="value">'
 241                 . PMA_formatNumber($status['Innodb_buffer_pool_pages_latched'], 0)
 242                 . '</td>'
 243                 . '        </tr>';
 244             }
 245
 246             $output .= '    </tbody>' . "\n"
 247                 . '</table>' . "\n\n"
 248                 . '<table class="data" id="table_innodb_bufferpool_activity">' . "\n"
 249                 . '    <caption class="tblHeaders">' . "\n"
 250                 . '        ' . $GLOBALS['strBufferPoolActivity'] . "\n"
 251                 . '    </caption>' . "\n"
 252                 . '    <tbody>' . "\n"
 253                 . '        <tr class="odd">' . "\n"
 254                 . '            <th>' . $GLOBALS['strReadRequests'] . '</th>' . "\n"
 255                 . '            <td class="value">'
 256                 . PMA_formatNumber($status['Innodb_buffer_pool_read_requests'], 0) . "\n"
 257                 . '</td>' . "\n"
 258                 . '        </tr>' . "\n"
 259                 . '        <tr class="even">' . "\n"
 260                 . '            <th>' . $GLOBALS['strWriteRequests'] . '</th>' . "\n"
 261                 . '            <td class="value">'
 262                 . PMA_formatNumber($status['Innodb_buffer_pool_write_requests'], 0) . "\n"
 263                 . '</td>' . "\n"
 264                 . '        </tr>' . "\n"
 265                 . '        <tr class="odd">' . "\n"
 266                 . '            <th>' . $GLOBALS['strBufferReadMisses'] . '</th>' . "\n"
 267                 . '            <td class="value">'
 268                 . PMA_formatNumber($status['Innodb_buffer_pool_reads'], 0) . "\n"
 269                 . '</td>' . "\n"
 270                 . '        </tr>' . "\n"
 271                 . '        <tr class="even">' . "\n"
 272                 . '            <th>' . $GLOBALS['strBufferWriteWaits'] . '</th>' . "\n"
 273                 . '            <td class="value">'
 274                 . PMA_formatNumber($status['Innodb_buffer_pool_wait_free'], 0) . "\n"
 275                 . '</td>' . "\n"
 276                 . '        </tr>' . "\n"
 277                 . '        <tr class="odd">' . "\n"
 278                 . '            <th>' . $GLOBALS['strBufferReadMissesInPercent'] . '</th>' . "\n"
 279                 . '            <td class="value">'
 280                 . ($status['Innodb_buffer_pool_read_requests'] == 0
 281                     ? '---'
 282                     : htmlspecialchars(PMA_formatNumber($status['Innodb_buffer_pool_reads'] * 100 / $status['Innodb_buffer_pool_read_requests'], 3, 2)) . ' %') . "\n"
 283                 . '</td>' . "\n"
 284                 . '        </tr>' . "\n"
 285                 . '        <tr class="even">' . "\n"
 286                 . '            <th>' . $GLOBALS['strBufferWriteWaitsInPercent'] . '</th>' . "\n"
 287                 . '            <td class="value">'
 288                 . ($status['Innodb_buffer_pool_write_requests'] == 0
 289                     ? '---'
 290                     : htmlspecialchars(PMA_formatNumber($status['Innodb_buffer_pool_wait_free'] * 100 / $status['Innodb_buffer_pool_write_requests'], 3, 2)) . ' %') . "\n"
 291                 . '</td>' . "\n"
 292                 . '        </tr>' . "\n"
 293                 . '    </tbody>' . "\n"
 294                 . '</table>' . "\n";
 295         return $output;
 296     }
 297
 298     /**
 299      * returns InnoDB status
 300      *
 301      * @uses    htmlspecialchars()
 302      * @uses    PMA_DBI_fetch_value()
 303      * @return  string  result of SHOW INNODB STATUS inside pre tags
 304      */
 305     function getPageStatus()
 306     {
 307         return '<pre id="pre_innodb_status">' . "\n"
 308             . htmlspecialchars(PMA_DBI_fetch_value('SHOW INNODB STATUS;', 0, 'Status')) . "\n"
 309             . '</pre>' . "\n";
 310     }
 311
 312     /**
 313      * returns content for page $id
 314      *
 315      * @uses    $this->getInfoPages()
 316      * @uses    array_key_exists()
 317      * @param   string  $id page id
 318      * @return  string  html output
 319      */
 320     function getPage($id)
 321     {
 322         if (! array_key_exists($id, $this->getInfoPages())) {
 323             return false;
 324         }
 325
 326         $id = 'getPage' . $id;
 327
 328         return $this->$id();
 329     }
 330
 331     /**
 332      * returns string with filename for the MySQL helppage
 333      * about this storage engne
 334      *
 335      * @return  string  mysql helppage filename
 336      */
 337     function getMysqlHelpPage()
 338     {
 339         return 'innodb';
 340     }
 341
 342     /**
 343      *
 344      * Gets the InnoDB plugin version number
 345      * http://www.innodb.com/products/innodb_plugin
 346      * (do not confuse this with phpMyAdmin's storage engine plugins!)
 347      *
 348      * @return string the version number, or empty if not running as a plugin
 349      */
 350     function getInnodbPluginVersion()
 351     {
 352         return PMA_DBI_fetch_value('SELECT @@innodb_version;');
 353     }
 354
 355     /**
 356      *
 357      * Gets the InnoDB file format
 358      * (works only for the InnoDB plugin)
 359      * http://www.innodb.com/products/innodb_plugin
 360      * (do not confuse this with phpMyAdmin's storage engine plugins!)
 361      *
 362      * @return string the InnoDB file format
 363      */
 364     function getInnodbFileFormat()
 365     {
 366         return PMA_DBI_fetch_value("SHOW GLOBAL VARIABLES LIKE 'innodb_file_format';", 0, 1);
 367     }
 368
 369     /**
 370      *
 371      * Verifies if this server supports the innodb_file_per_table feature
 372      * (works only for the InnoDB plugin)
 373      * http://www.innodb.com/products/innodb_plugin
 374      * (do not confuse this with phpMyAdmin's storage engine plugins!)
 375      *
 376      * @return boolean whether this feature is supported or not
 377      */
 378     function supportsFilePerTable()
 379     {
 380         $innodb_file_per_table = PMA_DBI_fetch_value("SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';", 0, 1);
 381         if ($innodb_file_per_table == 'ON') {
 382             return true;
 383         } else {
 384             return false;
 385         }
 386
 387     }
 388 }
 389
 390 ?>