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 …

In transaction processing systems we have to guarantee the transaction committed will be durable permanently. For example, In an online commerce platform, The completed transaction will remain safe even if the system crashes. This can be achieved by flushing the transactional log records to non-volatile storage devices before acknowledging the commit. MySQL guarantees maximum durability of transaction by optimally setting following system variables :

innodb_doublewrite (enabled by default) 

InnoDB stores all data twice, first to doublewrite buffer (storage area in system tablespace to write pages that are flushed from InnoDB buffer pool, before written in data file). If ever operating system / storage / mysqld process crash during the middle of page write, InnoDB can still find a durable copy of the page from doublewrite buffer for recovery. Though data is written twice,  the doublewrite buffer …

All our customers are on Linux, They have multiple flavors of Linux actually – Ubuntu, CentOS, RedHat Linux, Oracle Linux, SUSE Linux etc. Though we are an full-service everything MySQL shop, Our consulting, support and managed services are never restricted to only MySQL Ops. , We are experts in Linux,  DevOps and Site Reliability Engineering (SRE). We have proven methods to deliver Linux performance audit / health check / diagnostics and recommendations. What are the tools we use for monitoring Linux ops. ? This post is about those tools we use regularly in MinervaDB for monitoring Linux operations:

How long Linux server is up and running ? 

[root@localhost ~]# uptime 
 12:32:56 up 1800 min,  83 users,  load average: 88.01, 88.52, 88.64
[root@localhost ~]#

Print all the processes running as root

[root@localhost ~]# ps -U root -u root 
  PID TTY          TIME CMD
    1 ? …

We often enable MySQL slow_query_log to proactively troubleshoot the performance, There is so lot of information with-in slow_query_log which is enough to address most common (even complex ones occasionally) MySQL performance bottlenecks. What if we haven’t planned for the log lifecycle management of slow query log ? Your log will really grow huge (we keep long_query_time as-low-as 1 second), So we have to plan for slow_query_log rotating to avoid MySQL outage due to “no space left in the disk” to grow further. There are two ways you can do log rotate, I have explained below:

1. Manual log rotating (We use it only when there is an emergency)
2. Using logrotate to automatic MySQL log rotation (We love it, MinervaDB is an big fan of MySQL automation & DevOps. but we also recommend to use it very carefully)

Step-by-step manual log rotating …

To change the number or the size of your InnoDB redo log files, please follow the steps below:

Step 1: Stop the MySQL server and confirm that it has shutdown without any errors

Step 2: Edit my.cnf to configure innodb_log_file_size (log file size) and innodb_log_files_in_group (number of log files)

Step 3: Restart MySQL server

If ever InnoDB finds innodb_log_file_size differs from the redo log files, It writes a log checkpoint, closes and removes the old log files, create again new log files at requested size and opens the new log files.

The post How to change the number or size of InnoDB Redo Log Files ? appeared first on MySQL Consulting, …

Although there is no official software release for MySQL 8.0 as of yet, most insiders believe that it’s likely to arrive sometime in 2018.  In the meantime, Oracle has officially announced a tantalizing list of over two hundred new features!   We recently covered Replication Performance Enhancements.  Today’s blog will cover some of the other exciting enhancements we can expect when the production release of MySQL 8 hits the market.

New Database Roles

A role is a named collection of privileges that define what a user can and cannot do within a database. Roles play a vital part of database security by limiting who can connect to the server, access the database, or even access individual database objects and data.

Although prior to version 8, MySQL did provide a set of Privileges and Administrative Roles, the up-coming release will also support a set of flexible and properly …

This is a short post about DELETE data from huge table in MySQL. Most of us experienced, deleting huge record from MySQL tables taking long time, sometimes hours to complete deleting millions of records. Also, on production servers it locks the other table operations as well. Recently, I deleted around 70 million record from a production database in less than an hour. There are multiple workarounds to do this, however I am writing about the two methods which are frequently used by me for this operation. 

• Using intermediate table.
• Delete data in small chunks.

Before we proceed with using any of these methods, make sure the table has required indexes on where clause and you have a copy of the table as backup.
Using intermediate table:In this method, create a new table with similar data structure and copy only required data. Rename the original table as archive or backup table and Rename the …

As tables increase in size and more and more users come online, it becomes increasingly necessary to fine-tune your database server from time to time. The secret to knowing what adjustments need to be made is to perform regular monitoring. Most databases offer dozens – if not hundreds – of performance metrics that you can assess.

In the earlier blog, we showed a number of ways to determine the cause(s) of MySQL database performance issues using a couple of built-in tools. With this two blog series, we will focus on monitoring key performance metrics. In part one, we’ll:

• examine the benefits of performance monitoring,
• outline the main performance metric categories
• list the monitoring tools provided by MySQL, i.e.:
  • server variables
  • the Performance Schema
  …

Despite your very best efforts, as tables increase in size and more and more users come online, sluggish MySQL performance eventually rears its ugly head. When that happens, you may be tempted to shrug it off as part of doing business. Don’t. By understanding the mechanisms behind database slowdowns and by methodically attacking the problem, you can home in on the culprit(s) and remedy the issues in a timely manner, before your business suffers substantial losses as a result.

The purpose of this blog is to assist you in determining the cause(s) of MySQL database performance issues using a couple of built-in tools.

Why Database Performance Slows Down

It’s no secret that database performance tends to degrade over time. While it’s easy to point the finger at the number of concurrent users, table scans, and growing tables, the reality is more complex than that. The most common reason for slow database performance is …

Internet Protocol address is a special kind of data. This data does not correspond directly to available MySQL built-in data types. I have seen in many servers, the IP(Internet Protocol) address is stored in CHAR(12), VARCHAR(15), TEXT and so on. 

The dotted-quad notation is just a way of writing for better readability, it is not the original form of raw data. The IP address is a UNSIGNED INTEGERS they are not strings. 

So the question is how we can store the IP addresses with dot in integer columns? The MySQL provides the built-it function to do this operation. The functions are given below:







For IPv4 addresses:

INET_ATON()

mysql> select inet_aton('127.0.0.1');

+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
|             2130706433 | …