Why Does InnoDB Need A Dictionary

An InnoDB dictionary is a set of internal tables InnoDB uses to maintain various information about user tables. It serves as API between a human and the database. While the humans refer to tables by their names, InnoDB works with integer identifiers. The dictionary stores correspondence between table name and index_id.

The dictionary tables are normal InnoDB tables, but they’re not visible to a user. However, some versions of MySQL provide read-only access to the dictionary in information_schema database.

The dictionary is stored in ibdata1. Root page of SYS_TABLES, for example, has id 8, so it’s the eighth page from the beginning of ibdata1.

The dictionary pages are in REDUNDANT format even if you use MySQL 5.6. I hope to write more about record formats in future posts. For now, it’s enough to mention that REDUNDANT is the oldest record format. It was available since 4.0 …

In a previous blog post on Data Consistency for RDS for MySQL, we presented a workaround to manage run pt-table-checksum on RDS instances. However, if your instance is running a MySQL 8.0.X version, there’s a simpler way to check data consistency.

Starting with 8.0.1, MySQL introduced something called “Dynamic Privileges” which is a solution to grant more granulated privileges to the users, instead of the almighty SUPER privilege.

So what was the issue with pt-table-checksum and RDS again? Since there’s no SUPER privileges for any user, there was no way for the tool to change the binlog_format to STATEMENT… but not anymore.

The solution when using 8.0 is …

One of the questions you often will be faced with operating a Linux-based system is managing memory budget. If a program uses more memory than available you may get swapping to happen, oftentimes with a terrible performance impact, or have Out of Memory (OOM) Killer activated, killing process altogether.

Before adjusting memory usage, either by configuration, optimization, or just managing the load, it helps to know how much memory a given program really uses.

If your system runs essentially a single user program (there is always a bunch of system processes) it is easy.  For example, if I run a dedicated MySQL server on a system with 128GB of RAM I can use “used” as a good proxy of what is used and “available” as what can still be used.

root@rocky:/mnt/data2/mysql# free -h …

Watch the replay of our Geo-Scale MySQL webinar: Guaranteed Global Game Access for Hundreds of Millions of Players Worldwide - Cloud-Based Active/Passive Tungsten MySQL Clusters @ Riot Games.

Tags:  Webinar MySQL use case tungsten clustering mysql cluster geo-scale geo-distributed MySQL Riot Games …

Building Java with K8S using MySQL MDS

Please note that this is not recommended for any production purpose, don’t expose MySQL traffic to the Internet !

MySQL Database Service is now available, take a look at this nice introduction video:

It is usually a very bad idea to expose a database on a public IP (MySQL or any other database ). MDS doesn’t allow you to implement such bad security practice.

The best way to use your MySQL instance is to use a VPN as I explained in this article.

However, this can be restrictive for a simple test. In this post I will illustrate how you can setup a MDS instance and test it without having to setup any kind of VPN. Of course I recommend you to use such practice only for testing …

I have made changes to the RSS Feed of this blog:

1. Each <item/> does now contain a container <tags/>, inside a sequence of <tag/> containers, with each posts tags.
2. There is now a second RSS feed for posts tagged #mysql, because of demand. You can find it at https://blog.koehntopp.info/feed_mysql.xml.

Example for the tags:

<channel>
        <title>Die wunderbare Welt von Isotopp</title>
        ...
        <item>
                <title>A post title</title>
                <link>https://blog.koehntopp.info/2020/09/...</link>
                <guid isPermalink="false">/2020/09/...</guid> …

     If you are running MySQL 5.6 please note that it will hit support End of Life status in February 2021!  MySQL 5.7 has been Generally Available for almost five years and MySQL 8.0 has been out for over two  years.   You need to upgrade as soon as possible.  

    Upgrading to 5.7 is pretty easy. And I heartily suggest that after upgrading to 5.7 that you also upgrade to 8.0  using the upgrade checker …

MySQL Database Service is expanding to more Oracle Cloud Infrastructure Regions. In addition to the US East (Ashburn), we are rolling out the Service to the United Kingdom (London), Germany (Frankfurt), Brazil (Sao Paulo), and the US West (Phoenix).

Go to oracle.com/cloud/free and get access to a wide range of Oracle Cloud services for 30 days, including MySQL Database Service in Ashburn and Frankfurt Regions. Check the documentation for the quick steps to create your MySQL databases.

We will be rolling out even more Regions soon. Stay tuned!

Don’t forget to register for the live MySQL Database Service webinar series: www.mysql.com/news-and-events/web-seminars …

MySQL Database Service is expanding to more Oracle Cloud Infrastructure Regions. In addition to the US East (Ashburn), we are rolling out the Service to the United Kingdom (London), Germany (Frankfurt), Brazil (Sao Paulo), and the US West (Phoenix). Go to oracle.com/cloud/free and get access to a wi...