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. I’ll be using the sample database TSQLV6 in the examples in this article. You can download this sample database here.

Servers 142

Servers Testing C++ Code 142

HammerDB

JULY 5, 2022

Python is a popular programming language, especially for beginners, and consequently we see it occurring in places where it just shouldn’t be used, such as database benchmarking. We use stored procedures because, as the introductory post shows, using single SQL statements turns our database benchmark into a network test).

Benchmarking 98

Benchmarking Database C++ Virtualization 98

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. What is a stored procedure?

Benchmarking 62

Benchmarking Database Network C++ 62

Percona

JUNE 15, 2023

Sometimes we might need to install/upgrade Percona Server for MySQL /MySQL 8 to a particular version in a test or production environment. Install Percona Server for MySQL 8 specific version packages via repository. Download specific Percona Server for MySQL 8 tarball packages and install them manually. bullseye amd64 1.7

Servers 83

Servers C++ Database Systems 83

Percona

DECEMBER 7, 2023

Ensuring the security and resilience of your data hinges on having a robust backup strategy, and Percona XtraBackup (PXB), our open source backup solution for all versions of MySQL, is designed to make backups a seamless procedure without disrupting the performance of your server in a production environment. Percona XtraBackup 8.1

C++ 93

C++ Open Source Servers Database 93

Percona

MARCH 8, 2023

Over the years of being a DBA, I had to deal with all kinds of problems in the database. Still, the users keep falling into this trap, maybe because of a poorly configured database, a change in the application components, or just because of a sudden increase of connections in the application. A lot has been written about this error.

C++ 94

C++ Database Servers Open Source 94

Scalegrid

JULY 13, 2020

Oracle Database is a commercial, proprietary multi-model database management system produced by Oracle Corporation, and the largest relational database management system (RDBMS) in the world. While Oracle remains the #1 database on the market, its popularity has steadily declined by over 18% since 2013. Oracle Costs.

Open Source 244

Open Source Tuning C++ Database 244

Scalegrid

JULY 29, 2020

PgBouncer defines one pool per user+database combination. A client benefits from a pooled connection only if it connects to a child which has previously served a connection for this database+user combination. PgBouncer allows limiting connections per-pool, per-database, per-user or per-client. Performance Testing.

Benchmarking 246

Benchmarking Database Performance Testing Availability 246

Percona

DECEMBER 15, 2023

A database role can have a number of attributes that define its privileges and interact with the client authentication system.One such attribute is the CREATEROLE attribute, which is important to PostgreSQL database management of users and roles.

C++ 94

C++ Database Open Source Systems 94

Percona

MARCH 13, 2023

to MySQL 8, we could choose to do an in-place upgrade or set up another MySQL 8 server(s) from the existing running MySQL 5.7 server with Percona XtraBackup. In my lab, we have two test nodes: PRIMARY: mysql57 (192.168.56.111) with Percona Server 5.7 server instances are ready for an upgrade. and xtrabackup 2.4

Servers 104

Servers C++ Storage Systems 104

Percona

SEPTEMBER 29, 2023

This is a global and dynamic variable, and we can set this mode when the server is running. Let’s test this. To test this, create a new user, “app_user,” with only the privileges for DDL/DML. If you are killing the connections manually or using pt-kill, you can’t avoid the new connection creation.

C++ 96

C++ Servers Open Source Database 96

Testlodge

APRIL 14, 2021

Configuration testing is performed to discover the optimum combinations of software and hardware specifications that allow the system to work without flaws. What is Configuration Testing? However, configuration testing is a time-consuming technique, so the team must create a workable plan. Types of Configuration Testing.

Testing 89

Testing Hardware Virtualization Operating System 89

Percona

MAY 1, 2023

It has default settings for all of the database parameters. It is primarily the responsibility of the database administrator or developer to tune PostgreSQL according to their system’s workload. The performance of a PostgreSQL database has a significant impact on the overall effectiveness of an application.

Tuning 52

