5+ years ago one of my companies launched a product that is effectively a search engine monitoring tool. Is saves a lot of information about search engine results and the destination pages, then allows the users to see for which search phrases each pages ranks.

The workload is heavily write intensive. No matter the number of users we have to perform a bunch of data collection and save that into our database. A large increase in the number of users would increase the amount of reads, but the base workload of collecting all of the results remains the dominant workload for the database server.

We built this originally using MySQL 5.6, which we had used and managed extensively. We began having concerns with write capacity about the time the that AWS was starting to push Aurora as an alternative to MySQL, with cost and performance benefits. It seemed like an easy win, so we clicked the couple buttons and within minutes our database server was converted from MySQL to Aurora.

Things worked well for a long time. The product worked well and customers liked it. We tweaked the application here and there, but most of the base functionality just continued to do its thing. We moved on to developing other products and maintaining this one.

Fast forward a few years and we found that minor complaints had started to pile up. We add some indexes, make some code and queries more efficient. Adding indexes or altering a 500Gb table has some challenges, but there are tools like pt-online-schema-change that make table changes a little easier without downtime.

As time went on, we got better about allocating costs to the each product that we run and I did start to notice that the cost to run the Aurora instance was quite high. The instance cost itself was predictable, but the pricing of Aurora Database Storage includes a seemingly small cost of $0.20 per million I/O requests that was adding up to sometimes $200+ per day! It was at this point that I started to call Aurora a “Pay for Performance” product. Because it had the ability to scale I/O capacity quite high, inefficient queries executed fast enough not to notice. You just get charged more for them! It can be difficult to track down inefficient queries when everything is running fast. Performance Insights was very helpful to track down queries that could be optimized. By adding some indexes, we reduced our Database I/O and got our I/O costs down to under $100/day. On a traditional MySQL instance, with more limited I/O Capacity, these queries would have been more obvious, as they would have executed more slowly and our traditional troubleshooting would have brought them to our attention for the same optimizations. The “pay for performance” aspect of Aurora kept us from fixing the inefficient queries because they were hidden by being charged more.

Comparing Aurora Billed IO’s to MySQL IOPS

In November 2022 AWS announced that GP3 volumes are now available for RDS instances. The public documentation mentions a 3,000 IPS base capacity but doesn’t mention that for 400G+ volumes, that AWS actually spreads your storage over four volumes for theoretical base 12,000 IOPS. For an additional $0.02/IOPS you can increase your capacity up to 64,000 IOPS. So on the high end, the extra 52,000 extra IOPS at $0.02 comes to $1,040/month or about $35/day. There may be additional throughput needed as well, but for our workload, I found that IOPS was the bottleneck more than throughput.

Since we were still paying $60-$100 most days for Aurora Storage IOPS, it makes sense cost-wise to switch back from Aurora to MySQL. I also favor MySQL because it’s what we’re already used to. I’ve always thought that the monitoring and metrics available on Aurora instances wasn’t up to par with the MySQL instances. And there is just enough of a “black box” in Aurora that it makes things difficult.

In trying to estimate how much IOPS we needed if we switch back to MySQL, I found it a bit of work to estimate how much Aurora was using in terms that I’m used to seeing for MySQL. The only IO metrics available are “[Billed] Volume Read IOPS” and “[Billed] Volume Write IOPS”. These are under the “Cluster” in Cloudwatch Metrics and look like they are billed at 1-hour granularities. Make sure to use the “Sum” statistic instead of “Average” or else you will be off a lot! My server had values values of around 4,000,000 to 13,000,000 for reads and 5,000,000 to 15,000,000 for writes. These values lined up pretty well to costs per day that I was able to see in Cost Explorer. When Cloudwatch Metrics showed a combined 500M IO’s for a day, I was charged $100. To convert the “Billed IOs” that Aurora reports, you have to divide by the number of seconds in the period. If looking at one-hour period, the 9,000,000 IO’s averages out to 2,500 IOPS (divide by 3600 seconds). 30,000,000 IO’s in an hour equates to an average of 8,333 IOPS.

Note that these are averages over an entire hour, so peaks within that hour could be dramatically higher! This gave me confidence that the 12,000 baseline IOPS and availability to pay for up to 64,000 IOPS with GP3 volumes should be able to perform the same workload that was being handled by Aurora.

The effect of Double-Writes
Also, announced in the past month was support for RDS Optimized Writes on newly launched instances within certain instance types. Its unclear if Aurora already has this type of feature enabled, so I’m not certain if the Billed Aurora IO Writes mentioned above would be the number calculated from there, or potentially half of that. Please let me know in comments below if you know, and I’ll update here once I’ve experimented and been able to tell.

There are many articles around the Internet that discuss enabling Encryption in-transit to MySQL servers. They all include instructions about creating Client Certificates, but they don’t clearly explain that Client-Side Certificates are not a requirement to achieve end-to-end encryption between client and server.

Creating Client certificates that can be authenticated by the server can be complicated. It is not even possible in some scenarios, such as using servers hosted by AWS RDS, since AWS runs its own Certificate Authority. But don’t let that stop you. Below, I will demonstrate that enabling SSL/TLS on the server, and using a client that supports encryption is sufficient to securely encrypt traffic between the two.

