Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 72 10 Older Entries

Displaying posts with tag: information_schema (reset)

Fun with Bugs #36 - Bugs fixed in MySQL 5.6.25
+0 Vote Up -0Vote Down

Two days ago Oracle had released MySQL 5.6.25, so it's time to check what bugs reported by MySQL Community are fixed there. As usual, I'll mention both a bug reporter and engineer who verified the bug. Please, pay attention to fixes in replication and partitioning - if you use these features (or queries to INFORMATION_SCHEMA with a lot of complex tables in your database), please, consider upgrading ASAP.

The following …

  [Read more...]
Checking table definition consistency with mysqldiff
+0 Vote Up -0Vote Down

Data inconsistencies in replication environments are a pretty common. There are lots of posts that explain how to fix those using pt-table-checksum and pt-table-sync. Usually we only care about the data but from time to time we receive this question in support:

How can I check the table definition consistency between servers?

Replication also …

  [Read more...]
How well does your table fit in the InnoDB buffer pool in MySQL 5.6+?
+3 Vote Up -0Vote Down

Some time ago, Peter Zaitsev posted a blog titled “How well does your table fits in innodb buffer pool?” He used some special INFORMATION_SCHEMA tables developed for Percona Server 5.1 to report how much of each InnoDB table and index resides in your buffer pool.

As Peter pointed out, you can use this view into the buffer pool to watch a buffer pool warm up with pages as you run queries. You can also use it for capacity planning. If you expect some tables need to be fully loaded in the buffer pool to be …

  [Read more...]
When your query is blocked, but there is no blocking query - Part 3
+0 Vote Up -0Vote Down

In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions.

In this post I will show you how to get even more information about what is locked by a transaction.

As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the …



  [Read more...]
When your query is blocked, but there is no blocking query
+1 Vote Up -0Vote Down

When I queried information_schema.innodb_trx (introduced in 5.1 with the InnoDB Plugin) I noticed there were a few transactions in LOCK WAIT state.

Example:

mysql [information_schema] > select trx_id,trx_state 
    -> from information_schema.innodb_trx;
+--------+-----------+
| trx_id | trx_state |
+--------+-----------+
| 7974 | LOCK WAIT |
| 7962 | RUNNING |
+--------+-----------+
2 rows in set (0.00 sec)


Then I made a query to join a few information_schema and performance_schema tables to find out which query was blocking my …












  [Read more...]
Fun with Bugs #30 - quick review of my reports in February, 2014
+0 Vote Up -0Vote Down

I've got only one comment to my previous post about deadlock, and it was more like a hint based on a different use case, not a real explanation. So far there is nobody who wants to get free beer... Maybe this is even good, as I do not go to the conference and BOF I've submitted will be supervised by my colleague Przemysław Malkowski. But you still have entire month till the conference …

  [Read more...]
innodb_stats_on_metadata and slow queries on INFORMATION_SCHEMA
+2 Vote Up -0Vote Down

INFORMATION_SCHEMA is usually the place to go when you want to get facts about a system (how many tables do we have? what are the 10 largest tables? What is data size and index size for table t?, etc). However it is also quite common that such queries are very slow and create lots of I/O load. Here is a tip to avoid theses hassles: set innodb_stats_on_metadata to OFF.

This is a topic we already talked about, but given the number of systems suffering from …

  [Read more...]
Persistent statistics and partitions
+1 Vote Up -0Vote Down

Today when I was studying for the MySQL 5.6 exams.

I was studying for these two items:

  • Create and utilize table partitioning
  • Obtain MySQL metadata from INFORMATION_SCHEMA tables


The first step is to create a table, partition it with a hash.

mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
-> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
-> …









  [Read more...]
common_schema & openark-kit in the media: #DBHangOps, OurSQL
+0 Vote Up -0Vote Down

#DBHangOps

I had the pleasure of joining into @DBHangOps today, and speak about common_schema and openark-kit. What was meant to be a 15 minute session turned to be 50 -- sorry, people, I don't talk as much at home, but when it comes to my pet projects...

I also realized I was missing on a great event: DBHangOps is a hangout where you can chat and discuss MySQL & related technologies with friends and colleagues, with …

  [Read more...]
Calculating the InnoDB free space - part 2
+0 Vote Up -0Vote Down

This is part 2, you can find part 1 here.

So in part 1 we learned how to calculate the free space within InnoDB. But unfortunately that won't always work perfectly.

The first issue: the DATA_FREE column in the INFORMATION_SCHEMA.TABLES table will not show a sum of the free space of each partition. This means that if you have innodb_file_per_table disabled and are using partitioning then you must divide DATA_FREE by the number of partitions.
This is …




  [Read more...]
Showing entries 1 to 10 of 72 10 Older Entries

Planet MySQL © 1995, 2015, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.