Percona

JANUARY 3, 2024

ProxySQL is a high-performance SQL proxy that runs as a daemon watched by a monitoring process. Most configurations can be done at runtime using queries similar to SQL statements in the ProxySQL admin interface. ProxySQLAdmin> select config into outfile /var/lib/proxysql/proxybkp.cnf; Query OK, 9920 rows affected (0.00

Traffic 110

Traffic Hardware Cache Servers 110

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. SELECT orderid , orderdate , shippeddate FROM dbo. Query the underlying table: SELECT * FROM dbo. In this article, I focus on modification considerations. Orders ; GO.

Servers 130

Servers Code Engineering Database 130

DZone

SEPTEMBER 17, 2020

One of its best features is the Object-relational mapper (ORM), which allows you to make queries to the database without having to write any SQL. Django will allow you to write your queries in Python and then it will try to turn those statements into efficient SQL. One common database problem is that ORMs can cause N+1 queries.

Website 130

Website Database Efficiency Performance 130

Percona

DECEMBER 5, 2022

Type 'c' to clear the current input statement. mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'G 1. sec) mysql> SELECT @@audit_log_include_accounts; + + | @@audit_log_include_accounts | + + | test1@%,test2@% | + + 1 row in set (0.00 Type 'help;' or 'h' for help. What to look for.

Open Source 68

Open Source C++ Servers Database 68

Percona

SEPTEMBER 6, 2023

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. SQL > SHOW INDEX FROM product G 1. The index itself takes up storage space as any other index.

Speed 84

Speed Virtualization Storage Open Source 84

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. As an example from the TPC-C specification, this is the Stock Level procedure. So we call the stored procedure and pass a warehouse id, district id and threshold and receive a stock_count as a result.

Benchmarking 62

Benchmarking Database Network C++ 62

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. Affected Rows : If the triggering event affects multiple rows (e.g.,

Cache 62

Cache Database Games Healthcare 62

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. Window functions can appear only in a query's SELECT and ORDER BY clauses.

C++ 143

C++ Code Design Servers 143

Dynatrace

AUGUST 12, 2019

With OneAgent installed on an application server, Davis, the Dynatrace AI causation engine, continuously analyzes all database statements within the context of your applications. In this way, Dynatrace can tell you which statements need your attention. Chatty database requests – requests that invoke too many database statements.

Database 127

Database Metrics Cache Servers 127

Percona

DECEMBER 14, 2022

OLAP is a major component of overall activity i.e. lots of different kinds of SELECT statements. Projection Pushdown: Only those column(s) within the SELECT are returned further reducing IO. Distributed query engine routes and parallelizes SELECT, DML, and operations. The missing sizzle. Indexes are not feasible .

Database 92

Database Analytics Storage Processing 92

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

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. SELECT custid , companyname FROM Sales. USACustomers.

Database 140

Database Code Design Development 140

SQL Performance

AUGUST 12, 2020

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. It uses the term with list element to represent what T-SQL calls a CTE.

C++ 140

C++ Code Design Database 140

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 128

Tuning Cache Storage Database 128

Percona

JUNE 27, 2023

In addition to the provided SQL functionality, similar to MySQL, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. To access the utilities from within MySQL Shell, use the util global object, which is available in JavaScript and Python modes, but not SQL mode. sql -rw-r --.

Database 73

Database Servers Open Source Availability 73

Percona

AUGUST 16, 2023

In database management, well-formatted SQL code is easier to read and a vital aspect of maintaining clean, efficient, and error-free databases. PostgreSQL developers and database administrators often deal with complex SQL queries, making code readability a critical factor for productivity. What is pgFormatter? cat samples/ex1.sql

Code 79

Code Best Practices Open Source C++ 79

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. SQL Server solves this problem using lock classes. A lock class is a container for locks needing a lifetime extension within the current statement.

Testing 91

Testing Servers Cache Engineering 91

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. WITH ( TABLOCKX ) ( i , lob ) SELECT. BEGIN TRANSACTION ; -- Test statement(s). DECLARE @i bigint ; SELECT @i = LT. INSERT dbo.

Database 98

Database Storage Testing Servers 98

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

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. Let’s look at an example: SELECT. DisplayName. Users AS U.

Servers 61

Servers Cache Database Availability 61

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. ( SELECT empid , mgrid , empname FROM dbo. ( SELECT empid , mgrid , empname FROM dbo. The statement terminated.

C++ 143

C++ Code Servers Availability 143

Percona

OCTOBER 20, 2023

“These appear to be normal tables to SQL statements but are horizontally partitioned across worker nodes. To do this, open a terminal and run the following SQL query: “SELECT version();” This will display the version of Percona PostgreSQL installed on your system. ” Citus official website.

Scalability 93

Scalability Database Open Source Systems 93

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. This paragraph explains the data types selected in the prior examples.

Cache 90

Cache Servers Database Testing 90

SQL Performance

MARCH 22, 2022

Both target performance and efficiency for workloads frequently submitting simple statements. It's almost always better to explicitly parameterize statements, rather than relying on the server to do it. In any event, it would be an unusual workload that does not contain at least some parameterized statements server-side.

Cache 61

Cache Servers Database Code 61

SQL Performance

APRIL 13, 2022

L0 AS ( SELECT 1 AS c. T1 WITH ( TABLOCK ) ( grp , ord , val ) SELECT. 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. INSERT INTO dbo. T1 ( grp , ord , val ) VALUES. (

C++ 136

C++ Strategy Testing Servers 136

Percona

OCTOBER 3, 2023

Full support for database-driven logging Tasks can be arranged in chains A chain can consist of built-in commands, SQL, and executables Parameters can be passed to chains/tasks Installation There are currently two options on how you can install and run pg_timetable. Container installation. For this, please refer to this page. .:

Open Source 79

Open Source Database Servers Programming 79

Percona

JULY 19, 2023

How would you integrate some new fancy processing library into MySQL without having to recreate the complexities in pure SQL? sec) mysql> SELECT ULID(); + + | ULID() | + + | 0x018931B0EA52D664C7CBE83255C3468D | + + 1 row in set (0.00 MySQL Loadable Functions would be the way to go. cd ~/percona-server-8.0.32-24/plugin/ulid/

C++ 82

C++ Servers Benchmarking Best Practices 82

Percona

JULY 27, 2023

SQL > EXPLAIN FORMAT=JSON INTO @myvar SELECT * FROM City WHERE District='Texas'; Query OK, 0 rows affected (0.0043 sec) SQL > SELECT @myvarG 1. You can see the parse tree for selects if you build the server with debug mode enabled. has EXPLAIN FORMAT=JSON that can output data into a user variable.

Servers 84

Servers Open Source Tuning Cache 84

SQL Performance

APRIL 18, 2022

It’s more complicated than you might expect to tell from the information provided in execution plans if a SQL statement uses simple parameterization. It’s no surprise even highly experienced SQL Server users tend to get this wrong, given the contradictory information often supplied to us. Simple Parameterization Applied.

Cache 58

Cache Servers Database Testing 58

SQL Performance

OCTOBER 9, 2019

The relevance to T-SQL is that sometimes we assume that we know everything there is to know about certain T-SQL features, and don't always bother checking the documentation to see if there's more to them. The EOMONTH function was introduced in SQL Server 2012. In my examples I'll use a sample database called TSQLV5.

C++ 111

C++ Database Servers Code 111

SQL Performance

APRIL 8, 2020

This article is the first in a series about the fundamentals of table expressions in T-SQL. I will mainly focus on four types of named table expressions, which are known in T-SQL as derived tables, common table expressions (CTEs), views, and inline table-valued functions (inline TVFs). My expertise is T-SQL.

C++ 143

C++ Azure Database Servers 143

Percona

OCTOBER 10, 2023

When on-prem or DBaaS like RDS or Cloud SQL, it is relatively straightforward to apply a change. Type 'c' to clear the current input statement. Type 'c' to clear the current input statement. Type 'c' to clear the current input statement. Type 'c' to clear the current input statement. Type 'help;' or 'h' for help.

Database 97

Database Performance C++ Engineering 97

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. EXECUTE sys. sp_configure.

Cache 55

Cache Database Servers Testing 55

Percona

DECEMBER 28, 2022

mysql> SELECT AURORA_VERSION(); + + | AURORA_VERSION() | + + | 3.02.2 | + + 1 row in set (0.22 Start a transaction on reader: mysql> SELECT connection_id(); + --+ | connection_id() | + --+ | 21| + --+ 1 row in set (0.27 The SQL thread will apply the query from the relay log. Trying to reconnect.

Storage 93

Storage Servers AWS Database 93

Percona

DECEMBER 1, 2023

partman=# SELECT partman.create_parent('public.students', p_control := 'id',p_type := 'native',p_interval := '100000',p_premake := 3, p_start_partition := '0'); create_parent t (1 row) The structure of the parent table with its partitions created is as follows.

Open Source 79

Open Source Storage Performance Design 79

Percona

JULY 7, 2023

It can lock out the entire table for reading and writing because of the exclusive lock acquired by the ALTER TABLE statement that changes the data type. Changing the data type on a column in a big table is almost always a pain. It works for any data type; let’s look at Integer to Big Integer change as an example.

Open Source 88

Open Source Testing Design Code 88

SQL Performance

DECEMBER 3, 2019

SELECT TOP ( 7000 ) DATEADD ( DAY , ABS ( object_id % 730 ) , '20171101' ) , 1. SELECT TOP ( 50 ) DATEADD ( DAY , ABS ( object_id % 30 ) , '20191201' ) , 0. Note that the statement is still parameterized, but it looks a bit different: Tomfoolery. all_columns.

Database 81

Database Efficiency Cache Code 81