7.3    Administration

KI-Tools helfen auch bei der Administration von Datenbankmanagementsystemen. Ob es nun um eher triviale Aufgaben wie das Einrichten neuer Datenbanken bzw. das Durchführen eines Backups geht oder ob Sie einen DBMS komplett neu einrichten und konfigurieren müssen – ChatGPT & Co. stehen Ihnen mit Tipps zur Seite.

Unsere Erfahrung ist, dass die Qualität derartiger Anleitungen in einfachen Fällen meist gut ist. Wenn Sie dagegen echtes Spezialwissen brauchen, wo eine längere Internet-Recherche nur wenige und womöglich einander widersprechende Ratschläge liefert, sollten Sie auch KI-Antworten mit viel Vorsicht betrachten. Ein Beispiel dafür gibt der Unterabschnitt »MariaDB-Tuning«.

PostgreSQL: User-Administration

Das Setup neuer Datenbanken umfasst die immer gleichen Schritte: Datenbank erzeugen, Benutzer erzeugen, den Benutzern die notwendigen Rechte zuweisen. Mit KI-Tools können Sie sich eine Menge Tipparbeit ersparen.

Prompt: PostgreSQL: I want to create a new database and set up three users: One with full administrative privileges, one for database use (basically SELECT, INSERT, UPDATE, DELETE) and one for an automated backup system. Can you help me with the SQL commands I need?

Certainly! Below are the SQL commands needed to create a new database and set up three users with the specified privileges in PostgreSQL (more details …)

• Connect to PostgreSQL as a superuser (usually postgres).

  psql -U postgres

• Create the database:

  CREATE DATABASE your_database_name;

• Create the users:

  CREATE USER admin_user   WITH PASSWORD 'admin_password';
  CREATE USER regular_user WITH PASSWORD 'regular_password';
  CREATE USER backup_user  WITH PASSWORD 'backup_password';

• Grant privileges:

  -- Admin user
  GRANT ALL PRIVILEGES ON DATABASE your_database_name 
    TO admin_user;
  \c your_database_name
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public 
    TO admin_user;
  ...
  ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT ALL PRIVILEGES ON FUNCTIONS TO admin_user;
  
  -- Regular user
  GRANT CONNECT ON DATABASE your_database_name 
    TO regular_user;
  ...
  
  -- Backup user
  GRANT CONNECT ON DATABASE your_database_name 
    TO backup_user;
  ...

Replace your_database_name, admin_user, admin_password, regular_user, regular_password, backup_user, and backup_password with your desired names and passwords.

An der Antwort, die hier aus Platzgründen etwas verkürzt wiedergegeben ist, gibt es wenig auszusetzen. Die Schwierigkeit dieser Frage war nicht hoch. Ein KI-Tool dient hier primär der Zeitersparnis. Noch effizienter klappt das, wenn Sie gleich die gewünschten Namen für die Datenbank und die User-Accounts angeben.

Prompt: Perfect. Could you please repeat the SQL commands (without the explanation) using the following names:

database: bikerental
admin user: bike_admin
regular user: bike_user
backup user: bike_backup

SQL Server: automatisiertes Backup

Gewissermaßen der Klassiker unter den Administrationsaufgaben ist die Durchführung eines Backups. Je nach DBMS und je nach Datenbankgröße gibt es unzählige Varianten. In diesem Beispiel soll ein inkrementelles Backup eingerichtet werden.

Prompt: SQL Server: I would like to set up an automated backup system for my accounting database. A full backup every Sunday night, incremental backups the rest of the week. I want the system to keep the last two weeks of backups before overwriting them. The backups should be stored in a local directory, from where they are synchronized to an external server (not relevant here).

Die Antwort von ChatGPT würde sich in der Formatierung dieses Buchs über dreieinhalb Seiten erstrecken. Wir haben hier nur die Eckpunkte zitiert.

To set up an automated backup system for your SQL Server database with the specified requirements, you can use SQL Server Agent for scheduling the backup jobs. Here’s a step-by-step guide:

Create a script for the full backup, saving it as FullBackup.sql:

DECLARE @BackupFileName NVARCHAR(500)
DECLARE @BackupPath NVARCHAR(500) = 'C:\SQLBackups\'
DECLARE @DatabaseName NVARCHAR(500) = 'YourDatabaseName'
-- Generate file name with timestamp
SET @BackupFileName = @BackupPath + @DatabaseName + 
  '_Full_' + CONVERT(NVARCHAR(500), GETDATE(), 112) + 
  '.bak'
-- Perform full backup
BACKUP DATABASE @DatabaseName 
TO DISK = @BackupFileName 
WITH INIT

Create a script for the differential backup, saving it as DifferentialBackup.sql:

...
-- Perform differential backup
BACKUP DATABASE @DatabaseName 
TO DISK = @BackupFileName 
WITH DIFFERENTIAL, INIT

