The Window Ranking functions: ROW_NUMBER(), RANK(), and DENSE_RANK() each rank rows with an increasing integer value. I wrote a previous blog post, ROW_NUMBER() Window Function – find duplicate values, where I covered how the ROW_NUMBER() window function can be used to target any duplicate rows, with the use of the PARTITION BY clause in the OVER() clause. In this post, I cover the differences between RANK(), and DENSE_RANK() in handling any ties according to the sorting performed by the ORDER BY clause with regards to the assigned increasing integer. Continue reading and see examples…

…

In this blog, I am going to demonstrate how to create your own consolidated audit log archive across many mysql instances. In a followup I’ll show how to extend this example by creating a simple hash chain on that archive – so you can prove whether or not its been modified or tainted in any way and if so where.…

Facebook Twitter LinkedIn

So you’ve just added some more memory to your MySQL server; now what? If you’ve been around the MySQL block for a while, you know that nothing is automatically changed to take advantage of this new system RAM. Let’s have a look at a few parameters you would want to adjust.

InnoDB Parameters innodb_buffer_pool_size

The InnoDB buffer pool is “…the memory area that holds cached InnoDB data for both tables and indexes.” This parameter is probably the #1 tuning parameter in MySQL. If your buffer pool is too small, then InnoDB must spend extra CPU/Disk time, loading, and unloading pages in/out of memory. This is time better spent executing your queries.

The default size of this cache is 128MB; woefully small for any serious database. Increasing the size of this cache allows more frequently accessed pages to remain in memory for the fastest access. Obviously, you do not need a buffer pool which is larger than your …

In the previous blog, we saw that users don’t lose anything by moving to MySQL on ARM. Infact, users are set to gain performance and save cost. In this blog post we will see performance numbers and analyze them to understand points where ARM scores.

Benchmarking Setup

Benchmarking is done using sysbench. We have used the CPM model. You can read more about it here. In short the idea is to keep the cost the same and hunt for comparable configuration.

• Server Configuration:
  • sysbench 100 tables * 3 millions (roughly 69 GB of data)
  • We tried 2 combinations:
    • CPU Bound: buffer pool = 80 GB so complete data in memory (lesser IO).
    • IO Bound: buffer pool = 35 …

Previously posted on link at Nov 3, 2020. Traditionally, MySQL has been used to power most of the backend services at Bolt. We've designed our schemas in a way that they're sharded into different MySQL clusters. Each MySQL cluster contains a subset of data and consists of one primary and multiple replication nodes. Once data is persisted to the database, we use the Debezium MySQL Connector to capture data change events and send them to Kafka.

Understanding how MySQL uses memory is key to tuning it for optimal performance as well as troubleshooting cases of unexpected memory usage, i.e. when you have MySQL Server using a lot more than you would expect based on your configuration settings.

Early in MySQL history, understanding memory usage details was hard and included a lot of guesswork.  Is it possible that some queries running require a large temporary table or allocated a lot of memory for stored user variables?  Are any stored procedures taking an unexpectedly high amount of memory? All could be reasons for excessive MySQL memory usage, but you would not easily see if that is just the case.

All that changed with MySQL 5.7, which added memory instrumentation in Performance Schema, and with MySQL 8.0, this instrumentation is enabled by default, so you can get this data from pretty much any running instance.

If you’re looking for current memory …

 This is a reminder that MySQL 5.6 reaches End of Life status in February 2021.  I have been sending out such reminders since February of this year. But in my presentations this year when I bring up the 5.6 EOL, there are a lot of users out there that are surprised to hear the news.

    Wikipedia says ""End-of-life" ("EOL") is a term used with respect to a product supplied to customers, indicating that the product is in the end of its useful life (from the vendor's point of view), and a vendor stops marketing, selling, or rework sustaining it. (The vendor may simply intend to limit or end support for the product.)" 

…

MySQL 8.0.22 introduces many new improvements and updates, many of which deserve their own blog post for a deep dive into the new features.  Among the notable changes are: Prepared Statements, SHOW PROCESSLIST, TIMESTAMP, Read Only Schema, Error Log, User Management, Optimizer, Replication, Keyring, Router, and more.

Here are the MySQL 8.0.22 top blog posts:

Server

• The MySQL 8.0.22 Maintenance Release is Generally Available
• Announcing MySQL Server 8.0.22, 5.7.32 and 5.6.50

Shell

• MySQL Shell 8.0.22 …

MySQL 8.0.22 introduces many new improvements and updates, many of which deserve their own blog post for a deep dive into the new features. Among the notable changes are: Prepared Statements, SHOW PROCESSLIST, TIMESTAMP, Read Only Schema, Error Log, User Management, Optimizer, Replication, Keyring, ...

Let’s answer a question which, to my knowledge, has never been systematically addressed: What are the requirements for a production-ready query metrics app at scale? I am uniquely qualified to answer that because I have written four query metrics apps, two of which are the open-source standard for MySQL: pt-query-digest and the original (v1) code behind the query metrics/analysis part of Percona Monitoring and Management. I’ve also published a couple of packages related to query metrics: go-mysql/slowlog and go-mysql/query.