In this blog post, we will discuss how to validate at the operating system level the effects of changing the innodb_flush_method to variations other than the default (particularly for O_DIRECT which is most commonly used) and the use of innodb_use_fdatasync.IntroductionFirst, let’s define what the innodb_flush_method parameter does. It dictates how InnoDB manages the flushing of data […]

Oracle recently released MySQL 8.0.36 and MySQL 8.3, and this is a perusal of the release notes to determine what is new, what is in the seventy-three bugs fixed, and what has changed in the three months since the last releases. Comments in italics are my own and do not reflect the view of anyone […]

One of our customers wants to create a table having a column of data type TEXT with the default value, but they encountered an error: [crayon-661430a432b94245573814-i/]. It seems reasonable at first glimpse, as we know that each BLOB, TEXT, GEOMETRY, or JSON value is represented internally by a separately allocated object. This is in contrast […]

When working with group replication, MySQL router would be the obvious choice for the connection layer. It is tightly coupled with the rest of the technologies since it is part of the InnoDB cluster stack.The problem is that except for simple workloads, MySQL router’s performance is still not on par with other proxies like Haproxy […]

One of the most underlooked parameters to configure MySQL is server_id, which is an integer number to identify a server inside a replication topology uniquely. Note that two servers within a replication set can’t have the same server_id value.It is generally set up as a “random” number, just different from the one configured on the […]

Managing storage and performance efficiently in your MySQL database is crucial, and general tablespaces offer flexibility in achieving this. This blog discusses general tablespaces and explores their functionalities, benefits, and practical usage, along with illustrative examples.What are MySQL general tablespaces?In contrast to the single system tablespace that holds system tables by default, general tablespaces are […]

ProxySQL is a high-performance SQL proxy that runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime.The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.The proxy is designed to run continuously without needing to […]

Managing database servers involves different aspects, among which security is critical. We know that we should always grant the minimal required permissions to the different user accounts in the database, as having a user with high-level permission can lead to unexpected results, such as having an index drop affecting the system performance or even more […]

We’re happy to introduce Audit Log Filter — our newly upgraded audit plugin. Its functionality has been significantly improved in comparison to Audit Log and currently mirrors the functionality of the MySQL Enterprise Audit plugin. Starting with Percona Server for MySQL 8.0.34-26, the Audit Log Filter is available in a technical preview mode. You’re welcome […]

The efficiency of database queries in MySQL can make all the difference in the performance and responsiveness of applications. In this blog post, I’ll dig into MySQL query optimization and show how MySQL uses indexes in cases of queries using sorting and limiting. While sorting may seem simple, it’s important to understand how to do it efficiently and effectively to ensure your queries are optimized and use better indexing.

Since sorting your rows is not free, it can take a significant amount of resources and time to sort large data sets; thus, it’s important to do it cautiously. If you don’t need your rows in a certain order, don’t order them.

However, if you need to order your rows, doing it efficiently and effectively is essential to optimize your queries. You must understand how to use indexes to make sorting cheaper. 

Looking at this, can you say which is faster: LIMIT 1 or LIMIT 10? Presumably, fetching …