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 …

pre { background: lightgrey; font-size: 14px; border: 2px solid grey; padding: 14px; }

Oracle Cloud Infrastructure (OCI) is the Oracle Cloud Platform where the tutorial leverages 3 services – namely the MySQL Database Service (MDS), Kubernetes Container (K8s) under Developer Services and Compute VM services.

This article is written to provide the steps to provision MDS, K8s and Compute VM services.   It also provides the steps to create a docker image from Image:java:latest as tar for local node image repository import (loading).  

2 Java Client sources are used with the tutorial.

• Helloworld.java – it is a simple hello world output java source
• HelloDB.java – it is multi-threaded java application with Runnable Thread to pump Data into MySQL Database Service.   It uses the Connect/J (MySQL JDBC Driver) to connect to MySQL MDS.

MySQL Shell is …

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 …

The MySQL DROP statement is one of many powerful DDL commands. Be it ALTER TABLE some_table DROP some_column or DROP some_table, this type of command can drastically change your data landscape because in executing MySQL DROP, you are completely removing objects from the database! If you are using the phpMyAdmin web interface, you can execute the MySQL DROP statement with just a few mouse clicks. Continue reading to see how…

Drops of water.

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

Note: The …