Percona

APRIL 24, 2023

Indexes are generally considered to be the panacea when it comes to SQL performance tuning, and PostgreSQL supports different types of indexes catering to different use cases. I keep seeing many articles and talks on “tuning” discussing how creating new indexes speeds up SQL but rarely ones discussing removing them.

Tuning 124

Tuning Cache Storage Database 124

SQL Performance

OCTOBER 20, 2022

When SQL Server reads data under locking read committed isolation (the default for non-cloud offerings) and chooses row-level locking granularity, it normally only locks one row at a time. This is the case for a pipelined (or streaming ) execution plan (one without blocking operators). Lock Lifetimes. Locking optimizations.

Testing 91

Testing Servers Cache Engineering 91

Percona

DECEMBER 5, 2022

Type 'c' to clear the current input statement. The need to audit who is using a database and their activities is crucial in many environments. This functionality is available in the free, open source Audit Plugin that is included in Percona Server for MySQL. Percona Server for MySQL is a drop-in replacement for MySQL. xml version="1.0"

Open Source 68

Open Source C++ Servers Database 68

SQL Performance

SEPTEMBER 6, 2022

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. BEGIN TRANSACTION ; -- Test statement(s). WITH ( REPEATABLEREAD ) ; -- End test statement(s). -- Show locks held by the current transaction.

Database 98

Database Storage Testing Servers 98

SQL Performance

JUNE 9, 2021

As I explained, derived tables and CTEs are statement-scoped named table expressions. Once the statement that defines them finishes, they’re gone. As usual when discussing relational theory, we SQL practitioners are often told that the terminology we’re using is wrong. In Part 2 , Part 3 and Part 4 I focused on derived tables.

Database 140

Database Code Design Development 140

Dynatrace

MAY 21, 2023

The recent innovation around log storage, processing, and analysis— Grail —makes Dynatrace a great solution for security use cases such as threat hunting and investigating the who-what-when-where-why-how of an incident. As a result, teams are turning to technologies such as observability to understand events in their cloud environments.

Analytics 230

Analytics Metrics Cloud Systems 230

SQL Shack

NOVEMBER 9, 2021

In this article, we will discuss the performance details of the SQL TOP statement, and we will also work on a performance case study. Pre-requirement In this article, we will use the Adventureworks2019 sample database.

Performance 106

Performance Database Monitoring 106

SQL Performance

OCTOBER 13, 2021

So far I covered derived tables and CTEs , which are statement-scoped named table expressions, and views , which are reusable named table expressions. So, the easiest way to describe an iTVF is as a parameterized view, although technically you create it with a CREATE FUNCTION statement and not with a CREATE VIEW statement.

Cache 144

Cache Strategy C++ Code 144

SQL Performance

MAY 13, 2020

Last month I provided a background to table expressions in T-SQL. I explained the context from relational theory and the SQL standard. I explained how a table in SQL is an attempt to represent a relation from relational theory. I will demonstrate some of the above use cases and others in this series.

C++ 143

C++ Code Design Servers 143

SQL Performance

AUGUST 4, 2022

The Failed Auto-Params/sec counter is incremented when decoding fails and the statement is not parameterized. Shell plans optimize for very frequent execution of simple statements, bypassing the parsing, parameter replacement, normalization, and decoding stages. [ This series: Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 ].

Servers 61

Servers Cache Database Availability 61

Percona

MARCH 21, 2023

In this blog, I’ll discuss the use case for replication. This can pose a security risk if the user account has privileges not necessary for replication, such as the SUPER privilege or the ability to execute arbitrary SQL statements. It does this to carry out all instructions from its upstream.

Storage 84

Storage Open Source Database Engineering 84

Percona

JANUARY 3, 2024

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. Most configurations can be done at runtime using queries similar to SQL statements in the ProxySQL admin interface.

Traffic 107

Traffic Hardware Cache Servers 107

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

SQL Performance

SEPTEMBER 9, 2020

I describe T-SQL’s support for recursive CTEs, as well as standard elements that are not yet supported by T-SQL. I do provide logically equivalent T-SQL workarounds for the unsupported elements. Recursive CTEs have many use cases. This article is the sixth part in a series about table expressions. Employees ; GO.

