phpmyadmin/libraries/engines/innodb.lib.php

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