Last year, I made the first post in a small series, which aimed to highlight unique features of Percona Server for MySQL, by discussing binlog_space_limit option.

Today, I am going to discuss another important type of log available in MySQL that is enhanced in Percona Server for MySQL – the slow query log. The reason why I am doing this is that although this extension has existed since the very early times of versions 5.1 (over 10 years ago!), many people are still unaware of it, which I see from time to time when working with Support customers.

Default Slow Log Inadequacy

How many times have you been wondering why, whilst reviewing slow query logs, the very same query occasionally runs way slower than usual? There may be many reasons for that, but the standard slow …

Amazon RDS for MySQL uses many default values for system variables, but it also sets a few “sys vars” with different values. As with any database, neither product (MySQL) nor provider (AWS) defaults can best suite all use cases. It’s our responsibility to carefully review and set every important system variable. This is tedious and difficult, but I’ve done it for you. Below are are 21 MySQL 5.7 system variables that I recommend changing by creating a new parameter group.

Amazon RDS for MySQL uses many default values for system variables, but it also sets a few “sys vars” with different values. As with any database, neither product (MySQL) nor provider (AWS) defaults can best suite all use cases. It’s our responsibility to carefully review and set every important system variable. This is tedious and difficult, but I’ve done it for you.

Below are are 21 MySQL 5.7 system variables that I recommend changing by creating a new parameter group. This presumes new RDS instances; some of these sys vars cannot be changed easily after provisioning MySQL. This only applies to RDS for MySQL, not Amazon Aurora.

Amazon RDS for MySQL uses many default values for system variables, but it also sets a few “sys vars” with different values. As with any database, neither product (MySQL) nor provider (AWS) defaults can best suite all use cases. It’s our responsibility to carefully review and set every important system variable. This is tedious and difficult, but I’ve done it for you. Below are are 21 MySQL 5.7 system variables that I recommend changing by creating a new parameter group.

In April, when I updated from MySQL 8.0.17 to MySQL 8.0.19, I found that my Java connection example failed. That’s because of a change in the JDBC driver, which I blogged about then. Starting yesterday, I began updating a base Fedora 30 configuration again to MySQL 8.0.20. I wrote a testing program for the Java JDBC file last time, and when I ran it this time it told me that I didn’t have the JDBC driver installed, or in the $CLASSPATH. My Java diagnostic script, MySQLDriver.java, returned the following error message:

Error: Could not find or load main class MySQLDriver

The Java JDBC test program code is in the prior post. It simply loads the user, password, database, host, and port statically for my student …

Join Sveta Smirnova, MySQL Engineer at Percona, as she discusses modern solutions for modern database loads.

MySQL is famous for working well in high performing environments. This is the reason why it is the most popular backend for web applications. But our view of what to call high-performance changes over the cycles. Every year we get faster data transfer speed; more devices, connected to the Internet; more users and, as a result, more data.

The challenges MySQL developers have to solve are getting harder over time.

In this session, Sveta will show how use-case scenarios are changing over 25 years of MySQL history. She will show what did MySQL engineers do to keep the product up to date and cover topics such as handling a large number of active connections and high volumes of data as well as how the latest MySQL versions handle increased load better.

After attending this …

After I updated a Fedora 30 instance, I could no longer connect to the MySQL database. An attempt to connect raised the following error:

Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

My guess was correct, the mysqld.service got removed during the update (a synonym for upgrade). So, I ran the following command as a sudoer user:

sudo systemctl enable mysqld.service

It creates the following symbolic link:

Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

That would start the MySQL Daemon (mysqld) on the next restart of the OS. However, I didn’t want to restart to have access to the service. I simply started it with the following command:

sudo systemctl start mysqld.service

Then, I could connect to the MySQL database. As always, …

As the name indicates, an undo log record contains information about how to undo the recent changes by a transaction. When a transaction writes data, it always makes writes on the tablespace files. InnoDB Undo log stores copy of data that is being modified by any current transaction. So, at this point in time if any other transaction queries for the original data (row) which is being modified,  the undo logs provide the same and serve the purpose. This is what provides a consistent read view ( based on isolation ) during any data modifications.

Here in the above representation, Transaction T1 modifies the data (Data-1). During the time of modification to ensure the reads are consistent, transactions T2  and T3 are given access only to the copy (previous row version) of Data -1 which is stored in the “UNDO” …

In this continuing series of posts of fundamental DBA security tasks,  we will look at passwords again, the good, the bad and the outright ugly. This group of tasks will help you find insecure users with poor passwords and provide … Continue reading →

Usually, database people are familiar with table fragmentation with DELETE statements. Whenever doing a huge delete, in most cases, they are always rebuilding the table to reclaim the disk space. But, are you thinking only DELETEs can cause table fragmentation? (Answer: NO).

In this blog post, I am going to explain how table fragmentation is happening with the INSERT statement.

Before going into the topic, we need to know that with MySQL, there are two kinds of fragmentation:

• Fragmentation where some of the InnoDB pages are completely free inside the table.
• Fragmentation where some of the InnoDB pages are not completely filled (the page has some free space).

There are three major cases of table fragmentation with INSERTs :

• INSERT with ROLLBACK
• Failed INSERT statement
• Fragmentation with page-splits

…