Coinciding with the new native data dictionary in MySQL 8.0, we have made a number of useful enhancements to our INFORMATION_SCHEMA subsystem design in MySQL 8.0. In this post I will first go through our legacy implementation as it has stood since MySQL 5.1, and then cover what’s changed.…
MySQL information_schema comes with useful information about the
database instance, status, … etc. which is needed for daily DBA
There are some simple queries on the information_schema that I use on my daily basis in which I’m writing this post for my reference and maybe a good reference for someone else too …
Finding tables without Primary or Unique Keys:
PKs are so important, especially, for InnoDB tables as MySQL uses PKs as a clustered index and having no PKs might lead to severe performance problems.
Also having no PKs is one of the main causes of slave lagging problems mainly when using RBR (Row-Based Replication), e.g. if a delete statement on the master will delete 1 million rows on a table without PK, a full table scan will take place. This “might” not be a problem on the master but on the slave 1 million full table scan will take place – because changes to the individual rows are being …[Read more]
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 allow us to have different table definition between master and slaves. For example, there are some cases that you need some indexes on slaves for querying purposes but are not really needed on the master. There are some other cases where those differences are just a mistake that needs to be fixed.
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 used efficiently, but the buffer pool isn’t large enough to hold them, then it’s time to increase the size of the buffer pool.
The problem, however, was that system tables change from version to version. Specifically, the INNODB_BUFFER_POOL_PAGES_INDEX table no longer exists in Percona Server 5.6, and the …[Read more]
In the previous blog posts I've talked about transactions which
block other transactions but don't do anything and about some
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 documentation says:
INNODB_LOCKS table contains information
about each lock that an
InnoDB transaction has
requested but not yet acquired, and each lock that a transaction
holds that is blocking another …
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.
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 transactions. It turned out that the blocking transaction had a trx_query=NONE. So my query was block by a transaction doing nothing. That's not really helpful.
Let's try to recreate the situation and see exactly what happened. I'll use two sessions for the transactions and a third to monitor the …
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
This is a topic we already talked about, but given the number
of systems suffering from
slowness, I think it is good to bring
innodb_stats_on_metadata back on the table.
Let’s look at a system I’ve seen recently: MySQL 5.5, working set fitting in memory but not the whole dataset, around 4000 InnoDB tables.
The I/O load is very light as …[Read more]
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))[Read more]
-> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM pfoo;
| id | name |
| 4 | test04 |
| 8 | test08 |
| 1 | test01 |
| 5 | test05 …
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 Bug #36312.
mysql> SELECT CONCAT(T.TABLE_SCHEMA,'.',T.TABLE_NAME) AS TABLE_NAME,[Read more]
-> P.PARTITION_NAME AS PART,IBT.SPACE,IBD.PATH,T.DATA_FREE AS T_DATA_FREE,
-> P.DATA_FREE AS P_DATA_FREE FROM INFORMATION_SCHEMA.TABLES T
-> LEFT …
Recently someone asked my if it's possible to find the total free
space within InnoDB. I thought this would be very easy as the
INFORMATION_SCHEMA.TABLES table has a DATA_FREE column. So we
could just use SELECT SUM(DATA_FREE) FROM
INFORMATION_SCHEMA.TABLES couldn't we?
So what does the DATA_FREE column tell us? It tells us the free data within InnoDB for that particular table. A table can share a tablespace with multiple other tables.
The tablespace which is used by a table depends on whether the innodb_file_per_table was enabled during table creation and/or at the last time the table was rebuild (e.g. by OPTIMIZE TABLE).
If innodb_file_per_table was always disabled then this query probably reports the correct free space:
SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' LIMIT 1;
This is because all tables will share 1 …