Planet MySQL
Planet MySQL -

  • MySQL Invisible Column: part II
    This article is the second part of the series related to MySQL Invisible Column started here. This post covers why Invisible Column is important for InnoDB Storage Engine. To start, let me explain briefly how InnoDB deals with Primary Key and why an good primary key is important. And finally, why having a Primary Key is also important. How does InnoDB Stores Data? InnoDB stores data in table spaces. The records are stored and sorted using the clustered index (the primary key): they are called index-oraganized tables. All secondary indexes also contain the primary key as the right-most column in the index (even if this is not exposed). That means when a secondary index is used to retrieve a record, two indexes are used: first the secondary one pointing to the primary key that will be used to finally retrieve the record. The primary key impact the ratio between random and sequential I/O and the size of the secondary indexes. Random or Sequential Primary Key? As written above, the data is stored on the tablespace following the clustered index. Meaning that if you don’t use a sequential index, when performing inserts, InnoDB will have to heavily rebalance all the pages of the tablespace. If we use InnoDB Ruby to illustrate this process, the picture below shows how a tablespace is updated when inserting records using a random string as Primary Key: every time there is an insert almost all pages are touchedAnd now the same insert operations when using an auto_increment integer as Primary Key: with auto_increment PK only some first pages and last pages are touchedLet’s try to explain this with a high level example: Let’s imagine one InnoDB Page can store 4 records (disclaimer: this is just a fiction for the example), and we have inserted some records using a random Primary Key: Now we need to insert a new record and the Primary Key is AA ! All pages were modified to “rebalance” the clustered index, in case of a sequential PK, only the last page would have been touched. Imagine the extra work when thousands of inserts are happening. This means that choosing a good primary key is important. Two things to keep in mind: the primary key must be sequential the primary key must be short What about UUID? I always recommend to use auto_increment integers (or bigint…) as primary key but don’t forget to monitor them ! (see this post) But I also understand that more and more developers prefer to use UUIDs. If you plan to use UUIDs, you should read this article about UUID support in MySQL 8.0 that recommends to store UUIDs using binary(16). Like this: CREATE TABLE t (id binary(16) PRIMARY KEY);   INSERT INTO t VALUES(UUID_TO_BIN(UUID())); However, I don’t share at 100% the same opinion… why ? Because the use of uuid_to_bin() might change the sequential behavior of the UUID implementation of MySQL (read the Extra section for more info). But if you need UUIDs, then you also need to pay the price of large indexes, so please don’t waste storage and ram with unnecessary secondary indexes select * from sys.schema_unused_indexes where object_schema not in ('performance_schema', 'mysql'); And without any Primary Key ? For InnoDB tables, when no primary key is defined, the first unique not null key is used. And if none is available, InnoDB will create an hidden primary key (6 bytes). The problem with such key is that you don’t have any control on itand worse, this value is global to all tables without primary keys and can be a contention problem if you perform multiple simultaneous writes on such tables (dict_sys->mutex). Invisible Column at the rescue With the new invisible column, we have now an option to add an optimal primary key to a table without Primary Key if the application doesn’t allow a new column. The first step is to detect such table using the query of Roland Bouman (already used in this post): SELECT tables.table_schema , tables.table_name , tables.engine FROM information_schema.tables LEFT JOIN ( SELECT table_schema , table_name FROM information_schema.statistics GROUP BY table_schema, table_name, index_name HAVING SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name WHERE puks.table_name IS null AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB"; +--------------+--------------+--------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | +--------------+--------------+--------+ | test | table2 | InnoDB | +--------------+--------------+--------+ You can also use MySQL Shell with the check plugin: Let’s check the table’s definition: show create table table2\G *************** 1. row *************** Table: table2 Create Table: CREATE TABLE table2 ( name varchar(20) DEFAULT NULL, age int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci And the current records: select * from table2; +--------+-----+ | name | age | +--------+-----+ | mysql | 25 | | kenny | 35 | | lefred | 44 | +--------+-----+ Now let’s update the schema to add that specific invisible primary key: alter table table2 add column id int unsigned auto_increment primary key invisible first; Now let’s add one record: insert into table2 (name, age) values ('PHP', 25); select * from table2; +--------+-----+ | name | age | +--------+-----+ | mysql | 25 | | kenny | 35 | | lefred | 44 | | PHP | 25 | +--------+-----+ And if we want to verify the primary key: select id, table2.* from table2; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 1 | mysql | 25 | | 2 | kenny | 35 | | 3 | lefred | 44 | | 4 | PHP | 25 | +----+--------+-----+ Conclusion Now you know why Primary Keys are important in InnoDB and why a good Primary Key is even more important. And since MySQL 8.0.23, you also have solution for tables without Primary Key with the invisible column ! Extra Just for fun and to illustrate my opinion regarding the use of UUID_TO_BIN(UUID()) as Primary Key, let’s redo the example of the invisible column but this time using UUIDs. alter table table2 add column id binary(16) invisible first; alter table table2 modify column id binary(16) default (UUID_TO_BIN(UUID())) invisible; update table2 set id=uuid_to_bin(uuid()); alter table table2 add primary key(id); So far nothing special, it was just a bit more tricky to create that invisible Primary Key. Let’s query: select * from table2; +--------+-----+ | name | age | +--------+-----+ | mysql | 25 | | kenny | 35 | | lefred | 44 | +--------+-----+ And now, we will insert a new record and query the table again: insert into table2 (name, age) values ('PHP', 25); select * from table2; +--------+-----+ | name | age | +--------+-----+ | PHP | 25 | | mysql | 25 | | kenny | 35 | | lefred | 44 | +--------+-----+ Mmmm.. why is PHP the first one now ? Because the uuid() is not really sequential… select bin_to_uuid(id), table2.* from table2; +--------------------------------------+--------+-----+ | bin_to_uuid(id) | name | age | +--------------------------------------+--------+-----+ | 05aedcbd-5b36-11eb-94c0-c8e0eb374015 | PHP | 25 | | af2002e8-5b35-11eb-94c0-c8e0eb374015 | mysql | 25 | | af20117a-5b35-11eb-94c0-c8e0eb374015 | kenny | 35 | | af201296-5b35-11eb-94c0-c8e0eb374015 | lefred | 44 | +--------------------------------------+--------+-----+ Do we have an alternative ? Yes, if we follow the manual, we can see that the function uuid_to_bin() allows a swap flag. Let’s try it: alter table table2 add column id binary(16) invisible first; alter table table2 modify column id binary(16) default (UUID_TO_BIN(UUID(),1)) invisible; update table2 set id=uuid_to_bin(uuid(),1); Now every time we add a record, the insert will be sequential as expected: select bin_to_uuid(id,1), table2.* from table2; +--------------------------------------+--------+-----+ | bin_to_uuid(id,1) | name | age | +--------------------------------------+--------+-----+ | 5b3711eb-023c-e634-94c0-c8e0eb374015 | mysql | 25 | | 5b3711eb-0439-e634-94c0-c8e0eb374015 | kenny | 35 | | 5b3711eb-0471-e634-94c0-c8e0eb374015 | lefred | 44 | | f9f075f4-5b37-11eb-94c0-c8e0eb374015 | PHP | 25 | | 60ccffda-5b38-11eb-94c0-c8e0eb374015 | PHP8 | 1 | | 9385cc6a-5b38-11eb-94c0-c8e0eb374015 | Python | 20 | +--------------------------------------+--------+-----+

  • MySQL Shell AdminAPI – What’s new in 8.0.23?
    The MySQL Development Team is happy to announce a new 8.0 Maintenance Release of MySQL Shell AdminAPI – 8.0.23! In addition to several bug fixes and minor changes, some significant enhancements regarding monitoring/troubleshooting and performance were included. MySQL Shell AdminAPI Cluster diagnostics Checking how a Cluster is running and, whenever the cluster is not 100% healthy, perform troubleshooting tasks is certainly one of the main tasks of a DBA.… Facebook Twitter LinkedIn

  • How to Setup Automatic Failover for the Moodle MySQL Database
    In a previous blog, we had discussed how to migrate a standalone Moodle setup to scalable setup based on a clustered database.  The next step you will need to think about is the failover mechanism - what do you do if and when your database service goes down.  A failed database server is not unusual if you have MySQL Replication as your backend Moodle database, and if it happens, you will need to find a way to recover your topology by for instance promoting a standby server to become a new primary server. Having automatic failover for your Moodle MySQL database helps  application uptime. We will explain how failover mechanisms work, and how to build automatic failover into your setup. High Availability Architecture for MySQL Database High availability architecture can be achieved by clustering your MySQL database in a couple of different ways. You can use MySQL Replication, set up multiple replicas that closely follow your primary database. On top of that, you can put a database load balancer to split the read/write traffic, and distribute the traffic across read-write and read-only nodes. Database high availability architecture using MySQL Replication can be described as below : It consists of one primary database, two database replicas, and database load balancers (in this blog, we use ProxySQL as database load balancers), and keepalived as a service to monitor the ProxySQL processes. We use Virtual IP Address as a single connection from the application. The traffic will be distributed to the active load balancer based on the role flag in keepalived.  ProxySQL is able to analyze the traffic and understand whether a request is a read or a write. It will then forward the request to the appropriate host(s).  Failover on MySQL Replication MySQL Replication uses binary logging to replicate data from the primary to the replicas. The replicas connect to the primary node, and every change  is replicated and written to the replica nodes’ relay logs through IO_THREAD. After the changes are stored in the relay log, the SQL_THREAD process will proceed with applying data into the replica database. The default setting for parameter read_only in a replica is ON. It is used to protect the replica itself from any direct write, so the changes will always come from the primary database. This is important as we do not want the replica to diverge from the primary server. Failover scenario in MySQL Replication happens when the primary is not reachable. There can be many reasons for this; e.g., server crashes or network issues. You need to promote one of the replicas to primary, disable the read-only parameter on the promoted replica so it can be writable. You also need to change the other replica to connect to the new primary. In GTID mode, you do not need to note the binary log name and position from where to resume replication. However, in traditional binlog based replication, you definitely need to know the last binary log name and position from which to carry on. Failover in binlog based replication is quite a complex process, but even failover in GTID based replication is not trivial either as you need to look out for things like errant transactions. Detecting a failure is one thing, and then reacting to the failure within a short delay is probably not possible without automation.  How ClusterControl Enables Automatic Failover  ClusterControl has the ability to perform automatic failover for your Moodle MySQL database. There is an Automatic Recovery for Cluster and Node feature which will trigger the failover process when the database primary crashes.  We will simulate how Automatic Failover happens in ClusterControl. We will make the primary database crash, and just see on the ClusterControl dashboard. Below is the current Topology of the cluster : The database primary is using IP Address and the replicas are : and When the crash happens on the primary, ClusterControl triggers an alert and a failover starts as shown in the below picture: One of the replicas will be promoted to primary, resulting in the Topology as in the  below picture: The IP address is now serving the write traffic as primary, and also we are left with only one replica which has IP address On the ProxySQL side, the proxy will detect the new primary automatically. Hostgroup (HG10) still serve the write traffic which has member as shown below: Hostgroup (HG20) still can serve read traffic, but as you can see the node is offline because of the crash : Once the primary failed server comes back online, it will not be automatically re-introduced in the database topology. This is to avoid losing troubleshooting information, as re-introducing the node as a replica might require overwriting some logs or other information. But it is possible to configure auto-rejoin of the failed node.  Tags:  MySQL moodle lms automatic failover

  • MySQL Invisible Column – part I
    With the new MySQL 8.0.23, something very interesting has been released: Invisible Column. This is the first post dedicated to this new feature, I expect to write a series of 3. This one is the introduction. Prior to MySQL 8.0.23, all columns of a table were always visible (if you had the privilege to see it). Now, an invisible column can be specified and will be hidden to queries. It can always be accessed if explicitly referenced. Let’s see how it works: create table table1 ( id int auto_increment primary key, name varchar(20), age int invisible); In the table description we can see the INVISIBLE keyword in the Extra column: desc table1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int | YES | | NULL | INVISIBLE | +-------+-------------+------+-----+---------+----------------+ With the show create table statement, we can notice a difference, I was expecting to see the INVISIBLE keyword as when I created the table, but this is not the case: show create table table1\G ************************* 1. row ************************* Table: table1 Create Table: CREATE TABLE `table1` ( id int NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, age int DEFAULT NULL /*!80023 INVISIBLE */, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci But I confirm that this statement will create the table as set the age column as invisible. So we have two different valid syntax to create a INVISIBLE column. INFORMATION_SCHEMA also includes that info: SELECT TABLE_NAME, COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'table1'; +------------+-------------+----------------+ | TABLE_NAME | COLUMN_NAME | EXTRA | +------------+-------------+----------------+ | table1 | id | auto_increment | | table1 | name | | | table1 | age | INVISIBLE | +------------+-------------+----------------+ It’s time to add some data and see how it behaves: insert into table1 values (0,'mysql', 25), (0,'kenny', 35), (0, 'lefred','44'); ERROR: 1136: Column count doesn't match value count at row 1 We can see that as expected, if we don’t reference it we have an error even with the INSERT statement. Let’s reference the columns then: insert into table1 (id, name, age) values (0,'mysql', 25), (0,'kenny', 35), (0, 'lefred','44'); Query OK, 3 rows affected (0.1573 sec Time to query the data in that table: select * from table1; +----+--------+ | id | name | +----+--------+ | 1 | mysql | | 2 | kenny | | 3 | lefred | +----+--------+ Once again, as expected, we can see that the invisible column is not displayed. If we specify it then we have it: select name, age from table1; +--------+-----+ | name | age | +--------+-----+ | mysql | 25 | | kenny | 35 | | lefred | 44 | +--------+-----+ Of course an column can be changed from VISIBLE to INVISIBLE and vice versa: alter table table1 modify name varchar(20) invisible, modify age integer visible; Query OK, 0 rows affected (0.1934 sec) select * from table1; +----+-----+ | id | age | +----+-----+ | 1 | 25 | | 2 | 35 | | 3 | 44 | +----+-----+ I’m very happy of this new feature and in the next post we will see why this is an important feature for InnoDB.

  • PHP portfolio piece – Analytics data.
    I recently published a blog post about a portfolio project I am developing using the LAMP stack, Bootstrap 4, jQuery, and the MVC (Model-View-Controller) design pattern in core PHP. In this post, I will introduce an additional feature I integrated into the existing project. Image by xresch from Pixabay 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! I keep tabs on walking data via a pedometer mobile application for the many walks I take as I work towards better health and manageable weight. Analytics are vital in understanding patterns in your data. Utilizing MySQL VIEW‘s along with the Aggregate functions SUM() and AVG(), I broke down the analytic data into these broad categories: The shoe worn (Totally irrelevant. I just happen to have purchased, worn, and tried a lot of different hiking shoes/boots) The day of the week (E.g., Monday, Tuesday, etc…) The month The year I further subdivided the categories – with the exception of ‘yearly’ data – into 2 similar sub-groups: sums and averages. I added in an Analytics button between the Export CSV and Add A Walk buttons on the ‘All Walks’ page: ‘Analytics’ button.Clicking Analytics opens this page of Bootstrap 4 tabs: Using Bootstrap 4 tabs looking at the default tab view.Notice for each category (Shoes, Monthly, and Weekday) there is an ‘Averages’ and ‘Totals’ tab. Following are several screen-shots of the various data provided by these tabs: Monthly Totals tab using Bootstrap 4 and PHP. Weekday Averages tab. Yearly Stats tab with Bootstrap 4 and dynamic PHP.Goals and Improvements My original intent was to provide each tab’s table with the necessary data using a dynamic jQuery DataTable, utilizing AJAX, and server-side processing. However, at the time of this writing, I used passing the retrieved MySQL data to the view from the applicable controller as is common in the MVC architecture. Each tab’s Bootstrap 4 table is still dynamic but does not use the jQuery DataTable with AJAX so I hope to integrate that functionality in the future. As always, if you have any questions or comments about the post, feel free to leave them in the comments section below. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The post PHP portfolio piece – Analytics data. appeared first on Digital Owl's Prose.