Percona

JANUARY 2, 2024

Managing database servers involves different aspects, among which security is critical. However, we know having a “runaway” grant is always a possibility, especially in environments with a large number of servers. Fortunately, in Percona Server for MySQL , this feature is free to use. How does it work?

Servers 81

Servers Database Open Source Scalability 81

SQL Performance

NOVEMBER 10, 2021

Last month I explained when SQL Server inlines iTVFs, it applies parameter embedding optimization by default. Parameter embedding means SQL Server replaces parameter references in the query with the literal constant values from the current execution, and then the code with the constants gets optimized. MyOrders. (

Cache 121

Cache Servers Code Database 121

SQL Performance

OCTOBER 13, 2021

I also compare them with stored procedures, mainly focusing on differences in terms of default optimization strategy, and plan caching and reuse behavior. In my examples I’ll use a sample database called TSQLV5. The former is an inlinable named table expression based on a single query similar to a view and is the focus of this article.

Cache 144

Cache Strategy C++ Code 144

The Netflix TechBlog

JUNE 15, 2022

This post will discuss how Studio Search supports querying the data available in these indices. With a goal of abstracting users away from the complexity of interacting with Elasticsearch directly, we landed on a custom Studio Search DSL reminiscent of SQL. From the grammar, ANTLR generates a parser that can walk the parse tree.

Engineering 183

Engineering Architecture Availability Tuning 183

Percona

JULY 31, 2023

In the first case, all the heuristics are kept at the application layer, and the database has little or no effect on the data quality. The second case has the rules required by management are handled by the database itself, not relying on the software developers to be fully cognizant of rules. Some examples?

Database 73

Database Open Source Servers Code 73

Percona

SEPTEMBER 6, 2023

Functional indexes are found in both of Percona’s relational databases, MySQL and PostgreSQL, but they are probably the least used and most understood index type, aside from geospatial. Definitions The examples below use MySQL, but it is helpful to check their competition’s documentation to help explain what a functional index is.

Speed 80

Speed Virtualization Storage Open Source 80

SQL Performance

AUGUST 1, 2022

Recently I covered some of the T-SQL improvements in SQL Server 2022 here , and Aaron Bertrand covered additional improvements here. Microsoft just announced the release of SQL Server 2022 CTP 2.1. This release includes a number of additional interesting T-SQL improvements, which include: The distinct predicate.

Servers 142

Servers Testing C++ Code 142

Percona

JUNE 23, 2023

I’ll explain using a bad analogy: What type of car is best? But you cannot parallel park it or use it to run down to the store for some chips and beer. A small economy car is better for that store run and is operated at a fraction of the cost of the dragster. ” — with no resolution.

Database 118

Database Transportation Servers Cloud 118

Scalegrid

AUGUST 30, 2019

Redis , short for Remote Dictionary Server, is a BSD-licensed, open-source in-memory key-value data structure store written in C language by Salvatore Sanfillipo and was first released on May 10, 2009. Instead, Redis stores data in data structures which makes it very flexible to use. Redis Strings Use Cases.

Cache 232

Cache Ecommerce Social Media Database 232

Percona

JANUARY 4, 2023

While working, the customer raised two interesting questions: how can he make complete use of the performance schema, and how can he find what he requires? I realized that it is important to understand the insights of the performance schema and how we can make effective use of it. T he MySQL version I am using here is 8.0.30.

Performance 98

Performance C++ Monitoring Servers 98

SQL Performance

SEPTEMBER 21, 2022

The need to manipulate data at the bit level with bitwise operations isn’t common in T-SQL, but you might stumble into such a need in some specialized scenarios. Some implementations store a set of flags (yes/no, on/off, true/false) in a single integer or binary-typed column, where each bit represents a different flag.

Servers 122

Servers C++ Code Cache 122

Percona

OCTOBER 20, 2023

This blog post is part two of a multi-post series about using the Citus extension with Percona Distribution for PostgreSQL. We will use the “Sharding Technique” with Percona Distribution for PostgreSQL. “These appear to be normal tables to SQL statements but are horizontally partitioned across worker nodes.

Scalability 91

Scalability Database Open Source Operating System 91

SQL Performance

JANUARY 23, 2020

Tom Davidson, Opening Microsoft's Performance-Tuning Toolbox SQL Server Pro Magazine, December 2003. Waits and Queues has been used as a SQL Server performance tuning methodology since Tom Davidson published the above article as well as the well-known SQL Server 2005 Waits and Queues whitepaper in 2006.

Tuning 115

Tuning Servers Monitoring Hardware 115

HammerDB

OCTOBER 23, 2023

HammerDB uses stored procedures to achieve maximum throughput when benchmarking your database. HammerDB has always used stored procedures as a design decision because the original benchmark was implemented as close as possible to the example workload in the TPC-C specification that uses stored procedures.

Benchmarking 62

Benchmarking Database Network C++ 62

Percona

JULY 5, 2023

Long database service downtime Some queries become slow Applications stop working due to incompatibilities Data loss This is what can happen if MySQL’s major upgrade to 8.0 From my experience as a Support Engineer, I think the most frequent problem is query performance after an upgrade. Upgrading to MySQL version 8.0

C++ 115

C++ Database Servers Testing 115

SQL Performance

SEPTEMBER 8, 2021

In Part 9 I compared views to derived tables and CTEs, and in Part 10 I discussed DDL changes and the implications of using SELECT * in the view’s inner query. You can impose further restrictions to modifications through views by using an option called CHECK OPTION, which I’ll cover in this article. Sample Data. Orders ; GO.

Servers 130

Servers Code Engineering Database 130

Percona

JUNE 1, 2023

Hello friends, on certain occasions, some clients, for whatever reason, ask us to migrate a supposedly “equivalent” brand of an engine in terms of belonging to the MySQL family or ecosystem. In this case, we will be analyzing the migration of MariaDB to Percona Server for MySQL 8 in particular. Simple, right?

Servers 71

Servers Database Testing Open Source 71

Percona

SEPTEMBER 1, 2023

MySQL server performance can sometimes be perplexing, and if you’ve ever wondered about the role of triggers in influencing your MySQL server’s memory allocation, this post is for you. This code can include SQL statements, procedures, or other actions defined within the trigger. What is a Trigger in MySQL?

Cache 59

Cache Database Games Healthcare 59

Percona

JANUARY 3, 2023

I thought it was easy that we can use quotes or backtick symbols to specify the table to drop. sec) mysql> Drop table `#Tableau_01__Test`; Query OK, 0 rows affected (0.01 In this blog post, I will showcase an issue we faced when dropping a table and how it was resolved. The following example recreates the problem.

Database 73

Database Servers Storage Systems 73

SQL Performance

OCTOBER 14, 2020

In my examples I’ll continue using the sample databases TSQLV5 and PerformanceV5. I explained that when SQL Server optimizes a query involving derived tables, it applies transformation rules to the initial tree of logical operators produced by the parser, possibly shifting things around across what were originally table expression boundaries.

C++ 140

C++ Servers Code Efficiency 140

SQL Performance

MARCH 17, 2020

Back in 2014, I wrote an article called Performance Tuning the Whole Query Plan. This follow-up post revisits the question for SQL Server 2019, using a larger number of rows. I will be using the 50GB Stack Overflow 2013 database , but any large table with a low number of distinct values would do. Test Environment.

Servers 86

Servers Tuning Database Efficiency 86

SQL Performance

JUNE 10, 2020

The relational model and the standard querying language that is based on it are supposed to deal only with the conceptual aspects of the data and leave the physical implementation details like storage, optimization, access and processing of the data to the database platform (the implementation ).

C++ 109

C++ Database Servers Code 109

SQL Performance

APRIL 13, 2022

The challenge itself isn’t complicated to handle when using well-known techniques, which primarily employ window functions. Use the following code to create a table called T1 and populate it with a small set of sample data: SET NOCOUNT ON ; USE tempdb ; DROP TABLE IF EXISTS dbo. And I found such a solution. The challenge.

C++ 136

C++ Strategy Testing Servers 136

Percona

DECEMBER 27, 2022

Encrypting data at rest in a database management system (DBMS) refers to securing data by encrypting it when it is not being used or accessed. Disk-level encryption is a security measure that encrypts all data stored on a disk or storage device. This is independent of the database, and external tools can be used to achieve this.

Database 93

Database Storage Servers Strategy 93

Percona

MAY 15, 2023

The scaling, in that case, is offered by the different number of Replica nodes, no matter if using standard asynchronous replication or synchronous replication. Also, in the case of multi-Primary, the writes will be distributed by transaction. Historically MySQL is great in horizontal READ scale.

Traffic 121

Traffic Scalability Database Servers 121

Percona

JANUARY 5, 2023

It involves removing deleted or outdated rows from tables and indexes and updating statistics used by the query planner. This process is necessary to prevent the accumulation of unnecessary data, known as “dead rows,” which can take up significant space and slow down queries. Multi-version concurrency control (MVCC).

Database 105

Database Performance C++ Efficiency 105

Percona

DECEMBER 14, 2022

Hopefully, you will find it useful in your deliberations. I was pretty optimistic that I’d truly have impressive numbers to share but as it turns out, while there is a potential for significant advantage, a detailed testing regime of the CITUS columnar store extension should be carried out before implementing them into your own environment.

Database 92

Database Analytics Storage Processing 92

Percona

FEBRUARY 20, 2023

Introduction/TLDR: We are considering changing EXPLAIN in Percona Server for MySQL to require less privileges for providing execution plans for INSERT/UPDATE/DELETE statements (and possibly changing the behavior for EXPLAIN SELECT as well), to make it more convenient and safer to use with monitoring and query analysis tools.

Monitoring 81

Monitoring Servers Code Systems 81

Percona

OCTOBER 18, 2023

With proprietary databases, customers often get used to having constant technical support from their vendors, especially if the organization they work for lacks in-house expertise. It’s a complex process involving various factors and meticulous planning. This could cause apprehension in making the jump to open source.

Open Source 90

Open Source Database Hardware Strategy 90

Percona

JULY 20, 2023

It is very common to see many infrastructure layers standing between a PostgreSQL database and the Application server. But it could cause severe performance penalties in many cases and overall throughput deterioration. But it could cause severe performance penalties in many cases and overall throughput deterioration.

Network 61

Network Performance Latency Servers 61

SQL Performance

JUNE 10, 2020

The relational model and the standard querying language that is based on it are supposed to deal only with the conceptual aspects of the data and leave the physical implementation details like storage, optimization, access and processing of the data to the database platform (the implementation ).

C++ 93

C++ Database Servers Code 93

SQL Performance

NOVEMBER 4, 2021

First introduced in SQL Server 2017 Enterprise Edition, an adaptive join enables a runtime transition from a batch mode hash join to a row mode correlated nested loops indexed join (apply) at runtime. The batch mode flavour of hash join stores these rows in an optimized format, and partitions them using one or more hash functions.

Servers 98

Servers Design Database Code 98

SQL Performance

JUNE 8, 2022

When the parser allows simple parameterization to continue, SQL Server® increments the Auto-Param Attempts/sec counter of the SQL Statistics object. As in previous parts, code examples use the Stack Overflow 2010 database on SQL Server 2019 CU 16 with the following additional nonclustered index: CREATE INDEX [ IX dbo.

Cache 54

Cache Servers Database Processing 54

SQL Performance

SEPTEMBER 6, 2022

Let’s look at some examples using a fresh database. I happen to be using SQL Server 2019 CU16 but the details I’ll describe haven’t materially changed since partition level lock escalation was added to SQL Server 2008. USE Escalation ; GO. Testing environment. CREATE OR ALTER PROCEDURE dbo.

Database 98

Database Storage Testing Servers 98

Percona

AUGUST 15, 2023

In this blog post, I provide an in-depth introduction to MySQL Replication, answering what it is, how it works , its benefits and challenges, as well as reviewing some of the MySQL replication concepts that are part of the MySQL environment (and Percona Server for MySQL specifically). Set the server-id to a unique value.

Servers 52

Servers Database Availability Traffic 52

SQL Performance

AUGUST 11, 2021

Often the input is time series data stored in a table where the rows represent measurements taken at regular time intervals. For example, the measurements could be temperature and humidity readings taken every 5 minutes, and you want to group the data using hourly buckets and compute aggregates like average per hour. DATE_BUCKET.

Azure 139

Azure Code Database Servers 139

SQL Performance

APRIL 13, 2020

In my last post , I started to outline the process I go through when tuning queries – specifically when I discover that I need to add a new index, or modify an existing one. Up to this point we've identified the problematic query, the index I need, what indexes currently exist on the table, and whether or not those indexes are being used.

Tuning 137

Tuning Cache Metrics Testing 137