First, I set up a MySQL server on RDS using MySQL version 8.0.25. Nothing special here, except that I’m going to make it “Publicly Accessible” which gives is a Public IP Address so that I can access it over the Internet. My Security Group here already allows inbound port 3307 from my desired IP Addresses for testing:

aws rds create-db-instance \
    --db-instance-identifier=encryption-tester \
    --allocated-storage=20 \
    --db-instance-class=db.t3.micro \
    --engine=mysql \
    --master-username=admin \
    --master-user-password="thepasswordIchose" \
    --vpc-security-group-ids="sg-0bf6fa7080100e55b" \
    --backup-retention-period=0 \
    --port=3307 \
    --no-multi-az \
    --engine-version=8.0.25 \
    --publicly-accessible

It takes several minutes for my Database Instance to be created, then I can log into it with the command:

mysql -h encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com -u admin --port=3307 -p

I run the command show status like 'ssl_cipher'; and look at that! My connection is encrypted already, as indicated by Cipher method present:

$ mysql -h encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com -u admin --port=3307 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.25 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show status like 'ssl_cipher';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| Ssl_cipher    | ECDHE-RSA-AES128-GCM-SHA256 |
+---------------+-----------------------------+
1 row in set (0.01 sec)

I haven’t set up any client certificates or anything special, yet my connection is encrypted. But let’s not take the session variable’s word for it. Lets double-check by capturing and inspecting some packets.

I’ll run tcpdump with this command:

sudo tcpdump -i any host encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com  -s 65535 -w /tmp/initial-connection.pcap

To make it quick, Instead of using a full packet-analysis program, I just run the strings command to look for text strings in the packet capture:

17:05 $ strings -8  /tmp/initial-connection.pcap
=JpgP~eS
mysql_native_password
Washington1
Seattle1"0
Amazon Web Services, Inc.1
Amazon RDS1%0#
Amazon RDS us-east-1 2019 CA0
210824170035Z
240822170850Z0
:encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com1
Amazon.com1
Seattle1
Washington1
Seattle1
Washington1"0
Amazon Web Services, Inc.1
Amazon RDS1 0
Amazon RDS Root 2019 CA
:encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com0
Seattle1
Washington1"0
Amazon Web Services, Inc.1
Amazon RDS1 0
Amazon RDS Root 2019 CA0
190919181653Z
240822170850Z0
Washington1
Seattle1"0
Amazon Web Services, Inc.1
Amazon RDS1%0#
Amazon RDS us-east-1 2019 CA0
HId0%aC>

Looks like a lot of stuff in that output about the certificate and SSL negotiation, but nothing containing the queries I executed.

I’m going to try it again and specifically disable encryption to see what the packets look like and ensure they contain the plain-text statements and responses I expect:

This is my SQL session:

17:05 $ mysql --ssl-mode=DISABLED -h encryption-tester.accountidstuff.us-east-1.rds.amazonaws.com -u admin --port=3307 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.25 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)

mysql> show status like 'ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
1 row in set (0.01 sec)

mysql> \q

The strings in the packets captured during that session clearly contain things relevant to the commands that I executed:

17:05 $ strings -5 /tmp/skip-ssl.pcap
1%aCu
1%a\u
8.0.25
*Bi|tm
UkU-dsK
mysql_native_password
admin
mysql_native_password
Linux
_client_name
libmysql
24817
_client_version
5.7.35  _platform
x86_64
program_name
mysql
select @@version_comment limit 1
@@version_comment
Source distribution
select version()
        version()
8.0.25
show status like 'ssl_cipher'
performance_schema
session_status
session_status
Variable_name
Variable_name
performance_schema
session_status
session_status
Value
Value
Ssl_cipher

Conclusion:

SSL Client-Certificates are NOT required for traffic to be encrypted to a MySQL server. In fact, with a modern client and server, SSL is preferred and is automatically enabled. Just like I’d expect for traffic to be encrypted by default in 2021.

So what is the purpose of Client Certificates during a MySQL Connection

Client Certificates are intended to verify the identity of the Client. They are an extra step of authentication beyond a typical username and password. By presenting a client certificate that has been properly signed by a recognized Certificate Authority, the client is proving that their identity or system has been verified by the Certificate Authority.

Because SSL is complicated and is not well understood, many well-meaning people have instructions for creating a client key and client certificate, and transmitting those to authorized users. While that does provide a second piece of information needed to authenticate to the server, it is not how a secure client should authenticate.

The proper, fully secure method for a client to get a certificate is for the client to create its own private key. It should never share that key, even with the Certificate Authority. With the private key created, it would then create a certificate signing request (CSR), and present only the certificate signing request to the Certificate Authority. The certificate authority takes whatever steps it requires to verify the authenticity of the client, then provide the client back with a Client Certificate signed by the Certificate Authority. That Client Certificate is the client’s evidence that it’s identity has been verified by the Certificate Authority. The Certificate Authority is able to provide the client certificate without ever having the client’s private key.

Best Practices