Tuning Efficiency Database Performance 52

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++ 103

C++ Database Cache Storage 103

Scalegrid

FEBRUARY 15, 2024

In a world where data security is essential, enabling MongoDB SSL is critical in fortifying your database. Each section is crafted to elevate your database’s security protocol, from acquiring the necessary SSL certificates to configuring server and client connections.

Servers 130

Servers Best Practices Government Network 130

Percona

MAY 2, 2023

That’s why it’s essential to implement the best practices and strategies for MongoDB database backups. Why are MongoDB database backups important? Regular database backups are essential to protect against data loss caused by system failures, human errors, natural disasters, or cyber-attacks.

Best Practices 52

Best Practices Database Storage Servers 52

SQL Performance

AUGUST 2, 2019

I have been making the same recommendations about tempdb since I started working with SQL Server over 15 years ago, when I was working with customers running version 2000. From the customer side, this has been the limit of what can be done*, until SQL Server 2019. is out , I thought it might be time to do a bit of testing myself.

Servers 95

Servers C++ Testing Systems 95

Percona

DECEMBER 5, 2022

There can be an instance of ProxySQL using a lot of memory on the server. This post is related to one of the causes that can use memory on the ProxySQL server and how we handle it. Proxysql has a stats database which we can see with an admin login. Type 'c' to clear the current input statement. Explanation.

Servers 60

Servers Cache C++ Metrics 60

HammerDB

DECEMBER 5, 2020

compared to previous releases is that the workload names have changed from TPC-C and TPC-H to TPROC-C and TPROC-H respectively and therefore a key question is how are the v4.0 The simple answer is nothing, the workloads are exactly the same workloads derived from the TPC-C and TPC-H specifications and HammerDB v4.0

C++ 40

C++ Benchmarking Virtualization Open Source 40

Percona

JUNE 26, 2023

We have faced different levels of corruption related to databases in PostgreSQL. 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. So we need to check whether this base / 16384 / 16391 file is available in / base location.

Hardware 75

Hardware C++ Database Open Source 75

HammerDB

MAY 26, 2022

If you are not already familiar with the programming languages that HammerDB uses, then this earlier post serves as an ideal introduction to what makes up the highest performing GIL free database benchmarking application. The build has been tested on x64 Red Hat 8.X The build has been tested on x64 Windows 10 and 11.

C++ 113

C++ Database Open Source Servers 113

Percona

DECEMBER 29, 2023

Not everything can be addressed on the database side. These connections can accumulate, eventually exhausting server resources and causing the system to become unresponsive. Human factor: In an ideal case, there must be stringent restrictions and enforcement on what load and number of connections can be onboarded to the Database.

Servers 81

Servers Database C++ Airlines 81

Percona

SEPTEMBER 19, 2023

This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur. Physical backups are the backups that consist of raw copies of the directories and files that store database contents. Copyright (c) 2016, 2023, Oracle and/or its affiliates.

C++ 105

C++ Servers Storage Speed 105

Percona

MAY 8, 2023

In simpler terms, logical replication in PostgreSQL is a way to copy data from one database to another in a more flexible and customizable manner compared to physical replication. Instead of copying the entire database, logical replication focuses on replicating changes made to individual rows or transactions. 12 Replica1 10.0.0.80

C++ 100

C++ Servers Database Open Source 100

Percona

OCTOBER 3, 2023

While THP can be beneficial for many applications, enabling it on a database server could have unintended consequences. In this post, we will explore THP, its impact on database servers, and how to disable it for optimal performance and stability. What are Transparent Huge Pages? In this case, THP is enabled.

Database 88

Database Servers Open Source C++ 88

Percona

APRIL 21, 2023

Below is my current topology: [root@monitor ~]# orchestrator-client -c topology-tabulated -alias testcluster | tr '|' 't' 192.168.56.10:3306 Below is my current topology: [root@monitor ~]# orchestrator-client -c topology-tabulated -alias testcluster | tr '|' 't' 192.168.56.10:3306 3306 0s ok 5.7.41-44-log 3306 0s ok 5.7.41-44-log

