Showing entries 1 to 3
Displaying posts with tag: Investigations (reset)
The story of MySQL Bug #86664

This is a story about why it's a good idea to test and verify the behavior of new software releases, even if the change log says that a particular bug was already fixed.
Background MySQL 5.6 and 5.7 both support the following CREATE USER syntax:

CREATE USER 'user'@'host'
IDENTIFIED BY 'password';

This syntax create a user with the default authentication plugin (mysql_native_password unless configured otherwise) and the password provided. The IDENTIFIED BY syntax is also supported for GRANT command.
Both major versions also support the following syntax:

CREATE USER 'user'@'host'
IDENTIFIED WITH mysql_native_password AS 'hash_string';

The password hash for "passw0rd" is "*74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3", you might then expect …

[Read more]
Investigating InnoDB FULLTEXT cache performance issues in MySQL

I'm not a huge fan of the InnoDB FULLTEXT feature and I admit I wasn't too keen to play with it in the past. Apparently, the feeling is mutual and so FULLTEXT issues haven't popped up in too many projects I worked on... until last week.

This post describes the troubleshooting process of a FULLTEXT cache performance issue. Quite inconspicuous at the beginning, it proved to be a lot of fun in the end.

Ready, Set, GDB!
Background It all started with a generic performance issue report unrelated to FULLTEXT indexing. The issue, as described in the report, was:

  1. The server performs acceptably for a few days.
  2. After a certain amount of time, DML performance suddenly drops and remains low until the server is rebooted.
  3. Reboot restores original performance, which again only lasts for a few days.

The situation was resulting in DML latency degradation during normal OLTP …

[Read more]
SHOW CREATE TABLE and foreign key constraints with innodb_read_only

The "innodb_read_only" parameter is perhaps not among the most commonly used MySQL parameters. It may however influence server behaviour in a way that you may not expect. This post is a record of an investigation that revealed one of MySQL quirks caused by said parameter.
Background The MySQL 5.6 server topology I was looking at during this investigation consisted of a single main server (R/W) and multiple replicas serving read-only statements.
This is relatively simple and you wouldn't expect to run into issues on the replicas as long as you stick to statements that do not modify data, right?
Not quite. Issue I was asked to investigate can be described as follows:
The DBA created two tables connected using a foreign key relationship:

CREATE TABLE `employee` (
`e_id` int(11) NOT …
[Read more]
Showing entries 1 to 3