Percona XtraDB Information_schema Changing

I've been working a lot with xtradb versions of mysql over the last year, and more and more have begun to attempt to leverage the information_schema instrumentation that has been put in place. 

Today, I went into a system with this install (admittedly a very recent one) 

Server version: 5.1.51-rel11.5-log Percona Server (GPL), 11.5, Revision 132

Having been considering upgrading RAM on the server, I went to look at how the innodb_buffer_pool was currently being used.   The information_schema tables seem to have changed dramatically, particularly around the innodb_buffer_pool related tables found here.

At this point, I'm at a loss of how to get information on which tables/indexes are using space (and how much) in the buffer pool.  I had used INNODB_BUFFER_POOL_PAGES_INDEX to do this in the past.  Hopefully documentation will be forthcoming on the new table structures.  Admittedly, I haven't dug too deeply into the new structures (since they are not documented).  I'm curious if anyone else has worked with the newer table structures:

INNODB_BUFFER_POOL_PAGES_INDEX\G
*************************** 1. row ***************************
       Table: INNODB_BUFFER_POOL_PAGES_INDEX
Create Table: CREATE TEMPORARY TABLE `INNODB_BUFFER_POOL_PAGES_INDEX` (
  `index_id` bigint(21) unsigned NOT NULL DEFAULT '0',
  `space_id` bigint(21) unsigned NOT NULL DEFAULT '0',
  `page_no` bigint(21) unsigned NOT NULL DEFAULT '0',
  `n_recs` bigint(21) unsigned NOT NULL DEFAULT '0',
  `data_size` bigint(21) unsigned NOT NULL DEFAULT '0',
  `hashed` bigint(21) unsigned NOT NULL DEFAULT '0',
  `access_time` bigint(21) unsigned NOT NULL DEFAULT '0',
  `modified` bigint(21) unsigned NOT NULL DEFAULT '0',
  `dirty` bigint(21) unsigned NOT NULL DEFAULT '0',
  `old` bigint(21) unsigned NOT NULL DEFAULT '0',
  `lru_position` bigint(21) unsigned NOT NULL DEFAULT '0',
  `fix_count` bigint(21) unsigned NOT NULL DEFAULT '0',
  `flush_type` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8

Comments

Hi,  There was performance issue with old design as finding name of the table by ID was taking a lot of time.   Now you need to do the join to couple of others table to get the same information but it works a lot faster for very large number of tables because MySQL can use Join Buffer.  I need to get some VIEWs created so it is easy to use

Thu, 11/11/2010 - 21:55

Thanks Morgan! Much appreciated.

Laine Campbell
Thu, 11/11/2010 - 16:11

You've hit this bug: https://bugs.launchpad.net/percona-server/+bug/576041It was documented as a bug fix in the release notes - http://www.percona.com/docs/wiki/percona-server:release_notes_51#release... I hit the bug myself... within 3 days of 11.3 being released.  This I_S feature is seriously awesome.

Thu, 11/11/2010 - 15:23

Reply

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.
Website by Digital Loom