C++ 84

C++ Monitoring Database Open Source 84

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 Although there are tools and methods to prepare and test the upgrade before going live, often, not all problems can be anticipated. Checking server version.

C++ 118

C++ Database Servers Testing 118

Scalegrid

JUNE 18, 2019

In this blog, we’ll show you how to connect to a MongoDB server configured with self-signed certificates for SSL using both the Ruby MongoDB driver and the popular Object-Document-Mapper (ODM) mongoid. servers.mongodirector.com:27017/test?replicaSet=RS-example-0&ssl=true" The user's database roles. for this example.

C++ 100

C++ Servers Database Network 100

Percona

DECEMBER 5, 2023

Inspired by David’s insights, I embarked on a journey to explore logical replication from a different perspective – within the realm of on-premises server databases. Also, it is a helpful method for version upgrades since the target database can run on a different (minor or major) PostgreSQL version.

Database 77

Database Traffic C++ Servers 77

Percona

JUNE 5, 2023

Sooner or later, one must address this issue; otherwise, one can suffer the consequences of handicapping your entire database infrastructure. The problem is you either don’t or can’t refactor your application servers; something, maybe, about not having enough time in the day. Note that the md5 hashed password is pgbouncer.

Database 121

Database Open Source Servers C++ 121

HammerDB

MARCH 25, 2023

Docker build, example CLI scripts were added to build and run the TPROC-C workload in the Tcl language. these were enhanced to also add Python based scripts, and to include scripts for both TPROC-C and TPROC-H and a driver script for Linux environments. With the HammerDB v4.5 In HammerDB v4.6 With HammerDB v4.7 hammerdbcli auto./scripts/tcl/maria/tprocc/maria_tprocc_buildschema.tcl

C++ 83

C++ Virtualization Benchmarking Database 83

HammerDB

OCTOBER 17, 2018

For a really quick answer create a schema with 250-500 warehouses per server CPU socket for more details size as follows. . The official TPC-C test has a fixed number of users per warehouse and uses keying and thinking time so that the workload generated by each user is not intensive.

C++ 40

C++ Testing Virtualization Cache 40

Percona

AUGUST 21, 2023

While trying to be as complete yet succinct as possible, I’m detailing how to enable TLS between a PostgreSQL and the OpenLDAP server. Username accounts, but not passwords, are created on the Postgres server. Both username accounts and passwords are configured on the LDAP server. 0 ldap ldapserver=127.0.0.1

C++ 70

C++ Servers Open Source Testing 70

Dynatrace

MAY 23, 2022

Shifting from monolith to microservices makes it easier to test, develop, and release innovative features more rapidly. C-level executives “must race to reinvent their organizations for the fast-paced, multiplied innovation world,” says Frank Gens, senior vice president and chief analyst at IDC. Server-side application.

Architecture 192

Architecture DevOps Monitoring Programming 192

Percona

OCTOBER 2, 2023

Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL, MariaDB, MongoDB, and PostgreSQL server and system tasks that are too difficult or complex to perform manually. And did I mention they are open source?! virtual = 2.2G

Cache 100

Cache Database Virtualization Servers 100

Percona

AUGUST 18, 2023

Why do you need multiple instances on the same server? Why not just create another database on the same instance? You can have a host with two or three instances configured as a delayed replica of the source server with SQL Delay of, let’s say, 24hr, 12hr, and 6/3hrs. Backup testing. AFAIK Facebook does this.

Programming 84

Programming Servers Database Hardware 84

Percona

FEBRUARY 16, 2023

I asked ChatGPT the question: “C an we backup and then remove the keyring file used for encryption at rest plugin after MySQL starts? ” keyring_file_data=/var/lib/mysql/keyring_file mysql> create database test; mysql> show create schema testG 1. row Database: test Create Database: CREATE DATABASE `test` /*!40100

Database 117

Database Servers Engineering Testing 117