Mysql bzw. mariadb und postgresql haben teiweise große Unterschiede.
Deshalb ein paar der Spezialitäten beider DB
Alle Datenbanken zeigen:
show databases;
Alle Datenbanken zeigen, die den Pattern xwiki haben
show databases like '%xwiki%';
mysqldump -S /var/run/mysqld/mysqld.sock --no-data --compact dbispconfig client web_domain web_database_user
gibt den Aufbau der Tabellen client web_domain web_database_user aus
Empfehlungen
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
skip-name-resolve❌1❌
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache(400) > 2682 or -1 (autosizing if supported)
performance_schema=ON
key_buffer_size (~ 25M)
innodb_buffer_pool_size (>= 1.2G) if possible.
innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals 25% of buffer pool size.
skip-name-resolve=1 ❌
Falsch, muss 0 bleiben, sonst kein ispconfig und kein postfix❗
2024-02-04 14:42:12 15200 [Warning] Access denied for user 'ispconfig'@'127.0.0.1' (using password: YES)
2024-02-04 14:42:39 15235 [Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
2024-02-04 14:43:55 15321 [Warning] Access denied for user 'ispconfig'@'127.0.0.1' (using password: YES)
2024-02-04 14:43:55 15322 [Warning] Access denied for user 'ispconfig'@'127.0.0.1' (using password: YES)
skip-name-resolve = 0 # ACHTUNG❗ das muss 0 sein❗
tmp_table_size = 32M
join_buffer_size = 256k # Hier gibt es erhebliche Bedenken, deshalb nur verdoppelt
max_heap_table_size = 32M
table_definition_cache = 2048 # auto 0 -1 wird nicht unterstütz, DB startet nicht
key_buffer_size = 25M
#innodb_buffer_pool_size = 256M
#innodb_log_file_size = 32M
https://releem.com/docs/mysql-performance-tuning/join_buffer_size
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
13 main 5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
Version anzeigen:
# psql --version
psql (PostgreSQL) 15.6 (Debian 15.6-0+deb12u1)
Show datadbases:
postgres=# \l
show Users,roles
postgres=# \ul
z.B. /etc/postgresql/13/main/pg_hba.conf
Zugriffsrechte basierend auf Internetadressen, Datenbanken und User setzen