Dec 05, 2022 |
Insight for DBAs, MySQL, Percona Software
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. The prerequisite for installing Percona’s Audit Log Plugin is to first install Percona Server for MySQL. Percona Server for […]
Dec 01, 2022 |
Insight for DBAs, MySQL, Percona Software
Data masking is a handy tool to obscure sensitive information. Percona Server for MySQL is a free, fully compatible, enhanced, and open source drop-in replacement for any MySQL database. It provides superior performance, scalability, and instrumentation including data masking. With Percona Server for MySQL, you get the enterprise features without the cost. So how does […]
Nov 30, 2022 |
Database Trends, Insight for DBAs, MySQL
While the LAMP stack – Linux/Apache/MySQL/PHP – is not the dominant platform it was a decade ago, there are still many websites that depend on it. One of the pillars of this quartet had a milestone last week when PHP 8.0 passed into End Of Life status. While it will still have limited security issue […]
Nov 09, 2022 |
Insight for Developers, MySQL, Percona Software
MySQL 8.0.31 added INTERSECT and EXCEPT to augment the long-lived UNION operator. That is the good news. The bad news is that you have to be careful using the EXCEPT operator as there is a trick. Let’s start with some simple tables and load some simple data.
|
SQL > create table a (id int, nbr int); Query OK, 0 rows affected (0.0180 sec) SQL > create table b (id int, nbr int); Query OK, 0 rows affected (0.0199 sec) SQL > insert into a (id,nbr) values (1,10),(3,30),(5,50),(7,70); Query OK, 4 rows affected (0.0076 sec) Records: 4 Duplicates: 0 Warnings: 0 SQL > insert into b (id,nbr) values (1,10),(2,20),(3,30),(4,40); Query OK, 4 rows affected (0.0159 sec) Records: 4 Duplicates: 0 Warnings: 0 |
So each table has four rows of […]
Oct 25, 2022 |
Cloud, Insight for DBAs, MySQL
This week is KubeCon in Detroit, and in preparation for attending I have been polishing up my Kubernetes skills. This big rush to put software in containers and have Kubernetes run everything is getting a lot of push in the industry. Many software applications run perfectly well in ready-made packages from a container made for […]
Oct 12, 2022 |
Insight for DBAs, Insight for Developers, MySQL
Oracle releases updates to MySQL on a quarterly basis and the Release Notes for 8.0.31 arrived just before the software. This time around there are some very interesting new features that will be handy including SQL standards support, as well as over 130 bug fixes. The TL;DR: Some nice new features but nothing spectacular. FULL […]
Oct 04, 2022 |
Insight for DBAs, MySQL
If you watched Finding Poorly Designed Schemas and How to Fix Them you witnessed Marcos Albe use some very interesting queries. These queries let you find tables without primary keys, tables with non-integer primary keys, tables that do not use InnoDB, tables and indexes with the most latency, indexes that are 50% larger than the […]
Sep 13, 2022 |
Insight for DBAs, MySQL, PostgreSQL
First, a big thanks for the kind responses to this series, and, as requested, here is an overview of index types available in PostgreSQL. The supporting video with bonus material can be found here. PostgreSQL has several popular index types including B-tree, Hash, GiST, SP-GiST, GIN, and BRIN. Actually, a couple of those are frameworks […]
Sep 01, 2022 |
Insight for DBAs, MySQL, Percona Software
You may have read MyRocks Use Case: Big Dataset and been intrigued enough to want to evaluate RocksDB with InnoDB. It has several advantages including requiring less storage space. So how do you start? On a fresh install of Percona Server for MySQL, install RocksDB by entering sudo apt install percona-server-rocksdb:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
|
percona@DellXPS:~$ sudo apt install percona-server-rocksdb [sudo] password for percona: Reading package lists... Done Building dependency tree Reading state information... Done The following NEW packages will be installed: percona-server-rocksdb 0 upgraded, 1 newly installed, 0 to remove and 32 not upgraded. Need to get 65.3 MB of archives. After this operation, 292 MB of additional disk space will be used. Get:1 http://repo.percona.com/ps-80/apt focal/main amd64 percona-server-rocksdb amd64 8.0.29-21-1.focal [65.3 MB] Fetched 65.3 MB in 8s (8531 kB/s) Selecting previously unselected package percona-server-rocksdb. (Reading database ... 78308 files and directories currently installed.) Preparing to unpack .../percona-server-rocksdb_8.0.29-21-1.focal_amd64.deb ... Unpacking percona-server-rocksdb (8.0.29-21-1.focal) ... Setting up percona-server-rocksdb (8.0.29-21-1.focal) ... * This release of Percona Server is distributed with RocksDB storage engine. * Run the following script to enable the RocksDB storage engine in Percona Server: ps-admin --enable-rocksdb -u <mysql_admin_user> -p[mysql_admin_pass] [-S <socket>] [-h <host> -P <port>] |
Next, we can […]
Aug 29, 2022 |
Insight for DBAs, MySQL, PostgreSQL
Those new to the realm of PostgreSQL from other databases will find little gems sprinkled liberally through the software. One of those gems is the watch command. It can be used to repeatedly run a query over and over again. Pretend you are watching the progress of the importation of a CSV file and want […]
Aug 18, 2022 |
Insight for DBAs, MySQL, PostgreSQL
Thank you for the many kind responses to this series. There are many out there who have MySQL experience and are seeking to expand into PostgreSQL for reasons ranging from simple curiosity to fortifying their professional skills. Now that we have covered the low-level basics, it is time to gain knowledge of some community resources […]
Jul 28, 2022 |
Insight for DBAs, MySQL, PostgreSQL
Episode 8 in the PostgreSQL for MySQL DBAs series covers materialized views. MySQL has had views for many years and they are very useful, but it has never had materialized views. Those who use materialized views appreciate their utility and here we will cover how they are used. This episode starts with regular, non-materialized views […]
Jul 27, 2022 |
Insight for DBAs, MySQL
MySQL 8.0 is now over four years old and Oracle released the latest quarterly offering today with MySQL 8.0.30, which comes with lots of bug fixes and some interesting additions. This is my take on the release notes for those who do not have the time to wade through them, comments in italics are my […]
Jul 18, 2022 |
Insight for DBAs, MySQL, PostgreSQL
Experienced MySQL DBAs exploring PostgreSQL are often shocked to learn that they will need to vacuum their tables. This is not using a Hoover brand-like device or letting a Roomba-like robot loose on the server. Vacuuming is a necessary part of maintenance on a PostgreSQL server. This is why you need to watch Episode 7 […]
Jul 15, 2022 |
Insight for DBAs, MySQL, PostgreSQL
The differences between MySQL and PostgreSQL are often trivial but occasionally there are stark differences. A MySQL DBA wanting to optimize a query on a PostgreSQL server will hopefully have some experience with using EXPLAIN. For the uninitiated, the keyword EXPLAIN is pre-pended to a query to reveal what the server wants to do to […]
Jun 30, 2022 |
Insight for DBAs, MySQL, PostgreSQL
Many MySQL DBAs have expressed interest in learning about PostgreSQL and this series is a guided tour through the basics. One of the ‘Wow! That is Different!’ things for MySQL DBAs looking at PostgreSQL are sequences. Sequences can be used as a rough equivalent to MySQL’s AUTO_INCREMENT but that is only part of the story. […]
Jun 21, 2022 |
Insight for DBAs, MySQL, PostgreSQL
The videos for PostgreSQL for MySQL Database Administrators (DBA) episodes three and four are live here and here. Episode three covers a simple backup and restoration while episode four covers some handy PSL commands. For those of you who missed the first two videos in this series you can find them here: Episode one and […]
May 26, 2022 |
Insight for DBAs, MySQL, Open Source
Last week at Percona Live, I was asked what book I recommend for novices seeking to learn MySQL. For a long time, there has not been a good choice for modern versions of MySQL. Luckily I had just stumbled upon such a book. Now I am happy to recommend The MySQL Workshop – A practical […]
May 24, 2022 |
Database Trends, Insight for DBAs, PostgreSQL
PostgreSQL 15 Beta 1 was announced on May 15th and to keep with the spirit of past peeks at MySQL and MariaDB products, this is a cursory glance at the release notes to see what is coming. This is a preview of all the features that will be in the General Available version when it […]
Apr 27, 2022 |
Insight for DBAs, MySQL
Oracle released MySQL Server 8.0.29 on April 26th and this is a quick review of the release notes. I have put my own comments in italics. So what is in the ’29 release of MySQL Server? Does it come festooned with new, neat features or is it a big bug-fix bonanza? The TL;DR While this […]