C++ 143

C++ Code Servers Availability 143

SQL Performance

JUNE 8, 2022

In part 4, I described the role the parser plays turning a statement with constant values into a parameterized prepared statement. When the parser allows simple parameterization to continue, SQL Server® increments the Auto-Param Attempts/sec counter of the SQL Statistics object. Assigns an initial data type.

Cache 54

Cache Servers Database Processing 54

Dynatrace

MAY 9, 2019

Turned out that a recent deployment of that app included some malformed SQL that was executed against the backend DB2 running on the IBM AS400 Mainframe. A common use case is to let Dynatrace look for Code-Level Exceptions (Java,NET …) that are thrown, but that the service hasn’t translated into an HTTP Status Code. HTTP 4xx, 5xx.

Mobile 134

Mobile Traffic Java Infrastructure 134

Percona

SEPTEMBER 1, 2023

Here’s a step-by-step explanation of how row-level triggers work for each affected row: Trigger Activation : When a triggering event, such as an UPDATE statement, occurs on a table with an associated row-level trigger, the trigger is activated. This blog was originally published in November 2021 and was updated in September 2023.

Cache 59

Cache Database Games Healthcare 59

SQL Performance

MARCH 29, 2022

When SQL Server applies simple parameterization to an ad-hoc statement, it makes a guess about the data type of the replacement parameter. For the time being, let’s look at some examples using the Stack Overflow 2010 database on SQL Server 2019 CU 14. Parameter Data Types. Data Type Inference.

Cache 90

Cache Servers Database Testing 90

SQL Performance

AUGUST 12, 2020

Neither this term, nor its acronym CTE, appear in the ISO/IEC SQL standard specs. Neither this term, nor its acronym CTE, appear in the ISO/IEC SQL standard specs. You can find it in the documentation of Microsoft SQL Server and Azure SQL Database. T-SQL supports it starting with SQL Server 2005.

C++ 140

C++ Code Design Database 140

Percona

JULY 20, 2023

But it could cause severe performance penalties in many cases and overall throughput deterioration. Previously I had written about how the volume of data transmission as part of SQL execution, as well as the cursor location, affects the overall performance. Case 2: Bulk data loading This is the opposite of the previous case.

Network 61

Network Performance Latency Servers 61

Percona

JANUARY 5, 2023

These options are being used in this case as well. These options are being used in this case as well. This option is useful to handle temporary tables on replicas and when used it stops the SQL thread and waits until there are no temporary tables before taking a backup. we also upgraded PXB to 8.0.27 –slave-info.

Database 73

Database Servers Open Source Engineering 73

HammerDB

OCTOBER 23, 2023

Also, note in passing that we include a COMMIT statement in this procedure because one is included in the example code in TPC-C specification. Also, note in passing that we include a COMMIT statement in this procedure because one is included in the example code in TPC-C specification.

Benchmarking 62

Benchmarking Database Network C++ 62

Percona

DECEMBER 14, 2022

OLAP is a major component of overall activity i.e. lots of different kinds of SELECT statements. In this case, the query only reads one column and not 50. This blog is the result of my investigation into column-wise tables. Hopefully, you will find it useful in your deliberations. This weakness is being addressed by the industry.

Database 92

Database Analytics Storage Processing 92

Percona

SEPTEMBER 25, 2023

ProxySQL is a high-performance SQL proxy, which 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. Most configurations can be done at runtime using queries similar to SQL statements in the ProxySQL admin interface. Replica_127.0.0.2>stop

Traffic 91

Traffic Servers Monitoring Processing 91

Percona

SEPTEMBER 6, 2023

In this case, the calculation on the expression must be completed before the data can be inserted into the row. Example one Anyone who has used Structured Query Language (SQL) for over thirty seconds has run into a situation where the query ‘looks’ good, but its results do not, or it runs much more slowly than expected.

Speed 80

Speed Virtualization Storage Open Source 80

Percona

APRIL 3, 2023

SET WITHOUT OIDS; A list of tables with the problem is in the file: tables_with_oids.txt Failure, exiting postgres@xx.xx.xx.xx:~$ more tables_with_oids.txt In database: abs_test public.craft public.ports In database: postgres public.oid_test We can use the below SQL to find tables with OID, and it also generates the DDL to remove.