Create a script for cleaning up old backups, saving it as CleanupBackups.sql:

DECLARE @BackupPath NVARCHAR(500) = 'C:\SQLBackups\'
DECLARE @DaysToKeep INT = 14
-- Delete backup files older than @DaysToKeep days
EXECUTE master.dbo.xp_delete_file 0, @BackupPath, 
        'bak', @DaysToKeep, 1

Create SQL Server Agent Jobs: Open SQL Server Management Studio (SSMS), connect to your SQL Server instance, expand the SQL Server Agent node, right-click on Jobs and select New Job …

Grundsätzlich klingt der Lösungsansatz vernünftig, und Sie realisieren das Backup-System. Dabei wollen Sie einen Blick in die Originaldokumentation werfen:

Prompt: Can you provide manual links for BACKUP and RESTORE DATABASE?

Certainly! Here are the links to the official Microsoft documentation for the BACKUP and RESTORE DATABASE commands in SQL Server:

• https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

• https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15

Die Links beziehen sich auf den SQL Server 15 (2019). Aktuell war zum Zeitpunkt, als wir dieses Beispiel durchgetestet haben, SQL Server 16 (2022). Bei den Backup- und Restore-Kommandos sind allerdings keine großen Syntaxänderungen zu erwarten – insofern sind die Links in Ordnung.

Die wichtigste Regel beim Einrichten eines Backup-Systems lautet: Testen Sie auch die Wiederherstellung!

Prompt: Suppose my main server goes down on a Wednesday. I pull the backups from my offsite backup source. How do I recover my database?

To recover your database from backups when the main server goes down, follow these steps (more details …). Here’s a summary of all the commands you might need:

-- Restore full backup
RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\SQLBackups\Accounting_Full_20240602.bak'
WITH NORECOVERY;
-- Restore differential backup from June 3, 2024
RESTORE DATABASE Accounting
FROM DISK = 'C:\SQLBackups\Accounting_Diff_20240603.bak'
WITH NORECOVERY;
-- Restore differential backup from June 4, 2024
RESTORE DATABASE Accounting
FROM DISK = 'C:\SQLBackups\Accounting_Diff_20240604.bak'
WITH NORECOVERY;
-- Restore differential backup from June 5, 2024
RESTORE DATABASE Accounting
FROM DISK = 'C:\SQLBackups\Accounting_Diff_20240605.bak'
WITH RECOVERY;

Schema einer Datenbank ermitteln

Damit ChatGPT Ihnen bei Fragen zu einer schon existierenden Datenbank behilflich sein kann, müssen Sie vorher das Datenbankschema bekannt machen. Am einfachsten gelingt das, wenn Sie eine SQL-Datei mit allen erforderlichen CREATE-TABLE-Kommandos haben. Bei ChatGPT können Sie die Datei per Drag-and-drop hochladen. In VS Code oder einem anderen Editor laden Sie die Datei und beziehen sich in Ihrer Frage auf diese Datei.

Doch woher nehmen Sie eine derartige Datei? Fast alle DBMS verfügen über Kommandos, um entsprechende Schemadateien in Textform zu erzeugen. Am einfachsten fragen Sie ChatGPT, wie das bei dem von Ihnen eingesetzten Programm am einfachsten gelingt.

Prompt: MySQL: How can I save my database schema in a SQL file?

To save your MySQL database schema to a SQL file, you can use the mysqldump command with the --no-data option. This will create a SQL file containing only the database structure (tables, indexes, etc.) without any of the data.

mysqldump -u [username] -p --no-data [dbname] > schema.sql

Here’s a breakdown of the command (more details …)

Prompt: Is there a similar command for SQL Server?

Yes, in SQL Server, you can use the SQL Server Management Studio (SSMS) or a script to generate the schema-only backup. Here’s how you can do it using both methods:

Using SQL Server Management Studio (SSMS)

• Open SSMS and connect to your SQL Server instance.

• Right-click the database you want to script.

• Select Tasks • Generate Scripts

• In the wizard, click Next to go past the introduction screen.

• Select the specific database objects you want to script (or select the entire database). Click Next.

• On the Set Scripting Options page, click Advanced.

• (More details …)

Using a Script with SQLCMD

Alternatively, you can use the sqlcmd utility along with a custom query to generate the schema-only script. Here’s an example script:

