We are really excited about MySQL 8.0 new features (https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html) and our consultants spend several hours weekly, testing new features and doing research on how best we can create value for our customers from having those in production. Being an pure-play MySQL consulting, support and remote DBA services company, We are fully accountable for our customer database infrastructure operations performance, scalability, high availability and reliability. As we are aggressive about gaining maximum results from MySQL 8 investments made by our customers, We are equally conservative (our customer data reliability is critical for us !) on adopting new features, until we are fully confident after several rounds of testing (at different scales on multiple platforms) and technical review (we engage both internal and …[Read more]
We build highly available and fault tolerant MySQL database infrastructure operations for some of the largest internet properties in this planet, Our consulting team spend several hours daily researching on MySQL documentation and MySQL blogs to understand what are the best possible ways we can build optimal, scalable, highly available and reliable database infrastructure operations for planet-scale web properties. The most common approach towards building a fault-tolerant system is to make all the components in the ecosystem redundant, To make it even simple, component can be removed and system should continue to operate as expected. MySQL replication is an proven method to build redundant database infrastructure operations, operationally these systems are highly complex, requiring maintenance and administration of several servers instead of just one, You need Sr. DBAs to manage such systems.
MySQL Group Replication can operate …[Read more]
We are all familiar with “.frm” files since the earliest days of MySQL, The community has been continuously requesting for replacement of file-system based metadata for several good reasons, So with MySQL 8.0 “.frm” files are gone for ever, Going forward MySQL stores table metadata in the data dictionary tables which uses InnoDB storage engine. This blog is about MySQL 8.0 data dictionary and how it creates value for MySQL going forward:
How file based metadata management used to work in the past (before MySQL 8.0) ?
- Every table in MySQL will have corresponding .frm file, This .frm file stores information like column names and data-types in the binary format, In addition to the .frm file, there are .trn, .trg and .par files to support triggers, trigger namespace and partitioning .
What are major bottlenecks faced due to the usage of file based metadata management …[Read more]
We manage several hundreds of MySQL servers, We carefully benchmark and build custom database infrastructure operations for performance, scalability, availability and reliability … But What if we have provision for auto sizing of MySQL system variables innodb_buffer_pool_size, innodb_log_file_sizeand innodb_flush_method ? Actually, These are top 3 system variables we consider tuning for MySQL performance and when we first read about this feature, we got super excited so did some research and decided to write this post:
What was our first reaction, when we first read about innodb_dedicated_server ?
Wow, That will be awesome … Indeed, When we manage several hundreds of MySQL instances, This feature will really improve efficiency and DBA Ops. governance.
Now, Let us explain what we have found:
How does innodb_dedicated_server system variable in MySQL 8.0 size the following …[Read more]
The UTF-8 is a variable-length encoding. In the case of UTF-8, it means that storing one code point requires one to four bytes. But, In MySQL’s encoding called “utf8” only stores a maximum of three bytes per code point. In the modern web / mobile applications, we have to support for storing not only language characters but also symbols and emojis, Let me show you below some very weird issues faced using MySQL “utf8” :
mysql> SET NAMES utf8; # just to emphasize that the connection charset is set to `utf8` Query OK, 0 rows affected (0.00 sec) mysql> UPDATE custfeeds.reactions SET reacted = 'super like ' WHERE id = 13015; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> SELECT reactions FROM custfeeds.reactions WHERE id = 13015; +-------------+ | reactions | +-------------+ | super liked | +-------------+ 1 row in set (0.00 sec) mysql> SHOW WARNINGS; …[Read more]
Recently we were evaluating MySQL 8 Enterprise for a customer. During our installation (our MySQL Enterprise Edition installations are always RPM based) using RPM file (CentOS Linux release 7.4.1708 (Core)) we ended up in a very unusual error, “error: Failed dependencies:pkgconfig(openssl) is needed by mysql-commercial-devel-8.0.11-1.1.el7.x86_64” , This was happening more specifically during the installation of “mysql-commercial-devel-8.0.11-1.1.el7.x86_64.rpm” . So decided to write this post about the error and how we fixed it.
[root@localhost MySQL8-Enterprise-RPM]# rpm -ivh mysql-commercial-server-8.0.11-1.1.el7.x86_64.rpm mysql-commercial-client-8.0.11-1.1.el7.x86_64.rpm mysql-commercial-libs-8.0.11-1.1.el7.x86_64.rpm mysql-commercial-common-8.0.11-1.1.el7.x86_64.rpm mysql-commercial-devel-8.0.11-1.1.el7.x86_64.rpm warning: mysql-commercial-server-8.0.11-1.1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, …[Read more]
MySQL 8 supports invisible indexes, This allows you to on-demand enable/disable indexes from being used by MySQL optimizer. Now please don’t get confused with “disabled indexes“, “invisible indexes are not disabled indexes, MYISAM supports disabled indexes, ” , The disabled indexes halt maintenance of an index. Invisible indexes are a new feature in MySQL 8.0 , which mark an index unavailable for use by the optimizer. That means, Index will still be maintained and keep up-to-date as data is modified, but no queries will be permitted to make use of the index (even if the query uses a FORCE INDEX hint) .
Why we really love invisible indexes in MySQL 8.0?
- You want to make only one query to use that index, In this case “invisible index” is a great option
- On-demand indexing, You will have index (up-to-date with data) but you can make it visible or …
Although only available as a Release Candidate, MySQL 8 is already proving itself to be a huge leap forward in many regards. Error logging is no exception. The MySQL development team just announced that they have redesigned the error logging subsystem to use a new component architecture.
The redesign will allow the filtering of log events, as well as the routing of error log output to multiple destinations, via the enabling of multiple sink components. This will make it possible to send error log events to third-party systems for additional formatting and analysis.
In today’s blog, we’ll explore how to employ MySQL 8’s component-based error logging to achieve a variety of logging configurations. Note that all this is specific for MySQL 8 and is not available in earlier versions and also not in MariaDB. However, MariaDB has an option to write the error log to the system “syslog” on systemd-based Linux variants – …[Read more]
Pretend you have a JSON array of data that looks roughly like the
mysql> insert into x(y) values('["a","b","c","d"]');
Query OK, 1 row affected (0.10 sec)
You could get all the values from that array using $[*]
mysql> select y->"$[*]" from x;
| y->"$[*]" |
| ["a", "b", "c", "d"] |
1 row in set (0.00 sec)
Or the individual members of the array with an index that starts
mysql> select y->"$" from x;
| y->"$" |
| "a" |
1 row in set (0.00 sec)
But what about the times you want the last item in the array and really do not want to loop through all the items? How about using …
If you want to have a look on what is about to come in the new version of the popular database and is used to Syntax Highlighting you don’t need to be chained to the Terminal.
Some of you may use tools like MySQL
Workbench or Sequel Pro (as of the release of this post both
tools had the following error occurring), and even if you are
using the Terminal (if you are using an old version of
mysql, like 5.7) you may encounter this error:
Unable to connect to host 127.0.0.1, or the request timed out.
Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).
MySQL said: Authentication plugin ‘caching_sha2_password’ cannot be loaded: …[Read more]