C++ 100

C++ Database Cache Storage 100

Percona

OCTOBER 11, 2023

Here are the primary SQL commands that we will be discussing in relation to managing MySQL roles: CREATE ROLE and DROP ROLE create and remove roles. To assign privileges to the roles, execute GRANT statements using the same syntax as for assigning privileges to user accounts: GRANT ALL ON mytestdb.* Below are examples of role usage.

Database 88

Database Servers Systems Open Source 88

Percona

JUNE 5, 2023

However, in this case, with a little help from systemd , which manages all service processes, we will solve this little puzzle using PgBouncer with a concoction of configuration files. This SQL statement must be executed against each and every database that is to be accessed by any ROLE connection using PgBouncer.

Database 119

Database Open Source Servers C++ 119

SQL Performance

MAY 20, 2022

Multiple compilation components are involved in successfully turning a SQL statement containing constants into a parameterized prepared statement with typed parameters. configname = 'cost threshold for parallelism' , @configvalue = 50 ; RECONFIGURE ; Statement Parsing. That’s roughly the task facing the parser.

Cache 55

Cache Database Servers Testing 55

Percona

AUGUST 15, 2023

Replication also provides for data availability by enabling failover to a replica in case the primary server becomes unavailable. This blog was originally published in February 2017 and was updated in August 2023. What is MySQL Replication? The below diagram shows an example of a MySQL replication environment. Restart the MySQL service.

Servers 52

Servers Database Availability Traffic 52

SQL Performance

JANUARY 11, 2021

There are features many of us shy away from, like cursors, triggers, and dynamic SQL. There is no question they each have their use cases, but when we see a trigger with a cursor inside dynamic SQL, it can make us cringe (triple whammy). But, as with cursors, triggers, and dynamic SQL, they have their use cases.

Cache 59

Cache Tuning Database Performance 59

SQL Performance

APRIL 13, 2022

I used a SELECT INTO statement to write the result into a temporary table. My test machine has four logical CPUs, running SQL Server ® 2019 Enterprise. However, these techniques require explicit sorting despite the presence of a supporting index. This affects the scalability and response time of the solutions.

C++ 136

C++ Strategy Testing Servers 136

Percona

JUNE 26, 2023

In case of hardware failure, first, we need to fix the hardware issue or migrate our database to new hardware and then perform a restore, as mentioned below. We have faced different levels of corruption related to databases in PostgreSQL. So we need to check whether this base / 16384 / 16391 file is available in / base location.

Hardware 73

Hardware C++ Database Open Source 73

Percona

OCTOBER 10, 2023

When on-prem or DBaaS like RDS or Cloud SQL, it is relatively straightforward to apply a change. You can perform a DIRECT ALTER, use a tool such as pt-osc, or even, for certain cases where async replication is in use, perform changes on replicas and failover. Type 'c' to clear the current input statement. drop_triggers:10000:0.25,copy_rows:10000:0.25,swap_tables:10000:0.25,update_foreign_keys:10000:0.25,analyze_table:10000:0.25

Database 93

Database Performance C++ Engineering 93

Dynatrace

APRIL 16, 2019

Many of our customers also run their.NET, ASP.NET,NET Core, SQL Server, CRM, SharePoint … applications on AWS and have reached out to us in the past to ask about best practices around optimizing these workloads in order to run more efficiently and more cost effective. Step #1: Install Dynatrace.

AWS 125

AWS Best Practices Database Traffic 125

Percona

APRIL 19, 2023

This interface provides plugins and components a way to retrieve notifications related to SQL statements’ lifetime. Primary key use is not suitable in such cases due to the presence of LIMIT and due to the nature of data distribution. MySQL released version 8.0.33 on April 18th, featuring some attention-catching features.

Open Source 90

Open Source Servers Scalability Database 90

SQL Performance

DECEMBER 3, 2019

We can see this because the statement captured is different from the statement we sent to SQL Server, which had a literal 0 : You can eliminate the warning, with perhaps no difference in actual performance, using a couple of tricks. Note that the statement is still parameterized, but it looks a bit different: Tomfoolery.

Database 81

Database Efficiency Cache Code 81