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.…
10 Older Entries »
MySQL 5.7 introduces a change in the way we query for global variables and status variables: the INFORMATION_SCHEMA.(GLOBAL|SESSION)_(VARIABLES|STATUS) tables are now deprecated and empty. Instead, we are to use the respective performance_schema.(global|session)_(variables|status) tables.
But the change goes farther than that; there is also a security change. Oracle created a pitfall of 2 changes at the same time:
- Variables/status moved to a different table
- Privileges required on said table
As an example, my non-root user gets:
mysql> show session variables like 'tx_isolation'; ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'session_variables'
Who gets affected by this? Nearly everyone and everything.
- Your Nagios will not be able to read …
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]
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 InnoDB related bugs were fixed:
- Bug #69990 - CREATE_TIME and UPDATE_TIME are wrong for partitioned tables. Finally this bug reported by my colleague Justin Swanhart and verified by Umesh (almost …
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 …
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 to get a chance for a beer from him (we'll
arrange this somehow).
In the meantime I'd like to review bug reports for MySQL server (few) and fine manual (many) that I've submitted in February, 2014. 22 in total, one was just plain wrong and I closed it as "Not a bug" almost immediately. So, 21 to …
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]
10 Older Entries »