sqlcmd -S [server_name] -U [username] -P [password] `
       -d [database_name] `
       -Q "EXEC sp_generate_inserts @schema_only = 1" `
       -o "[output_file].sql"

Using PowerShell and SQL Server Management Objects (SMO)

You can also use a PowerShell script to generate the schema (more details …)

Bei MySQL ist das mysqldump-Kommando der einfachste Weg. Beim SQL Server stellt ChatGPT gleich drei Lösungswege zur Auswahl, wobei sich der erste auf das SQL Server Management Studio bezieht. Derartige Anleitungen haben allerdings den Nachteil, dass sie oft nur mit einer bestimmten Version dieser Benutzeroberfläche eins zu eins nachvollzogen werden können.

Prompt: To which version of SSMS does your answer apply?

The steps provided for generating a schema-only script using SQL Server Management Studio (SSMS) apply to SSMS versions 2012 and later, including the latest versions up to SSMS 19. SSMS has had a consistent interface for these scripting tasks across these versions, so the steps should be applicable.

Als wir den obigen Prompt im Mai 2024 ausgeführt habe, war allerdings bereits Version 20.1 aktuell. So aktuell war das Trainingsmaterial von ChatGPT nicht. Aber mit etwas Glück funktioniert die Anleitung so oder ganz ähnlich auch mit der neuesten Version.

MariaDB-Tuning

Vor ein paar Monaten haben wir einen LAMP-Server eingerichtet (Linux + Apache + MariaDB + PHP). MariaDB hat großen Einfluss auf die Gesamt-Performance der dort laufenden Webapplikation. Dementsprechend haben wir uns einige Mühe beim Tuning des Datenbankservers gegeben. Hätte uns ChatGPT auch zum Ziel gebracht?

Prompt: I have just installed a new LAMP server running Ubuntu 24.04. The server has 64 GB of RAM. It will run as a web server (Apache), mail server (Postfix) and database server (MariaDB 10.11). I need help tuning MariaDB.

MariaDB may use up to 50% of the RAM. There are many databases (about 200) with about 20 tables each. Read performance is critical. (The ratio between SELECT and INSERT/UPDATE/DELETE is about 100:1).

Please give me five concise recommendations for MariaDB or Linux settings to achieve good performance.

To optimize MariaDB for your LAMP server setup, follow these five recommendations:

• Adjust the InnoDB Buffer Pool Size (more details …)

  [mysqld]
  innodb_buffer_pool_size = 32G

• Optimize Query Cache (more details …)

  [mysqld]
  query_cache_type = 1
  query_cache_size = 1G
  query_cache_limit = 1M

• Increase the Number of InnoDB Buffer Pool Instances (more details …)

  [mysqld]
  innodb_buffer_pool_instances = 16

• Tune InnoDB Log File Size and Flush Method (more details …)

  [mysqld]
  innodb_log_file_size = 2G
  innodb_flush_method = O_DIRECT

• Optimize File System and I/O Scheduler: Use the XFS or ext4 file system with the noatime option to reduce disk I/O and set the I/O scheduler to deadline for better performance (more details)

  sudo mount -o remount,noatime /
  sudo echo deadline > /sys/block/sda/queue/scheduler

Restlos überzeugt haben uns die Vorschläge nicht. Wirklich unumstritten ist eigentlich nur die erste Maßnahme.

Den Query Cache haben wir, unabhängig von der Empfehlung von ChatGPT, ebenfalls aktiviert. Allerdings hat sich in den letzten Jahren die Meinung durchgesetzt, dass der Cache nur in Ausnahmefällen positiv auf die Gesamt-Performance einwirkt. Ein Blick auf die Cache-Statistik lässt vermuten, dass unser Server tatsächlich zu diesen Ausnahmen zählt. Wie dem auch sei: In aktuellen MySQL-Versionen steht der Query Cache gar nicht mehr zur Verfügung; in MariaDB ist er standardmäßig deaktiviert. ChatGPTs Empfehlung für den Query Cache resultiert vermutlich aus unzähligen alten Webseiten, die beim Training des Sprachmodells verwendet wurden. Das ist durchaus ein gängiges Problem: ChatGPT präferiert tendenziell veraltete Maßnahmen, weil diese im Trainingsmaterial öfter vorkommen als moderne Richtlinien.

Die Maßnahmen 3, 4 und 5 werden vermutlich kaum messbare Performance-Unterschiede mit sich bringen. Falls wirklich der Deadline-Scheduler zum Einsatz kommen soll, ist zu beachten, dass dieser in aktuellen Kernel-Versionen (und so auch unter Ubuntu 24.04, das im Prompt erwähnt wurde), mq-deadline heißt.

Dafür fehlt uns in der Liste der Vorschläge eine andere Maßnahme, die bei dem im Prompt beschriebenen Setup eigentlich naheliegend wäre: Die Erhöhung des Limits für gleichzeitig offene Dateien (mysql soft nofile 65535 und mysql hard nofile 65535 in /etc/security/limits.conf).

Kurz und gut, als Tuning-Gehilfe kann ChatGPT zwar Denkanstöße geben; aber die Qualität der Antwort ist bei Weitem nicht so gut wie beim Zusammenstellen von SQL-Kommandos.