If you run a MySQL Server and want to require that all clients to connect via SSL/TLS, you can set the global setting require_secure_transport to true. To require SSL only for specific users, use the CREATE USER ... REQUIRE SSL statement when creating the MySQL user

For a long time, I’ve never been able to answer some basic questions that I thought fundamental to optimizing server performance. MySQL gives you some server-wide metrics about activity, but none of it is broken down per-table so that an application developer could look into where to reduce the number of writes, or generally where to focus their attention in order to improve the server performance.

I finally got ambitious enough to tackle this problem and asked a question on StackOverflow at https://stackoverflow.com/questions/39459185/mysql-how-to-count-the-number-of-inserts-updates-to-a-table

A commenter named barat pointed me to this post which had the insightful idea of parsing the binary log for analysis.
Since my servers are generally hosted on AWS, I don’t have direct access to the binary log, so I had to retrieve those. The MySQL documentation for the mysqlbinlog command briefly mentions how to read the binary log from a remote server. It took some experimentation to get the right command and output options with all of the data I wanted. Specifically, the `–base64-output=DECODE-ROWS –verbose` options which translate some of the row-based logging into MySQL commands that can be parsed.

The first step is to create a user that has access to the binary logs. I used the main ‘admin’ user that RDS creates because it was convenient. If creating a new user, you probably need to grant the REPLICATION_SLAVE privilege.

You can see which binary logs are available on the server with the SHOW BINARY LOGS; command:

mysql> show binary logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| mysql-bin-changelog.232522 | 16943219  |
| mysql-bin-changelog.232523 | 32300889  |
| mysql-bin-changelog.232524 | 15470603  |
+----------------------------+-----------+

Then you can actually retrieve the log and print to STDOUT using this command:

14:01 $ mysqlbinlog --read-from-remote-server \
  --host myhost.somerandomchars.us-east-1.rds.amazonaws.com \
  --user admin \
  --password="mypassword"
  mysql-bin-changelog.232522

Note that if you get the error below, you need to make sure that your MySQL client and server tools are using the same version. I originally attempted to use MySQL 5.5 tools with a MySQL 5.6 server.

ERROR: Got error reading packet from server: Slave can not handle replication events with
the checksum that master is configured to log; the first event 'mysql-bin-changelog.232519'
at 4, the last event read from '/rdsdbdata/log/binlog/mysql-bin-changelog.232519' at 120,
the last byte read from '/rdsdbdata/log/binlog/mysql-bin-changelog.232519' at 120.

After that, it was just a matter of parsing the file for the relevant commands. I’ve put all of that logic now into a quick PHP script that I can reuse anywhere. Now, I can go through a bunch of binary logs on a server and see which tables are updated the most frequently with output like this:

Parsed 1,096,063 lines spanning 300 seconds between 2016-09-13 03:05:00 and 2016-09-13 03:10:00
master                         metrics                        update          = 43570
master                         metrics                        insert into     = 9
DEFAULT                        accounts                       update          = 501
DEFAULT                        users                          update          = 5
DEFAULT                        logins                         insert into     = 1
mysql                          rds_heartbeat2                 insert into     = 1

I’ve committed this project to Github at https://github.com/sellerlabs/mysql-writes-per-table for others to use.

I’ve had this error occur when something went terribly wrong on a master server and it had to be rebooted. It appears that the master’s binary log file was not completely written to the server before it was rebooted. However part of the master log file was transmitted to the slave before that time. The error is coming from the slave server when it tried to reconnect to the master. It is trying to connect to the master server and start copying the binary log file from a position which the master does not have.

The proper way to fix this error is to completely resync the data from the master and begin replication over again. However, in many cases, that is impractical, or not worth the hassle, so I was able to fix the problem by setting the slave back to a valid position on the master and then skipping forward over the missing entries.

First, you need to find out what the last valid position is on the master. Run ‘SHOW SLAVE STATUS’ on the slave to figure out which master log file it was reading when the master crashed. You’ll need the value of the Relay_Master_Log_File parameter. Also take note of the Exec_Master_Log_Pos value.

Next, go to the master, and ensure that log file exists in the MySQL data directory. The file size will probably be less than the Exec_Master_Log_Pos value that the slave had stored. To find the last valid position in the log file use the mysqlbinlog utility. The command

mysqlbinlog [LOGFILE] |tail -n 100

should show the final entries in the binary log. Find the last line in the file that looks similar to this:

#120512 13:30:44 server id 3  end_log_pos 8796393       Query   thread_id=138884124     exec_time=0     error_code=0

The number after end_log_pos is the last valid entry that the master has available. The difference between the master log position that the slave had, and this highest one on the master will give you some idea how far ahead the slave got before the master crashed.

You can now go back to the slave and issue the commands:

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_POS=8796393;
START SLAVE;

This will tell the slave to try to start from that valid position, and continue from there.

There is a strong possibility that you’ll run into some duplicate key errors if the slave was very far ahead of where the master’s log ended. In that case, you can issue this command to bypass those one at a time (or more if you want to skip more than one)

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

!! Note that if you have to do this, then your data between the master and slave is almost certainly inconsistent !! Be sure to understand your data to make sure that this is something you can live with.